基于ClickHouse设备事件表，用SQL计算每10分钟设备在线时长

我有一张记录设备上下线事件的ClickHouse表，表结构定义如下：

CREATE TABLE device_events
(
    `device_id` String,
    `event_datetime` DateTime,
    `event_type` Enum8('disconnected' = 0, 'connected' = 1)
)
ENGINE = MergeTree
ORDER BY (device_id, event_datetime)

同时存在如下示例数据：

INSERT INTO device_events (device_id, event_datetime, event_type) VALUES
('device_1', '2024-02-28 08:01:00', 'connected'),
('device_1', '2024-02-28 09:30:00', 'disconnected'),
('device_1', '2024-02-28 11:00:00', 'connected'),
('device_1', '2024-02-28 11:05:00', 'disconnected'),
('device_1', '2024-02-28 20:00:00', 'connected'),
('device_2', '2024-02-28 08:30:00', 'connected'),
('device_2', '2024-02-28 10:30:00', 'disconnected'),
('device_2', '2024-02-28 12:00:00', 'connected'),
('device_2', '2024-02-28 12:30:00', 'disconnected'),
('device_3', '2024-02-28 07:00:00', 'connected'),
('device_3', '2024-02-28 08:15:00', 'disconnected'),
('device_3', '2024-02-28 14:00:00', 'connected'),
('device_3', '2024-02-28 18:00:00', 'disconnected');

现需基于该表，计算任意给定时间段内设备每10分钟的在线时长，请问是否可以通过SQL实现此需求？

完全可以通过ClickHouse SQL实现该需求，核心思路是先配对设备的上下线事件生成在线区间，再将区间拆分到对应10分钟窗口中，统计每个窗口的重叠时长。

步骤1：生成设备在线区间 #

为每个connected事件匹配对应的下一个disconnected事件，若设备在查询时间段结束时仍在线，则用时间段结束时间作为下线时间：

WITH 
-- 自定义查询时间范围
'2024-02-28 07:00:00' AS start_time,
'2024-02-28 21:00:00' AS end_time
SELECT 
    device_id,
    event_datetime AS connect_time,
    coalesce(
        lead(event_datetime) OVER (PARTITION BY device_id ORDER BY event_datetime),
        toDateTime(end_time)
    ) AS disconnect_time
FROM device_events
WHERE 
    event_datetime BETWEEN start_time AND end_time
    AND event_type = 'connected'

步骤2：生成10分钟时间窗口 #

生成查询时间段内所有按10分钟对齐的时间窗口：

WITH 
'2024-02-28 07:00:00' AS start_time,
'2024-02-28 21:00:00' AS end_time
SELECT 
    toDateTime(toStartOfInterval(toDateTime(start_time) + INTERVAL (number * 10) MINUTE, INTERVAL 10 MINUTE)) AS window_start,
    toDateTime(window_start + INTERVAL 10 MINUTE) AS window_end
FROM numbers(0, floor((toDateTime(end_time) - toDateTime(start_time)) / 600))

步骤3：关联区间与窗口，计算在线时长 #

将在线区间与时间窗口关联，计算重叠时长并按设备和窗口汇总：

WITH 
-- 自定义查询时间段
'2024-02-28 07:00:00' AS start_time,
'2024-02-28 21:00:00' AS end_time,
-- 生成10分钟时间窗口集合
(
    SELECT arrayJoin(arrayMap(
        x -> (toDateTime(toStartOfInterval(toDateTime(start_time) + INTERVAL x*10 MINUTE, INTERVAL 10 MINUTE)),
              toDateTime(toStartOfInterval(toDateTime(start_time) + INTERVAL x*10 MINUTE, INTERVAL 10 MINUTE)) + INTERVAL 10 MINUTE),
        numbers(0, floor((toDateTime(end_time) - toDateTime(start_time)) / 600))
    )) AS (window_start, window_end)
) AS time_windows,
-- 生成设备在线区间集合
(
    SELECT 
        device_id,
        event_datetime AS connect_time,
        coalesce(lead(event_datetime) OVER (PARTITION BY device_id ORDER BY event_datetime), toDateTime(end_time)) AS disconnect_time
    FROM device_events
    WHERE 
        event_datetime BETWEEN start_time AND end_time
        AND event_type = 'connected'
) AS device_online_intervals
SELECT 
    doi.device_id,
    tw.window_start,
    -- 计算重叠时长（单位：秒）
    toUInt32(
        greatest(0, 
            least(doi.disconnect_time, tw.window_end) - greatest(doi.connect_time, tw.window_start)
        )
    ) AS online_seconds
FROM device_online_intervals doi
CROSS JOIN time_windows tw
-- 过滤无重叠的区间与窗口
WHERE greatest(doi.connect_time, tw.window_start) < least(doi.disconnect_time, tw.window_end)
ORDER BY device_id, window_start;

补充说明 #

• 修改start_time和end_time即可切换任意查询时间段
• 若需要以分钟为单位展示时长，将online_seconds除以60即可
• 如果需要显示所有10分钟窗口（包括在线时长为0的），可将CROSS JOIN改为RIGHT JOIN，并把online_seconds的默认值设为0

内容的提问来源于stack exchange，提问作者xren