sql - 计算雪花中的警报洪水

Question

我正在尝试在雪花中进行警报洪水计算。我使用雪花窗函数创建了以下数据集。因此，如果该值大于或等于 3，则警报洪水将开始，对于下一个 0 值，它将结束。所以在下面的例子中，警报洪水从“9:51”开始，在“9:54”结束，持续了 3 分钟。下一次洪水从“9:57”开始，在“10:02”结束，即5 分钟。仅供引用，9:59 的值是 3，但是由于洪水已经开始，我们不必考虑它。下一次洪水是在 10:03 但没有 0 值，所以我们必须考虑边缘值 10:06。
所以在洪水中的总时间是 3+5+4=12 分钟。

   DateTime    Value
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

所以，简而言之，我期待低于输出



我在 SQL 下尝试过，但它没有给我正确的输出，它在第二次洪水时间失败(因为在下一个 0 之前再次有值 3)

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 t;

Answer 1

我不是最引以为豪的代码，但它有效并提供了一个起点。我相信它可以被清理或简化。我还没有评估较大表的性能。

我使用的关键见解是，如果将 date_diff 添加到日期，那么您可以找到它们都添加到相同值的情况，这意味着它们都计入相同的“0”记录。如果没有别的，希望这个概念是有帮助的。

此外，第一个 cte 是在结果结束时获得 4 的半hacky 方法。

--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;