Hive学习笔记（七）—— 查询 （高级）

高级查询部分

1. CASE WHEN / IF

1. 数据准备

name	dept_id	sex
悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女

2．需求
求出不同部门男女各多少人。结果如下：

A 2 1
B 1 2

3．创建本地 emp_sex.txt，添加数据

悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女

4．创建 hive 表并导入数据

create table emp_sex(
name string, 
dept_id string, 
sex string) 
row format delimited fields terminated by "\t";

load data local inpath '/opt/module/data/emp_sex.txt' into table 
emp_sex;

5．按需求查询数据

（case when查询）

select 
 dept_id,
 sum(case sex when '男' then 1 else 0 end) male_count,
 sum(case sex when '女' then 1 else 0 end) female_count
from 
 emp_sex
group by
 dept_id;

（if查询）

select dept_id,
	sum(if(sex='男',1,0)) male_count,
	sum(if(sex='女',1,0)) female_count
from 
	emp_sex 
group by 
	dept_id;

结果如下

Total MapReduce CPU Time Spent: 21 seconds 690 msec
OK
dept_id	male_count	female_count
A	2	1
B	1	2
Time taken: 77.979 seconds, Fetched: 2 row(s)

2.行转列

1．相关函数说明
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 array 类型字段。

concat用例：

hive (default)> select concat('dahuan','-','haoge');
OK
_c0
dahuan-haoge
Time taken: 6.558 seconds, Fetched: 1 row(s)

concat_ws用例

hive (default)> select concat_ws('-','hello','dahuan');
OK
_c0
hello-dahuan

collect_set用例：

hive (default)> select collect_set(dept_id) from emp_sex;
Query ID = root_20201023145457_5b8b6c78-5c73-4680-b8e9-989a2332d4ee
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1603435346879_0001, Tracking URL = http://Bigdata02:8088/proxy/application_1603435346879_0001/
Kill Command = /opt/module/hadoop-2.7.2/bin/hadoop job  -kill job_1603435346879_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2020-10-23 14:55:42,444 Stage-1 map = 0%,  reduce = 0%
2020-10-23 14:56:14,588 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 17.04 sec
2020-10-23 14:56:30,140 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 26.49 sec
MapReduce Total cumulative CPU time: 26 seconds 490 msec
Ended Job = job_1603435346879_0001
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 26.49 sec   HDFS Read: 7049 HDFS Write: 4 SUCCESS
Total MapReduce CPU Time Spent: 26 seconds 490 msec
OK
_c0
["A","B"]

2．数据准备

name	constellation	blood_type
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A

3．需求
把星座和血型一样的人归类到一起。结果如下：

射手座,A  大海|凤姐
白羊座,A  孙悟空|猪八戒
白羊座,B  宋宋

4．创建本地 person_info.txt，导入数据

[root@hadoop102 datas]$ vi person_info.txt
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A

5．创建 hive 表并导入数据

create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive/data/person_info.txt" into 
table person_info;

6．按需求查询数据

select
 t1.base,
 concat_ws('|', collect_set(t1.name)) name
from
 (select
 name,
 concat(constellation, ",", blood_type) base
 from
 person_info) t1
group by
 t1.base;

3.列转行

1．函数说明
EXPLODE(col)：将 hive 一列中复杂的 array 或者 map 结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

2．数据准备

movie	category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼 2》	战争,动作,灾难

3．需求
将电影分类中的数组数据展开。结果如下：

《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼 2》 战争
《战狼 2》 动作
《战狼 2》 灾难

4．创建本地 movie.txt，导入数据

[root@hadoop102 datas]$ vi movie.txt
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼 2》 战争,动作,灾难

5．创建 hive 表并导入数据

create table movie_info(
 movie string,
 category array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
load data local inpath "/opt/module/hive/data/movie.txt" into table 
movie_info;

测试EXPLODE：

hive (default)> select explode(category) from movie_info;
OK
col
悬疑
动作
科幻
剧情
悬疑
警匪
动作
心理
剧情
战争
动作
灾难
Time taken: 0.176 seconds, Fetched: 12 row(s)

6．按需求查询数据

select
 movie,
 category_name
from
 movie_info #表名
 lateral view explode(category) table_tmp #随意名
 as 
category_name;

4.窗口函数

1．相关函数说明
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化；
CURRENT ROW：当前行； n PRECEDING：往前 n 行数据； n FOLLOWING：往后 n 行数据；
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点，UNBOUNDED FOLLOWING 表示到后面的终点；
LAG(col,n)：往前第 n 行数据；
LEAD(col,n)：往后第 n 行数据；
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，
对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

2．数据准备：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

3．需求
（1）查询在 2017 年 4 月份购买过的顾客及总人数
（2）查询顾客的购买明细及购买总额
（3）上述的场景,要将 cost 按照日期进行累加
（4）查询顾客上次的购买时间
（5）查询前 20%时间的订单信息

4．创建本地 business.txt，导入数据

[root@hadoop102 datas]$ vi business.txt

5．创建 hive 表并导入数据

create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/hive/data/business.txt" into table 
business;

6．按需求查询数据
（1）查询在 2017 年 4 月份购买过的顾客及总人数

select name,count(*) over ()
from business
where substring(orderdate,1,7) = '2017-04'
group by name;

（2）查询顾客的购买明细及购买总额

select *,sum(cost) over() from business;

（3）上述的场景,要将 cost 按照日期进行累加

select name,orderdate,cost,sum(cost) over(distribute by name sort by orderdate)
from business;

（4）查询顾客上次的购买时间

注意：Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

select name,orderdate,cost,
lag(orderdate,1,'1970-01-01') over(distribute by name sort by orderdate)
from
business;

（5）查询前 20%时间的订单信息

select
t1.name,t1.orderdate,t1.cost
from
(select name,orderdate,cost,
ntile(5) over(order by orderdate) ntile_5
from
business) t1
where ntile_5=1;

5.Rank（排名函数）

1．函数说明

RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

2．数据准备

name	subject	score
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78

3．需求
计算每门学科成绩排名。
4．创建本地 movie.txt，导入数据

[root@hadoop102 data]$ vi score.txt
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78

5．创建 hive 表并导入数据

create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";

load data local inpath '/opt/module/hive/data/score.txt' into table 
score;

6．按需求查询数据

select
t1.name,
t1.subject,
t1.score,
rank() over(partition by t1.subject order by t1.score desc) rank1,
dense_rank() over(partition by t1.subject order by t1.score desc) rank2,
row_number() over(partition by t1.subject order by t1.score desc) rank3
from
score t1;