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
;