MySQL8超实用的lag函数实现同比、环比计算

最新推荐文章于 2024-07-30 03:44:55 发布

奋斗的老史

最新推荐文章于 2024-07-30 03:44:55 发布

阅读量347

点赞数 3

分类专栏： mysql8.0新特性文章标签：数据库

本文链接：https://blog.csdn.net/qq_34249468/article/details/139492376

版权

mysql8.0新特性专栏收录该内容

4 篇文章 0 订阅

订阅专栏

在这里插入图片描述
环比：month-on-month ratio
同比：on year-on-year basis
MySQL5.x时代巨麻烦的分组左关联运算实现同比环比

select  year(c.销售时间) yy,month(c.销售时间) mm,     
       sum(c.销售数量*d.产品单价) ss,
concat(ifnull(abs(round((sum(c.销售数量*d.产品单价)-ss1)/ss1*100,2)),0),'%') 同比,
concat(ifnull(abs(round((sum(c.销售数量*d.产品单价)-ss2)/ss2*100,2)),0),'%')  环比
from sales c
left join product d on c.产品ID=d.产品ID
left join (select month(a.销售时间) mm1,
                    year(a.销售时间) yy1,
                    sum(a.销售数量*d.产品单价) ss1
          from sales a
          left join product d on a.产品ID=d.产品ID
          GROUP BY mm1,yy1) a
          on month(c.销售时间) = a.mm1 
          and a.yy1 = year(c.销售时间)-1	 
 left join  (select month(a.销售时间) mm2,
                    year(a.销售时间) yy2,
                    sum(a.销售数量*d.产品单价) ss2
             from sales a
		     left join product d on a.产品ID=d.产品ID
              GROUP BY mm2,yy2) b
on (b.yy2 = year(c.销售时间) and b.mm2+1 = month(c.销售时间) OR (yy2=year(c.销售时间)-1 
AND b.mm2 = 12 AND month(c.销售时间) = 1))
 group by yy, mm
 order by yy,mm asc

MySQL8.x提供了Lag开窗函数可以超级方便的实现类似功能
lead()/lag()函数
lag与lead函数是跟偏移量相关的两个分析函数
通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤
原始脚本

CREATE TABLE `sale_data`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `product` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `num` int(0) NOT NULL,
  `period` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `sale_data` VALUES (1, 'A', 62, '202201');
INSERT INTO `sale_data` VALUES (2, 'B', 45, '202201');
INSERT INTO `sale_data` VALUES (3, 'C', 46, '202201');
INSERT INTO `sale_data` VALUES (4, 'A', 87, '202202');
INSERT INTO `sale_data` VALUES (5, 'B', 24, '202202');
INSERT INTO `sale_data` VALUES (6, 'C', 32, '202202');
INSERT INTO `sale_data` VALUES (7, 'A', 72, '202203');
INSERT INTO `sale_data` VALUES (8, 'B', 38, '202203');
INSERT INTO `sale_data` VALUES (9, 'C', 34, '202203');
INSERT INTO `sale_data` VALUES (10, 'A', 91, '202204');
INSERT INTO `sale_data` VALUES (11, 'B', 99, '202204');
INSERT INTO `sale_data` VALUES (12, 'C', 87, '202204');
INSERT INTO `sale_data` VALUES (13, 'A', 19, '202205');
INSERT INTO `sale_data` VALUES (14, 'B', 72, '202205');
INSERT INTO `sale_data` VALUES (15, 'C', 86, '202205');
INSERT INTO `sale_data` VALUES (16, 'A', 9, '202206');
INSERT INTO `sale_data` VALUES (17, 'B', 56, '202206');
INSERT INTO `sale_data` VALUES (18, 'C', 28, '202206');
INSERT INTO `sale_data` VALUES (19, 'A', 59, '202207');
INSERT INTO `sale_data` VALUES (20, 'B', 91, '202207');
INSERT INTO `sale_data` VALUES (21, 'C', 52, '202207');
INSERT INTO `sale_data` VALUES (22, 'A', 82, '202208');
INSERT INTO `sale_data` VALUES (23, 'B', 81, '202208');
INSERT INTO `sale_data` VALUES (24, 'C', 78, '202208');
INSERT INTO `sale_data` VALUES (25, 'A', 46, '202209');
INSERT INTO `sale_data` VALUES (26, 'B', 86, '202209');
INSERT INTO `sale_data` VALUES (27, 'C', 25, '202209');
INSERT INTO `sale_data` VALUES (28, 'A', 86, '202210');
INSERT INTO `sale_data` VALUES (29, 'B', 25, '202210');
INSERT INTO `sale_data` VALUES (30, 'C', 67, '202210');
INSERT INTO `sale_data` VALUES (31, 'A', 84, '202211');
INSERT INTO `sale_data` VALUES (32, 'B', 75, '202211');
INSERT INTO `sale_data` VALUES (33, 'C', 52, '202211');
INSERT INTO `sale_data` VALUES (34, 'A', 17, '202212');
INSERT INTO `sale_data` VALUES (35, 'B', 82, '202212');
INSERT INTO `sale_data` VALUES (36, 'C', 33, '202212');
INSERT INTO `sale_data` VALUES (37, 'A', 66, '202301');
INSERT INTO `sale_data` VALUES (38, 'B', 24, '202301');
INSERT INTO `sale_data` VALUES (39, 'C', 33, '202301');

得到上月销量数据

select * ,lag(num,1,0) over (PARTITION by product order by period) as last_month from sale_data

得到上年销量数据

select * ,lag(num,12,0) over (PARTITION by product order by period) last_year	from sale_data

得到均值数据

select * ,avg(num) over (PARTITION by product order by period) avg_num
	from sale_data

组合使用得到同比、环比、均值差数据

select product , num 
,last_month, round((num-last_month) / last_month * 100,2) mom
,last_year , round((num-last_year) / last_year *100,2)	 yoy 
,avg_num , round((num-avg_num) / avg_num *100,2)	 avg_diff 
from (
	select * ,lag(num,1,0) over (PARTITION by product order by period) as last_month 
	,lag(num,12,0) over (PARTITION by product order by period) last_year	
	,avg(num) over (PARTITION by product order by period) avg_num
	from sale_data order by product,period
) r