Сброс счетчика ROW_NUMBER на основе 3-месячного разрыва в дате

Question

Ищите решение о том, как начать отсчет ROW_NUMBER, если в поле даты (clm_line_srvc_strt_dt) имеется промежуток более 3 месяцев.

В настоящее время используется следующее для подсчета строк:

,ROW_NUMBER() 
OVER(PARTITION BY c.src_sbscrbr_id
, c.src_mbr_sqnc_nbr
, cl.hlth_srvc_cd 
, df.serv_prov_id
ORDER BY c.src_sbscrbr_id
, c.src_mbr_sqnc_nbr
, cl.hlth_srvc_cd 
, df.serv_prov_id
, cl.clm_line_srvc_strt_dt) as rncnt

Например: ID участника 011 имеет три записи с полем clm_line_srvc_strt_dt, которое равно 28Dec2017, 28Apr2018 и 28Jul2018.

В настоящее время количество строк для этих 3 строк составляет 1, 2, 3.

Число строк должно быть сброшено до 1 со строкой 28 апреля 2018 года, поскольку разрыв между 28 декабря 2017 года и 28 апреля 2018 года превышает 3 месяца. Таким образом, это должно быть 1, 1, 2, с третьей строкой, продолжающей отсчитывать, поскольку разрыв между 28 апреля и 28 июля 2018 года не превышает 3 месяцев.

Answer 1

Teradata поддерживает проприетарное расширение для оконных агрегатов RESET WHEN, которое добавляет своего рода динамический раздел:

Row_Number() 
Over(PARTITION BY c.src_sbscrbr_id
                , c.src_mbr_sqnc_nbr
                , cl.hlth_srvc_cd 
                , df.serv_prov_id
     ORDER BY  cl.clm_line_srvc_strt_dt
     -- restart the row number when the previous date is more than 3 months ago
     RESET WHEN Min(clm_line_srvc_strt_dt)
                Over (PARTITION BY c.src_sbscrbr_id
                                 , c.src_mbr_sqnc_nbr
                                 , cl.hlth_srvc_cd 
                                 , df.serv_prov_id
                      ORDER BY cl.clm_line_srvc_strt_dt
                      ROWS BETWEEN 1 Preceding AND 1 Preceding) 
               < Add_Months(cl.clm_line_srvc_strt_dt, -3)) AS rncnt

Судя по сообщению об ошибке в ответе Гордона, ваша версия Teradata пока не поддерживает LAG (должно быть 16.10+). MIN такой же как:

                LAG(clm_line_srvc_strt_dt)
                Over (PARTITION BY c.src_sbscrbr_id
                                 , c.src_mbr_sqnc_nbr
                                 , cl.hlth_srvc_cd 
                                 , df.serv_prov_id
                      ORDER BY cl.clm_line_srvc_strt_dt)  

Кстати, нет необходимости ЗАКАЗАТЬ по тем же столбцам, которые вы уже используете в РАЗДЕЛЕ (в любом случае, внутри раздела это одно и то же значение)

Answer 2

Вы можете следовать подходу Lagged variable в шаге данных. Я использовал пример данных:

data sample;
input Mem_Id clm_line_srvc_strt_dt date9.;
format clm_line_srvc_strt_dt date9.;
datalines;
1011 28Dec2017
1011 28Apr2018
1011 28Jul2018
1011 28Aug2018
2012 15Apr2017
2012 15Jul2017
2012 15Oct2017
;
run;

proc sort data=sample; by Mem_Id clm_line_srvc_strt_dt; run;

data sample1;
 set sample;
  by Mem_id clm_line_srvc_strt_dt;
  Retain RowCount 0;

  diff=intck('MONTH',lag(clm_line_srvc_strt_dt),clm_line_srvc_strt_dt);
  if first.Mem_id then RowCount=1;
  else if diff<=3 then rowcount=rowcount+1;
  drop diff;
run; 

proc print data=sample1; run;

Answer 3

Вы бы сделали это, используя накопленную сумму и lag(), Идея заключается в следующем:

sum(case when cl.clm_line_srvc_strt_dt < prev_clm_line_srvc_strt_dt + interval '3 month'
         then 0 else 1
    end) over (partition by c.src_sbscrbr_id, c.src_mbr_sqnc_nbr, cl.hlth_srvc_cd, df.serv_prov_id
               order by cl.clm_line_srvc_strt_dt
              ) as rncnt
from (select . . .,
             lag(cl.clm_line_srvc_strt_dt) over (partition by c.src_sbscrbr_id, c.src_mbr_sqnc_nbr, cl.hlth_srvc_cd, df.serv_prov_id) as prev_clm_line_srvc_strt_dt
      . . .
     ) . . .