Mysql同环比计算详解-阿里云开发者社区

前言

同环比是数据分析中的一个重要指标，主要反应指标随时间变化的情况，在日常开发中会经常遇到。今天主要记录下Mysql下如何计算同环比。

一、什么是同环比

同环比分为2个指标：

同比

环比

根据统计周期的不同还可以分为：

环比分为：日环比、周环比、月环比和年环比

同比分为：日同比、周同比、月同比和年同比

同比

同比指的是本期数据和去年同期数据进行对比。

其中期指的是统计的时间范围，比如年，月，周等。

统计周期为月，则称为月同比；统计周期为周，则称为周同比。

同比主要是为了消除季节变化的影响，用以说明本期发展水平与去年同期发展水平对比而达到的相对发展速度；

适用于观察某个指标在不同年度的变化，其优势是可以去除大多数业务的季节因素，比如招聘2月是淡季，3月是旺季，做3月的分析使用环比实际上体现出的是招聘市场的变化，而不是公司销售额的变化，用同比则可以看出今年的增长情况，劣势是灵活性比较低，因为同比大多数是以年为单位，无法反映出数据的短期大量变化。

计算公式：同比=(本期数-上年同期数)/上年同期数×100%

同比增长率举例来说，例如说去年2月的产值100万，今年2月的产值300万，问同比增长率是多少？

计算方法是同比增长率=(300-100)÷100=200%。

注意⚠️：

这里的除数要取绝对指，不然如果除数为负数，那么求出的比值就无法反应实际的增长情况了。

比如说去年2月营业额增长率是-20%，今年2月营业额的增长率是30%，问营业额增长率同比是多少？

计算方法是同比增长率=(20% + 20%) ÷（-20%）= - 200%，同期的营业额增长率明明是增长了，但是计算的结果却为负数，显然不能反应实际的增长率。

环比

环比指的是本期数据和上一期数据进行对比。

比如上周和本周、上月和本月、上季度和本季度等等，**用于表示数据的连续变化趋势，**优势是对于高速增长型业务可以非常好的体现出业务的增长趋势和事件的影响。

计算公式：环比=(本期数-上期数)/上期数×100%

环比增长率举例来说，例如今年2月的产值100万，3月的产值300万，问环比增长率是多少？

计算方法是3月份的月环比增长率=(300-100)÷100=200%。

对比

1、对比基数不同：

同比的对比基数是上年的同一期间的数据，环比的基数则是上一期间的数据。

2、侧重点不同：

环比会突出显示数据的短期趋势，会受到季节等因素的影响。

同比更加侧重反映长期的大趋势，也就规避了季节的因素。

3、计算公式不同：

同比增长率＝（本期数－上年同期数）／上年同期数×100％。

环比增长率＝（本期数－上期数）／上期数×100％。

其他补充说明：

（1）如果计算结果为正值，则称增长率，如果计算为负值，则称下降率。

（2）环比中本期如果是本日、本周和本月，则上期相应指上一日、上一周和上一月；

同比中本期如果是本日、本周和本月，则同期相应指上年同一日、上年同一周和上年同一月；

（3）日环比则以每日的和作为比较条件，周环比以每月的和作为比较条件，月同比以每月的和作为比较条件。

（4）年环比和年同比是等同的，指年度的增长率。

二、同环比计算SQL

1.数据准备

新增商品销量表，并添加测试数据，然后统计对商品C1001进行同环比计算。

DROP TABLE if EXISTS sales;
--  商品销量表
CREATE TABLE `sales` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `product_id` varchar(25) NOT NULL COMMENT '产品ID',
  `num` int(11) DEFAULT NULL COMMENT '销售数量',
  `create_date` timestamp(6) NULL DEFAULT NULL COMMENT '销售时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

求月环比和月同比：

INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 12, '2020-01-02 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 32, '2020-01-07 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 12, '2020-01-27 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 16, '2020-02-06 3:00:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 10, '2020-03-05 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 23, '2020-04-04 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 21, '2020-04-03 0:10:12');
INSERT INTO sales(`product_id`,`num`,`create_date`)  VALUES ('C1001',26, '2020-05-02 0:10:12');
INSERT INTO sales(`product_id`,`num`,`create_date`) VALUES ('C1001', 15, '2020-06-01 10:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 32, '2020-07-07 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 16, '2020-08-06 3:0:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 10, '2020-09-05 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 23, '2020-10-04 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 21, '2020-11-03 0:10:12');
INSERT INTO sales(`product_id`,`num`,`create_date`)  VALUES ('C1001',26, '2020-12-02 0:10:12');
INSERT INTO sales(`product_id`,`num`,`create_date`) VALUES ('C1001', 15, '2020-12-05 10:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 50, '2021-01-07 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 8, '2021-01-27 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 38, '2021-02-06 3:00:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 20, '2021-03-05 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 28, '2021-04-04 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 30, '2021-04-03 0:10:12');
INSERT INTO sales(`product_id`,`num`,`create_date`)  VALUES ('C1001',45, '2021-05-02 0:10:12');
INSERT INTO sales(`product_id`,`num`,`create_date`) VALUES ('C1001', 20, '2021-06-01 10:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 26, '2021-07-07 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 36, '2021-08-06 3:0:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 15, '2021-09-05 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 20, '2021-10-04 0:10:12');
INSERT INTO sales (`product_id`,`num`,`create_date`) VALUES ('C1001', 18, '2021-11-05 0:10:12');
INSERT INTO sales(`product_id`,`num`,`create_date`)  VALUES ('C1001',30, '2021-12-02 0:10:12');
INSERT INTO sales(`product_id`,`num`,`create_date`) VALUES ('C1001', 20, '2021-12-06 10:10:12');
INSERT INTO sales(`product_id`,`num`,`create_date`) VALUES ('C1001', 28, '2021-12-28 10:10:12');

2.年环比和年同比

由于年环比和年同比表示的意义是一致的，都是表示年度的增长率，所以没有在sql语句中重复计算。

SELECT 
  ta.yy,
  ta.sumNum,
  concat(ifnull(round((ta.sumNum-tb.sumNum)/tb.sumNum*100,2),0),'%') as '年同比'
FROM (
    -- 内层的按年统计的自查询语句是相同的 
  SELECT
    t.product_id,
    year(t.create_date) as yy,
    sum(t.num) as sumNum
  FROM
    sales t WHERE product_id = 'C1001'
    GROUP BY t.product_id,yy
) ta 
-- 上年同月
LEFT JOIN 
(
  SELECT
    t.product_id,
    year(t.create_date) as yy,
    sum(t.num) as sumNum
  FROM
    sales t WHERE product_id = 'C1001'
    GROUP BY t.product_id,yy
) tb
ON   tb.yy = ta.yy-1  
ORDER BY ta.yy
;

3.月环比和月同比

SELECT 
  concat(ta.yy,'-',ta.mm) dateTime,
  ta.sumNum,
  tb.sumNum ytbSumNum,
  tc.sumNum yhbSumNum,
  -- 月同比  
    concat(ifnull(round((ta.sumNum-tb.sumNum)/tb.sumNum*100,2),0),'%') as '月同比',
    -- 月环比
    concat(ifnull(round((ta.sumNum-tc.sumNum)/tc.sumNum*100,2),0),'%') as '月环比'
FROM (
  SELECT
    t.product_id,
    year(t.create_date) as yy,
    month(t.create_date) as mm,
    sum(t.num) as sumNum
  FROM  sales t 
         WHERE product_id = 'C1001'
  GROUP BY t.product_id,yy,mm 
) ta 
-- 同比：上年同月
LEFT JOIN 
(
  SELECT
     t.product_id,
     year(t.create_date) as yy,
     month(t.create_date) as mm,
     sum(t.num) as sumNum
  FROM sales t 
     WHERE product_id = 'C1001'
  GROUP BY t.product_id,yy,mm 
) tb
ON  tb.mm = ta.mm  and tb.yy = ta.yy-1  
-- 环比：上月
LEFT JOIN 
(
  SELECT
     t.product_id,
     year(t.create_date) as yy,
     month(t.create_date) as mm,
     sum(t.num) as sumNum
  FROM sales t 
      WHERE product_id = 'C1001'
  GROUP BY t.product_id,yy,mm 
) tc
ON  ( (tc.yy = ta.yy and tc.mm = ta.mm - 1) OR (tc.yy=ta.yy - 1 AND tc.mm = 12 AND ta.mm = 1) )
ORDER BY dateTime
;

4.周环比和周同比

SELECT 
  ta.yy,
  ta.mm,
  ta.ww,
  ta.sumNum,
  tb.sumNum wtbSumNum,
  tc.sumNum whbSumNum,
    concat(ifnull(round((ta.sumNum-tb.sumNum)/tb.sumNum*100,2),0),'%') as wtbRate,
    concat(ifnull(round((ta.sumNum-tc.sumNum)/tc.sumNum*100,2),0),'%') as whbRate
FROM (
  SELECT
     t.product_id,
     year(t.create_date) as yy,
     month(t.create_date) as mm,
  week(t.create_date) as ww,
  SUM(num) as sumNum
  FROM sales t 
      WHERE product_id = 'C1001'
  GROUP BY t.product_id,yy,mm,ww
) ta 
-- 同比：上年同月
LEFT JOIN 
(
  SELECT
     t.product_id,
     year(t.create_date) as yy,
     month(t.create_date) as mm,
  week(t.create_date) as ww,
  SUM(num) as sumNum
  FROM sales t 
      WHERE product_id = 'C1001'
  GROUP BY t.product_id,yy,mm,ww
) tb
ON  tb.mm = ta.mm and tb.ww = ta.ww and tb.yy = ta.yy-1  
-- 环比：上一周
LEFT JOIN 
(
  SELECT
     t.product_id,
     year(t.create_date) as yy,
     month(t.create_date) as mm,
  week(t.create_date) as ww,
  SUM(num) as sumNum
  FROM sales t 
      WHERE product_id = 'C1001'
  GROUP BY t.product_id,yy,mm,ww
) tc
-- 1年有53周
ON  ( (tc.yy = ta.yy  and tc.ww = ta.ww - 1) OR (tc.yy=ta.yy - 1 AND tc.ww = 52 AND ta.ww = 0) )
ORDER BY yy,mm,ww
;

5.日环比和日同比

SELECT 
  ta.ymd ,
  ta.sumNum,
  tb.sumNum rtbSumNum,
  tc.sumNum rhbSumNum,
    concat(ifnull(round((ta.sumNum-tb.sumNum)/tb.sumNum*100,2),0),'%') as rtbRate,
    concat(ifnull(round((ta.sumNum-tc.sumNum)/tc.sumNum*100,2),0),'%') as rhbRate
FROM (
  SELECT
     t.product_id,
     DATE_FORMAT(t.create_date,'%Y-%m-%d') ymd,
     sum(t.num) as sumNum
  FROM sales t 
      WHERE product_id = 'C1001'
  GROUP BY t.product_id,ymd
) ta 
-- 同比：上年同期
LEFT JOIN 
(
  SELECT
     t.product_id,
     DATE_FORMAT(t.create_date,'%Y-%m-%d') ymd,
     sum(t.num) as sumNum
  FROM sales t 
      WHERE product_id = 'C1001'
  GROUP BY t.product_id,ymd
) tb
-- 计算上年同期时间
ON  ta.ymd = date_add(tb.ymd, interval 1 year)
-- 环比：上期
LEFT JOIN 
(
  SELECT
     t.product_id,
     DATE_FORMAT(t.create_date,'%Y-%m-%d') ymd,
     sum(t.num) as sumNum
  FROM sales t 
      WHERE product_id = 'C1001'
  GROUP BY t.product_id,ymd
) tc
-- 计算上1天
ON  ta.ymd = date_add(tc.ymd, interval 1 day)
ORDER BY ta.ymd
;

总结

本文主要对同环比的概念和计算公式进行了说明，并介绍了在Mysql中如何通过SQL语句对销售量的同环比进行计算。

1、计算公式：

同比增长率＝（本期数－上年同期数）／上年同期数×100％；

环比增长率＝（本期数－上期数）／上期数×100％

2、使用sql语句计算同环比的过程中，需要注意不同数据库中日期计算函数的差异。

Mysql同环比计算详解

前言

一、什么是同环比

同比

环比

对比

二、同环比计算SQL

1.数据准备

2.年环比和年同比

3.月环比和月同比

4.周环比和周同比

5.日环比和日同比

总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像