SQL Server 2014按90天间隔分组拼接Activity字段的实现需求

针对SQL Server 2014中，按手机号分组且相邻记录间隔超过90天则拆分分组的需求，可通过窗口函数生成分组标识 + STUFF+FOR XML拼接的组合实现，具体步骤如下：

1. 测试数据准备 #

先创建并插入测试数据模拟场景：

CREATE TABLE CallHistory (
    PhoneNumber VARCHAR(20),
    Activity VARCHAR(100),
    ActivityDate DATE
);

INSERT INTO CallHistory VALUES
('13800138000', '通话1', '2023-01-01'),
('13800138000', '通话2', '2023-03-15'), -- 与上一条间隔73天，同组
('13800138000', '通话3', '2023-06-20'), -- 与上一条间隔97天，拆分新组
('13800138000', '通话4', '2023-08-25'), -- 与上一条间隔66天，同组
('13900139000', '通话A', '2023-02-01'),
('13900139000', '通话B', '2023-05-10'); -- 与上一条间隔98天，拆分新组

2. 核心实现代码 #

通过CTE生成分组ID，再按分组拼接Activity：

WITH GroupedCalls AS (
    SELECT 
        PhoneNumber,
        Activity,
        ActivityDate,
        -- 标记是否需要拆分分组：相邻日期间隔超90天则标记为1
        CASE 
            WHEN DATEDIFF(DAY, LAG(ActivityDate) OVER (PARTITION BY PhoneNumber ORDER BY ActivityDate), ActivityDate) > 90
            THEN 1
            ELSE 0
        END AS GroupSplit,
        -- 累加标记值生成分组ID，同一连续时段的记录共享相同GroupID
        SUM(CASE 
                WHEN DATEDIFF(DAY, LAG(ActivityDate) OVER (PARTITION BY PhoneNumber ORDER BY ActivityDate), ActivityDate) > 90
                THEN 1
                ELSE 0
            END) OVER (PARTITION BY PhoneNumber ORDER BY ActivityDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupID
    FROM CallHistory
)
-- 按手机号+分组ID拼接活动记录
SELECT 
    PhoneNumber,
    GroupID,
    STUFF((SELECT ',' + Activity 
           FROM GroupedCalls gc2 
           WHERE gc2.PhoneNumber = gc1.PhoneNumber AND gc2.GroupID = gc1.GroupID 
           ORDER BY gc2.ActivityDate 
           FOR XML PATH('')), 1, 1, '') AS Activities,
    MIN(ActivityDate) AS 分组开始日期,
    MAX(ActivityDate) AS 分组结束日期
FROM GroupedCalls gc1
GROUP BY PhoneNumber, GroupID
ORDER BY PhoneNumber, GroupID;

3. 代码说明 #

• LAG函数：获取同一手机号下上一条记录的ActivityDate，用于计算日期间隔
• 分组标记与累加：通过判断间隔是否超90天生成拆分标记，再用SUM窗口函数累加标记值，得到每个记录所属的分组ID
• 拼接逻辑：基于手机号+分组ID进行二次分组，用STUFF+FOR XML PATH拼接同组内的Activity字段

执行后将得到按手机号拆分后的分组拼接结果，同时展示每个分组的起止日期。

内容的提问来源于stack exchange，提问作者Depth of Field