在MS SQL Server中实现相对60天日期分组

问题分析 #

你需要按人员分组，为事件创建动态60天窗口：每个窗口的起始判断基于前一个窗口的结束日期（EndDate + 60天），若当前事件的StartDate落在该窗口内则归为同一组，超出则生成新窗口。此前用LAG()函数的方案存在无法处理NULL、无法连续继承窗口结束日期、窗口重置逻辑失效的问题。

解决方案：递归CTE模拟Excel下拉逻辑 #

递归CTE可以逐行计算窗口结束日期，完美模拟Excel中E3=IF(C3<=E2,E2,D3+60)的下拉填充逻辑，解决LAG()无法连续继承值的问题。

步骤1：定义示例表（替换为你的实际表结构） #

CREATE TABLE PersonEvents (
    PersonID VARCHAR(50),
    EventID INT,
    StartDate DATE,
    EndDate DATE
);

INSERT INTO PersonEvents VALUES
('Person A', 1, '2023-01-01', '2023-01-10'),
('Person A', 2, '2023-02-15', '2023-02-20'),
('Person A', 3, '2023-04-20', '2023-04-25'),
('Person B', 1, '2023-03-01', '2023-03-05');

步骤2：递归CTE计算窗口结束日期并生成分组 #

WITH SortedEvents AS (
    -- 按人员和事件开始日期排序，生成行号
    SELECT 
        PersonID,
        EventID,
        StartDate,
        EndDate,
        ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY StartDate) AS RowNum
    FROM PersonEvents
),
RecursiveWindows AS (
    -- 锚点：每个人员的第一个事件，初始化窗口结束日期
    SELECT 
        PersonID,
        EventID,
        StartDate,
        EndDate,
        DATEADD(DAY, 60, EndDate) AS WindowEnd,
        RowNum
    FROM SortedEvents
    WHERE RowNum = 1

    UNION ALL

    -- 递归处理后续事件：判断是否继承上一窗口或重置
    SELECT 
        se.PersonID,
        se.EventID,
        se.StartDate,
        se.EndDate,
        CASE 
            WHEN se.StartDate <= rw.WindowEnd THEN rw.WindowEnd
            ELSE DATEADD(DAY, 60, se.EndDate)
        END AS WindowEnd,
        se.RowNum
    FROM SortedEvents se
    JOIN RecursiveWindows rw ON se.PersonID = rw.PersonID AND se.RowNum = rw.RowNum + 1
)
-- 生成分组ID：同一人员内，窗口结束日期相同的归为一组
SELECT 
    PersonID,
    EventID,
    StartDate,
    EndDate,
    WindowEnd,
    DENSE_RANK() OVER (PARTITION BY PersonID ORDER BY WindowEnd) AS GroupID
FROM RecursiveWindows
ORDER BY PersonID, StartDate;

结果说明 #

执行后会得到如下结果（符合你的需求）：

为什么这个方案能解决你的问题 #

1. 处理NULL值：锚点成员直接取每个人员的第一行，不存在LAG()返回NULL的情况。
2. 连续继承窗口值：递归逻辑会逐行继承上一窗口的结束日期，即使中间有多个事件都属于同一窗口，也不会断链。
3. 正确重置窗口：当事件的StartDate超出当前窗口时，自动用当前事件的EndDate+60生成新窗口，重置分组逻辑生效。

对LAG()方案问题的解释 #

你之前的LAG()方案失效，主要因为：

• LAG()只能取上一行的值，无法连续继承更早行的窗口结束日期（比如第3行需要继承第1行的窗口值，但LAG()只能取第2行的）。
• 未处理第一行的NULL情况，导致初始窗口计算错误。
• 可能未正确按PersonID分区或按StartDate排序，导致跨人员取数或判断逻辑混乱。

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