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

#sql #snowflake-cloud-data-platform

Вопрос:

Я написал следующий запрос. Соединения f7 и f8 существуют, потому что иногда доход за предыдущий квартал/год равен НУЛЮ, однако только за этот день. Если доход за 15 дней до этого был положительным, то мы знаем, что это все еще была активная учетная запись, и НУЛЕВОЕ значение было вызвано временным разрывом контракта.

В любом случае, я пытаюсь обновить это так, чтобы вместо 15 дней до предыдущего квартала и года за каждый день я получал последнюю фактическую величину дохода до даты предыдущего квартала/года. Я не уверен, возможно ли это, потому что регистрация будет проходить в разные даты для каждой учетной записи. Так что, возможно, необходим другой подход. Любая помощь будет признательна.

Дайте мне знать, если я достаточно объяснил это.

 with  arr_base as (select * from arr_base_table opp)  ,cte_accounts as (select distinct account_id,  account_name  ,account_owner_name  ,account_region_c  ,account_theater_c  ,owner_theater_c  ,customer_first_purchase_date  ,cohort_date  from arr_base)  ,cte_account_product_info as (select account_id  ,account_name  ,activity_date  ,line_item_count  ,has_casb_count  ,has_casb_api_count  ,has_casb_inline_count  ,has_swg_count  ,has_ng_swg_count  ,has_swg_all_count  ,has_npa_count  ,has_iaas_count  ,has_dlp_count  ,has_dlp_adv_count  ,has_dlp_std_count  ,has_firewall_count  ,has_cspm_count  ,has_email_count  ,has_rbi_count  ,has_support_count  ,npa_user_count  ,is_casb_customer  ,is_swg_customer  ,is_npa_customer  ,is_firewall_customer  ,number_of_products  ,customer_has_two_or_more_products  from arr_base)  ,cte_dates as (select distinct activity_date from arr_base)  ,cte_arr as (select account_id  ,account_name  ,activity_date  ,arr  ,casb_api_arr  ,casb_inline_arr  ,casb_combined_arr  ,swg_arr  ,ng_swg_packages_arr  ,swg_combined_arr  ,cspm_arr  ,firewall_arr  ,iaas_storage_scan_arr  ,npa_arr  ,email_arr  ,rbi_arr  ,dlp_arr  ,dlp_std_arr  ,dlp_adv_arr  ,support_arr   from arr_base)   -- cartesian product  select  dim.activity_date  ,dateadd(year,-1,dim.activity_date) as prev_year_date  ,add_months(dim.activity_date, -3) as prev_quar_date  ,dim.account_id  ,dim.account_name  ,dim.account_owner_name  ,dim.account_region_c  ,dim.account_theater_c  ,dim.owner_theater_c  ,dim.customer_first_purchase_date  ,dim.cohort_date  ,f4.line_item_count  ,f5.line_item_count as line_item_count_prev_year  ,f6.line_item_count as line_item_count_prev_quarter  ,f1.arr as arr_current_year  ,f2.arr as arr_prev_year  ,f3.arr as arr_prev_quarter  ,f7.arr as arr_prev_year_plus15  ,f8.arr as arr_prev_quarter_plus15  from  (  select  a.*  ,d.activity_date  from cte_accounts a cross join cte_dates d  ) as dim  left outer join cte_arr f1 on dim.account_id = f1.account_id and dim.activity_date = f1.activity_date  left outer join cte_arr f2 on dim.account_id = f2.account_id and (dateadd(year,-1,dim.activity_date) = f2.activity_date)  left outer join cte_arr f3 on dim.account_id = f3.account_id and (add_months(dim.activity_date, -3) = f3.activity_date)  left outer join cte_account_product_info f4 on dim.account_id = f4.account_id and dim.activity_date = f4.activity_date  left outer join cte_account_product_info f5 on dim.account_id = f5.account_id and (dateadd(year,-1,dim.activity_date) = f5.activity_date)  left outer join cte_account_product_info f6 on dim.account_id = f6.account_id and (add_months(dim.activity_date, -3) = f6.activity_date)  left outer join cte_arr f7 on dim.account_id = f7.account_id and (dateadd(day,15,(dateadd(year,-1,dim.activity_date))) = f7.activity_date)  left outer join cte_arr f8 on dim.account_id = f8.account_id and (dateadd(day,15,(add_months(dim.activity_date, -3))) = f8.activity_date)  order by  dim.activity_date  ,dim.account_id  

