时间戳

首页 > 解决方案 > 在 PostgreSQL 中每小时累积经过的分钟数

sql - 在 PostgreSQL 中每小时累积经过的分钟数

问题描述

我有一个日期时间列。我需要导出从每小时的第一个到最后一个值按小时分组的总分钟数列，但是，在重叠事件的情况下，时间应该分布在两个小时之间。还有一种情况，如果两个连续记录之间经过的时间超过 30 分钟，则必须忽略它。

下面，我分三个阶段进行了说明，Original、Intermediate（计算运行总数）和 Final。

在此处输入图像描述

而且，我计划每小时采集一次增量数据，所以，我们如何正确地将它与旧数据合并是另一个问题。

样本数据：

 Moves_TS
1/4/2020 10:00
1/4/2020 10:25
1/4/2020 10:42
1/4/2020 10:56
1/4/2020 10:59
1/4/2020 11:02
1/4/2020 11:24
1/4/2020 11:43
1/4/2020 11:55
1/4/2020 12:26
1/4/2020 12:29

中间层：

Moves_TS    Hour    Running Total
1/4/2020 10:00  10  0
1/4/2020 10:25  10  25
1/4/2020 10:42  10  42
1/4/2020 10:56  10  56
1/4/2020 10:59  10  60
1/4/2020 11:02  11  2
1/4/2020 11:24  11  24
1/4/2020 11:43  11  43
1/4/2020 11:55  11  55
1/4/2020 12:26  12  0
1/4/2020 12:29  12  3

最终输出：

Hour    Work done/Hour
10  60
11  55
12  3

标签： sqlpostgresqlwindow-functionscumulative-sum

解决方案

这是一个带有一些曲折的缝隙和岛屿问题。首先，我将通过 30 分钟的间隔定义的“岛屿”进行总结：

select min(moves_ts) as start_ts, max(moves_ts) as end_ts
from (select o.*,
             count(prev_moves_ts) filter (where moves_ts > prev_moves_ts + interval '30 minute') over (order by moves_ts) as grp
      from (select o.*, lag(moves_ts) over (order by moves_ts) as prev_moves_ts
            from original o
           ) o
     ) o
group by grp;

然后您可以使用它generate_series()来扩展数据并计算每小时的重叠：

with islands as (
      select min(moves_ts) as start_ts, max(moves_ts) as end_ts
      from (select o.*,
                   count(prev_moves_ts) filter (where moves_ts > prev_moves_ts + interval '30 minute') over (order by moves_ts) as grp
            from (select o.*, lag(moves_ts) over (order by moves_ts) as prev_moves_ts
                  from original o
                 ) o
           ) o
      group by grp
     )
select hh.hh,
       sum( least(hh.hh + interval '1 hour', i.end_ts) -
            greatest(hh.hh, i.start_ts)
          ) as duration           
from (select generate_series(date_trunc('hour', min(moves_ts)),
                             date_trunc('hour', max(moves_ts)),
                             interval '1 hour'
                            ) hh
      from original o
     ) hh left join
     islands i
     on i.start_ts < hh.hh + interval '1 hour' and
        i.end_ts >= hh.hh
group by hh.hh
order by hh.hh;

这是一个 db<>fiddle。

推荐阅读