Oracle SQL - count每天每小时激活/打开的票的数量

Question

我有一个来自oracle db的数据集，看起来像这样:

ticket_num start_date          repair_date
1          1/1/2021 02:05:15   1/4/2021 09:30:00
2          1/2/2021 12:15:45   1/2/2021 14:03:00
3          1/2/2021 12:20:00   1/2/2021 13:54:00

我需要计算一个小时时间段内的有效门票数量。因此，如果在该小时之前开票，并在一小时后关闭，则将被计算在内。所有的日子和时间需要表示，无论是否有活跃的门票开放在那段时间。预期输出为:

month    day    hour   #active_tix
1        1      2      1
1        1      3      1
...
1        2      12     3
1        2      13     3
1        2      14     2
1        2      15     1
...
1        4      9      1
1        4      10     0

任何帮助都将是非常感激的。

Answer 1

您需要一个日历表。在下面的查询中，它是动态创建的

select c.hstart, count(t.ticket_num) n
from (
-- create calendar on the fly
select timestamp '2021-01-01 00:00:00' + NUMTODSINTERVAL(level-1, 'hour') hstart
from dual
connect by timestamp '2021-01-01 00:00:00' + NUMTODSINTERVAL(level-1, 'hour') < timestamp '2022-01-01 00:00:00'
) c
left join mytable t on t.start_date < c.hstart and t.repair_date >= c.hstart
group by c.hstart
order by c.hstart