mysql计算同比和环比的区别_MySQL实践之同比环比

SET global sql_mode='';

SET session sql_mode='';

SET @startDay = '2016-01-01';

SET @endDay = '2017-04-01';

SELECT

tbl.YearMonth,-- 年月

tbl.t1 AS Amount,-- 当期金额

CASE WHEN t2 = 0 OR t2 IS NULL

THEN 0.00

ELSE ((t1-t2) / t2) * 100 END AS ProportionH,-- 环比

CASE WHEN t3 = 0 OR t3 IS NULL

THEN 0.00

ELSE ((t1-t3) / t3) * 100 END AS ProportionT-- 同比

FROM

(

SELECT

DATE_FORMAT(b1.BIDate,'%Y-%m') AS YearMonth,

SUM(a1.SaleAmt) AS t1,-- 当期金额

(

SELECT

CASE WHEN a2.SaleAmt = 0 OR a2.SaleAmt IS NULL

THEN 0.00

ELSE SUM(a2.SaleAmt) END

FROM fact_sumday AS a2 INNER JOIN dim_bidate AS b2 ON a2.BIDateId = b2.BIDateId

WHERE DATE_FORMAT(b1.BIDate,'%Y-%m') = DATE_FORMAT(DATE_ADD(b2.BIDate,INTERVAL 1 MONTH),'%Y-%m')-- 表的自连接实现

) AS t2,-- 环期金额

(

SELECT

CASE WHEN a3.SaleAmt = 0 OR a3.SaleAmt IS NULL

THEN 0.00

ELSE SUM(a3.SaleAmt) END

FROM fact_sumday AS a3 INNER JOIN dim_bidate AS b3 ON a3.BIDateId = b3.BIDateId

WHERE DATE_FORMAT(b1.BIDate,'%Y-%m') = DATE_FORMAT(DATE_ADD(b3.BIDate,INTERVAL 1 YEAR),'%Y-%m')-- 表的自连接实现

) AS t3-- 同期金额

FROM fact_sumday AS a1 INNER JOIN dim_bidate AS b1 ON a1.BIDateId = b1.BIDateId

GROUP BY 1

) AS tbl

WHERE tbl.YearMonth BETWEEN DATE_FORMAT(@startDay,'%Y-%m') AND DATE_FORMAT(@endDay,'%Y-%m');