chenwu/MarketAnalysis-ETL
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
MarketAnalysis-ETL/Retail/08 map_to_dtp_dws_table.py
chenwu 832c7bcd69 new file: 01 dm_tf_external_sales.sql 
new file:   02 dm_td_external_keycompatitor.sql
	new file:   03 dm_td_external_brand_market.sql
	new file:   04 dm_td_external_calendar.sql
	new file:   05 dm_td_external_exchangerate.sql
	new file:   07 dm_td_external_packinfo.sql
	new file:   08 dm_td_external_corp.sql
	new file:   09 dm_td_external_geo_type.sql
	new file:   11 DM_TD_EXTERNAL_MARKET_NEW.sql
	new file:   12 dm_td_external_org.sql
	new file:   13 external auth.sql
	new file:   14 dm_tf_external_retail_special_bkp.sql
	new file:   AIA/01 dm_aia_pack_property.sql
	new file:   AIA/02 dm_ext_aia_sales.sql
	new file:   AIA/02 dm_ext_aia_sales_bakup_20230327.sql
	new file:   AIA/03 dm_aia_flag.sql
	new file:   AIA/04 dm_aia_provided_flag.sql
	new file:   AIA/06 DM_TD_EXT_AIA_PACK_PROPERTY.sql
	new file:   AIA/07 DM_TD_EXT_AIA_PACK2MARKET.sql
	new file:   AIA/08 DM_TD_EXT_AIA_MARKET_RATIO.sql
	new file:   AIA/09 DM_TD_EXT_AIA_MARKET_BRAND_RATIO.sql
	new file:   AIA/10 DM_TD_EXT_AIA_MARKET_PACK_MAPPING.sql
	new file:   AIA/11 DM_TD_EXT_AIA_TARGET_INST.sql
	new file:   AIA/11 dm_aia_targethp_flag.sql
	new file:   "AIA/z_03 dm_aia_flag_\345\244\207\344\273\275\347\224\250.sql"
	new file:   CHC/01 dm_chc_pack_property.sql
	new file:   CHC/02 DM_TF_EXT_CHC_SALES.sql
	new file:   CHC/03 DM_TD_EXT_CHC_PACK_PROPERTY.sql
	new file:   CHC/04 DM_TD_EXT_CHC_PACK2MARKET.sql
	new file:   CHC/05 DM_TD_EXT_CHC_MARKET_RATIO.sql
	new file:   CHC/06 DM_TD_EXT_CHC_MARKET_BRAND_RATIO.sql
	new file:   CHC/07 DM_TD_EXT_CHC_MARKET_PACK_MAPPING.sql
	new file:   CHPA/01 dwd_ims_atc_hierarchy.sql
	new file:   CHPA/01 dwd_ims_nfc_hierarchy.sql
	new file:   CHPA/01 dwd_ims_td_manufacturer_corp.sql
	new file:   CHPA/01 dwd_ims_td_pack_property.sql
	new file:   CHPA/01 dwd_update.sql
	new file:   CHPA/01_FB_BLOB_TO_DWD.sql
	new file:   CHPA/02 DWS_IMS_TD_GEO.sql
	new file:   CHPA/02 dws_ims_td_atc_cn.sql
	new file:   CHPA/02 dws_ims_td_corp_cn.sql
	new file:   CHPA/02 dws_ims_td_date.sql
	new file:   CHPA/02 dws_ims_td_manu_cn.sql
	new file:   CHPA/02 dws_ims_td_market.sql
	new file:   CHPA/02 dws_ims_td_market_ta.sql
	new file:   CHPA/02 dws_ims_td_nfc_cn.sql
	new file:   CHPA/02 dws_ims_td_prod_cn.sql
	new file:   CHPA/02 tmp_ims_td_prod_tmp.sql
	new file:   CHPA/02 tmp_ims_tf_fact_sales.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_MARKET_BRAND_RATIO.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_MARKET_PACK_MAPPING.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_MARKET_RATIO.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_PACK2MARKET.sql
	new file:   CHPA/03 DM_TD_EXT_CHPA_PACK_PROPERTY.sql
	new file:   CHPA/03 DM_TF_EXT_CHPA_SALES.sql
	new file:   CHPA/03 dm_ims_td_calendar.sql
	new file:   CHPA/03 dm_ims_td_geo.sql
	new file:   CHPA/03 dm_ims_td_market_property.sql
	new file:   CHPA/03 dm_ims_td_org.sql
	new file:   CHPA/03 dm_ims_td_org_hvh.sql
	new file:   CHPA/03 dm_ims_td_pack_property.sql
	new file:   CHPA/03 dm_ims_tf_sales.sql
	new file:   CHPA/03 dm_td_chpa_market_definition.sql
	new file:   CHPA/03 dm_td_ims_city_mapping.sql
	new file:   EC/03 ec_load_data.sql
	new file:   EC/04 DM_TD_EXT_EC_PACK_PROPERTY.sql
	new file:   EC/05 DM_TF_EXT_EC_SALES.sql
	new file:   EC/06 DM_TD_EXT_EC_PACK2MARKET.sql
	new file:   EC/07 DM_TD_EXT_EC_MARKET_RATIO.sql
	new file:   EC/08 DM_TD_EXT_EC_MARKET_BRAND_RATIO.sql
	new file:   EC/09 DM_TD_EXT_EC_MARKET_PACK_MAPPING.sql
	new file:   EC/1 (ec)blob_to_dwd.sql
	new file:   EC/2 dwd_inc_gnd_ext_ec_nationnal_pack_union_all.py
	new file:   Merged_Data/Merged_Data_Config_table_bkp.sql
	new file:   Merged_Data/Merged_Data_Config_table_bymonth.sql
	new file:   Merged_Data/dm_tf_exteranl_sales_merged_data_dtp_others_bkp.sql
	new file:   Merged_Data/dm_tf_exteranl_sales_merged_data_dtp_others_bymonth_bkp.sql
	new file:   ORG/DM_TD_EXT_AIA_ORG.sql
	new file:   ORG/DM_TD_EXT_CHC_ORG.sql
	new file:   ORG/DM_TD_EXT_CHPA_ORG.sql
	new file:   ORG/DM_TD_EXT_COUNTY_ORG.sql
	new file:   ORG/DM_TD_EXT_EC_ORG.sql
	new file:   ORG/DM_TD_EXT_RETAIL_ORG.sql
	new file:   ORG/DM_TD_EXT_THC_ORG.sql
	new file:   ORG/DM_TD_EXT_XIEHE_ORG.sql
	new file:   OTHERS/01 dm_td_report_url.sql
	new file:   OTHERS/02 dws_ext_email_warning.sql
	new file:   OTHERS/external_triggered_email.py
	new file:   Retail/01 load_tmp_data.py
	new file:   Retail/02 split_brand_data.py
	new file:   Retail/03 split_pack_data.py
	new file:   Retail/04 map_to_dws_table.py
	new file:   Retail/05 load_dtp_temp_data.py
	new file:   Retail/06 split_dtp_brand_data.py
	new file:   Retail/07 split_dtp_pack_data.py
	new file:   Retail/08 map_to_dtp_dws_table.py
	new file:   Retail/09 dwd_inc_gnd_ext_retail_nataional.py
	new file:   Retail/10 map_to_retail_dm_table.py
	new file:   Retail/11 map_to_overview_dm_table.py
	new file:   Retail/12 dws_tf_external_retail_dtp_special.sql
	new file:   Retail/13 DM_TF_EXT_RETAIL_SALES.sql
	new file:   Retail/14 DM_TF_EXT_RETAIL_DTP_SALES.sql
	new file:   Retail/15 DM_TD_EXT_RETAIL_PACK_PROPERTY.sql
	new file:   Retail/16 DM_TD_EXT_RETAIL_DTP_PACK_PROPERTY.sql
	new file:   Retail/17 DM_TD_EXT_DTP_PACK2MARKET.sql
	new file:   Retail/17 DM_TD_EXT_RETAIL_PACK2MARKET.sql
	new file:   Retail/18 DM_TD_EXT_DTP_MARKET_RATIO.sql
	new file:   Retail/18 DM_TD_EXT_RETAIL_MARKET_RATIO.sql
	new file:   Retail/19 DM_TD_EXT_DTP_MARKET_BRAND_RATIO.sql
	new file:   Retail/19 DM_TD_EXT_RETAIL_MARKET_BRAND_RATIO.sql
	new file:   Retail/20 DM_TD_EXT_DTP_MARKET_PACK_MAPPING.sql
	new file:   Retail/20 DM_TD_EXT_RETAIL_MARKET_PACK_MAPPING.sql
	new file:   "Retail/z1 dwd_inc_gnd_ext_retail_nataional_\344\275\234\345\272\237.py"
	new file:   "Retail/z2 retail_load_data_\344\275\234\345\272\237.sql"
	new file:   "Retail/z3 retail_overview_data_\344\275\234\345\272\237.sql"
	new file:   THC/01 dm_thc_pack_property.sql
	new file:   THC/02 dm_ext_thc_sales.sql
	new file:   THC/02 dm_ext_thc_sales_bakup_20260327.sql
	new file:   THC/03 DM_TF_EXT_THC_MARKET_SALES_CHT.sql
	new file:   THC/04 dm_tf_external_sales_thc.sql
	new file:   THC/05 DM_TD_EXT_THC_PACK_PROPERTY.sql
	new file:   THC/06 DM_TD_EXT_THC_PACK2MARKET.sql
	new file:   THC/07 DM_TD_EXT_THC_MARKET_RATIO.sql
	new file:   THC/08 DM_TD_EXT_THC_MARKET_BRAND_RATIO.sql
	new file:   THC/09 DM_TD_EXT_THC_MARKET_PACK_MAPPING.sql
	new file:   UNIONALL/DM_TD_EXT_UNIONALL_MARKET_PACK_MAPPING.sql
	new file:   UNIONALL/DM_TD_EXT_UNIONALL_PACKINFO.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_MARKET_SALES.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_MARKET_SALES_v1.0.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_SALES.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_SALES_MAPPING.sql
	new file:   UNIONALL/DM_TF_EXT_UNIONALL_SALES_MAPPING_NIAD.sql
	new file:   XIEHE/01 xiehe_blob_to_dwd.py
	new file:   XIEHE/02 dm_xiehe_pack_property.sql
	new file:   XIEHE/03 dm_ext_xiehe_geo.sql
	new file:   XIEHE/04 dm_ext_xiehe_sales.sql
	new file:   XIEHE/05 dm_td_xiehe_core_dept.sql
	new file:   XIEHE/06 DM_TF_EXT_XIEHE_SALES.sql
	new file:   XIEHE/07 DM_TD_EXT_XIEHE_PACK_PROPERTY.sql
	new file:   XIEHE/08 DM_TD_EXT_XIEHE_PACK2MARKET.sql
	new file:   XIEHE/09 DM_TD_EXT_XIEHE_MARKET_RATIO.sql
	new file:   XIEHE/10 DM_TD_EXT_XIEHE_MARKET_BRAND_RATIO.sql
	new file:   XIEHE/11 DM_TD_EXT_XIEHE_MARKET_PACK_MAPPING.sql
	new file:   XIEHE/bkp_01 xiehe_blob2dwd.py
	new file:   XIEHE/bkp_02 dm_ext_xiehe_sales.sql
	new file:   XIEHE/bkp_03 dm_ext_xiehe_pack_property.sql
	new file:   county/01 tmp_ims_county_fact_sales_sum.sql
	new file:   county/02 tmp_imscounty_Result.sql
	new file:   county/03 dm_ims_td_county_geo.sql
	new file:   county/04 dws_ext_county_tf_sales.sql
	new file:   county/05 dm_ext_county_td_pack_property.sql
	new file:   county/06 dm_td_county_pack_region.sql
	new file:   county/07 dm_ext_county_tf_sales_region.sql
	new file:   county/08 DM_TD_EXT_COUNTY_PACK_PROPERTY.sql
	new file:   county/09 DM_TF_EXT_COUNTY_SALES.sql
	new file:   county/10 DM_TD_EXT_COUNTY_PACK2MARKET.sql
	new file:   county/11 DM_TD_EXT_COUNTY_MARKET_RATIO.sql
	new file:   county/12 DM_TD_EXT_COUNTY_MARKET_BRAND_RATIO.sql
	new file:   county/13 DM_TD_EXT_COUNTY_MARKET_PACK_MAPPING.sql
	new file:   for_AIA_Dashboard/01 dm_td_aia_inst_mkt.sql
	new file:   for_AIA_Dashboard/02 dm_td_aia_auth_sales.sql
	new file:   for_AIA_Dashboard/03 dm_td_aia_original_col.sql
	new file:   for_AIA_Dashboard/04 dm_td_aia_nosales_inst.sql
	new file:   for_AIA_Dashboard/05 dm_td_aia_is_eagle_flag.sql
	new file:   for_AIA_Dashboard/06 dm_td_aia_rank.sql
	new file:   for_AIA_Dashboard/07 dm_ext_aia_data_remove_flag.sql
	new file:   for_AIA_Dashboard/07 dm_td_aia_remove_special_ins_bkp.py
	new file:   for_AIA_Dashboard/08 dm_ext_aia_data_quality_flag.sql
	new file:   z 01 dm_tf_external_sales.sql
	new file:   "z 10 dm_td_external_market_pack_mapping_\344\275\234\345\272\237.sql"
	new file:   "z 11 dm_td_external_market_\344\275\234\345\272\237.sql"
	new file:   "\344\270\212\347\272\277\350\204\232\346\234\254.sql"
	new file:   "\346\225\260\346\215\256\351\252\214\350\257\201.sql"
