1. 首页首页
  2. 编程笔记编程笔记

环比和同比的区别

编程笔记 • • 阅读 27

描述:从概念入手，从临时表分析解说，案例强化三部分。一文搞懂SQL同比环比！ 通过本文学习，你将学会： 1、SQL查询的高阶需求 2、同比环比运用更加灵活 ps:你的点赞评论是我翻新更新博文最大的动力! 🏆一、概念 同比就是比去年同期，

描述:从概念入手，从临时表分析解说，案例强化三部分。一文搞懂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年的环比，那就以要计算的为基础数据，进行表的关联
上面已经有全部的年份、月份对应的数据，这一步主要是进行表的关联
两个表进行关联：

  1. ■ 2020年数据的表，即判断条件设置年份为2020的查询结果表
    1.                                     【这个在我们前面t2临时表已经拿到了】
  2. ■ 考虑到2020年一月份对应的是2019年的12月份，所以这个张表是不加判断条件的表，包含了2019年和2020年数据的表
    1.                                     【只需要在写一个临时表取这段数据即可】
  3. 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、文件下载

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

知秋君
知秋君

知秋君

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请关注公众号“全栈程序员社区”联系小编举报，一经查实，本站将立刻删除。
上一篇 2024-07-18 20:02
下一篇 2024-07-18 19:36

相关推荐