SQL计算累积占比时出现除以零错误，求解决方案

你的SQL触发除零错误的原因是：当某个grp + category分组内所有customers的值都≤10时，customers_adj会全为0，导致分母SUM(customers_adj) OVER (PARTITION BY grp, category)的计算结果为0，触发除法运算错误。

以下两种方案可以解决这个问题：

方案一：使用NULLIF处理分母 #

通过NULLIF将分母为0的情况转为NULL，此时除法结果会变为NULL，避免报错：

SELECT 
  GRP,  
  category, 
  price, 
  units, 
  CASE WHEN customers > 10 THEN customers ELSE 0 END AS customers_adj,
  1.00000 * (
    SUM(customers_adj) OVER(PARTITION BY grp, category ORDER BY 
        FIGURE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )) / NULLIF(SUM(customers_adj) OVER (PARTITION BY grp, category), 0) AS cum_max_price_cust
FROM 
  table_1

方案二：用CASE WHEN自定义除零场景的返回值 #

如果需要在除零时返回特定值（比如0）而非NULL，可以用CASE WHEN明确判断：

SELECT 
  GRP,  
  category, 
  price, 
  units, 
  CASE WHEN customers > 10 THEN customers ELSE 0 END AS customers_adj,
  CASE 
    WHEN SUM(customers_adj) OVER (PARTITION BY grp, category) = 0 THEN 0  -- 可根据业务需求改为NULL或其他值
    ELSE 1.00000 * (
      SUM(customers_adj) OVER(PARTITION BY grp, category ORDER BY 
          FIGURE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      )) / SUM(customers_adj) OVER (PARTITION BY grp, category) 
  END AS cum_max_price_cust
FROM 
  table_1

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