Oracle单SQL实现按日期为行、小时为列的数据分组汇总查询

当然可以用单条SQL搞定这个需求！我们可以结合Oracle的PIVOT行转列功能，加上日期处理和聚合函数，直接生成你要的格式，还能包含每行的总计列。

思路解析 #

1. 划分小时区间：根据你的要求，01:00列要汇总00:01~01:00的数值，所以每条记录需要对应到它所在小时区间的结束时间作为列标识（比如00:01对应01:00列）。
2. 行转列透视：用PIVOT把不同的小时标识转成列，同时汇总对应区间的VAL总和。
3. 计算行总计：把每行的所有小时列数值相加，得到该行的Total。

完整SQL代码 #

SELECT
    TO_CHAR(day_date, 'DD.MM.YYYY') AS "日期",
    NVL("01:00", 0) AS "01:00",
    NVL("02:00", 0) AS "02:00",
    NVL("07:00", 0) AS "07:00",
    NVL("09:00", 0) AS "09:00",
    NVL("13:00", 0) AS "13:00",
    -- 计算每行的总计
    NVL("01:00", 0) + NVL("02:00", 0) + NVL("07:00", 0) + NVL("09:00", 0) + NVL("13:00", 0) AS "Total"
FROM (
    -- 预处理：提取日期、确定每条记录对应的小时列标识
    SELECT
        TRUNC(dt, 'DD') AS day_date,
        -- 把当前时间所在的小时区间结束时间作为列名（比如00:01对应01:00）
        TO_CHAR(TRUNC(dt, 'HH') + INTERVAL '1' HOUR, 'HH24:00') AS hour_col,
        val
    FROM test
)
-- 行转列，汇总每个小时区间的VAL总和
PIVOT (
    SUM(val)
    FOR hour_col IN (
        '01:00' AS "01:00",
        '02:00' AS "02:00",
        '07:00' AS "07:00",
        '09:00' AS "09:00",
        '13:00' AS "13:00"
    )
)
ORDER BY day_date;

代码详解 #

• 子查询部分：
  • TRUNC(dt, 'DD')提取日期的天部分，作为最终结果的行分组依据。
  • TRUNC(dt, 'HH') + INTERVAL '1' HOUR计算当前时间所在小时区间的结束时间，比如00:01对应的小时区间是00:00~01:00，结束时间是01:00，正好匹配你要求的列名规则。
• PIVOT部分：将hour_col的不同值转成列，并用SUM(val)汇总每个区间的数值。
• NVL函数：把没有数据的小时列的NULL值替换为0，避免总计计算时出现NULL。
• 总计列：直接将所有小时列的数值相加，得到该行的总和。

查询结果 #

执行后会得到如下格式的结果：

扩展说明 #

如果你的数据可能涉及所有24个小时，只需要在PIVOT的IN子句中补充所有'01:00'到'24:00'的列名即可，总计列也对应加上这些列的NVL值相加。

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