我們常常會需要將現在的數字跟以現在為基準的過去比較，例如：現在 (current) 跟過去幾天 (past 7 days) 做比較。這時候的數據處理方法，就會用到 SQL Window Function！

如果你還沒學過 SQL 視窗函數 (Window Function)，那可以先讀一下：

• 3 個基本寫法：先搞懂 SQL 視窗函數 Window Function 的使用場景
• 一定要用視窗函數嗎？視窗函數跟 JOIN 的差別？寫法實例分享：用戶第一次行為分析
• 用視窗函數 SUM：累積加總的 Window Function 寫法

SQL 視窗函數的基礎：PARTITION BY、ORDER BY

如果你已經讀完上面 SQL 視窗函數 (Window Function) 的基礎，代表你已經掌握的在小視窗內分群 (PARTITION BY) 跟排序 (ORDER BY)

今天會進階補充說明的是視窗區間，怎麼從固定計算區間變成浮動區間！

-- SQL 視窗函數的基礎：PARTITION BY、ORDER BY

sum(gmv) over (
		PARTITION BY grass_date
		ORDER BY gmv DESC)

在文章：用視窗函數 SUM：累積加總的 Window Function 寫法 中，所講到的就是這個語法的使用場景！

SQL 視窗函數的進階語法：FRAME

為什麼要用 FRAME？

• 預設的 FRAME 是過去的全部到現在這一行
• 累積加總 (預設的 FRAME)：我們常用的 sum(n) over(order by m) 的結果其實就是 sum(n) over(order by m RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，UNBOUNDED PRECEDING 就是過去的全部，CURRENT ROW 就是直到現在這一行
• 所以除了預設以外，還有其他東西可以設定的！接下來，就來解釋這一塊

什麼是 FRAME？用在哪裡？

• SQL 視窗函數 (Window Function) 除了分群 (PARTITION BY) 跟排序 (ORDER BY) 之外，還有一個元素叫 FRAME，讓你的小視窗變成 Slide Window (會滑動的小視窗)，功能是，從原本固定的計算區間變成浮動區間
  • 沒有 FRAME 的小視窗：也就是預設，從過去一直到現在 (上一段講的)
  • 有 FRAME 的小視窗：你可以指定你要計算的區間

FRAME 的 Window Function 怎麼寫？

• 有 FRAME 的視窗函數，會延續在 PARTITION BY、ORDER BY 後方，設定一下小視窗的區間
• 接下來就來細講一下，FRAME 語法的細節寫法

-- SQL 視窗函數的進階語法：FRAME

sum(gmv) over( 
		PARTITION BY grass_date
		ORDER BY gmv DESC
		[frame_type] BETWEEN [start_criteria] AND [end_criteria] )  -- 這行是 frame

-- 不一定要 BETWEEN，也可以簡寫成 [frame_type] [Start_criteria] / [end_criteria]

Window Function 的 FRAME 詳細解釋：參數有哪些？Criteria 和 Frame type

Window Function 的 FRAME：Criteria

• 區間 Criteria：小視窗的浮動區間 (Slide Window) 要從哪裡開始？哪裡結束？
  • 要寫開始跟結束的起點跟終點
    • CURRENT ROW : 目前這筆資料
    • UNBOUNDED : 指無限的，第一筆一直到 current row 當作範圍，或是 current row 到最後一行
    • PRECEDING / FOLLOWING : current row 之前 / 之後
  • 舉例：過去每一筆到現在這一筆：UNBOUNDED RECEDING AND CURRENT ROW

Window Function 的 FRAME：Frame type

• 類型 Frame type：做出浮動區間的邏輯，總共有 3 種
  • 第一種：ROWS 用位置來做區間，例如說前面一筆到現在這一筆
  • 第二種：RANGE 用值的大小來做區間，例如說比現在的值小 3 個刻度的資料
  • 第三種：GROUPS 以值來分組別，例如說 0 的是一組，3 的是一組，可以設定要哪些組別，有點 ROWS + RANGE 的感覺

最常用的 Window Function FRAME：Range

SELECT avg(gmv) OVER (
    PARTITION BY category
    ORDER BY grass_date
    RANGE BETWEEN interval '1' month PRECEDING AND CURRENT ROW)
FROM orders

• 寫法就是：RANGE BETWEEN 起點 AND 終點
• 起點跟終點不一定要包含 CURRENT ROW
• Range 的統計方式是跟 order by 這一行的值相比
• 情境一：計算跟 orderdate 相比，過去 30 天的平均價格：RANGE BETWEEN interval '30' days PRECEDING AND interval '1' days PRECEDING

-- 情境一
SELECT avg(gmv) OVER (
    PARTITION BY category
    ORDER BY grass_date
    RANGE BETWEEN interval '30' days PRECEDING AND interval '1' days PRECEDING)
FROM orders

• 情境二：計算跟 orderdate 相比，這 7 天的平均價格 (包含 orderdate)。RANGE BETWEEN interval ‘6’ days PRECEDING AND CURRENT ROW

-- 情境二
SELECT avg(gmv) OVER (
    PARTITION BY category
    ORDER BY grass_date
    RANGE BETWEEN interval '6' days PRECEDING AND CURRENT ROW)
FROM orders

Window Function FRAME：Row

SELECT avg(gmv) OVER (
    PARTITION BY category
    ORDER BY grass_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM orders

• 寫法就是：RANGE BETWEEN 起點 AND 終點
• 起點跟終點不一定要包含 CURRENT ROW
• Row 的統計方式是純粹的第幾行到第幾行

Window Function FRAME：Groups

• 比較少 SQL 語言支持 Groups，商業情境上，我也比較少碰到，以後有更好的舉例再講！
• 參考資料：比較一下 3 者的不同：Window Function Framing: ROWS vs RANGE vs GROUPS

Window Function FRAME：實際案例

• 資料庫：daily_sales 這份資料集，包含
  • 日期 (grass_date)：2021/1/1~2021/12/31 的資料
  • 銷售訂單數量 (order fraction)
• 情境一：3 個欄位
  • 當天日期
  • 當天訂單數
  • 過去 7 天平均訂單數

-- 情境一
SELECT grass_date -- 當天日期
, order_fraction AS orders -- 當天的業績
, SUM(order_fraction) OVER (ORDER BY grass_date RANGE between interval '7' day preceding and interval '1' day preceding) Last_7_days_orders --過去 7 天平均訂單數
FROM DAILY_SALES

總結

• 視窗函數 Window Function 可以處理進階一點的數據分析，而且可以做的很有效率
• 先把 PARTITION BY、ORDER BY 在小視窗裡面做的程序搞懂之後
• 再來學 Frame

更多學習資源推薦

如何使用這個網站？我的數據分析文章導覽！裡面也會分享更多實用 SQL 教學：本站數據分析文章導覽 及 更多 SQL 實用文章教學