MySQL计算同比和环比

MySQL计算同比和环比

今天在做数据建模的时候,ads层的需求中有个叫同比和环比的指标,这两指标的计算之前都没有接触过,经过我两三个小时的研究,终于搞明白什么是同比,什么是环比,如何使用Mysql来计算同比和环比。

我们先来看看什么是同比,什么是环比:

  • 同比:通常是指今年第n月与去年第n月相比。同比发展速度主要是为了消除季节变动的影响,用以说明本期发展水平与去年同期发展水平对比而达到的相对发展速度。

  • 环比:通常是指表示连续2个单位周期(比如连续两月)内的量的变化比。环比包括两种:环比增长速度和环比发展速度。

那同比增长率和环比增长率又如何计算呢:

同比增长率:
同 比 增 长 率 = ( 本 期 数 − 同 期 数 ) / 同 期 数 ∗ 100 % 同比增长率=(本期数-同期数)/同期数*100 \% =()/100%

环比增长率:
环 比 增 长 率 = ( 本 期 数 − 上 期 数 ) / 上 期 数 × 100 % 环比增长率=(本期数-上期数)/上期数×100\% =()/×100%

在MySQL中如何计算同比和环比

数据准备

创建商品表并向商品表中添加数据

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);

image-20220317095350813

创建订单明细表并向订单明细表中添加数据

CREATE TABLE sales  (
    `订单ID` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `产品ID` varchar(25) NOT NULL ,
	  `销售数量` int(20) ,
  `销售时间` timestamp(6) NULL DEFAULT NULL
);
INSERT INTO sales(`产品ID`,`销售数量`,`销售时间`) VALUES ('C1001', 15, '2020-06-01 10:10:12');
INSERT INTO sales(`产品ID`,`销售数量`,`销售时间`)  VALUES ('C1002',26, '2020-05-02 0:10:12');
INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1003', 21, '2020-04-03 0:10:12');
INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1003', 23, '2020-04-04 0:10:12');
INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1003', 0, '2020-03-05 0:10:12');
INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1001', 16, '2020-02-06 3:0:12');
INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1002', 32, '2020-01-07 0:10:12');
INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1001', 16, '2019-12-08 0:12:24');
INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1001', 32, '2019-06-09  0:12:24');
INSERT INTO sales (`产品ID`,`销售数量`,`销售时间`) VALUES ('C1002', 17, '2019-05-09 0:12:24');

image-20220317121540101

计算同比和环比

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

结果:

image-20220317121641787

除此之外,还想要计算累计销售的话,可以这样写:

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

同比和环比这块的确不好计算

<p> <span style="font-size:14px;color:#E53333;">限时福利1:</span><span style="font-size:14px;">购课进答疑群专享柳峰(刘运强)老师答疑服务</span> </p> <p> <br /> </p> <p> <br /> </p> <p> <span style="font-size:14px;"></span> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>为什么需要掌握高性能的MySQL实战?</strong></span> </p> <p> <span><span style="font-size:14px;"><br /> </span></span> <span style="font-size:14px;">由于互联网产品用户量大、高并发请求场景多,因此对MySQL的性能、可用性、扩展性都提出了很高的要求。使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能,也是衡量一个程序员能力薪资的标准之一。</span> </p> <p> <br /> </p> <p> <span style="font-size:14px;">为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了</span><span style="font-size:14px;">「高性能 MySQL 知识框架图」</span><span style="font-size:14px;">,帮你梳理学习重点,建议收藏!</span> </p> <p> <br /> </p> <p> <img alt="" src="https://img-bss.csdnimg.cn/202006031401338860.png" /> </p> <p> <br /> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>【课程设计】</strong></span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;">课程分为四大篇章,将为你建立完整的 MySQL 知识体系,同时将重点讲解 MySQL 底层运行原理、数据库的性能调优、高并发、海量业务处理、面试解析等。</span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;"></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>一、性能优化篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括经典 MySQL 问题剖析、索引底层原理事务与锁机制。通过深入理解 MySQL 的索引结构 B+Tree ,学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引,从而彻底掌握索引的使用优化技巧,能够避开很多实战中遇到的“坑”。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>二、MySQL 8.0新特性篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括窗口函数通用表表达式。企业中的许多报表统计需求,如果不采用窗口函数,用普通的 SQL 语句是很难实现的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>三、高性能架构篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括主从复制读写分离。在企业的生产环境中,很少采用单台MySQL节点的情况,因为一旦单个节点发生故障,整个系统都不可用,后果往往不堪设想,因此掌握高可用架构的实现是非常有必要的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>四、面试篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">程序员获得工作的第一步,就是高效的准备面试,面试篇主要从知识点回顾总结的角度出发,结合程序员面试高频MySQL问题精讲精练,帮助程序员吊打面试官,获得心仪的工作机会。</span> </p>

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:1024 设计师:我叫白小胖 返回首页
评论

打赏作者

我菜的要死

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值