如何按15分钟时间块分组并统计过去一小时的事件ID数量

我有一张包含EVENT_START_TIMESTAMP（时间戳）和EVENTS_ID（事件ID）的表，需要按15分钟时间块（如00:15、00:30、00:45等）统计每个时间块对应的过去一小时内非空EVENTS_ID的数量。

示例表定义及数据 #

CREATE GLOBAL TEMPORARY TABLE my_temp( 
  EVENT_START_TIMESTAMP TIMESTAMP, 
  EVENTS_ID VARCHAR2(30)) ON COMMIT PRESERVE ROWS;
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:02:49.623', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:02:59.250', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:03:40.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208749);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:03:45.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208750);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 01:09:15.320', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:09:26.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208765);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:10:25.000', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:34:23.240', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:35:01.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208767);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 01:35:05.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208768);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 01:35:07.850', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:35:10.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208769);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:36:07.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208772);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:36:11.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208773);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:36:16.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208774);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:36:21.360', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:45:03.830', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:45:04.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208779);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 02:45:08.533', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:45:09.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208780);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:47:29.000', 'YYYY-MM-DD HH24:MI:SS.FF3'),  69208789);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 00:48:43.833', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 01:02:20.333', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
INSERT INTO my_temp VALUES(TO_TIMESTAMP('2022-09-26 01:02:21.973', 'YYYY-MM-DD HH24:MI:SS.FF3'), NULL);
SELECT * FROM my_temp;

当前遇到的问题 #

我写的SQL代码如下：

SELECT  Trunc(das.EVENT_START_TIMESTAMP, 'HH') + NUMTODSINTERVAL(floor((EXTRACT(SECOND  FROM (das.EVENT_START_TIMESTAMP - Trunc(das.EVENT_START_TIMESTAMP, 'HH')))
                + EXTRACT(MINUTE FROM (das.EVENT_START_TIMESTAMP - Trunc(das.EVENT_START_TIMESTAMP, 'HH')))*60)/900)*900+900, 'second') AS timestamp_blk_end
        , count(das.EVENTS_ID) OVER (PARTITION BY Trunc(das.EVENT_START_TIMESTAMP) ORDER BY das.EVENT_START_TIMESTAMP RANGE BETWEEN INTERVAL '3600' SECOND PRECEDING AND INTERVAL '0' SECOND FOLLOWING) AS processed_count
FROM my_temp das
GROUP by  Trunc(das.EVENT_START_TIMESTAMP, 'HH') + NUMTODSINTERVAL(floor((EXTRACT(SECOND  FROM (das.EVENT_START_TIMESTAMP - Trunc(das.EVENT_START_TIMESTAMP, 'HH')))
                + EXTRACT(MINUTE FROM (das.EVENT_START_TIMESTAMP - Trunc(das.EVENT_START_TIMESTAMP, 'HH')))*60)/900)*900+900, 'second')

执行后报错：ORA-00979: not a GROUP BY expression。如果把das.EVENT_START_TIMESTAMP, das.EVENTS_ID加入GROUP BY子句，能得到正确数值但会产生重复行，不符合需求。

需要得到如下格式的输出：

TIMESTAMP_BLK_END       PROCESSED_COUNT
2022-09-26 00:15:00.000         3
2022-09-26 00:30:00.000         3
2022-09-26 00:45:00.000         8
2022-09-26 01:00:00.000         11
2022-09-26 01:15:00.000         8
2022-09-26 01:30:00.000         8
2022-09-26 01:45:00.000         4
2022-09-26 02:00:00.000         1
2022-09-26 02:15:00.000         1
2022-09-26 02:30:00.000         1
2022-09-26 02:45:00.000         0
2022-09-26 03:00:00.000         0

要实现需求，需要先生成所有需要统计的15分钟时间块，再关联原表统计过去一小时内的非空EVENTS_ID数量，具体步骤如下：

1. 生成连续的15分钟时间块：通过递归CTE生成覆盖数据时间范围的所有15分钟结束时间点，确保即使某个时间块没有数据也能显示0。
2. 统计每个时间块的符合条件的事件数：对每个时间块，统计原表中时间在[时间块结束时间-1小时, 时间块结束时间)范围内的非空EVENTS_ID数量。

完整SQL代码：

WITH time_blocks AS (
    -- 生成起始时间：取表中最早时间的15分钟上边界
    SELECT TRUNC(MIN(EVENT_START_TIMESTAMP), 'HH') + 
           NUMTODSINTERVAL(FLOOR(EXTRACT(MINUTE FROM MIN(EVENT_START_TIMESTAMP))/15)*15 + 15, 'MINUTE') AS block_end
    FROM my_temp
    UNION ALL
    -- 递归生成后续15分钟块，直到超过表中最晚时间+1小时
    SELECT block_end + INTERVAL '15' MINUTE
    FROM time_blocks
    WHERE block_end < (SELECT TRUNC(MAX(EVENT_START_TIMESTAMP), 'HH') + INTERVAL '2' HOUR FROM my_temp)
),
valid_events AS (
    -- 过滤出非空EVENTS_ID的记录
    SELECT EVENT_START_TIMESTAMP
    FROM my_temp
    WHERE EVENTS_ID IS NOT NULL
)
SELECT 
    tb.block_end AS TIMESTAMP_BLK_END,
    COUNT(ve.EVENT_START_TIMESTAMP) AS PROCESSED_COUNT
FROM time_blocks tb
LEFT JOIN valid_events ve 
    ON ve.EVENT_START_TIMESTAMP >= tb.block_end - INTERVAL '1' HOUR
    AND ve.EVENT_START_TIMESTAMP < tb.block_end
GROUP BY tb.block_end
ORDER BY tb.block_end;

代码说明 #

• time_blocks CTE：递归生成所有需要统计的15分钟时间块结束时间，起始点取表中最早时间的下一个15分钟边界，结束点取最晚时间+2小时，确保覆盖所有需要统计的范围。
• valid_events CTE：提前过滤掉EVENTS_ID为空的记录，减少后续统计的计算量。
• 主查询：通过左关联将时间块和有效事件关联，统计每个时间块前一小时内的有效事件数，左关联保证没有事件的时间块显示0。

如果需要更简洁的时间块生成方式，也可以用TRUNC函数简化15分钟块的计算：

-- 替代time_blocks的简化方式（如果不需要强制覆盖到最晚时间+1小时，可按需调整）
WITH all_dates AS (
    SELECT TRUNC(MIN(EVENT_START_TIMESTAMP), 'DD') AS start_date,
           TRUNC(MAX(EVENT_START_TIMESTAMP), 'DD') + INTERVAL '1' DAY AS end_date
    FROM my_temp
),
time_blocks AS (
    SELECT start_date + NUMTODSINTERVAL((LEVEL-1)*15, 'MINUTE') AS block_end
    FROM all_dates
    CONNECT BY LEVEL <= (end_date - start_date)*24*4 -- 一天有96个15分钟块
)
-- 后续逻辑同上

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