基于SQL Server 2014的SESSION日期合并与去重查询需求

示例DDL与测试数据 #

CREATE TABLE SessionRecords (
    SESSION_ID INT,
    SESSION_TYPE INT,
    START_DATE DATE,
    END_DATE DATE,
    SESSION_ENTER_DATE DATETIME,
    OTHER_FIELD VARCHAR(50)
);

INSERT INTO SessionRecords VALUES
(8642, 3256, '2024-01-01', '2024-01-03', '2024-01-01 09:00:00', 'A'),
(8642, 3256, '2024-01-01', '2024-01-03', '2024-01-01 10:00:00', 'B'),
(8642, 3256, '2024-01-04', '2024-01-05', '2024-01-04 08:30:00', 'C'),
(8642, 3256, '2024-01-06', '2024-01-08', '2024-01-06 11:00:00', 'D'),
(8642, 3256, '2024-01-02', '2024-01-02', '2024-01-02 12:00:00', 'E'),
(8642, 3256, '2024-01-09', '2024-01-10', '2024-01-09 09:00:00', 'F'),
(8642, 3256, '2024-01-11', '2024-01-11', '2024-01-11 10:00:00', 'G');

完整查询语句 #

WITH LatestPerStartDate AS (
    -- 步骤1：保留同一START_DATE下SESSION_ENTER_DATE最新的记录
    SELECT 
        SESSION_ID,
        SESSION_TYPE,
        START_DATE,
        END_DATE,
        SESSION_ENTER_DATE,
        OTHER_FIELD,
        ROW_NUMBER() OVER (PARTITION BY SESSION_ID, SESSION_TYPE, START_DATE ORDER BY SESSION_ENTER_DATE DESC) AS rn
    FROM SessionRecords
    WHERE SESSION_ID = 8642 AND SESSION_TYPE = 3256
),
FilteredLatest AS (
    SELECT * FROM LatestPerStartDate WHERE rn = 1
),
NonOverlapping AS (
    -- 步骤2：排除被其他会话日期区间完全覆盖的记录
    SELECT f.*
    FROM FilteredLatest f
    WHERE NOT EXISTS (
        SELECT 1
        FROM FilteredLatest f2
        WHERE f2.SESSION_ID = f.SESSION_ID
          AND f2.SESSION_TYPE = f.SESSION_TYPE
          AND f2.START_DATE <= f.START_DATE
          AND f2.END_DATE >= f.END_DATE
          AND (f2.START_DATE != f.START_DATE OR f2.END_DATE != f.END_DATE)
    )
),
DateGroups AS (
    -- 步骤3：通过GroupKey识别连续日期组
    SELECT 
        *,
        DATEADD(DAY, -ROW_NUMBER() OVER (PARTITION BY SESSION_ID, SESSION_TYPE ORDER BY START_DATE), START_DATE) AS GroupKey
    FROM NonOverlapping
)
-- 合并连续区间，保留起始START_DATE对应的字段
SELECT 
    SESSION_ID,
    SESSION_TYPE,
    MIN(START_DATE) AS START_DATE,
    MAX(END_DATE) AS END_DATE,
    FIRST_VALUE(OTHER_FIELD) OVER (PARTITION BY SESSION_ID, SESSION_TYPE, GroupKey ORDER BY START_DATE) AS OTHER_FIELD
FROM DateGroups
GROUP BY SESSION_ID, SESSION_TYPE, GroupKey
ORDER BY START_DATE;

目标输出说明 #

执行上述查询后，会得到符合需求的结果：

• 同START_DATE的记录保留了SESSION_ENTER_DATE最新的（B替代A）
• 被已有区间完全覆盖的记录（2024-01-02的E）被排除
• 连续的2024-01-09~2024-01-10与2024-01-11被合并为一个区间，保留起始记录的字段F

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