Рассчитать тревожный паводок в снежинке

Версия javascript udf:

select d, v, iff(3<=v and 1=row_number() over (partition by N order by d),
    count(*) over (partition by N), null) trig_duration
from t, lateral flood_count(t.v::float) 
order by d;

Где flood_count () определяется как:

create or replace function flood_count(V float) 
returns table (N float)
language javascript AS
$${

  initialize: function() { 
    this.n = 0 
    this.flood = false
  },

  processRow: function(row, rowWriter) { 
    if (3<=row.V && !this.flood) {
        this.flood = true
        this.n++
    }
    else if (0==row.V) this.flood=false
    rowWriter.writeRow({ N: this.flood ? this.n : null })  
  },

}$$;

Предполагая этот ввод:

create or replace table t as
select to_timestamp(d, 'mm/dd/yyyy hh:mi') d, v 
from values
    ('3/10/2020 9:50',  1),
    ('3/10/2020 9:51',  3),
    ('3/10/2020 9:52',  1),
    ('3/10/2020 9:53',  2),
    ('3/10/2020 9:54',  0),
    ('3/10/2020 9:55',  0),
    ('3/10/2020 9:56',  1),
    ('3/10/2020 9:57',  3),
    ('3/10/2020 9:58',  2),
    ('3/10/2020 9:59',  3),
    ('3/10/2020 10:00', 2),
    ('3/10/2020 10:01', 2),
    ('3/10/2020 10:02', 0),
    ('3/10/2020 10:03', 3),
    ('3/10/2020 10:04', 1),
    ('3/10/2020 10:05', 1),
    ('3/10/2020 10:06', 1)
    t(d,v)
;

Я не очень горжусь этим кодом, но он работает и дает отправную точку. Я уверен, что это можно исправить или упростить. и я не оценивал производительность для больших столов.

Ключевой вывод, который я использовал, заключается в том, что если вы добавите date_diff к дате, вы можете найти ситуации, когда они оба прибавляются к одному и тому же значению, что означает, что они оба ведут счет к одной и той же записи «0». Надеюсь, эта концепция поможет, как минимум.

Кроме того, первый cte - это полу-хакерский способ получить 4 в конце ваших результатов.

--Add a fake zero at the end of the table to provide a value for
-- comparing high values that have not been resolved
-- added a flag so this fake value can be removed later
with fakezero as
(
SELECT datetime, value, 1 flag
FROM test

UNION ALL

SELECT dateadd(minute, 1, max(datetime)) datetime, 0 value, 0 flag
FROM test  
)

-- Find date diffs between high values and subsequent low values
,diffs as (
select t.*,
       (case when value >= 3
             then datediff(minute,
                           datetime,
                           min(case when value = 0 then datetime end) over (order by datetime desc)
                          )
        end) as diff_minutes
from fakezero t
)

--Fix cases where two High values are "resolved" by the same low value
--i.e. when adding the date_diff to the datetime results in the same timestamp
-- this means that the prior high value record that still hasn't been "resolved"
select
  datetime
  ,value
  ,case when 
      lag(dateadd(minute, diff_minutes, datetime)) over(partition by value order by datetime)
      = dateadd(minute, diff_minutes, datetime)
    then null 
    else diff_minutes 
  end as diff_minutes
from diffs
where flag = 1
order by datetime;

WITH data as (
  select time::timestamp as time, value from values
    ('2020-03-10 9:50', 1 ),
    ('2020-03-10 9:51', 3 ),
    ('2020-03-10 9:52', 1 ),
    ('2020-03-10 9:53', 2 ),
    ('2020-03-10 9:54', 0 ),
    ('2020-03-10 9:55', 0 ),
    ('2020-03-10 9:56', 1 ),
    ('2020-03-10 9:57', 3 ),
    ('2020-03-10 9:58', 2 ),
    ('2020-03-10 9:59', 3 ),
    ('2020-03-10 10:00', 2 ),
    ('2020-03-10 10:01', 2 ),
    ('2020-03-10 10:02', 0 ),
    ('2020-03-10 10:03', 3 ),
    ('2020-03-10 10:04', 1 ),
    ('2020-03-10 10:05', 1 ),
    ('2020-03-10 10:06', 1 )
     s( time, value)
) 
select 
    a.time
    ,a.value
    ,min(trig_time)over(partition by reset_time_group order by time) as first_trigger_time
    ,iff(a.time=first_trigger_time, datediff('minute', first_trigger_time, reset_time_group), null) as trig_duration
