SQL 窗口函數 Window Function：三大應用快速教學

SQL 的資料分析任務中，窗口函數（Window Function）是能實現複雜運算的強大工具，它讓你能基於表格中的一小部分資料來運算，常見的運算包括平均、排序、還有「參照」其他列資料等等，我過去在 SQL 程式面試中也非常依賴窗口函數來幫我解題，可以說資料科學家幾乎不得不會使用窗口函數。

這則資料科學筆記是一篇 SQL 窗口函數的超基礎教學，目標是讓你在 2 分鐘內快速認識窗口函數好用之處。以下將先快速介紹窗口函數是什麼，接著分享筆者工作經驗中最常用的三大類窗口函數：

聚合型：SUM()、AVG()、COUNT()、MIN()、MAX()
排序型：RANK()、DENSE_RANK()、ROW_NUMBER()、NTILE()
位移型：LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()

要一次背熟這些語法當然不太容易，因此本篇筆記著重於分享這些函數的應用方式，幫助你記得「原來這個分析情境就是該用窗口函數！」。

（本文介紹的窗口函數語法適用於大多資料庫系統，像是 PostgreSQL 或 MySQL，也適用於雲端分析平台例如 Google BigQuery）

窗口函數是什麼

為了介紹窗口函數，在此引用來自 PostgreSQL 的說明：

SQL 的窗口函數（Window Function）是對表格的其中「一小塊」幾列資料進行運算，這「一小塊」資料是根據使用者設定的條件選擇、會跟當下的列資料有特定關聯。這類型運算做的事大多是聚合函數（Aggregate Function）能做到的，但與 GROUP BY 不同的是，窗口函數不會讓多個列整合成單一個列，即使窗口函數運算後，每一列還是能保有原本的樣子。

上面這段說明中，最重要的關鍵字是「特定關聯」，當下這列的資料跟其他資料之間會有的關聯動作有兩個：分區（Partition）以及排序（Order），理解這個關鍵後，以下語法就沒那麼複雜了。窗口函數的語法如下（參考自《資料科學 SQL 工作術》）：

< 窗口函數 > OVER (
              PARTITION BY [ 分區的欄位名稱 ]
              ORDER BY [ 排序的欄位名稱 ]
            )

< 窗口函數 >：此函數會作用於分區的資料
OVER：窗口函數非寫不可的語句，表示函數會作用於 OVER 後面定義的分區
PARTITON BY：以特定欄位作為分區
ORDER BY：根據特定欄位排序

（PARTITON BY 跟 ORDER BY 在 SQL 窗口函數不是必要的，後續文章會介紹）

剛開始學覺得有點複雜嗎？如果你第一次認識窗口函數，我認為先不用糾結於 SQL 語法，用一個簡單重點記得窗口函數的功能就好：

窗口函數可以做到 GROUP BY 運算，但是資料列數完全不會改變！

以後面會介紹的 MAX() 函數來圖示舉例：

聚合型窗口函數

聚合（Aggregate）指的幾乎都是我們用 GROUP BY 會做的運算，用於為資料做出某一種總結：

SUM() OVER (PARTITION BY ...)：加總分區內的數值
COUNT() OVER (PARTITION BY ...)：計數分區內的資料
AVG() OVER (PARTITION BY ...)：平均分區內的數值
MAX() OVER (PARTITION BY ...)：分區內的數值取最大值
MIN() OVER (PARTITION BY ...)：分區內的數值取最小值

在以上這幾個窗口函數，都用不到 ORDER BY，畢竟這些運算都沒有跟排序有關的邏輯。另外，PARTITION BY 也不是必要的，如果沒有 PARTITION BY，表示整個資料表都是同一個分區。

聚合型窗口函數的計算，筆者好豪在資料科學的每日工作中超級頻繁運用的是計算數據佔比，分析情境例如：

韓國的客群，佔全部客戶數量的多少百分比？
行銷預算有多少佔比是花在網路廣告？又有多少是花在實體活動？

在這些情境下使用窗口函數，可以在一次 SQL 查詢就算出所有需要的百分比資料，十分方便。下圖是計算金額佔比的範例，再次提醒，這裡 OVER () 裡面是空的、沒有 PARTITION BY，這表示整個資料表是同一個分區：

排序型窗口函數

排序型（Rank）窗口函數顧名思義就是用於排序，與 ORDER BY 會做的運算類似，但是使用窗口函數可以做出更精細的排序：

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)：為每個分區依據指定欄位算出不重複的排序序號，序號從 1 開始
RANK() OVER (PARTITION BY ... ORDER BY ...)：為每個分區依據指定欄位算出序號，序號可能重複。若序號重複，後方序號會因為重複人數跳號
DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)：為每個分區依據指定欄位算出序號。序號可能重複，若序號重複，後方序號不會因為重複人數跳號
NTILE( [N 等份] ) OVER (PARTITION BY ... ORDER BY ...)：將每個分區依據指定欄位排序後，分成 N 等份，每一個等份給予同一個序號

其中，前三者 ROW_NUMBER、RANK、以及 DENSE_RANK 的差別，是資料科學的 SQL 面試非常高頻率會考的面試題，這題 LeetCode SQL 練習就是個典型範例。它們三個函數主要任務都是排列出序號，需要特別記的只有它們各自如何處理數值相同的「平手」情形：

