SQL Server中分组相似对象日期范围以获取最小最大日期

这是个很典型的连续时间区间合并需求，在SQL Server里我们可以借助窗口函数来优雅解决，下面是具体的实现步骤和代码：

第一步：创建测试数据（方便验证） #

先把你提供的数据放到临时表中，方便我们测试查询逻辑：

CREATE TABLE #temp_data (
    account VARCHAR(20),
    onln_status VARCHAR(3),
    browse_status VARCHAR(1),
    beg_date DATE,
    end_date DATE
);

INSERT INTO #temp_data VALUES
('123456789', 'On', 'Y', '2018-01-01', '2018-02-01'),
('123456789', 'On', 'N', '2018-02-02', '2018-04-01'),
('123456789', 'On', 'Y', '2018-04-02', '2018-05-01'),
('123456789', 'Off', 'N', '2018-05-02', '2018-07-01'),
('123456789', 'Off', 'Y', '2018-07-02', '2018-08-01'),
('123456789', 'On', 'Y', '2018-08-02', '2018-10-01'),
('123456789', 'On', 'N', '2018-10-02', '2018-11-01');

第二步：核心查询逻辑 #

我们用两个CTE（公共表表达式）来实现分组：

WITH ranked_data AS (
    SELECT 
        account,
        onln_status,
        browse_status,
        beg_date,
        end_date,
        -- 判断当前记录是否是新分组的起点：如果和同组上一条的结束日期不连续，标记为1
        CASE 
            WHEN LAG(end_date) OVER (PARTITION BY account, onln_status, browse_status ORDER BY beg_date) + 1 = beg_date 
            THEN 0 
            ELSE 1 
        END AS is_new_group
    FROM #temp_data
),
grouped_data AS (
    SELECT 
        *,
        -- 累积求和生成唯一分组ID，连续的区间会共享同一个ID
        SUM(is_new_group) OVER (PARTITION BY account, onln_status, browse_status ORDER BY beg_date ROWS UNBOUNDED PRECEDING) AS group_id
    FROM ranked_data
)
SELECT 
    account,
    onln_status,
    browse_status,
    MIN(beg_date) AS beg_date,
    MAX(end_date) AS end_date
FROM grouped_data
GROUP BY account, onln_status, browse_status, group_id
ORDER BY account, beg_date;

逻辑解释 #

1. ranked_data CTE：

   • 用LAG窗口函数获取同一account+onln_status+browse_status分组内，上一条记录的end_date
   • 判断当前记录的beg_date是否和上一条的end_date连续（即上一条结束日+1天等于当前开始日），不连续的话标记为新分组起点（is_new_group=1）

2. grouped_data CTE：

   • 对is_new_group做累积求和，这样每个连续的时间区间会被分配同一个group_id，新的区间会生成新的ID

3. 最终聚合查询：

   • 按account、onln_status、browse_status和group_id分组，取每组的最小开始日期和最大结束日期，就得到了合并后的结果

查询结果 #

执行上述代码后，会得到如下输出：

这个结果完全符合你的需求：相同属性的连续时间区间被合并，非连续的则保持独立分组。

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