时间戳

首页 > 解决方案 > Bigquery 按事件的时间戳拆分行

sql - Bigquery 按事件的时间戳拆分行

问题描述

在 Google BigQuery 中，我在单个支持会话中有一个事件列表，这些事件由事件名称标记。每个支持问题resolved_time都是resolved事件的时间戳。每个问题start_time都是事件的第一次出现，message或者pending在unresolved会话的绝对开始处或在已解决的标记之后。

换句话说，对话的状态要么是开放的，要么是已解决的。状态在第一次出现 、 或 事件时变为打开状态message，pending并unresolved在事件时关闭resolved。

目前我只有一张timestamp桌子event_name。想加入start_time和resolved_time。

在下面的示例中，这会导致 4 个单独的支持问题。三个被resolved事件解决，最后一个未被解决，因为它没有被resolved事件关闭。

时间戳 事件名称 开始时间 解决时间
2021-01-15 20:27:59 UTC 未解决 2021-01-15 20:27:59 UTC 2021-01-20 19:31:37 UTC
2021-01-16 03:02:46 UTC 信息 2021-01-15 20:27:59 UTC 2021-01-20 19:31:37 UTC
2021-01-20 19:31:37 UTC 解决 2021-01-15 20:27:59 UTC 2021-01-20 19:31:37 UTC
2021-01-21 00:13:43 UTC 待办的 2021-01-21 00:13:43 UTC 2021-01-23 23:38:46 UTC
2021-01-23 23:38:46 UTC 解决 2021-01-21 00:13:43 UTC 2021-01-23 23:38:46 UTC
2021-01-24 00:38:17 UTC 信息 2021-01-24 00:38:17 UTC 2021-01-24 02:19:44 UTC
2021-01-24 00:42:31 UTC 未解决 2021-01-24 00:38:17 UTC 2021-01-24 02:19:44 UTC
2021-01-24 02:19:44 UTC 解决 2021-01-24 00:38:17 UTC 2021-01-24 02:19:44 UTC
2021-01-25 15:55:50 UTC 信息 2021-01-25 15:55:50 UTC 无效的
2021-01-25 15:59:55 UTC 未解决 2021-01-25 15:55:50 UTC 无效的 
WITH sample_table AS (
    SELECT  TIMESTAMP("2021-01-15 20:27:59") `timestamp` , "unresolved" event_name UNION ALL
    SELECT  TIMESTAMP("2021-01-16 03:02:46") , "message"  UNION ALL
    SELECT  TIMESTAMP("2021-01-20 19:31:37") , "resolved"  UNION ALL
    SELECT  TIMESTAMP("2021-01-21 00:13:43") , "pending"  UNION ALL
    SELECT  TIMESTAMP("2021-01-23 23:38:46") , "resolved" UNION ALL
    SELECT  TIMESTAMP("2021-01-24 00:38:17") , "message" UNION ALL
    SELECT  TIMESTAMP("2021-01-24 00:42:31") , "unresolved" UNION ALL
    SELECT  TIMESTAMP("2021-01-24 02:19:44") , "resolved" UNION ALL
    SELECT  TIMESTAMP("2021-01-25 15:55:50") , "message" UNION ALL
    SELECT  TIMESTAMP("2021-01-25 15:59:55") , "unresolved" )

SELECT * FROM sample_table
ORDER BY timestamp ASC

感谢您提前提供任何帮助。

标签： sqlgoogle-bigquery

解决方案

老实说，我不明白你想要什么，但是我将消息分组到一个数组中，并计算了票的状态。

WITH sample_table AS (
    SELECT  TIMESTAMP("2021-01-15 20:27:59") `timestamp` , "unresolved" event_name,  TIMESTAMP("2021-01-15 20:27:59") start_time, TIMESTAMP("2021-01-20 19:31:37") resolved_time UNION ALL
    SELECT  TIMESTAMP("2021-01-16 03:02:46") , "message",  TIMESTAMP("2021-01-15 20:27:59"), TIMESTAMP("2021-01-20 19:31:37") UNION ALL
    SELECT  TIMESTAMP("2021-01-20 19:31:37") , "resolved",  TIMESTAMP("2021-01-15 20:27:59"), TIMESTAMP("2021-01-20 19:31:37") UNION ALL
    SELECT  TIMESTAMP("2021-01-21 00:13:43") , "pending",  TIMESTAMP("2021-01-21 00:13:43"), TIMESTAMP("2021-01-23 23:38:46") UNION ALL
    SELECT  TIMESTAMP("2021-01-23 23:38:46") , "resolved",  TIMESTAMP("2021-01-21 00:13:43"), TIMESTAMP("2021-01-23 23:38:46") UNION ALL
    SELECT  TIMESTAMP("2021-01-24 00:38:17") , "message",  TIMESTAMP("2021-01-24 00:38:17"), TIMESTAMP("2021-01-24 02:19:44") UNION ALL
    SELECT  TIMESTAMP("2021-01-24 00:42:31") , "unresolved",  TIMESTAMP("2021-01-24 00:38:17"), TIMESTAMP("2021-01-24 02:19:44") UNION ALL
    SELECT  TIMESTAMP("2021-01-24 02:19:44") , "resolved",  TIMESTAMP("2021-01-24 00:38:17"), TIMESTAMP("2021-01-24 02:19:44") UNION ALL
    SELECT  TIMESTAMP("2021-01-25 15:55:50") , "message",  TIMESTAMP("2021-01-25 15:55:50"), NULL UNION ALL
    SELECT  TIMESTAMP("2021-01-25 15:59:55") , "unresolved",  TIMESTAMP("2021-01-25 15:55:50"), NULL
)
SELECT start_time, resolved_time, 
    array_agg(struct(timestamp, event_name) order by timestamp) as events,
    CASE 
        WHEN MAX('resolved' = event_name) THEN 'resolved'
        ELSE 'open'
    END as status
FROM sample_table
group by 1,2
order by 1,2

推荐阅读