基于SQL实现医疗领域指定分组内订阅者活跃状态判定及错误处理

核心规则 #

• 订阅者仅存在于W0051752或E0001000其中一个分组 → 标记为活跃
• 订阅者同时存在于两个指定分组 → 标记为非活跃，并写入CDM错误表
• 订阅者存在于指定分组以外的其他分组 → 写入CDM错误表

实现思路 #

先通过分组统计每个订阅者在指定分组中的出现次数，以及是否存在非指定分组，再基于统计结果判定活跃状态并处理错误日志。

完整SQL代码 #

1. 统计订阅者分组情况（CTE）

WITH subscriber_group_stats AS (
    SELECT 
        subscriber_id,
        -- 统计订阅者在指定两个分组中的数量
        SUM(CASE WHEN grp_id IN ('W0051752', 'E0001000') THEN 1 ELSE 0 END) AS valid_group_count,
        -- 标记是否存在非指定分组
        MAX(CASE WHEN grp_id NOT IN ('W0051752', 'E0001000') THEN 1 ELSE 0 END) AS has_invalid_group
    FROM 
        your_subscriber_table -- 替换为实际的订阅者-分组关联表名
    GROUP BY 
        subscriber_id
)

2. 判定活跃状态

SELECT 
    subscriber_id,
    -- 活跃状态判定
    CASE
        WHEN valid_group_count = 1 THEN '活跃'
        WHEN valid_group_count = 2 THEN '非活跃'
        ELSE '未定义' -- 对应存在非指定分组的情况
    END AS active_status,
    -- 错误码赋值
    CASE
        WHEN valid_group_count = 2 OR has_invalid_group = 1 THEN 'E'
        ELSE 'V'
    END AS err_cd
FROM 
    subscriber_group_stats;

3. 写入CDM错误日志

-- 情况1：订阅者同时存在于两个指定分组
INSERT INTO cdm_error_table (subscriber_id, err_cd, err_msg, create_time) -- 替换为实际错误表字段
SELECT 
    subscriber_id,
    'E',
    '订阅者同时属于W0051752和E0001000分组',
    CURRENT_TIMESTAMP
FROM 
    subscriber_group_stats
WHERE 
    valid_group_count = 2;

-- 情况2：订阅者存在非指定分组
INSERT INTO cdm_error_table (subscriber_id, err_cd, err_msg, create_time)
SELECT 
    s.subscriber_id,
    'E',
    CONCAT('订阅者存在非指定分组: ', GROUP_CONCAT(DISTINCT s.grp_id)),
    CURRENT_TIMESTAMP
FROM 
    your_subscriber_table s
JOIN 
    subscriber_group_stats gs ON s.subscriber_id = gs.subscriber_id
WHERE 
    gs.has_invalid_group = 1
GROUP BY 
    s.subscriber_id;

注意事项 #

• 替换your_subscriber_table为实际存储订阅者与分组关联数据的表名
• 替换cdm_error_table为实际的CDM错误日志表名，需根据表结构调整插入字段
• 字符串聚合函数GROUP_CONCAT需根据数据库类型调整：SQL Server用STRING_AGG，Oracle用LISTAGG，PostgreSQL用STRING_AGG

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