|
sql语句下的同比，环比操作
题目：有两个表 表一：销售明细  表二：产品明细  需求：查询2020年,每月的销售额,以及同比环比,并按照年月进行升序排序
建数据库建表插入数据 偷个懒，这些语句就先省略了 给看个截图   ps: 建表时时间要选择datetime ,后面要对时间进行操作的~
思路：分步操作
获得每月的数据 首先的抽取时间，以及获得每个月的销售额，要有 销售额，两个表肯定是要连接起来的 ------- 获取时间 YEAR(字段名)—获取年份 MONTH(字段名)----获取时间
SELECT YEAR(s.sail_time) 年, MONTH(s.sail_time) 月 , SUM(s.number*p.pro_price) 销售额
FROM sail_info s
LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id
GROUP BY YEAR(s.sail_time) , MONTH(s.sail_time)
此处使用左连接的原因：是要补充销售表里单品的价格，要以左表为基准，要用了左连接 结果： 
单步操作获得同比数据 同比：一般情况下是今年第n月与去年第n月比 计算方式：同比增长率=（本期数－同期数）÷同期数×100% 所以要有个本期数据与同期数据一一对应的表，那就要本期数据与同期数据分离 有了第一步之后很容易获得数据 本期数据：
SELECT YEAR(s.sail_time) 年, MONTH(s.sail_time) 月 , SUM(s.number*p.pro_price) 销售额
FROM sail_info s
LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id
where YEAR(s.sail_time)=2020
GROUP BY YEAR(s.sail_time) , MONTH(s.sail_time)
 同期数据：
SELECT YEAR(s.sail_time) 年, MONTH(s.sail_time) 月 , SUM(s.number*p.pro_price) 销售额
FROM sail_info s
LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id
where YEAR(s.sail_time)=2019
GROUP BY YEAR(s.sail_time) , MONTH(s.sail_time)
 下面就要进行表的关联了 本期、同期的区别就是年份不一样，月份一样呗,那就用月份作为连接点进行连接，代码和结果截图如下，同比就完成了~ 计算的是本期，本期数据是关键所以这边进行的是右连接—因为本期数据放在右边，放左边用左连接就好了 ps:如果觉得这边跳了步骤的话可以将查询结果改成 * 先去查看关联之后的表
SELECT CONCAT(e,'-',a) 年月, b 月销售额,
CASE WHEN d>0 THEN CONCAT((b-d)/d*100,'％') -- 转化为百分比，此处考虑到销售额为0的情况，分母不能为0，用case进行了条件判断
ELSE "同期没有数据" END 同比
-- 这边的文字可以 换掉
FROM
(SELECT YEAR(s.sail_time) f , MONTH(s.sail_time) c , SUM(s.number*p.pro_price) d FROM sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id WHERE YEAR(s.sail_time)=2019 GROUP BY MONTH(s.sail_time)) s2
RIGHT JOIN
(SELECT YEAR(s.sail_time) e，MONTH(s.sail_time) a , SUM(s.number*p.pro_price) b FROM sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id WHERE YEAR(s.sail_time)=2020 GROUP BY MONTH(s.sail_time)) s1
ON s1.a=s2.c
单步操作获得环比数据 环比：一般是指报告期水平与前一时期水平之比，此处指本月数据与上月数据 计算方式：环比增长速度=（本期数－上期数）÷上期数×100% 肯定也是要有表的关联了呗，怎么连呢？？？ 既然是计算2020年的环比，那就以要计算的为基础数据，进行表的关联 上面已经有全部的年份、月份对应的数据，这一步主要是进行表的关联 两个表进行关联： -1 2020年数据的表，即判断条件设置年份为2020的查询结果表 -2 考虑到2020年一月份对应的是2019年的12月份，所以这个张表是不加判断条件的表，包含了2019年和2020年数据的表
SELECT * FROM
(SELECT YEAR(s.sail_time) e,MONTH(s.sail_time) a , SUM(s.number*p.pro_price) 本期销售额 FROM sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id WHERE YEAR(s.sail_time)=2020 GROUP BY MONTH(s.sail_time)) s1
LEFT JOIN
(SELECT YEAR(s.sail_time) f, MONTH(s.sail_time) g, SUM(s.number*p.pro_price) 上期销售额 FROM sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id GROUP BY MONTH(s.sail_time),YEAR(s.sail_time)) s3
ON ((s1.a-1)=s3.g AND s1.e=s3.f) OR (s1.a=1 AND s3.g=12 AND s3.f=2019)
 解析以下这个： 
(s1.a-1)=s3.g AND s1.e=s3.f
这个是针对2020年2月份的数据，2020 02-06 的数据与2020 01-05的数据一一对应 按关联前的，a(月份)和g(月份)应该是相等的，因为对比的是上一期的，所以要错开，大的减掉等与小的 那一月份怎么办呢？？
s1.a=1 AND s3.g=12 AND s3.f=2019
这边就不能用上面的办法了，跨年了，所以需要用and连接的方式进行限制，而且需要限定为2019年的12月份，这边因为数据比较少，不限定年份也没问题
两个部分用 or 并列存在，两个条件要分别用括号括起来，否则这个查询条件等于没有。。。。 把* 换成
CONCAT(e,'-',a) 年月,f 月销售额,
CASE WHEN h>0 THEN CONCAT((b-h)/h*100,'％')
ELSE "上期没有数据" END 环比
执行查询： 
最后一步，将数据放到一个表里~ 通过上述说明，可以发现都关联了2020年数据查询的表，所以可以将这个表放在中间 ，三个表进行关联（也是操作同比数据用右连接的原因）
CASE WHEN d>0 THEN CONCAT((b-d)/d*100,'％')
ELSE "同期没有数据" END 同比 ,
CASE WHEN h>0 THEN CONCAT((b-h)/h*100,'％')
ELSE "上期没有数据" END 环比
FROM
(SELECT MONTH(s.sail_time) c , SUM(s.number*p.pro_price) d ,YEAR(s.sail_time) ye FROM sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id WHERE YEAR(s.sail_time)=2019 GROUP BY MONTH(s.sail_time)) s2
RIGHT JOIN
(SELECT MONTH(s.sail_time) a , SUM(s.number*p.pro_price) b ,YEAR(s.sail_time) e FROM sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id WHERE YEAR(s.sail_time)=2020 GROUP BY MONTH(s.sail_time)) s1
ON s1.a=s2.c
LEFT JOIN
(SELECT YEAR(s.sail_time) f, MONTH(s.sail_time) g, SUM(s.number*p.pro_price) h FROM sail_info s LEFT JOIN produce_detail p
ON s.produce_id=p.produce_id GROUP BY MONTH(s.sail_time),YEAR(s.sail_time)) s3 ON ((s1.a-1)=s3.g AND s1.e=s3.f) OR
(s1.a=1 AND s3.g=12 AND s3.f=2019) ORDER BY CONCAT(e,'-',a)
 结束！
|