Hive学习第九天--函数的用法
1.1 Hive窗口函数
普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。
简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。
开窗函数一般就是说的是over()函数，其窗口是由一个 OVER 子句 定义的多行记录
开窗函数一般分为两类,聚合开窗函数和排序开窗函数。
-- 聚合格式
select sum(字段名) over([partition by 字段名] [ order by 字段名]) as 别名,
max(字段名) over() as 别名
from 表名;
-- 排序窗口格式
select rank() over([partition by 字段名] [ order by 字段名]) as 别名 from 表名;
注意点：
- over()函数中的分区、排序、指定窗口范围可组合使用也可以不指定，根据不同的业务需求结合使用
- over()函数中如果不指定分区，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据
测试数据
-- 创建表
create table t_fraction(
name string,
subject string,
score int)
row format delimited fields terminated by ","
lines terminated by '\n';
-- 测试数据 fraction.txt
孙悟空,语文,10
孙悟空,数学,73
孙悟空,英语,15
猪八戒,语文,10
猪八戒,数学,73
猪八戒,英语,11
沙悟净,语文,22
沙悟净,数学,70
沙悟净,英语,31
唐玄奘,语文,21
唐玄奘,数学,81
唐玄奘,英语,23
-- 上传数据
load data local inpath '/usr/local/soft/hive_test/data/fraction.txt' into table t_fraction;
1.1.1 聚合开窗函数
sum(求和)
min(最小)
max(最大)
avg(平均值)
count(计数)
lag(获取当前行上一行的数据）
--
select name,subject,score,sum(score) over() as sumover from t_fraction;
+-------+----------+--------+----------+
| name | subject | score | sumover |
+-------+----------+--------+----------+
| 唐玄奘 | 英语 | 23 | 321 |
| 唐玄奘 | 数学 | 81 | 321 |
| 唐玄奘 | 语文 | 21 | 321 |
| 沙悟净 | 英语 | 31 | 321 |
| 沙悟净 | 数学 | 12 | 321 |
| 沙悟净 | 语文 | 22 | 321 |
| 猪八戒 | 英语 | 11 | 321 |
| 猪八戒 | 数学 | 73 | 321 |
| 猪八戒 | 语文 | 10 | 321 |
| 孙悟空 | 英语 | 15 | 321 |
| 孙悟空 | 数学 | 12 | 321 |
| 孙悟空 | 语文 | 10 | 321 |
+-------+----------+--------+----------+
select name,subject,score,
-- over()中如果什么都不写，窗口大小就是针对查询到得全部数据
sum(score) over() as sum1,
-- 按照科目进行开窗，算每个科目的总分
sum(score) over(partition by subject) as sum2,
-- 在over中加上排序，就会将分数进行排序，按照名次相加得到加和，如果名次一样，那么同时都加上
sum(score) over(partition by subject order by score) as sum3,
-- 由起点到当前行的窗口聚合，和sum3类似，区别是对于相同分数是不一样的
sum(score) over(partition by subject order by score rows between unbounded preceding and current row) as sum4,
-- 当前行和前面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding and current row) as sum5,
-- 当前行的前面一行到后面一行的窗口聚合 前一行+当前行+后一行
sum(score) over(partition by subject order by score rows between 1 preceding and 1 following) as sum6,
-- 当前行与后一行之和
sum(score) over(partition by subject order by score rows between current row and 1 following) as sum6,
-- 当前和后面所有的行
sum(score) over(partition by subject order by score rows between current row and unbounded following) as sum7
from t_fraction;
rows：行
unbounded preceding：起点
unbounded following：终点
n preceding：前 n 行
n following：后 n 行
current row：当前行
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| name | subject | score | sum1 | sum2 | sum3 | sum4 | sum5 | sum6 | sum7 |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| 孙悟空 | 数学 | 12 | 359 | 185 | 12 | 12 | 12 | 31 | 185 |
| 沙悟净 | 数学 | 19 | 359 | 185 | 31 | 31 | 31 | 104 | 173 |
| 猪八戒 | 数学 | 73 | 359 | 185 | 104 | 104 | 92 | 173 | 154 |
| 唐玄奘 | 数学 | 81 | 359 | 185 | 185 | 185 | 154 | 154 | 81 |
| 猪八戒 | 英语 | 11 | 359 | 80 | 11 | 11 | 11 | 26 | 80 |
| 孙悟空 | 英语 | 15 | 359 | 80 | 26 | 26 | 26 | 49 | 69 |
| 唐玄奘 | 英语 | 23 | 359 | 80 | 49 | 49 | 38 | 69 | 54 |
| 沙悟净 | 英语 | 31 | 359 | 80 | 80 | 80 | 54 | 54 | 31 |
| 孙悟空 | 语文 | 10 | 359 | 94 | 10 | 10 | 10 | 31 | 94 |
| 唐玄奘 | 语文 | 21 | 359 | 94 | 31 | 31 | 31 | 53 | 84 |
| 沙悟净 | 语文 | 22 | 359 | 94 | 53 | 53 | 43 | 84 | 63 |
| 猪八戒 | 语文 | 41 | 359 | 94 | 94 | 94 | 63 | 63 | 41 |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量。
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n,default_val)：往前第n行数据，col是列名，n是往上的行数，当第n行为null的时候取default_val
LEAD(col,n, default_val)：往后第n行数据，col是列名，n是往下的行数，当第n行为null的时候取default_val
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。
cume_dist（），计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：
小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。
聚合开窗函数实战：
实战1：Hive用户购买明细数据分析
创建表和加载数据
name，orderdate，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
建表加载数据
vim business.txt
create table business
(
name string,
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/hive_test/data/business.txt" into table business;
实战1需求：
需求1：查询在2017年4月份购买过的顾客及总人数
# 分析：按照日期过滤、分组count求总人数
select t1.name,t1.orderdate,count(1) over() as counts_04 from (select name,orderdate from business where month(orderdate)='04') t1;
select * from business where month(orderdate)='04';
select *,count(1) over() as counts from business where month(orderdate)='04';
需求2：查询顾客的购买明细及月购买总额
# 分析：按照顾客分组、sum购买金额
select name,orderdate,cost,sum(cost) over(partition by name,month(orderdate)) from business;
需求3：上述的场景,要将cost按照日期进行累加
# 分析：按照顾客分组、日期升序排序、组内每条数据将之前的金额累加
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) from business;
select *,sum(cost) over(partition by name,month(cast(orderdate as date)) order by cast(orderdate as date) as leiji_cost,sum(cost) over(partition by name,month(cast(orderdate as date)) as month_cost from business;
需求4：查询顾客上次的购买时间
·# 分析：查询出明细数据同时获取上一条数据的购买时间(肯定需要按照顾客分组、时间升序排序)
select name,orderdate,cost,lag(orderdate,1) over(partition by name order by orderdate) as last_time from business;
select
name
,orderdate as `current_date`
,cost
,nvl(lag(orderdate,1) over(partition by name,month(cast(orderdate as date)) order by cast(orderdate as date)),'本月第一次消费') as `last_date`
,nvl(lag(cost,1) over(partition by name,month(cast(orderdate as date)) order by cast(orderdate as date)),0) as last_cost
,sum(cost) over(partition by name,month(cast(orderdate as date)) order by cast(orderdate as date)) as leiji_cost
,sum(cost) over(partition by name,month(cast(orderdate as date))) as month_cost from business;
需求5：查询前20%时间的订单信息
分析：按照日期升序排序、取前20%的数据
select t1.name,t1.orderdate,t1.cost from (select name,orderdate,cost,ntile(5) over(order by orderdate) as n from business) t1 where t1.n=1;
1.1.2 排序开窗函数(重点)
- RANK() 排序相同时会重复，总数不会变，会跳号
- DENSE_RANK() 排序相同时会重复，总数会减少，不会跳号
- ROW_NUMBER() 会根据顺序计算
- PERCENT_RANK()计算给定行的百分比排名。可以用来计算超过了百分之多少的人(当前行的rank值-1)/(分组内的总行数-1)
select name,subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rnp,
percent_rank() over(partition by subject order by score) as percent_rank
from t_fraction;
select name,subject,score,
rank() over(order by score) as row_number,
percent_rank() over(partition by subject order by score) as percent_rank
from t_fraction;
实战2：Hive分析学生成绩信息
创建表语加载数据
name subject score
李毅 语文 87
李毅 数学 95
李毅 英语 68
黄仙 语文 94
黄仙 数学 56
黄仙 英语 84
小虎 语文 64
小虎 数学 86
小虎 英语 84
许文客 语文 65
许文客 数学 85
许文客 英语 78
建表加载数据
vim score.txt
create table score2
(
name string,
subject string,
score int
) row format delimited fields terminated by "\t";
load data local inpath '/hive_test/data/score.txt' into table score;
需求1：每门学科学生成绩排名(是否并列排名、空位排名三种实现)
分析：学科分组、成绩降序排序、按照成绩排名
select name,subject,score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from
score;
需求2：每门学科成绩排名top 2的学生
select t1.name,t1.subject,t1.score from (select name,subject,score,row_number() over(partition by subject order by score desc) as rn from score2) t1 where t1.rn<3;
Hive 列转行
列转行：就是将一行的数据转换成多行
lateral view explode:形成侧写表
- 关键字：explode()、split()和LATERAL VIEW
-
- split():
将一个字符串按照指定字符分割，结果为一个array；
2）explode():
将一列复杂的array或者map拆分为多行,它的参数必须为map或array；
3）lateral view:
lateral view udtf(字段名)表别名/表临时名as列别名/列临时名。lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateralview再把结果组合，产生一个支持别名表的虚拟表。
- split():
- 列转行基本语法：
select 字段,字段别名 from table_name lateral view explode（split(拆分字段,分隔符)）表别名 as 字段别名
/*原始数据
province city
河南 郑州市,开封市，洛阳市
河北 石家庄市，保定市
湖南 长沙市，岳阳市，常德市
*/
-- addr为表名
SELECT province,
city_n
FROM addr LATERAL VIEW explode (split(city,',')) addr_tmp AS city_n;
/*
-- 查询结果
河南 郑州市
河南 开封市
河南 洛阳市
河北 石家庄市
河北 保定市
湖南 长沙市
湖南 岳阳市
湖南 常德市
*/
create table testArray2(
name string,
weight array<string>
)row format delimited
fields terminated by '\t'
COLLECTION ITEMS terminated by ',';
小虎 "150","170","180"
火火 "150","180","190"
-- lateral view 形成侧写表，用于将炸裂后的表的字段与原表进行关联
-- 如果只需要获取炸裂后的字段，那么可以不用和原表进行关联，即可以不用lateral view
-- 在需要原表数据时，需要使用
-- explode：UDTF
select name,col1 from testarray2 lateral view explode(weight) t1 as col1;
小虎 150
小虎 170
小虎 180
火火 150
火火 180
火火 190
-----------------------------------------
select
a,b,c
from (
select
a,max(case b when "A" then c end) as col_A
,max(case b when "B" then c end) as col_B
from group_score group by a) t1
lateral view explode(map("A",col_A,"B",col_B)) tt1 as b,c;
------------------------------------------------
select key from (select explode(map('key1',1,'key2',2,'key3',3)) as (key,value)) t;
key1
key2
key3
select name,col1,col2 from testarray2 lateral view explode(map('key1',1,'key2',2,'key3',3)) t1 as col1,col2;
小虎 key1 1
小虎 key2 2
小虎 key3 3
火火 key1 1
火火 key2 2
火火 key3 3
select name,pos,col1 from testarray2 lateral view posexplode(weight) t1 as pos,col1;
小虎 0 150
小虎 1 170
小虎 2 180
火火 0 150
火火 1 180
火火 2 190
Hive 行转列
行转列：就是将多个列的数据转换在一列下面
-
关键字：collect_set() / collect_list()、concat_ws()
1）collect_set()/collect_list()：
collect_set( )函数只接受基本数据类型，作用是对参数字段进行去重汇总，返回array类型字段;
collect_list()函数和collect_set( )作用一样，只是前者不去重,后者去重。
2）concat_ws()：
concat_ws (separator,字符串A/字段名A,字符串B/字段名B…)是concat的特殊形式，第一个参数是分隔符，分隔符会放到要连接的字符串之间,分隔符可以是字符串，也可以是其他参数。如果分隔符为NULL,则结果为NULL,函数会忽略任何分隔符参数后的 NULL值。但是concat_ws( )不会忽略任何空字符串。（然而会忽略所有的 NULL)，如果参数为string类型数组则合并数组内字符串。
例:concat_ws( ',', [ 'a ', 'b'])–> 'a,b' -
行转列基本语法：
select 分类字段,concat_ws(',',collect_set(合并字段)) as 别名 from table_name group 分类字段
/* 原始数据
name gender times
张三 男 唐
李四 男 唐
王五 男 明
赵六 男 明
*/
-- 先用collect_set将列拼接在一起，然后再通过concat_ws进行展开拼接
SELECT a.gender_times,
concat_ws(';',collect_set(a.name)) name
FROM
(SELECT name,
concat(gender, '_','times') gender_times
FROM hero_info) t
GROUP BY t.gender_times;
/*查询结果
gender_times name
男_唐 张三;李四
男_明 王五;赵六
// testLieToLine
name col1
小虎 150
小虎 170
小虎 180
火火 150
火火 180
火火 190
create table testLieToLine(
name string,
col1 int
)row format delimited
fields terminated by '\t';
select name,collect_list(col1) from testLieToLine group by name;
// 结果
小虎 ["150","180","190"]
火火 ["150","170","180"]
select t1.name
,collect_list(t1.col1)
from (
select name
,col1
from testarray2
lateral view explode(weight) t1 as col1
) t1 group by t1.name;
