Mysql计算同比环比(超详细)
1、首先观察两个表的格式表1、销售明细表2、产品明细2、在mysql中新建表表1:订单明细表 salessql如下CREATE TABLE sales(`产品ID` int NOT NULL,`销售数量` int(20) ,`销售时间` timestamp(6) NULL DEFAULT NULL,PRIMARY KEY (`订单ID`))INSERT INTO sales VALUES ('C1
·
1、首先观察两个表的格式
表1、销售明细
表2、产品明细
2、在mysql中新建表
表1:订单明细表 sales
sql如下
CREATE TABLE sales (
`产品ID` int NOT NULL,
`销售数量` int(20) ,
`销售时间` timestamp(6) NULL DEFAULT NULL,
PRIMARY KEY (`订单ID`)
)
INSERT INTO sales VALUES ('C1001', 15, '2020-06-01 10:10:12');
INSERT INTO sales VALUES ('C1002',26, '2020-05-02 0:10:12');
INSERT INTO sales VALUES ('C1003', 21, '2020-04-03 0:10:12');
INSERT INTO sales VALUES ('C1003', 23, '2020-04-04 0:10:12');
INSERT INTO sales VALUES ('C1003', 0, '2020-03-05 0:10:12');
INSERT INTO sales VALUES ('C1001', 16, '2020-02-06 3:0:12');
INSERT INTO sales VALUES ('C1002', 32, '2020-01-07 0:10:12');
INSERT INTO sales VALUES ('C1001', 16, '2019-12-08 0:12:24');
INSERT INTO sales VALUES ('C1001', 32, '2019-06-09 0:12:24');
INSERT INTO sales VALUES ('C1002', 17, '2019-05-09 0:12:24');
数据预览:
表2:产品表 product
sql 如下:
CREATE TABLE product (
`产品ID` varchar(20) NOT NULL,
`产品名称` varchar(20) ,
`产品单价` int (10)
)
INSERT INTO product VALUES ('C1001','产品A',45);
INSERT INTO product VALUES ('C1002','产品B',52);
INSERT INTO product VALUES ('C1003','产品C',39);
预览
3、计算同比环比
sql如下:
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
sql解析:
select year(c.销售时间) yy,month(c.销售时间) mm,
sum(c.销售数量*d.产品单价) ss,
# concat函数,mysql字符串拼接,因为同比和环比都是百分数
# ifnull函数,mysql判断字段是否为空,为空则为0
# abs函数,mysql取绝对值,因为我这里取的都是正数
# round函数,mysql保留几位小数
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
# 同比月份相同,年份减1
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
运行结果如下:
结果测试:
温馨提示:
由于mysql在规则上和函数上与Oracle等也有些差异,列如:函数、大小写、别名的情况都可能导致出现sql执行的结果出现问题,所以在套用的时候需要细心一点。
除此之外,还要计算累计销售
sql如下:
select year(销售时间) yy,month(销售时间) mm,
sum(销售数量*b.产品单价) over(order by year(销售时间) ,month(销售时间) ) 累计数量
from sales a
left join product b on a.产品ID=b.产品ID
order by yy,mm
查看了一些资料,感觉环比同比还是不太好写,今天总结整理一下。
更多推荐
已为社区贡献2条内容
所有评论(0)