Oracle SQL连续同类型记录分段聚合查询求助

需求说明 #

同一customer_id下，将连续出现的相同type记录按时间分段，统计每段的最小日期（min_date）和最大日期（max_date）。

解决方案SQL #

WITH grouped_data AS (
    SELECT 
        customer_id,
        type,
        "date",
        -- 标记当前记录与上一条type是否不同，不同则生成分组起始标记
        CASE 
            WHEN LAG(type) OVER (PARTITION BY customer_id ORDER BY TO_DATE("date", 'DD/MM/YYYY')) <> type 
            THEN 1 
            ELSE 0 
        END AS group_flag,
        -- 累计求和生成连续段的分组ID
        SUM(
            CASE 
                WHEN LAG(type) OVER (PARTITION BY customer_id ORDER BY TO_DATE("date", 'DD/MM/YYYY')) <> type 
                THEN 1 
                ELSE 0 
            END
        ) OVER (PARTITION BY customer_id ORDER BY TO_DATE("date", 'DD/MM/YYYY') ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS segment_id
    FROM customer_records
)
SELECT 
    customer_id,
    INITCAP(type) AS type, -- 转换成首字母大写，匹配期望输出格式
    TO_CHAR(MIN(TO_DATE("date", 'DD/MM/YYYY')), 'DD/MM/YYYY') AS min_date,
    TO_CHAR(MAX(TO_DATE("date", 'DD/MM/YYYY')), 'DD/MM/YYYY') AS max_date
FROM grouped_data
GROUP BY customer_id, segment_id, type
ORDER BY MAX(TO_DATE("date", 'DD/MM/YYYY')) DESC;

逻辑解释 #

1. 排序与标记分组边界：通过LAG()窗口函数获取当前记录的上一条同客户的type，如果与当前type不同，则标记为1（表示新分组开始），否则为0。
2. 生成连续段ID：对分组标记值做累计求和，相同连续type的记录会得到同一个segment_id，实现分段。
3. 分组统计：按customer_id、segment_id、type分组，计算每段的最小和最大日期，最后按最大日期降序排列，匹配期望输出的顺序。

注意事项 #

• 假设原表名为customer_records，如果实际表名不同请自行替换。
• 原表中的date字段是字符串格式，需用TO_DATE转换为日期类型进行排序和计算，确保时间顺序正确；结果用TO_CHAR转换回字符串格式匹配期望输出。
• INITCAP(type)用于将CAR 1转换为Car 1，如果不需要格式转换可去掉该函数。

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