Следующее действие с использованием аналитических функций

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

У меня есть monhtly снимок со следующими данными:

customer | month      | last_action   | last_action_date
1          01-01-2012   subscription    01-01-2012
1          02-01-2012   subscription    01-01-2012
1          03-01-2012   subscription    01-01-2012
1          04-01-2012   downgrade       04-01-2012
1          05-01-2012   downgrade       04-01-2012
1          06-01-2012   downgrade       04-01-2012
1          07-01-2012   unsubscription  07-01-2012

Как видите, действие известно только в том месяце, когда оно выполнено, в 01-01-2012 мы еще не знаем, что клиент понизил в 04-01-2012, поэтому мы не можем проанализировать его поведение относительно к его месяцу понижения. То же самое для отписки.

Необходимый набор данных следующий:

customer | month      | downgrade_date   | unsubscription_date
1          01-01-2012   04-01-2012         07-01-2012
1          02-01-2012   04-01-2012         07-01-2012
1          03-01-2012   04-01-2012         07-01-2012
1          04-01-2012   12-31-9999         07-01-2012
1          05-01-2012   12-31-9999         07-01-2012
1          06-01-2012   12-31-9999         07-01-2012
1          07-01-2012   12-31-9999         07-01-2012

Я мог бы легко получить дату отписки с аналитической функцией last_value, но не смог найти дату понижения.

Вот мой SQL-запрос:

SELECT month_id, 
       customer_id,
       CASE 
         WHEN LAST_VALUE(last_action) OVER (PARTITION BY customer_id ORDER BY month_id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) = 'unsubscription' THEN LAST_VALUE(last_action_date) OVER (PARTITION BY customer_id ORDER BY month_id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)    
         ELSE TO_DATE('99991231', 'yyyymmdd')
       END unsubscription_date
FROM my_table
;

Любой способ получить "следующую" дату действия, как в "downgrade_date".

Я использую Oracle.

Источник

1 ответ

В оракуле 11 вы можете сделать это с lead() и ignore nulls опция:

select customer, MONTH,
       lead(case when last_action = 'downgrade' then last_action_date end ignore nulls) over
                 (partition by customer order by month desc) as downgrade_date,
       lead(case when last_action = 'unsubscription' then last_action_date end ignore nulls) over
                 (partition by customer order by month desc) as downgrade_date,
from my_table t

Если у вас нет ignore nullsможно сделать что-то похожее с min():

select customer, MONTH,
       min(case when last_action = 'downgrade' then last_action_date end) over
                (partition by customer order by month range between current and unbounded following
                ) as downgrade_date,
       min(case when last_action = 'unsubscription' then last_action_date end) over
                (partition by customer order by month range between current and unbounded following
                ) as unsubscription_date
from my_table t
Источник
Другие вопросы по тегам