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