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.514430

2018年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年的销量为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()窗口函数,因此无法得到销量第三高的月份。以上案例来自图书《SQL编程思想》。

发表评论

登录后才能评论
网站客服
网站客服
申请收录 侵权处理
分享本页
返回顶部