这是一个有些曲折的空白问题。首先，我将总结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;

Here是db 小提琴。

select 
   MOVES_TS,Hour,TO_CHAR(MOVES_TS,'YYYYMMDDHH') DATEHR,MIN(Moves_TS) over (partition by DATEHR) as MIN_MOVES_TS,(
    DATE_PART('day',MOVES_TS - MIN_MOVES_TS) * 24 +
   DATE_PART('hour',MOVES_TS - MIN_MOVES_TS) * 60 + 
   DATE_PART('minute',MOVES_TS - MIN_MOVES_TS)
   ) as RunningTotal

from dataset