跨年后Lag/First_value函数失效，求SQL记录有效性判定方案

输入数据 #

with
--my input--
x(id1,med_id,dt,dt_tm,casemgr_id,casemgr_clntid,status) as (
select 123456,98410,date'2024-04-19',timestamp'2024-04-19 09:00:00',12345,67891,-2
union all
select 194567,98410,date'2024-04-19',timestamp'2024-04-19 11:00:00',12345,67891,-2
union all
select 789101,98410,date'2024-04-24',timestamp'2024-04-24 09:00:00',12345,67891,-2
union all
select 194587,98410,date'2024-04-25',timestamp'2024-04-25 09:00:00',12345,67891,-2
union all
select 234561,98410,date'2024-04-26',timestamp'2024-04-26 09:00:00',12345,67891,-2
union all
select 456789,91956,date'2024-04-19',timestamp'2024-04-19 08:00:00',99012,87567,-2
union all
select 998415,91956,date'2024-12-20',timestamp'2024-12-20 07:00:00',99012,87567,-2
union all
select 01786,18745,date'2023-11-16',timestamp'2023-11-16 07:00:00',12434,87438,-2
union all
select 01786,18745,date'2023-11-21',timestamp'2023-11-21 07:00:00',12434,87438,-2
union all
select 01786,18745,date'2023-12-01',timestamp'2023-12-01 07:00:00',12434,87438,-2
union all
select 01786,18745,date'2023-12-04',timestamp'2023-12-04 07:00:00',12434,87438,-2
union all
select 01786,18745,date'2024-02-02',timestamp'2024-02-02 07:00:00',12434,87438,-2
union all
select 01786,18745,date'2024-07-05',timestamp'2024-07-05 07:00:00',12434,87438,-2
union all
select 01786,18745,date'2024-07-21',timestamp'2024-07-21 07:00:00',12434,87438,-2
union all
select 01786,18745,date'2024-07-21',timestamp'2024-07-23 07:00:00',12434,87438,-2
);

需求说明 #

• 按med_id、casemgr_clntid分组
• 每组内按dt_tm排序后的第一条记录标记为valid
• 180天内的后续记录标记为invalid
• 超过180天的第一条记录标记为valid，并以此为新的时间窗口起点，后续180天内记录为invalid
• 同一天多条记录中，时间戳更早的为valid，其余为invalid

尝试过的SQL（存在问题） #

-- sql qry which was suggested, tweaked to explictly put the date format...
SELECT
  id1
