如何编写SQL脚本计算月度销售额较上年的同比增长率？

嘿，你已经搞定2018年月度销售总额的查询了，接下来计算2018年各月份较2017年的销售额增长率其实没那么复杂，咱们一步步来拆解：

核心思路 #

要计算同比增长率，我们需要先分别提取2017年和2018年的月度销售数据，然后将两年对应月份的数据关联起来，最后用公式 ((今年销售额 - 去年销售额) / 去年销售额) * 100 计算增长率。同时要处理两种特殊情况：

• 2017年对应月份没有销售数据（结果为NULL）
• 2017年对应月份销售额为0（避免除以0的错误）

完整SQL脚本（含年度总计） #

这里用CTE（公共表表达式）来拆分逻辑，让代码更清晰易读，还保留了你原查询中的年度总计：

WITH sales_2017 AS (
    -- 获取2017年月度销售总额+年度总计
    SELECT 
        DATE_FORMAT(TRANSACTION_DATE, '%M') AS MONTH,
        ROUND(SUM(SALES_AMOUNT), 2) AS TOTAL_2017
    FROM SALES
    WHERE YEAR(TRANSACTION_DATE) = 2017
    GROUP BY MONTH
    UNION ALL
    SELECT 'Grand Total' AS MONTH, ROUND(SUM(SALES_AMOUNT), 2) AS TOTAL_2017
    FROM SALES
    WHERE YEAR(TRANSACTION_DATE) = 2017
),
sales_2018 AS (
    -- 获取2018年月度销售总额+年度总计（和你原查询逻辑一致）
    SELECT 
        DATE_FORMAT(TRANSACTION_DATE, '%M') AS MONTH,
        ROUND(SUM(SALES_AMOUNT), 2) AS TOTAL_2018
    FROM SALES
    WHERE YEAR(TRANSACTION_DATE) = 2018
    GROUP BY MONTH
    UNION ALL
    SELECT 'Grand Total' AS MONTH, ROUND(SUM(SALES_AMOUNT), 2) AS TOTAL_2018
    FROM SALES
    WHERE YEAR(TRANSACTION_DATE) = 2018
)
-- 关联两年数据并计算增长率
SELECT 
    COALESCE(s18.MONTH, s17.MONTH) AS MONTH,
    s18.TOTAL_2018,
    s17.TOTAL_2017,
    CASE
        WHEN s17.TOTAL_2017 IS NULL THEN 'No 2017 Data'
        WHEN s17.TOTAL_2017 = 0 THEN 'Undefined (2017 Total was 0)'
        ELSE ROUND(((s18.TOTAL_2018 - s17.TOTAL_2017) / s17.TOTAL_2017) * 100, 2) || '%'
    END AS YoY_Growth
FROM sales_2018 s18
LEFT JOIN sales_2017 s17 ON s18.MONTH = s17.MONTH
-- 确保月份按自然顺序排列，年度总计放在最后
ORDER BY 
    CASE WHEN s18.MONTH = 'Grand Total' THEN 13 
         ELSE FIELD(s18.MONTH, 'January', 'February', 'March', 'April', 'May', 'June', 
                   'July', 'August', 'September', 'October', 'November', 'December') 
    END;

关键细节解释 #

1. CTE拆分数据：用sales_2017和sales_2018两个CTE分别计算两年的月度和年度总计，逻辑清晰且易于维护。
2. LEFT JOIN关联：确保2018年的所有月份（包括总计）都能显示，即使2017年没有对应数据。
3. CASE处理异常：避免因去年数据为NULL或0导致的计算错误，返回友好提示。
4. 月份排序：用FIELD函数强制月份按自然顺序排列，避免字符串排序导致的顺序混乱（比如April排在August前面）。

简化版（仅月度数据） #

如果不需要年度总计，只保留月度数据，可以简化成：

WITH sales_2017 AS (
    SELECT 
        DATE_FORMAT(TRANSACTION_DATE, '%M') AS MONTH,
        ROUND(SUM(SALES_AMOUNT), 2) AS TOTAL_2017
    FROM SALES
    WHERE YEAR(TRANSACTION_DATE) = 2017
    GROUP BY MONTH
),
sales_2018 AS (
    SELECT 
        DATE_FORMAT(TRANSACTION_DATE, '%M') AS MONTH,
        ROUND(SUM(SALES_AMOUNT), 2) AS TOTAL_2018
    FROM SALES
    WHERE YEAR(TRANSACTION_DATE) = 2018
    GROUP BY MONTH
)
SELECT 
    s18.MONTH,
    s18.TOTAL_2018,
    COALESCE(s17.TOTAL_2017, 0) AS TOTAL_2017,
    CASE
        WHEN s17.TOTAL_2017 IS NULL THEN 'No 2017 Data'
        WHEN s17.TOTAL_2017 = 0 THEN 'Undefined (2017 Total was 0)'
        ELSE ROUND(((s18.TOTAL_2018 - s17.TOTAL_2017) / s17.TOTAL_2017) * 100, 2) || '%'
    END AS YoY_Growth
FROM sales_2018 s18
LEFT JOIN sales_2017 s17 ON s18.MONTH = s17.MONTH
ORDER BY FIELD(s18.MONTH, 'January', 'February', 'March', 'April', 'May', 'June', 
               'July', 'August', 'September', 'October', 'November', 'December');

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