Как добавить колонку на основании значений предыдущих записей в рамках окна?

Question

Есть такая таблица, где client_id - ид клиента, months - месяцы, когда с клиентом был контракт.

Пока такой запрос:

select client_id, months, LAG(sas) OVER( PARTITION BY client_id ORDER BY months) renew 
from (
    select client_id, months, months_between (
        LEAD (TO_DATE (T0_CHAR(TO_DATE (months, 'yyyymm'), 'dd.mm.yyyy'), 'dd.mm.yyyy')) 
            OVER(PARTITION BY client_id ORDER BY months),
        to_DATE (TO_CHAR(TO_DATE (months, 'yyyymm'), 'dd.mm.yyyy'), 'dd.mm.yvyy')) sas 
    from renew_clients)

Как видно, из таблицы, у клиента был договор с января 2018 г по апрель 2018 г. В конце апреля договор был закрыт. Потом, тот же клиент заключил контракт в ноябре 2018 г., то есть через 7 месяцев. В колонке renew признак повторного заключения контракта, если поле не равно 1, то считается что клиент повторно заключил контракт.

Значение renew нашел как разницу между следующим и текущим значением поля months:

months_between(
    lead(to_date(to_char(to_date(months,'yyyymm'),'dd.mm.yyyy'),'dd.mm.yyyy')) 
        over (partition by client_id order by months), 
    to_date(to_char(to_date(months,'yyyymm'),'dd.mm.yyyy'),'dd.mm.yyyy'))

Задача: если клиент повторно заключил контракт, то этот признак должен быть во всех последующих запписях данного клиента.

Пример желаемого результата:

Прошу подсказать, намекнуть - какими методами, функциями можно реализовать эту задачу?

Тестовые данные:

    Create table renew_clients(client_id number, months varchar2(20)); 
    Insert into renew_clients(client_id,months) values(15154156, '201801');
    Insert into renew_clients(client_id,months) values(15154156, '201802');
    Insert into renew_clients(client_id,months) values(15154156, '201803');
    Insert into renew_clients(client_id,months) values(15154156, '201804');
    Insert into renew_clients(client_id,months) values(15154156, '201811');
    Insert into renew_clients(client_id,months) values(15154156, '201812');
    Insert into renew_clients(client_id,months) values(15154156, '201901');
    Insert into renew_clients(client_id,months) values(15154156, '201902');
    Insert into renew_clients(client_id,months) values(15154156, '201903');
    Insert into renew_clients(client_id,months) values(15154156, '201904');
    Insert into renew_clients(client_id,months) values(19542330, '201808');
    Insert into renew_clients(client_id,months) values(19542330, '201809');
    Insert into renew_clients(client_id,months) values(19542330, '201902');
    Insert into renew_clients(client_id,months) values(19542330, '201903');
    Insert into renew_clients(client_id,months) values(19542330, '201904');

Answer 1

Попробуйте так:

select d.*, max (renew) over (partition by client_id, grp) flag
from (
    select d.*,  
        sum (case when renew = 1 then 0 else 1 end) 
            over (partition by client_id order by months) grp 
    from ( 
        select d.*, 
            months_between (to_date (months,'yyyymm'), to_date (lag (months) 
                over (partition by client_id order by months), 'yyyymm')) renew  
        from renew_clients d) d) d
order by client_id, months

Результат:

 CLIENT_ID MONTHS      RENEW        GRP       FLAG
---------- ------ ---------- ---------- ----------
  15154156 201801       null          1          1
  15154156 201802          1          1          1
  15154156 201803          1          1          1
  15154156 201804          1          1          1
  15154156 201811          7          2          7
  15154156 201812          1          2          7
  15154156 201901          1          2          7
  15154156 201902          1          2          7
  15154156 201903          1          2          7
  15154156 201904          1          2          7
  19542330 201808       null          1          1
  19542330 201809          1          1          1
  19542330 201902          5          2          5
  19542330 201903          1          2          5
  19542330 201904          1          2          5

БЛОГ НА HUSL

Как добавить колонку на основании значений предыдущих записей в рамках окна?

Ответы (1 шт):