如何用集合运算在T-SQL中计算会员账户日期范围？

我在#account表中存储了会员账户数据，需要统计会员在机构拥有任意账户的连续日期范围，以及对应时段内最早有效（或关闭时剩余最早）账户的开户支行。

示例说明 #

• 会员1：2024-01-01开立首账户，2024-01-05在Branch 2开立的第二个账户仍处于开放状态，首账户于2024-01-30关闭。该会员自2024-01-01起持续为会员，对应最早有效账户的开户支行为Branch 2。
• 会员2：2023-12-05开立账户并在2024-01-05关闭；2024-02-01开立第二个账户并在2024-02-08关闭，同时还有一个同期重叠的账户也同期关闭。

原始数据（#account表） #

期望结果（#member表） #

已实现的游标代码 #

我已经用游标实现了需求，但想知道有没有更简洁的集合运算方法完成这个操作。游标代码如下：

declare iterator cursor for 
    select ID, Branch, OpenDate, CloseDate
    from #account 
    where #account.ID is not NULL
    order by ID
        , OpenDate
        , coalesce(CloseDate, '2078-12-31') desc

open iterator
fetch next from iterator into @currID, @currBranch, @currOpenDate, @currCloseDate
while @@fetch_status = 0
begin
    if coalesce(@ID, '') != coalesce(@currID, '')
    begin
        insert into #term values (@ID, @Branch, @OpenDate, @CloseDate);
        set @ID = @currID;
        set @Branch = @currBranch;
        set @OpenDate = @currOpenDate;
        set @CloseDate = @currCloseDate;
    end;
    else if @currOpenDate >= @CloseDate
    begin
        insert into #term values (@ID, @Branch, @OpenDate, @CloseDate);
        set @Branch = @currBranch;
        set @OpenDate = @currOpenDate;
        set @CloseDate = @currCloseDate;
    end;
    else if coalesce(@currCloseDate, '2078-12-31') > @CloseDate
    begin
        set @CloseDate = @currCloseDate;
        set @Branch = @currBranch;
    end;
    fetch next from iterator into @currID, @currBranch, @currOpenDate, @currCloseDate
end
insert into #term values (@ID, @Branch, @OpenDate, @CloseDate);

close iterator
deallocate iterator

集合运算解决方案 #

可以用窗口函数+区间合并的思路实现，无需游标，具体步骤如下：

完整SQL代码 #

WITH account_clean AS (
    -- 处理空值：将未关闭账户的CloseDate替换为未来日期
    SELECT 
        ID,
        Branch,
        [Open Date] AS OpenDate,
        ISNULL([Close Date], '9999-12-31') AS CloseDate
    FROM #account
),
account_intervals AS (
    -- 标记每个账户是否属于新的连续时段
    SELECT 
        *,
        CASE 
            WHEN OpenDate > MAX(CloseDate) OVER (PARTITION BY ID ORDER BY OpenDate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
            THEN 1
            ELSE 0
        END AS IsNewPeriod
    FROM account_clean
),
period_groups AS (
    -- 生成每个会员的连续时段分组ID
    SELECT 
        *,
        SUM(IsNewPeriod) OVER (PARTITION BY ID ORDER BY OpenDate) AS PeriodID
    FROM account_intervals
),
period_summary AS (
    -- 聚合每个时段的日期范围，同时确定对应支行（取时段内结束最晚的账户的支行）
    SELECT 
        ID,
        PeriodID,
        MIN(OpenDate) AS PeriodStart,
        MAX(CloseDate) AS PeriodEnd,
        FIRST_VALUE(Branch) OVER (PARTITION BY ID, PeriodID ORDER BY CloseDate DESC, OpenDate) AS Branch
    FROM period_groups
    GROUP BY ID, PeriodID
)
-- 还原未关闭账户的CloseDate为NULL，输出最终结果
SELECT 
    ID,
    Branch,
    PeriodStart AS [Open Date],
    CASE WHEN PeriodEnd = '9999-12-31' THEN NULL ELSE PeriodEnd END AS [Close Date]
FROM period_summary
ORDER BY ID, PeriodStart;

逻辑说明 #

1. 数据清洗：把CloseDate为NULL的账户替换为一个极晚的日期（9999-12-31），统一处理未关闭账户的结束时间。
2. 识别连续时段：通过窗口函数对比当前账户的开户日期与之前所有账户的最晚结束日期，判断是否开启新的连续时段，并用累加标记生成时段分组ID。
3. 聚合时段信息：对每个时段取最早开户日期、最晚结束日期，同时通过FIRST_VALUE函数选取时段内结束最晚的账户对应的支行（符合需求中"最早有效/关闭时剩余最早"的逻辑）。
4. 结果还原：把替换的未来日期还原为NULL，输出符合要求的结果。

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