如何基于多条件统计SQL Server中保单的所有者变更次数

需求说明 #

• RoleCode=8代表所有者
• 无RoleCode=8记录的保单，变更次数为0
• 变更判定规则：
  1. 同一保单多条RoleCode=8记录，第一条之后的每条算1次变更
  2. 某条RoleCode=8记录的SourceDate晚于任意一条RoleCode=32记录，该条算1次变更

测试表与数据 #

CREATE TABLE PolicyTable (
PolicyNumber VARCHAR(10),
RoleCode INT,
SourceDate DATE
);

-- Insert data into the table
INSERT INTO PolicyTable (PolicyNumber, RoleCode, SourceDate)
VALUES
('660169245', 34, '2023-10-03'),
('660169245', 31, '2023-10-03'),
('660169245', 8, '2023-10-05'),
('660169245', 32, '2023-10-03'),
('660169243', 8, '2023-10-04'),
('660169243', 41, '2023-10-01'),
('660169243', 32, '2023-09-10'),
('660169243', 38, '2023-09-10'),
('660169243', 107, '2023-09-10'),
('660169243', 8, '2023-09-10'),
('660169243', 38, '2023-09-30'),
('660169243', 107, '2023-09-30'),
('660169243', 8, '2023-09-30'),
('660169238', 8, '2023-01-01'),
('660169238', 32, '2023-01-01'),
('660169238', 107, '2023-01-01'),
('660169238', 38, '2023-01-01'),
('660169236A', 107, '2023-05-31'),
('660169236A', 38, '2023-06-10'),
('660169236A', 8, '2023-06-10'),
('660169236A', 8, '2023-05-31'),
('660169236A', 32, '2023-05-15'),
('660169236A', 38, '2023-05-15'),
('660169236A', 8, '2023-05-15'),
('660169236A', 107, '2023-05-15'),
('660169236A', 32, '2023-05-31');

现有代码缺陷 #

现有代码仅实现了第一条规则，无法处理第二条规则（比如保单660169245的OwnerChanges当前输出0，预期为1）：

WITH OwnerChangeCTE AS (
SELECT
    PolicyNumber,
    RoleCode,
    SourceDate,
    ROW_NUMBER() OVER (PARTITION BY PolicyNumber ORDER BY SourceDate) AS RowNum
FROM
    PolicyTable
WHERE
    RoleCode = 8
)

SELECT
p.PolicyNumber,
COUNT(DISTINCT CASE
    WHEN o1.RowNum = 1 THEN NULL
    WHEN o2.RoleCode = 32 AND o2.SourceDate < o1.SourceDate THEN NULL
    ELSE o1.RowNum
END) AS OwnerChanges
FROM PolicyTable p
LEFT JOIN OwnerChangeCTE o1
  ON p.PolicyNumber = o1.PolicyNumber
LEFT JOIN OwnerChangeCTE o2
  ON p.PolicyNumber = o2.PolicyNumber
  AND o1.RowNum < o2.RowNum
GROUP BY
  p.PolicyNumber

当前输出 #

预期输出 #

修正后的代码 #

WITH OwnerRecords AS (
    -- 提取所有所有者记录并按日期排序编号
    SELECT 
        PolicyNumber,
        SourceDate,
        ROW_NUMBER() OVER (PARTITION BY PolicyNumber ORDER BY SourceDate) AS RowNum
    FROM PolicyTable
    WHERE RoleCode = 8
),
PolicyRole32Dates AS (
    -- 提取每个保单的所有RoleCode=32的记录日期
    SELECT 
        PolicyNumber,
        SourceDate AS Role32Date
    FROM PolicyTable
    WHERE RoleCode = 32
)
SELECT 
    p.PolicyNumber,
    -- 统计符合变更规则的记录数
    COUNT(DISTINCT 
        CASE 
            WHEN o.RowNum > 1 THEN o.RowNum
            WHEN EXISTS (SELECT 1 FROM PolicyRole32Dates r WHERE r.PolicyNumber = o.PolicyNumber AND r.Role32Date < o.SourceDate) THEN o.RowNum
            ELSE NULL
        END
    ) AS OwnerChanges
FROM (SELECT DISTINCT PolicyNumber FROM PolicyTable) p
LEFT JOIN OwnerRecords o ON p.PolicyNumber = o.PolicyNumber
GROUP BY p.PolicyNumber;

代码说明 #

1. OwnerRecords CTE：筛选所有RoleCode=8的记录，按保单分组、日期排序并编号，用于识别第一条之后的所有者记录。
2. PolicyRole32Dates CTE：提取每个保单的RoleCode=32记录日期，用于判断所有者记录是否晚于这类记录。
3. 主查询：
   • 先获取所有唯一保单编号，确保无所有者记录的保单也能被统计（变更次数为0）。
   • 通过CASE语句判断每条所有者记录是否符合变更规则：
     • 若为第一条之后的记录（RowNum>1），计入变更；
     • 若第一条记录日期晚于任意Role32记录日期，计入变更；
     • 否则不计入。
   • 使用COUNT(DISTINCT)避免重复统计同一记录。

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