我正在尝试计算设备列表在每个状态下处于特定状态的持续时间。每个状态不会对列表中设备重复。
我让它为1个设备工作，但无法让组/顺序通过工作来处理所有设备并返回每个设备的一组行。
示例数据

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
...

型
包含更多数据单个函数示例：

http://www.sqlfiddle.com/#!17/4e8f0/1

with t as (
  SELECT    device,
            time, 
            lead(time) over (order by time) - time as duration, 
            state
    from telemetry
    where device = 'Device_001'
    order by time asc
)  
select 
       min(t.device) as DeviceId, -- get first row value
       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;

的字符串