MySQL计算2021年5月各州收入环比、同比变化率问题

原始数据 #

Date         State  Revenue 
   31-01-2020     M       100
   05-05-2020     M       500
   05-05-2020     k       500
   31-05-2020     M       100
   12-04-2021     K       250
   15-04-2021     M       300
   20-05-2021     K       250
   21-05-2021     M       300

需求说明 #

筛选出2021年5月的数据，按州输出以下信息：

1. 该州2021年5月的总收入
2. 环比上月（2021年4月）的收入变化百分比
3. 同比去年同月（2020年5月）的收入变化百分比

预期输出格式：

STATE Total_Revenue  % change of revenue compared to the previous month of the state     % change of revenue compared to same month from previous year for the state
 M
 K

解决方案（MySQL示例） #

WITH monthly_revenue AS (
    -- 按州、年月分组，计算每月总收入（统一州名大小写避免差异）
    SELECT
        UPPER(State) AS STATE,
        DATE_FORMAT(STR_TO_DATE(Date, '%d-%m-%Y'), '%Y-%m') AS year_month,
        SUM(Revenue) AS monthly_total
    FROM your_table_name
    GROUP BY UPPER(State), DATE_FORMAT(STR_TO_DATE(Date, '%d-%m-%Y'), '%Y-%m')
)
SELECT
    STATE,
    -- 获取2021年5月总收入
    (SELECT monthly_total FROM monthly_revenue mr2 WHERE mr2.STATE = mr.STATE AND mr2.year_month = '2021-05') AS Total_Revenue,
    -- 计算环比上月变化百分比（处理除数为0的情况）
    ROUND(
        IFNULL(
            ((SELECT monthly_total FROM monthly_revenue mr2 WHERE mr2.STATE = mr.STATE AND mr2.year_month = '2021-05') - 
             (SELECT monthly_total FROM monthly_revenue mr2 WHERE mr2.STATE = mr.STATE AND mr2.year_month = '2021-04')) / 
            NULLIF((SELECT monthly_total FROM monthly_revenue mr2 WHERE mr2.STATE = mr.STATE AND mr2.year_month = '2021-04'), 0) * 100,
            0
        ),
        2
    ) AS '% change of revenue compared to the previous month of the state',
    -- 计算同比去年同月变化百分比
    ROUND(
        IFNULL(
            ((SELECT monthly_total FROM monthly_revenue mr2 WHERE mr2.STATE = mr.STATE AND mr2.year_month = '2021-05') - 
             (SELECT monthly_total FROM monthly_revenue mr2 WHERE mr2.STATE = mr.STATE AND mr2.year_month = '2020-05')) / 
            NULLIF((SELECT monthly_total FROM monthly_revenue mr2 WHERE mr2.STATE = mr.STATE AND mr2.year_month = '2020-05'), 0) * 100,
            0
        ),
        2
    ) AS '% change of revenue compared to same month from previous year for the state'
FROM monthly_revenue mr
WHERE mr.year_month = '2021-05'
GROUP BY STATE;

逻辑说明 #

1. CTE预计算：先将原始数据按州、年月聚合，得到每个州每月的总收入，简化后续环比、同比的查询逻辑。
2. 环比计算：用2021年5月收入减去4月收入，除以4月收入得到变化率，用NULLIF避免除数为0的错误，ROUND保留两位小数。
3. 同比计算：逻辑同环比，替换为2020年5月的收入数据。
4. 结果筛选：仅保留2021年5月的州记录，确保输出符合需求。

最终计算结果 #

内容的提问来源于stack exchange，提问作者Harish reddy