sql - 在持续时间列表中汇总每日总和

Question

现状

我有一个表来跟踪特定资源的使用情况。它看起来像这样:

started   | timestamp with time zone | not null
last_ping | timestamp with time zone |
stopped   | timestamp with time zone |

stopped 和 last_ping 字段可能为空。如果它们都已满，则 stopped 是相关的。

开始日期和结束日期只能跨越几秒或多个日期。

目标

我想获取过去 14 天的每日使用情况列表。

现状

我知道我可以获得最近 14 天的列表:

SELECT day
FROM generate_series(CURRENT_DATE, CURRENT_DATE - 14, '-1 day'::interval) day;

我可以获得每个使用条目的总持续时间:

SELECT COALESCE(stopped, last_ping, started) - started AS duration
FROM api_sessionusage;

我还可以结合这两个查询并添加一个仅考虑到午夜的持续时间的限制:

SELECT
  day,
  (
    SELECT SUM(
      LEAST(COALESCE(stopped, last_ping, started), day + interval '1 day') - started
    )
    FROM api_sessionusage
    WHERE started >= day AND started < day + interval '1 day'
  ) AS aggregated_duration
FROM generate_series(CURRENT_DATE, CURRENT_DATE -14, '-1 day'::interval) day;

这里的问题是，午夜后结束的使用 session 仅计入开始日，但不考虑午夜后的持续时间。

如何重写查询，以便获得过去 14 天内每一天的汇总使用情况？

Answer 1

使用overlap and intersect operators时间戳范围类型:

select 
    day, 
    sum(upper(daily_range) - lower(daily_range))
from (
    select 
        day, 
        session_range * tstzrange(day, day::date + 1) daily_range
    from generate_series(current_date, current_date -14, '-1 day'::interval) day
    left join (
        select tstzrange(started, coalesce(stopped, last_ping, started)) session_range
        from api_sessionusage
        ) s
    on session_range && tstzrange(day, day::date + 1)
) s
group by 1
order by 1;

注意。

coalesce(stopped, last_ping, started) - started as duration

如果 stopped 和 last_ping 都为空，则返回零。也许应该是

coalesce(stopped, last_ping, current_date) --?