五種主流數(shù)據(jù)庫：窗口函數(shù)
SQL 窗口函數(shù)為在線分析系統(tǒng)（OLAP）和商業(yè)智能（BI）提供了復(fù)雜分析和報(bào)表統(tǒng)計(jì)的功能，例如產(chǎn)品的累計(jì)銷量統(tǒng)計(jì)、分類排名、同比/環(huán)比分析等。這些功能通常很難通過聚合函數(shù)和分組操作來實(shí)現(xiàn)。
本文比較了五種主流數(shù)據(jù)庫實(shí)現(xiàn)的窗口函數(shù)，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。
窗口函數(shù)定義
窗口函數(shù)（Window Function）可以像聚合函數(shù)一樣對(duì)一組數(shù)據(jù)進(jìn)行分析并返回結(jié)果，二者的不同之處在于，窗口函數(shù)不是將一組數(shù)據(jù)匯總成單個(gè)結(jié)果，而是為每一行數(shù)據(jù)都返回一個(gè)分析結(jié)果。聚合函數(shù)和窗口函數(shù)的區(qū)別如下圖所示。
我們以 SUM 函數(shù)為例演示這兩種函數(shù)的差異，以下語句中的 SUM() 是一個(gè)聚合函數(shù)：
SELECT SUM(salary) AS "月薪總和"
FROM employee;以上 SUM 函數(shù)作為聚合函數(shù)使用，表示將所有員工的數(shù)據(jù)匯總成一個(gè)結(jié)果。因此，查詢返回了所有員工的月薪總和：
月薪總和
---------
245800.00以下語句中的 SUM 是一個(gè)窗口函數(shù)：
SELECT emp_name AS "員工姓名",
SUM(salary) OVER () AS "月薪總和"
FROM employee;其中，關(guān)鍵字 OVER 表明 SUM() 是一個(gè)窗口函數(shù)。括號(hào)內(nèi)為空，表示將所有數(shù)據(jù)作為一個(gè)分組進(jìn)行匯總。該查詢返回的結(jié)果如下：
員工姓名|月薪總和
-------|---------
劉備 |245800.00
關(guān)羽 |245800.00
張飛 |245800.00
...以上查詢結(jié)果返回了所有的員工姓名，并且通過聚合函數(shù) SUM() 為每個(gè)員工都返回了相同的匯總結(jié)果。
從以上示例中可以看出，窗口函數(shù)的語法與聚合函數(shù)的不同之處在于，它包含了一個(gè) OVER 子句。OVER 子句用于指定一個(gè)數(shù)據(jù)分析的窗口，完整的窗口函數(shù)定義如下：
window_function ([expression], ...) OVER (
PARTITION BY ...
ORDER BY ...
frame_clause
)其中 window_function 是窗口函數(shù)的名稱，expression 是可選的分析對(duì)象（字段名或者表達(dá)式），OVER 子句包含分區(qū)（PARTITION BY）、排序（ORDER BY）以及窗口大小（frame_clause）3 個(gè)選項(xiàng)。
提示：聚合函數(shù)將同一個(gè)分組內(nèi)的多行數(shù)據(jù)匯總成單個(gè)結(jié)果，窗口函數(shù)則保留了所有的原始數(shù)據(jù)。在某些數(shù)據(jù)庫中，窗口函數(shù)也被稱為聯(lián)機(jī)分析處理（OLAP）函數(shù)，或者分析函數(shù)（Analytic Function）。
創(chuàng)建數(shù)據(jù)分區(qū)
窗口函數(shù) OVER 子句中的 PARTITION BY 選項(xiàng)用于定義分區(qū)，其作用類似于查詢語句中的 GROUP BY 子句。如果我們指定了分區(qū)選項(xiàng)，窗口函數(shù)將會(huì)分別針對(duì)每個(gè)分區(qū)單獨(dú)進(jìn)行分析。
例如，以下語句按照不同部門分別統(tǒng)計(jì)員工的月薪合計(jì)：
SELECT emp_name "員工姓名", salary "月薪", dept_id "部門編號(hào)",
SUM(salary) OVER (
PARTITION BY dept_id
) AS "部門合計(jì)"
FROM employee;其中，PARTITION BY 選項(xiàng)表示按照部門進(jìn)行分區(qū)。查詢返回的結(jié)果如下：
員工姓名|月薪 |部門編號(hào)|部門合計(jì)
-------|--------|-------|--------
劉備 |30000.00| 1|80000.00
關(guān)羽 |26000.00| 1|80000.00
張飛 |24000.00| 1|80000.00
諸葛亮 |24000.00| 2|39500.00
黃忠 | 8000.00| 2|39500.00
魏延 | 7500.00| 2|39500.00
...查詢結(jié)果中的前 3 行數(shù)據(jù)屬于同一個(gè)部門，因此它們對(duì)應(yīng)的部門合計(jì)字段都等于 80000（30000+26000+24000）。其他部門的員工采用同樣的方式進(jìn)行統(tǒng)計(jì)。
提示：在窗口函數(shù) OVER 子句中指定了 PARTITION BY 選項(xiàng)之后，我們無須使用 GROUP BY 子句也能獲得分組統(tǒng)計(jì)結(jié)果。如果不指定 PARTITION BY 選項(xiàng)，表示將全部數(shù)據(jù)作為一個(gè)整體進(jìn)行分析。
分區(qū)內(nèi)的排序
窗口函數(shù) OVER 子句中的 ORDER BY 選項(xiàng)用于指定分區(qū)內(nèi)數(shù)據(jù)的排序方式，作用類似于查詢語句中的 ORDER BY 子句。
排序選項(xiàng)通常用于數(shù)據(jù)的分類排名。例如，以下語句用于分析員工在部門內(nèi)的月薪排名：
SELECT emp_name "姓名", salary "月薪", dept_id "部門編號(hào)",
RANK() OVER (
PARTITION BY dept_id
ORDER BY salary DESC
) AS "部門排名"
FROM employee;其中，RANK 函數(shù)用于計(jì)算數(shù)據(jù)的名次，PARTITION BY 選項(xiàng)表示按照部門進(jìn)行分區(qū)，ORDER BY 選項(xiàng)表示在部門內(nèi)按照月薪從高到低進(jìn)行排序。查詢返回的結(jié)果如下：
姓名 |月薪 |部門編號(hào)|部門排名
------|--------|-------|-------
劉備 |30000.00| 1| 1
關(guān)羽 |26000.00| 1| 2
張飛 |24000.00| 1| 3
諸葛亮|24000.00| 2| 1
黃忠 | 8000.00| 2| 2
魏延 | 7500.00| 2| 3
...查詢結(jié)果中的前 3 行數(shù)據(jù)屬于同一個(gè)部門：“劉備”的月薪最高，在部門內(nèi)排名第 1；“關(guān)羽”排名第 2；“張飛”排名第 3。其他部門的員工采用同樣的方式進(jìn)行排名。
提示：窗口函數(shù) OVER 子句中的 ORDER BY 選項(xiàng)和查詢語句中的 ORDER BY 子句的使用方法相同。因此，對(duì)于 Oracle、PostgreSQL 以及 SQlite，我們也可以使用 NULLS FIRST 或者 NULLS LAST 選項(xiàng)指定空值的排序位置。
指定窗口大小
窗口函數(shù) OVER 子句中的 frame_clause 選項(xiàng)用于指定一個(gè)移動(dòng)的分析窗口，窗口總是位于分區(qū)的范圍之內(nèi)，是分區(qū)的一個(gè)子集。在指定了分析窗口之后，窗口函數(shù)不再基于分區(qū)進(jìn)行分析，而是基于窗口內(nèi)的數(shù)據(jù)進(jìn)行分析。
窗口選項(xiàng)可以用于實(shí)現(xiàn)各種復(fù)雜的分析功能，例如計(jì)算累計(jì)到當(dāng)前日期為止的銷量總和，每個(gè)月及其前后各 N 個(gè)月的平均銷量等。
指定窗口大小的具體選項(xiàng)如下：
{ ROWS | RANGE } frame_start
{ ROWS | RANGE } BETWEEN frame_start AND frame_end其中，ROWS 表示以數(shù)據(jù)行為單位計(jì)算窗口的偏移量，RANGE 表示以數(shù)值（例如 10 天、5 千米等）為單位計(jì)算窗口的偏移量。
提示：除了 ROWS 和 RANGE 之外，Oracle、PostgreSQL 以及 SQLite 還支持 GROUPS 類型的窗口大小，數(shù)值相等的數(shù)據(jù)行都屬于一個(gè) GROUP。
frame_start 選項(xiàng)用于定義窗口的起始位置，可以指定以下內(nèi)容之一：
- UNBOUNDED PRECEDING，表示窗口從分區(qū)的第一行開始。
- N PRECEDING，表示窗口從當(dāng)前行之前的第 N 行開始。
- CURRENT ROW，表示窗口從當(dāng)前行開始。
frame_end 選項(xiàng)用于定義窗口的結(jié)束位置，可以指定以下內(nèi)容之一：
- CURRENT ROW，表示窗口到當(dāng)前行結(jié)束。
- N FOLLOWING，表示窗口到當(dāng)前行之后的第 N 行結(jié)束。
- UNBOUNDED FOLLOWING，表示窗口到分區(qū)的最后一行結(jié)束。
下圖說明了這些窗口大小選項(xiàng)的含義。
隨著窗口函數(shù)對(duì)每一行數(shù)據(jù)的分析，圖中的 CURRENT ROW 代表了當(dāng)前正在處理的數(shù)據(jù)行，其他的數(shù)據(jù)行則可以通過它們相對(duì)于當(dāng)前行的位置進(jìn)行表示。例如，以下窗口選項(xiàng)：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示分析窗口從當(dāng)前分區(qū)的第一行開始，直到當(dāng)前行結(jié)束。
分析窗口的大小不會(huì)超出當(dāng)前分區(qū)的范圍，每個(gè)窗口函數(shù)支持的窗口大小選項(xiàng)不同，我們將會(huì)在下面的案例分析中分別進(jìn)行介紹。
窗口函數(shù)分類
常見的 SQL 窗口函數(shù)可以分為以下幾類：
- 聚合窗口函數(shù)（Aggregate Window Function）。許多常見的聚合函數(shù)也可以作為窗口函數(shù)使用，包括 AVG()、SUM()、COUNT()、MAX() 以及 MIN() 等。
- 排名窗口函數(shù)（Ranking Window Function）。排名窗口函數(shù)用于對(duì)數(shù)據(jù)進(jìn)行分組排名，包括 ROW_NUMBER()、RANK()、DENSE_RANK()、PERCENT_RANK()、CUME_DIST() 以及 NTILE() 等函數(shù)。
- 取值窗口函數(shù)（Value Window Function）。取值窗口函數(shù)用于返回指定位置上的數(shù)據(jù)行，包括 FIRST_VALUE()、LAST_VALUE()、LAG()、LEAD()、NTH_VALUE() 等函數(shù)。
接下來我們將會(huì)使用兩個(gè)示例表，其中 sales_monthly 表中存儲(chǔ)了不同產(chǎn)品（蘋果、香蕉、桔子）每個(gè)月的銷量情況，以下是該表中的部分?jǐn)?shù)據(jù)：
product|ym |amount
-------|------|--------
蘋果 |201801|10159.00
蘋果 |201802|10211.00
蘋果 |201803|10247.00
蘋果 |201804|10376.00
蘋果 |201805|10400.00
蘋果 |201806|10565.00
...transfer_log 表中記錄了一些銀行賬號(hào)的交易日志，以下是該表中的部分?jǐn)?shù)據(jù)：
log_id|log_ts |from_user |to_user |type|amount
------|-------------------|--------------|--------------|----|------
1|2019-01-02 10:31:40|62221234567890| |存款 | 50000
2|2019-01-02 10:32:15|62221234567890| |存款 |100000
3|2019-01-03 08:14:29|62221234567890|62226666666666|轉(zhuǎn)賬 |200000
4|2019-01-05 13:55:38|62221234567890|62226666666666|轉(zhuǎn)賬 |150000
5|2019-01-07 20:00:31|62221234567890|62227777777777|轉(zhuǎn)賬 |300000
6|2019-01-09 17:28:07|62221234567890|62227777777777|轉(zhuǎn)賬 |500000
...該表中的字段分別表示交易日志編號(hào)、交易時(shí)間、交易發(fā)起賬號(hào)、交易接收賬號(hào)、交易類型以及交易金額。
聚合窗口函數(shù)
案例分析：移動(dòng)平均值
AVG 函數(shù)在作為窗口函數(shù)使用時(shí)，可以用于計(jì)算隨著當(dāng)前行移動(dòng)的窗口內(nèi)數(shù)據(jù)行的平均值。例如，以下語句用于查找不同產(chǎn)品截至每個(gè)月、最近 3 個(gè)月的平均銷量：
SELECT product AS "產(chǎn)品", ym "年月", amount "銷量",
AVG(amount) OVER (
PARTITION BY product
ORDER BY ym
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS "最近平均銷量"
FROM sales_monthly
ORDER BY product, ym;AVG 函數(shù) OVER 子句中的 PARTITION BY 選項(xiàng)表示按照產(chǎn)品進(jìn)行分區(qū)；ORDER BY 選項(xiàng)表示按照月份進(jìn)行排序；ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 表示窗口從當(dāng)前行的前 2 行開始，直到當(dāng)前行結(jié)束。該查詢返回的結(jié)果如下：
產(chǎn)品|年月 |銷量 |最近平均銷量
----|------|--------|------------
桔子|201801|10154.00|10154.000000
桔子|201802|10183.00|10168.500000
桔子|201803|10245.00|10194.000000
桔子|201804|10325.00|10251.000000
桔子|201805|10465.00|10345.000000
桔子|201806|10505.00|10431.666667
...對(duì)于“桔子”，第一個(gè)月的分析窗口只有 1 行數(shù)據(jù)，因此平均銷量為“10154”。第二個(gè)月的分析窗口為第 1 行和第 2 行數(shù)據(jù)，因此平均銷量為“10168.5”（(10154+10183)/2）。第三個(gè)月的分析窗口為第 1 行到第 3 行數(shù)據(jù)，因此平均銷量為“10194”（(10154+10183+10245)/3）。依此類推，直到計(jì)算完“桔子”所有月份的平均銷量，然后開始計(jì)算其他產(chǎn)品的平均銷量。
案例分析：累計(jì)求和
SUM 函數(shù)作為窗口函數(shù)時(shí)，可以用于統(tǒng)計(jì)指定窗口內(nèi)的累計(jì)值。例如，以下語句用于查找不同產(chǎn)品截至當(dāng)前月份的累計(jì)銷量：
SELECT product AS "產(chǎn)品", ym "年月", amount "銷量",
SUM(amount) OVER (
PARTITION BY product
ORDER BY ym
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS "累計(jì)銷量"
FROM sales_monthly
ORDER BY product, ym;SUM 函數(shù) OVER 子句中的 PARTITION BY 選項(xiàng)表示按照產(chǎn)品進(jìn)行分區(qū)；ORDER BY 選項(xiàng)表示按照月份進(jìn)行排序；ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示窗口從當(dāng)前分區(qū)第 1 行開始，直到當(dāng)前行結(jié)束。該查詢返回的結(jié)果如下：
產(chǎn)品|年月 |銷量 |累計(jì)銷量
----|------|--------|---------
桔子|201801|10154.00| 10154.00
桔子|201802|10183.00| 20337.00
桔子|201803|10245.00| 30582.00
桔子|201804|10325.00| 40907.00
桔子|201805|10465.00| 51372.00
桔子|201806|10505.00| 61877.00
...對(duì)于“桔子”，第一個(gè)月的分析窗口只有 1 行數(shù)據(jù)，因此累計(jì)銷量為“10154”。第二個(gè)月的分析窗口為第 1 行和第 2 行數(shù)據(jù)，因此累計(jì)銷量為“20337”（10154+10183）。第三個(gè)月的分析窗口為第 1 行到第 3 行數(shù)據(jù)，因此累計(jì)銷量為“30582”（10154+10183+10245）。依此類推，直到計(jì)算完“桔子”所有月份的累計(jì)銷量，然后開始計(jì)算其他產(chǎn)品的累計(jì)銷量。
提示：對(duì)于聚合窗口函數(shù)，如果我們沒有指定 ORDER BY 選項(xiàng)，默認(rèn)的窗口大小就是整個(gè)分區(qū)。如果我們指定了 ORDER BY 選項(xiàng)，默認(rèn)的窗口大小就是分區(qū)的第一行到當(dāng)前行。因此，以上示例語句中的 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 選項(xiàng)可以省略。
除使用 ROWS 關(guān)鍵字以數(shù)據(jù)行為單位指定窗口的偏移量外，我們也可以使用 RANGE 關(guān)鍵字以數(shù)值為單位指定窗口的偏移量。例如，以下語句用于查找短期之內(nèi)（5 天）累計(jì)轉(zhuǎn)賬超過100 萬元的賬號(hào)：
-- Oracle、MySQL 以及 PostgreSQL
SELECT log_ts, from_user, total_amount
FROM (
SELECT log_ts, from_user,
SUM(amount) OVER (
PARTITION BY from_user
ORDER BY log_ts
RANGE INTERVAL '5' DAY PRECEDING
) AS total_amount
FROM transfer_log
WHERE TYPE = '轉(zhuǎn)賬'
) t
WHERE total_amount >= 1000000;其中，SUM 函數(shù) OVER 子句中的 RANGE 選項(xiàng)指定了一個(gè) 5 天之內(nèi)的時(shí)間窗口。該查詢返回的結(jié)果如下：
log_ts |from_user |total_amount
-------------------|--------------|------------
2021-01-10 07:46:02|62221234567890| 1050000截至 2021 年 1 月 10 日 7 時(shí) 46 分 02 秒，賬號(hào)“62221234567890”在最近 5 天之內(nèi)累計(jì)轉(zhuǎn)賬 105 萬元。
SQLite 不支持 INTERVAL 時(shí)間常量，我們可以將時(shí)間戳數(shù)據(jù)轉(zhuǎn)換為整數(shù)后使用，例如：
-- SQLite
WITH tl(log_ts, unix, from_user, amount) AS (
SELECT log_ts, CAST(STRFTIME('%s', log_ts) AS INT), from_user, amount
FROM transfer_log
WHERE type = '轉(zhuǎn)賬'
)
SELECT log_ts, from_user, total_amount
FROM (
SELECT log_ts, from_user,
SUM(amount) OVER (
PARTITION BY from_user
ORDER BY unix
RANGE 5 * 86400 PRECEDING
) AS total_amount
FROM tl
) t
WHERE total_amount >= 1000000;我們首先定義了一個(gè) CTE，字段 unix 表示將 log_ts 轉(zhuǎn)換為 1970 年 1 月 1 日以來的整數(shù)秒。然后我們?cè)?SUM 函數(shù)中通過 RANGE 選項(xiàng)指定了一個(gè) 5 天（5*86 400 秒）之內(nèi)的時(shí)間窗口。
Microsoft SQL Server 中的 RANGE 窗口大小選項(xiàng)只能指定 UNBOUNDED PRECEDING、UNBOUNDED FOLLOWING 或者 CURRENT ROW，不能指定一個(gè)具體的數(shù)值，因此無法實(shí)現(xiàn)以上查詢。
排名窗口函數(shù)
排名窗口函數(shù)可以用來獲取數(shù)據(jù)的分類排名。常見的排名窗口函數(shù)如下：
- ROW_NUMBER 函數(shù)可以為分區(qū)中的每行數(shù)據(jù)分配一個(gè)序列號(hào)，序列號(hào)從 1 開始。
- RANK 函數(shù)返回當(dāng)前行在分區(qū)中的名次。如果存在名次相同的數(shù)據(jù)，后續(xù)的排名將會(huì)產(chǎn)生跳躍。
- DENSE_RANK 函數(shù)返回當(dāng)前行在分區(qū)中的名次。即使存在名次相同的數(shù)據(jù)，后續(xù)的排名也是連續(xù)值。
- PERCENT_RANK 函數(shù)以百分比的形式返回當(dāng)前行在分區(qū)中的名次。如果存在名次相同的數(shù)據(jù)，后續(xù)的排名將會(huì)產(chǎn)生跳躍。
- CUME_DIST 函數(shù)計(jì)算當(dāng)前行在分區(qū)內(nèi)的累積分布。
- NTILE 函數(shù)將分區(qū)內(nèi)的數(shù)據(jù)分為 N 等份，并返回當(dāng)前行所在的分片位置。
排名窗口函數(shù)不支持動(dòng)態(tài)的窗口大小選項(xiàng)，而是以整個(gè)分區(qū)作為分析的窗口。
案例分析：分類排名
以下查詢使用 4 個(gè)不同的排名函數(shù)計(jì)算每個(gè)員工在其部門內(nèi)的月薪排名：
SELECT d.dept_name AS "部門名稱", e.emp_name AS "姓名", e.salary AS "月薪",
ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "row_number",
RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "rank",
DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "dense_rank",
PERCENT_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "percent_rank"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id);其中，4 個(gè)窗口函數(shù)的 OVER 子句完全相同，PARTITION BY 表示按照部門進(jìn)行分區(qū)，ORDER BY 表示按照月薪從高到低進(jìn)行排序。該查詢返回的結(jié)果如下：
部門名稱 |姓名 |月薪 |row_number|rank|dense_rank|percent_rank
--------|-----|--------|-----------|----|----------|----------------
行政管理部|劉備 |30000.00| 1| 1| 1| 0.0
行政管理部|關(guān)羽 |26000.00| 2| 2| 2| 0.5
行政管理部|張飛 |24000.00| 3| 3| 3| 1.0
...
研發(fā)部 |趙云 |15000.00| 1| 1| 1| 0.0
研發(fā)部 |周倉 | 8000.00| 2| 2| 2| 0.125
研發(fā)部 |關(guān)興 | 7000.00| 3| 3| 3| 0.25
研發(fā)部 |關(guān)平 | 6800.00| 4| 4| 4| 0.375
研發(fā)部 |趙氏 | 6600.00| 5| 5| 5| 0.5
研發(fā)部 |廖化 | 6500.00| 6| 6| 6| 0.625
研發(fā)部 |張苞 | 6500.00| 7| 6| 6| 0.625
研發(fā)部 |趙統(tǒng) | 6000.00| 8| 8| 7| 0.875
...我們以“研發(fā)部”為例，ROW_NUMBER 函數(shù)為每個(gè)員工分配了一個(gè)連續(xù)的數(shù)字編號(hào)，其中“廖化”和“張苞”的月薪相同，但是編號(hào)不同。
RANK 函數(shù)為每個(gè)員工返回了一個(gè)名次，其中“廖化”和“張苞”的名次都是 6，在他們之后“趙統(tǒng)”的名次為 8，產(chǎn)生了跳躍。
DENSE_RANK 函數(shù)為每個(gè)員工返回了一個(gè)名次，其中“廖化”和“張苞”的名次都是 6，在他們之后“趙統(tǒng)”的名次為 7，沒有產(chǎn)生跳躍。
PERCENT_RANK 函數(shù)按照百分比指定名次，取值位于 0 到 1 之間。其中“趙統(tǒng)”的百分比排名為 0.875，產(chǎn)生了跳躍。
提示：我們也可以使用 COUNT()窗口函數(shù)產(chǎn)生和 ROW_NUMBER 函數(shù)相同的結(jié)果，讀者可以自行嘗試。
另外，以上示例中 4 個(gè)窗口函數(shù)的 OVER 子句完全相同。此時(shí)，我們可以采用一種更簡單的寫法：
-- MySQL、Oracle、PostgreSQL 以及 SQLite
SELECT d.dept_name AS "部門名稱", e.emp_name AS "姓名", e.salary AS "月薪",
ROW_NUMBER() OVER w AS "row_number",
RANK() OVER w AS "rank",
DENSE_RANK() OVER w AS "dense_rank",
PERCENT_RANK() OVER w AS "percent_rank"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
WINDOW w AS (PARTITION BY e.dept_id ORDER BY e.salary DESC);我們?cè)诓樵冋Z句的最后使用 WINDOW 子句定義了一個(gè)窗口變量 w，然后在所有窗口函數(shù)的 OVER 子句中使用了該變量。
這種使用窗口變量的寫法可以簡化窗口選項(xiàng)的輸入，目前 Microsoft SQL Server還不支持這種命名窗口語法。
基于排名窗口函數(shù)，我們還可以實(shí)現(xiàn)分類 Top-N 排行榜。例如，以下語句用于查找每個(gè)部門中最早入職的 2 名員工：
WITH ranked_emp AS (
SELECT d.dept_name,
e.emp_name,
e.hire_date,
ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.hire_date) AS rn
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
)
SELECT dept_name "部門名稱", emp_name "姓名", hire_date "入職日期", rn "入職順序"
FROM ranked_emp
WHERE rn <= 2;其中，ranked_emp 是一個(gè)通用表表達(dá)式，包含了員工在其部門內(nèi)的入職順序。然后我們?cè)谥鞑樵冋Z句中返回了每個(gè)部門前 2 名入職的員工：
部門名稱 |姓名 |入職日期 |入職順序
--------|-----|----------|-------
行政管理部|劉備 |2000-01-01| 1
行政管理部|關(guān)羽 |2000-01-01| 2
人力資源部|諸葛亮|2006-03-15| 1
人力資源部|魏延 |2007-04-01| 2
財(cái)務(wù)部 |孫尚香|2002-08-08| 1
財(cái)務(wù)部 |孫丫鬟|2002-08-08| 2
...案例分析：累積分布
CUME_DIST 函數(shù)可以返回當(dāng)前行在分區(qū)內(nèi)的累積分布，也就是排名在當(dāng)前行之前（包含當(dāng)前行）所有數(shù)據(jù)所占的比率，取值范圍為大于 0 且小于或等于 1。
例如，以下查詢返回了所有員工按照月薪排名的累積分布情況：
SELECT emp_name AS "姓名", salary AS "月薪",
CUME_DIST() OVER (ORDER BY salary) AS "累積占比"
FROM employee;其中，OVER 子句沒有指定分區(qū)選項(xiàng)，因此 CUME_DIST 函數(shù)會(huì)將全體員工作為一個(gè)整體進(jìn)行分析。ORDER BY 選項(xiàng)表示按照月薪從低到高進(jìn)行排序。該查詢返回的結(jié)果如下：
姓名 |月薪 |累積占比
----|--------|-------
蔣琬 | 4000.00|0.08
鄧芝 | 4000.00|0.08
龐統(tǒng) | 4100.00|0.12
...
關(guān)羽 |26000.00|0.96
劉備 |30000.00| 1.0結(jié)果顯示 8%（2/25）的員工月薪小于或等于 4000 元；或者也可以說，月薪 4000 元，意味著在公司中的月薪排名屬于最低的 8%。
NTILE 函數(shù)用于將分區(qū)內(nèi)的數(shù)據(jù)分為 N 等份，并計(jì)算當(dāng)前行所在的分片位置。例如，以下語句將員工按照入職先后順序分為 5 組，并計(jì)算每個(gè)員工所在的分組：
SELECT emp_name AS "姓名", hire_date AS "入職日期",
NTILE(5) OVER (ORDER BY hire_date) AS "分組位置"
FROM employee;其中，OVER 子句沒有指定分區(qū)選項(xiàng)，因此 NTILE 函數(shù)會(huì)將全體員工作為一個(gè)整體進(jìn)行分析。ORDER BY 選項(xiàng)表示按照入職先后進(jìn)行排序。該查詢返回的結(jié)果如下：
姓名 |入職日期 |分組位置
-----|----------|-------
劉備 |2000-01-01| 1
關(guān)羽 |2000-01-01| 1
張飛 |2000-01-01| 1
孫尚香|2002-08-08| 1
孫丫鬟|2002-08-08| 1
趙云 |2005-12-19| 2
...
簡雍 |2019-05-11| 5分組位置為 1 的是最早入職的 20% 員工，分組位置為 5 的是最晚入職的 20% 員工。
取值窗口函數(shù)
取值窗口函數(shù)可以用來返回窗口內(nèi)指定位置的數(shù)據(jù)行。常見的取值窗口函數(shù)如下：
- LAG 函數(shù)可以返回窗口內(nèi)當(dāng)前行之前的第 N 行數(shù)據(jù)。
- LEAD 函數(shù)可以返回窗口內(nèi)當(dāng)前行之后的第 N 行數(shù)據(jù)。
- FIRST_VALUE 函數(shù)可以返回窗口內(nèi)第一行數(shù)據(jù)。
- LAST_VALUE 函數(shù)可以返回窗口內(nèi)最后一行數(shù)據(jù)。
- NTH_VALUE 函數(shù)可以返回窗口內(nèi)第 N 行數(shù)據(jù)。
其中，LAG 和 LEAD 函數(shù)不支持動(dòng)態(tài)的窗口大小，它們以整個(gè)分區(qū)作為分析的窗口。
案例分析：環(huán)比、同比分析
環(huán)比增長指的是本期數(shù)據(jù)與上期數(shù)據(jù)相比的增長，例如，產(chǎn)品 2019 年 6 月的銷量與 2019\ 年 5 月的銷量相比增加的部分。以下語句統(tǒng)計(jì)了各種產(chǎn)品每個(gè)月的環(huán)比增長率：
SELECT product AS "產(chǎn)品", ym "年月", amount "銷量",
((amount - LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym))/ LAG(amount, 1) OVER (PARTITION BY product ORDER BY ym)) * 100 AS "環(huán)比增長率（%）"
FROM sales_monthly
ORDER BY product, ym;其中，LAG(amount, 1) 表示獲取上一期的銷量，PARTITION BY 選項(xiàng)表示按照產(chǎn)品分區(qū)，ORDER BY 選項(xiàng)表示按照月份進(jìn)行排序。當(dāng)前月份的銷量 amount 減去上一期的銷量，再除以上一期的銷量，就是環(huán)比增長率。該查詢返回的結(jié)果如下：
產(chǎn)品|年月 |銷量 |環(huán)比增長率（%）
---|------|--------|------------
桔子|201801|10154.00|
桔子|201802|10183.00| 0.285602
桔子|201803|10245.00| 0.608858
...
香蕉|201904|11408.00| 1.063076
香蕉|201905|11469.00| 0.534712
香蕉|201906|11528.00| 0.5144302018 年 1 月是第一期，因此其環(huán)比增長率為空。2018 年 2 月“桔子”的環(huán)比增長率為 0.2856%（(10183 - 10154) / 10154×100），依此類推。
同比增長指的是本期數(shù)據(jù)與上一年度或歷史同期相比的增長，例如，產(chǎn)品 2019 年 6 月的銷量與 2018 年 6 月的銷量相比增加的部分。以下語句統(tǒng)計(jì)了各種產(chǎn)品每個(gè)月的同比增長率：
SELECT product AS "產(chǎn)品", ym "年月", amount "銷量",
((amount - LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym))/ LAG(amount, 12) OVER (PARTITION BY product ORDER BY ym)) * 100 AS "同比增長率（%）"
FROM sales_monthly
ORDER BY product, ym;其中，LAG(amount, 12)表示當(dāng)前月份之前第 12 期的銷量，也就是去年同月份的銷量。PARTITION BY 選項(xiàng)表示按照產(chǎn)品分區(qū)，ORDER BY 選項(xiàng)表示按照月份進(jìn)行排序。當(dāng)前月份的銷量 amount 減去去年同期的銷量，再除以去年同期的銷量，就是同比增長率。該查詢返回的結(jié)果如下：
產(chǎn)品|年月 |銷量 |同比增長率（%）
---|------|--------|------------
桔子|201801|10154.00|
桔子|201802|10183.00|
桔子|201803|10245.00|
...
桔子|201901|11099.00| 9.306677
桔子|201902|11181.00| 9.800648
桔子|201903|11302.00|10.317228
...2018 年的 12 期數(shù)據(jù)都沒有對(duì)應(yīng)的同比增長率，“桔子”2019 年 1 月的同比增長率為 9.3067%
（(11099 - 10154) / 10154×100），依此類推。
提示：LEAD 函數(shù)與 LAG 函數(shù)的使用方法類似，不過它的返回結(jié)果是當(dāng)前行之后的第 N 行數(shù)據(jù)。
案例分析：復(fù)合增長率
復(fù)合增長率是第 N 期的數(shù)據(jù)除以第一期的基準(zhǔn)數(shù)據(jù)，然后開 N-1 次方再減去 1 得到的結(jié)果。假如 2018 年的產(chǎn)品銷量為 10 000，2019 年的產(chǎn)品銷量為 12 500，2020 年的產(chǎn)品銷量為 15 000（銷量單位省略，下同）。那么這兩年的復(fù)合增長率的計(jì)算方式如下：
(15000/10000)(1/2) - 1 = 22.47%以年度為單位計(jì)算的復(fù)合增長率被稱為年均復(fù)合增長率，以月度為單位計(jì)算的復(fù)合增長率被稱為月均復(fù)合增長率。以下查詢統(tǒng)計(jì)了自 2018 年 1 月以來不同產(chǎn)品的月均銷量復(fù)合增長率：
WITH s(product, ym, amount, first_amount, num) AS (
SELECT product, ym, amount,
FIRST_VALUE(amount) OVER (PARTITION BY product ORDER BY ym),
ROW_NUMBER() OVER (PARTITION BY product ORDER BY ym)
FROM sales_monthly
)
SELECT product AS "產(chǎn)品", ym "年月", amount "銷量",
(POWER(1.0*amount/first_amount, 1.0/NULLIF(num-1, 0)) - 1) * 100 AS "月均復(fù)合增長率（%）"
FROM s
ORDER BY product, ym;我們首先定義了一個(gè)通用表表達(dá)式，其中 FIRST_VALUE(amount)返回了第一期（201801）的銷量，ROW_NUMBER 函數(shù)返回了每一期的編號(hào)。主查詢中的 POWER 函數(shù)用于執(zhí)行開方運(yùn)算，NULLIF 函數(shù)用于處理第一期數(shù)據(jù)的除零錯(cuò)誤，常量 1.0 用于避免由整數(shù)除法所導(dǎo)致的精度丟失問題。該查詢返回的結(jié)果如下：
產(chǎn)品|年月 |銷量 |月均復(fù)合增長率（%）
---|------|--------|-----------------
桔子|201801|10154.00|
桔子|201802|10183.00| 0.285602
桔子|201803|10245.00| 0.447100
桔子|201804|10325.00| 0.558233
桔子|201805|10465.00| 0.757067
桔子|201806|10505.00| 0.681987
...2018 年 1 月是第一期，因此其產(chǎn)品月均銷量復(fù)合增長率為空?！敖圩印?018 年 2 月的月均銷量復(fù)合增長率等于它的環(huán)比增長率，2018 年 3 月的月均銷量復(fù)合增長率等于 0.4471%，依此類推。
以下語句統(tǒng)計(jì)了不同產(chǎn)品最低銷量、最高銷量以及第三高銷量所在的月份：
SELECT product AS "產(chǎn)品", ym "年月", amount "銷量",
FIRST_VALUE(ym) OVER (
PARTITION BY product ORDER BY amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "最高銷量月份",
LAST_VALUE(ym) OVER (
PARTITION BY product ORDER BY amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "最低銷量月份",
-- Microsoft SQL Server 不支持 NTH_VALUE
NTH_VALUE(ym, 3) OVER (
PARTITION BY product ORDER BY amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "第三高月份"
FROM sales_monthly
ORDER BY product, ym;三個(gè)窗口函數(shù)的OVER子句相同，PARTITION BY選項(xiàng)表示按照產(chǎn)品進(jìn)行分區(qū)，ORDER BY 選項(xiàng)表示按照銷量從高到低排序。以上三個(gè)函數(shù)的默認(rèn)窗口都是從分區(qū)的第一行到當(dāng)前行，因此我們將窗口擴(kuò)展到了整個(gè)分區(qū)。該查詢返回的結(jié)果如下：
產(chǎn)品|年月 |銷量 |最高銷量月份|最低銷量月份|第三高月份
---|------|-----|----------|----------|---------
桔子|201801|10154|201906 |201801 |201904
桔子|201802|10183|201906 |201801 |201904
桔子|201803|10245|201906 |201801 |201904
桔子|201804|10325|201906 |201801 |201904
桔子|201805|10465|201906 |201801 |201904
桔子|201806|10505|201906 |201801 |201904
...“桔子”的最高銷量出現(xiàn)在 2019 年 6 月，最低銷量出現(xiàn)在 2018 年 1 月，第三高銷量出現(xiàn)在 2019 年 4 月。
Microsoft SQL Server 目前還不支持 NTH_VALUE() 窗口函數(shù)，因此無法得到銷量第三高的月份。
