1. 炸裂函数
定义:接收一行数据,输出一行或多行数据
语法:lateral view explode(depend) tmp as fields
depend:炸裂依据,接收的一行数据
tmp:临时表
fileds:炸裂后形成的列,用于输出一行或多行数据
案例演示:
novel | category |
斗破苍穹 | 玄幻,武侠,仙侠 |
星辰变 | 武侠,仙侠,热血 |
西游记 | 玄幻,热血,仙侠 |
需求:按照上述小说的描述,统计各分类小说的数量
<1>. 建表:
create table novel_info
(
novel string, --小说名字
category string --小说分类
)
row format delimited fields terminated by "\t";
<2>. 导入数据
insert overwrite table novel_info
values("斗破苍穹","玄幻,武侠,仙侠"),
("星辰变","武侠,仙侠,热血"),
("西游记","玄幻,热血,仙侠");
<3>. 答案
select cates,
count(*) counts
from (
select novel,
split(category, ',') cate
from novel_info
) t1 lateral view explode(cate) tmp as cates
group by cates;
<4>. 答案展示
cates | counts |
仙侠 | 3 |
武侠 | 2 |
热血 | 2 |
玄幻 | 2 |
2. 窗口函数(开窗函数)
定义:为每行数据单独划分一个窗口,对该窗口范围内的数据进行计算,最后将计算结果返回给该行数据。
(1).聚合函数
max:最大值
max(字段名) over(partition by 字段名1 order by 字段名2) 新字段名
min:最小值
min(字段名) over(partition by 字段名1 order by 字段名2) 新字段名
sum:求和
sum(字段名) over(partition by 字段名1 order by 字段名2) 新字段名
avg:平均值
avg(字段名) over(partition by 字段名1 order by 字段名2) 新字段名
count:计数
count(字段名) over(partition by 字段名1 order by 字段名2) 新字段名
(2). 跨行取值函数
lag(字段名,偏移量,默认值) over(partition by 字段名1 order by 字段名2) 新字段名
解释:向上取偏移量行字段名的数据作为新字段,新字段以字段名1分组,以字段名2排序
例:lag(date,1,"2022-01-01") over(partition by user order by date) last_date
取当前行的date上一行的数据,为空则设为2022-01-01,以user分组,以date排序,新字段名为last_date
lead(字段名,偏移量,默认值) over(partition by 字段名 order by 字段名) 新字段名
解释:向下取偏移量行字段名的数据作为新字段,新字段以字段名1分组,以字段名2排序
例:lead(date,1,"2022-01-01") over(partition by user order by date) next_date
取当前行的date下一行的数据,为空则设为2022-01-01,以user分组,以date排序,新字段名为next_date
first_value:获取某字段的第一个值
first_value(字段名,是否跳过null(true/false)) over(partition by 字段名 order by 字段名) 新字段名
last_value:获取某字段的最后一个值
last_value(字段名,是否跳过null(true/false)) over(partition by 字段名 order by 字段名) 新字段名
(3). 排名函数
rank() :允许名次相同,跳过后续名词 例:1,1,3
语法:rank() over(partition by 字段名 order by 字段名) 新字段名
dense_rank():允许名次相同,不跳过后续名词 例:1,1,2
语法:dense_rank() over(partition by 字段名 order by 字段名) 新字段名
row_number():按行号排序 例:1,2,3
row_number() over(.partition by 字段名 order by 字段名) 新字段名