SQL同比环比

admin管理员组

文章数量:1794759

SQL同比环比

描述:从概念入手，从临时表分析解说，案例强化三部分。一文搞懂SQL同比环比！

通过本文学习，你将学会：

1、SQL查询的高阶需求

2、同比环比运用更加灵活

                                                                                ps:你的点赞评论是我翻新更新博文最大的动力!

🏆一、概念

同比就是比去年同期，环比就是这个月比上个月，单位不一定是月，可以是任何时间单位。

同比计算方式：同比增长率 =（本期数－同期数）÷ 同期数×100%

环比计算方式：环比增长速度 =（本期数－上期数）÷ 上期数×100%

🏆二、吹角连营、步步为营

ps:数据库版本MYSQL8.0

⭐️2.1、准备数据

销售明细

产品明细

⭐️2.2、步骤拆解

 首先，要获取每年每月的销售额，要有销售额，两个表肯定是要连接起来的

SELECT YEAR ( a.销售时间 ) years, substr( a.销售时间, 6, 2 ) months, SUM( a.销售数量 * b.产品单价 ) money FROM 销售明细 a LEFT JOIN 产品明细 b ON a.产品ID = b.产品ID GROUP BY YEAR ( a.销售时间 ), substr( a.销售时间, 6, 2 ) ORDER BY years, months

同比：一般情况下是今年第n月与去年第n月比

所以要有个本期数据与同期数据一一对应的表，那就要本期数据与同期数据分离。有了第一步之后很容易获得数据。

本期数据:

with t as ( SELECT YEAR ( a.销售时间 ) years, substr( a.销售时间, 6, 2 ) months, SUM( a.销售数量 * b.产品单价 ) money FROM 销售明细 a LEFT JOIN 产品明细 b ON a.产品ID = b.产品ID GROUP BY YEAR ( a.销售时间 ), substr( a.销售时间, 6, 2 ) ORDER BY years, months )select * from t where years = '2020'

 同期数据：

with t as ( SELECT YEAR ( a.销售时间 ) years, substr( a.销售时间, 6, 2 ) months, SUM( a.销售数量 * b.产品单价 ) money FROM 销售明细 a LEFT JOIN 产品明细 b ON a.产品ID = b.产品ID GROUP BY YEAR ( a.销售时间 ), substr( a.销售时间, 6, 2 ) ORDER BY years, months ),t2 as ( select * from t where years = '2020' )select * from t where years = '2019'

下面就要进行表的关联了 本期、同期的区别就是年份不一样，月份一样呗,那就用月份作为连接点进行连接，代码和结果(未优化)截图如下，同比就完成了~

with t as ( SELECT YEAR ( a.销售时间 ) years, substr( a.销售时间, 6, 2 ) months, SUM( a.销售数量 * b.产品单价 ) money FROM 销售明细 a LEFT JOIN 产品明细 b ON a.产品ID = b.产品ID GROUP BY YEAR ( a.销售时间 ), substr( a.销售时间, 6, 2 ) ORDER BY years, months ),t2 as ( select years a,months b,money c from t where years = '2020' ),t3 as ( select years e,months f,money g from t where years = '2019' )select * from t2 left join t3 on t2.b = t3.f

(优化后代码)转化为百分比，此处考虑到销售额为0的情况，分母不能为0，用case进行了条件判断

计算的是本期，本期数据是关键所以这边进行的是左连接—因为本期数据放在左边，放右边用右连接就好了

with t as ( SELECT YEAR ( a.销售时间 ) years, substr( a.销售时间, 6, 2 ) months, SUM( a.销售数量 * b.产品单价 ) money FROM 销售明细 a LEFT JOIN 产品明细 b ON a.产品ID = b.产品ID GROUP BY YEAR ( a.销售时间 ), substr( a.销售时间, 6, 2 ) ORDER BY years, months ),t2 as ( select years a,months b,money c from t where years = '2020' ),t3 as ( select years e,months f,money g from t where years = '2019' ) select CONCAT(a,'-',b) 日期, c 销售额, CASE WHEN t3.g > 0 THEN CONCAT((c - g) / g * 100,'％') ELSE "同期没有数据" END 同比 from t2 left join t3 on t2.b = t3.f

