PrestoSQL 将事件数据转换为每小时摘要

马塞尔D

我有一个包含事件和时间戳的表

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_truncfrom_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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

将行合并到PrestoSQL中的列表中

将分钟数据转换为每小时数据

如何将每小时数据转换为6/12/24每小时数据

将每小时的行数据转换为新列Pandas

PrestoSQL的32位或64位浮点除法

如何在PrestoSQL中计算中位数?

在 PrestoSQL 中从 varchar 中提取数组

Trino (PrestoSQL) 的前年初至今窗口函数

将每小时时间序列数据帧转换为单个小时的多个数据帧

如何将每小时数据转换为半小时

熊猫-将事件持续时间的每小时转换为单独的一行

如何在R中正确地将分钟数据转换为每小时数据?

按元素组将每小时数据转换为每日和双日数据

我需要通过求和将每小时数据转换为每日数据

如何将15分钟数据转换为每小时以熊猫为单位的时间?

将日期时间列表转换为每小时分布数据框

PrestoSql DistributedExecutionPlanner.Visitor中DynamicFilters的作用是什么?

无论如何要为 prestosql Web UI 配置 URL 前缀

如何将每分钟数据表转换为每小时数据表?

R将每小时数据转换为每日数据,直到0:00而不是23:00

如何将168个数据点转换为工作日的每小时图表数据

将模型摘要转换为数据框

计算datetime / numpy中每小时发生的事件数

根据时间戳绘制每小时的事件数

如何将具有“结束”行和“开始”行的事件数据帧转换为按事件数据帧重新分组?

EMR prestoSQL 343:java.lang.IllegalStateException:未加载身份验证器错误

将不规则的时间序列转换为python pandas中的每小时数据

将开始时间和总持续时间转换为每小时经过的时间

R - 将每日格式的日期列转换为每小时粒度