postgresql 如何计算两个非周期时间序列+分组重叠持续时间

h7appiyu 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(84)

我有两个没有固定间隔时间戳的时间序列表（为了简单起见，样本数据固定为10s）。我目前无法使用TimeScaleDB或其他工具，需要计算设备表的每个时间序列与检查表同时处于活动状态时的持续时间，在24小时内精确度为1秒。
我在尝试使用generateSeries将数据重采样为1并填补空白时失败了，没有什么是完全正确的，当它看起来是正确的，postgres只是永远消失或pgadmin崩溃。
如果我用C语言编写程序，我希望每个表行都有两个游标，并在一次循环中遍历整个表。我们在这里肯定能做到吗？
问题示意图：

的数据
示例表：

DeviceOnline

  Device Id      Start                  End

( 'Device_001', '2023-07-03 11:00:00', '2023-07-03 11:00:50' ), 
( 'Device_001', '2023-07-03 11:01:10', '2023-07-03 11:01:30' ),
( 'Device_002', '2023-07-03 11:00:10', '2023-07-03 11:00:30' ),
( 'Device_002', '2023-07-03 11:01:00', '2023-07-03 11:01:20' ),
( 'Device_003', '2023-07-03 11:00:01', '2023-07-03 11:01:59' ),
( 'Device_003', '2023-07-03 11:04:00', '2023-07-03 11:05:00' ),
( 'Device_004', '2023-07-03 11:00:00', '2023-07-03 11:00:10' ),
( 'Device_004', '2023-07-03 11:00:40', '2023-07-03 11:00:50' ),

ServerOnline

( '2023-07-03 10:55:00', '2023-07-03 10:59:00' ),
( '2023-07-03 11:00:20', '2023-07-03 11:00:40' ), 
( '2023-07-03 11:01:10', '2023-07-03 11:01:40' ),
( '2023-07-03 11:02:00', '2023-07-03 11:03:00' ),

Expected Output

Device      Duration

Device_001  40s
Device_002  20s
Device_003  50s
Device_004   0s

字符串
测试小提琴

postgresql

来源：https://stackoverflow.com/questions/76732119/how-to-calculate-overlap-duration-of-two-non-periodic-time-series-grouped

1条答案

按热度按时间

bhmjp9jg1#

tsrange类型使这变得简单。
你真的在用PostgreSQL 9.6吗？

select deviceid, 
       sum(upper(overlap) - lower(overlap)) as overlap_time
  from (select d.deviceid, 
               tsrange(d.starttime, d.endtime) 
                 * tsrange(s.starttime, s.endtime) as overlap 
          from devicestatus d
               join serverstatus s
                 on tsrange(d.starttime, d.endtime) 
                      && tsrange(s.starttime, s.endtime)) o
 group by deviceid;

字符串
更新Fiddle

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql 如何计算两个非周期时间序列+分组重叠持续时间

1条答案

相关问题

热门标签

最新问答