今天給大家分享一個 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 子句中各個選項的作用在下文中進(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)多個窗口函數(shù)的 OVER 子句完全相同，命名窗口可以簡化函數(shù)的輸入。MySQL、PostgreSQL、SQLite 支持命名窗口，Oracle、SQL Server 不支持。

PARTITION BY

OVER 子句中的 PARTITION BY 選項用于定義分區(qū)，作用類似于 GROUP BY 分組；如果指定了分區(qū)選項，窗口函數(shù)將會分別針對每個分區(qū)單獨進(jìn)行分析；否則，所有數(shù)據(jù)作為一個整體進(jìn)行分析。

以下查詢按照不同 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ū)進(jìn)行計算，而是基于窗口內(nèi)的數(shù)據(jù)進(jìn)行計算。

指定窗口大小的語法如下：

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)前行開始。

• CURRENT ROW，窗口到當(dāng)前行結(jié)束；

• M FOLLOWING，窗口到當(dāng)前行之后的第 M 行、范圍 M 之內(nèi)或者第 M 個組結(jié)束；

• UNBOUNDED FOLLOWING，窗口到分區(qū)的最后一行結(jié)束。

以下是窗口選項的一些示例：

第一個窗口使用 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)前行。

如果沒有指定窗口大小選項，默認(rèn)使用的窗口如下：

• 如果指定了 ORDER BY， 默認(rèn)窗口為 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW；

• 如果沒有指定 ORDER BY， 默認(rèn)窗口為 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

SQL 子句邏輯執(zhí)行順序

窗口函數(shù)可以用于 SELECT 列表或者 ORDER BY 子句中，但是不能出現(xiàn)在其他子句中。各種 SQL 子句的邏輯執(zhí)行順序如下：

1. FROM、JOIN

2. WHERE

3. GROUP BY

4. 聚合函數(shù)

5. HAVING

6. 窗口函數(shù)

7. SELECT

8. DISTINCT

9. UNION、INTERSECT、EXCEPT、MINUS

10. ORDER BY

11. OFFSET

12. LIMIT、FETCH、TOP

常用窗口函數(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]。

下圖演示了 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 可選，默認(rèn)值分別為 1 和 NULL。

• LEAD(expr, offset, default)， 返回分區(qū)中當(dāng)前行之后第 offset 行的對應(yīng)expr 。offset 和 default 可選，默認(rèn)值分別為 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(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ù)行的最小值。