​​MySQL中如何计算同比和环比!

 

 

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

 

前言

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

 

我们先来看看什么是同比，什么是环比：

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

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

 

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

同比增长率：

同 比 增 长 率 = ( 本 期 数 − 同 期 数 ) / 同 期 数 ∗ 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);

 

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

 

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

 

 

计算同比和环比

 

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

 

 

结果：

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

 

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

 

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

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需99元，即可下载本站文章涉及的文件和软件。