MS SQL Server 2019中基于动态日期范围的捐赠记录分组需求

需求说明 #

处理MS SQL Server 2019中的定期捐赠记录，每条记录包含FirstGiftDate（首次捐赠日期）和LastGiftDate（末次捐赠日期），需要为记录添加GroupedID，规则如下：

• 同一捐赠人下，若两条记录的间隔不超过45天，归为同一组，组内取最早的FirstGiftDate和最晚的LastGiftDate作为完整日期范围
• 间隔超过45天则分为不同组，每个捐赠人的分组计数从1重新开始

示例场景 #

• Bob的多次捐赠间隔均在45天内，所有记录归为同一GroupedID
• June首次捐赠后间隔6个月才再次捐赠，后续两次间隔在45天内，因此首次记录单独一组，后两组为同一组

初始尝试代码 #

曾尝试用自连接识别间隔45天内的记录，但无法实现关联分组：

SELECT #Donation.*, D2.*    
FROM #Donation
LEFT JOIN #Donation D2 ON #Donation.RecurringGiftID <> D2.RecurringGiftID
                       AND #Donation.Donor = D2.Donor 
                       AND ABS(DATEDIFF(DAY, #Donation.FirstGiftDate, D2.LastGiftDate)) < 45

表结构与示例数据 #

CREATE TABLE #Donation 
(
    RecurringGiftID int, 
    Donor nvarchar(25), 
    FirstGiftDate date, 
    LastGiftDate date
)

INSERT INTO #Donation 
VALUES (1, 'Bob', '2017-02-15', '2018-07-01'),
       (15, 'Bob', '2018-08-05', '2019-04-01'),
       (32, 'Bob', '2019-04-15', '2022-06-15'),
       (54, 'June', '2015-05-01', '2016-05-01'),
       (96, 'June', '2016-12-15', '2018-02-01'),
       (120, 'June', '2018-03-04', '2020-07-01')

期望输出 #

解决方案 #

可以使用窗口函数累加分组标记的方式实现，逻辑清晰且高效，适合SQL Server 2019及以上版本：

WITH DonationsOrdered AS (
    SELECT 
        *,
        -- 标记是否需要开启新分组：当前记录的首次日期与上一条的末次日期间隔>45天则为1，否则为0
        CASE WHEN DATEDIFF(DAY, LAG(LastGiftDate) OVER (PARTITION BY Donor ORDER BY FirstGiftDate), FirstGiftDate) > 45 THEN 1 ELSE 0 END AS NewGroupFlag
    FROM #Donation
)
SELECT 
    RecurringGiftID,
    Donor,
    FirstGiftDate,
    LastGiftDate,
    -- 累加标记得到分组ID，初始从1开始
    1 + SUM(NewGroupFlag) OVER (PARTITION BY Donor ORDER BY FirstGiftDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupedID
FROM DonationsOrdered
ORDER BY Donor, FirstGiftDate;

逻辑说明 #

1. 先按捐赠人分组、首次捐赠日期排序，用LAG()函数获取上一条记录的末次捐赠日期
2. 判断当前记录与上一条的间隔是否超过45天，生成新分组标记
3. 对每个捐赠人的标记进行累加，得到最终的GroupedID

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