如何合并同用户同编码的连续记录获取连续日期区间

需求规则 #

需要对单日粒度的明细数据做合并，生成连续日期区间段，合并规则如下：

• 同一login（登录账号）下，code（业务编码）相同、日期连续的记录聚合为一条
• 聚合后的date_start取连续段内最早日期，date_end取连续段内最晚日期

输入样例数据 #

login    date_start    date_end    code
    'user1', '2022-02-09', '2022-02-09'  DO
    'user1', '2022-02-10', '2022-02-10'  DO
    'user1', '2022-02-11', '2022-02-11'  DO
    'user1', '2022-03-28', '2022-03-28'  OT
    'user1', '2022-03-29', '2022-03-29'  OT
    'user1', '2022-03-30', '2022-03-30'  OT
    'user1', '2022-03-31', '2022-03-31'  OT
    'user1', '2022-04-01', '2022-04-01'  OT
    'user1', '2022-04-04', '2022-04-04'  DO

期望输出结果 #

login  date_start  date_end   
user1  2022-02-09  2022-02-11 
user1  2022-03-28  2022-04-01
user1  2022-04-04  2022-04-04

实现方案 #

这类连续区间合并问题通用解法是差值打标法，核心逻辑：

1. 按login、code分组，按日期升序给每条记录生成递增行号
2. 用每条记录的日期减去对应行号的天数，同一段连续日期计算出的差值完全一致，用这个差值作为连续段的分组标识
3. 按login、code、分组标识做聚合，取段内最小、最大日期即可得到结果

以MySQL 8.0+版本为例，实现代码如下：

WITH sorted AS (
    SELECT
        login,
        date_start,
        code,
        ROW_NUMBER() OVER (PARTITION BY login, code ORDER BY date_start) AS rn
    FROM raw_data
),
tagged AS (
    SELECT
        login,
        code,
        date_start,
        DATE_SUB(date_start, INTERVAL rn DAY) AS group_flag
    FROM sorted
)
SELECT
    login,
    MIN(date_start) AS date_start,
    MAX(date_start) AS date_end
FROM tagged
GROUP BY login, code, group_flag
ORDER BY date_start;

执行后返回结果和期望输出完全一致。

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