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

710 lines
18 KiB
SQL
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
-- CREATE OR REPLACE TABLE DM.DM_TF_EXT_AIA_SALES (
-- YYYYMM STRING,
-- PACK_CODE STRING,
-- CORP_CODE STRING,
-- AUDIT_CODE STRING,
-- PLATFORM_TYPE STRING,
-- STORE_NAME STRING,
-- STORE_TYPE STRING,
-- REGION_TYPE STRING,
-- PACK_FLAG INT,
-- PROD_FLAG INT,
-- DTP_FLAG INT,
-- SALES_UNIT_CAL DECIMAL(38,10),
-- SALES_UNIT_CAL_LY DECIMAL(38,10),
-- SALES_VALUE_CAL DECIMAL(38,10),
-- SALES_VALUE_CAL_LY DECIMAL(38,10),
-- CONUTING_UNIT DECIMAL(38,10),
-- CONUTING_UNIT_LY DECIMAL(38,10),
-- DATA_SOURCE STRING,
-- INST_CODE STRING COMMENT '内部机构编码',
-- CMPS_FLAG STRING COMMENT '分子式标签',
-- DEPT_NAME STRING COMMENT '科室名称',
-- PRESCRIPTION DECIMAL(38,10) COMMENT '处方张数',
-- PRESCRIPTION_LY DECIMAL(38,10) COMMENT '去年同期处方张数',
-- NEW_CODE STRING COMMENT '主数据关联CODE',
-- AREA STRING COMMENT '城市',
-- H_LEVEL STRING COMMENT '医院类型',
-- REIMBURSE STRING COMMENT '报销情况',
-- REIMBURSE_TYPE STRING COMMENT '报销类型',
-- PRESCRIPTION_SOURCE STRING COMMENT '处方来源',
-- ETL_INSERT_DT TIMESTAMP,
-- ETL_UPDATE_DT TIMESTAMP
-- )
-- USING delta
-- LOCATION 'abfss://master@azcdatalakeprd.dfs.core.chinacloudapi.cn/DM/dm_tf_ext_aia_sales';
-- 上面是生产环境location，下面是测试环境location
-- LOCATION 'abfss://master@retaildlstoragetest.dfs.core.chinacloudapi.cn/DM/dm_tf_ext_aia_sales';
-- COMMAND ----------
-- MAGIC %run ../../../Common/config
-- COMMAND ----------
-- MAGIC %python
-- MAGIC spark.read.table(f'`{CDW_CATALOG}`.`dwd`.`dim_product_wide`').createOrReplaceTempView('cdw_dwd_dim_product_wide')
-- COMMAND ----------
-- 原始数据
-- 字段值的初步处理
CREATE OR REPLACE TEMPORARY VIEW AIA_RAWDATA
AS
SELECT
YEAR,
QTR,
CAST(YEAR * 100 + YM AS INT) YM,
ORG_PROV,
ORG_CITY,
INS_LEVEL,
CASE
WHEN
INS_CD IS NOT NULL
AND
LENGTH (INS_CD) < 7
THEN RIGHT (CONCAT ('0000000', INS_CD), 7)
ELSE INS_CD
END AS ORG_INS_CD,
ATC1,
ATC2,
ATC3,
ATC4,
ATC,
MOLE_NM,
PROD_NM,
ORG_PRD_NM_C,
ORG_PK,
ORG_PRD_STR,
ORG_PTH,
MIN_UNIT,
VALUE,
UNIT,
CAST(PACK_DESC AS INT) AS PACK_DESC,
ORG_PK_UN,
MANU
FROM
DWD.DWD_GND_AIA_RAWDATA
-- COMMAND ----------
--关联出AUDIT_COD、INST_CODE
CREATE OR REPLACE TEMPORARY VIEW AIA_RAWDATA_WITH_AUDIT_INST
AS
SELECT
UPPER(NVL (T2.INST_CODE, T1.ORG_INS_CD)) AS AUDIT_COD,
T1.YEAR,
T1.QTR,
T1.YM,
T1.ORG_PROV,
T1.ORG_CITY,
T1.INS_LEVEL,
T1.ORG_INS_CD,
T1.ATC1,
T1.ATC2,
T1.ATC3,
T1.ATC4,
T1.ATC,
T1.MOLE_NM,
T1.PROD_NM,
T1.ORG_PRD_NM_C,
T1.ORG_PK,
T1.ORG_PRD_STR,
T1.ORG_PTH,
T1.MIN_UNIT,
T1.VALUE,
T1.UNIT,
T1.PACK_DESC,
T1.ORG_PK_UN,
T1.MANU,
NVL (T2.INST_CODE, T1.ORG_INS_CD) AS INST_CODE
FROM
AIA_RAWDATA AS T1
LEFT JOIN
(
SELECT
DISTINCT
CASE
WHEN LENGTH (CPA_HOSPITAL_CODE) < 7
THEN RIGHT (CONCAT ('0000000', CPA_HOSPITAL_CODE), 7)
ELSE CPA_HOSPITAL_CODE
END AS CPA_HOSPITAL_CODE,
INS_CD_NL AS INST_CODE
FROM
DWD.DWD_GND_HOSPITAL_NOT_PROVIDED
) AS T2
ON
T1.ORG_INS_CD = T2.CPA_HOSPITAL_CODE
-- COMMAND ----------
/*
修改人 CHENWU
修改时间 20250513
修改内容 打包分子的数据，不论 打通表 和 事实表，都没有系数，需要用 事实表里的 最小制剂单位数量 作为COUNTING_UNIT
*/
--关联出PACK_COD、CORP_COD、COUNTING_UNIT
CREATE OR REPLACE TEMPORARY VIEW AIA_RAWDATA_WIHT_COUNTING_UNIT
AS
SELECT
CASE
WHEN
LENGTH (TRIM(T2.IQVIA_PACK_CODE)) < 12
AND
TRIM(T2.IQVIA_PACK_CODE) REGEXP '^[0-9]'
THEN RIGHT (CONCAT ('000000000000', TRIM(T2.IQVIA_PACK_CODE)),12)
ELSE TRIM(T2.IQVIA_PACK_CODE)
END AS PACK_COD,
T3.CORP_COD,
CASE
WHEN LEFT (T2.IQVIA_PACK_CODE, 4) = 'AZP_'
THEN CAST(T1.MIN_UNIT / T1.ORG_PK_UN AS DECIMAL(38, 10))
ELSE T3.COUNTING_UNIT
END COUNTING_UNIT,
T1.AUDIT_COD,
T1.YEAR,
T1.QTR,
T1.YM,
T1.ORG_PROV,
T1.ORG_CITY,
T1.INS_LEVEL,
T1.ORG_INS_CD,
T1.ATC1,
T1.ATC2,
T1.ATC3,
T1.ATC4,
T1.ATC,
T1.MOLE_NM,
T1.PROD_NM,
T1.ORG_PRD_NM_C,
T1.ORG_PK,
T1.ORG_PRD_STR,
T1.ORG_PTH,
T1.MIN_UNIT,
T1.VALUE,
T1.UNIT,
T1.PACK_DESC,
T1.ORG_PK_UN,
T1.MANU,
T1.INST_CODE
FROM
AIA_RAWDATA_WITH_AUDIT_INST T1
LEFT JOIN
(
SELECT
DISTINCT
TRIM(IQVIA_PACK_CODE) AS IQVIA_PACK_CODE,
ATC1,
ATC2,
ATC3,
ATC4,
ORG_PRD_CD,
ORG_MOLE_NM_C,
ORG_MANU_PRD_NM_C,
ORG_PRD_NM_C,
ORG_PK,
ORG_PRD_STR,
ORG_PTH,
MIN_PK_UNIT,
ORG_PK_UNIT,
ORG_MANU_NM
FROM
DWD.DWD_GND_EXT_AIA_CPT_DATA
) AS T2
--ATC1编码+ATC2编码+ATC3编码+ATC4编码+药品编码(ATC)+药品通用名+药品产品名+药品商品名+规格+剂型+给药途径+最小销售包装单位+包装规格+生产企业
ON
NVL (T1.ATC1, '') = NVL (T2.ATC1, '')
AND NVL (T1.ATC2, '') = NVL (T2.ATC2, '')
AND NVL (T1.ATC3, '') = NVL (T2.ATC3, '')
AND NVL (T1.ATC4, '') = NVL (T2.ATC4, '')
AND NVL (T1.ATC, '') = NVL (T2.ORG_PRD_CD, '')
AND NVL (T1.MOLE_NM, '') = NVL (T2.ORG_MOLE_NM_C, '')
AND NVL (T1.PROD_NM, '') = NVL (T2.ORG_MANU_PRD_NM_C, '')
AND NVL (T1.ORG_PRD_NM_C, '') = NVL (T2.ORG_PRD_NM_C, '')
AND NVL (T1.ORG_PK, '') = NVL (T2.ORG_PK, '')
AND NVL (T1.ORG_PRD_STR, '') = NVL (T2.ORG_PRD_STR, '')
AND NVL (T1.ORG_PTH, '') = NVL (T2.ORG_PTH, '')
AND NVL (T1.UNIT, '') = NVL (T2.MIN_PK_UNIT, '')
AND NVL (T1.PACK_DESC, '') = NVL (T2.ORG_PK_UNIT, '')
AND NVL (T1.MANU, '') = NVL (T2.ORG_MANU_NM, '')
LEFT JOIN
(
SELECT
PACK_COD,
CORP_COD,
COUNTING_UNIT
FROM
DM.DM_AIA_PACK_PROPERTY
GROUP BY
PACK_COD,
CORP_COD,
COUNTING_UNIT
) T3 ON T2.IQVIA_PACK_CODE = T3.PACK_COD
-- COMMAND ----------
CREATE OR REPLACE TEMPORARY VIEW FILTER_YM
AS
SELECT
MAX(YM) AS MAX_YYYYMM,
CONCAT(CAST(FLOOR(MAX(YM)/100-2) AS STRING),'01') AS MIN_YYYYMM
FROM
AIA_RAWDATA_WIHT_COUNTING_UNIT
-- COMMAND ----------
--计算出LY销量
CREATE OR REPLACE TEMPORARY VIEW AIA_RAWDATA_FINAL AS
SELECT
YM,
ORG_INS_CD,
INST_CODE,
AUDIT_COD,
PACK_COD,
CORP_COD,
SUM(VALUE) AS VALUE,
SUM(VOLUME) AS VOLUME,
SUM(COUNTING_UNIT) AS COUNTING_UNIT,
SUM(VALUE_LY) AS VALUE_LY,
SUM(VOLUME_LY) AS VOLUME_LY,
SUM(COUNTING_UNIT_LY) AS COUNTING_UNIT_LY
FROM
(
SELECT
YM,
ORG_INS_CD,
INST_CODE,
AUDIT_COD,
PACK_COD,
CORP_COD,
CAST(VALUE AS DECIMAL(38, 10)) AS VALUE,
CAST(ORG_PK_UN AS DECIMAL(38, 10)) AS VOLUME,
CAST(ORG_PK_UN AS DECIMAL(38, 10)) * COALESCE(CAST(COUNTING_UNIT AS DECIMAL(38, 10)), 1) AS COUNTING_UNIT,
0 AS VALUE_LY,
0 AS VOLUME_LY,
0 AS COUNTING_UNIT_LY
FROM
AIA_RAWDATA_WIHT_COUNTING_UNIT
UNION ALL
SELECT
CAST(CAST(YM AS INT) + 100 AS STRING) AS YM,
ORG_INS_CD,
INST_CODE,
AUDIT_COD,
PACK_COD,
CORP_COD,
0 AS VALUE,
0 AS VOLUME,
0 AS COUNTING_UNIT,
CAST(VALUE AS DECIMAL(38, 10)) AS VALUE_LY,
CAST(ORG_PK_UN AS DECIMAL(38, 10)) AS VOLUME_LY,
CAST(ORG_PK_UN AS DECIMAL(38, 10)) * COALESCE(CAST(COUNTING_UNIT AS DECIMAL(38, 10)), 1) AS COUNTING_UNIT_LY
FROM
AIA_RAWDATA_WIHT_COUNTING_UNIT T1
JOIN
FILTER_YM T2
ON
1 = 1
WHERE
CAST(CAST(YM AS INT) + 100 AS STRING) <= T2.MAX_YYYYMM
)
GROUP BY
YM,
ORG_INS_CD,
INST_CODE,
AUDIT_COD,
PACK_COD,
CORP_COD
-- COMMAND ----------
CREATE OR REPLACE TEMPORARY VIEW FINAL_DM_TF_EXT_AIA_SALES
AS
SELECT
YM,
ORG_INS_CD,
INST_CODE,
AUDIT_COD,
PACK_COD,
CORP_COD,
SUM(VALUE) AS VALUE,
SUM(VOLUME) AS VOLUME,
SUM(COUNTING_UNIT) AS COUNTING_UNIT,
SUM(VALUE_LY) AS VALUE_LY,
SUM(VOLUME_LY) AS VOLUME_LY,
SUM(COUNTING_UNIT_LY) AS COUNTING_UNIT_LY
FROM
AIA_RAWDATA_FINAL
GROUP BY
YM,
ORG_INS_CD,
INST_CODE,
AUDIT_COD,
PACK_COD,
CORP_COD
-- COMMAND ----------
INSERT OVERWRITE TABLE DM.DM_TF_EXT_AIA_SALES (
YYYYMM,
PACK_CODE,
AUDIT_CODE,
DATA_SOURCE,
SALES_UNIT_CAL,
SALES_UNIT_CAL_LY,
SALES_VALUE_CAL,
SALES_VALUE_CAL_LY,
CONUTING_UNIT,
CONUTING_UNIT_LY,
PRESCRIPTION,
PRESCRIPTION_LY,
PACK_FLAG,
PROD_FLAG,
DTP_FLAG,
CMPS_FLAG,
PLATFORM_TYPE,
STORE_NAME,
STORE_TYPE,
REGION_TYPE,
INST_CODE,
DEPT_NAME,
NEW_CODE,
AREA,
H_LEVEL,
REIMBURSE,
REIMBURSE_TYPE,
PRESCRIPTION_SOURCE,
CORP_CODE,
ETL_INSERT_DT,
ETL_UPDATE_DT
)
SELECT
A.ym,
A.PACK_COD as PACK_CODE,
A.AUDIT_COD AS AUDIT_CODE,
'AIA(Monthly)' DATA_SOURCE,
-------------------------------------
sum(volume) as SALES_UNIT_CAL,
sum(volume_ly) as SALES_UNIT_CAL_LY,
sum(value) as SALES_VALUE_CAL,
sum(value_ly) as SALES_VALUE_CAL_LY,
sum(counting_unit) as CONUTING_UNIT,
sum(counting_unit_ly) as CONUTING_UNIT_LY,
null as prescription,
null as prescription_ly,
-------------------------------------
--Retail藏数逻辑标签------------------
1 PACK_FLAG,
1 PROD_FLAG,
0 DTP_FLAG,
null as cmps_flag,
-------------------------------------
--EC数据标签--------------------------
'' PLATFORM_TYPE,
'' STORE_NAME,
'' STORE_TYPE,
-------------------------------------
--COUNTY数据标签----------------------
'' REGION_TYPE,
-------------------------------------
--AIA数据标签-------------------------
inst_code AS inst_code, --可以移除，报告没有使用
-------------------------------------
--XIE HE 数据标签---------------------
'' as dept_name,
'' as new_code,
'' as area,
'' as h_level,
'' as reimburse,
'' as reimburse_type,
'' as prescription_source,
-------------------------------------
'' as CORP_CODE, --可以移除，报告没有使用
-------------------------------------
FROM_UTC_TIMESTAMP(CURRENT_TIMESTAMP(), 'UTC+8') AS ETL_INSERT_DT,
FROM_UTC_TIMESTAMP(CURRENT_TIMESTAMP(), 'UTC+8') AS ETL_UPDATE_DT
FROM
FINAL_DM_TF_EXT_AIA_SALES A
WHERE
ym >= '202401'
GROUP BY
YM,
PACK_COD,
CORP_COD,
AUDIT_COD,
INST_CODE
-- COMMAND ----------
-- 20260109 dm.dm_ext_aia_sales 是老的表，现在已经用DM.DM_TF_EXT_AIA_SALES 替换，但是为了下游，老表暂时不下线，仍然写入
insert overwrite table dm.dm_ext_aia_sales (
ym,
org_ins_cd,
inst_code,
AUDIT_COD,
pack_cod,
corp_cod,
value,
volume,
counting_unit,
value_ly,
volume_ly,
counting_unit_ly
)
select
ym,
org_ins_cd,
inst_code,
AUDIT_COD,
pack_cod,
corp_cod,
sum(value) as value,
sum(volume) as volume,
sum(counting_unit) as counting_unit,
sum(value_ly) as value_ly,
sum(volume_ly) as volume_ly,
sum(counting_unit_ly) as counting_unit_ly
from
aia_rawdata_FINAL
CROSS JOIN
filter_ym
WHERE
YM BETWEEN filter_ym.min_yyyymm AND filter_ym.max_yyyymm
group by
ym,
org_ins_cd,
inst_code,
AUDIT_COD,
pack_cod,
corp_cod
-- COMMAND ----------
-- MAGIC %md
-- MAGIC 下面是重构验证，chenwu可删
-- COMMAND ----------
-- %sql
-- inst_code
-- ym
-- MARKET
-- COMMAND ----------
-- SELECT ym,AUDIT_COD,inst_code,SUM(value) value,SUM(volume) volume,SUM(counting_unit) counting_unit,SUM(value_ly) value_ly,SUM(volume_ly) volume_ly,SUM(counting_unit_ly) counting_unit_ly
-- FROM dm.dm_ext_aia_sales
-- GROUP BY ym,AUDIT_COD,inst_code
-- ORDER BY ym,AUDIT_COD,inst_code
-- COMMAND ----------
-- SELECT ym,AUDIT_COD,SUM(value) value,SUM(volume) volume,SUM(counting_unit) counting_unit,SUM(value_ly) value_ly,SUM(volume_ly) volume_ly,SUM(counting_unit_ly) counting_unit_ly
-- FROM dm.dm_ext_aia_sales
-- GROUP BY ym,AUDIT_COD
-- ORDER BY ym,AUDIT_COD
-- COMMAND ----------
-- SELECT
-- old.ym,
-- OLD.AUDIT_COD,
-- OLD.inst_code,
-- OLD.value,
-- NEW.value,
-- OLD.value-NEW.value as dif_value,
-- OLD.value_ly,
-- NEW.value_ly,
-- OLD.value_ly-NEW.value_ly as dif_value_ly,
-- OLD.volume,
-- NEW.volume,
-- OLD.volume-NEW.volume as dif_volume,
-- OLD.volume_ly,
-- NEW.volume_ly,
-- OLD.volume_ly-NEW.volume_ly as dif_volume_ly,
-- OLD.counting_unit,
-- NEW.counting_unit,
-- OLD.counting_unit-NEW.counting_unit as dif_counting_unit,
-- OLD.counting_unit_ly,
-- NEW.counting_unit_ly,
-- OLD.counting_unit_ly-NEW.counting_unit_ly as dif_counting_unit_ly
-- FROM
-- (
-- SELECT ym,AUDIT_COD,inst_code,SUM(value) value,SUM(volume) volume,SUM(counting_unit) counting_unit,SUM(value_ly) value_ly,SUM(volume_ly) volume_ly,SUM(counting_unit_ly) counting_unit_ly
-- FROM dm.dm_ext_aia_sales
-- GROUP BY ym,AUDIT_COD,inst_code
-- -- ORDER BY ym
-- ) AS OLD
-- LEFT JOIN
-- (
-- SELECT YYYYMM,AUDIT_CODE,inst_code,SUM(SALES_VALUE_CAL) value,SUM(SALES_UNIT_CAL) volume,SUM(CONUTING_UNIT) counting_unit,SUM(SALES_VALUE_CAL_LY) value_ly,SUM(SALES_UNIT_CAL_LY) volume_ly,SUM(CONUTING_UNIT_LY) counting_unit_ly
-- FROM DM.DM_TF_EXT_AIA_SALES
-- GROUP BY YYYYMM,AUDIT_CODE,inst_code
-- -- ORDER BY YYYYMM
-- ) AS NEW
-- ON OLD.YM =NEW.YYYYMM AND old.AUDIT_COD = NEW.AUDIT_CODE AND old.inst_code = new.inst_code
-- where
-- ABS(OLD.value - NEW.value) > 0.0000000001 OR
-- ABS(OLD.volume - NEW.volume) > 0.0000000001 OR
-- ABS(OLD.value_ly - NEW.value_ly) > 0.0000000001 OR
-- ABS(OLD.volume_ly - NEW.volume_ly) > 0.0000000001 OR
-- ABS(OLD.counting_unit - NEW.counting_unit) > 0.0000000001 OR
-- ABS(OLD.counting_unit_ly - NEW.counting_unit_ly) > 0.0000000001;
-- COMMAND ----------
-- SELECT
-- old.ym,
-- OLD.AUDIT_COD,
-- OLD.value,
-- NEW.value,
-- OLD.value-NEW.value as dif_value,
-- OLD.value_ly,
-- NEW.value_ly,
-- OLD.value_ly-NEW.value_ly as dif_value_ly,
-- OLD.volume,
-- NEW.volume,
-- OLD.volume-NEW.volume as dif_volume,
-- OLD.volume_ly,
-- NEW.volume_ly,
-- OLD.volume_ly-NEW.volume_ly as dif_volume_ly,
-- OLD.counting_unit,
-- NEW.counting_unit,
-- OLD.counting_unit-NEW.counting_unit as dif_counting_unit,
-- OLD.counting_unit_ly,
-- NEW.counting_unit_ly,
-- OLD.counting_unit_ly-NEW.counting_unit_ly as dif_counting_unit_ly
-- FROM
-- (
-- SELECT ym,AUDIT_COD,SUM(value) value,SUM(volume) volume,SUM(counting_unit) counting_unit,SUM(value_ly) value_ly,SUM(volume_ly) volume_ly,SUM(counting_unit_ly) counting_unit_ly
-- FROM dm.dm_ext_aia_sales
-- GROUP BY ym,AUDIT_COD
-- -- ORDER BY ym
-- ) AS OLD
-- LEFT JOIN
-- (
-- SELECT YYYYMM,AUDIT_CODE,SUM(SALES_VALUE_CAL) value,SUM(SALES_UNIT_CAL) volume,SUM(CONUTING_UNIT) counting_unit,SUM(SALES_VALUE_CAL_LY) value_ly,SUM(SALES_UNIT_CAL_LY) volume_ly,SUM(CONUTING_UNIT_LY) counting_unit_ly
-- FROM DM.DM_TF_EXT_AIA_SALES
-- GROUP BY YYYYMM,AUDIT_CODE
-- -- ORDER BY YYYYMM
-- ) AS NEW
-- ON OLD.YM =NEW.YYYYMM AND old.AUDIT_COD = NEW.AUDIT_CODE
-- where
-- ABS(OLD.value - NEW.value) > 0.0000000001 OR
-- ABS(OLD.volume - NEW.volume) > 0.0000000001 OR
-- ABS(OLD.value_ly - NEW.value_ly) > 0.0000000001 OR
-- ABS(OLD.volume_ly - NEW.volume_ly) > 0.0000000001 OR
-- ABS(OLD.counting_unit - NEW.counting_unit) > 0.0000000001 OR
-- ABS(OLD.counting_unit_ly - NEW.counting_unit_ly) > 0.0000000001;
-- COMMAND ----------
-- SELECT
-- old.ym,
-- OLD.value,
-- NEW.value,
-- OLD.value-NEW.value as dif_value,
-- OLD.value_ly,
-- NEW.value_ly,
-- OLD.value_ly-NEW.value_ly as dif_value_ly,
-- OLD.volume,
-- NEW.volume,
-- OLD.volume-NEW.volume as dif_volume,
-- OLD.volume_ly,
-- NEW.volume_ly,
-- OLD.volume_ly-NEW.volume_ly as dif_volume_ly,
-- OLD.counting_unit,
-- NEW.counting_unit,
-- OLD.counting_unit-NEW.counting_unit as dif_counting_unit,
-- OLD.counting_unit_ly,
-- NEW.counting_unit_ly,
-- OLD.counting_unit_ly-NEW.counting_unit_ly as dif_counting_unit_ly
-- FROM
-- (
-- SELECT ym,SUM(value) value,SUM(volume) volume,SUM(counting_unit) counting_unit,SUM(value_ly) value_ly,SUM(volume_ly) volume_ly,SUM(counting_unit_ly) counting_unit_ly
-- FROM dm.dm_ext_aia_sales
-- GROUP BY ym
-- ORDER BY ym
-- ) AS OLD
-- LEFT JOIN
-- (
-- SELECT YYYYMM,SUM(SALES_VALUE_CAL) value,SUM(SALES_UNIT_CAL) volume,SUM(CONUTING_UNIT) counting_unit,SUM(SALES_VALUE_CAL_LY) value_ly,SUM(SALES_UNIT_CAL_LY) volume_ly,SUM(CONUTING_UNIT_LY) counting_unit_ly
-- FROM DM.DM_TF_EXT_AIA_SALES
-- GROUP BY YYYYMM
-- ORDER BY YYYYMM
-- ) AS NEW
-- ON OLD.YM =NEW.YYYYMM
-- where
-- ABS(OLD.value - NEW.value) > 0.0000000001 OR
-- ABS(OLD.volume - NEW.volume) > 0.0000000001 OR
-- ABS(OLD.value_ly - NEW.value_ly) > 0.0000000001 OR
-- ABS(OLD.volume_ly - NEW.volume_ly) > 0.0000000001 OR
-- ABS(OLD.counting_unit - NEW.counting_unit) > 0.0000000001 OR
-- ABS(OLD.counting_unit_ly - NEW.counting_unit_ly) > 0.0000000001;
-- COMMAND ----------
-- -- 这个sql证明，两张表的inst_code，值也相同
-- SELECT
-- *
-- FROM
-- (
-- SELECT inst_code,COUNT(*) as c1 FROM DM.DM_TF_EXT_AIA_SALES
-- GROUP BY inst_code
-- ) AS NEW
-- FULL JOIN
-- (
-- SELECT inst_code,COUNT(*) as c1 FROM DM.dm_ext_aia_sales
-- GROUP BY inst_code
-- ) AS OLD
-- ON NEW.inst_code = old.inst_code
-- WHERE new.c1 != old.c1
-- COMMAND ----------
-- -- 这个sql证明，两张表的inst_code相同
-- SELECT
-- *
-- FROM
-- (
-- SELECT inst_code,COUNT(*) as c1 FROM DM.DM_TF_EXT_AIA_SALES
-- GROUP BY inst_code
-- ) AS NEW
-- FULL JOIN
-- (
-- SELECT inst_code,COUNT(*) as c1 FROM DM.DM_TF_EXT_AIA_SALES
-- GROUP BY inst_code
-- ) AS OLD
-- ON NEW.inst_code = old.inst_code
-- WHERE NEW.inst_code IS NULL OR old.inst_code IS NULL
-- COMMAND ----------
-- -- 这个sql证明，两张表的inst_code，值也相同
-- SELECT
-- *
-- FROM
-- (
-- SELECT inst_code,COUNT(*) as c1 FROM DM.DM_TF_EXT_AIA_SALES
-- GROUP BY inst_code
-- ) AS NEW
-- FULL JOIN
-- (
-- SELECT inst_code,COUNT(*) as c1 FROM DM.DM_TF_EXT_AIA_SALES
-- GROUP BY inst_code
-- ) AS OLD
-- ON NEW.inst_code = old.inst_code
-- WHERE new.c1 != old.c1
-- COMMAND ----------
-- SELECT COUNT(*) FROM DM.DM_TF_EXT_AIA_SALES
-- COMMAND ----------
-- SELECT COUNT(*) FROM DM.dm_ext_aia_sales
Reference in New Issue View Git Blame Copy Permalink