环比: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