hive concat_w實現將多行記錄合並成一行

hive concat_w實現將多行記錄合並成一行

建表如下：

# 創建商品與促銷活動的映射表
hive -e "set mapred.job.queue.name=pms;
set hive.exec.reducers.max=32;
set mapred.reduce.tasks=32;

drop table if exists product_promotion;
create table product_promotion(product_id bigint, promotion_id String);

insert into table product_promotion 
select p2.product_id, p2.promotion_id 
from pms.promotionv2 p1 inner join pms.promotionv2_main_product_sku p2 
on (p1.id=p2.promotion_id)
where from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') between p1.start_date and p1.end_date;"

數據表的記錄如下：

5112 960024
5112 960025
5112 960026
5112 960027
5112 960028
5113 960043
5113 960044
5113 960045
5113 960046

對promotion_id進行合並：

select product_id, concat_ws('_',collect_set(promotion_id)) as promotion_ids from product_promotion group by product_id

執行結果：

hive > select product_id, concat_ws('_',collect_set(promotion_id)) as promotion_ids from product_promotion group by product_id;
OK
5112 960024_960025_960026_960027_960028
5113 960043_960044_960045_960046
Time taken: 3.116 seconds

這裡的collect_set的作用是對promotion_id去重，值得注意的是，必須保證promotion_id的類型是string類型