如何从Oracle表中按小时分组查询数据

需求说明 #

我的查询返回的数据包含created_date字段，该字段是带小时信息的日期类型，代表记录的创建时间。我需要生成按日期分组、同时展示各小时数据分布的结果（类似每天一行，各小时列对应统计数量的表格样式）。

目前我仅能实现按天维度统计的查询：

SELECT TRUNC(CREATED_DATE),COUNT(*) FROM IMAGE_HISTORY
WHERE CREATED_DATE >= SYSDATE-7
GROUP BY TRUNC(CREATED_DATE)
ORDER BY TRUNC(CREATED_DATE) ASC

解决方案 #

1. 转置为小时列（匹配示例表格样式） #

如果需要将小时转为列，每天一行展示各小时的统计数，可以使用Oracle的PIVOT函数：

SELECT *
FROM (
    SELECT 
        TRUNC(CREATED_DATE) AS 统计日期,
        EXTRACT(HOUR FROM CREATED_DATE) AS 小时,
        COUNT(*) AS 记录数
    FROM IMAGE_HISTORY
    WHERE CREATED_DATE >= SYSDATE - 7
    GROUP BY TRUNC(CREATED_DATE), EXTRACT(HOUR FROM CREATED_DATE)
)
PIVOT (
    NVL(SUM(记录数), 0)  -- 无数据时显示0，替换NULL
    FOR 小时 IN (
        0 AS "0点", 1 AS "1点", 2 AS "2点", 3 AS "3点", 4 AS "4点", 5 AS "5点",
        6 AS "6点", 7 AS "7点", 8 AS "8点", 9 AS "9点", 10 AS "10点", 11 AS "11点",
        12 AS "12点", 13 AS "13点", 14 AS "14点", 15 AS "15点", 16 AS "16点", 17 AS "17点",
        18 AS "18点", 19 AS "19点", 20 AS "20点", 21 AS "21点", 22 AS "22点", 23 AS "23点"
    )
)
ORDER BY 统计日期 ASC;

2. 按日期+小时展示行数据 #

如果不需要转置列，仅需按日期和小时分行展示统计结果，直接使用分组查询即可：

SELECT 
    TRUNC(CREATED_DATE) AS 统计日期,
    EXTRACT(HOUR FROM CREATED_DATE) AS 小时,
    COUNT(*) AS 总记录数
FROM IMAGE_HISTORY
WHERE CREATED_DATE >= SYSDATE - 7
GROUP BY TRUNC(CREATED_DATE), EXTRACT(HOUR FROM CREATED_DATE)
ORDER BY 统计日期 ASC, 小时 ASC;

内容的提问来源于stack exchange，提问作者Jyoti Prakash Mallick