spark sql 分析血缘问题。在含有多个子查询(left join)的语句中怎么分析源表?

问答 ev7mtc3 ⋅ 于 2021-03-18 09:14:32 ⋅ 最后回复由 潘牛 2021-03-18 10:57:14 ⋅ 1417 阅读
INSERT OVERWRITE TABLE ldldws.fact_mars_offtake PARTITION (partition_year_month)
SELECT *,DATE_FORMAT(mars_offtake_date,'yyyy-MM') AS partition_year_month
FROM
(
  SELECT
    t1.DOCNO AS mars_offtake_document,
    t1.ODATE AS mars_offtake_date,
    t1.CTMSI AS terminal_code,
    t1.PDTID AS material_code,
    CASE WHEN t1.bomfg = 'NR' THEN 'FG' ELSE t1.bomfg END  AS complex_flag_code,
    mat.sub_material_code AS sub_material_code,
    t1.DIVID AS division_code,
    t3.company_code AS company_code,
    t4.sales_organization_code AS sales_organization_code,
    t1.SOLDT AS customer_code,
    t1.SHIPT AS ship_to_party,
    t1.OQTY AS sellout_offtake_quantity,
    t1.OQTY * t2.sales_price AS sellout_retail_value,
    NULL AS sellout_promotion_value,
    t1.OAMT AS sellout_actual_value,
    NULL AS sellout_recommend_sales_valued,
    t1.OTYPE AS sales_type,
    t1.DELAY AS delay_falg,
    t1.OTYPE AS mars_offtake_type_code,
    t1.BACODE AS ba_code,
    t1.GP_TIERS AS tires,
    t1.GL_NUMORDRE AS num_ordre,
    t1.GP_REFEXTERNE AS refex_terne,
    t1.GP_TICKETANNULE AS ticket_annule,
    t1.GL_DPA AS price,
    t1.EDATE AS mars_offtake_document_date,
    t1.O2OType AS o2o_type,
    t1.PDTID AS bom_code
    FROM
    (
      SELECT * FROM ldldwd.mars_offtake_smb
      WHERE ODATE >= '2020-01-01'
    ) t1
    LEFT JOIN ldldws.vw_dim_terminal t3
      ON t1.CTMSI = t3.terminal_code AND t3.is_current_flag=1
    LEFT JOIN ldldws.dim_company_division t4
      ON t1.DIVID = t4.division_code AND t3.company_code = t4.company_code
    LEFT JOIN
    (
      SELECT material_code, sales_price, from_date, to_date,company_code FROM ldldws.dim_material_price_list
    ) t2
      ON t1.PDTID = t2.material_code AND to_date(t1.ODATE, 'yyyy-MM-dd') BETWEEN t2.from_date AND t2.to_date and t2.company_code = '0888'
    LEFT JOIN
    (
    select material_code,sub_material_code,from_date, to_date,company_code FROM ldldws.dim_material
    ) mat
      on  t1.PDTID = mat.material_code AND to_date(t1.ODATE, 'yyyy-MM-dd') BETWEEN mat.from_date AND mat.to_date and mat.company_code = '0888'
)t5
DISTRIBUTE BY partition_year_month, mars_offtake_document, terminal_code, material_code, division_code, company_code, sales_organization_code
;

