-1

I am trying to use the polynomial equation for each segment in each channel to extract the coefficients from those segment and calculate the residuals. I got the error below when I tried to run the query on snowflake below. Could you please advise me how to fix this error? I need really your help to fix this error.

The error is

100051 (22012): Division by zero

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohamed
  • 3
  • 3
  • What’s confusing you about the error message? You can’t divide by zero and somewhere in your code you have a divisor that’s evaluating to zero. You need to find where this is happening and fix it – NickW Jul 08 '23 at 20:18
  • Thank you for your help. The error I have received coming from c4 table. It is not clear to me what is the problem. Could you please give me some more help in trying to solve this issue? – Mohamed Jul 08 '23 at 20:23
  • i would expect that c3 is the culprit and not c4 as it is making divisions – nbk Jul 08 '23 at 20:28
  • You are correct. The error coming exactly in this part of query in c3 table 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(engine_exhaust_temperature1) - (SUM(x1 * time_sec_sq) / SUM(time_sec_sq)) - (SUM(x1 * time_min) / SUM(time_sec_sq)) AS c1, AVG(engine_exhaust_temperature2) - (SUM(x2 * time_sec_sq) / SUM(time_sec_sq)) - (SUM(x2 * time_min) / SUM(time_sec_sq)) AS c2 – Mohamed Jul 08 '23 at 20:30
  • I think the issue coming from here exactly 'SUM(time_sec_sq)'. Do you have any idea how to overcome on this issue using sql? – Mohamed Jul 08 '23 at 20:31
  • chekc with a case when if time_sec_sq is null or 0 and then decide what you want as result – nbk Jul 08 '23 at 20:33
  • I tried case when but not working 'CASE WHEN time_sec_sq = 0 THEN 0 ELSE (SUM(x1 * time_min) / SUM(time_sec_sq) AS b1),' – Mohamed Jul 08 '23 at 20:43
  • I think case when should be like ''CASE WHEN SUM(time_sec_sq) = 0 THEN 0 ELSE SUM(x1 * time_min) / NULLIF(SUM(time_sec_sq), 0) END AS b1 – Mohamed Jul 08 '23 at 20:54
  • 1) Comment out every calculated column that has a division 2) Add in just the divisor portion of each of the commented out columns, as their own columns 3) Identify which of these columns contain nulls, zeros or any other values that will cause errors 4) Fix these issues – NickW Jul 08 '23 at 20:54
  • Actually, the columns: b1,b2,c1,c2 contains null or zeros and this what I did to fix the issues: in b1 and c1 as an example. CASE WHEN SUM(time_sec_sq) = 0 THEN 0 ELSE SUM(x1 * time_min) / NULLIF(SUM(time_sec_sq), 0) END AS b1, CASE WHEN SUM(time_sec_sq) = 0 THEN 0 ELSE AVG(engine_exhaust_temperature1) - (SUM(x1 * time_sec_sq) / NULLIF(SUM(time_sec_sq),0)) - (SUM(x1 * time_min) / NULLIF(SUM(time_sec_sq),0)) END AS c1, – Mohamed Jul 08 '23 at 21:19

1 Answers1

0

As the error message indicates, the divisor in one of the divisions in your query is zero.

You could try using one of Snowflake's zero-safe division functions such as DIV0(), which returns 0 if the divisor is 0.

Looking at your query, my prime suspect would be the sum over the time_sec_sq column in the c3 query (CTE). Rewriting it like this should give you what you expected:

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, 
        DIV0(SUM(x1 * time_min), SUM(time_sec_sq)) AS b1,
        DIV0(SUM(x2 * time_min), SUM(time_sec_sq)) AS b2,

        -- Add more b variables for additional channels
        AVG(temperature1_channel1) - DIV0(SUM(x1 * time_sec_sq), SUM(time_sec_sq)) - DIV0((SUM(x1 * time_min), SUM(time_sec_sq))) AS c1,
        AVG(temperature2_channel2) - DIV0(SUM(x2 * time_sec_sq), SUM(time_sec_sq)) - DIV0((SUM(x2 * time_min), SUM(time_sec_sq))) AS c2
    FROM 
        c2
    GROUP BY 
        segment_id
)

Snowflake documentation:

lziegler
  • 41
  • 3