sql - 给定打开和关闭日期，随着时间的推移生成打开的票数

Question

我有一些门票的一组数据，带有 datetime它们何时打开和关闭(或 NULL，如果它们仍然打开)。

+------------------+------------------+
|    opened_on     |    closed_on     |
+------------------+------------------+
| 2019-09-01 17:00 | 2020-01-01 13:37 |
| 2020-04-14 11:00 | 2020-05-14 14:19 |
| 2020-03-09 10:00 | NULL             |
+------------------+------------------+

我们想生成一个数据表，显示按时间分组的按时间打开的门票总数。类似于以下内容:

+------------------+------------------+
|    date          |    num_open      |
+------------------+------------------+
| 2019-09-01 00:00 | 1                |
| 2020-09-02 00:00 | 1                |
| etc...           |                  |
| 2020-01-01 00:00 | 0                |
| 2020-01-02 00:00 | 0                |
| etc...           |                  |
| 2020-03-08 00:00 | 0                |
| 2020-03-09 00:00 | 1                |
| etc...           |                  |
| 2020-04-14 00:00 | 2                |
+------------------+------------------+

请注意，我不确定 num_open 的情况如何。是针对给定日期考虑的 - 是否应该从日期的结束或开始的角度来考虑，即如果在同一日期打开和关闭，那应该算作 0 吗？
这是在 Postgres 中，所以我想为此使用窗口函数，但是尝试按日期截断使其变得复杂。我曾尝试使用 generate_series函数来创建要加入的日期系列，但是当我使用聚合函数时，我“失去”了对单个票证日期时间的访问权限。

Answer 1

您可以使用 generate_series()构建日期列表，然后是 left join关于提出表格的不平等条件:

select s.dt, count(t.opened_on) num_open
from generate_series(date '2019-09-01', date '2020-09-01', '1 day') s(dt)
left join mytable t
    on s.dt >= t.opened_on and s.dt < coalesce(t.closed_on, 'infinity')
group by s.dt

实际上，这似乎更接近您想要的:

select s.dt, count(t.opened_on) num_open
from generate_series(date '2019-09-01', date '2020-09-01', '1 day') s(dt)
left join mytable t
    on s.dt >= t.opened_on::date and s.dt < coalesce(t.closed_on::date, 'infinity')
group by s.dt