Sliding windows

Data Manipulation in SQL

Mona Khalil

Data Scientist, Greenhouse Software

Sliding windows

Perform calculations relative to the current row
Can be used to calculate running totals, sums, averages, etc
Can be partitioned by one or more columns

Sliding window keywords

ROWS BETWEEN <start> AND <finish>

PRECEDING
FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW

Sliding window example

-- Manchester City Home Games 
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            |

Sliding window frame

-- Manchester City Home Games 
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';

Let's practice!

Data Manipulation in SQL