Блог

Значение масла иногда равно нулю. Мне нужно исключение деления на ноль, и оператор null if все еще выдает этот код ошибки. Не уверен, что не так с синтаксисом.
ОШИБКА: деление на ноль Состояние SQL: 22012

 WITH query1 AS (
   SELECT well_id, produced_at, oil,
  (EXTRACT(EPOCH FROM age(produced_at,
                          LAG(produced_at) OVER w))/3600)::int as hourly_rate
                          FROM public.production
WINDOW w AS (PARTITION BY well_id ORDER BY well_id, produced_at 
             ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
     
),
query2 as (
   select Well_id, produced_at, nullif(oil,0), hourly_rate,
   (nullif(oil,0)/hourly_rate*6) as Last_Six_Hours_Of_Production,
   (nullif(oil,0)/hourly_rate*12) as Last_Twelve_Hours_Of_Production
    from query1
)
Select *
from query2; 
 

Я думаю, что вам также нужно добавить некоторую NULLIF проверку к знаменателю и добавить COALESCE предложение.

 WITH query1 AS (
   SELECT well_id, produced_at, oil,
  (EXTRACT(EPOCH FROM age(produced_at,
                          LAG(produced_at) OVER w))/3600)::int as hourly_rate
                          FROM public.production
WINDOW w AS (PARTITION BY well_id ORDER BY well_id, produced_at 
             ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
     
),
query2 as (
   select Well_id, produced_at, nullif(oil,0), hourly_rate,
   COALESCE((nullif(oil,0)/nullif(hourly_rate*6,0)),0) as Last_Six_Hours_Of_Production,
   COALESCE((nullif(oil,0)/nullif(hourly_rate*12,0)),0) as Last_Twelve_Hours_Of_Production
    from query1
)
Select *
from query2;