Добавление текущих результатов и желаемых результатов. Только включение соответствующих столбцов в примеры данных. Для учетной записи 2 значение arr_prev_year равно НУЛЮ, поскольку для этой учетной записи в январе 2020 года не было получено дохода. Значение arr_prev_year_plus15 также равно НУЛЮ, поскольку доход не был получен за весь январь.

В желаемых результатах, до 31 января 2020 года, на счете 2 в последний раз доход был получен 31 декабря 2019 года. Таким образом, эта дата и соответствующий доход возвращаются в столбцах prev_year_most_recent_date и arr_prev_year_most_recent.

Текущие Результаты

Activity_date Prev_year_date Prev_quar_date prev_year_plus15_date prev_quar_plus15_date идентификатор учетной записи arr_current_year arr_prev_year arr_prev_quarter arr_prev_year_plus15 arr_prev_quarter_plus15
31 января 2021 года 31 января 2020 года 31 октября 2020 года 16 января 2020 года 16 октября 2020 года 1 100 90 95 90 95
31 января 2021 года 31 января 2020 года 31 октября 2020 года 16 января 2020 года 16 октября 2020 года 2 100 NULL 80 NULL 80

Желаемые результаты:

Activity_date Prev_year_date Prev_quar_date prev_year_most_recent_активный_дата prev_quarter_most_recent_активный_дат account_id arr_current_year arr_prev_year arr_prev_quarter arr_prev_year_most_recent arr_prev_quarter_most_recent
31 января 2021 года 31 января 2020 года 31 октября 2020 года 30 января 2020 года 30 октября 2020 года 1 100 90 95 90 95
31 января 2021 года 31 января 2020 года 31 октября 2020 года 31 декабря 2019 года 30 октября 2020 года 2 100 NULL 80 75 80

Комментарии:

1. Привет, Майкл, в таких случаях, как этот образец данных и ожидаемые результаты, вы получите гораздо лучшие ответы. Трудно сказать, что вам нужно, просто по запросу, который этого не делает. Если то, что вам нужно, сводится к получению предыдущего ненулевого значения для данного атрибута, вы можете создать нулевой индикатор в подзапросе. Затем выполните секционирование LAG() для вашего идентификатора учетной записи, null_indicator. Это просто выстрел в темноте, хотя, потому что я не совсем уверен, чего вы пытаетесь достичь.

2. Справедливо. Позвольте мне обновить данные с образцами и желаемыми результатами

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

4. Да, вы правы. В будущем я постараюсь максимально упростить свои примеры, сохраняя при этом основную мысль. Спасибо за отзыв.

Ответ №1:

Поэтому я бы сначала переписал ваш текущий SQL следующим образом.

