hive的collect_set函数

imei	app_id	app_type	third_app_type	is_hiad	is_iap	row_num
i1	a	游戏	动作射击	0	1	1
i1	b	应用	阅读	1	1	3
i1	c	游戏	null	0	0	5
2	a	应用	阅读	1	0	1
i2	b	游戏	动作射击	1	1	2
i2	c	游戏	阅读	0	0	6

 

create external table pinko.app_info
(
imei string
,app_id string
,app_type string
,third_app_type string
,is_hiad int
,is_iap int
,row_num int
)
row format delimited
fields terminated by '\t'
stored as textfile
location '/warehouse/pinko/app_info'
;

select
imei
,concat_ws(',',collect_set(app_id) over(partition by imei order by row_num) )  as  app_id_list
,concat_ws(',',collect_set(app_type) over(partition by imei order by row_num) )  as  app_id_list
,concat_ws(',',collect_set(third_app_type) over(partition by imei order by row_num) )  as  app_id_list
,concat_ws(',',collect_set(if(is_hiad=1,app_id,null)) over(partition by imei order by row_num) )  as  hiad_app_id_list
,concat_ws(',',collect_set(if(is_hiad=1,app_type,null)) over(partition by imei order by row_num) )  as  hiad_app_id_list
,concat_ws(',',collect_set(if(is_hiad=1,third_app_type,null)) over(partition by imei order by row_num) )  as  hiad_app_id_list
,row_num
from app_info

 

select
imei
,app_id_list
,app_type_list
,third_app_id_list
,hiad_app_id_list
, hiad_app_type_list
, hiad_third_app_id_list
from
(
    select
    imei
    ,concat_ws(',',collect_set(app_id) over(partition by imei order by row_num) )                                      as  app_id_list
    ,concat_ws(',',collect_set(app_type) over(partition by imei order by row_num) )                                  as  app_type_list
    ,concat_ws(',',collect_set(third_app_type) over(partition by imei order by row_num) )                          as  third_app_id_list
    ,concat_ws(',',collect_set(if(is_hiad=1,app_id,null)) over(partition by imei order by row_num) )             as  hiad_app_id_list
    ,concat_ws(',',collect_set(if(is_hiad=1,app_type,null)) over(partition by imei order by row_num) )          as  hiad_app_type_list
    ,concat_ws(',',collect_set(if(is_hiad=1,third_app_type,null)) over(partition by imei order by row_num) )  as  hiad_third_app_id_list
    ,row_number() over(partition by imei order by row_num desc) as row_num
    from app_info
) t1
where row_num=1

select
imei
,concat_ws(',',collect_set(app_id)                                        )      as  app_id_list
,concat_ws(',',collect_set(app_type)                                    )       as  app_type_list 
,concat_ws(',',collect_set(third_app_type)                            )       as  third_app_id_list 
,concat_ws(',',collect_set(if(is_hiad=1,app_id,null))               )       as  hiad_app_id_list 
,concat_ws(',',collect_set(if(is_hiad=1,app_type,null))            )        as  hiad_app_type_list 
,concat_ws(',',collect_set(if(is_hiad=1,third_app_type,null))   )        as  hiad_third_app_id_list 
from app_info
group by imei