除了我們已經(jīng)介紹過(guò)的聚合窗口函數(shù)（AVG、SUM等）和排名窗口函數(shù)（ROW_NUMBER、RANK等）之外，還有一類常用的SQL窗口函數(shù)：取值窗口函數(shù)。

取值窗口函數(shù)可以用于返回分析窗口內(nèi)指定位置的數(shù)據(jù)記錄，常見(jiàn)的取值窗口函數(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)比增長(zhǎng)是指本期數(shù)據(jù)與上期數(shù)據(jù)相比的增長(zhǎng)，例如產(chǎn)品2019年6月份的銷量與2019年5月份的銷量相比增加的部分。

以下語(yǔ)句統(tǒng)計(jì)了各種產(chǎn)品每個(gè)月份的環(huán)比增長(zhǎng)率：

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)比增長(zhǎng)率（%）"
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)比增長(zhǎng)率。

該查詢返回的結(jié)果如下：

產(chǎn)品|年月   |銷量     |環(huán)比增長(zhǎng)率（%）
---|------|--------|------------
桔子|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.514430

2018年1月份是第一期，因此環(huán)比增長(zhǎng)率為空。“桔子”2018年2月份的環(huán)比增長(zhǎng)率為0.2856%（(10183 - 10154) / 10154 * 100），其他的數(shù)據(jù)依此類推。

同比增長(zhǎng)是指本期數(shù)據(jù)與上一年度或歷史同期相比的增長(zhǎng)，例如產(chǎn)品2019年6月份的銷量與2018年6月份的銷量相比增加的部分。

以下語(yǔ)句統(tǒng)計(jì)了各種產(chǎn)品每個(gè)月份的同比增長(zhǎng)率：

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 "同比增長(zhǎng)率（%）"
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減去去年同期的銷量，再除以去年同期的銷量，就是同比增長(zhǎng)率。

該查詢返回的結(jié)果如下：

產(chǎn)品|年月   |銷量     |同比增長(zhǎng)率（%）
---|------|--------|------------
桔子|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ù)都沒(méi)有對(duì)應(yīng)的同比增長(zhǎng)率，“桔子”2019年1月份的同比增長(zhǎng)率為9.3067（(11099 - 10154) / 10154 * 100），其他的數(shù)據(jù)依此類推。

提示：LEAD()函數(shù)與LAG()函數(shù)的使用方法類似，不過(guò)它的返回結(jié)果是當(dāng)前行之后的第N行數(shù)據(jù)。

復(fù)合增長(zhǎng)率

復(fù)合增長(zhǎng)率是第N期的數(shù)據(jù)除以第一期的基準(zhǔn)數(shù)據(jù)，然后開(kāi)N-1次方再減去1得到的結(jié)果。

假如2018年的銷量為10000元，2019年的銷量為12500元，2020年的銷量為15000元。那么這兩年的復(fù)合增長(zhǎng)率的計(jì)算方式如下：

(15000/10000)(1/2) - 1 = 22.47%

以年度為單位計(jì)算的復(fù)合增長(zhǎng)率稱為年均復(fù)合增長(zhǎng)率，以月度為單位計(jì)算的復(fù)合增長(zhǎng)率稱為月均復(fù)合增長(zhǎng)率。

以下查詢統(tǒng)計(jì)了自2018年1月以來(lái)不同產(chǎn)品的月均銷量復(fù)合增長(zhǎng)率：

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ù)合增長(zhǎng)率（%）"
FROM s
ORDER BY product, ym;

我們首先定義了一個(gè)通用表表達(dá)式，其中FIRST_VALUE(amount)返回了第一期（201801）的銷量，ROW_NUMBER()函數(shù)返回了每一期的編號(hào)。主查詢中的POWER()函數(shù)用于執(zhí)行開(kāi)方運(yùn)算，NULLIF()函數(shù)用于處理第一期數(shù)據(jù)的除零錯(cuò)誤，常量1.0用于避免整數(shù)除法導(dǎo)致的精度丟失問(wèn)題。

該查詢返回的結(jié)果如下：

產(chǎn)品|年月   |銷量     |月均復(fù)合增長(zhǎng)率（%）
---|------|--------|-----------------
桔子|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月份是第一期，因此月均銷量復(fù)合增長(zhǎng)率為空?！敖圩印?018年2月份的月均銷量復(fù)合增長(zhǎng)率等于它的環(huán)比增長(zhǎng)率，2018年3月份的月均銷量復(fù)合增長(zhǎng)率等于0.4471%。其他的數(shù)據(jù)依此類推。

以下語(yǔ)句統(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 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;

三個(gè)窗口函數(shù)的OVER子句相同，PARTITION BY選項(xiàng)表示按照產(chǎn)品進(jìn)行分區(qū)，ORDERBY選項(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ù)，因此無(wú)法得到銷量第三高的月份。