SQL时序数据：计算多设备各状态持续时长

要实现多设备批量统计，只需对原单设备查询做两处关键调整：

• 在窗口函数 lead() 中添加 PARTITION BY device，确保仅在同一设备的时间序列内计算下一条记录的时间差
• 最终查询通过 GROUP BY device 聚合每个设备的统计结果，替代原查询中的 min(t.device)

修正后的完整SQL #

WITH t AS (
  SELECT 
    device,
    time, 
    lead(time) OVER (PARTITION BY device ORDER BY time) - time AS duration, 
    state
  FROM telemetry
)  
SELECT 
  device AS DeviceId,
  EXTRACT(EPOCH FROM SUM(t.duration)) AS "total",
  EXTRACT(EPOCH FROM SUM(t.duration * (t.state = 0)::int)) AS "state1",
  EXTRACT(EPOCH FROM SUM(t.duration * (t.state = 1)::int)) AS "state2",
  EXTRACT(EPOCH FROM SUM(t.duration * (t.state = 2)::int)) AS "state3",
  EXTRACT(EPOCH FROM SUM(t.duration * (t.state = 3)::int)) AS "state4" 
FROM t
GROUP BY device;

示例数据 #

CREATE TABLE telemetry (device character varying, time timestamptz, state int);
INSERT INTO telemetry (device, time, state) values
('Device_001', '2021-07-03 11:28:50', 3),
('Device_001', '2021-07-03 11:28:56', 0),
('Device_001', '2021-07-03 11:29:01', 1),
('Device_001', '2021-07-03 11:45:22', 0),
('Device_001', '2021-07-03 11:45:43', 3),
('Device_001', '2021-07-03 11:45:53', 1),
('Device_001', '2021-07-03 13:00:48', 0),
('Device_002', '2021-07-03 11:28:41', 3),
('Device_002', '2021-07-03 11:28:46', 0),
('Device_002', '2021-07-03 11:28:51', 3),
('Device_002', '2021-07-03 11:28:56', 0),
('Device_002', '2021-07-03 11:29:01', 1),
('Device_002', '2021-07-03 11:29:20', 3),
('Device_002', '2021-07-03 11:29:26', 0);

期望输出 #

deviceid    total   state1  state2  state3  state4
Device_001  5518    26      5476    0       16
Device_002  5500    14      5445    10      31
...

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