mysql如果计算本月变动/本月增幅/同比变动/同比增幅？

问题描述：

原数据

第一列是id, 第二列是日期（都是每个月的最后的一天），第三列是一个月的销售额。
比如第一行的意思是id为1001,2022年7月的销售额是500。

id	date	sales
1001	20220731	500
1001	20210731	600
1001	20220630	800
1002	20220731	400
1002	20210731	300
1002	20220630	100
1003	20220731	600
1003	20210731	400
1003	20220630	600
1004	20220731	800
1004	20210731	100
1004	20220630	300

最终结果

id	this_month	this_month_sales	last_month_sales	last_year_sales	本月增幅	本月变动	同比增幅	同比变动
1001	Jul-22	500	800	600	0.60%	300	-0.17%	-100
1002	Jul-22	400	100	300	-0.75%	-300	0.33%	100
1003	Jul-22	600	600	400	0.00%	0	0.50%	200
1004	Jul-22	800	300	100	-0.63%	-500	7.00%	700

解题思路：

创建三张临时表，表名分别为tem_this_month、tem_next_month、tem_next_year。
三张表的columns分别为：

表名	columns
tem_this_month:	id, date, this_month, next_month, next_year,sales
tem_next_month:	id, next_month, sales
tem_next_year:	id, next_year, sales

然后left join三张表

具体过程及代码

1. 创建三张临时表

1.1 第一张临时表tem_this_month

create temporary table tem_this_month as (select id,
                                                 date,
                                                 date_format(date, '%Y-%m')                             as this_month,
                                                 date_format(date_sub(date, interval -1 month), '%Y-%m') as next_month,
                                                 date_format(date_sub(date, interval -1 year), '%Y-%m')  as next_year,
                                                 sales
                                          from mysql_xox
);
select *
from tem_this_month;

tem_this_month

1.2 第二张临时表tem_next_month

create temporary table tem_next_month as (select id,
                                                 date_format(date_sub(date, interval -1 month), '%Y-%m') as next_month,
                                                 sales
                                          from mysql_xox
);
select *
from tem_next_month;

tem_next_month

1.3 第三张临时表tem_next_year

create temporary table tem_next_year as (select id,
                                                    date_format(date_sub(date, interval -1 year), '%Y-%m') as 'next_year',
                                                    sales
                                             from mysql_xox
);
select *
from tem_next_year;

tem_next_year

2. left join三张表并计算

select a.id,
       a.this_month,
       date_format(date_sub(a.date, interval 1 month), '%Y-%m') as last_month,
        date_format(date_sub(a.date, interval 1 year), '%Y-%m')  as last_year,
       a.sales                                                                                 as this_month_sales,
       b.sales                                                                                 as last_month_sales,
       c.sales                                                                                 AS last_year_sales,
       case
           when a.sales is not null and b.sales is not null
               then concat(round((b.sales - a.sales) / a.sales,2),'%') end                                      as 本月增幅,
       case
           when a.sales is not null and b.sales is not null
               then b.sales - a.sales end                                                    as 本月变动,
       case
           when this_month is not null and c.sales is not null
               then concat(round((a.sales - c.sales) / c.sales,2),'%') end                                      as 同比增幅,
       case when this_month is not null and c.sales is not null then a.sales - c.sales end as 同比变动

from tem_this_month a
         left join tem_next_month b on a.id = b.id and a.this_month = b.next_month
         left join tem_next_year c on a.id = c.id and a.this_month = c.next_year;

精简版：

select a.id,
       a.this_month,
       a.sales                                                                                 as this_month_sales,
       b.sales                                                                                 as last_month_sales,
       c.sales                                                                                 AS last_year_sales,
       case
           when a.sales is not null and b.sales is not null
               then concat(round((b.sales - a.sales) / a.sales,2),'%') end                                      as 本月增幅,
       case
           when a.sales is not null and b.sales is not null
               then b.sales - a.sales end                                                    as 本月变动,
       case
           when this_month is not null and c.sales is not null
               then concat(round((a.sales - c.sales) / c.sales,2),'%') end                                      as 同比增幅,
       case when this_month is not null and c.sales is not null then a.sales - c.sales end as 同比变动

from tem_this_month a
         left join tem_next_month b on a.id = b.id and a.this_month = b.next_month
         left join tem_next_year c on a.id = c.id and a.this_month = c.next_year
where b.sales is not null and c.sales is not null;