文章目录

高级查询部分1. CASE WHEN / IF2.行转列3.列转行4.窗口函数5.Rank（排名函数）

高级查询部分

1. CASE WHEN / IF

数据准备 namedept_idsex悟空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．数据准备

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

moviecategory《疑犯追踪》悬疑,动作,科幻,剧情《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．数据准备

namesubjectscore孙悟空语文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;