SQL:在有间隔的交点中求和

我有以下“操作”表:

"actions"."starts_at" (datetime)
"actions"."ends_at" (datetime)

我将过去24小时内的“操作”按小时分组,如下所示:

 SELECT date_trunc('hour', actions.starts_at) AS "time"
 FROM actions
 WHERE (age(now(), actions.starts_at < '23:59:00'::interval)
 GROUP BY (date_trunc('hour', actions.starts_at));

我需要知道,对于每个分组的小时,其中与分钟[start_at,ends_at]相互作用的分钟总和是多少。

例:

starts_at: 2020-05-11 09:40:00
ends_at: 2020-05-11 10:13:00

组:

2020-05-11 08:00:00 -> 0 (minutes)
2020-05-11 09:00:00 -> 20 (minutes)
2020-05-11 10:00:00 -> 13 (minutes)
2020-05-11 11:00:00 -> 0 (minutes)
评论
  • laut
    laut 回复

    You can use a timestamp range for that and get the length of the intersection:

    with data (starts_at) as (
      -- replace this with your current query 
      values 
        (timestamp '2020-05-11 08:00:00'),
        (timestamp '2020-05-11 09:00:00'),
        (timestamp '2020-05-11 10:00:00'),
        (timestamp '2020-05-11 11:00:00')
    ), diff as (
      select starts_at, 
              tsrange(starts_at, starts_at + interval '1 hour', '[)') 
                 * tsrange(timestamp '2020-05-11 09:40:00', timestamp '2020-05-11 10:13:00', '[)') as overlap
      from data
    )
    select starts_at, 
           upper(overlap) - lower(overlap) as overlap_duration
    from diff
    

    The range constructed from the starts_at column has a hardcoded length of 1 hour. In theory this could be made dynamic, by looking at the "next row" using the lag() function. But handling the "last" row in that set would again require the knowledge of the used interval.

    The column overlap_duration is an interval, if you want to convert that into minutes, you can use:

    extract(epoch from upper(overlap) - lower(overlap)) / 60  as overlap_minutes
    

    Online example