1.列转行
select t.cust_id
,concat_ws(',',collect_list(group_id)) one_pace
from (select 'A_001' cust_id
,'20191014' group_id
union all
select 'A_001' cust_id
,'20191015' group_id
union all
select 'A_001' cust_id
,'20191016' group_id) t
group by t.cust_id;
2.行转列
select cust_id
,one_pace
,group_id
from (select 'A_001' cust_id
,'20191014,20191015,20191016' one_pace
union all
select 'A_002' cust_id
,'20191014,20191015,20191016' one_pace
union all
select 'A_003' cust_id
,'20191014,20191015,20191016' one_pace) t
lateral view explode(split(one_pace,',')) num as group_id;
-------------------------------------------------------------------------------------------------------
--客户渗透率--
--客户渗透率--
drop table if exists ads_stat_permeability_group_tmp_0701;
create table if not exists ads_stat_permeability_group_tmp_0701
as
select count(distinct case when t.cust_source in ('00001','00002','00003') then t.main_customer_id end ) cust_cnt_all --总客户数
,count(distinct case when (t.is_zq_rh = '1' or t.is_zq_bs = '1' or t.is_bs_rh = '1') then t.main_customer_id end ) cust_cnt_two --持有两家合同及以上的客户数
,count(distinct case when t.cust_source in ('00001','00002') then t.main_customer_id end ) cust_cnt_zq_bs_all --00001-00002总客户数
,count(distinct case when t.cust_source in ('00001','00003') then t.main_customer_id end ) cust_cnt_zq_rh_all --00001-00003总客户数
,count(distinct case when t.cust_source in ('00002','00003') then t.main_customer_id end ) cust_cnt_bs_rh_all --00002-00003总客户数
,count(distinct case when t.is_zq_rh = '1' then t.main_customer_id end ) cust_cnt_zq_rh --00001-00003交叉客户数
,count(distinct case when t.is_zq_bs = '1' then t.main_customer_id end ) cust_cnt_zq_bs --00001-00002交叉客户数
,count(distinct case when t.is_bs_rh = '1' then t.main_customer_id end ) cust_cnt_bs_rh --00002-00003交叉客户数
,count(distinct case when (t.is_zq_rh = '1' or t.is_zq_bs = '1') then t.main_customer_id end ) cust_cnt_cross_zq
,count(distinct case when (t.is_bs_rh = '1' or t.is_zq_bs = '1') then t.main_customer_id end ) cust_cnt_cross_bs
,count(distinct case when (t.is_bs_rh = '1' or t.is_zq_rh = '1') then t.main_customer_id end ) cust_cnt_cross_rh
,count(distinct case when t.cust_source in ('00001') then t.main_customer_id end ) cust_cnt_zq
,count(distinct case when t.cust_source in ('00002') then t.main_customer_id end ) cust_cnt_bs
,count(distinct case when t.cust_source in ('00003') then t.main_customer_id end ) cust_cnt_rh
from dws_coordination_cust_base t
where t.pdate = '2020-06-30'
and t.status = '有效'
and t.cust_source in ('00001','00002','00003')
;
set hive.exec.dynamici.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ads_stat_coordination_index_result_mid partition(category_code,pdate)
select date_add(current_date,-1) stat_date
,stat_index_key key
,cast(stat_index_value as decimal(19,4)) value
,substr(current_timestamp,1,19) create_time
,'custPermeability' category_code
,'2020-06-30' pdate
from (select str_to_map(concat('permeability-all:',round(t.cust_cnt_two / t.cust_cnt_all,6)
,'&permeability-all-00001-00003:',round(t.cust_cnt_zq_rh / t.cust_cnt_zq_rh_all,6)
,'&permeability-all-00001-00002:',round(t.cust_cnt_zq_bs / t.cust_cnt_zq_bs_all,6)
,'&permeability-all-00002-00003:',round(t.cust_cnt_bs_rh / t.cust_cnt_bs_rh_all,6)
,'&00001:',round(t.cust_cnt_cross_zq / t.cust_cnt_zq,6)
,'&permeability-00001-00002:',round(t.cust_cnt_zq_bs / t.cust_cnt_zq,6)
,'&permeability-00001-00003:',round(t.cust_cnt_zq_rh / t.cust_cnt_zq,6)
,'&00003:',round(t.cust_cnt_cross_rh / t.cust_cnt_rh,6)
,'&permeability-00003-00001:',round(t.cust_cnt_zq_rh / t.cust_cnt_rh,6)
,'&permeability-00003-00002:',round(t.cust_cnt_bs_rh / t.cust_cnt_rh,6)
,'&00002:',round(t.cust_cnt_cross_bs / t.cust_cnt_bs,6)
,'&permeability-00002-00001:',round(t.cust_cnt_zq_bs / t.cust_cnt_bs,6)
,'&permeability-00002-00003:',round(t.cust_cnt_bs_rh / t.cust_cnt_bs,6)
), '&', ':') as stat_index
from ads_stat_permeability_group_tmp_0701 t
) k
LATERAL VIEW explode(stat_index) myTable1 AS stat_index_key,stat_index_value
;
-------------------------------
优化前
with
t_cust_trading_flow_i_tmp
as
(
select
serial_no, cust_no,
name
,cust_type,sec_type,done_amt,done_vol,bs,status,fund_bal,sec_bal,done_counts,net_amt,contract_no,occur_date
from
ods_syn_t_cust_trading_flow_i
where
pdate =
'${IncStartAll}'
),
t_cust_trading_flow_tmp
as
(
select
serial_no, cust_no,
name
,cust_type,sec_type,done_amt,done_vol,bs,status,fund_bal,sec_bal,done_counts,net_amt,contract_no,occur_date
from
ods_bak_t_cust_trading_flow
where
pdate =
'${IncStartAll}'
)
insert
overwrite
table
ods_cmzq_bas_t_cust_trading_flow_excep_log
select
tmp.member_ent,
tmp.member_ent_desc,
tmp.id,
tmp.id_name,
tmp.reason,
tmp.status,
tmp.operator,
tmp.handle_time,
tmp.update_time,
tmp.account_time,
tmp.account_tab_name,
tmp.account_status,
tmp.account_info_flag
from
(
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'客户号未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.cust_no != a2.cust_no
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'姓名未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.
name
!= a2.
name
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'客户类型未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.cust_type != a2.cust_type
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'证券类型未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.sec_type != a2.sec_type
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'成交金额未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.done_amt != a2.done_amt
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'成交数量未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.done_vol != a2.done_vol
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'业务类别未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.bs != a2.bs
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'状态未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.status != a2.status
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'资金余额未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.fund_bal != a2.fund_bal
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'证券余额未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.sec_bal != a2.sec_bal
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'成交笔数未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.done_counts != a2.done_counts
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'成交净额未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.net_amt != a2.net_amt
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'合同编号未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.contract_no != a2.contract_no
UNION
ALL
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
a1.serial_no
as
id,
'serial_no'
as
id_name,
'发生日期未更新'
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'${IncStartAll}'
as
update_time,
'${IncEndAll}'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
t_cust_trading_flow_i_tmp
as
a1
join
t_cust_trading_flow_tmp
as
a2
on
a1.serial_no = a2.serial_no
where
a1.occur_date != a2.occur_date) tmp
group
by
tmp.member_ent,
tmp.member_ent_desc,
tmp.id,
tmp.id_name,
tmp.reason,
tmp.status,
tmp.operator,
tmp.handle_time,
tmp.update_time,
tmp.account_time,
tmp.account_tab_name,
tmp.account_status,
tmp.account_info_flag;
set
hive.groupby.skewindata=
true
;
WITH
t_cust_trading_flow_i_tmp
AS
(
SELECT
serial_no,
cust_no,
name
,
cust_type,
sec_type,
done_amt,
done_vol,
bs,
status,
fund_bal,
sec_bal,
done_counts,
net_amt,
contract_no,
occur_date
FROM
(
SELECT
*,
row_number() over (partition
BY
serial_no
ORDER
BY
occur_date
DESC
) num
FROM
ods_syn_t_cust_trading_flow_i t
WHERE
pdate =
'2018-09-07'
) a
WHERE
a.num=1),
t_cust_trading_flow_tmp
AS
(
SELECT
serial_no,
cust_no,
name
,
cust_type,
sec_type,
done_amt,
done_vol,
bs,
status,
fund_bal,
sec_bal,
done_counts,
net_amt,
contract_no,
occur_date
FROM
(
SELECT
*,
row_number() over (partition
BY
serial_no
ORDER
BY
occur_date
DESC
) num
FROM
ods_bak_t_cust_trading_flow t
WHERE
pdate =
'2018-09-07'
) a
WHERE
a.num=1)
insert
overwrite
table
ods_cmzq_bas_t_cust_trading_flow_excep_log
select
'cmzq'
as
member_ent,
'交易流水_普通信息'
as
member_ent_desc,
tmp.serial_no
as
id,
'serial_no'
as
id_name,
reason,
'未处理'
as
status,
''
as
operator,
''
as
handle_time,
'2018-09-07'
as
update_time,
'2018-09-07'
as
account_time,
'ods_bas_t_cust_trading_flow_test'
as
account_tab_name,
'失败'
as
account_status,
'1'
account_info_flag
from
(
select
a1.serial_no,
concat_ws(
','
,
case
when
a1.cust_no != a2.cust_no
then
'客户号未更新'
else
null
end
,
case
when
a1.
name
!= a2.
name
then
'姓名未更新'
else
null
end
,
case
when
a1.cust_type != a2.cust_type
then
'客户类型未更新'
else
null
end
,
case
when
a1.sec_type != a2.sec_type
then
'证券类型未更新'
else
null
end
,
case
when
a1.done_amt != a2.done_amt
then
'成交金额未更新'
else
null
end
,
case
when
a1.done_vol != a2.done_vol
then
'成交数量未更新'
else
null
end
,
case
when
a1.bs != a2.bs
then
'业务类别未更新'
else
null
end
,
case
when
a1.status != a2.status
then
'状态未更新'
else
null
end
,
case
when
a1.fund_bal != a2.fund_bal
then
'资金余额未更新'
else
null
end
,
case
when
a1.sec_bal != a2.sec_bal
then
'证券余额未更新'
else
null
end
,
case
when
a1.done_counts != a2.done_counts
then
'成交笔数未更新'
else
null
end
,
case
when
a1.net_amt != a2.net_amt
then
'成交净额未更新'
else
null
end
,
case
when
a1.contract_no != a2.contract_no
then
'合同编号未更新'
else
null
end
,
case
when
a1.occur_date != a2.occur_date
then
'发生日期未更新'
else
null
end
)
as
reason_set
from
t_cust_trading_flow_i_tmp a1
join
t_cust_trading_flow_tmp a2
on
a1.serial_no = a2.serial_no) tmp
lateral
view
explode(split(reason_set,
','
)) num
as
reason
group
by
tmp.serial_no,
reason;