sql - 在SQL中如何找到在几秒钟内彼此分开的数据行

Question

我有一个记录传感器事件的表。并且在随机时间每小时选择一个随机日志进行测试。我想找到每个小时采摘的 T 秒内的 N 个事件。

数据看起来像这样

eventID eventTs             picked_for_testing
123     2021-04-01 13:21:45         0
3532    2020-12-16 08:31:12         1
987341  2021-04-01 13:21:41         1

事件 1 和 3 都在 4 秒内发生。我想找到在 10 秒内发生的所有此类事件。

我能够通过对所有选定事件与未选定事件进行交叉连接来获得它，然后在 eventT 之间进行时间差异，然后对其进行过滤。但由于数据中的事件数量，它花费的时间太长。

有更好的方法吗？

标签： sqlgoogle-bigquery

Answer 1

对于该要求，BigQuery 分析功能似乎是可行的方法。特别是导航功能。

在不知道您正在寻找的确切结果的情况下，很难给您一个正确的查询。我假设您只需要具有以下架构的表：

eventID, next_eventID_within_10

所以，这个查询将完成这项工作：

WITH data as (
SELECT 123 as eventID, timestamp("2021-04-01 13:21:45") as eventTs, 0 as picked_for_testing
UNION ALL
SELECT 3532 as eventID, timestamp("2020-12-16 08:31:12") as eventTs, 1 as picked_for_testing
UNION ALL 
SELECT 987341 as eventID, timestamp("2021-04-01 13:21:41") as eventTs, 1 as picked_for_testing
UNION ALL 
SELECT 87345874 as eventID, timestamp("2021-04-01 13:21:49") as eventTs, 1 as picked_for_testing)

, tmp as (
SELECT 
  eventID, 
  picked_for_testing,
  eventTs,
  IF(TIMESTAMP_DIFF(LEAD(eventTs) OVER (ORDER BY eventTs), eventTs, SECOND) <= 10, LEAD(eventID) OVER (ORDER BY eventTs), Null) as next_eventID_within_10
FROM data)

SELECT eventID, next_eventID_within_10 
FROM tmp 
WHERE next_eventID_within_10 IS NOT NULL