如何按指定小时区间统计PRODUCT_ID数量（支持任意时间范围）

需求：统计00:15:00至01:15:00区间内的PRODUCT_ID数量，且方案需支持适配任意时间范围。

数据库结构及测试数据 #

CREATE TABLE time1 (cr_date date , product_id number );

insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12345);
insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12346);
insert into time1 values (to_date ('01-JAN-2022 01:00:00', 'DD_MON-YYYY HH:MI:SS') , 12347);
insert into time1 values (to_date ('01-JAN-2022 03:30:00', 'DD_MON-YYYY HH:MI:SS') , 42345);
insert into time1 values (to_date ('01-JAN-2022 03:30:00', 'DD_MON-YYYY HH:MI:SS') , 42346);
insert into time1 values (to_date ('01-JAN-2022 03:35:00', 'DD_MON-YYYY HH:MI:SS') , 42347);
insert into time1 values (to_date ('01-JAN-2022 03:40:00', 'DD_MON-YYYY HH:MI:SS') , 42348);
insert into time1 values (to_date ('01-JAN-2022 10:40:00', 'DD_MON-YYYY HH:MI:SS') , 10348);
insert into time1 values (to_date ('01-JAN-2022 10:42:00', 'DD_MON-YYYY HH:MI:SS') , 10349);
insert into time1 values (to_date ('01-JAN-2022 10:43:00', 'DD_MON-YYYY HH:MI:SS') , 11348);

COMMIT;

适配任意时间范围的统计方案 #

以下SQL通过生成完整的小时区间（以15分作为区间锚点），再与业务数据左关联，确保无数据的区间也能显示0值，同时支持修改时间范围适配不同需求：

WITH hour_intervals AS (
    -- 生成00:15到23:15的所有小时区间，可根据需求调整起止时间
    SELECT 
        TO_CHAR(TRUNC(TO_DATE('01-JAN-2022', 'DD-MON-YYYY'), 'DD') + (LEVEL-1)/24 + 15/1440, 'HH24:MI:SS') AS hours,
        TRUNC(TO_DATE('01-JAN-2022', 'DD-MON-YYYY'), 'DD') + (LEVEL-1)/24 + 15/1440 AS start_time,
        TRUNC(TO_DATE('01-JAN-2022', 'DD-MON-YYYY'), 'DD') + LEVEL/24 + 15/1440 AS end_time
    FROM dual
    CONNECT BY LEVEL <= 24
),
product_counts AS (
    -- 统计每个区间内的product_id数量
    SELECT 
        hi.hours,
        COUNT(t.product_id) AS count
    FROM hour_intervals hi
    LEFT JOIN time1 t 
        ON t.cr_date >= hi.start_time 
        AND t.cr_date < hi.end_time
    -- 如需指定时间范围，添加WHERE条件，例如：
    -- WHERE hi.start_time BETWEEN TO_DATE('01-JAN-2022 00:15:00', 'DD-MON-YYYY HH24:MI:SS') 
    --                         AND TO_DATE('01-JAN-2022 23:15:00', 'DD-MON-YYYY HH24:MI:SS')
    GROUP BY hi.hours, hi.start_time
    ORDER BY hi.start_time
)
SELECT hours, count FROM product_counts;

方案说明 #

1. hour_intervals CTE：生成一天中每个小时的XX:15:00作为区间起始，下一个XX:15:00作为区间结束，确保覆盖完整时间范围；
2. 左关联统计：通过左连接业务表，确保即使区间内无数据也能返回0；
3. 适配任意时间范围：修改hour_intervals中的起止日期，或添加WHERE条件即可调整统计的时间范围。

期望输出格式 #

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