Oracle SQL实现按时间戳区间分组创建视图的方法

实现思路 #

用递归公共表表达式（CTE）实现你要求的顺序判断逻辑：

1. 先将所有记录按添加时间、ID升序排序，保证处理顺序符合你要求的「依次检查后续连续记录」的规则
2. 递归处理每一条记录：判断当前记录和当前分组的首条记录时间差是否在1秒内，符合则归入当前分组，不符合则开启新分组
3. 最后按分组ID聚合，拼接组内ID列表，取分组首条的流派和时间即可

完整实现代码 #

CREATE OR REPLACE VIEW song_time_groups AS
WITH sorted_songs AS (
    -- 按时间、ID排序，统一处理顺序，date_added如果本身是TIMESTAMP类型可跳过转换
    SELECT 
        ID,
        name,
        genre,
        TO_TIMESTAMP(date_added, 'YY/MM/DD HH24:MI:SS,FF3') AS date_added_ts,
        date_added AS original_date
    FROM songs
    ORDER BY date_added_ts, ID
),
recursive_groups (ID, genre, date_added_ts, original_date, group_id, group_start_ts, first_genre) AS (
    -- 递归锚点：取第一条记录作为第一个分组的起点
    SELECT 
        ID,
        genre,
        date_added_ts,
        original_date,
        1 AS group_id,
        date_added_ts AS group_start_ts,
        genre AS first_genre
    FROM sorted_songs
    WHERE ROWNUM = 1
    UNION ALL
    -- 递归处理后续所有记录
    SELECT 
        s.ID,
        s.genre,
        s.date_added_ts,
        s.original_date,
        CASE WHEN s.date_added_ts - rg.group_start_ts <= INTERVAL '1' SECOND 
             THEN rg.group_id 
             ELSE rg.group_id + 1 END AS group_id,
        CASE WHEN s.date_added_ts - rg.group_start_ts <= INTERVAL '1' SECOND 
             THEN rg.group_start_ts 
             ELSE s.date_added_ts END AS group_start_ts,
        CASE WHEN s.date_added_ts - rg.group_start_ts <= INTERVAL '1' SECOND 
             THEN rg.first_genre 
             ELSE s.genre END AS first_genre
    FROM sorted_songs s
    JOIN recursive_groups rg ON s.ID = rg.ID + 1
)
-- 按分组聚合得到最终结果
SELECT 
    group_id AS ID,
    MAX(first_genre) AS first_genre,
    '[' || LISTAGG(ID, ',') WITHIN GROUP (ORDER BY ID) || ']' AS ids,
    MIN(original_date) AS first_date
FROM recursive_groups
GROUP BY group_id
ORDER BY group_id;

注意事项 #

• 如果你的date_added字段本身就是TIMESTAMP类型，可删除sorted_songs中的类型转换逻辑，直接使用原字段即可
• LISTAGG是Oracle 11gR2及以上版本支持的聚合函数，如果你使用的版本更低，可以替换为WM_CONCAT实现ID拼接
• 视图创建完成后，直接执行SELECT * FROM song_time_groups即可得到你需要的分组结果

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