使用T-SQL按序列分组事件，定义序列结束条件为后续一年无事件

这是典型的**间隙与岛屿（Gaps and Islands）**场景，刚好可以用SQL的窗口函数来实现，逻辑和你Excel里的公式思路一致，我来一步步拆解实现：

核心逻辑 #

我们需要给每个PersonID下的事件划分序列：

• 同一个序列内的事件，相邻两个的间隔小于365天
• 如果某事件和上一个事件间隔≥365天，或者是该用户的第一个事件，就开启一个新序列

具体SQL实现（以SQL Server为例，其他数据库可微调） #

-- 步骤1：给每个用户的事件排序，并转换日期为可计算的标准格式
WITH ranked_events AS (
    SELECT 
        PersonID,
        CONVERT(DATE, Date, 103) AS EventDate, -- 103对应dd/mm/yyyy格式
        ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY CONVERT(DATE, Date, 103)) AS rn
    FROM tbl_events
),
-- 步骤2：标记每个事件是否是新序列的起点
gap_markers AS (
    SELECT 
        PersonID,
        EventDate,
        CASE 
            WHEN rn = 1 THEN 1 -- 第一个事件直接作为新序列起点
            -- 计算当前事件与上一个事件的间隔，≥365天则标记为新序列
            WHEN DATEDIFF(day, LAG(EventDate) OVER (PARTITION BY PersonID ORDER BY EventDate), EventDate) >= 365 THEN 1
            ELSE 0
        END AS is_new_sequence
    FROM ranked_events
),
-- 步骤3：累加标记值，生成序列编号
sequence_groups AS (
    SELECT 
        PersonID,
        EventDate,
        SUM(is_new_sequence) OVER (
            PARTITION BY PersonID 
            ORDER BY EventDate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Sequence
    FROM gap_markers
)
-- 步骤4：分组统计每个序列的信息
SELECT 
    PersonID,
    FORMAT(MIN(EventDate), 'dd/MM/yyyy') AS FirstDate, -- 转回原日期格式
    Sequence,
    COUNT(*) AS Events
FROM sequence_groups
GROUP BY PersonID, Sequence
ORDER BY PersonID, Sequence;

针对不同数据库的微调说明 #

• MySQL：日期转换用STR_TO_DATE(Date, '%d/%m/%Y')，日期差判断用DATEDIFF(EventDate, LAG(EventDate) OVER (...)) >= 365
• PostgreSQL：日期转换用TO_DATE(Date, 'DD/MM/YYYY')，日期差判断用(EventDate - LAG(EventDate) OVER (...)) >= INTERVAL '365 days'

逻辑和你Excel公式的对应关系 #

你Excel里的公式=IF(A2<>A3,1,IF((B3-B2)<365,C2,C2+1))，本质是通过判断上一行的ID和日期差来决定序列编号。SQL里的SUM(is_new_sequence)累加操作，和这个逻辑完全等价：每次遇到新序列起点（标记为1），序列编号就加1，后续事件继承当前编号，直到下一个新起点出现。

验证预期结果 #

比如PersonID=3的情况：

• 第一个事件2014-10-01是序列1，下一个事件2016-11-28和它间隔788天≥365，所以开启序列2
• 序列2里的三个事件（2016-11-28、2016-11-28、2017-01-16）间隔都小于365天，所以归为同一个序列，最终统计事件数3，和你的预期一致。

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