mysql计算同比环比

使用mysql 来 计算同比和环比

– 同比
SELECT
t1.now_year AS 年份,
t1.now_month AS 月份,
CASE

WHEN now_month_num IS NULL
OR now_month_num = 0 THEN
0 ELSE now_month_num
END 本月销售量,
CASE

WHEN last_month_num IS NULL
OR last_month_num = 0 THEN
0 ELSE last_month_num
END 去年同期销售量,
CASE

WHEN last_month_num IS NULL
OR last_month_num = 0 THEN
0 ELSE
round(( now_month_num - last_month_num ) / last_month_num ,2)
END 月订单量同比
FROM(SELECT DATE_FORMAT(a.date, ‘%Y’ ) AS now_year,DATE_FORMAT(a.date, ‘%Y-%m’ ) AS now_month,sum( a.num ) AS now_month_num FROM yearmonth AS a GROUP BY a.date) t1
LEFT JOIN (
SELECT
DATE_FORMAT( DATE_ADD( b.date, INTERVAL 1 year ), ‘%Y-%m’ )
AS now_month,
sum( b.num ) AS last_month_num
FROM
yearmonth b
GROUP BY
b.date
) AS t2 ON t1.now_month = t2.now_month;

– 环比

SELECT
t1.now_year AS 年份,
t1.now_month 月份,
CASE

WHEN now_month_num IS NULL
OR now_month_num = 0 THEN
0 ELSE now_month_num
END 本月订单量,
CASE

WHEN last_month_num IS NULL
OR last_month_num = 0 THEN
0 ELSE last_month_num
END 上个月订单量,
CASE

WHEN last_month_num IS NULL
OR last_month_num = 0 THEN
0 ELSE
( CONVERT ( ( ( now_month_num - last_month_num ) / last_month_num ) * 100, DECIMAL ( 10, 2 ) ) )
END 月订单量环比
FROM
(
SELECT
DATE_FORMAT( a.date, ‘%Y’ ) AS now_year,
DATE_FORMAT( a.date, ‘%Y-%m’ ) AS now_month,
sum( a.num ) AS now_month_num
FROM
yearmonth AS a
GROUP BY
a.date
) t1
LEFT JOIN (
SELECT
DATE_FORMAT( DATE_ADD( b.date, INTERVAL 1 MONTH ), ‘%Y-%m’ )
AS now_month,
sum( b.num ) AS last_month_num
FROM
yearmonth b
GROUP BY
b.date
) AS t2 ON t1.now_month = t2.now_month;
– round() 优化
SELECT
t1.now_year AS 年份,
t1.now_month 月份,
CASE

WHEN now_month_num IS NULL
OR now_month_num = 0 THEN
0 ELSE now_month_num
END 本月订单量,
CASE

WHEN last_month_num IS NULL
OR last_month_num = 0 THEN
0 ELSE last_month_num
END 上个月订单量,
CASE

WHEN last_month_num IS NULL
OR last_month_num = 0 THEN
0 ELSE
round(( now_month_num - last_month_num ) / last_mont