sql语句下的同比,环比操作

数据库 专栏收录该内容
4 篇文章 0 订阅

sql语句下的同比,环比操作

题目:有两个表
表一:销售明细
在这里插入图片描述
表二:产品明细
在这里插入图片描述
需求:查询2020年,每月的销售额,以及同比环比,并按照年月进行升序排序

  1. 建数据库
  2. 建表
  3. 插入数据
    偷个懒,这些语句就先省略了
    给看个截图
    在这里插入图片描述
    在这里插入图片描述
    ps: 建表时时间要选择datetime ,后面要对时间进行操作的~

思路:分步操作

  1. 获得每月的数据
    首先的抽取时间,以及获得每个月的销售额,要有 销售额,两个表肯定是要连接起来的
    ------- 获取时间
    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)  

此处使用左连接的原因:是要补充销售表里单品的价格,要以左表为基准,要用了左连接
结果:
在这里插入图片描述

  1. 单步操作获得同比数据
    同比:一般情况下是今年第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 

在这里插入图片描述

  1. 单步操作获得环比数据
    环比:一般是指报告期水平与前一时期水平之比,此处指本月数据与上月数据
    计算方式:环比增长速度=(本期数-上期数)÷上期数×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 环比

执行查询:
在这里插入图片描述

  1. 最后一步,将数据放到一个表里~
    通过上述说明,可以发现都关联了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) 

在这里插入图片描述
结束!

  • 19
    点赞
  • 0
    评论
  • 42
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值