MySQL 8.0 利用窗口函数计算同比环比

站长

2023年06月22日 16:56 · 阅读数 16

我们在业务中常常需要统计这个月销售量多少，同比增加多少，环比增加多少。这篇博文我们就看看如何利用窗口函数实现同比及环比的计算。用到的关键字包括：lag, window

准备工作

首先我们得有MySQL 8.0及以上版本, 然后我们准备一张统计表。

CREATE TABLE `my_stat` (
  `month` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `profit` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

表里的内容如下：

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

INSERT INTO `my_stat` (`month`, `profit`)
VALUES
  ('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及输出结果

select `month`,profit,
 lag(profit) OVER w AS 上月,
 (profit - lag(profit) OVER w) as 环比,
 100*(profit - lag(profit) OVER w)/lag(profit) OVER w as 环比比例
 from my_stat
 WINDOW w AS (ORDER BY `month`);

输出结果:

month|profit|上月|环比|环比比例
--|--|--|--|--
2022-05|10.00|NULL|NULL|NULL
2022-06|12.00|10.00|2.00|20.000000
2022-07|6.00|12.00|-6.00|-50.000000
2022-08|30.00|6.00|24.00|400.000000
2022-09|20.00|30.00|-10.00|-33.333333
2022-10|8.00|20.00|-12.00|-60.000000
2022-11|2.00|8.00|-6.00|-75.000000
2022-12|4.00|2.00|2.00|100.000000
2023-01|14.00|4.00|10.00|250.000000
2023-02|10.00|14.00|-4.00|-28.571429
2023-03|8.00|10.00|-2.00|-20.000000
2023-04|9.00|8.00|1.00|12.500000
2023-05|7.00|9.00|-2.00|-22.222222
2023-06|10.00|7.00|3.00|42.857143
2023-07|15.00|10.00|5.00|50.000000

lag

lag 函数的完整定义如下：

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示例最后一行：

 WINDOW w AS (ORDER BY `month`);

这里定义了一个窗口(window), 基于 month这个字段升序。

window

上一节的 over_clause 定义如下：

over_clause:
    {OVER (window_spec) | OVER window_name}

我们看到，可以 over (窗口定义) 或者 over 窗口名称。

 lag(profit) OVER w AS 上月

这就是一个 over 窗口名称的示例。

接下来我们看 window_spec 的定义：

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

window_name 就是给窗口起个名字，方便使用
partition_clause 分区定义, 就是定义查询结果如何分组，有点类似group by 的意思。
order_clause 排序定义,定义窗口内容排序字段及方式，如前面示例里的 order by `month` 。如果省略了order by , 那就按查询到结果的顺序来排序。
frame_clause frame_clause(框架子句)指定如何定义子集

同比环比计算

还是先看最终SQL

select `month`,profit,
 lag(profit) OVER w AS 上月,
 (profit - lag(profit) OVER w) as 环比,
 100*(profit - lag(profit) OVER w)/lag(profit) OVER w as 环比比例,
 
 lag(profit,12) OVER w AS 上年同月,
 (profit - lag(profit,12) OVER w) as 同比,
 100*(profit - lag(profit,12) OVER w)/lag(profit,12) OVER w as 同比比例
 
 from my_stat
 WINDOW w AS (ORDER BY `month`);

返回结果

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

CREATE TABLE `my_stat_food` (
  `month` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `profit` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

然后插入数据

INSERT INTO `my_stat_food` (`month`, `type`, `profit`)
VALUES
 ('2023-05', '蔬菜', 1.00),
 ('2023-05', '水果', 2.00),
 ('2023-05', '肉类', 3.00),
 ('2023-06', '蔬菜', 4.00),
 ('2023-06', '水果', 5.00),
 ('2023-06', '肉类', 6.00),
 ('2023-07', '蔬菜', 7.00),
 ('2023-07', '水果', 8.00),
 ('2023-07', '肉类', 9.00);

定义

partition_clause:
    PARTITION BY expr [, expr] ...

然后我们试试pratition的效果

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呢？

参考

dev.mysql.com/doc/refman/…