from (
select d.time
   ,d.value 
   ,iff(d.value>=3,d.time,null) as trig_time
   ,iff(d.value=0,d.time,null) as reset_time
   ,max(time)over(order by time ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) as max_time
   ,coalesce(lead(reset_time)ignore nulls over(order by d.time), max_time) as lead_reset_time
   ,coalesce(reset_time,lead_reset_time) as reset_time_group
from data as d
) as a
order by time;

это дает результаты, которые вы, кажется, ожидаете / описываете ..

TIME                     VALUE  FIRST_TRIGGER_TIME         TRIG_DURATION
2020-03-10 09:50:00.000    1        
2020-03-10 09:51:00.000    3    2020-03-10 09:51:00.000    3
2020-03-10 09:52:00.000    1    2020-03-10 09:51:00.000    
2020-03-10 09:53:00.000    2    2020-03-10 09:51:00.000    
2020-03-10 09:54:00.000    0    2020-03-10 09:51:00.000    
2020-03-10 09:55:00.000    0        
2020-03-10 09:56:00.000    1        
2020-03-10 09:57:00.000    3    2020-03-10 09:57:00.000    5
2020-03-10 09:58:00.000    2    2020-03-10 09:57:00.000    
2020-03-10 09:59:00.000    3    2020-03-10 09:57:00.000    
2020-03-10 10:00:00.000    2    2020-03-10 09:57:00.000    
2020-03-10 10:01:00.000    2    2020-03-10 09:57:00.000    
2020-03-10 10:02:00.000    0    2020-03-10 09:57:00.000    
2020-03-10 10:03:00.000    3    2020-03-10 10:03:00.000    3
2020-03-10 10:04:00.000    1    2020-03-10 10:03:00.000    
2020-03-10 10:05:00.000    1    2020-03-10 10:03:00.000    
2020-03-10 10:06:00.000    1    2020-03-10 10:03:00.000    

Итак, как это работает, мы находим время срабатывания и время сброса, затем вычисляется max_time для случая края последней строки. После этого мы находим следующие форварды reset_time и используем max_time, если его нет, а затем выбираем текущее время сброса или предыдущее lead_reset_time, для работы, которую вы здесь выполняете, эти шаги можно игнорировать, так как ваши данные не могут запускать и сбрасывать тот же ряд. И, учитывая, что мы выполняем математические вычисления в строке триггеров, строка сброса, зная, в какой группе она находится, не имеет значения.

Затем мы переходим на новый слой выбора, так как мы достигли предела снежинок для вложенного / взаимосвязанного SQL, и делаем min через reset_group, чтобы найти время первого триггера, которое мы затем сравниваем со временем строки и делаем разницу по дате.

Кстати, date_diff немного наивен в своей математике, а '2020-01-01 23:59:59' '2020-01-02 00:00:01' разделены на 2 секунды, но это 1 минута и 1 с интервалом в час и 1 день, потому что функция приводит метки времени к выбранной единице (и усекает), а затем различает эти результаты.

Чтобы получить финальный пакет со значением 4, как указано в запросе, измените строку lead_reset_time на:

,coalesce(lead(reset_time)ignore nulls over(order by d.time), dateadd('minute', 1, max_time)) as lead_reset_time

чтобы переместить это max_time вперед на одну минуту, если вы хотите предположить, что за пределами данных в будущем время существующее состояние строки 10:06 действительно в течение 1 минуты. Я бы не сделал этого ... но вот код, который вам нужен ...