接下来单步操作获得环比数据

环比：一般是指报告期水平与前一时期水平之比，此处指本月数据与上月数据

肯定也是要有表的关联了呗，怎么连呢？？？ 既然是计算2020年的环比，那就以要计算的为基础数据，进行表的关联 上面已经有全部的年份、月份对应的数据，这一步主要是进行表的关联 两个表进行关联：

■ 2020年数据的表，即判断条件设置年份为2020的查询结果表

                                    【这个在我们前面t2临时表已经拿到了】

■ 考虑到2020年一月份对应的是2019年的12月份，所以这个张表是不加判断条件的表，包含了2019年和2020年数据的表

                                    【只需要在写一个临时表取这段数据即可】

with t as ( SELECT YEAR ( a.销售时间 ) years, substr( a.销售时间, 6, 2 ) months, SUM( a.销售数量 * b.产品单价 ) money FROM 销售明细 a LEFT JOIN 产品明细 b ON a.产品ID = b.产品ID GROUP BY YEAR ( a.销售时间 ), substr( a.销售时间, 6, 2 ) ORDER BY years, months ),t2 as ( select years a,months b,money c from t where years = '2020' ),t3 as ( select years e,months f,money g from t where years = '2019' ),t4 as ( -- 同比数据 select CONCAT(a,'-',b) 日期, c 销售额, CASE WHEN t3.g > 0 THEN CONCAT((c - g) / g * 100,'％') ELSE "同期没有数据" END 同比 from t2 left join t3 on t2.b = t3.f ),t5 as ( select years x,months y,money z from t )select * from t2 left join t5 on ((t2.b - 1) = y and t2.a = t5.x) or (t2.b = 1 and t5.y = 12 and t5.x = 2019)

解析一下这个： on  ((t2.b - 1) = y and t2.a = t5.x) or (t2.b = 1 and t5.y = 12 and t5.x = 2019)

(t2.b - 1) = y and t2.a = t5.x

这个针对的是2020年大于1月的数据 让：

2022年02月数据和2020年01月数据对应

2022年03月数据和2020年02月数据对应

2022年04月数据和2020年03月数据对应

2022年05月数据和2020年04月数据对应

2022年06月数据和2020年05月数据对应

好了 现在就剩下一个2020年01月的数据了

就不能用上面的办法了，跨年了，所以需要用and连接的方式进行限制，而且需要限定为2019年的12月份。这边因为数据比较少，不限定年份也没问题

两个部分用 or 并列存在，两个条件要分别用括号括起来，否则这个查询条件等于没有。。。。

把* 优化一下 换成concat case when代码

with t as ( SELECT YEAR ( a.销售时间 ) years, substr( a.销售时间, 6, 2 ) months, SUM( a.销售数量 * b.产品单价 ) money FROM 销售明细 a LEFT JOIN 产品明细 b ON a.产品ID = b.产品ID GROUP BY YEAR ( a.销售时间 ), substr( a.销售时间, 6, 2 ) ORDER BY years, months ),t2 as ( select years a,months b,money c from t where years = '2020' ),t3 as ( select years e,months f,money g from t where years = '2019' ) ,t4 as ( -- 同比数据 select CONCAT(a,'-',b) 日期, c 销售额, CASE WHEN t3.g > 0 THEN CONCAT((c - g) / g * 100,'％') ELSE "同期没有数据" END 同比 from t2 left join t3 on t2.b = t3.f ),t5 as ( select years x,months y,money z from t ) select CONCAT(a,'-',b) 日期, c 本期月销售额, CASE WHEN z> 0 THEN CONCAT((c - z) / z * 100,'％') ELSE "上期没有数据" END 环比 from t2 left join t5 on ((t2.b - 1) = y and t2.a = t5.x) or (t2.b = 1 and t5.y = 12 and t5.x = 2019)

最后一步，将数据放到一个表里~

通过上述说明，可以发现都关联了2020年数据查询的表，所以可以将同比环比进行关联（也是操作同比数据用右连接的原因）

                                【临时表t4是同比数据，临时表t6是环比数据】