2026-04-27 15:48:38 +08:00

510 lines
19 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# Databricks notebook source
# MAGIC %sql
# MAGIC -- CREATE or REPLACE TABLE tmp.tmp_retail_dtp_final_sales (
# MAGIC -- year STRING,
# MAGIC -- yq STRING,
# MAGIC -- yyyymm STRING,
# MAGIC -- iqvia_pack_code STRING,
# MAGIC -- geo_key STRING,
# MAGIC -- count_unit DOUBLE,
# MAGIC -- average_price DOUBLE,
# MAGIC -- sales_value DECIMAL(20,10),
# MAGIC -- sales_unit DECIMAL(20,10),
# MAGIC -- counting_units_obversion DECIMAL(20,10),
# MAGIC -- counting_unit DECIMAL(20,10),
# MAGIC -- sales_value_ly DECIMAL(20,10),
# MAGIC -- sales_unit_ly DECIMAL(20,10),
# MAGIC -- counting_unit_ly DECIMAL(20,10),
# MAGIC -- pack_flag INT,
# MAGIC -- brand_flag INT)
# MAGIC -- USING delta
# MAGIC -- LOCATION 'abfss://master@retaildlstoragetest.dfs.core.chinacloudapi.cn/TMP/tmp_retail_dtp_final_sales';
# COMMAND ----------
############################################################START##############################################################
### STEP-1: insert splited pack data into tmp final table: tmp_retail_final_dtp_sales
### STEP-2: calculate OTHERS data
# COMMAND ----------
# MAGIC %md
# MAGIC ## STEP-1: insert splited pack data
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-1: insert splited pack data into tmp final table
# MAGIC -- insert into tmp_retail_dtp_final_sales (dws.dws_retail_dtp_sales)
# MAGIC -------------------------------------------------------------------------------------
# MAGIC with tmp_has_roc as (
# MAGIC select
# MAGIC pack_code,
# MAGIC time
# MAGIC from tmp.tmp_retail_dtp_pack_rawdata
# MAGIC where region = 'ROC'
# MAGIC ), temp_dtp_pack_property as (
# MAGIC select iqvia_pack_code,max(counting_unit) as counting_unit,max(unit) as unit
# MAGIC from dwd.dwd_gnd_ext_dtp_pack_property
# MAGIC group by iqvia_pack_code
# MAGIC ), tmp_pack as (
# MAGIC select
# MAGIC a.year,
# MAGIC a.time,
# MAGIC a.pack_code,
# MAGIC a.region,
# MAGIC a.counting_unit,
# MAGIC a.average_price,
# MAGIC a.sales_amount,
# MAGIC a.sales_volume,
# MAGIC a.counting_units_obversion,
# MAGIC ------------------------------------------------------
# MAGIC -- counting_unit取值逻辑：
# MAGIC -- 不能直接取原始pack文件表中的值，改为取pack_property表中counting_unit / unit的值
# MAGIC a.sales_volume * (b.counting_unit/ coalesce(b.unit,1)) as counting_unit_property,
# MAGIC ------------------------------------------------------
# MAGIC a.data_flag,
# MAGIC a.brand_flag,
# MAGIC -- a.prescription_nature,
# MAGIC -- a.medicine_attribute,
# MAGIC -- a.dosage_form,
# MAGIC -- a.object,
# MAGIC -- a.zk_classify1,
# MAGIC -- a.zk_classify2,
# MAGIC -- a.zk_classify3,
# MAGIC a.target_points
# MAGIC -- a.common_name,
# MAGIC -- a.brand_name,
# MAGIC -- a.product_name,
# MAGIC -- a.pack_des,
# MAGIC -- a.factory,
# MAGIC -- a.corp_des
# MAGIC from tmp.tmp_retail_dtp_pack_rawdata a
# MAGIC left join temp_dtp_pack_property b
# MAGIC ----------------------------------------------------
# MAGIC -- format iqvia_pack_code from dwd_gnd_ext_dtp_pack_property
# MAGIC -- on a.pack_code =
# MAGIC -- case when length(trim(b.iqvia_pack_code)) < 12 and trim(b.iqvia_pack_code) REGEXP '^[0-9]'
# MAGIC -- then right(concat('000000000000',trim(b.iqvia_pack_code)),12)
# MAGIC -- else trim(b.iqvia_pack_code)
# MAGIC -- end
# MAGIC -- ----------------------------------------------------
# MAGIC -- and nvl(a.prescription_nature,'') = nvl(b.prescription_nature,'')
# MAGIC -- and nvl(a.medicine_attribute,'') = nvl(b.medicine_attribute,'')
# MAGIC -- and nvl(a.dosage_form,'') = nvl(b.dosage_form,'')
# MAGIC -- and nvl(a.object,'') = nvl(b.object,'')
# MAGIC -- and nvl(a.zk_classify1,'') = nvl(b.zk_classify1,'')
# MAGIC -- and nvl(a.zk_classify2,'') = nvl(b.zk_classify2,'')
# MAGIC -- and nvl(a.zk_classify3,'') = nvl(b.zk_classify3,'')
# MAGIC -- and nvl(a.target_points,'') = nvl(b.target_points,'')
# MAGIC -- and nvl(a.common_name,'') = nvl(b.common_name,'')
# MAGIC -- and nvl(a.brand_name,'') = nvl(b.brand_name,'')
# MAGIC -- and nvl(a.product_name,'') = nvl(b.product_name,'')
# MAGIC -- and nvl(a.pack_des,'') = nvl(b.zk_pack_des,'')
# MAGIC -- and nvl(a.factory,'') = nvl(b.factory,'')
# MAGIC -- and nvl(a.corp_des,'') = nvl(b.zk_corp_des,'')
# MAGIC -- 20260226 不再使用上面的join条件,改为product_id关联
# MAGIC on a.iqvia_pack_code = b.iqvia_pack_code
# MAGIC ),tmp_pack_this_year_with_roc (
# MAGIC select
# MAGIC a.year,
# MAGIC concat(a.year, 'Q', CEIL(CAST(RIGHT(a.time,2) AS INT)/3)) as yq,
# MAGIC a.time as yyyymm,
# MAGIC a.pack_code as iqvia_pack_code,
# MAGIC a.region as geo_key,
# MAGIC a.counting_unit as count_unit,
# MAGIC a.average_price,
# MAGIC a.sales_amount as sales_value,
# MAGIC a.sales_volume as sales_unit,
# MAGIC a.counting_units_obversion,
# MAGIC a.counting_unit_property as counting_unit,
# MAGIC case when a.data_flag = 0 then 1 else 2 end as pack_flag,
# MAGIC case when a.brand_flag = 1 then 1 else 2 end as brand_flag,
# MAGIC -- a.prescription_nature,
# MAGIC -- a.medicine_attribute,
# MAGIC -- a.dosage_form,
# MAGIC -- a.object,
# MAGIC -- a.zk_classify1,
# MAGIC -- a.zk_classify2,
# MAGIC -- a.zk_classify3,
# MAGIC a.target_points
# MAGIC -- a.common_name,
# MAGIC -- a.brand_name,
# MAGIC -- a.product_name,
# MAGIC -- a.pack_des,
# MAGIC -- a.factory,
# MAGIC -- a.corp_des
# MAGIC from tmp_pack a
# MAGIC where exists (
# MAGIC select * from tmp_has_roc c
# MAGIC where c.pack_code = a.pack_code
# MAGIC and c.time = a.time
# MAGIC ) and a.region <> '全国'
# MAGIC ), tmp_pack_next_year_with_roc (
# MAGIC select
# MAGIC cast(a.year + 1 as int) as year,
# MAGIC concat(cast(a.year + 1 as int) , 'Q', CEIL(CAST(RIGHT(a.time,2) AS INT)/3)) as yq,
# MAGIC cast(a.time + 100 as int) as yyyymm,
# MAGIC a.pack_code as iqvia_pack_code,
# MAGIC a.region as geo_key,
# MAGIC a.counting_unit as count_unit,
# MAGIC a.average_price,
# MAGIC a.sales_amount as sales_value_ly,
# MAGIC a.sales_volume as sales_unit_ly,
# MAGIC a.counting_unit_property as counting_unit_ly,
# MAGIC a.counting_units_obversion,
# MAGIC case when a.data_flag = 0 then 1 else 2 end as pack_flag,
# MAGIC case when a.brand_flag = 1 then 1 else 2 end as brand_flag,
# MAGIC -- a.prescription_nature,
# MAGIC -- a.medicine_attribute,
# MAGIC -- a.dosage_form,
# MAGIC -- a.object,
# MAGIC -- a.zk_classify1,
# MAGIC -- a.zk_classify2,
# MAGIC -- a.zk_classify3,
# MAGIC a.target_points
# MAGIC -- a.common_name,
# MAGIC -- a.brand_name,
# MAGIC -- a.product_name,
# MAGIC -- a.pack_des,
# MAGIC -- a.factory,
# MAGIC -- a.corp_des
# MAGIC from tmp_pack a
# MAGIC where a.time + 100 <= (select max(time) from tmp_pack)
# MAGIC and exists (
# MAGIC select * from tmp_has_roc c
# MAGIC where c.pack_code = a.pack_code
# MAGIC and c.time = a.time
# MAGIC ) and a.region <> '全国'
# MAGIC ), tmp_pack_with_roc as (
# MAGIC select
# MAGIC ifnull(a.year, b.year) as year,
# MAGIC ifnull(a.yq, b.yq) as yq ,
# MAGIC ifnull(a.yyyymm, b.yyyymm) as yyyymm,
# MAGIC ifnull(a.iqvia_pack_code, b.iqvia_pack_code) as iqvia_pack_code,
# MAGIC ifnull(a.geo_key, b.geo_key) as geo_key,
# MAGIC ifnull(a.count_unit, b.count_unit) as count_unit,
# MAGIC ifnull(a.average_price, b.average_price) as average_price,
# MAGIC ifnull(a.sales_value, 0) as sales_value,
# MAGIC ifnull(a.sales_unit, 0) as sales_unit,
# MAGIC ifnull(a.counting_unit, 0) as counting_unit,
# MAGIC ifnull(a.counting_units_obversion, b.counting_units_obversion) as counting_units_obversion,
# MAGIC ifnull(a.pack_flag, b.pack_flag) as pack_flag,
# MAGIC ifnull(a.brand_flag, b.brand_flag) as brand_flag,
# MAGIC ifnull(b.sales_value_ly, 0) as sales_value_ly,
# MAGIC ifnull(b.sales_unit_ly, 0) as sales_unit_ly,
# MAGIC ifnull(b.counting_unit_ly, 0) as counting_unit_ly
# MAGIC from tmp_pack_this_year_with_roc a
# MAGIC full outer join tmp_pack_next_year_with_roc b
# MAGIC on a.yyyymm = b.yyyymm
# MAGIC and a.iqvia_pack_code = b.iqvia_pack_code
# MAGIC and a.geo_key = b.geo_key
# MAGIC -- and nvl(a.prescription_nature,'') = nvl(b.prescription_nature,'')
# MAGIC -- and nvl(a.medicine_attribute,'') = nvl(b.medicine_attribute,'')
# MAGIC -- and nvl(a.dosage_form,'') = nvl(b.dosage_form,'')
# MAGIC -- and nvl(a.object,'') = nvl(b.object,'')
# MAGIC -- and nvl(a.zk_classify1,'') = nvl(b.zk_classify1,'')
# MAGIC -- and nvl(a.zk_classify2,'') = nvl(b.zk_classify2,'')
# MAGIC -- and nvl(a.zk_classify3,'') = nvl(b.zk_classify3,'')
# MAGIC and nvl(a.target_points,'') = nvl(b.target_points,'')
# MAGIC -- and nvl(a.common_name,'') = nvl(b.common_name,'')
# MAGIC -- and nvl(a.brand_name,'') = nvl(b.brand_name,'')
# MAGIC -- and nvl(a.product_name,'') = nvl(b.product_name,'')
# MAGIC -- and nvl(a.pack_des,'') = nvl(b.pack_des,'')
# MAGIC -- and nvl(a.factory,'') = nvl(b.factory,'')
# MAGIC -- and nvl(a.corp_des,'') = nvl(b.corp_des,'')
# MAGIC ), tmp_pack_this_year_without_roc (
# MAGIC select
# MAGIC left(a.time, 4) year,
# MAGIC concat(a.year, 'Q', CEIL(CAST(RIGHT(a.time,2) AS INT)/3)) as yq,
# MAGIC a.time as yyyymm,
# MAGIC a.pack_code as iqvia_pack_code,
# MAGIC 'ROC' as geo_key,
# MAGIC a.counting_unit as count_unit,
# MAGIC a.average_price,
# MAGIC a.sales_amount as sales_value,
# MAGIC a.sales_volume as sales_unit,
# MAGIC a.counting_units_obversion,
# MAGIC a.counting_unit_property as counting_unit,
# MAGIC case when data_flag = 0 then 1 else 2 end as pack_flag,
# MAGIC case when brand_flag = 1 then 1 else 2 end as brand_flag,
# MAGIC -- a.prescription_nature,
# MAGIC -- a.medicine_attribute,
# MAGIC -- a.dosage_form,
# MAGIC -- a.object,
# MAGIC -- a.zk_classify1,
# MAGIC -- a.zk_classify2,
# MAGIC -- a.zk_classify3,
# MAGIC a.target_points
# MAGIC -- a.common_name,
# MAGIC -- a.brand_name,
# MAGIC -- a.product_name,
# MAGIC -- a.pack_des,
# MAGIC -- a.factory,
# MAGIC -- a.corp_des
# MAGIC from tmp_pack a
# MAGIC where not exists (
# MAGIC select * from tmp_has_roc c
# MAGIC where c.pack_code = a.pack_code
# MAGIC and c.time = a.time
# MAGIC )
# MAGIC ), tmp_pack_next_year_without_roc as (
# MAGIC select
# MAGIC cast(a.year + 1 as int) as year,
# MAGIC concat(cast(a.year + 1 as int) , 'Q', CEIL(CAST(RIGHT(a.time,2) AS INT)/3)) as yq,
# MAGIC cast(a.time + 100 as int) as yyyymm,
# MAGIC a.pack_code as iqvia_pack_code,
# MAGIC 'ROC' as geo_key,
# MAGIC a.counting_unit as count_unit,
# MAGIC a.average_price,
# MAGIC a.sales_amount as sales_value_ly,
# MAGIC a.sales_volume as sales_unit_ly,
# MAGIC a.counting_unit_property as counting_unit_ly,
# MAGIC a.counting_units_obversion,
# MAGIC case when data_flag = 0 then 1 else 2 end as pack_flag,
# MAGIC case when brand_flag = 1 then 1 else 2 end as brand_flag,
# MAGIC -- a.prescription_nature,
# MAGIC -- a.medicine_attribute,
# MAGIC -- a.dosage_form,
# MAGIC -- a.object,
# MAGIC -- a.zk_classify1,
# MAGIC -- a.zk_classify2,
# MAGIC -- a.zk_classify3,
# MAGIC a.target_points
# MAGIC -- a.common_name,
# MAGIC -- a.brand_name,
# MAGIC -- a.product_name,
# MAGIC -- a.pack_des,
# MAGIC -- a.factory,
# MAGIC -- a.corp_des
# MAGIC from tmp_pack a
# MAGIC where a.time + 100 <= (select max(time) from tmp_pack)
# MAGIC and not exists (
# MAGIC select * from tmp_has_roc c
# MAGIC where c.pack_code = a.pack_code
# MAGIC and c.time = a.time
# MAGIC )
# MAGIC ),tmp_pack_without_roc as (
# MAGIC select
# MAGIC ifnull(a.year, b.year) as year,
# MAGIC ifnull(a.yq, b.yq) as yq ,
# MAGIC ifnull(a.yyyymm, b.yyyymm) as yyyymm,
# MAGIC ifnull(a.iqvia_pack_code, b.iqvia_pack_code) as iqvia_pack_code,
# MAGIC ifnull(a.geo_key, b.geo_key) as geo_key,
# MAGIC ifnull(a.count_unit, b.count_unit) as count_unit,
# MAGIC ifnull(a.average_price, b.average_price) as average_price,
# MAGIC ifnull(a.sales_value, 0) as sales_value,
# MAGIC ifnull(a.sales_unit, 0) as sales_unit,
# MAGIC ifnull(a.counting_unit, 0) as counting_unit,
# MAGIC ifnull(a.counting_units_obversion, b.counting_units_obversion) as counting_units_obversion,
# MAGIC ifnull(a.pack_flag, b.pack_flag) as pack_flag,
# MAGIC ifnull(a.brand_flag, b.brand_flag) as brand_flag,
# MAGIC ifnull(b.sales_value_ly, 0) as sales_value_ly,
# MAGIC ifnull(b.sales_unit_ly, 0) as sales_unit_ly,
# MAGIC ifnull(b.counting_unit_ly, 0) as counting_unit_ly
# MAGIC from tmp_pack_this_year_without_roc a
# MAGIC full outer join tmp_pack_next_year_without_roc b
# MAGIC on a.yyyymm = b.yyyymm
# MAGIC and a.iqvia_pack_code = b.iqvia_pack_code
# MAGIC and a.geo_key = b.geo_key
# MAGIC -- and nvl(a.prescription_nature,'') = nvl(b.prescription_nature,'')
# MAGIC -- and nvl(a.medicine_attribute,'') = nvl(b.medicine_attribute,'')
# MAGIC -- and nvl(a.dosage_form,'') = nvl(b.dosage_form,'')
# MAGIC -- and nvl(a.object,'') = nvl(b.object,'')
# MAGIC -- and nvl(a.zk_classify1,'') = nvl(b.zk_classify1,'')
# MAGIC -- and nvl(a.zk_classify2,'') = nvl(b.zk_classify2,'')
# MAGIC -- and nvl(a.zk_classify3,'') = nvl(b.zk_classify3,'')
# MAGIC and nvl(a.target_points,'') = nvl(b.target_points,'')
# MAGIC -- and nvl(a.common_name,'') = nvl(b.common_name,'')
# MAGIC -- and nvl(a.brand_name,'') = nvl(b.brand_name,'')
# MAGIC -- and nvl(a.product_name,'') = nvl(b.product_name,'')
# MAGIC -- and nvl(a.pack_des,'') = nvl(b.pack_des,'')
# MAGIC -- and nvl(a.factory,'') = nvl(b.factory,'')
# MAGIC -- and nvl(a.corp_des,'') = nvl(b.corp_des,'')
# MAGIC ), tmp_final_sales as (
# MAGIC select * from tmp_pack_with_roc
# MAGIC union all
# MAGIC select * from tmp_pack_without_roc
# MAGIC )
# MAGIC
# MAGIC insert overwrite table tmp.tmp_retail_dtp_final_sales
# MAGIC
# MAGIC select
# MAGIC year,
# MAGIC yq,
# MAGIC yyyymm,
# MAGIC iqvia_pack_code,
# MAGIC geo_key,
# MAGIC count_unit,
# MAGIC average_price,
# MAGIC sales_value,
# MAGIC sales_unit,
# MAGIC counting_units_obversion,
# MAGIC counting_unit,
# MAGIC sales_value_ly,
# MAGIC sales_unit_ly,
# MAGIC counting_unit_ly,
# MAGIC pack_flag,
# MAGIC brand_flag
# MAGIC from tmp_final_sales
# MAGIC order by yyyymm
# MAGIC
# COMMAND ----------
# MAGIC %md
# MAGIC ## STEP-2: calculate OTHERS data
# COMMAND ----------
# DBTITLE 1,不再计算
# %sql
# -------------------------------------------------------------------------------------
# -- STEP-2: calculate OTHERS data
# -- 2.1 calaulate DTP_AZ_OTHERS data
# -------------------------------------------------------------------------------------
# with tmp_az_total_now as (
# select
# cast(top_corp.sales_quarter as int ) yyyymm,
# top_corp.corp_name,
# top_corp.sales_amount *1000000.0 sales_amount,
# pack.CORP_COD
# from dwd.dwd_gnd_ext_retail_dtp_top_copd top_corp
# left join (
# select distinct ZK_Corp_C,CORP_COD
# from dwd.dwd_inc_gnd_retail_b2c_label_total
# ) pack on replace(top_corp.corp_name,'-','')=pack.ZK_Corp_C
# where pack.CORP_COD='A5Z'
# ), tmp_az_total_ly as (
# select
# cast(yyyymm + 100 as int) as yyyymm,
# corp_name,
# sales_amount as sales_amount_ly,
# CORP_COD
# from tmp_az_total_now
# ), tmp_az_total as (
# select
# a.*,
# ifnull(b.sales_amount_ly, 0) as sales_amount_ly
# from tmp_az_total_now a
# left join tmp_az_total_ly b
# on a.yyyymm = b.yyyymm
# and a.corp_name = b.corp_name
# and a.CORP_COD = b.CORP_COD
# ),tmp_az_pack_total as (
# select
# a.year,
# a.yq,
# a.yyyymm,
# sum(a.sales_value) as sales_value,
# sum(a.sales_unit) as sales_unit,
# sum(a.counting_unit) as counting_unit,
# sum(a.sales_value_ly) as sales_value_ly,
# sum(a.sales_unit_ly) as sales_unit_ly,
# sum(a.counting_unit_ly) as counting_unit_ly
# from tmp.tmp_retail_dtp_final_sales a
# where a.iqvia_pack_code in (
# select distinct iqvia_pack_code
# from tmp.tmp_zk_retail_dtp_market_corp
# where corp_cod = 'A5Z'
# )
# group by a.year,a.yq,a.yyyymm
# order by a.yyyymm
# )
# insert into table tmp.tmp_retail_dtp_final_sales
# select
# left(a.yyyymm, 4) as year,
# concat(left(a.yyyymm, 4), 'Q', CEIL(CAST(RIGHT(a.yyyymm,2) AS INT)/3)) as yq,
# a.yyyymm,
# 'DTP_AZ_OTHERS' as iqvia_pack_code,
# 'ROC' as geo_key,
# 0 as count_unit,
# 0 as average_price,
# a.sales_amount - nvl(b.sales_value, 0) as sales_value,
# 0 as sales_unit,
# 0 as counting_units_obversion,
# 0 as counting_unit,
# a.sales_amount_ly - nvl(b.sales_value_ly, 0) as sales_value_ly,
# 0 as sales_unit_ly,
# 0 as counting_unit_ly,
# 0 as pack_flag,
# 0 as brand_flag
# from tmp_az_total a
# left join tmp_az_pack_total b
# on a.yyyymm = b.yyyymm
# order by a.yyyymm
# COMMAND ----------
# DBTITLE 1,不再计算
# %sql
# -------------------------------------------------------------------------------------
# -- STEP-2: calculate OTHERS data
# -- 2.2 calaulate DTP_OTHERS data
# -------------------------------------------------------------------------------------
# with tmp_total_now as (
# SELECT
# dtp_name,
# sales_quarter as yyyymm,
# sales_amount * 1000000.0 as sales_amount
# FROM dwd.dwd_gnd_ext_retail_dtp_overall_market
# ), tmp_total_ly as (
# select
# dtp_name,
# cast(yyyymm + 100 as int) as yyyymm,
# sales_amount as sales_amount_ly
# from tmp_total_now
# ), tmp_total as (
# select
# a.*,
# ifnull(b.sales_amount_ly, 0) as sales_amount_ly
# from tmp_total_now a
# left join tmp_total_ly b
# on a.yyyymm = b.yyyymm
# ),tmp_pack_total as (
# select
# a.year,
# a.yq,
# a.yyyymm,
# sum(a.sales_value) as sales_value,
# sum(a.sales_unit) as sales_unit,
# sum(a.counting_unit) as counting_unit,
# sum(a.sales_value_ly) as sales_value_ly,
# sum(a.sales_unit_ly) as sales_unit_ly,
# sum(a.counting_unit_ly) as counting_unit_ly
# from tmp.tmp_retail_dtp_final_sales a
# group by a.year,a.yq,a.yyyymm
# order by a.yyyymm
# )
# insert into table tmp.tmp_retail_dtp_final_sales
# select
# left(a.yyyymm, 4) as year,
# concat(left(a.yyyymm, 4), 'Q', CEIL(CAST(RIGHT(a.yyyymm,2) AS INT)/3)) as yq,
# a.yyyymm,
# 'DTP_OTHERS' as iqvia_pack_code,
# 'ROC' as geo_key,
# 0 as count_unit,
# 0 as average_price,
# a.sales_amount - nvl(b.sales_value, 0) as sales_value,
# 0 as sales_unit,
# 0 as counting_units_obversion,
# 0 as counting_unit,
# a.sales_amount_ly - nvl(b.sales_value_ly, 0) as sales_value_ly,
# 0 as sales_unit_ly,
# 0 as counting_unit_ly,
# 0 as pack_flag,
# 0 as brand_flag
# from tmp_total a
# left join tmp_pack_total b
# on a.yyyymm = b.yyyymm
# order by a.yyyymm
# COMMAND ----------
############################################################END################################################################
Reference in New Issue View Git Blame Copy Permalink