hive列转行、行转列

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;
posted @ 2019-10-15 16:07  树下的猫  阅读(362)  评论(0)    收藏  举报