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