Mysql 计算同比，环比

数据链接参考：

(7条消息) MySql计算环比_BlueKitty的博客-CSDN博客_mysql 环比

(7条消息) hive SQL实现占比、同比、环比计算（lag函数，lead函数）_云舒s的博客-CSDN博客_sql占比函数

1、

创建测试数据：

CREATE TABLE `tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `date` date DEFAULT NULL COMMENT '日期',
  `num` int(11) DEFAULT NULL COMMENT '数量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
 
INSERT INTO tb (id, date, num) VALUES ('1', '2020-01-01', '500');
INSERT INTO tb (id, date, num) VALUES ('2', '2020-01-02', '704');
INSERT INTO tb (id, date, num) VALUES ('3', '2020-01-03', '945');
INSERT INTO tb (id, date, num) VALUES ('4', '2020-01-04', '343');
INSERT INTO tb (id, date, num) VALUES ('5', '2020-01-05', '343');
INSERT INTO tb (id, date, num) VALUES ('6', '2020-01-06', '426');
INSERT INTO tb (id, date, num) VALUES ('7', '2020-01-07', '697');
INSERT INTO tb (id, date, num) VALUES ('8', '2020-01-08', '421');
INSERT INTO tb (id, date, num) VALUES ('9', '2020-01-09', '746');
INSERT INTO tb (id, date, num) VALUES ('10', '2020-01-10', '504');
INSERT INTO tb (id, date, num) VALUES ('11', '2020-01-11', '474');
INSERT INTO tb (id, date, num) VALUES ('12', '2020-01-12', '763');
INSERT INTO tb (id, date, num) VALUES ('13', '2020-01-13', '135');
INSERT INTO tb (id, date, num) VALUES ('14', '2020-01-14', '202');
INSERT INTO tb (id, date, num) VALUES ('15', '2020-01-15', '561');
INSERT INTO tb (id, date, num) VALUES ('16', '2020-01-16', '219');
INSERT INTO tb (id, date, num) VALUES ('17', '2020-01-17', '779');
INSERT INTO tb (id, date, num) VALUES ('18', '2020-01-18', '673');
INSERT INTO tb (id, date, num) VALUES ('19', '2020-01-19', '906');
INSERT INTO tb (id, date, num) VALUES ('20', '2020-01-20', '951');

2、数据分析：

数据环比 = (本期-上期)/abs(上期) 　　

3、"今日数据："

select date,num from tb

日期加一天(这里计算今天与昨天的数据环比)

select DATE_ADD(date,INTERVAL 1 day),date,num from tb

通过日期关联数据：

select t.date, t.num tnum, y.num ynum,

case
when y.num is null or y.num = 0 then 0.00
else round((t.num - y.num) / y.num * 100, 2)
end ratio
from(
select date,num
from tb
) t
left join (
select date_add(date,INTERVAL 1 DAY) tomorrow, num
from tb
) y
on t.date = y.tomorrow

纸上得来终觉浅，绝知此事要躬行。