INSERT OVERWRITE TABLE ldldws.i_mars_offtake partition(offtake_year)
SELECT
/*+ BROADCAST(current_tem,current_mat,offtake_type) */
fact.mars_offtake_document AS mars_offtake_document,
fact.terminal_code AS terminal_code,
fact.mars_offtake_date AS mars_offtake_date,
fact.material_code AS material_code,
fact.complex_flag_code AS complex_flag_code,
fact.sub_material_code AS sub_material_code,
fact.division_code AS division_code,
fact.company_code AS company_code,
fact.sales_organization_code AS sales_organization_code,
fact.ba_code AS ba_code,
fact.tiers AS tiers,
CASE WHEN fact.mars_offtake_type_code='N' THEN 'NS' ELSE 'SR' END AS mars_offtake_type_code,
fact.sellout_quantity AS sellout_quantity,
fact.sellout_retail_value AS sellout_retail_value,
fact.sellout_recommend_sales_valued AS sellout_recommend_sales_valued,
fact.sellout_promotion_value AS sellout_promotion_value,
fact.sellout_actual_value AS sellout_actual_value,
fact.sales_type AS sales_type,
fact.customer_code AS customer_code,
fact.num_ordre AS num_ordre,
fact.refex_terne AS refex_terne,
fact.ticket_annule AS ticket_annule,
fact.price AS price,
fact.mars_offtake_document_date AS mars_offtake_document_date,
fact.o2o_type AS o2o_type,
fact.delay_flag AS delay_flag,
fact.ship_to_party AS ship_to_party,
current_tem.terminal_name_cn                    ,
current_tem.division_region_name_cn             ,
current_tem.region_code                         ,
current_tem.region_name_cn                      ,
current_tem.crm_counter_code                    ,
current_tem.personname_zh                       ,
current_tem.parent_person_name                  ,
current_tem.terminal_level                      ,
current_tem.is_ka                               ,
current_tem.ka_type                             ,
current_tem.ka_name                             ,
current_tem.city_code                           ,
current_tem.city_name_cn                        ,
current_tem.city_name_en                        ,
current_tem.line_city_name_cn                   ,
current_tem.line_city_name_en                   ,
current_tem.province_code                       ,
current_tem.province_name_cn                    ,
current_tem.province_name_en                    ,
current_tem.channel_code                        ,
current_tem.channel_name_cn                     ,
current_tem.level1_name_cn                      ,
current_tem.level2_name_cn                      ,
current_tem.level3_name_cn                      ,
current_tem.distributor_code as dis_code                ,
current_mat.material_name_cn                    ,
current_mat.material_type_code                  ,
current_mat.material_name_en                    ,
current_mat.material_group_code                 ,
current_mat.material_group_name_en              ,
current_mat.material_class_name_en                        ,
current_mat.material_class_name_en_lv1                    ,
current_mat.material_class_name_en_lv2                    ,
current_mat.material_class_name_en_lv3                    ,
current_mat.material_class_name_en_lv4                    ,
current_mat.material_class_name_en_lv5                    ,
current_mat.material_class_name_en_lv6                    ,
current_mat.material_class_name_en_lv7                    ,
current_mat.material_class_name_en_lv8                    ,
current_mat.material_class_name_en_lv9                    ,
current_mat.material_class_code_local                     ,
current_mat.material_class_name_en_local                  ,
current_mat.material_class_name_en_lv1_local              ,
current_mat.material_class_name_en_lv2_local              ,
current_mat.material_class_name_en_lv3_local              ,
current_mat.material_class_name_en_lv4_local              ,
current_mat.material_class_name_en_lv5_local              ,
current_mat.material_class_name_en_lv6_local              ,
current_mat.material_class_name_en_lv7_local              ,
current_mat.material_class_name_en_lv8_local              ,
current_mat.material_class_name_en_lv9_local              ,
current_mat.material_sales_price                          ,
current_mat.material_sales_price_full                     ,
current_mat.bom_flag                                      ,
current_mat.pcb_flag                                      ,
current_mat.cas_flag                                      ,
offtake_type.mars_offtake_type_name AS mars_offtake_type_name,
date_format(fact.mars_offtake_date,'yyyy') as offtake_year
FROM
(
  SELECT
  mars_offtake_document,
  terminal_code,
  mars_offtake_date,
  material_code,
  complex_flag_code,
  sub_material_code,
  division_code,
  company_code,
  sales_organization_code,
  ba_code,
  tiers,
  mars_offtake_type_code,
  sellout_quantity,
  sellout_retail_value,
  sellout_recommend_sales_valued,
  sellout_promotion_value,
  sellout_actual_value,
  sales_type,
  customer_code,
  num_ordre,
  refex_terne,
  ticket_annule,
  price,
  mars_offtake_document_date,
  o2o_type,
  delay_flag,
  ship_to_party
  FROM ldldws.fact_mars_offtake
) fact
  left join
  (
    select
    terminal_code,terminal_name_cn,division_region_name_cn,region_code,region_name_cn,crm_counter_code,personname_zh,
    parent_person_name,terminal_memo1 as terminal_level,is_ka,ka_type,city_code,ka_name,city_name_cn,city_name_en,
    province_code,province_name_cn,province_name_en,channel_code,channel_name_cn,level1_name_cn,level2_name_cn,level3_name_cn,
    distributor_code,line_city_name_cn,line_city_name_en
    from ldldws.dim_terminal
    where is_current_flag=1
  )current_tem
    on fact.terminal_code = current_tem.terminal_code
  left join
  (
    select
    material_code,material_name_cn,material_type_code,material_name_en,material_group_code,material_group_name_en,
    material_class_name_en,material_class_name_en_lv1,material_class_name_en_lv2,material_class_name_en_lv3,material_class_name_en_lv4,
    material_class_name_en_lv5,material_class_name_en_lv6,material_class_name_en_lv7,material_class_name_en_lv8,material_class_name_en_lv9,
    material_class_code_local,material_class_name_en_local,material_class_name_en_lv1_local,material_class_name_en_lv2_local,material_class_name_en_lv3_local  ,
    material_class_name_en_lv4_local,material_class_name_en_lv5_local,material_class_name_en_lv6_local,material_class_name_en_lv7_local,material_class_name_en_lv8_local,
    material_class_name_en_lv9_local,material_sales_price,
    material_sales_price_full,bom_flag,pcb_flag,cas_flag
    from ldldws.dim_material
    where is_current_flag=1
  )current_mat
    on fact.material_code = current_mat.material_code
LEFT JOIN
(
  SELECT
  CASE WHEN mars_offtake_type_code='N' THEN 'NS' WHEN mars_offtake_type_code='R' THEN 'SR' ELSE mars_offtake_type_code END AS mars_offtake_type_code,
  mars_offtake_type_name
  FROM ldldws.dim_mars_offtake_type
) offtake_type
ON fact.mars_offtake_type_code = offtake_type.mars_offtake_type_code
;

这两个spark sql中 目标表和源表都是哪些?

成为第一个点赞的人吧 :bowtie:
回复数量: 1
  • 潘牛
    2021-03-18 10:57:14

    先找最外层的sql关系,找到from后面的表名或select数据集,如果是select就继续找其from,直到找到表名为止

暂无评论~~
  • 请注意单词拼写,以及中英文排版,参考此页
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,可用Emoji的自动补全, 在输入的时候只需要 ":" 就可以自动提示了 :metal: :point_right: 表情列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif,教程
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
Ctrl+Enter