PL/SQL中按时间线合并同类型客户重复数据的技术问询

你遇到的是典型的时间区间合并问题，需要将同id、pp、type且时间连续/重叠的记录合并，同时拆分不连续的同类型记录。直接用GROUP BY聚合会把所有同类型记录合并，不符合需求，这里可以通过窗口函数结合分组聚合解决。

完整解决方案代码 #

WITH ranked_data AS (
    SELECT 
        id,
        pp,
        type,
        -- 若原始字段为字符串，需转换为日期类型，示例格式为DD.MM.RR
        TO_DATE(start_dt, 'DD.MM.RR') AS start_dt,
        TO_DATE(end_dt, 'DD.MM.RR') AS end_dt,
        -- 标记当前记录是否属于新时间组：当前开始日期超出上一条同组记录的结束日期则为新组
        CASE 
            WHEN TO_DATE(start_dt, 'DD.MM.RR') > LAG(TO_DATE(end_dt, 'DD.MM.RR')) OVER (PARTITION BY id, pp, type ORDER BY TO_DATE(start_dt, 'DD.MM.RR'))
            THEN 1 
            ELSE 0 
        END AS is_new_group
    FROM your_table_name
),
grouped_data AS (
    SELECT 
        id,
        pp,
        type,
        start_dt,
        end_dt,
        -- 累积求和生成分组ID，连续/重叠区间的记录会被分配到同一组
        SUM(is_new_group) OVER (PARTITION BY id, pp, type ORDER BY start_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM ranked_data
)
SELECT 
    id,
    pp,
    type,
    TO_CHAR(MIN(start_dt), 'DD.MM.RR') AS start_dt,
    TO_CHAR(MAX(end_dt), 'DD.MM.RR') AS end_dt,
    COUNT(*) AS cnt
FROM grouped_data
GROUP BY id, pp, type, group_id
ORDER BY start_dt;

代码分步解释 #

1. ranked_data CTE：

   • 使用LAG()窗口函数，按id、pp、type分组，按start_dt排序，获取当前记录的上一条同组记录的end_dt。
   • 通过CASE判断当前记录是否为新时间组：如果当前start_dt大于上一条的end_dt，说明时间线不连续，标记为1，否则标记为0。

2. grouped_data CTE：

   • 对is_new_group字段做累积求和，生成group_id。连续或重叠的时间区间会被分配到同一个group_id，不连续的区间会生成新的group_id。

3. 最终聚合：

   • 按id、pp、type、group_id分组，取每组的最小start_dt、最大end_dt，同时统计该组的记录数cnt。
   • 用TO_CHAR()将日期转换回原始字符串格式，匹配需求输出。

注意事项 #

• 若你的start_dt、end_dt已经是日期类型，可去掉TO_DATE()和TO_CHAR()转换，直接使用字段本身。
• 确保ORDER BY的排序字段正确，必须按start_dt排序才能保证LAG()函数取到正确的前序记录。

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