Mysql 计算同比，环比

     数据链接参考：

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

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

1、

      创建测试数据：

     

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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

     

 

 

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

?

1	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