Oracle SQL查询需求：按30分钟间隔汇总日期时间区间数据

问题需求 #

需要生成Oracle SQL查询，按每30分钟间隔汇总指定日期内VALUE字段的总和，将时间区间内的记录分组聚合。

现有数据表数据 #

表reading中START_TIME和END_TIME为Date类型，原始数据如下：

**RDNG_DT       TAG ST_TIME     END_TM      VALUE**
10-Jan-23            ALB    10-Jan-23   10-Jan-23   2
10-Jan-23            ALB    10-Jan-23   10-Jan-23   4
10-Jan-23            ALB    10-Jan-23   10-Jan-23   6
10-Jan-23            ALB    10-Jan-23   10-Jan-23   8
10-Jan-23            ALB    10-Jan-23   10-Jan-23   2
10-Jan-23            ALB    10-Jan-23   10-Jan-23   2
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3
10-Jan-23            ALB    10-Jan-23   10-Jan-23   3

使用to_char格式化时间字段后的数据：

RDNG_DT     TAG         ST_TIME                     END_TM                  VALUE
=======================================================================================
10-Jan-23   ALB     10-JAN-23 12.00.00.000000000 AM  10-JAN-23 12.05.00.000000000 AM    2
10-Jan-23   ALB     10-JAN-23 12.05.00.000000000 AM  10-JAN-23 12.10.00.000000000 AM    4
10-Jan-23   ALB     10-JAN-23 12.10.00.000000000 AM  10-JAN-23 12.15.00.000000000 AM    6
10-Jan-23   ALB     10-JAN-23 12.15.00.000000000 AM  10-JAN-23 12.20.00.000000000 AM    8
10-Jan-23   ALB     10-JAN-23 12.20.00.000000000 AM  10-JAN-23 12.25.00.000000000 AM    2
10-Jan-23   ALB     10-JAN-23 12.25.00.000000000 AM  10-JAN-23 12.30.00.000000000 AM    2
10-Jan-23   ALB     10-JAN-23 12.30.00.000000000 AM  10-JAN-23 12.35.00.000000000 AM    3
10-Jan-23   ALB     10-JAN-23 12.35.00.000000000 AM  10-JAN-23 12.40.00.000000000 AM    3
10-Jan-23   ALB     10-JAN-23 12.40.00.000000000 AM  10-JAN-23 12.45.00.000000000 AM    3
10-Jan-23   ALB     10-JAN-23 12.45.00.000000000 AM  10-JAN-23 12.50.00.000000000 AM    3
10-Jan-23   ALB     10-JAN-23 12.50.00.000000000 AM  10-JAN-23 12.55.00.000000000 AM    3
10-Jan-23   ALB     10-JAN-23 12.55.00.000000000 AM  10-JAN-23 01.00.00.000000000 AM    3

期望结果 #

按30分钟区间分组，汇总每个区间的VALUE总和：

RDNG_DT     TAG       ST_TIME                 END_TM         VALUE
=======================================================================================
10-Jan-23   ALB     10-JAN-23 12.00.00.000000000 AM   10-JAN-23 12.30.00.000000000 AM    24
10-Jan-23   ALB     10-JAN-23 12.30.00.000000000 AM   10-JAN-23 01.00.00.000000000 AM    18

尝试的查询（未得到预期结果） #

SELECT
RDNG_DT,
TAG,
RDNG,
START_TIME + interval '30' minute AS START_TIME1
FROM (
SELECT 
RDNG_DT,
TAG,
value
to_CHAR(START_TIME, 'DD-MON-YYYY HH24:MI') AS START_TIME,
to_CHAR(END_TIME, 'DD-MON-YYYY HH24:MI') AS END_TIME

FROM reading 
where tag = 'ALB' 
AND RDNG_DT = '10-JAN-23'
 ) X

解决方案 #

可以通过将START_TIME截断到30分钟间隔的起始时间来分组，具体SQL如下：

SELECT
    RDNG_DT,
    TAG,
    -- 格式化区间起始时间
    TO_CHAR(interval_start, 'DD-MON-YYYY HH.MI.SS.FF9 AM') AS ST_TIME,
    -- 计算并格式化区间结束时间（起始+30分钟）
    TO_CHAR(interval_start + INTERVAL '30' MINUTE, 'DD-MON-YYYY HH.MI.SS.FF9 AM') AS END_TM,
    SUM(VALUE) AS VALUE
FROM (
    SELECT
        RDNG_DT,
        TAG,
        VALUE,
        -- 将START_TIME截断到最近的30分钟起始点
        TRUNC(START_TIME, 'HH') + FLOOR(TO_CHAR(START_TIME, 'MI') / 30) * INTERVAL '30' MINUTE AS interval_start
    FROM reading
    WHERE tag = 'ALB' 
      AND RDNG_DT = DATE '2023-01-10' -- 建议用DATE类型而非字符串，避免格式问题
)
GROUP BY RDNG_DT, TAG, interval_start
ORDER BY interval_start;

说明 #

1. 截断时间到30分钟区间：TRUNC(START_TIME, 'HH')先把时间截断到小时，再通过FLOOR(TO_CHAR(START_TIME, 'MI')/30)*30分钟计算当前小时内的30分钟区间起始点（比如12:05会被归到12:00区间，12:35归到12:30区间）。
2. 分组聚合：按RDNG_DT、TAG和计算出的interval_start分组，对VALUE求和。
3. 日期格式：使用DATE '2023-01-10'代替字符串比较，避免因会话日期格式不同导致的查询错误。

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