如何修复Snowflake SQL查询中的'100051(22012):除零错误'

我尝试为每个通道的每个分段使用多项式方程提取分段系数并计算残差，但在Snowflake上运行SQL时触发除零错误，错误信息如下：

100051 (22012): Division by zero

原SQL代码：

WITH c1 AS 
(
    SELECT 
        event_ts AS time,        
        VIMS:CH1_TEMP1:mean::DOUBLE AS temperature1_channel1,
        VIMS:CH2_TEMP2:mean::DOUBLE AS temperature2_channel2,    
        FLOOR((ROW_NUMBER() OVER (ORDER BY event_ts) - 1) / 48) + 1 AS segment_id,
        SERIAL_NUMBER
    FROM 
        TABLE1
    WHERE 
        (serial_number = 'XXX00305' 
         AND event_ts >= '2018-03-07 02:30:00' 
         AND event_ts <= '2019-02-13 17:00:00')
), c2 AS 
(
    SELECT
        segment_id,
        temperature1_channel1 - AVG(temperature1_channel1) OVER (PARTITION BY segment_id) AS x1,
        temperature2_channel2 - AVG(temperature2_channel2) OVER (PARTITION BY segment_id) AS x2,
        temperature1_channel1, temperature2_channel2,
        EXTRACT(MINUTE FROM time) AS time_min,
        POWER(EXTRACT(Minute FROM time), 2)*60*60 AS time_sec_sq
    FROM 
        c1
), c3 AS 
(
    SELECT 
        segment_id,        
        AVG(temperature1_channel1) AS avg_temp1,
        AVG(temperature2_channel2) AS avg_temp2,
        AVG(x1) AS a1, AVG(x2) AS a2, 
        SUM(x1 * time_min) / SUM(time_sec_sq) AS b1,
        SUM(x2 * time_min) / SUM(time_sec_sq) AS b2,

        -- Add more b variables for additional channels
        AVG(temperature1_channel1) - (SUM(x1 * time_sec_sq) / SUM(time_sec_sq)) - (SUM(x1 * time_min) / SUM(time_sec_sq)) AS c1,
        AVG(temperature2_channel2) - (SUM(x2 * time_sec_sq) / SUM(time_sec_sq)) - (SUM(x2 * time_min) / SUM(time_sec_sq)) AS c2
    FROM 
        c2
    GROUP BY 
        segment_id
), c4 AS 
(
    SELECT
        c1.time, c1.segment_id, c1.SERIAL_NUMBER, c3.avg_temp1, c3.avg_temp2,
        -- Add more avg_temp variables for additional channels
        c3.a1, c3.a2,
        -- Add more a variables for additional channels
        c3.b1, c3.b2,
        -- Add more b variables for additional channels
        c3.c1, c3.c2, 
        -- Add more c variables for additional channels
        c2.time_min
    FROM 
        c1
    CROSS JOIN 
        c3
    JOIN 
        c2 ON c1.segment_id = c2.segment_id
    GROUP BY 
        c1.time, c1.SERIAL_NUMBER, c1.segment_id, 
        c3.avg_temp1, c3.avg_temp2, c3.a1, c3.a2, c3.b1, c3.b2, 
        c3.c1, c3.c2, c2.time_min
)
SELECT
    c4.time,
    c4.segment_id,
    c4.SERIAL_NUMBER,
    c4.avg_temp1 AS Avg_temperature1,
    c4.avg_temp2 AS Avg_temperature2,
    c4.avg_temp1 - (c4.a1 * (c4.time_min * c4.time_min) + c4.b1 * c4.time_min + c4.c1) AS residual_temperature_1,
    c4.avg_temp2 - (c4.a2 * (c4.time_min * c4.time_min) + c4.b2 * c4.time_min + c4.c2) AS residual_temperature_2            
FROM 
    c4
WHERE 
    residual_temperature_1 IS NOT NULL
    OR residual_temperature_2 IS NOT NULL

问题分析 #

1. 除零根源：在c3CTE中，SUM(time_sec_sq)被用作分母。time_sec_sq的计算式是POWER(EXTRACT(Minute FROM time), 2)*60*60，如果某个分段内所有记录的EXTRACT(Minute FROM time)结果为0，time_sec_sq会全为0，导致SUM(time_sec_sq)=0，触发除零错误。
2. JOIN逻辑错误：c4中使用CROSS JOIN c3会产生笛卡尔积，导致数据量爆炸且逻辑错误，应该改为按segment_id关联c1和c3。

修复方案 #

