#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. К сожалению, похоже, что это не возвращает желаемых результатов. Я думаю, что мне, возможно, потребуется опубликовать более упрощенную версию этого вопроса, чтобы лучше передать то, чего я пытаюсь достичь