我有一个包含事件和时间戳的表
ts | 事件 | 姓名 |
---|---|---|
1650000000 | 一切都好 | 进程_a |
1650003700 | 东西坏了! | 进程_a |
1650007100 | 一切都好 | 进程_a |
1650010000 | 一切都好 | 进程_b |
1650013100 | 东西坏了! | 进程_b |
1650017400 | 一切都好 | 进程_b |
我想每小时计算每个进程的“一切正常”和“有什么东西坏了!”的小时百分比。最终结果应该类似于
小时 | 姓名 | ok_min |
---|---|---|
... | ... | ... |
2022-04-22 7:00:00 | 进程_a | .912 |
2022-04-22 8:00:00 | 进程_a | .634 |
2022-04-22 9:00:00 | 进程_a | 1 |
2022-04-22 1:00:00 | 进程_b | .354 |
2022-04-22 2:00:00 | 进程_b | .533 |
2022-04-22 3:00:00 | 进程_b | .987 |
... | ... | ... |
我研究了各种子查询来帮助我达到我想要的目标。我意识到我将遇到的第一个问题是我不会有所有的时间,而只有当状态发生变化的几个小时。所以我创建了:
select
timestamp_column
from
(values
(sequence(cast('2022-01-01' as timestamp), --don't bother that this doesn't match my pseudo timestamps in the events table
cast(now() as timestamp),
interval '1' hour
)
)
) as t(timestamp_array)
cross join
unnest(timestamp_array) as t1(timestamp_column)
以上为我提供了事件将要发生的所有小时间隔。
然后我离开加入了每小时时间戳和我的事件表上date_trunc
的from_unixtime(ts)
,所以如果在那一小时有一个事件,我有值,否则我有空值。请注意,一个小时内可能有多个事件。
出于某种原因,我认为将最后一个事件持续到下一小时,如果下一小时的事件为空,是一个好主意
coalesce
(
event,
lag(event) ignore nulls over
(
partition by name
order by timestamp_column
)
)
而且我还认为其他可以帮助我解决这个问题的方法是计算每个事件的持续时间,以这种方式(取上一个事件时间戳减去当前事件时间戳):
lead(ts) over
(
partition by
name
order by ts
) - ts as seconds_in_state
出于某种原因,我想我会知道,在给定的小时内,我处于一种状态有多少秒,处于另一种状态有多少秒。但seconds_in_state
有时我的时间超过一个小时,这告诉我我没有朝着正确的方向前进。
一般来说,这似乎是一个很常见的问题:我如何按某个时间间隔总结事件表,每个事件是否都有一个事物的隐含状态name
?
不知何故,我陷入了困境,我固执地在 Presto SQL 中修复它,而不是下载事件数据并在 Python 中进行一些操作——这绝对应该是可能的!
这是一种可能的方法 - 在当前时间戳和下一个时间戳之间生成一个每小时间隔,用于按名称分区,然后将生成的数组展平unnest
并使用group by
名称和小时来执行所需的计算:
-- sample data
WITH dataset (ts, event, name) AS (
VALUES (1650000000, 'everything is ok', 'process_a'),
(1650003700, 'something is broken!', 'process_a'),
(1650007100, 'everything is ok', 'process_a'),
(1650010000, 'everything is ok', 'process_b'),
(1650013100, 'something is broken!', 'process_b'),
(1650017400, 'everything is ok','process_b')
)
-- query
select name,
ts_hour_exp hour,
count_if(is_ok) * 1.0 / count(*) ok_perc
from (
select date_trunc('hour', from_unixtime(ts)) ts_hour,
if(event = 'everything is ok', true, false) is_ok, -- reduced strings to boolean flag
lead(date_trunc('hour', from_unixtime(ts))) over(
partition by name
order by ts
) next_ts,
name
from dataset
)
cross join unnest ( -- some magic for interval generation
coalesce(
array_except(
sequence(ts_hour, next_ts, interval '1' hour),
array [ ts_hour, next_ts ] -- exclude borders
),
array [ ] -- in case of null
) || ts_hour -- attach current hour
) as t(ts_hour_exp)
group by name, ts_hour_exp
order by name, ts_hour_exp
输出:
姓名 | 小时 | ok_min |
---|---|---|
进程_a | 2022-04-15 05:00:00.000 | 1.0 |
进程_a | 2022-04-15 06:00:00.000 | 0.0 |
进程_a | 2022-04-15 07:00:00.000 | 1.0 |
进程_b | 2022-04-15 08:00:00.000 | 0.5 |
进程_b | 2022-04-15 09:00:00.000 | 0.0 |
进程_b | 2022-04-15 10:00:00.000 | 1.0 |
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句