chenwu/MarketAnalysis-ETL
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
MarketAnalysis-ETL/Retail/02 split_brand_data.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

672 lines
22 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
### constant
DATA_TYPE_MARKET = 'MARKET'
DATA_TYPE_MOLECULE = 'MOLECULE'
DATA_TYPE_BRAND = 'BRAND'
DATA_TYPE_MULTI_MARKET = 'MULTI_MARKET'
DATA_TYPE_MULTI_MOLECULE = 'MULTI_MOLECULE'
DATA_TYPE_MULTI_BRAND = 'MULTI_BRAND'
DATA_TYPE_DUPLICATE = 'DUPLICATE'
DATA_TYPE_HEDGE_TA = 'HEDGE_TA'
DATA_TYPE_HEDGE_MARKET = 'HEDGE_MARKET'
DATA_TYPE_HEDGE_MOLECULE = 'HEDGE_MOLECULE'
DATA_TYPE_HEDGE_BRAND = 'HEDGE_BRAND'
DATA_FLAG_RAW = 0 ### final pack_flag = 1
DATA_FLAG_SUB = 1 ### final pack_flag = 2
DATA_FLAG_ADD = 2 ### final pack_flag = 2
DATA_FLAG_RATIO = 3 ### final pack_flag = 2
DATA_FLAG_AVG = 4 ### final pack_flag = 2 ### The current version is not in use
DATA_FLAG_HEDGE = 5 ### final pack_flag = 2
BRAND_FLAG_NATIVE = 1 ### final brand_flag = 1
BRAND_FLAG_CALCULATE = 2 ### final brand_flag = 2
BRAND_FLAG_NONE = 3 ### final brand_flag = 2
DATA_OTHER_TA = 'OTHER_TA'
DATA_OTHER_MARKET = 'OTHER_MARKET'
DATA_OTHER_MOLECULE = 'OTHER_MOLECULE'
DATA_OTHER_BRAND = 'OTHER_BRAND'
DATA_AREA_TYPE_ROC = 'ROC'
DATA_LABEL_AREA_ALL = '全国'
# COMMAND ----------
############################################################START##############################################################
### STEP-1: load data to tmp table
### STEP-2: substract data from level market to brand
### STEP-3: accumulate data from level brand to market
### STEP-4: calculate brand ratio
# COMMAND ----------
# MAGIC %md
# MAGIC ## STEP-1: load data to tmp table
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-1: load data to tmp table
# MAGIC -- 1.1 load LEVEL_MARKET data to tmp.tmp_retail_level_market
# MAGIC -------------------------------------------------------------------------------------
# MAGIC
# MAGIC insert overwrite table tmp.tmp_retail_level_market
# MAGIC
# MAGIC select
# MAGIC a.quarter,
# MAGIC a.province_city,
# MAGIC b.level_ta,
# MAGIC b.level_market,
# MAGIC a.sales_val,
# MAGIC a.sales_vol,
# MAGIC 0
# MAGIC from tmp.tmp_retail_brand_rawdata a
# MAGIC inner join dwd.dwd_gnd_retail_split_automatic b
# MAGIC on a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.brand_category = b.brand
# MAGIC and ifnull(a.common_name, '') = ifnull(b.common_name, '')
# MAGIC where b.level = 'MARKET'
# MAGIC
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-1: load data to tmp table
# MAGIC -- 1.2 load LEVEL_MOLECULE data to tmp.tmp_retail_level_molecule
# MAGIC -------------------------------------------------------------------------------------
# MAGIC insert overwrite table tmp.tmp_retail_level_molecule
# MAGIC
# MAGIC select
# MAGIC a.quarter,
# MAGIC a.province_city,
# MAGIC b.level_ta,
# MAGIC b.level_market,
# MAGIC b.level_molecule,
# MAGIC a.sales_val,
# MAGIC a.sales_vol,
# MAGIC 0
# MAGIC from tmp.tmp_retail_brand_rawdata a
# MAGIC inner join dwd.dwd_gnd_retail_split_automatic b
# MAGIC on a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.brand_category = b.brand
# MAGIC and ifnull(a.common_name, '') = ifnull(b.common_name, '')
# MAGIC where b.level = 'MOLECULE'
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-1: load data to tmp table
# MAGIC -- 1.3 load LEVEL_BRAND(BRAND & MULTI_BRAND) data to tmp.tmp_retail_level_brand
# MAGIC -------------------------------------------------------------------------------------
# MAGIC insert overwrite table tmp.tmp_retail_level_brand
# MAGIC
# MAGIC with exploded_data as (
# MAGIC select
# MAGIC ta,
# MAGIC market,
# MAGIC brand,
# MAGIC ifnull(common_name,'' ) as common_name,
# MAGIC level,
# MAGIC split(level_ta,',') array_ta,
# MAGIC split(level_market,',') array_market,
# MAGIC split(level_molecule,',') array_molecule,
# MAGIC split(level_brand,',') array_brand
# MAGIC from dwd.dwd_gnd_retail_split_automatic
# MAGIC where level like '%MULTI_BRAND%'
# MAGIC ), tmp_config as (
# MAGIC select
# MAGIC ta,
# MAGIC market,
# MAGIC brand,
# MAGIC common_name,
# MAGIC level,
# MAGIC array_ta[idx] as level_ta,
# MAGIC array_market[idx] as level_market,
# MAGIC array_molecule[idx] as level_molecule,
# MAGIC array_brand[idx] as level_brand
# MAGIC from exploded_data
# MAGIC lateral view posexplode(array_ta) AS idx, split_ta
# MAGIC )
# MAGIC ----------------brand data---------------------
# MAGIC select
# MAGIC a.quarter,
# MAGIC a.province_city,
# MAGIC b.level_ta,
# MAGIC b.level_market,
# MAGIC b.level_molecule,
# MAGIC b.level_brand,
# MAGIC a.sales_val,
# MAGIC a.sales_vol,
# MAGIC null,
# MAGIC null,
# MAGIC null,
# MAGIC null,
# MAGIC 0
# MAGIC from tmp.tmp_retail_brand_rawdata a
# MAGIC inner join dwd.dwd_gnd_retail_split_automatic b
# MAGIC on a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.brand_category = b.brand
# MAGIC and ifnull(a.common_name, '') = ifnull(b.common_name, '')
# MAGIC where b.level = 'BRAND'
# MAGIC
# MAGIC union all
# MAGIC ----------------multi brand data---------------------
# MAGIC select
# MAGIC a.quarter,
# MAGIC a.province_city,
# MAGIC b.level_ta,
# MAGIC b.level_market,
# MAGIC b.level_molecule,
# MAGIC b.level_brand,
# MAGIC a.sales_val,
# MAGIC a.sales_vol,
# MAGIC null,
# MAGIC null,
# MAGIC null,
# MAGIC null,
# MAGIC 0
# MAGIC from tmp.tmp_retail_brand_rawdata a
# MAGIC inner join tmp_config b
# MAGIC on a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.brand_category = b.brand
# MAGIC and ifnull(a.common_name, '') = ifnull(b.common_name, '')
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-1: load data to tmp table
# MAGIC -- 1.4 append HEDGE_TA data to every level
# MAGIC -- 1.4.1 append HEDGE_MARKET data to LEVEL_MARKET
# MAGIC -------------------------------------------------------------------------------------
# MAGIC
# MAGIC with tmp_hedge as (
# MAGIC select
# MAGIC a.quarter,
# MAGIC a.province_city,
# MAGIC 0 - sum(a.sales_val) as hedge_val,
# MAGIC 0 - sum(a.sales_vol) as hedge_vol
# MAGIC from tmp.tmp_retail_brand_rawdata a
# MAGIC inner join dwd.dwd_gnd_retail_split_automatic b
# MAGIC on a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.brand_category = b.brand
# MAGIC and ifnull(a.common_name, '') = ifnull(b.common_name, '')
# MAGIC where b.level like '%HEDGE_TA%'
# MAGIC group by a.quarter,a.province_city
# MAGIC )
# MAGIC
# MAGIC insert into tmp.tmp_retail_level_market
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC 'HEDGE_TA',
# MAGIC 'HEDGE_MARKET',
# MAGIC hedge_val,
# MAGIC hedge_vol,
# MAGIC 5
# MAGIC from tmp_hedge
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-1: load data to tmp table
# MAGIC -- 1.4 append HEDGE_TA data to every level
# MAGIC -- 1.4.2 append HEDGE_MOLECULE data to LEVEL_MOLECULE
# MAGIC -------------------------------------------------------------------------------------
# MAGIC
# MAGIC with tmp_hedge as (
# MAGIC select
# MAGIC a.quarter,
# MAGIC a.province_city,
# MAGIC 0 - sum(a.sales_val) as hedge_val,
# MAGIC 0 - sum(a.sales_vol) as hedge_vol
# MAGIC from tmp.tmp_retail_brand_rawdata a
# MAGIC inner join dwd.dwd_gnd_retail_split_automatic b
# MAGIC on a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.brand_category = b.brand
# MAGIC and ifnull(a.common_name, '') = ifnull(b.common_name, '')
# MAGIC where b.level like '%HEDGE_TA%'
# MAGIC group by a.quarter,a.province_city
# MAGIC )
# MAGIC insert into tmp.tmp_retail_level_molecule
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC 'HEDGE_TA',
# MAGIC 'HEDGE_MARKET',
# MAGIC 'HEDGE_MOLECULE',
# MAGIC hedge_val,
# MAGIC hedge_vol,
# MAGIC 5
# MAGIC from tmp_hedge
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-1: load data to tmp table
# MAGIC -- 1.4 append HEDGE_TA data to every level
# MAGIC -- 1.4.3 append HEDGE_BRAND data to LEVEL_BRAND
# MAGIC -------------------------------------------------------------------------------------
# MAGIC
# MAGIC with tmp_hedge as (
# MAGIC select
# MAGIC a.quarter,
# MAGIC a.province_city,
# MAGIC 0 - sum(a.sales_val) as hedge_val,
# MAGIC 0 - sum(a.sales_vol) as hedge_vol
# MAGIC from tmp.tmp_retail_brand_rawdata a
# MAGIC inner join dwd.dwd_gnd_retail_split_automatic b
# MAGIC on a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.brand_category = b.brand
# MAGIC and ifnull(a.common_name, '') = ifnull(b.common_name, '')
# MAGIC where b.level like '%HEDGE_TA%'
# MAGIC group by a.quarter,a.province_city
# MAGIC )
# MAGIC
# MAGIC insert into table tmp.tmp_retail_level_brand
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC 'HEDGE_TA',
# MAGIC 'HEDGE_MARKET',
# MAGIC 'HEDGE_MOLECULE',
# MAGIC 'HEDGE_BRAND',
# MAGIC hedge_val,
# MAGIC hedge_vol,
# MAGIC null,
# MAGIC null,
# MAGIC null,
# MAGIC null,
# MAGIC 5
# MAGIC from tmp_hedge
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-1: load data to tmp table
# MAGIC -- 1.5 load data from pack, balance level_brand
# MAGIC -- add regional data that is included in the pack but not in the brand
# MAGIC -------------------------------------------------------------------------------------
# MAGIC with tmp_pack_to_brand as (
# MAGIC select distinct a.quarter,a.level_ta,a.level_market, c.cmps_des_c,c.prod_des_c,a.product_id
# MAGIC from tmp.tmp_retail_pack_rawdata a
# MAGIC inner join dwd.dwd_gnd_ext_retail_pack_property c
# MAGIC on a.product_id = c.product_id
# MAGIC where exists (
# MAGIC select distinct b.level_ta, b.level_market
# MAGIC from dwd.dwd_gnd_retail_split_automatic b
# MAGIC where b.level_ta is not null
# MAGIC and a.level_ta = b.level_ta
# MAGIC and a.level_market = b.level_market
# MAGIC ) and a.data_flag = 0
# MAGIC and a.zk_regin <> '全国'
# MAGIC order by a.quarter,a.level_ta,a.level_market, c.cmps_des_c,c.prod_des_c
# MAGIC ), tmp_pack_sum as (
# MAGIC select
# MAGIC a.quarter,
# MAGIC a.zk_regin,
# MAGIC a.level_ta,
# MAGIC a.level_market,
# MAGIC b.cmps_des_c as molecule_desc,
# MAGIC nvl(b.prod_des_c,concat(b.cmps_des_c,'_OTHER_PROD')) as product_desc,
# MAGIC sum(a.sales_value) as sales_val,
# MAGIC round(sum(a.sales_unit * (c.counting_unit/ coalesce(c.unit,1)) ) ,4) as sales_vol
# MAGIC from tmp.tmp_retail_pack_rawdata a
# MAGIC inner join tmp_pack_to_brand b
# MAGIC on a.quarter = b.quarter
# MAGIC and a.level_ta = b.level_ta
# MAGIC and a.level_market = b.level_market
# MAGIC and a.product_id = b.product_id
# MAGIC inner join dwd.dwd_gnd_ext_retail_pack_property c
# MAGIC on a.product_id = c.product_id
# MAGIC where a.data_flag = 0 and zk_regin <> 'ROC'
# MAGIC group by a.quarter, a.zk_regin,a.level_ta,a.level_market,b.cmps_des_c,b.prod_des_c
# MAGIC order by a.quarter,a.zk_regin,a.level_ta
# MAGIC ), tmp_brand as (
# MAGIC select * from tmp.tmp_retail_level_brand where data_flag = 0
# MAGIC ),tmp_final as (
# MAGIC select
# MAGIC a.*,
# MAGIC null as ratio_val,
# MAGIC null as ratio_vol,
# MAGIC null as total_val,
# MAGIC null as total_vol,
# MAGIC 0 as data_flag --从pack聚合出来的brand数据，也是真实的原生数据
# MAGIC from tmp_pack_sum a
# MAGIC where not exists (
# MAGIC select 1 from tmp_brand b
# MAGIC where a.quarter = b.quarter
# MAGIC and a.zk_regin = b.province_city
# MAGIC and a.level_ta = b.ta
# MAGIC and a.level_market = b.market
# MAGIC and a.molecule_desc = b.molecule
# MAGIC and a.product_desc = b.brand
# MAGIC )
# MAGIC )
# MAGIC
# MAGIC insert into table tmp.tmp_retail_level_brand
# MAGIC select * from tmp_final
# COMMAND ----------
# MAGIC %md
# MAGIC ## STEP-2: substract data from level market to brand
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-2: substract data from level market to brand
# MAGIC -- 2.1 substract level market data and add 'OTHER_MOLECULE' data
# MAGIC -------------------------------------------------------------------------------------
# MAGIC
# MAGIC with tmp_aggregate_market as (
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC ta,
# MAGIC market,
# MAGIC sum(sales_val) as sales_val,
# MAGIC sum(sales_vol) as sales_vol
# MAGIC from tmp.tmp_retail_level_molecule
# MAGIC group by quarter, province_city , ta ,market
# MAGIC ),tmp_molecule as (
# MAGIC select
# MAGIC a.quarter,
# MAGIC a.province_city,
# MAGIC a.ta,
# MAGIC a.market,
# MAGIC a.sales_val - ifnull(b.sales_val, 0) as sales_val,
# MAGIC a.sales_vol - ifnull(b.sales_vol, 0) as sales_vol
# MAGIC from tmp.tmp_retail_level_market a
# MAGIC left join tmp_aggregate_market b
# MAGIC on a.quarter = b.quarter
# MAGIC and a.province_city = b.province_city
# MAGIC and a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC )
# MAGIC
# MAGIC -------append OTHER_MOLECULE to tmp.tmp_retail_level_molecule
# MAGIC insert into tmp.tmp_retail_level_molecule
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC ta,
# MAGIC market,
# MAGIC 'OTHER_MOLECULE',
# MAGIC sales_val,
# MAGIC sales_vol,
# MAGIC 1
# MAGIC from tmp_molecule
# MAGIC where sales_val > 0 or sales_vol > 0
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-2: substract data from level market to brand
# MAGIC -- 2.2 substract level molecule data and add 'OTHER_BRAND' data
# MAGIC -------------------------------------------------------------------------------------
# MAGIC with tmp_aggregate_molecule as (
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC ta,
# MAGIC market,
# MAGIC molecule,
# MAGIC sum(sales_val) as sales_val,
# MAGIC sum(sales_vol) as sales_vol
# MAGIC from tmp.tmp_retail_level_brand
# MAGIC group by quarter,province_city ,ta ,market,molecule
# MAGIC ),tmp_brand as (
# MAGIC select
# MAGIC a.quarter,
# MAGIC a.province_city,
# MAGIC a.ta,
# MAGIC a.market,
# MAGIC a.molecule,
# MAGIC a.sales_val - ifnull(b.sales_val, 0) as sales_val,
# MAGIC a.sales_vol - ifnull(b.sales_vol, 0) as sales_vol
# MAGIC from tmp.tmp_retail_level_molecule a
# MAGIC left join tmp_aggregate_molecule b
# MAGIC on a.quarter = b.quarter
# MAGIC and a.province_city = b.province_city
# MAGIC and a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.molecule = b.molecule
# MAGIC )
# MAGIC
# MAGIC -------append OTHER_BRAND to tmp.tmp_retail_level_brand
# MAGIC insert into tmp.tmp_retail_level_brand
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC ta,
# MAGIC market,
# MAGIC molecule,
# MAGIC 'OTHER_BRAND',
# MAGIC sales_val,
# MAGIC sales_vol,
# MAGIC null,
# MAGIC null,
# MAGIC null,
# MAGIC null,
# MAGIC 1
# MAGIC from tmp_brand
# MAGIC where sales_val > 0 or sales_vol > 0
# COMMAND ----------
# MAGIC %md
# MAGIC ## STEP-3: accumulate data from level brand to market
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-3: accumulate data from level brand to market
# MAGIC -- 3.1 accumulate level molecule data and balance 'OTHER_BRAND' & 'OTHER_MOLECULE' data
# MAGIC -- 3.1.1 accumulate level molecule data
# MAGIC -------------------------------------------------------------------------------------
# MAGIC with tmp_aggregate_molecule as (
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC ta,
# MAGIC market,
# MAGIC molecule,
# MAGIC sum(sales_val) as sales_val,
# MAGIC sum(sales_vol) as sales_vol
# MAGIC from tmp.tmp_retail_level_brand
# MAGIC group by quarter,province_city ,ta ,market,molecule
# MAGIC ), tmp_append_molecule as (
# MAGIC select a.*
# MAGIC from tmp_aggregate_molecule a
# MAGIC where not exists (
# MAGIC select 1
# MAGIC from tmp.tmp_retail_level_molecule b
# MAGIC where a.quarter = b.quarter
# MAGIC and a.province_city = b.province_city
# MAGIC and a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.molecule = b.molecule
# MAGIC )
# MAGIC )
# MAGIC -------append level molecule data accumulate from level brand
# MAGIC insert into tmp.tmp_retail_level_molecule
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC ta,
# MAGIC market,
# MAGIC molecule,
# MAGIC sales_val,
# MAGIC sales_vol,
# MAGIC 2
# MAGIC from tmp_append_molecule
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-3: accumulate data from level brand to market
# MAGIC -- 3.1 accumulate level molecule data and balance 'OTHER_BRAND'& 'OTHER_MOLECULE' data
# MAGIC -- 3.1.2 balance 'OTHER_MOLECULE' data
# MAGIC -------------------------------------------------------------------------------------
# MAGIC
# MAGIC merge into tmp.tmp_retail_level_molecule a
# MAGIC using(
# MAGIC select * from tmp.tmp_retail_level_molecule
# MAGIC where data_flag = 2
# MAGIC ) as b
# MAGIC on a.quarter = b.quarter
# MAGIC and a.province_city = b.province_city
# MAGIC and a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.molecule = 'OTHER_MOLECULE'
# MAGIC and a.data_flag = 1
# MAGIC when matched then
# MAGIC update set
# MAGIC a.sales_val = a.sales_val - b.sales_val,
# MAGIC a.sales_vol = a.sales_vol - b.sales_vol
# MAGIC
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-3: accumulate data from level brand to market
# MAGIC -- 3.1 accumulate level molecule data and balance 'OTHER_BRAND'& 'OTHER_MOLECULE' data
# MAGIC -- 3.1.3 balance 'OTHER_BRAND' data
# MAGIC -------------------------------------------------------------------------------------
# MAGIC
# MAGIC merge into tmp.tmp_retail_level_brand a
# MAGIC using(
# MAGIC select * from tmp.tmp_retail_level_molecule
# MAGIC where data_flag = 2
# MAGIC ) as b
# MAGIC on a.quarter = b.quarter
# MAGIC and a.province_city = b.province_city
# MAGIC and a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.molecule = 'OTHER_MOLECULE'
# MAGIC and a.brand = 'OTHER_BRAND'
# MAGIC and a.data_flag = 1
# MAGIC when matched then
# MAGIC update set
# MAGIC a.sales_val = a.sales_val - b.sales_val,
# MAGIC a.sales_vol = a.sales_vol - b.sales_vol
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-3: accumulate data from level brand to market
# MAGIC -- 3.2 accumulate level market data and balance 'OTHER_MOLECULE' data
# MAGIC -- 3.2.1 accumulate level market data
# MAGIC -------------------------------------------------------------------------------------
# MAGIC
# MAGIC with tmp_aggregate_market as (
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC ta,
# MAGIC market,
# MAGIC sum(sales_val) as sales_val,
# MAGIC sum(sales_vol) as sales_vol
# MAGIC from tmp.tmp_retail_level_molecule
# MAGIC group by quarter,province_city ,ta ,market
# MAGIC ), tmp_append_market as (
# MAGIC select a.*
# MAGIC from tmp_aggregate_market a
# MAGIC where not exists (
# MAGIC select 1
# MAGIC from tmp.tmp_retail_level_market b
# MAGIC where a.quarter = b.quarter
# MAGIC and a.province_city = b.province_city
# MAGIC and a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC )
# MAGIC )
# MAGIC -------append level market data accumulate from level molecule
# MAGIC insert into tmp.tmp_retail_level_market
# MAGIC select
# MAGIC quarter,
# MAGIC province_city,
# MAGIC ta,
# MAGIC market,
# MAGIC sales_val,
# MAGIC sales_vol,
# MAGIC 2
# MAGIC from tmp_append_market
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-3: accumulate data from level brand to market
# MAGIC -- 3.2 accumulate level market data and balance 'OTHER_MOLECULE' data
# MAGIC -- 3.2.2 balance 'OTHER_MOLECULE' data
# MAGIC -------------------------------------------------------------------------------------
# MAGIC
# MAGIC ---------not using in this case, beacuse level ta has no data------------------------
# MAGIC --merge into tmp.tmp_retail_level_market a
# MAGIC --using(
# MAGIC -- select * from tmp.tmp_retail_level_molecule
# MAGIC -- where data_flag = 2
# MAGIC --) as b
# MAGIC --on a.quarter = b.quarter
# MAGIC --and a.province_city = b.province_city
# MAGIC --and a.ta = b.ta
# MAGIC --and a.market = 'OTHER_MARKET'
# MAGIC --and a.data_flag = 1
# MAGIC --when matched then
# MAGIC -- update set
# MAGIC -- a.sales_val = a.sales_val - b.sales_val,
# MAGIC -- a.sales_vol = a.sales_vol - b.sales_vol
# COMMAND ----------
# MAGIC %md
# MAGIC ## STEP-4: calculate brand ratio
# COMMAND ----------
# MAGIC %sql
# MAGIC -------------------------------------------------------------------------------------
# MAGIC -- STEP-4: calculate brand ratio
# MAGIC -- update total_val&total_vol and ratio_val&ratio_vol
# MAGIC -------------------------------------------------------------------------------------
# MAGIC
# MAGIC merge into tmp.tmp_retail_level_brand a
# MAGIC using(
# MAGIC select * from tmp.tmp_retail_level_brand
# MAGIC where province_city = '全国'
# MAGIC ) as b
# MAGIC on a.quarter = b.quarter
# MAGIC and a.ta = b.ta
# MAGIC and a.market = b.market
# MAGIC and a.molecule = b.molecule
# MAGIC and a.brand = b.brand
# MAGIC when matched then
# MAGIC update set
# MAGIC a.total_val = b.sales_val,
# MAGIC a.total_vol = b.sales_vol,
# MAGIC a.ratio_val = round(a.sales_val/b.sales_val,10),
# MAGIC a.ratio_vol = round(a.sales_vol/b.sales_vol,10)
# COMMAND ----------
############################################################END##############################################################
Reference in New Issue View Git Blame Copy Permalink