CASE求和查询除零错误规避及多日期范围平均值优化咨询

我懂你现在的困扰：既要搞定除零报错的问题，又要避免为每个日期范围重复写一堆嵌套CASE，搞得代码臃肿又难维护。下面给你两个优化方案，让代码简洁又好读。

方案1：用NULLIF简化单区间的除零处理 #

针对单个日期区间，你原来的嵌套CASE写法可以用NULLIF直接简化，它能把除数为0的情况转为NULL（也可以根据需求改成默认值，比如0）：

SELECT 
  UPC,
  -- 当数量总和为0时，结果返回NULL（想返回0就用COALESCE包裹，看下面示例）
  SUM(CASE WHEN WEEKS <= 13 THEN Cost_Amount ELSE 0 END) 
  / NULLIF(SUM(CASE WHEN WEEKS <= 13 THEN Cost_Quantity ELSE 0 END), 0) AS Avg_Cost_W1_13
FROM your_table
GROUP BY UPC;

NULLIF(a, b)的逻辑很简单：如果a等于b就返回NULL，否则返回a。这样除数为0时不会触发错误，只会得到NULL；要是你希望无数据时返回0而非NULL，就用COALESCE包裹：

COALESCE(
  SUM(CASE WHEN WEEKS <=13 THEN Cost_Amount ELSE 0 END) 
  / NULLIF(SUM(CASE WHEN WEEKS <=13 THEN Cost_Quantity ELSE 0 END), 0),
  0
) AS Avg_Cost_W1_13

方案2：预计算区间总和，解决多日期范围的冗余问题 #

如果要计算多个日期范围（比如W1-13、W14-26、W27-52），重复写CASE太麻烦。可以先用CTE（公共表表达式）预计算每个UPC在不同区间的金额和数量总和，再统一做除法：

CTE示例代码： #

WITH UpcRangeTotals AS (
  SELECT 
    UPC,
    -- 计算第一个区间的金额、数量总和
    SUM(CASE WHEN WEEKS <=13 THEN Cost_Amount ELSE 0 END) AS Amt_W1_13,
    SUM(CASE WHEN WEEKS <=13 THEN Cost_Quantity ELSE 0 END) AS Qty_W1_13,
    -- 第二个区间
    SUM(CASE WHEN WEEKS BETWEEN 14 AND 26 THEN Cost_Amount ELSE 0 END) AS Amt_W14_26,
    SUM(CASE WHEN WEEKS BETWEEN 14 AND 26 THEN Cost_Quantity ELSE 0 END) AS Qty_W14_26,
    -- 第三个区间
    SUM(CASE WHEN WEEKS >=27 THEN Cost_Amount ELSE 0 END) AS Amt_W27_52,
    SUM(CASE WHEN WEEKS >=27 THEN Cost_Quantity ELSE 0 END) AS Qty_W27_52
  FROM your_table
  GROUP BY UPC
)
SELECT 
  UPC,
  COALESCE(Amt_W1_13 / NULLIF(Qty_W1_13, 0), 0) AS Avg_Cost_W1_13,
  COALESCE(Amt_W14_26 / NULLIF(Qty_W14_26, 0), 0) AS Avg_Cost_W14_26,
  COALESCE(Amt_W27_52 / NULLIF(Qty_W27_52, 0), 0) AS Avg_Cost_W27_52
FROM UpcRangeTotals;

这种写法的优势很明显：

• 逻辑分离：先算总和再处理除法，代码结构一目了然
• 减少重复：每个区间的CASE只写一次，后续改日期范围只需调整CTE里的条件
• 易于扩展：新增日期范围时，CTE里加两行、主查询里加一行就搞定

额外小提示 #

如果你能接受无数据时返回NULL，那直接用NULLIF就行，不用套COALESCE；要是业务要求必须有默认值，COALESCE就能帮你把NULL转成0或者其他你需要的值。

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