Hive列拼接

在工作中需要根据user_id将两列以一定规则合并，实现语句如下：

select user_id,concat_ws('@',collect_list(concat(save_on,'#',end_time)))  //效果为***#***@***#***
from default.wireless_coupons 
where partitiontime=:DAY and status=0 and end_time>concat_ws('-',substr(partitiontime,0,4),substr(partitiontime,5,2),substr(partitiontime,7,2)) and coupon_type=0
group by user_id

在hive中join默认为inner join，需要full outer join实现全连接，实例：

INSERT OVERWRITE TABLE userportrait.user_f_portrait_coupons_d PARTITION(PARTITIONTIME=:DAY)
select (case when man.user_id is NULL then zhi.user_id else man.user_id end) as user_id,
((case when man_count is NULL then 0 else man_count end) + (case when zhi_count is NULL then 0 else zhi_count end)) as coupons_count,
((case when zhi_sum is NULL then 0 else zhi_sum end) + (case when man_sum is NUL