Главное-выберите нужные столбцы и избегайте * , не используйте функции в предложениях joins/where.

 with arr_base as (  select   account_id  ,account_name  ,account_owner_name  ,account_region_c  ,account_theater_c  ,owner_theater_c  ,customer_first_purchase_date  ,cohort_date    ,activity_date  ,line_item_count  ,has_casb_count  ,has_casb_api_count  ,has_casb_inline_count  ,has_swg_count  ,has_ng_swg_count  ,has_swg_all_count  ,has_npa_count  ,has_iaas_count  ,has_dlp_count  ,has_dlp_adv_count  ,has_dlp_std_count  ,has_firewall_count  ,has_cspm_count  ,has_email_count  ,has_rbi_count  ,has_support_count  ,npa_user_count  ,is_casb_customer  ,is_swg_customer  ,is_npa_customer  ,is_firewall_customer  ,number_of_products  ,customer_has_two_or_more_products    ,arr  ,casb_api_arr  ,casb_inline_arr  ,casb_combined_arr  ,swg_arr  ,ng_swg_packages_arr  ,swg_combined_arr  ,cspm_arr  ,firewall_arr  ,iaas_storage_scan_arr  ,npa_arr  ,email_arr  ,rbi_arr  ,dlp_arr  ,dlp_std_arr  ,dlp_adv_arr  ,support_arr    from arr_base_table ), cte_accounts as (  select distinct   account_id  ,account_name  ,account_owner_name  ,account_region_c  ,account_theater_c  ,owner_theater_c  ,customer_first_purchase_date  ,cohort_date  from arr_base ), cte_account_product_info as (  select   account_id  ,account_name  ,activity_date  ,line_item_count  ,has_casb_count  ,has_casb_api_count  ,has_casb_inline_count  ,has_swg_count  ,has_ng_swg_count  ,has_swg_all_count  ,has_npa_count  ,has_iaas_count  ,has_dlp_count  ,has_dlp_adv_count  ,has_dlp_std_count  ,has_firewall_count  ,has_cspm_count  ,has_email_count  ,has_rbi_count  ,has_support_count  ,npa_user_count  ,is_casb_customer  ,is_swg_customer  ,is_npa_customer  ,is_firewall_customer  ,number_of_products  ,customer_has_two_or_more_products  from arr_base ), cte_dates as (  select distinct   activity_date  from arr_base ), cte_arr as (  select   account_id  ,account_name  ,activity_date  ,arr  ,casb_api_arr  ,casb_inline_arr  ,casb_combined_arr  ,swg_arr  ,ng_swg_packages_arr  ,swg_combined_arr  ,cspm_arr  ,firewall_arr  ,iaas_storage_scan_arr  ,npa_arr  ,email_arr  ,rbi_arr  ,dlp_arr  ,dlp_std_arr  ,dlp_adv_arr  ,support_arr  from arr_base ), dim_data AS (  select  a.account_id  ,a.account_name  ,a.account_owner_name  ,a.account_region_c  ,a.account_theater_c  ,a.owner_theater_c  ,a.customer_first_purchase_date  ,a.cohort_date  ,d.activity_date  ,dateadd(year, -1, d.activity_date) as prev_year_date  ,dateadd(month, -3, d.activity_date) as prev_quar_date  ,dateadd(day, 15, prev_year_date) as prev_year_plus15d_date  ,dateadd(day, 15, prev_quar_date) as prev_quar_plus15d_date  from cte_accounts a   cross join cte_dates d ) select  dim.activity_date  ,dim.prev_year_date  ,dim.prev_quar_date  ,dim.account_id  ,dim.account_name  ,dim.account_owner_name  ,dim.account_region_c  ,dim.account_theater_c  ,dim.owner_theater_c  ,dim.customer_first_purchase_date  ,dim.cohort_date  ,f4.line_item_count  ,f5.line_item_count as line_item_count_prev_year  ,f6.line_item_count as line_item_count_prev_quarter  ,f1.arr as arr_current_year  ,f2.arr as arr_prev_year  ,f3.arr as arr_prev_quarter  ,f7.arr as arr_prev_year_plus15  ,f8.arr as arr_prev_quarter_plus15 from dim_data as dim left outer join cte_arr f1   on dim.account_id = f1.account_id and dim.activity_date = f1.activity_date left outer join cte_arr f2   on dim.account_id = f2.account_id and dim.prev_year_date = f2.activity_date left outer join cte_arr f3   on dim.account_id = f3.account_id and dim.prev_quar_date = f3.activity_date left outer join cte_account_product_info f4   on dim.account_id = f4.account_id and dim.activity_date = f4.activity_date left outer join cte_account_product_info f5   on dim.account_id = f5.account_id and dim.prev_year_date = f5.activity_date left outer join cte_account_product_info f6   on dim.account_id = f6.account_id and dim.prev_quar_date = f6.activity_date left outer join cte_arr f7   on dim.account_id = f7.account_id and dim.prev_year_plus15d_date = f7.activity_date left outer join cte_arr f8   on dim.account_id = f8.account_id and dim.prev_quar_plus15d_date = f8.activity_date order by dim.activity_date, dim.account_id  