with t as ( SELECT YEAR ( a.销售时间 ) years, substr( a.销售时间, 6, 2 ) months, SUM( a.销售数量 * b.产品单价 ) money FROM 销售明细 a LEFT JOIN 产品明细 b ON a.产品ID = b.产品ID GROUP BY YEAR ( a.销售时间 ), substr( a.销售时间, 6, 2 ) ORDER BY years, months ),t2 as ( select years a,months b,money c from t where years = '2020' ),t3 as ( select years e,months f,money g from t where years = '2019' ) ,t4 as ( -- 同比数据 select CONCAT(a,'-',b) 日期, c 销售额, CASE WHEN t3.g > 0 THEN CONCAT((c - g) / g * 100,'％') ELSE "同期没有数据" END 同比 from t2 left join t3 on t2.b = t3.f ),t5 as ( select years x,months y,money z from t ),t6 as ( select CONCAT(a,'-',b) 日期, c 本期月销售额, CASE WHEN z> 0 THEN CONCAT((c - z) / z * 100,'％') ELSE "上期没有数据" END 环比 from t2 left join t5 on ((t2.b - 1) = y and t2.a = t5.x) or (t2.b = 1 and t5.y = 12 and t5.x = 2019) )select t4.日期,销售额,同比,环比 from t4 left join t6 on t4.日期 = t6.日期

 

 到这里可以将我们的SQL优化一下 因为一步步走过来后 我们已经知道思路怎么回事、SQL语句怎么写，那考虑SQL的可读性、美观、效率等因素，可以将我们的SQL进行优化

with t as ( -- 1、基础表 SELECT YEAR(a.销售时间) years, SUBSTR(a.销售时间, 6, 2) months, SUM(a.销售数量 * b.产品单价) money FROM 销售明细 a LEFT JOIN 产品明细 b ON a.产品ID = b.产品ID GROUP BY YEAR ( a.销售时间 ), SUBSTR( a.销售时间, 6, 2 ) ORDER BY years, months ),t2 as ( -- 2、本期数据 select years a,months b,money c from t where years = '2020' ),t3 as ( -- 3、上期数据 select years e,months f,money g from t where years = '2019' ),t5 as ( -- 5、所有数据 select years x,months y,money z from t ) ,t4 as ( -- 4、同比数据 select CONCAT(a,'-',b) 日期, c 销售额, CASE WHEN t3.g > 0 THEN CONCAT((c - g) / g * 100,'％') ELSE "同期没有数据" END 同比 from t2 left join t3 on t2.b = t3.f ),t6 as ( -- 6、环比数据 select CONCAT(a,'-',b) 日期, c 本期月销售额, CASE WHEN z> 0 THEN CONCAT((c - z) / z * 100,'％') ELSE "上期没有数据" END 环比 from t2 left join t5 on ((t2.b - 1) = y and t2.a = t5.x) or (t2.b = 1 and t5.y = 12 and t5.x = 2019) ),t7 as( -- 7、最终统计 SELECT t4.日期, 销售额, 同比, 环比 FROM t4 LEFT JOIN t6 ON t4.日期 = t6.日期 ) select * from t7 🏆三、案例强化

注:只针对Finreport的一个小案例

描述 数据来源使用frdemo的订单明细和订单表

效果图

⭐️3.1、下拉框选择年份参数筛选

select distinct strftime('%Y',订购日期) as year from 订单 order by year asc

⭐️3.2、页面隔行变色

条件属性隔行变色

⭐️3.3、在SQL中计算同环比，不在单元格内计算与过滤

select t.m,t.ym,

      t.销量,

      t1.销量 同期,

      (case when t1.销量 is null then '' 

          else (t.销量-t1.销量)/t1.销量 end) 同比,

      t2.销量 上期,

      (case when t2.销量 is null then '' 

          else (t.销量-t2.销量)/t2.销量 end) 环比

from 

(

select strftime('%m',b.订购日期)+0 m,

      strftime('%Y-%m',b.订购日期) ym,

      sum(a.数量) 销量

from 订单明细 a

    left join 订单 b on a.订单ID=b.订单ID

where strftime('%Y',b.订购日期) = '1997'

group by strftime('%Y-%m',b.订购日期)

) t   -- 1997年 m月份 ym年月 销量 销售额

