[Hive]用concat_w实现将多行记录合并成一行

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/yeweiouyang/article/details/41286469

建表如下:

# 创建商品与促销活动的映射表
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类型

阅读更多 登录后自动展开
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页