如何用SQL为指定字段组合的重复行基于日期生成状态列？

核心思路 #

通过窗口函数对med_id、casemgr_id、casemgr_clnt_id组合分组，标记组内行号并获取首行日期，再通过日期间隔判断后续行的状态：

• 组内第一行直接标记为valid
• 后续行与首行日期间隔超过6个月则标记为valid，否则为invalid

通用SQL代码示例 #

假设日期字段为record_date（请替换为实际业务日期字段），表名为your_table：

SELECT
    med_id,
    casemgr_id,
    casemgr_clnt_id,
    record_date,
    CASE
        WHEN ROW_NUMBER() OVER (
            PARTITION BY med_id, casemgr_id, casemgr_clnt_id 
            ORDER BY record_date -- 若首行指插入顺序，替换为插入时间戳（如create_at）
        ) = 1 THEN 'valid'
        WHEN record_date > FIRST_VALUE(record_date) OVER (
            PARTITION BY med_id, casemgr_id, casemgr_clnt_id 
            ORDER BY record_date
        ) + INTERVAL '6 months' THEN 'valid'
        ELSE 'invalid'
    END AS status
FROM your_table;

不同数据库的日期函数适配 #

• MySQL：将INTERVAL '6 months'替换为INTERVAL 6 MONTH，也可使用DATE_ADD函数：

  WHEN record_date > DATE_ADD(
      FIRST_VALUE(record_date) OVER (PARTITION BY med_id, casemgr_id, casemgr_clnt_id ORDER BY record_date),
      INTERVAL 6 MONTH
  ) THEN 'valid'
  

• SQL Server：使用DATEADD函数：

  WHEN record_date > DATEADD(month, 6, 
      FIRST_VALUE(record_date) OVER (PARTITION BY med_id, casemgr_id, casemgr_clnt_id ORDER BY record_date)
  ) THEN 'valid'
  

逻辑验证 #

对应你的示例场景：

1. 组合med_id=98410、casemgr_id=12345、casemgr_clnt_id=67891：
   • 首行状态为valid
   • 后续行日期与首行间隔不足6个月，触发ELSE分支，状态为invalid
2. 组合med_id=91956、casemgr_id=99012、casemgr_clnt_id=87567：
   • 首行状态为valid
   • 后续行日期与首行间隔超过6个月，触发第二个WHEN分支，状态为valid

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