如何跟踪rx_claims表中GPI字段变化并区分新药与剂量变更？

需求概述 #

现有rx_claims表，包含epi（患者标识）、drugname（药品名称）、gpi（16位药品编码）字段，存储历史处方数据。需要针对新增的同患者、同药品记录，检测其GPI编码的变更，并按规则标记变更类型。

变更检测规则 #

• 新药标记：新增记录的GPI前10位与同一epi+drugname组合的历史记录GPI前10位不一致时，标记为「新药」
• 剂量变更标记：新增记录的GPI前10位与历史记录一致，但前12位不一致时，输出新旧GPI数据并标记为「剂量变更」

示例场景 #

历史记录：epi=100、drugname=ibrofine、gpi=0123456789012300
新增记录：epi=100、drugname=ibrofine、gpi=0123456789000000
输出结果：drugname：ibrofine、oldgpi：0123456789012300、newgpi：0123456789000000，标记为「剂量变更」

实现SQL（以MySQL为例） #

假设表中存在create_time字段用于区分记录的新旧（新增记录的create_time晚于历史记录），以下SQL可实现需求：

SELECT
    curr.drugname,
    hist.gpi AS oldgpi,
    curr.gpi AS newgpi,
    CASE
        WHEN LEFT(curr.gpi, 10) != LEFT(hist.gpi, 10) THEN '新药'
        WHEN LEFT(curr.gpi, 12) != LEFT(hist.gpi, 12) THEN '剂量变更'
        ELSE '无变更'
    END AS change_type
FROM rx_claims curr
INNER JOIN rx_claims hist
    ON curr.epi = hist.epi
    AND curr.drugname = hist.drugname
    AND curr.create_time > hist.create_time
-- 关联同一患者同一药品的最新历史记录
WHERE hist.create_time = (
    SELECT MAX(create_time)
    FROM rx_claims
    WHERE epi = curr.epi AND drugname = curr.drugname AND create_time < curr.create_time
)
-- 仅筛选出有变更的记录
AND (LEFT(curr.gpi, 10) != LEFT(hist.gpi, 10) OR LEFT(curr.gpi, 12) != LEFT(hist.gpi, 12));

补充说明 #

• 如果表中没有create_time，可以用自增主键id替代（假设id越大记录越新），将SQL中的create_time替换为id即可
• 若需要批量检测所有历史记录中的变更，可调整关联逻辑，对比每一条记录与其前序的同患者同药品记录

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