基于共享列值与连续日期范围的Rank计算方案问询

需求明确 #

将临时表中满足以下条件的记录归为同一组，并为每个组分配唯一不重复的rank_t：

• 拥有相同的id1、id2、flag
• 记录间日期连续：后一条记录的startdate = 前一条记录的enddate + 1天

临时表创建与样例数据 #

-- 创建临时表
CREATE TEMPORARY TABLE temp_data (
    id1 INT,
    id2 INT,
    flag VARCHAR(10),
    startdate DATE,
    enddate DATE
);

-- 插入样例数据
INSERT INTO temp_data VALUES
(1, 1, 'A', '2023-01-01', '2023-01-02'),
(1, 1, 'A', '2023-01-03', '2023-01-05'),
(1, 1, 'A', '2023-01-06', '2023-01-07'),
(1, 2, 'A', '2023-01-01', '2023-01-01'),
(1, 2, 'B', '2023-01-02', '2023-01-03'),
(2, 1, 'A', '2023-01-01', '2023-01-03');

期望输出 #

实现代码与步骤 #

核心思路 #

通过窗口函数标记新组起始点，累积求和生成组内标识，最后全局生成唯一rank_t。

WITH ranked_data AS (
    SELECT 
        *,
        -- 标记当前记录是否为新组起始：前一条enddate+1不等于当前startdate则为新组
        CASE 
            WHEN DATE_ADD(LAG(enddate) OVER (PARTITION BY id1, id2, flag ORDER BY startdate), INTERVAL 1 DAY) = startdate 
            THEN 0 
            ELSE 1 
        END AS is_new_group
    FROM temp_data
),
grouped_data AS (
    SELECT 
        *,
        -- 累积求和is_new_group，同组连续记录会得到相同的group_id
        SUM(is_new_group) OVER (PARTITION BY id1, id2, flag ORDER BY startdate) AS group_id
    FROM ranked_data
)
-- 对全局唯一的(id1, id2, flag, group_id)组合生成连续唯一的rank_t
SELECT 
    *,
    DENSE_RANK() OVER (ORDER BY id1, id2, flag, group_id) AS rank_t
FROM grouped_data
ORDER BY id1, id2, flag, startdate;

步骤解释 #

1. ranked_data：使用LAG()窗口函数获取同id1/id2/flag分组内前一条记录的enddate，判断当前记录是否与前一条连续，生成is_new_group（1=新组开始，0=延续上一组）。
2. grouped_data：对同分组内的is_new_group做累积求和，得到每个记录的组内唯一标识group_id——连续的记录会共享同一个group_id。
3. 最终查询：用DENSE_RANK()对全局的(id1, id2, flag, group_id)组合排序，生成唯一不重复的rank_t，确保不同组的rank_t无重复。

跨数据库兼容说明 #

• PostgreSQL：日期计算语法改为LAG(enddate) OVER (...) + INTERVAL '1 day'
• SQL Server：日期计算语法改为DATEADD(day, 1, LAG(enddate) OVER (...))

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