UDF 一进一出 可无限嵌套
UDA(Aggregation)F 聚合函数,多进一出 如 sum
()
UDT(Generating)F 一进多出 如
lateral view explode()
orc的存储格式+snappy的压缩格式
hive相当于是Hadoop的一个客户端,存数据依赖于hdfs,运行依赖mr
常用内置函数
NVL:给值为 NULL 的数据赋值 NVL( value,default_value)
如果 value 为 NULL,则 NVL 函数返回 default_value 的值,否则返回 value 的值,如果两个参数 都为 NULL ,则返回 NULL。
emp表:
查询:如果员工的 sal 为 NULL,则用领导 id 代替
select ename,nvl(sal,mgr) from emp;
OK
ename _c1
SMITH 800.0
ALLEN 1600.0
WARD 1250.0
JONES 2975.0
MARTIN 1250.0
BLAKE 2850.0
CLARK 2450.0
SCOTT 3000.0
KING 10.0
TURNER 1500.0
ADAMS 1100.0
JAMES 950.0
FORD 3000.0
MILLER 1300.0
UDF:一行->一行可任意嵌套
CASE WHEN THEN ELSE END 有点类似于三元表达式
也可以做多个when then
case a when b then c when d then e when f then g else h end
数据准备
select * from emp_sex;
OK
emp_sex.name emp_sex.dept_id emp_sex.sex
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女
hive (default)> select
> dept_id,
> sum(case sex when '男' then 1 else 0 end) malecount,
> sum(case sex when '女' then 1 else 0 end) femalecount
> from
> emp_sex
> group by dept_id;
dept_id malecount femalecount
A 2 1
B 1 2
也可以用if 语句 if(条件,value1,value2) 也可以嵌套做多个分支(if(if(条件,value1,value2),value3,value4))
select dept_id,sum(if(sex='男',1,0)) malecount,sum(if(sex='女',1,0)) femalecoount from emp_sex group by dept_id;
行转列 将多行的内容转换为一个列单元内容
相关函数说明
CONCAT(string A/col, string B/col
…
)
:返回输入字符串连接后的结果,支持任意个输入字
符串
;
CONCAT_WS(separator, str1, str2,...):第一个参数剩余参数间的分隔符
CONCAT_WS must be "string or array<string>
COLLECT_SET(col): 只接受基本数据类型
将某字段的值进行去重 汇总,产生 Array 类型字段
1.Hive不允许直接访问非group by字段;
2.对于非group by字段,可以用Hive的collect_set函数(这也是UDAF函数)收集这些字段,返回一个数组;
2.对于非group by字段,可以用Hive的collect_set函数(这也是UDAF函数)收集这些字段,返回一个数组;
数据准备
select * from person_info;
OK
person_info.name person_info.constellation person_info.blood_type
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
苍老师 白羊座 B
将星座和血型拼接再一起
select name,concat_ws(',',constellation,blood_type) c_b from person_info;
OK
name c_b
孙悟空 白羊座,A
大海 射手座,A
宋宋 白羊座,B
猪八戒 白羊座,A
凤姐 射手座,A
苍老师 白羊座,B
select t1.c_b, concat_ws('|',collect_set(t1.name)) name from(select name,concat_ws(',',constellation,blood_type) c_b from person_info) t1 group by t1.c_b;
t1.c_b name
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋|苍老师
列转行 将一个列单元的数据转为多行
函数说明
EXPLODE(col)
:将
hive
一列中复杂的
Array
或者
Map
结构拆分成多行。
LATERAL VIEW LATERAL VIEW udtf(expression) tableAlias AS columnAlias
用于和
split, explode
等
UDTF
一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。后面As 为炸裂出来字段的别名
数据准备
select * from movie_info;
OK
movie_info.movie movie_info.category
《疑犯追踪》 悬疑,动作,科幻,剧情
这里用lateral view 是为了和原表能产生关联
select movie,category_name from movie_info lateral view explode(split(category,',')) movie_info as category_name;
OK
movie category_name
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
窗口函数(开窗函数) 可理解为高级的group by
一行对应一个窗口,不加条件时,每个窗口对应所有行,对聚合函数生效
相关函数说明
OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW:当前行 n PRECEDING
:往前
n 行数据 n FOLLOWING
:往后
n
行数据
UNBOUNDED:起点, UNBOUNDED PRECEDING
表示从前面的起点,
UNBOUNDED FOLLOWING
表示到后面的终点
LAG(col,n,default_val)
:往前第
n 行数据 LEAD(col,n, default_val)
:往后第
n
行数据
NTILE(n)
:把有序窗口的行分发到指定数据的组中,各个组有编号,编号从
1
开始,对
于每一行,
NTILE
返回此行所属的组的编号。
注意:
n
必须为
int
类型。
数据准备
select * from business;
OK
business.name business.orderdate business.cost
jack 2017-01-01 10
tony 2017-01-02 15
jack 2017-02-03 23
tony 2017-01-04 29
jack 2017-01-05 46
jack 2017-04-06 42
tony 2017-01-07 50
jack 2017-01-08 55
mart 2017-04-08 62
mart 2017-04-09 68
neil 2017-05-10 12
mart 2017-04-11 75
neil 2017-06-12 80
mart 2017-04-13 94
Time taken: 0.151 seconds, Fetched: 14 row(s)
(1) 查询在 2017
年
4
月份购买过的顾客及总人数 这里的总人数就是指有几个人
不加窗口函数
select name,count(*) from business where substring(orderdate,1,7)='2017-04' group by name;
name _c1
jack 1
mart 4
会显示每个人4月份来的次数,而并不是总人数
加窗口函数
select name,count(*) over() from business where substring(orderdate,1,7)='2017-04' group by name;
name count_window_0
mart 2
jack 2
(2) 查询顾客的购买明细及月购买总额(就是每个月的总额,总体的总额)
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;
name orderdate cost sum_window_0
jack 2017-01-01 10 205
jack 2017-01-08 55 205
tony 2017-01-07 50 205
jack 2017-01-05 46 205
tony 2017-01-04 29 205
tony 2017-01-02 15 205
jack 2017-02-03 23 23
mart 2017-04-13 94 341
jack 2017-04-06 42 341
mart 2017-04-11 75 341
mart 2017-04-09 68 341
mart 2017-04-08 62 341
neil 2017-05-10 12 12
neil 2017-06-12 80 80
Time taken: 17.591 seconds, Fetched: 14 row(s)
(3)将每个顾客的
cost
按照日期进行累加
select name,orderdate,cost,sum(cost) over() as sample1,
sum(cost) over(partition by name) as sample2,
sum(cost) over(partition by name order by orderdate) as sample3,
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as sample4
from business;
name orderdate cost sample1 sample2 sample3 sample4
jack 2017-01-01 10 661 176 10 10
jack 2017-01-05 46 661 176 56 56
jack 2017-01-08 55 661 176 111 111
jack 2017-02-03 23 661 176 134 134
jack 2017-04-06 42 661 176 176 176
mart 2017-04-08 62 661 299 62 62
mart 2017-04-09 68 661 299 130 130
mart 2017-04-11 75 661 299 205 205
mart 2017-04-13 94 661 299 299 299
neil 2017-05-10 12 661 92 12 12
neil 2017-06-12 80 661 92 92 92
tony 2017-01-02 15 661 94 15 15
tony 2017-01-04 29 661 94 44 44
tony 2017-01-07 50 661 94 94 94
Time taken: 67.901 seconds, Fetched: 14 row(s)
sample1 没加任何条件,是所有行相加的结果
sample2 按照name分组,组内数据相加
sample3 按照name分组,组内数据相加 但是是根据orderdate排序后的数据 没加条件就默认从组内起点到
当前点
sample4 和sample3一样
select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as sample5,
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as sample6,
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as sample7
from business;
name orderdate cost sample5 sample6 sample7
jack 2017-01-01 10 10 56 176
jack 2017-01-05 46 56 111 166
jack 2017-01-08 55 101 124 120
jack 2017-02-03 23 78 120 65
jack 2017-04-06 42 65 65 42
mart 2017-04-08 62 62 130 299
mart 2017-04-09 68 130 205 237
mart 2017-04-11 75 143 237 169
mart 2017-04-13 94 169 169 94
neil 2017-05-10 12 12 92 92
neil 2017-06-12 80 92 92 80
tony 2017-01-02 15 15 44 94
tony 2017-01-04 29 44 94 79
tony 2017-01-07 50 79 79 50
sample5 是前面一行和当前行数据相加
sample6 是前面一行和当前行以及后面一行相加
sample7 是从当前到到最后一行相加
rows
必须跟在
order by
子句之后,对排序的结果进行限制
(4)查看顾客上次的购买时间
select name,orderdate,cost,lag(orderdate,1,'2020-20-20') over(partition by name order by orderdate) pre_time,
LEAD(orderdate,1,'2020-02-10') over(partition by name order by orderdate) flo_time
from business;
name orderdate cost pre_time flo_time
jack 2017-01-01 10 2020-20-20 2017-01-05
jack 2017-01-05 46 2017-01-01 2017-01-08
jack 2017-01-08 55 2017-01-05 2017-02-03
jack 2017-02-03 23 2017-01-08 2017-04-06
jack 2017-04-06 42 2017-02-03 2020-02-10
mart 2017-04-08 62 2020-20-20 2017-04-09
mart 2017-04-09 68 2017-04-08 2017-04-11
mart 2017-04-11 75 2017-04-09 2017-04-13
mart 2017-04-13 94 2017-04-11 2020-02-10
neil 2017-05-10 12 2020-20-20 2017-06-12
neil 2017-06-12 80 2017-05-10 2020-02-10
tony 2017-01-02 15 2020-20-20 2017-01-04
tony 2017-01-04 29 2017-01-02 2017-01-07
tony 2017-01-07 50 2017-01-04 2020-02-10
Time taken: 17.318 seconds, Fetched: 14 row(s)
(5)查询前
20%
时间的订单信息
select * from(select name,orderdate,cost,ntile(5) over(order by orderdate) sorted from business) t where sorted=1;
t.name t.orderdate t.cost t.sorted
jack 2017-01-01 10 1
tony 2017-01-02 15 1
tony 2017-01-04 29 1
Time taken: 18.186 seconds, Fetched: 3 row(s)
将14组数据分为了5组 按照order顺序 做个子查询,查询第一个
Rank
函数说明
RANK()
排序相同时
会重复
,
总数不会变
DENSE_RANK()
排序相同时
会重复
,
总数会减少
ROW_NUMBER()
会根据顺序计算
数据准备
score.name score.subject score.score
孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78
select name,subject,score,rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) rp,
row_number() over(partition by subject order by score desc) rp
from score;
name subject score rp rp rp
孙悟空 数学 95 1 1 1
宋宋 数学 86 2 2 2
婷婷 数学 85 3 3 3
大海 数学 56 4 4 4
宋宋 英语 84 1 1 1
大海 英语 84 1 1 2
婷婷 英语 78 3 2 3
孙悟空 英语 68 4 3 4
大海 语文 94 1 1 1
孙悟空 语文 87 2 2 2
婷婷 语文 65 3 3 3
宋宋 语文 64 4 4 4
这里求完后,可以根据rank() 别名来取想要的内容(类似where rk<=3等等),不过要嵌套一个子查询(因为执行的顺序)