SQL案例分析：環比、同比以及復合增長率
除了我們已經介紹過的聚合窗口函數（AVG、SUM等）和排名窗口函數（ROW_NUMBER、RANK等）之外，還有一類常用的SQL窗口函數：取值窗口函數。
取值窗口函數可以用于返回分析窗口內指定位置的數據記錄，常見的取值窗口函數如下：
- 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月份是第一期，因此環比增長率為空?！敖圩印?018年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年的銷量為10000元，2019年的銷量為12500元，2020年的銷量為15000元。那么這兩年的復合增長率的計算方式如下：
(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 ANDUNBOUNDED 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選項表示按照產品進行分區，ORDERBY選項表示按照銷量從高到低排序。
以上三個函數的默認窗口都是從分區的第一行到當前行，因此我們將窗口擴展到了整個分區。該查詢返回的結果如下：
產品|年月 |銷量 |最高銷量月份|最低銷量月份|第三高月份
---|------|-----|----------|----------|---------
桔子|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()窗口函數，因此無法得到銷量第三高的月份。
