Блог

Мои данные структурированы следующим образом —

1.Для каждого идентификатора месяц обозначает отчетный месяц, созданная подписка-это первоначальная дата покупки подписки, статус = был ли клиент активен или нет, срок действия-месяцы жизни ( он сбрасывается до 1 при возвращении клиента).

 ID  Month       Sub_created status  tenure
100 2017-02-01  2017-02-01  active  1
100 2017-03-01              active  2
100 2017-04-01              active  3
100 2017-05-01              churned 3
100 2021-02-01  2021-02-01  active  1
100 2021-03-01            active    2
100 2021-04-01            active    3
100 2021-05-01            active    4
100 2021-06-01            active    5
100 2021-07-01            active    6
 

Я хочу, чтобы для всех строк была создана подписка, пока у нее не появится новая дата подписки. Результат, который я пытаюсь получить, приведен ниже —

 ID  Month       Sub_created status  tenure
100 2017-02-01  2017-02-01  active  1
100 2017-03-01  2017-02-01  active  2
100 2017-04-01  2017-02-01  active  3
100 2017-05-01  2017-02-01  churned 3
100 2021-02-01  2021-02-01  active  1
100 2021-03-01  2021-02-01  active  2
100 2021-04-01  2021-02-01  active  3
100 2021-05-01  2021-02-01  active  4
100 2021-06-01  2021-02-01  active  5
100 2021-07-01  2021-02-01  active  6
 

Кто-нибудь может предложить код снежинки ? Спасибо

Вы можете использовать функцию окна last_value() примерно так:

 with CTE as (
select 100 as ID, '2017-02-01' as Month, '2017-02-01' as Sub_created, 'active' as status, 1 as tenure union all 
select 100 as ID, '2017-03-01' as Month, null         as Sub_created, 'active' as status, 2 as tenure union all 
select 100 as ID, '2017-04-01' as Month, null         as Sub_created, 'active' as status, 3 as tenure union all 
select 100 as ID, '2017-05-01' as Month, null         as Sub_created, 'churned' as status, 3 as tenure union all 
select 100 as ID, '2021-02-01' as Month, '2021-02-01' as Sub_created, 'active' as status, 1 as tenure union all 
select 100 as ID, '2021-03-01' as Month, null         as Sub_created, 'active' as status, 2 as tenure union all 
select 100 as ID, '2021-04-01' as Month, null         as Sub_created, 'active' as status, 3 as tenure union all 
select 100 as ID, '2021-05-01' as Month, null         as Sub_created, 'active' as status, 4 as tenure union all 
select 100 as ID, '2021-06-01' as Month, null         as Sub_created, 'active' as status, 5 as tenure union all 
select 100 as ID, '2021-07-01' as Month, null         as Sub_created, 'active' as status, 6 as tenure
)
select ID, Month, Sub_created as Sub_created_orig, 
last_value(sub_created ignore nulls) over (partition by id order by month rows between unbounded preceding and current row) as Sub_created_new,
status, tenure
from CTE
order by ID, month;