после извлечения всех облаков в select неиспользуемое можно удалить, предоставив

 with arr_base as (  select   account_id  ,account_name  ,account_owner_name  ,account_region_c  ,account_theater_c  ,owner_theater_c  ,customer_first_purchase_date  ,cohort_date    ,activity_date  ,line_item_count    ,arr   from arr_base_table ), cte_accounts as (  select distinct   account_id  ,account_name  ,account_owner_name  ,account_region_c  ,account_theater_c  ,owner_theater_c  ,customer_first_purchase_date  ,cohort_date  from arr_base ), cte_account_product_info as (  select   account_id  ,activity_date  ,line_item_count  from arr_base ), cte_dates as (  select distinct   activity_date  from arr_base ), cte_arr as (  select   account_id  ,activity_date  ,arr  from arr_base ), dim_data AS (  select  a.account_id  ,a.account_name  ,a.account_owner_name  ,a.account_region_c  ,a.account_theater_c  ,a.owner_theater_c  ,a.customer_first_purchase_date  ,a.cohort_date  ,d.activity_date  ,dateadd(year, -1, d.activity_date) as prev_year_date  ,dateadd(month, -3, d.activity_date) as prev_quar_date  ,dateadd(day, 15, prev_year_date) as prev_year_plus15d_date  ,dateadd(day, 15, prev_quar_date) as prev_quar_plus15d_date  from cte_accounts a   cross join cte_dates d ) select  dim.activity_date  ,dim.prev_year_date  ,dim.prev_quar_date  ,dim.account_id  ,dim.account_name  ,dim.account_owner_name  ,dim.account_region_c  ,dim.account_theater_c  ,dim.owner_theater_c  ,dim.customer_first_purchase_date  ,dim.cohort_date  ,f4.line_item_count  ,f5.line_item_count as line_item_count_prev_year  ,f6.line_item_count as line_item_count_prev_quarter  ,f1.arr as arr_current_year  ,f2.arr as arr_prev_year  ,f3.arr as arr_prev_quarter  ,f7.arr as arr_prev_year_plus15  ,f8.arr as arr_prev_quarter_plus15 from dim_data as dim left outer join cte_arr f1   on dim.account_id = f1.account_id and dim.activity_date = f1.activity_date left outer join cte_arr f2   on dim.account_id = f2.account_id and dim.prev_year_date = f2.activity_date left outer join cte_arr f3   on dim.account_id = f3.account_id and dim.prev_quar_date = f3.activity_date left outer join cte_account_product_info f4   on dim.account_id = f4.account_id and dim.activity_date = f4.activity_date left outer join cte_account_product_info f5   on dim.account_id = f5.account_id and dim.prev_year_date = f5.activity_date left outer join cte_account_product_info f6   on dim.account_id = f6.account_id and dim.prev_quar_date = f6.activity_date left outer join cte_arr f7   on dim.account_id = f7.account_id and dim.prev_year_plus15d_date = f7.activity_date left outer join cte_arr f8   on dim.account_id = f8.account_id and dim.prev_quar_plus15d_date = f8.activity_date order by dim.activity_date, dim.account_id  

