滑動視窗

SQL 資料操作

Mona Khalil

Data Scientist, Greenhouse Software

滑動視窗

相對目前列執行計算
可用來計算逐步累計、總和、平均等
可依一或多個欄分割

滑動視窗關鍵字

ROWS BETWEEN <start> AND <finish>

PRECEDING
FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
CURRENT ROW

滑動視窗範例

-- 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            |

滑動視窗框架

-- 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';

一起來練習吧！

SQL 資料操作