结合GROUP BY与CASE语句实现疫苗剂次标识的SQL查询

基础信息 #

• 待查询表：tbl
• 表样例数据：

ID  vaccine_code     vaccine_day
A   1A               0
A   2A               30
B   moderna,1A       0
B   moderna,2A       35
C   moderna          0
C   moderna          25

• 业务规则：
  • vaccine_code中包含1A代表第一剂，包含2A代表第二剂
  • 编码无剂次标识的记录（如ID=C的纯moderna记录），按同ID下vaccine_day从小到大排序，依次对应第1剂、第2剂
• 期望输出（注：样例中ID=B的dose1对应vaccine_day=5为笔误，和原始数据匹配应为0）：

ID  vaccine_dose   vaccine_day
A   dose1          0
A   dose2          30
B   dose1          0
B   dose2          35
C   dose1          0
C   dose2          25

原有SQL错误点 #

1. CASE语法错误：多条件判断仅需开头声明一次CASE，结尾加END，不需要每个分支重复写CASE
2. 聚合使用错误：直接在行级判断中写min(vaccine_day)没有指定分组范围，无法匹配到同ID下的最小接种天
3. 逻辑笔误：moderna对应最大接种天的分支，结果错写为dose1，实际应为dose2
4. 分组逻辑错误：按vaccine_day分组会合并不同ID同天的记录，无法按用户维度区分剂次

修正后可直接运行的SQL #

SELECT
    ID,
    CASE
        WHEN vaccine_code LIKE '%1A%' THEN 'dose1'
        WHEN vaccine_code LIKE '%2A%' THEN 'dose2'
        WHEN vaccine_code = 'moderna' AND vaccine_day = MIN(vaccine_day) OVER (PARTITION BY ID) THEN 'dose1'
        WHEN vaccine_code = 'moderna' AND vaccine_day = MAX(vaccine_day) OVER (PARTITION BY ID) THEN 'dose2'
    END AS vaccine_dose,
    vaccine_day
FROM tbl;

逻辑说明 #

• 优先匹配vaccine_code中明确标注的1A/2A剂次标识
• 无明确标识的记录，通过窗口函数按ID分区，计算每个用户的最小、最大接种天数，和当前行的vaccine_day匹配即可得到对应剂次，不需要额外GROUP BY，可以保留所有原始接种记录

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