Аннотирование в столбце при изменении логического значения в другом столбце sql

ProgramBox

Аннотирование в столбце при изменении логического значения в другом столбце sql

Post author:admin
Запись опубликована:9 апреля, 2023
Post category:Вопросы по программированию

#sql #snowflake-cloud-data-platform

Вопрос:

Я пытаюсь аннотировать тип транзакции при изменении логического значения в столбце в таблице

 ID   REVENUE   TERMINATED_PLAN  START_DATE        END_DATE
1      0           FALSE        2020-06-10       2021-06-09
1      5000        FALSE        2020-06-10       2021-06-09
1      7500        FALSE        2020-06-10       2021-06-09
1      15000       FALSE        2020-06-10       2021-06-09
1      10000       FALSE        2020-06-10       2021-06-09
1       0          TRUE         2020-06-10       2020-09-09   
1      5000        FALSE        2020-10-10       2021-10-09

Это макет данных, которые у меня есть, и мне нужно комментировать всякий раз, когда происходит изменение дохода. Допустим, доход увеличился с 5000 до 7500, что означает, что клиент обновил план, и аннотация должна быть «расширение«, и если они понизят рейтинг, аннотация должна быть «сокращение«. Когда доход достигает 0, это означает, что клиент сбит, мы помечаем это как «отток«, и когда это происходит, завершенный план становится истинным, а End_date обновляется до дня, когда они сбиты. Иногда клиент может вернуться, и доход увеличивается с 0 до некоторого значения (здесь 5000), и это должно быть «Повторной активацией», и когда это происходит, Terminated_plan становится ложным, а поля start_date и end_date обновляются

Я смог сделать все, кроме части реактивации, и я немного запутался в том, как с этим справиться. Один из способов сделать это, когда логическое значение изменяется с True на False, мы можем аннотировать его как реактивацию, но не уверены, как поместить это в сценарий.

Это запрос, который я использовал для создания поля с именем transaction

 select 
id,
revenue, 
start_date,
end_date,,
TERMINATED_PLAN,
revenue - lag(revenue, 1, 0) over (partition by id order by updated_at asc) as diff_mrr,
case when revenue>0 and TERMINATED_PLAN = FALSE and month(start_date) = month(current_date) then 'New Business' 
     when TERMINATED_PLAN = TRUE and diff_mrr < 0 then 'Churn'
     when diff_mrr <0 then 'Contraction' 
     when diff_mrr >0 then 'Expansion'
     when revenue > 0 and month(start_date) = month(current_date()) then 'Reactivation' 
     end as transaction
from diff_mrr_t
group by 1,2,3,4,5
order by updated_at asc;

P.S: Существует также другое поле с именем updated_at, которое заполняется всякий раз, когда происходит изменение дохода

 ID   REVENUE   TERMINATED_PLAN  START_DATE     END_DATE   DIFF_REV   TRANSACTION
1      0           FALSE        2020-06-10    2021-06-09     0        New Business
1      5000        FALSE        2020-06-10    2021-06-09    5000       Expansion
1      7500        FALSE        2020-06-10    2021-06-09    2500       Expansion
1      15000       FALSE        2020-06-10    2021-06-09    7500       Expansion
1      10000       FALSE        2020-06-10    2021-06-09   -5000       Contraction
1       0          TRUE         2020-06-10    2020-09-09   -10000      Churn
1      5000        FALSE        2020-10-10    2021-10-09    5000       Reactivation(Expected but shows as New Business with the current logic)

Будем признательны за любую помощь. Я использую базу данных snowflake для решения этой проблемы.

1. Почему первая строка показывает, New Business где находится revenue значение 0 ?

2. Именно тогда клиент зарегистрировался

3. Но как revenue>0 and TERMINATED_PLAN = FALSE and month(start_date) = month(current_date) then 'New Business' это передается?

4. Итак, это данные клиента, который зарегистрировался в июне, поэтому вы можете увидеть новый бизнес

5. Хорошо, теперь я понимаю, что вы имеете в виду. Первой записью всегда должен быть новый бизнес, поскольку мы хотим видеть новый бизнес только один раз за весь жизненный цикл клиента, и с этой логикой новый бизнес появляется каждый месяц. Это хороший улов

Ответ №1:

Если я правильно понял, нам также нужно отслеживать предыдущий terminated_plan, а затем проверять его при настройке на Reactivation ,

Не совсем уверен, но не могли бы вы попробовать ниже,

 select 
  id,
  revenue, 
  start_date,
  end_date,
  terminated_plan,
  lag(terminated_plan,1) over (partition by id order by updated_at asc) as prev_plan,
  revenue - lag(revenue, 1, 0) over (partition by id order by updated_at asc) as diff_mrr,
  case when    revenue > 0 
           and terminated_plan = FALSE 
           and month(start_date) = month(current_date()) 
       then 
          'New Business' 
       when    terminated_plan = TRUE 
           and diff_mrr < 0 
       then 
          'Churn'
       when diff_mrr < 0 
       then 
         'Contraction' 
       when diff_mrr > 0 
       then 
         'Expansion'
       when    revenue > 0
           AND prev_plan = TRUE
           AND terminated_plan = FALSE
           and month(start_date) = month(current_date()) 
       then 
         'Reactivation' 
       end as transaction
from diff_mrr_t
group by 1,2,3,4,5
order by updated_at asc;

