Oracle按5分钟间隔统计数据求和的SQL实现求助

问题场景 #

现有SQL查询可获取每分钟的统计结果：

select to_char(date, 'HH24:MI') as Timestamp,
count(case when type = 5 then 1 end) as Counts1,
count(case when type = 6 then 1 end) as Counts2,
from data
where date >= to_date('2022-10-27 01:00', 'YYYY-MM-DD HH24:MI')
and date <= to_date('2022-10-27 01:20', 'YYYY-MM-DD HH24:MI')
and type IN (5,6)
group by to_char(date, 'HH24:MI')
order by to_char(date, 'HH24:MI')

执行后得到每分钟统计结果：

+-----------+-----------+----------+
| Timestamp | Counts1   | Counts2  |
+-----------+-----------+----------+
| 01:00     | 200       | 12       |
| 01:01     | 250       | 35       |
| 01:02     | 300       | 47       |
| 01:03     | 150       | 78       |
| 01:04     | 100       | 125      |
| 01:05     | 125       | 5        |
| 01:06     | 130       | 10       |
| 01:07     | 140       | 12       |
| 01:08     | 150       | 35       |
| 01:09     | 160       | 47       |
| 01:10     | 170       | 78       |
| 01:11     | 180       | 125      |
| 01:12     | 190       | 5        |
| 01:13     | 210       | 10       |
| 01:14     | 220       | 12       |
| 01:15     | 230       | 35       |
| 01:16     | 240       | 47       |
| 01:17     | 260       | 78       |
| 01:18     | 270       | 125      |
| 01:19     | 280       | 5        |
| 01:20     | 290       | 10       |
+-----------+-----------+----------+

需要将数据按每5分钟间隔求和，预期结果如下：

+-----------+-----------+----------+
| Timestamp | Counts1   | Counts2  |
+-----------+-----------+----------+
| 01:05     | 1125      | 302      |
| 01:10     | 750       | 182      |
| 01:15     | 1030      | 187      |
| 01:20     | 1340      | 265      |
+-----------+-----------+----------+

此前尝试直接给date加5分钟后分组，仅实现了时间戳后移，未完成聚合：

select to_char(date + interval '5' minute, 'HH24:MI') as Timestamp,
count(case when type = 5 then 1 end) as Counts1,
count(case when type = 6 then 1 end) as Counts2,
from data
where date >= to_date('2022-10-27 01:00', 'YYYY-MM-DD HH24:MI')
and date <= to_date('2022-10-27 01:20', 'YYYY-MM-DD HH24:MI')
and type IN (5,6)
group by to_char(date + interval '5' minute, 'HH24:MI')
order by to_char(date + interval '5' minute, 'HH24:MI')

错误结果：

+-----------+-----------+----------+
| Timestamp | Counts1   | Counts2  |
+-----------+-----------+----------+
| 01:05     | 125       | 5        |
| 01:06     | 130       | 10       |
| 01:07     | 140       | 12       |
| 01:08     | 150       | 35       |
| 01:09     | 160       | 47       |
| 01:10     | 170       | 78       |
| 01:11     | 180       | 125      |
| 01:12     | 190       | 5        |
| 01:13     | 210       | 10       |
| 01:14     | 220       | 12       |
| 01:15     | 230       | 35       |
| 01:16     | 240       | 47       |
| 01:17     | 260       | 78       |
| 01:18     | 270       | 125      |
| 01:19     | 280       | 5        |
| 01:20     | 290       | 10       |
+-----------+-----------+----------+

正确解决方案 #

核心逻辑是将每个时间戳归到对应的5分钟间隔结束点，再分组求和，以下是适用于Oracle的实现：

select 
    to_char(
        trunc(date, 'HH24') + floor(to_number(to_char(date, 'MI'))/5)*interval '5' minute + interval '5' minute,
        'HH24:MI'
    ) as Timestamp,
    sum(case when type = 5 then 1 else 0 end) as Counts1,
    sum(case when type = 6 then 1 else 0 end) as Counts2
from data
where date >= to_date('2022-10-27 01:00', 'YYYY-MM-DD HH24:MI')
and date <= to_date('2022-10-27 01:20', 'YYYY-MM-DD HH24:MI')
and type IN (5,6)
group by 
    trunc(date, 'HH24') + floor(to_number(to_char(date, 'MI'))/5)*interval '5' minute + interval '5' minute
order by Timestamp;

逻辑拆解 #

1. trunc(date, 'HH24')：将时间截断到当前小时，例如01:03转为01:00:00
2. floor(to_number(to_char(date, 'MI'))/5)*interval '5' minute：计算当前分钟所属的5分钟块，例如03分钟属于0-4分钟块，对应累加05分钟；06分钟属于5-9分钟块，对应累加15分钟
3. + interval '5' minute：将分组标记设为间隔结束时间，和预期结果的Timestamp格式匹配
4. sum替代count：语义更清晰地累加每个5分钟块内的符合条件记录数

如果是PostgreSQL等数据库，可使用date_trunc和extract简化写法：

select 
    to_char(date_trunc('hour', date) + (floor(extract(minute from date)/5) * interval '5 minute') + interval '5 minute', 'HH24:MI') as Timestamp,
    sum(case when type=5 then 1 else 0 end) as Counts1,
    sum(case when type=6 then 1 else 0 end) as Counts2
from data
where date >= to_date('2022-10-27 01:00', 'YYYY-MM-DD HH24:MI')
and date <= to_date('2022-10-27 01:20', 'YYYY-MM-DD HH24:MI')
and type in (5,6)
group by date_trunc('hour', date) + (floor(extract(minute from date)/5) * interval '5 minute') + interval '5 minute'
order by Timestamp;

内容的提问来源于stack exchange，提问作者Sai