[hive]with语句,这样就不用创建那么多的临时表了

with 
临时表名1 as (
 ...
),
临时表名2 as (
 ...
),
临时表名3 as (
 ...
),
临时表名4 as (
 ...
)
select xxx
from 这些表

create table dwd.dwd_csyd_monthly_area_index1949(
area_code string,
indicator_type string,
indicator_name string,
di_qu_lei_bie_pin_ci string,
di_qu_lei_bie_zhi_biao_pin_ci string,
jian_suo_zhi_biao string,
qi_shi_shi_jian string,
jie_shu_shi_jian string,
shu_ju_lei_xing string,
guo_biao_di_yu_dai_ma string,
biao_zhun_dan_wei string,
di_qu_ming_cheng string
)
row format delimited
fields terminated by "\t"
;



with one_to_many as --找到一个"指标+地区"对应对个ndicator_type, zheng_shi_unit的数据
(
     select
     zheng_shi_indicator,
     area_code,
     count(*) cnt
     from
     (
         select
         zheng_shi_indicator,
         area_code,
         indicator_type,
         zheng_shi_unit
         from ods.ods_yearbook_table_data_2008_formal_monthly1949_233
         group by zheng_shi_indicator, area_code, indicator_type, zheng_shi_unit
     )t1
     group by zheng_shi_indicator, area_code having cnt>1
)
insert overwrite table dwd.dwd_csyd_monthly_area_index1949
select
t3.area_code,
indicator_type,
indicator_name,
di_qu_lei_bie_pin_ci,
di_qu_lei_bie_zhi_biao_pin_ci,
jian_suo_zhi_biao,
qi_shi_shi_jian,
jie_shu_shi_jian,
shu_ju_lei_xing_str,
if(area.area_num is null,"",area.area_num) guo_biao_di_yu_dai_ma,
biao_zhun_dan_wei,
if(area.show_name is null,"",area.show_name) di_qu_ming_cheng
from
(
    select *
    from
    (
        select
        area_code,
        indicator_type,
        indicator_name,
        di_qu_lei_bie_pin_ci,
        di_qu_lei_bie_zhi_biao_pin_ci,
        jian_suo_zhi_biao,
        if(size(month_set)>0,sort_array(month_set)[0],"") qi_shi_shi_jian,
        if(size(month_set)>0,sort_array(month_set)[size(month_set)-1],"")jie_shu_shi_jian,
        concat_ws(";",jin_du_shu_ju_type_set) shu_ju_lei_xing_str,
        biao_zhun_dan_wei,
        row_number() over (partition by indicator_name,area_code order by indicator_type asc) rn
        from
        (
            select
            t.area_code,
            indicator_type,
            t.zheng_shi_indicator indicator_name,
            "0" di_qu_lei_bie_pin_ci,
            "0" di_qu_lei_bie_zhi_biao_pin_ci,
            t.zheng_shi_indicator jian_suo_zhi_biao,
            zheng_shi_unit biao_zhun_dan_wei,
            collect_set(month) over(partition by t.zheng_shi_indicator,t.area_code) month_set, --空格也会包含进来
            collect_set(jin_du_shu_ju_type) over(partition by t.zheng_shi_indicator,t.area_code) jin_du_shu_ju_type_set
            from
            ods.ods_yearbook_table_data_2008_formal_monthly1949_233 t
            left join one_to_many
            on t.zheng_shi_indicator=one_to_many.zheng_shi_indicator and t.area_code=one_to_many.area_code
            left join ods.ods_excel_ti_xi_biao_for_jin_du_shu_ju_fen_xi_new20220114 excel
            on t.zheng_shi_indicator=excel.tm_wan_zheng_zhi_biao
            where one_to_many.zheng_shi_indicator is null
            and excel.tm_wan_zheng_zhi_biao is not null
        )t1
    )t2
    where rn=1
)t3
left join dim.dim_area_code area
on t3.area_code=area.area_code
;