Hive函数

发布于 2021-03-17  42 次阅读


collect_list & collect_set

将分组中的某列转为一个数组返回(列转行)

collect_list不去重 collect_set去重

数据样本:

user_name video_name
张三,大唐双龙传
李四,天下无贼
张三,神探狄仁杰
李四,霸王别姬
李四,霸王别姬
王五,机器人总动员
王五,放牛班的春天
王五,盗梦空间

将数据加载到Hive表:

load data local inpath './data' into table 表名 partition (day='20180516');

张三 大唐双龙传  20180516
李四 天下无贼 20180516
张三 神探狄仁杰 20180516
李四 霸王别姬 20180516
李四 霸王别姬 20180516
王五 机器人总动员 20180516
王五 放牛班的春天 20180516
王五 盗梦空间 20180516

按用户分组,取出每个用户每天看过的所有视频的名字:

select username, collect_list(video_name) from 表名 group by username;

张三 ["大唐双龙传","神採秋仁杰"]
李四 ["天下无贼","霸王别姬","霸王别姬"]
王五 ["机器人总动员","放牛班的春天","盗梦空间"]

select username, collect_set(video_name) from 表名 group by username;

张三 ["大唐双龙传","神採秋仁杰"]
李四 ["天下无贼","霸王别姬"]
王五 ["机器人总动员","放牛班的春天","盗梦空间"]

突破group by限制

不是分组列 却能出现在select中

select username, collect_list(video_name)[0] from 表名 group by username;

张三 大唐双龙传
李四 天下无贼
王五 机器人总动员

eplode

将一行复杂的array或者map 拆分成多行(行转列)

lateral view

命令格式:

LATERAL VIEW [outer] explode(字段) tableAlias AS columnAlias

说明:

  • outer关键字 避免explode返回null 影响主表
  • where 中可以使用columAlias别名
刘德华    演员,导演,制片人
李小龙    演员,导演,制片人,幕后,武术指导
李连杰    演员,武术指导
刘亦菲    演员
刘德华 演员
刘德华 导演
刘德华 制片人
李小龙 演员
李小龙 导演
李小龙 制片人
李小龙 幕后
李小龙 武术指导
select
   username,role
from
    ods.ods_actor_data
LATERAL VIEW
    explode(split(userrole,',')) tmpTable as role;

开窗函数

命令格式:

聚合函数 OVER(PARTITION BY 字段 ORDER BY 字段 ROWS BETWEEN 起始 AND 终止)

说明:

  • n PRECEDING 往前n行
  • n FOLLOWING 往后n行
  • CURRENT ROW 当前行
  • UNBOUNDED PRECEDING 最前
  • UNBOUNDED FOLLOWING 最后
  • partition by和group by不能同时出现

排名函数:

  • row_number() 行号
  • rank() 排名
  • desc_rank() 去重排名

LAG 和 LEAD 函数

命令格式:

  • LAG(col,n,DEFAULT) 统计窗口内往上第n行值
  • LEAD(col,n,DEFAULT) 统计窗口内往下第n行值

参数说明:

  • 第一个参数为列名
  • 第二个参数为往上第n行(默认为1)
  • 第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)