SQL Server中8周周期内安装与移除的抵消匹配实现咨询

需求明确 #

基于包含Week、Unit、Customer、Activity（仅Install/Removal两类）的业务数据，需为每个操作标记分类，并统计各类数量：

• Replacement Install：同一客户的该安装操作，在8周内存在未被匹配的移除操作，且双方仅匹配一次
• Replacement Removal：同一客户的该移除操作，在8周内存在未被匹配的安装操作，且双方仅匹配一次
• True Install：同一客户的该安装操作，8周内无匹配的移除操作
• True Removal：同一客户的该移除操作，8周内无匹配的安装操作

实现步骤与SQL代码 #

核心思路 #

通过给同客户同类型操作排序生成序号，再在时间窗口内关联对立操作，用ROW_NUMBER()实现一对一唯一匹配，最后根据匹配结果标记分类。

-- 步骤1：给每个客户的同类型操作生成排序序号，用于后续一对一匹配
WITH OperationWithSeq AS (
    SELECT 
        Week,
        Unit,
        Customer,
        Activity,
        ROW_NUMBER() OVER (PARTITION BY Customer, Activity ORDER BY Week, Unit) AS Seq
    FROM YourBusinessTable -- 替换为你的实际表名
),
-- 步骤2：关联对立操作，找出8周内的潜在匹配项并排序
MatchedPairs AS (
    -- 为每个安装操作找8周内的移除操作
    SELECT 
        i.Week AS InstallWeek,
        i.Unit AS InstallUnit,
        i.Customer AS InstallCustomer,
        r.Week AS RemovalWeek,
        r.Unit AS RemovalUnit,
        r.Customer AS RemovalCustomer,
        ROW_NUMBER() OVER (PARTITION BY i.Customer, i.Seq ORDER BY DATEDIFF(WEEK, r.Week, i.Week)) AS MatchRank
    FROM OperationWithSeq i
    LEFT JOIN OperationWithSeq r 
        ON i.Customer = r.Customer
        AND i.Activity = 'Install'
        AND r.Activity = 'Removal'
        AND DATEDIFF(WEEK, r.Week, i.Week) BETWEEN 0 AND 8 -- 移除发生在安装前0-8周内
    UNION ALL
    -- 为每个移除操作找8周内的安装操作
    SELECT 
        i.Week AS InstallWeek,
        i.Unit AS InstallUnit,
        i.Customer AS InstallCustomer,
        r.Week AS RemovalWeek,
        r.Unit AS RemovalUnit,
        r.Customer AS RemovalCustomer,
        ROW_NUMBER() OVER (PARTITION BY r.Customer, r.Seq ORDER BY DATEDIFF(WEEK, i.Week, r.Week)) AS MatchRank
    FROM OperationWithSeq r
    LEFT JOIN OperationWithSeq i 
        ON r.Customer = i.Customer
        AND r.Activity = 'Removal'
        AND i.Activity = 'Install'
        AND DATEDIFF(WEEK, i.Week, r.Week) BETWEEN 0 AND 8 -- 安装发生在移除前0-8周内
),
-- 步骤3：筛选出唯一匹配对（每个操作仅匹配一次）
UniqueMatches AS (
    SELECT *
    FROM MatchedPairs
    WHERE MatchRank = 1 -- 仅保留每个操作的第一个匹配项
),
-- 步骤4：为每个操作标记分类Type
OperationType AS (
    SELECT 
        o.Week,
        o.Unit,
        o.Customer,
        o.Activity,
        CASE
            WHEN o.Activity = 'Install' AND EXISTS (SELECT 1 FROM UniqueMatches m WHERE m.InstallUnit = o.Unit) THEN 'Replacement Install'
            WHEN o.Activity = 'Install' THEN 'True Install'
            WHEN o.Activity = 'Removal' AND EXISTS (SELECT 1 FROM UniqueMatches m WHERE m.RemovalUnit = o.Unit) THEN 'Replacement Removal'
            ELSE 'True Removal'
        END AS Type
    FROM YourBusinessTable o
)
-- 输出带分类的明细数据
SELECT * FROM OperationType;

-- 若需统计各类操作数量，执行以下语句
-- SELECT Type, COUNT(*) AS OperationCount FROM OperationType GROUP BY Type;

逻辑说明 #

1. OperationWithSeq：按客户、操作类型分组，按时间和Unit排序生成序号，确保同类型操作有唯一标识，避免重复匹配。
2. MatchedPairs：通过UNION ALL双向关联安装与移除操作，限定8周时间窗口，用MatchRank给每个操作的潜在匹配项排序，优先匹配时间最近的对立操作。
3. UniqueMatches：仅保留每个操作的第一个匹配项，保证一对一匹配规则（每个操作只能被匹配一次）。
4. OperationType：根据操作是否存在唯一匹配项，标记对应的分类Type。

示例数据验证 #

针对你提供的示例数据，执行上述代码后会得到如下分类结果：

• 2024-01-08的Install a → Replacement Install（匹配同一天的Removal c）
• 2024-01-08的Removal c → Replacement Removal（匹配同一天的Install a）
• 2024-01-08的Install b → Replacement Install（匹配2024-01-15的Removal d）
• 2024-01-15的Removal d → Replacement Removal（匹配2024-01-08的Install b）
• 2024-01-15的Install e → Replacement Install（匹配2024-01-22的Removal f）
• 2024-01-22的Removal f → Replacement Removal（匹配2024-01-15的Install e）
• 2024-01-22的Install g → True Install（8周内无匹配的移除）
• 2024-06-16的Removal h/i → True Removal（与2024-08-01的Install j间隔超过8周）
• 2024-08-01的Install j → True Install（8周内无匹配的移除）

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