SELECT查询中聚合函数与CASE WHEN用法及销售报表SQL错误排查

错误点梳理 #

• 语法错误1：CASE表达式前多余左括号，无匹配的右括号，导致语法解析失败。
• 语法错误2：第二个WHEN分支中ROUND函数括号匹配错误，ROUND(SUM(pred_sum)), 2)多了一个右括号，正确写法为ROUND(SUM(pred_sum), 2)。
• 关联逻辑缺陷：以sales_fact作为主表左联其他表，会丢失「仅存在预测销售额、无实际销售额」的产品月度数据，无法覆盖全量报表场景。
• 聚合逻辑问题：未对sales_fact、sales_pred先按日期和产品ID单独聚合就直接关联，若同一产品同一天存在多条实际/预测记录，会产生笛卡尔积导致求和结果偏大。
• 代码冗余：偏差值计算逻辑可通过COALESCE函数大幅简化，无需多层CASE判断。

偏差值逻辑验证 #

你给出的偏差规则完全可以用 COALESCE(预测销售额,0) - COALESCE(实际销售额,0) 实现，对应规则如下：

• 实际销售额为null：预测销售额 - 0 = 预测销售额，符合要求
• 预测销售额为null：0 - 实际销售额 = -实际销售额，符合要求
• 二者均不为空：预测销售额-实际销售额，符合要求
• 二者均为空：0-0=0，符合要求

修正后通用版SQL（支持FULL OUTER JOIN的数据库如PostgreSQL、Oracle等） #

SELECT 
    COALESCE(YEAR(sf.fact_date), YEAR(sp.pred_date)) AS 年份,
    COALESCE(MONTH(sf.fact_date), MONTH(sp.pred_date)) AS 月份,
    p.name AS 产品名称,
    ROUND(COALESCE(sf.month_fact, 0), 2) AS 实际销售额,
    ROUND(COALESCE(sp.month_pred, 0), 2) AS 预测销售额,
    ROUND(COALESCE(sp.month_pred, 0) - COALESCE(sf.month_fact, 0), 2) AS 偏差值
FROM 
    -- 先聚合计算每个产品每月的实际销售额
    (SELECT 
        prod_id,
        YEAR(fact_date) AS fact_year,
        MONTH(fact_date) AS fact_month,
        SUM(fact_sum) AS month_fact
    FROM sales_fact
    GROUP BY prod_id, fact_year, fact_month) sf
FULL OUTER JOIN 
    -- 先聚合计算每个产品每月的预测销售额
    (SELECT 
        prod_id,
        YEAR(pred_date) AS pred_year,
        MONTH(pred_date) AS pred_month,
        SUM(pred_sum) AS month_pred
    FROM sales_pred
    GROUP BY prod_id, pred_year, pred_month) sp
ON sf.prod_id = sp.prod_id AND sf.fact_year = sp.pred_year AND sf.fact_month = sp.pred_month
-- 关联产品维度表
LEFT JOIN product p ON COALESCE(sf.prod_id, sp.prod_id) = p.prod_id
ORDER BY 年份, 月份, 产品名称;

MySQL适配版SQL（MySQL不支持FULL OUTER JOIN，改用UNION ALL实现） #

SELECT 
    year,
    month,
    name,
    ROUND(SUM(month_fact), 2) AS 实际销售额,
    ROUND(SUM(month_pred), 2) AS 预测销售额,
    ROUND(SUM(month_pred) - SUM(month_fact), 2) AS 偏差值
FROM (
    -- 取实际销售数据
    SELECT 
        YEAR(fact_date) AS year,
        MONTH(fact_date) AS month,
        prod_id,
        SUM(fact_sum) AS month_fact,
        0 AS month_pred
    FROM sales_fact
    GROUP BY year, month, prod_id
    UNION ALL
    -- 取预测销售数据
    SELECT 
        YEAR(pred_date) AS year,
        MONTH(pred_date) AS month,
        prod_id,
        0 AS month_fact,
        SUM(pred_sum) AS month_pred
    FROM sales_pred
    GROUP BY year, month, prod_id
) t
LEFT JOIN product p ON t.prod_id = p.prod_id
GROUP BY year, month, name
ORDER BY year, month, name;

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