Oracle中合并连续同服务的客户表行（仅日期字段不同）

这个问题属于典型的连续相同分组合并（常说的「岛屿问题」），你之前用GROUP BY customer_id, service取最小/最大日期的方式踩了个常见的坑——它会把同一个客户下所有同服务的记录（哪怕中间换过别的服务又换回来）都硬合并到一起，而你需要的是连续时间段内的同服务记录合并，得用窗口函数来识别连续的分组才行。

先明确你的数据与需求 #

原始Customer表数据：

customer_id | customer_effective_date | customer_term_date | service
------------|-------------------------|--------------------|--------
1           | 1/1/2017                | 1/31/2017          | Bike
1           | 2/1/2017                | 12/31/2017         | Bike
1           | 1/1/2018                | 1/31/2018          | Car
1           | 2/1/2018                | 2/28/2018          | Car
1           | 3/1/2018                | 3/31/2018          | Bike
1           | 4/1/2018                | 4/30/2018          | Bike

你期望的合并结果：

customer_id | customer_effective_date | customer_term_date | service
------------|-------------------------|--------------------|--------
1           | 1/1/2017                | 12/31/2017         | Bike
1           | 1/1/2018                | 2/28/2018          | Car
1           | 3/1/2018                | 4/30/2018          | Bike

解决方案：用窗口函数标记连续分组 #

核心思路是通过两个行号的差值，识别出连续的同服务时间段：

1. 给每个客户的记录按时间顺序分配全局行号
2. 给每个客户+服务的组合按时间顺序分配分组行号
3. 两个行号的差值相同的记录，就是连续的同服务段（服务切换时分组行号会重置，差值会变化）

通用SQL代码（支持CTE的数据库：MySQL 8+/PostgreSQL/SQL Server等）

WITH ranked_data AS (
    SELECT 
        customer_id,
        customer_effective_date,
        customer_term_date,
        service,
        -- 全局行号：按客户+生效日期排序
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_effective_date) AS global_rn,
        -- 分组行号：按客户+服务+生效日期排序
        ROW_NUMBER() OVER (PARTITION BY customer_id, service ORDER BY customer_effective_date) AS service_rn
    FROM Customer
)
SELECT 
    customer_id,
    MIN(customer_effective_date) AS customer_effective_date,
    MAX(customer_term_date) AS customer_term_date,
    service
FROM ranked_data
-- 用差值分组，标记连续的同服务段
GROUP BY customer_id, service, (global_rn - service_rn)
ORDER BY customer_id, customer_effective_date;

兼容旧版数据库的写法（比如MySQL 5.x，用子查询替代CTE）

SELECT 
    customer_id,
    MIN(customer_effective_date) AS customer_effective_date,
    MAX(customer_term_date) AS customer_term_date,
    service
FROM (
    SELECT 
        customer_id,
        customer_effective_date,
        customer_term_date,
        service,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_effective_date) AS global_rn,
        ROW_NUMBER() OVER (PARTITION BY customer_id, service ORDER BY customer_effective_date) AS service_rn
    FROM Customer
) AS ranked_data
GROUP BY customer_id, service, (global_rn - service_rn)
ORDER BY customer_id, customer_effective_date;

代码解释 #

• global_rn：每个客户下按时间顺序递增的行号，确保记录按时间排列
• service_rn：每个客户+服务组合下的行号，当服务切换时，这个行号会重新从1开始计数
• global_rn - service_rn：连续同服务的记录，这个差值是固定的；服务切换后，差值会变化，以此作为分组依据，最后取每组的最小生效日期和最大终止日期，就得到了你想要的合并结果。

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