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