, med_id
, dt
,dt_tm
, casemgr_id
, casemgr_clntid
, CASE WHEN (
           to_char(dt,'yyyy-mm-dd)' -
         , lag(to_char(dt,'yyyy-mm-dd')) OVER(PARTITION BY med_id, casemgr_clntid ORDER BY dt_tm)
         )  is null
      OR (
           to_char(dt,'yyyy-mm-dd)' -
         , lag(to_char(dt,'yyyy-mm-dd')) OVER(PARTITION BY med_id, casemgr_clntid ORDER BY dt_tm)

         )  >180
    THEN 'valid'
    ELSE 'invalid'
  END AS status
FROM x
ORDER BY med_id, casemgr_clntid, dt_tm 
;

问题点 #

• 将日期转为字符串相减存在语法错误
• 使用dt_tm - first_value(dt_tm)时提示“invalid operation”错误
• 跨年后Lag/First_value函数无法正常工作，无法得到期望输出

期望输出 #

with
    --my desired output--
    x(id1,med_id,dt,dt_tm,casemgr_id,casemgr_clntid,status) as (
    select 123456,98410,date'2024-04-19',timestamp'2024-04-19 09:00:00',12345,67891, 'valid'
    union all
    select 194567,98410,date'2024-04-19',timestamp'2024-04-19 11:00:00',12345,67891,'invalid'
    union all
    select 789101,98410,date'2024-04-24',timestamp'2024-04-24 09:00:00',12345,67891,'invalid'
    union all
    select 194587,98410,date'2024-04-25',timestamp'2024-04-25 09:00:00',12345,67891,'invalid'
    union all
    select 234561,98410,date'2024-04-26',timestamp'2024-04-26 09:00:00',12345,67891,'invalid'
    union all
    select 456789,91956,date'2024-04-19',timestamp'2024-04-19 08:00:00',99012,87567,'valid'
    union all
    select 998415,91956,date'2024-12-20',timestamp'2024-12-20 07:00:00',99012,87567,'valid'
union all
  select 01786,18745,date'2023-11-16',timestamp'2023-11-16 07:00:00',12434,87438,'valid'
union all
  select 01786,18745,date'2023-11-21',timestamp'2023-11-21 07:00:00',12434,87438,'invalid'
union all
  select 01786,18745,date'2023-12-01',timestamp'2023-12-01 07:00:00',12434,87438,'invalid'
union all
  select 01786,18745,date'2023-12-04',timestamp'2023-12-04 07:00:00',12434,87438,'invalid'
union all
  select 01786,18745,date'2024-02-02',timestamp'2024-02-02 07:00:00',12434,87438,'invalid'
union all
  select 01786,18745,date'2024-07-05',timestamp'2024-07-05 07:00:00',12434,87438,'valid'
union all
  select 01786,18745,date'2024-07-21',timestamp'2024-07-21 07:00:00',12434,87438,'invalid'
union all
  select 01786,18745,date'2024-07-23',timestamp'2024-07-23 07:00:00',12434,87438,'invalid'

    );

解决方案 #

实现思路 #

要实现动态的180天窗口标记，需使用递归CTE逐个确认有效记录：

1. 先对每组数据按dt_tm排序，生成行号
2. 递归起始点为每组的第一条记录（标记为valid）
3. 后续递归中，判断当前记录是否与最近的valid记录间隔超过180天：
   • 超过则标记为valid，更新当前窗口起点
   • 未超过则标记为invalid

完整SQL代码 #

WITH sorted_data AS (
    SELECT 
        id1, med_id, dt, dt_tm, casemgr_id, casemgr_clntid,
        ROW_NUMBER() OVER(PARTITION BY med_id, casemgr_clntid ORDER BY dt_tm) AS rn
    FROM x
),
recursive_valid AS (
    -- 初始递归：每组第一条记录标记为valid
    SELECT 
        id1, med_id, dt, dt_tm, casemgr_id, casemgr_clntid,
        'valid' AS status,
        dt_tm AS window_start,
        rn
    FROM sorted_data
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归处理后续记录
    SELECT 
        sd.id1, sd.med_id, sd.dt, sd.dt_tm, sd.casemgr_id, sd.casemgr_clntid,
        CASE 
            WHEN sd.dt_tm > rv.window_start + INTERVAL '180 days' THEN 'valid'
            ELSE 'invalid'
        END AS status,
        CASE 
            WHEN sd.dt_tm > rv.window_start + INTERVAL '180 days' THEN sd.dt_tm
            ELSE rv.window_start
        END AS window_start,
        sd.rn
    FROM sorted_data sd
    JOIN recursive_valid rv 
        ON sd.med_id = rv.med_id 
        AND sd.casemgr_clntid = rv.casemgr_clntid 
        AND sd.rn = rv.rn + 1
)
SELECT 
    id1, med_id, dt, dt_tm, casemgr_id, casemgr_clntid, status
FROM recursive_valid
ORDER BY med_id, casemgr_clntid, dt_tm;

代码说明 #

1. sorted_data CTE：对每组数据按dt_tm排序，生成行号rn，方便递归按顺序处理每条记录
2. recursive_valid CTE：
   • 初始部分选取每组第一条记录，标记为valid，并将其dt_tm设为窗口起点window_start
   • 递归部分通过行号关联上一条记录，判断当前记录是否超出上一个有效窗口的180天范围：
     • 超出则标记为valid，并更新窗口起点为当前记录的dt_tm
     • 未超出则标记为invalid，保持原窗口起点
3. 最终查询输出所有记录，按分组和时间排序

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