Но на самом деле то, что вам нужно, похоже,:

 with arr_base as (  select   account_id  ,account_name  ,account_owner_name  ,account_region_c  ,account_theater_c  ,owner_theater_c  ,customer_first_purchase_date  ,cohort_date    ,activity_date  ,line_item_count    ,arr   from arr_base_table ), cte_accounts as (  select distinct   account_id  ,account_name  ,account_owner_name  ,account_region_c  ,account_theater_c  ,owner_theater_c  ,customer_first_purchase_date  ,cohort_date  from arr_base ), cte_account_product_info as (  select   account_id  ,activity_date  ,line_item_count  from arr_base ), cte_dates as (  select distinct   activity_date  from arr_base ), cte_arr as (  select   account_id  ,activity_date  ,arr  from arr_base ), cte_make_sure_only_one_arr_per_day AS (  select   account_id  ,activity_date  ,dateadd(year, -1, activity_date) as prior_year_date  ,dateadd(month, -3, activity_date) as prior_quater_date   ,max(arr) as arr  from cte_arr  group by 1,2 ), cte_prior_year_arrs AS (  SELECT   a.account_id  a.activity_date  b.activity_date as prior_year_activity_date  b.arr as prior_year_arr  FROM cte_make_sure_only_one_arr_per_day AS a  JOIN cte_make_sure_only_one_arr_per_day AS b   ON a.account_id = b.account_id AND b.activity_date lt; a.prior_year_date  QUALIFY ROW_NUMBER() OVER (PARTITION BY a.account_id, a.activity_date ORDER BY b.activity_date DESC) = 1 ), cte_prior_quarter_arrs AS (  SELECT   a.account_id  a.activity_date  b.activity_date as prior_quarter_activity_date  b.arr as prior_quarter_arr  FROM cte_make_sure_only_one_arr_per_day AS a  JOIN cte_make_sure_only_one_arr_per_day AS b   ON a.account_id = b.account_id AND b.activity_date lt; a.prior_quater_date  QUALIFY ROW_NUMBER() OVER (PARTITION BY a.account_id, a.activity_date ORDER BY b.activity_date DESC) = 1 ), dim_data AS (  select  a.account_id  ,a.account_name  ,a.account_owner_name  ,a.account_region_c  ,a.account_theater_c  ,a.owner_theater_c  ,a.customer_first_purchase_date  ,a.cohort_date  ,d.activity_date  ,dateadd(year, -1, d.activity_date) as prev_year_date  ,dateadd(month, -3, d.activity_date) as prev_quar_date  from cte_accounts a   cross join cte_dates d ) select  dim.activity_date  ,f7.prior_year_activity_date as prev_year_date  ,f8.prior_quarter_activity_date as prev_quar_date  ,dim.account_id  ,dim.account_name  ,dim.account_owner_name  ,dim.account_region_c  ,dim.account_theater_c  ,dim.owner_theater_c  ,dim.customer_first_purchase_date  ,dim.cohort_date  ,f4.line_item_count  ,f5.line_item_count as line_item_count_prev_year  ,f6.line_item_count as line_item_count_prev_quarter  ,f1.arr as arr_current_year  ,f2.arr as arr_prev_year  ,f3.arr as arr_prev_quarter  ,f7.prior_year_arr as arr_prev_year_plus15  ,f8.prior_quarter_arr as arr_prev_quarter_plus15 from dim_data as dim left outer join cte_arr f1   on dim.account_id = f1.account_id and dim.activity_date = f1.activity_date left outer join cte_arr f2   on dim.account_id = f2.account_id and dim.prev_year_date = f2.activity_date left outer join cte_arr f3   on dim.account_id = f3.account_id and dim.prev_quar_date = f3.activity_date left outer join cte_account_product_info f4   on dim.account_id = f4.account_id and dim.activity_date = f4.activity_date left outer join cte_account_product_info f5   on dim.account_id = f5.account_id and dim.prev_year_date = f5.activity_date left outer join cte_account_product_info f6   on dim.account_id = f6.account_id and dim.prev_quar_date = f6.activity_date left outer join cte_prior_year_arrs f7   on dim.account_id = f7.account_id and dim.activity_date = f7.activity_date left outer join cte_prior_quarter_arrs f8   on dim.account_id = f8.account_id and dim.activity_date = f8.activity_date order by dim.activity_date, dim.account_id  

У этого будет «предыдущий» день для каждого текущего дня, в котором есть активность. Но если вам нужен предыдущий день за -1 год или -1 квартал до текущего дня без даты, то cte_make_sure_only_one_arr_per_day его нужно будет заменить на dim_data

Но я думаю, что это показывает, как получить нужные вам данные.

Комментарии:

1. Спасибо за предложения! Мне придется потратить некоторое время, чтобы переварить это и проверить. Я ценю вашу помощь!

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