时间戳

首页 > 解决方案 > 基于非模式特定的列值序列检索未知值

sql - 基于非模式特定的列值序列检索未知值

问题描述

我想根据相关的事件值返回和操作时间值，但前提是发生特定的事件序列。下面是一个简化的示例表：

+--------+------------+-------+-------------+-------+-------------+-------+-------------+-------+-------------+-------+
|   id   |   event1   | time1 |   event2    | time2 |   event3    | time3 |   event4    | time4 |   event5    | time5 |
+--------+------------+-------+-------------+-------+-------------+-------+-------------+-------+-------------+-------+
| abc123 | firstevent | 10:00 | secondevent | 10:01 | thirdevent  | 10:02 | fourthevent | 10:03 | fifthevent  | 10:04 |
| abc123 | thirdevent | 10:10 | secondevent | 10:11 | thirdevent  | 10:12 | firstevent  | 10:13 | secondevent | 10:14 |
| def456 | thirdevent | 10:20 | firstevent  | 10:21 | secondevent | 10:22 | thirdevent  | 10:24 | fifthevent  | 10:25 |
+--------+------------+-------+-------------+-------+-------------+-------+-------------+-------+-------------+-------+

对于此表，我们希望在此特定事件序列发生时检索时间：firstevent、secondevent、thirdevent和任何非零值的最终事件。这意味着返回的相关条目如下：

+--------+------------+-------+-------------+-------+-------------+-------+-------------+-------+------------+-------+
|   id   |   event1   | time1 |   event2    | time2 |   event3    | time3 |   event4    | time4 |   event5   | time5 |
+--------+------------+-------+-------------+-------+-------------+-------+-------------+-------+------------+-------+
| abc123 | firstevent | 10:00 | secondevent | 10:01 | thirdevent  | 10:02 | fourthevent | 10:03 | null       | null  |
| null   | null       | null  | null        | null  | null        | null  | null        | null  | null       | null  |
| def456 | null       | null  | firstevent  | 10:21 | secondevent | 10:22 | thirdevent  | 10:24 | fifthevent | 10:26 |
+--------+------------+-------+-------------+-------+-------------+-------+-------------+-------+------------+-------+

如上所示，列与序列的出现无关，从event1和event2列开始返回两个结果，因此解决方案应该是独立的并支持 n 列。然后可以通过在 3 个固定变量之后的序列中发生的最终非零事件聚合这些值，以给出如下所示的内容：

+-------------+-------------------------------+
| FinalEvent  | AverageTimeBetweenFinalEvents |
+-------------+-------------------------------+
| fourthevent | 1:00                          |
| fifthevent  | 2:00                          |
+-------------+-------------------------------+

标签： sqlgoogle-cloud-platformgoogle-bigquery

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
WITH search_events AS (
  SELECT ['firstevent', 'secondevent', 'thirdevent'] search
), temp AS (
  SELECT *, REGEXP_EXTRACT(events, CONCAT(search, r',(\w*)')) FinalEvent
  FROM (
    SELECT id, [time1, time2, time3, time4, time5] times,
      (SELECT STRING_AGG(event) FROM UNNEST([event1, event2, event3, event4, event5]) event) events,
      (SELECT STRING_AGG(search) FROM UNNEST(search) search) search
    FROM `project.dataset.table`, search_events 
  )
)
SELECT FinalEvent, 
  times[SAFE_OFFSET(ARRAY_LENGTH(REGEXP_EXTRACT_ALL(REGEXP_EXTRACT(events, CONCAT(r'(.*?)', search, ',', FinalEvent )), ',')) + 3)] time
FROM temp
WHERE IFNULL(FinalEvent, '') != ''

如果适用于您的问题的样本数据 - 结果是 

Row FinalEvent  time     
1   fourthevent 10:03    
2   fifthevent  10:25

所以，正如您所看到的 - 所有最终事件都与它们各自的时间一起被提取
现在，您可以在这里进行任何您需要的分析 - 我不确定背后的逻辑AverageTimeBetweenFinalEvents，所以我把这个留给你 - 特别是我认为主要问题的重点是提取那些最终事件

你能提供这个陈述背后的逻辑吗？
times[SAFE_OFFSET(ARRAY_LENGTH(REGEXP_EXTRACT_ALL(REGEXP_EXTRACT(events, CONCAT(r'(.*?)', search, ',', FinalEvent )), ',')) + 3)] time

当然，希望下面有助于了解该表达式背后的逻辑

在此处输入图像描述

  1. 组装正则表达式以提取匹配事件之前发生的事件列表
  2. 提取那些事件
  3. 将所有逗号提取到数组中
  4. 通过取上述数组中的逗号数 + 3 来计算最终事件的位置（三是反映搜索序列中的位置数）
  5. 提取相应的时间作为时间数组的元素

推荐阅读