获取SQL中时间戳的滚动计数

Question

我有一个表(在Oracle数据库中(，它看起来像下面显示的那样，有大约4000条记录。这只是表格设计的一个例子。时间戳的范围长达数年。

|        Time                    |    Action      |
|   9/25/2019 4:24:32 PM         |      Yes       |
|   9/25/2019 4:28:56 PM         |      No        |  
|   9/28/2019 7:48:16 PM         |      Yes       |
|         ....                   |     ....       |

我希望能够获得滚动15分钟间隔内发生的时间戳的计数。我的主要目标是确定任何15分钟间隔出现的最大时间戳数。我希望通过查看每个时间戳并获得该时间戳后15分钟内出现的时间戳计数来完成这项工作。

我的目标是拥有类似的东西

|      Interval                             |              Count          |
| 9/25/2019 4:24:00 PM - 9/25/2019 4:39:00  |               2             |
| 9/25/2019 4:25:00 PM - 9/25/2019 4:40:00  |               2             |
|            .....                          |             .....           |
| 9/25/2019 4:39:00 PM - 9/25/2019 4:54:00  |               0             |  

我不确定我怎么能做到这一点，如果有的话。任何想法或建议都将不胜感激。

Answer 1

如果您希望数据中有任何15分钟的间隔，那么您可以使用：

select t.*,
count(*) over (order by timestamp
range between interval '15' minute preceding and current row
) as cnt_15
from t;

如果你想要最大值，那么在这个上使用rank()

select t.*
from (select t.*, rank() over (order by cnt_15 desc) as seqnum
from (select t.*,
count(*) over (order by timestamp
range between interval '15' minute preceding and current row
) as cnt_15
from t
) t
) t
where seqnum = 1;

这不会完全产生您在查询中指定的结果。但它确实回答了一个问题：

我希望能够获得滚动15分钟间隔内发生的时间戳计数。我的主要目标是确定任何15分钟间隔出现的最大时间戳数。

Answer 2

您可以使用递归查询枚举分钟数，然后使用left join:

with recursive cte (start_dt, max_dt) as (
select trunc(min(time), 'mi'), max(time) from mytable
union all
select start_dt + interval '1' minute, max_dt from cte where start_dt < max_dt
)
select 
c.start_dt,
c.start_dt + interval '15' minute end_dt,
count(t.time) cnt
from cte c
left join mytable t 
on  t.time >= c.start_dt 
and t.time <  c.start_dt + interval '15' minute
group by c.start_dt