hiveSql学习

1、如何将多字段拼接成一个字段，并且排除原始表中的某些字段
(user_pin)?+.+就是不需要user_pin字段，属于高阶用法

insert overwrite table user_table partition(dt='2020-11-21')
select user_pin, a.`(dt)?+.+`, b.`(user_pin)?+.+`
from 
    user_table a
left join (
    select 
        user_pin, 
        concat_ws(';', collect_list(user_item_imp_cnt_30d)) as user_item_imp_cnt_30d,
        concat_ws(';', collect_list(user_item_imp_cnt_7d)) as user_item_imp_cnt_7d,
        concat_ws(';', collect_list(user_item_imp_cnt_3d)) as user_item_imp_cnt_3d,
        concat_ws(';', collect_list(user_item_click_cnt_30d)) as user_item_click_cnt_30d,
        concat_ws(';', collect_list(user_item_click_cnt_7d)) as user_item_click_cnt_7d,
        concat_ws(';', collect_list(user_item_click_cnt_3d)) as user_item_click_cnt_3d,
        concat_ws(';', collect_list(user_item_click_rate_30d)) as user_item_click_rate_30d,
        concat_ws(';', collect_list(user_item_click_rate_7d)) as user_item_click_rate_7d,
        concat_ws(';', collect_list(user_item_click_rate_3d)) as user_item_click_rate_3d,
        concat_ws(';', collect_list(user_item_click_days_30d)) as user_item_click_days_30d,
        concat_ws(';', collect_list(user_item_lst_click_days_30d)) as user_item_lst_click_days_30d
    from (
        select 
            user_pin,  
            concat(rescode, ':', user_item_imp_cnt_30d) as user_item_imp_cnt_30d,
            concat(rescode, ':', user_item_imp_cnt_7d) as user_item_imp_cnt_7d,
            concat(rescode, ':', user_item_imp_cnt_3d) as user_item_imp_cnt_3d,
            concat(rescode, ':', user_item_click_cnt_30d) as user_item_click_cnt_30d,
            concat(rescode, ':', user_item_click_cnt_7d) as user_item_click_cnt_7d,
            concat(rescode, ':', user_item_click_cnt_3d) as user_item_click_cnt_3d,
            concat(rescode, ':', user_item_click_rate_30d) as user_item_click_rate_30d,
            concat(rescode, ':', user_item_click_rate_7d) as user_item_click_rate_7d,
            concat(rescode, ':', user_item_click_rate_3d) as user_item_click_rate_3d,
            concat(rescode, ':', user_item_click_days_30d) as user_item_click_days_30d,
            concat(rescode, ':', user_item_lst_click_days_30d) as user_item_lst_click_days_30d
        from dmc_qm.dmcqm_lhmx_new_cms_recmd_user_item_fea_s_d
        where dt='2020-11-21'
    ) t
    group by user_pin
) b
on a.user_pin = b.user_pin and a.dt='2020-11-21'

2、将字符串拆成多列
select count(1) from (select explode(split(data, ‘,’)) from dmc_bc.dmcbc_ust_dmc_bc_bx_feed_rand_recall_result_s_d where dt=‘2020-12-06’) t
3、hive函数
https://www.cnblogs.com/end/archive/2012/06/18/2553682.html