1. Этот код не выполняется, и при исправлении синтаксических ошибок он выдает неправильные результаты. Но, похоже, это было отмечено как правильный ответ. Возможно, потребуется обновить его с помощью рабочего кода.

2. @CodeMonkey: Спасибо за подсказку. Так это причина для голосования «Против»? Я полагаю, что иногда при ответе у вас может не быть подходящей инфраструктуры, но логика, которую вы выводите, имеет значение, и, что наиболее важно, OP удовлетворен этим.

3. Хотя ваш ответ может быть полезным, он неполный и дает неправильные результаты. Это может соответствовать требованиям OP, но не помогает другим, которые могут наткнуться на вопрос и ответ. Но я удалил понижающий голос, поскольку OP действительно принял его, несмотря на это — потому что вы приложили усилия для ответа.

4. Тот факт, что OP упомянул , что он смог вывести все, кроме Reactivation логики, где я предложил проверить предыдущее значение, для terminated_value которого вы также сделали то же самое ( кроме того, вы удалили проверку даты по сравнению с кодом в исходном вопросе) . Когда вы говорите неправильно, пожалуйста, уточните, какую часть вы имеете в виду?

5. Использование вашего запроса с данными примера приведет к тому, что первая запись будет иметь нулевую транзакцию и «расширение» для последней записи. Последняя запись никогда не достигнет оператора case для повторной активации, потому что она попадет в вашу логику расширения «когда diff_mrr > 0». Первая запись возвращает значение NULL, потому что у вас нет значения по умолчанию в вашем лаге, и вы не проверяете значение NULL в своем операторе case.

Ответ №2:

Попробуйте следующий код. Я предположил, что первая запись представляет новый бизнес, несмотря на доход (доход не обязательно должен быть > 0). Затем я использовал флаг TERMINATED_PLAN, чтобы проверить, активируется ли учетная запись повторно или расширяется.

 WITH CTE AS (
    SELECT 1 AS ID, 0 AS REVENUE, FALSE AS TERMINATED_PLAN, TO_TIMESTAMP('2020-08-10') AS START_DATE, TO_TIMESTAMP('2021-08-09') AS END_DATE, TO_TIMESTAMP('2020-08-10') AS UPDATED_AT
  UNION ALL SELECT 1 AS ID, 5000 AS REVENUE, FALSE AS TERMINATED_PLAN, TO_TIMESTAMP('2020-08-10') AS START_DATE, TO_TIMESTAMP('2021-08-09') AS END_DATE, TO_TIMESTAMP('2020-08-10') AS UPDATED_AT
  UNION ALL SELECT 1 AS ID, 7500 AS REVENUE, FALSE AS TERMINATED_PLAN, TO_TIMESTAMP('2020-08-10') AS START_DATE, TO_TIMESTAMP('2021-08-09') AS END_DATE, TO_TIMESTAMP('2020-08-10') AS UPDATED_AT
  UNION ALL SELECT 1 AS ID, 15000 AS REVENUE, FALSE AS TERMINATED_PLAN, TO_TIMESTAMP('2020-08-10') AS START_DATE, TO_TIMESTAMP('2021-08-09') AS END_DATE, TO_TIMESTAMP('2020-08-10') AS UPDATED_AT
  UNION ALL SELECT 1 AS ID, 10000 AS REVENUE, FALSE AS TERMINATED_PLAN, TO_TIMESTAMP('2020-08-10') AS START_DATE, TO_TIMESTAMP('2021-08-09') AS END_DATE, TO_TIMESTAMP('2020-08-10') AS UPDATED_AT
  UNION ALL SELECT 1 AS ID, 0 AS REVENUE, TRUE AS TERMINATED_PLAN, TO_TIMESTAMP('2020-08-10') AS START_DATE, TO_TIMESTAMP('2020-09-09') AS END_DATE, TO_TIMESTAMP('2020-08-10') AS UPDATED_AT
  UNION ALL SELECT 1 AS ID, 5000 AS REVENUE, FALSE AS TERMINATED_PLAN, TO_TIMESTAMP('2020-10-10') AS START_DATE, TO_TIMESTAMP('2021-10-09') AS END_DATE, TO_TIMESTAMP('2020-08-10') AS UPDATED_AT
  UNION ALL SELECT 1 AS ID, 10000 AS REVENUE, FALSE AS TERMINATED_PLAN, TO_TIMESTAMP('2020-10-11') AS START_DATE, TO_TIMESTAMP('2021-10-10') AS END_DATE, TO_TIMESTAMP('2020-08-10') AS UPDATED_AT
)
select 
ID,
revenue, 
start_date,
end_date,
TERMINATED_PLAN
,revenue - lag(revenue, 1, NULL) over (partition by id order by updated_at asc) as diff_mrr
,CASE WHEN diff_mrr IS NULL THEN 'New Business'
    WHEN TERMINATED_PLAN = TRUE and diff_mrr < 0 then 'Churn'
    WHEN diff_mrr <0 then 'Contraction' 
    WHEN diff_mrr >0 AND lag(TERMINATED_PLAN, 1, NULL) over (partition by id order by updated_at asc) = FALSE then 'Expansion'
    WHEN diff_mrr >0 AND lag(TERMINATED_PLAN, 1, NULL) over (partition by id order by updated_at asc) = TRUE then 'Reactivation'    
END AS Transaction
from CTE
GROUP BY
  ID,
  revenue, 
  start_date,
  end_date,
  TERMINATED_PLAN,
  updated_at
order by updated_at asc;