窗口函数或其他用于筛选事件的函数

Question

我有一个表，它经常加载来自"传感器"；连接到服务器。服务器发送我们加载到bigquery表中的事件。我想每30分钟查询一次表，列出所有没有发送心跳的传感器，并添加一列，其中包含接收到最后一次心跳的时间戳。我的桌子是这样的：

sensor, event_timestamp, received_timestamp, event, level, message
sensor1, 2021-04-26 03:57:56.964 UTC, 2021-04-26 03:57:57.964 UTC, heartbeat_lost, error, "heartbeat lost for 20 mins"
sensor2, 2021-04-26 03:57:56.964 UTC, 2021-04-26 03:57:57.964 UTC, heartbeat_lost, warning, "heartbeat lost for 5 mins"
sensor1, 2021-04-26 03:36:56.964 UTC, 2021-04-26 03:36:57.964 UTC, status, heartbeat, null
sensor2, 2021-04-26 03:51:56.964 UTC, 2021-04-26 03:51:57.964 UTC, status, heartbeat, null
......
sensor3,..
sensor100,...
sensor1, 2021-04-26 02:57:56.964 UTC, 2021-04-26 02:57:57.964 UTC, heartbeat_lost, error, "heartbeat lost for 20 mins"

期望的结果。我想知道哪些传感器没有接收到心跳，以及何时接收到最新的心跳。类似这样的东西：

sensor, event_timestamp, received_timestamp, event, level, message, last_heartbeat_timestamp
sensor1, 2021-04-26 03:57:56.964 UTC, 2021-04-26 03:57:57.964 UTC, heartbeat_lost, error, "heartbeat lost for 20 mins", 2021-04-26 03:36:56.964 UTC

我一直试图用窗口函数添加last_heartbeat_timestamp，但我很难知道如何只获得带有心跳的最后一个事件。我还尝试首先查询错误消息，左边加入了带有检测信号的查询，但我只想要检测信号中的一行——最后一行error_event_timestamp>heartbeat_event_timestamp。

有什么建议吗？

Answer 1

按传感器分组，然后获取最新事件，还可以通过获取两个时间戳之间的差来检查它是否符合最近的窗口时间：

SELECT 
sensor,
MAX(event_ts) AS last_event_ts,
IF( TIMESTAMP_DIFF(TIMESTAMP('2021-04-28 23:50:00'), MAX(event_ts), SECOND) < 2, MAX(event_ts), NULL) AS recent_event 
FROM sensors
GROUP BY sensor 

使用与您的结构类似的伪数据的完整工作示例：

WITH sensors AS (
SELECT 
'sensor-1' AS sensor,
TIMESTAMP('2021-04-28 23:50:00') AS event_ts
UNION ALL 
SELECT 
'sensor-1' AS sensor,
'2021-04-28 23:51:00' AS event_ts
UNION ALL 
SELECT 
'sensor-1' AS sensor,
'2021-04-28 23:52:00' AS event_ts
UNION ALL 
SELECT 
'sensor-1' AS sensor,
'2021-04-28 23:54:00' AS event_ts
UNION ALL
SELECT 
'sensor-2' AS sensor,
'2021-04-28 23:40:00' AS event_ts
UNION ALL 
SELECT 
'sensor-2' AS sensor,
'2021-04-28 23:41:00' AS event_ts
UNION ALL 
SELECT 
'sensor-2' AS sensor,
'2021-04-28 23:42:00' AS event_ts
UNION ALL 
SELECT 
'sensor-2' AS sensor,
'2021-04-28 23:54:13' AS event_ts
UNION ALL
SELECT 
'sensor-3' AS sensor,
'2021-04-28 23:40:00' AS event_ts
UNION ALL 
SELECT 
'sensor-3' AS sensor,
'2021-04-28 23:41:00' AS event_ts

)
SELECT 
sensor,
MAX(event_ts) AS last_event_ts,
IF( TIMESTAMP_DIFF(TIMESTAMP('2021-04-28 23:50:00'), MAX(event_ts), SECOND) < 2, MAX(event_ts), NULL) AS recent_event 
FROM sensors
GROUP BY sensor