left join

(

select strftime('%m',b.订购日期)+0 m,

      strftime('%Y-%m',b.订购日期) ym,

      sum(a.数量) 销量

from 订单明细 a

    left join 订单 b on a.订单ID=b.订单ID

where strftime('%Y',b.订购日期) = '1996'

group by strftime('%Y-%m',b.订购日期)

) t1 on t.m=t1.m  -- 1996 年 m月份 ym年月 销量 销售额

left join

(

select (case when strftime('%m',b.订购日期)+0 <=11

        then strftime('%m',b.订购日期)+1 end) m, -- 1997年 使用then判断 ym中月份小于等于11 将月份+1

      strftime('%Y-%m',b.订购日期) ym,

      sum(a.数量) 销量

from 订单明细 a

    left join 订单 b on a.订单ID=b.订单ID

where strftime('%Y',b.订购日期) = '1997'

group by strftime('%Y-%m',b.订购日期)

union

select (case when strftime('%m',b.订购日期)+0 =12

        then 1 end) m,

      strftime('%Y-%m',b.订购日期) ym,

      sum(a.数量) 销量

from 订单明细 a

    left join 订单 b on a.订单ID=b.订单ID

where strftime('%Y',b.订购日期) = '1996'

group by strftime('%Y-%m',b.订购日期)

) t2 on t.m=t2.m

⭐️3.4、比例为负时加粗标红

 颜色+字体 没有生效

 背景颜色 + 字体 生效

解决方法 条件属性分开设置

先加粗 先显示红色 先后顺序好像导致最终显示有所出入 (咱也不知道这是为啥)

需要先加粗后显红才能达到下图显示效果

如果是先显红 后加粗 最终结果显示只是加粗的情况

 

效果图 ⭐️3.5、标题随年份动态显示

将三、步骤中 年份替换参数名

样式如下

select t.m,t.ym, t.销量, t1.销量 同期, (case when t1.销量 is null then '' else (t.销量-t1.销量)/t1.销量 end) 同比, t2.销量 上期, (case when t2.销量 is null then '' else (t.销量-t2.销量)/t2.销量 end) 环比 from ( select strftime('%m',b.订购日期)+0 m, strftime('%Y-%m',b.订购日期) ym, sum(a.数量) 销量 from 订单明细 a left join 订单 b on a.订单ID=b.订单ID where strftime('%Y',b.订购日期) = '${y}' group by strftime('%Y-%m',b.订购日期) ) t -- 1997年 m月份 ym年月 销量 销售额 left join ( select strftime('%m',b.订购日期)+0 m, strftime('%Y-%m',b.订购日期) ym, sum(a.数量) 销量 from 订单明细 a left join 订单 b on a.订单ID=b.订单ID where strftime('%Y',b.订购日期) = '${y-1}' group by strftime('%Y-%m',b.订购日期) ) t1 on t.m=t1.m -- 1996 年 m月份 ym年月 销量 销售额 left join ( select (case when strftime('%m',b.订购日期)+0 <=11 then strftime('%m',b.订购日期)+1 end) m, -- 1997年 使用then判断 ym中月份小于等于11 将月份+1 strftime('%Y-%m',b.订购日期) ym, sum(a.数量) 销量 from 订单明细 a left join 订单 b on a.订单ID=b.订单ID where strftime('%Y',b.订购日期) = '${y}' group by strftime('%Y-%m',b.订购日期) union select (case when strftime('%m',b.订购日期)+0 =12 then 1 end) m, strftime('%Y-%m',b.订购日期) ym, sum(a.数量) 销量 from 订单明细 a left join 订单 b on a.订单ID=b.订单ID where strftime('%Y',b.订购日期) = '${y-1}' group by strftime('%Y-%m',b.订购日期) ) t2 on t.m=t2.m ⭐️3.6、文件下载

链接：pan.baidu/s/1YjJXJPVQXe3meBFEhp4p2Q  提取码：1111  --来自百度网盘超级会员V4的分享

本文标签： 环比SQL