MySQL 8.0 利用窗口函数计算同比环比
我们在业务中常常需要统计这个月销售量多少,同比增加多少,环比增加多少。这篇博文我们就看看如何利用窗口函数实现同比及环比的计算。
用到的关键字包括:lag, window
准备工作
首先我们得有MySQL 8.0及以上版本, 然后我们准备一张统计表。
1 | CREATE TABLE `my_stat` ( |
表里的内容如下:
month | profit |
---|---|
2022-05 | 10.00 |
2022-06 | 12.00 |
2022-07 | 6.00 |
2022-08 | 30.00 |
2022-09 | 20.00 |
2022-10 | 8.00 |
2022-11 | 2.00 |
2022-12 | 4.00 |
2023-01 | 14.00 |
2023-02 | 10.00 |
2023-03 | 8.00 |
2023-04 | 9.00 |
2023-05 | 7.00 |
2023-06 | 10.00 |
2023-07 | 15.00 |
数据 插入的SQL
1 | INSERT INTO `my_stat` (`month`, `profit`) |
环比计算
先看最终SQL及输出结果
1 | select `month`,profit, |
输出结果:
1 | month|profit|上月|环比|环比比例 |
lag
lag 函数的完整定义如下:
1 | LAG(expr [, N[, default]]) [null_treatment] over_clause |
参数一 expr(表达式)是必须的,这里可以是字段名,如上面的示例SQL,也可以是其它运算表达式。
参数二 N 是可选的,必须是大于等于0的整数。默认1,1表示上一行,0表示当前行,12表示前12行,后面计算同比时会用到
参数三 是默认值,比如第一行的 上一行是不存在的,这个时候 返回什么值就可以通过参数三来控制,默认是 NULL
null_treatment 的定义是处理NULL的策略,但是因为MySQL只实现了 RESPECT NULLS 并且作为默认值,就感觉它只是提醒我们 计算结果需要考虑NULL
over_clause 是必须的,它描述窗口的定义, 如上面的SQL示例最后一行:
1 | WINDOW w AS (ORDER BY `month`); |
这里定义了一个 窗口(window), 基于 month这个字段升序。
window
上一节的 over_clause 定义如下:
1 | over_clause: |
我们看到,可以 over (窗口定义) 或者 over 窗口名称。
1 | lag(profit) OVER w AS 上月 |
这就是一个 over 窗口名称的示例。
接下来我们看 window_spec 的定义:
1 | window_spec: |
- window_name 就是给窗口起个名字,方便使用
- partition_clause 分区定义, 就是定义查询结果如何分组,有点类似group by 的意思。
- order_clause 排序定义,定义窗口内容排序字段及方式,如前面示例里的 order by `month` 。如果省略了order by , 那就按查询到结果的顺序来排序。
- frame_clause frame_clause(框架子句)指定如何定义子集
同比环比计算
还是先看最终SQL
1 | select `month`,profit, |
返回结果
month | profit | 上月 | 环比 | 环比比例 | 上年同月 | 同比 | 同比比例 |
---|---|---|---|---|---|---|---|
2022-05 | 10.00 | NULL | NULL | NULL | NULL | NULL | NULL |
2022-06 | 12.00 | 10.00 | 2.00 | 20.000000 | NULL | NULL | NULL |
2022-07 | 6.00 | 12.00 | -6.00 | -50.000000 | NULL | NULL | NULL |
2022-08 | 30.00 | 6.00 | 24.00 | 400.000000 | NULL | NULL | NULL |
2022-09 | 20.00 | 30.00 | -10.00 | -33.333333 | NULL | NULL | NULL |
2022-10 | 8.00 | 20.00 | -12.00 | -60.000000 | NULL | NULL | NULL |
2022-11 | 2.00 | 8.00 | -6.00 | -75.000000 | NULL | NULL | NULL |
2022-12 | 4.00 | 2.00 | 2.00 | 100.000000 | NULL | NULL | NULL |
2023-01 | 14.00 | 4.00 | 10.00 | 250.000000 | NULL | NULL | NULL |
2023-02 | 10.00 | 14.00 | -4.00 | -28.571429 | NULL | NULL | NULL |
2023-03 | 8.00 | 10.00 | -2.00 | -20.000000 | NULL | NULL | NULL |
2023-04 | 9.00 | 8.00 | 1.00 | 12.500000 | NULL | NULL | NULL |
2023-05 | 7.00 | 9.00 | -2.00 | -22.222222 | 10.00 | -3.00 | -30.000000 |
2023-06 | 10.00 | 7.00 | 3.00 | 42.857143 | 12.00 | -2.00 | -16.666667 |
2023-07 | 15.00 | 10.00 | 5.00 | 50.000000 | 6.00 | 9.00 | 150.000000 |
同比与环比的差别其实不大,只是 lag(profit)变成了 lag(profit,12)。 lag(profit,12)表示取前12行的数据。
需要注意的是计算同比的时候,我们需要保证数据是连续的,不然数据会有偏差,因为这里的lag(profit,12)取的是前12行的数据,如果月份数据有缺失就可以取错数据。比如:如果少了一个月,那么前12行取的可能就是去年上个月的数据了。
partition_clause 聊聊分区
我们先准备一下数据,先创建一张表 my_stat_food
1 | CREATE TABLE `my_stat_food` ( |
然后插入数据
1 | INSERT INTO `my_stat_food` (`month`, `type`, `profit`) |
定义
1 | partition_clause: |
然后我们试试pratition的效果
1 | select *,lag(`profit`) over (partition by `type`) as lp from my_stat_food |
输出:
month | type | profit | lp |
---|---|---|---|
2023-05 | 水果 | 2.00 | NULL |
2023-06 | 水果 | 5.00 | 2.00 |
2023-07 | 水果 | 8.00 | 5.00 |
2023-05 | 肉类 | 3.00 | NULL |
2023-06 | 肉类 | 6.00 | 3.00 |
2023-07 | 肉类 | 9.00 | 6.00 |
2023-05 | 蔬菜 | 1.00 | NULL |
2023-06 | 蔬菜 | 4.00 | 1.00 |
2023-07 | 蔬菜 | 7.00 | 4.00 |
我们的数据是按月分排序的,但我们看到通过 partition进行分区后,就会分区来返回内容了。这样我们就可以通过分区来统计不同品类的环比。
问题
- 上面的给数据都是按月统计好的数据。但原始数据往往是一笔笔订单,那么如何通过原始数据生成每个月的统计数据呢?
- 如何保证数据的连续性?在业务上每个月的数据往往是有保证的,毕竟一个月一单不成交的话,也没有统计的必要了。但是考虑到天呢?可以一些小店一天一个成交也没有,也是正常的。那么如何实现如果某天没有数据,统计的时候让那天的统计数据变成0呢?