1. 使用NULLIF函数将分母为0的情况转换为NULL，避免除零错误；
2. 修正c4的JOIN逻辑，替换错误的CROSS JOIN为INNER JOIN；
3. 可选：如果time_sec_sq的业务逻辑是计算小时内秒数的平方，建议修正计算式为POWER(EXTRACT(EPOCH FROM time) % 3600, 2)（提取当前小时内的秒数再平方），避免分钟为0时的无效值。

修改后的SQL代码 #

WITH c1 AS 
(
    SELECT 
        event_ts AS time,        
        VIMS:CH1_TEMP1:mean::DOUBLE AS temperature1_channel1,
        VIMS:CH2_TEMP2:mean::DOUBLE AS temperature2_channel2,    
        FLOOR((ROW_NUMBER() OVER (ORDER BY event_ts) - 1) / 48) + 1 AS segment_id,
        SERIAL_NUMBER
    FROM 
        TABLE1
    WHERE 
        serial_number = 'XXX00305' 
        AND event_ts >= '2018-03-07 02:30:00' 
        AND event_ts <= '2019-02-13 17:00:00'
), c2 AS 
(
    SELECT
        segment_id,
        temperature1_channel1 - AVG(temperature1_channel1) OVER (PARTITION BY segment_id) AS x1,
        temperature2_channel2 - AVG(temperature2_channel2) OVER (PARTITION BY segment_id) AS x2,
        temperature1_channel1, temperature2_channel2,
        EXTRACT(MINUTE FROM time) AS time_min,
        -- 若业务是计算小时内秒数的平方，建议替换为以下行：
        -- POWER(EXTRACT(EPOCH FROM time) % 3600, 2) AS time_sec_sq,
        POWER(EXTRACT(Minute FROM time), 2)*60*60 AS time_sec_sq
    FROM 
        c1
), c3 AS 
(
    SELECT 
        segment_id,        
        AVG(temperature1_channel1) AS avg_temp1,
        AVG(temperature2_channel2) AS avg_temp2,
        AVG(x1) AS a1, AVG(x2) AS a2, 
        -- 用NULLIF处理分母，避免除零
        SUM(x1 * time_min) / NULLIF(SUM(time_sec_sq), 0) AS b1,
        SUM(x2 * time_min) / NULLIF(SUM(time_sec_sq), 0) AS b2,
        -- 同样处理c1、c2计算中的分母
        AVG(temperature1_channel1) 
            - (SUM(x1 * time_sec_sq) / NULLIF(SUM(time_sec_sq), 0)) 
            - (SUM(x1 * time_min) / NULLIF(SUM(time_sec_sq), 0)) AS c1,
        AVG(temperature2_channel2) 
            - (SUM(x2 * time_sec_sq) / NULLIF(SUM(time_sec_sq), 0)) 
            - (SUM(x2 * time_min) / NULLIF(SUM(time_sec_sq), 0)) AS c2
    FROM 
        c2
    GROUP BY 
        segment_id
), c4 AS 
(
    SELECT
        c1.time, 
        c1.segment_id, 
        c1.SERIAL_NUMBER, 
        c3.avg_temp1, 
        c3.avg_temp2,
        c3.a1, c3.a2,
        c3.b1, c3.b2,
        c3.c1, c3.c2, 
        c2.time_min
    FROM 
        c1
    -- 替换CROSS JOIN为按segment_id关联
    INNER JOIN c3 ON c1.segment_id = c3.segment_id
    INNER JOIN c2 ON c1.segment_id = c2.segment_id AND c1.time = c2.time
    GROUP BY 
        c1.time, c1.SERIAL_NUMBER, c1.segment_id, 
        c3.avg_temp1, c3.avg_temp2, c3.a1, c3.a2, c3.b1, c3.b2, 
        c3.c1, c3.c2, c2.time_min
)
SELECT
    c4.time,
    c4.segment_id,
    c4.SERIAL_NUMBER,
    c4.avg_temp1 AS Avg_temperature1,
    c4.avg_temp2 AS Avg_temperature2,
    c4.avg_temp1 - (c4.a1 * (c4.time_min * c4.time_min) + c4.b1 * c4.time_min + c4.c1) AS residual_temperature_1,
    c4.avg_temp2 - (c4.a2 * (c4.time_min * c4.time_min) + c4.b2 * c4.time_min + c4.c2) AS residual_temperature_2            
FROM 
    c4
WHERE 
    residual_temperature_1 IS NOT NULL
    OR residual_temperature_2 IS NOT NULL

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