Schuifvensters

Gegevens manipuleren in SQL

Mona Khalil

Data Scientist, Greenhouse Software

Schuifvensters

Berekeningen uitvoeren ten opzichte van de huidige rij
Kan worden gebruikt voor lopende totalen, sommen, gemiddelden, etc.
Kan worden opgedeeld per één of meer kolommen

Schuifvenster trefwoorden

ROWS BETWEEN <start> AND <finish>

PRECEDING
FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW

Schuifvenster voorbeeld

-- Manchester City Thuiswedstrijden 
SELECT 
  date,
  home_goal,
  away_goal,
  SUM(home_goal) 
     OVER(ORDER BY date ROWS BETWEEN 
          UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM match
WHERE hometeam_id = 8456 AND season = '2011/2012';

| date       | home_goal | away_goal | running_total |
|------------|-----------|-----------|---------------|
| 2011-08-15 | 4         | 0         | 4             |
| 2011-09-10 | 3         | 0         | 7             |
| 2011-09-24 | 2         | 0         | 9             |
| 2011-10-15 | 4         | 1         | 13            |

Schuifvenster frame

-- Manchester City Thuiswedstrijden 
SELECT date,
       home_goal,
       away_goal,
       SUM(home_goal) 
          OVER(ORDER BY date 
          ROWS BETWEEN 1 PRECEDING 
          AND CURRENT ROW) AS last2
FROM match
WHERE hometeam_id = 8456
      AND season = '2011/2012';

Laten we oefenen!

Gegevens manipuleren in SQL