MySQL求环比同比。有视图a_date和视图b_date，求环比和同比。

Question

现在创建了视图a_date和视图b_date，求环比和同比。

视图a_date
INSERT INTO a_date(date, feiyong) VALUES ('2017-09-01', 57557.50);
INSERT INTO a_date(date, feiyong) VALUES ('2017-10-01', 57257.08);
INSERT INTO a_date(date, feiyong) VALUES ('2017-11-01', 57100.79);
INSERT INTO a_date(date, feiyong) VALUES ('2017-12-01', 57763.20);
INSERT INTO a_date(date, feiyong) VALUES ('2018-01-01', 57180.89);
INSERT INTO a_date(date, feiyong) VALUES ('2018-02-01', 57076.54);
INSERT INTO a_date(date, feiyong) VALUES ('2018-03-01', 57211.45);
INSERT INTO a_date(date, feiyong) VALUES ('2018-04-01', 57124.92);
INSERT INTO a_date(date, feiyong) VALUES ('2018-05-01', 58431.51);
INSERT INTO a_date(date, feiyong) VALUES ('2018-06-01', 56914.29);
INSERT INTO a_date(date, feiyong) VALUES ('2018-07-01', 56866.79);
INSERT INTO a_date(date, feiyong) VALUES ('2018-08-01', 56081.86);
INSERT INTO a_date(date, feiyong) VALUES ('2018-09-01', 53450.41);
INSERT INTO a_date(date, feiyong) VALUES ('2018-10-01', 52854.91);
INSERT INTO a_date(date, feiyong) VALUES ('2018-11-01', 52784.23);
INSERT INTO a_date(date, feiyong) VALUES ('2018-12-01', 53019.75);
INSERT INTO a_date(date, feiyong) VALUES ('2019-01-01', 55041.18);
INSERT INTO a_date(date, feiyong) VALUES ('2019-02-01', 54135.55);
INSERT INTO a_date(date, feiyong) VALUES ('2019-03-01', 53856.55);
INSERT INTO a_date(date, feiyong) VALUES ('2019-04-01', 54259.70);
INSERT INTO a_date(date, feiyong) VALUES ('2019-05-01', 54156.53);
INSERT INTO a_date(date, feiyong) VALUES ('2019-06-01', 55552.37);
视图b_date
INSERT INTO b_date(date, feiyong) VALUES ('2017-10-01', 57557.50);
INSERT INTO b_date(date, feiyong) VALUES ('2017-11-01', 57257.08);
INSERT INTO b_date(date, feiyong) VALUES ('2017-12-01', 57100.79);
INSERT INTO b_date(date, feiyong) VALUES ('2018-01-01', 57763.20);
INSERT INTO b_date(date, feiyong) VALUES ('2018-02-01', 57180.89);
INSERT INTO b_date(date, feiyong) VALUES ('2018-03-01', 57076.54);
INSERT INTO b_date(date, feiyong) VALUES ('2018-04-01', 57211.45);
INSERT INTO b_date(date, feiyong) VALUES ('2018-05-01', 57124.92);
INSERT INTO b_date(date, feiyong) VALUES ('2018-06-01', 58431.51);
INSERT INTO b_date(date, feiyong) VALUES ('2018-07-01', 56914.29);
INSERT INTO b_date(date, feiyong) VALUES ('2018-08-01', 56866.79);
INSERT INTO b_date(date, feiyong) VALUES ('2018-09-01', 56081.86);
INSERT INTO b_date(date, feiyong) VALUES ('2018-10-01', 53450.41);
INSERT INTO b_date(date, feiyong) VALUES ('2018-11-01', 52854.91);
INSERT INTO b_date(date, feiyong) VALUES ('2018-12-01', 52784.23);
INSERT INTO b_date(date, feiyong) VALUES ('2019-01-01', 53019.75);
INSERT INTO b_date(date, feiyong) VALUES ('2019-02-01', 55041.18);
INSERT INTO b_date(date, feiyong) VALUES ('2019-03-01', 54135.55);
INSERT INTO b_date(date, feiyong) VALUES ('2019-04-01', 53856.55);
INSERT INTO b_date(date, feiyong) VALUES ('2019-05-01', 54259.70);
INSERT INTO b_date(date, feiyong) VALUES ('2019-06-01', 54156.53);
INSERT INTO b_date(date, feiyong) VALUES ('2019-07-01', 55552.37);

MySQL版本5.6.42

Answer 1

你这算同环比只需要一张表就足够了吧。我这就以a表未真实数据的表来计算同环比吧
select t1.date,
case when t2.feiyong is not null then (t1.feiyong-t2.feiyong)/t2.feiyong else null end as 环比,
case when t3.feiyong is not null then (t1.feiyong-t3.feiyong)/t3.feiyong else null end as 同比
from a_date t1
left join
(select DATE_ADD(date,INTERVAL 1 month) as date,feiyong from a_date) t2
on
t1.date=t2.date
left join
(select DATE_ADD(date,INTERVAL 1 year) as date,feiyong from a_date) t3
on
t1.date=t3.date
;