Oracle 11查询组/计数和按小时分割结果

Question

我在Oracle v11上有一个简单的查询来分组和计数记录，没有什么特别的:

select ADDR, count(ADDR) from DBTB group by ADDR;

表也有TIMESTAMP列，我要做的是按小时分组和计数唯一的ADDR。例如:

TIMESTAMP               ADDR
19-OCT-2021 17:15:00    12345
19-OCT-2021 17:20:00    12345
19-OCT-2021 17:25:00    12345
19-OCT-2021 17:27:00    67890
19-OCT-2021 18:10:00    55555
19-OCT-2021 18:20:00    55555
19-OCT-2021 18:30:00    66666
19-OCT-2021 18:43:00    77777

输出应该是:

HOUR  COUNT
17        2
18        3

有人能帮我做一个查询计数和组相同的ADDR分割小时?谢谢你！卢卡斯

Answer 1

使用TO_CHAR()获取每个时间戳的小时:

SELECT TO_CHAR("TIMESTAMP", 'HH24') HOUR,
COUNT(DISTINCT "ADDR") COUNT
FROM DBTB
GROUP BY TO_CHAR("TIMESTAMP", 'HH24');

可能你还需要按日期分组:

SELECT TRUNC("TIMESTAMP") DAY,
TO_CHAR("TIMESTAMP", 'HH24') HOUR,
COUNT(DISTINCT "ADDR") COUNT
FROM DBTB
GROUP BY TRUNC("TIMESTAMP"), TO_CHAR("TIMESTAMP", 'HH24');

或指定日期的过滤器:

SELECT TO_CHAR("TIMESTAMP", 'HH24') HOUR,
COUNT(DISTINCT "ADDR") COUNT
FROM DBTB
WHERE TRUNC("TIMESTAMP") = TO_DATE('19-Oct-2021', 'DD-MON-YYYY')
GROUP BY TO_CHAR("TIMESTAMP", 'HH24');