计算雪花中的警报洪水
计算雪花中的警报洪水
提问于 2020-03-24 16:05:52
我正在试着在雪花中做一个警报洪水计算。我使用雪花窗口函数创建了下面的数据集。因此，如果该值大于或等于3，则警报泛洪将开始，而对于下一个0值，警报泛洪将结束。因此，在下面的示例中，警报洪水在“9:51”开始，在"9:54“结束，持续了3分钟。下一次洪水在"9:57”开始，在"10:02“结束，即5 minutes.FYI，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;回答 3
Stack Overflow用户
回答已采纳
发布于 2020-03-25 04:59:13
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)
;Stack Overflow用户
发布于 2020-03-25 02:58:30
我不是最引以为豪的这段代码，但它的工作，并提供了一个起点。我确信它可以被清理或简化。而且我还没有评估过较大表的性能。
我使用的关键洞察力是，如果您将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;Stack Overflow用户
发布于 2020-03-25 04:20:38
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，对于您在这里所做的工作，此步骤可以忽略，因为您的数据不能触发和重置同一行。如果我们在触发器行上做数学运算，那么知道它属于哪个组的reset行并不重要。
然后我们进入一个新的选择层，因为我们已经达到了嵌套/相关SQL的雪花限制，并在reset_group上执行min以找到第一个触发时间，然后我们将其与行时间进行比较，并对其进行日期比较。
附注: 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分钟内有效，请执行以下操作。我不会这么做的..。但是这里有你想要的代码..
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60827040
复制相关文章
相似问题
腾讯云开发者
