hive的collect_set函数

最新推荐文章于 2025-02-10 22:44:08 发布

隔壁老登

最新推荐文章于 2025-02-10 22:44:08 发布

阅读量2k

点赞数 5

文章标签： hive

本文链接：https://blog.youkuaiyun.com/weixin_45547818/article/details/115287597

版权

本文探讨了IMEI在设备标识中的角色，并聚焦于不同类型的App（游戏和应用）及其特性（如动作射击、阅读），同时揭示了付费应用和高级功能的分布。通过收集和分析各列数据，揭示了设备上安装的应用和游戏的多样化趋势及HiAD和IAP的使用情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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