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