了解：SQL案例分析：环比、同比以及复合增长率
除了我们已经介绍过的聚合窗口函数（AVG、SUM等）和排窗口函数（ROW_NUMBER、RANK等）之外，还有一类常用的SQL窗口函数：取值窗口函数。取值窗口函数可以用于返回分析窗口内指定位置的数据记录，常见的取值窗口函数如下：
LAG()函数可以返回窗口内当前行之前的第N行数据。
LEAD()函数可以返回窗口内当前行之后第N行数据。
FIRST_VALUE()函数可以返回窗口内首行数据。
LAST_VALUE()函数可以返回窗口内比较后一行数据。
NTH_VALUE()函数可以返回窗口内第N行数据。
其中，LAG()和LEAD()函数不支持动态的窗口大小，它们以整个分区作为分析的窗口。
环比、同比分析
环比增长是指本期数据与上期数据相比的增长，例如产品2022年6月份的销量与2022年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;
1.
2.
3.
4.
5.
6.
其中，LAG(amount, 1)表示获取上一期的销量，PARTITION BY选项表示按照产品分区，ORDER BY选项表示按照月份进行排序。当前月份的销量amount减去上一期的销量，再除以上一期的销量，就是环比增长率。
该查询返回的结果如下：
复制
产品|年月 |销量 |环比增长率（%）
---|------|--------|------------
桔子|202201|10154.00|
桔子|202202|10183.00| 0.285602
桔子|202203|10245.00| 0.608858
...
香蕉|202204|11408.00| 1.063076
香蕉|202205|11469.00| 0.534712
香蕉|202206|11528.00| 0.514430
1.
2.
3.
4.
5.
6.
7.
8.
9.
2022年1月份是首期，因此环比增长率为空。“桔子”2022年2月份的环比增长率为0.2856%（(10183 - 10154) / 10154 * 100），其他的数据依此类推。
同比增长是指本期数据与上一年度或历史同期相比的增长，例如产品2022年6月份的销量与2022年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;
1.
2.
3.
4.
5.
6.
其中，LAG(amount, 12)表示当前月份之前第12期的销量，也就是去年同月份的销量。PARTITION BY选项表示按照产品分区，ORDER BY选项表示按照月份进行排序。当前月份的销量amount减去去年同期的销量，再除以去年同期的销量，就是同比增长率。
该查询返回的结果如下：
复制
产品|年月 |销量 |同比增长率（%）
---|------|--------|------------
桔子|202201|10154.00|
桔子|202202|10183.00|
桔子|202203|10245.00|
...
桔子|202201|11099.00| 9.306677
桔子|202202|11181.00| 9.800648
桔子|202203|11302.00|10.317228
...
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
2022年的12期数据都没有对应的同比增长率，“桔子”2022年1月份的同比增长率为9.3067（(11099 - 10154) / 10154 * 100），其他的数据依此类推。
提示：LEAD()函数与LAG()函数的使用方法类似，不过它的返回结果是当前行之后的第N行数据。
复合增长率
复合增长率是第N期的数据除以首期的基准数据，然后开N-1次方再减去1得到的结果。
假如2022年的销量为10000元，2022年的销量为12500元，2022年的销量为15000元。那么这两年的复合增长率的计算方式如下：
复制
(15000/10000)(1/2) - 1 = 22.47%
1.
以年度为单位计算的复合增长率称为年均复合增长率，以月度为单位计算的复合增长率称为月均复合增长率。
以下查询统计了自2022年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;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
我们首先定义了一个通用表表达式，其中FIRST_VALUE(amount)返回了首期（202201）的销量，ROW_NUMBER()函数返回了每一期的编号。主查询中的POWER()函数用于执行开方运算，NULLIF()函数用于处理首期数据的除零错误，常量1.0用于避免整数除法导致的精度丢失问题。
该查询返回的结果如下：
复制
产品|年月 |销量 |月均复合增长率（%）
---|------|--------|-----------------
桔子|202201|10154.00|
桔子|202202|10183.00| 0.285602
桔子|202203|10245.00| 0.447100
桔子|202204|10325.00| 0.558233
桔子|202205|10465.00| 0.757067
桔子|202206|10505.00| 0.681987
...
1.
2.
3.
4.
5.
6.
7.
8.
9.
2022年1月份是首期，因此月均销量复合增长率为空。“桔子”2022年2月份的月均销量复合增长率等于它的环比增长率，2022年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;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
个窗口函数的OVER子句相同，PARTITION BY选项表示按照产品进行分区，ORDERBY选项表示按照销量从高到低排序。
以上个函数的默认窗口都是从分区的首行到当前行，因此我们将窗口扩展到了整个分区。该查询返回的结果如下：
复制
产品|年月 |销量 |比较高销量月份|比较低销量月份|第高月份
---|------|-----|----------|----------|---------
桔子|202201|10154|202206 |202201 |202204
桔子|202202|10183|202206 |202201 |202204
桔子|202203|10245|202206 |202201 |202204
桔子|202204|10325|202206 |202201 |202204
桔子|202205|10465|202206 |202201 |202204
桔子|202206|10505|202206 |202201 |202204
...
1.
2.
3.
4.
5.
6.
7.
8.
9.
“桔子”的比较高销量出现在2022年6月份，比较低销量出现在2022年1月份，第高销量出现在2022年4月份。
Microsoft SQL Server目前还不支持NTH_VALUE()窗口函数，因此法得到销量第高的月份。
希望将来大模型书籍能够得到长足稳定的发展，为社会发展及人们的需求做好服务。悦数图数据库是一款完全自主研发的国产图数据库和原生分布式图数据库,具有高性能,易扩展,安全稳定,自主可控的特点.万亿级数据仅需毫秒级查询延时,应用于金融风控,实时推荐,知识图谱等业务场景。https://www.yueshu.com.cn/https://www-cdn.yueshu.cn/yueshu-website/images/New-Pages/caselogo-bangshengkeji.png
页:
[1]