今天給大家分享一個 SQL 窗口函數的速查表，包括窗口函數的語法、窗口函數列表以及相關示例，內容適用于 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等關系型數據庫。
窗口函數概述
窗口函數（Window Function）基于一個滑動窗口，也就是與當前行相關的一組數據行為其計算出一個結果；通常也稱為分析函數（Analytic Function）。
我們知道，聚合函數（Aggregate Function）用于將一組數據匯總成一個結果；而窗口函數則為每一行數據計算出一個結果。它們的區別如下圖所示：
窗口函數的語法如下：
SELECT column1, column2,
window_function OVER (
PARTITION BY ...
ORDER BY ...
frame_clause) AS column_alias
FROM table_name;
其中，window_function 是窗口函數的名稱；OVER 子句包含三個可選項：分區（PARTITION BY）、排序（ORDER BY）以及窗口大小（frame_clause）。
以下是一個窗口函數的示例：
SELECT city, month,
sum(sold) OVER (
PARTITION BY city
ORDER BY month
RANGE UNBOUNDED PRECEDING) total
FROM sales;
該查詢返回了不同城市，按照月份排序后，累計到每個月份的總銷量；OVER 子句中各個選項的作用在下文中進行介紹。
窗口函數還提供了命名窗口的功能：
SELECT column1, column2,
window_function1 OVER window_name
window_function2 OVER window_name
FROM table_name
WINDOW window_name AS (
PARTITION BY ...
ORDER BY ...
frame_clause);
當多個窗口函數的 OVER 子句完全相同，命名窗口可以簡化函數的輸入。MySQL、PostgreSQL、SQLite 支持命名窗口，Oracle、SQL Server 不支持。
PARTITION BY
OVER 子句中的 PARTITION BY 選項用于定義分區，作用類似于 GROUP BY 分組；如果指定了分區選項，窗口函數將會分別針對每個分區單獨進行分析；否則，所有數據作為一個整體進行分析。
以下查詢按照不同 city 統計總銷量：
SELECT month, city, sold,
sum(sold) OVER (
PARTITION BY city ) sum
FROM sales;
ORDER BY
OVER 子句中的 ORDER BY 選項用于指定分區內的排序方式，與 ORDER BY 子句的作用類似；排序選項通常用于數據的排名分析。下圖演示了按照 city 分區、按照 month 排序之后的數據：
窗口大小
OVER 子句中的 frame_clause 選項用于指定一個滑動的窗口。窗口總是位于分區范圍之內，是分區的一個子集。指定了窗口之后，分析函數不再基于分區進行計算，而是基于窗口內的數據進行計算。
指定窗口大小的語法如下：
ROWS | RANGE | GROUPS BETWEEN frame_start AND frame_end
其中，ROWS 表示以行為單位計算窗口的偏移量；RANGE 表示以數值（例如 10 天之內）為單位計算窗口的偏移量；GROUPS 以組（ORDER BY 排序相同的數據為一組）為單位計算窗口的偏移量，只有 PostgreSQL、SQLite 支持 GROUPS 選項。
frame_start 用于定義窗口的起始位置，可以指定以下內容之一：
frame_end 用于定義窗口的結束位置，可以指定以下內容之一：
以下是窗口選項的一些示例：
第一個窗口使用 ROWS 選項，包含了前后各 1 行以及當前行；第二個窗口使用 RANGE 選項，包含了當前行的數值減去 1（4-1=3）到當前行的數值加上 1（4+1=5）之間的所有數據；第三個窗口使用 GROUPS 選項，包含了前后各 1 組（ORDER BY 排序相同的數據為一組）和當前行。
如果沒有指定窗口大小選項，默認使用的窗口如下：
SQL 子句邏輯執行順序
窗口函數可以用于 SELECT 列表或者 ORDER BY 子句中，但是不能出現在其他子句中。各種 SQL 子句的邏輯執行順序如下：
常用窗口函數
排名窗口函數
RANK()，計算每行數據在其分區中的名次；如果存在名次相同的數據，后續的排名將會產生跳躍。
DENSE_RANK()，計算每行數據在其分區中的名次；存在名次相同的數據，后續的排名不會跳躍。
PERCENT_RANK()，與 RANK() 相同，但是以百分比的形式顯示每行數據的名次，取值范圍 [0-1]。
ROW_NUMBER()，為分區中的每行數據分配一個唯一序列號，從 1 開始分配。
NTILE()， 將分區內的數據分為 N 等份，為每行數據計算其所在的位置。
CUME_DIST()， 計算每行數據在其分區內的累積分布， 也就是排在該行數據之前的所有數據所占的比率，取值范圍 (0-1]。
排名窗口函數不支持動態的窗口大小（frame_clause），而是以整個分區（PARTITION BY）作為分析的窗口。
下圖演示了 ROW_NUMBER()、RANK() 以及 DENSE_RANK() 函數的效果：
RANK() 和 DENSE_RANK() 函數必須指定 ORDER BY 選項，ROW_NUMBER() 函數可以不指定 ORDER BY 選項。
下圖演示了 CUME_DIST() 和 PERCENT_RANK() 函數的效果：
CUME_DIST() 和 PERCENT_RANK() 函數必須指定 ORDER BY 選項。
下圖演示了 NTILE() 函數的效果：
NTILE() 函數必須指定 ORDER BY 選項。
取值窗口函數
LAG(expr, offset, default)， 返回分區中當前行之前第 offset 行對應的 expr 。offset 和 default 可選，默認值分別為 1 和 NULL。
LEAD(expr, offset, default)， 返回分區中當前行之后第 offset 行的對應expr 。offset 和 default 可選，默認值分別為 1 和 NULL。
FIRST_VALUE(expr)， 返回窗口內第一行對應的 expr 。
LAST_VALUE(expr)， 返回窗口內最后一行對應的 expr 。
NTH_VALUE(expr, n)， 返回窗口內第 n 行對應的 expr 。
LAG 和 LEAD 函數不支持動態的窗口大小（frame_clause），而是以整個分區（PARTITION BY）作為分析的窗口。
下圖演示了 LAG(expr, offset, default) 和 LEAD(expr, offset, default) 函數的效果：
LAG(expr, offset, default) 和 LEAD(expr, offset, default) 函數必須指定 ORDER BY 選項。
下圖演示了 FIRST_VALUE(expr) 和 LAST_VALUE(expr) 函數的效果：
FIRST_VALUE(expr) 和 LAST_VALUE(expr) 函數可以不指定 ORDER BY 選項。
下圖演示了 NTH_VALUE(expr, n) 函數的效果：
SQL Server 不支持 NTH_VALUE(expr, n) 函數。NTH_VALUE(expr, n) 函數可以不指定 ORDER BY 選項。
聚合窗口函數
AVG(expr)，窗口內數據行的平均值；
SUM(expr)，窗口內數據行的和值；
COUNT(expr)，窗口內數據行的計數；
MAX(expr)，窗口內數據行的最大值；
MIN(expr)，窗口內數據行的最小值。
聚合函數通常也可以作為窗口函數使用，可以用于計算數據的累計總值和移動平均值。聚合窗口函數可以不指定 ORDER BY 選項。
該文章在 2024/3/15 15:02:11 編輯過
400 186 1886