ROW_NUMBER()：堅持序號不重複。就算平手，還是給予不同序號
RANK()：平手的資料給予相同序號，但後續的序號會跳號
- 如果有三個人都是第 11 名，那再下個序號會跳到從 14 繼續
DENSE_RANK()：平手的資料給予相同序號，但後續的序號不會跳號
- 如果有三個人都是第 11 名，再下個序號依然從 12 繼續
- 記憶法：DENSE 在英文是稠密、沒有間隔的意思，以此聯想序號之間沒有間隔、不會跳號

排序型窗口函數的實際用途，以一個範例說明更好懂。假設我們有 6 個人的百米短跑秒數資料，要頒獎給跑得快的前幾名：

情境 1：頒獎給跑最快的前 3 名，獎品只有 3 份、不能多發
- 可以使用 ROW_NUMBER() 或 NTILE (2)
- 但不能使用 RANK() 與 DENSE_RANK()
  - 原因請見下圖，若使用 rank <= 3 來篩選，會得到不只 3 筆資料
情境 2：頒獎給跑最快的前 3 名，遇到速度平手的情形就增發獎項
- 可以使用 RANK() 或 DENSE_RANK()
- 反而不能使用 ROW_NUMBER() 與 NTILE ()

SELECT 
    ROW_NUMBER( ) OVER ( ORDER BY 秒數 )  AS  row_number,
    RANK( ) OVER ( ORDER BY 秒數 )  AS  rank,
    DENSE_RANK( ) OVER ( ORDER BY 秒數 )  AS  dense_rank,
    NTILE( 2 ) OVER ( ORDER BY 秒數 )  AS  ntile_2
FROM  百米短跑秒數

位移型窗口函數

位移型的窗口函數可以直接查看其他列的資料，讓你能與其他列資料比較。這類型動作透過 SELF JOIN 也能達成，不過，用窗口函數寫出來的 SQL 查詢會更簡潔好讀。常見的位移型窗口函數包括：

LAG() OVER (PARTITION BY ... ORDER BY ...)：把資料往後移動，用來與上一列資料比較
LEAD() OVER (PARTITION BY ... ORDER BY ...)：把資料往前移動，用來與下一列資料比較
FIRST_VALUE() OVER (PARTITION BY ... ORDER BY ...)：查詢該分區第一列資料
LAST_VALUE() OVER (PARTITION BY ... ORDER BY ...)：查詢該分區最後一列資料

在需要分析同一個單位（例如同一位顧客）的一系列行為時，我相當常用這些位移型窗口函數，像是：

顧客購買頻率分析：顧客距離上次消費，間隔了幾天？
新舊顧客行為比較：顧客會隨著品牌忠誠度越花越多錢？還是只有第一次消費花最多錢？

以下圖示用 LAG() 函數介紹「與上一列資料比較」的其中一種應用：

值得一提的還有 LAG() 與 LEAD() 的參數設定，在此就簡略說明，因為我個人不是那麼常調整這些參數設定，以下拿 LAG() 當例子，介紹函數內部的三個參數：LAG ( [ 要位移的欄位 ], [ 位移列數 ], [ 沒資料時的預設值 ] )。

結語

希望讀到這裡的你已經能感受到，SQL 的窗口函數（Window Function）是可以做到多種複雜運算的好用工具，尤其本文介紹的三大類型，在業界工作中將會非常常使用。

事實上，SQL 的窗口函數不只這些！還有能計算百分位數的 PERCENTRANK、參照特定列數的 NTH_VALUE 等等，然而，這則筆記介紹到這些窗口函數是筆者好豪個人工作經驗中體會到最好用的函數，例如，在這篇 Mode 網站的統計數據中，我們方才介紹過的 SUM、ROW_NUMBER、DENSE_RANK、LAG、以及 MAX 函數佔了所有窗口函數使用頻率的將近 7 成！

因此，只要能熟悉這些窗口函數，將能讓你的 SQL 以及資料科學功力大增！

最後，如果你正在準備 SQL 的資料科學面試，在我過去多次的面試經驗裡，運用溝通能力跟熟悉程式語法知識同等重要！SQL 面試並不只是讓你一味秀出酷炫的語法，面試官還期待你釐清資料、用 Pseudo Code 展現架構、最後簡要呈現結果等等，這些 SQL 面試重要技能，我都分享在 Coding Interview 技術面試溝通技巧要點這篇文章裡，推薦你繼續閱讀。

本文撰寫靈感來自《資料科學 SQL 工作術》這本書，書中完整教學 SQL 查詢技能，不只適合初學者，對 SQL 稍有經驗的專業人士也適合閱讀。我剛讀此書時，已有超過 5 年在工作中天天用 SQL 的經驗，但是翻閱此書依然讓我收穫良多，尤其書中教學的進階技巧確實增加了我的分析效率，除了本文介紹的窗口函數，還有儲存視圖（Views）、COALESCE() 函數、SELF JOIN 等等，我認為都是在資料科學職涯越早學會越好的技能。

只要你想扎實學 SQL，不論你是 SQL 新手或已稍有經驗，這本書不只讓你透過數據案例學習，還教你用 ChatGPT 提升學習效率，值得一讀。

sql-for-data-scientists 《資料科學 SQL 工作術》（博客來連結）

如果這篇 SQL 語法筆記有幫助到你，歡迎追蹤好豪的 Facebook 粉絲專頁與 Threads 帳號，我會持續跟你分享我學習到的更多 SQL 技巧、以及資料科學知識；也可以點選下方按鈕，分享這篇文章給對數據分析有興趣的朋友們。

窗口函數是什麼

聚合型窗口函數

排序型窗口函數

位移型窗口函數

結語

推薦閱讀