MySQL计算同比和环比

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

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

  • 同比：通常是指今年第n月与去年第n月相比。同比发展速度主要是为了消除季节变动的影响，用以说明本期发展水平与去年同期发展水平对比而达到的相对发展速度。
  • 环比：通常是指表示连续2个单位周期（比如连续两月）内的量的变化比。环比包括两种：环比增长速度和环比发展速度。

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

同比增长率：
Java同比增长和环比增长 同比与环比增长_mysql

环比增长率：
Java同比增长和环比增长 同比与环比增长_数据仓库_02

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

Java同比增长和环比增长 同比与环比增长_MySQL_03

创建订单明细表并向订单明细表中添加数据
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');

Java同比增长和环比增长 同比与环比增长_数据仓库_04

计算同比和环比

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

结果：

Java同比增长和环比增长 同比与环比增长_mysql_05

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

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

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