1. order/sort/distuibute/cluster by 的区别
1.1 order by
如果使用 order by 会在 mr 的 reducer 时进行全局排序（只启动一个reducer）。（什么是全局排序：对应的是下面的非全局排序）这里是和 mysql 查询结果一样的。
select deptno, ename from emp order by deptno desc;
deptno ename
30 WARD
30 ALLEN
30 JAMES
30 TURNER
30 BLAKE
30 MARTIN
20 ADAMS
20 FORD
20 SCOTT
20 SMITH
20 JONES
10 MILLER
10 KING
10 CLARK
1.2 distruibute by
对比 order by，distruibute by 是对数据进行分区，然后各分区按照查询语句来查（对应 hadoop 的 mapper 分区执行），对应生成多个reducer。默认按照hash分布对分区数取余，分布到各分区中。
查看分区数量：set mapreduce.job.reduces;
设置分区数量：set mapreduce.job.reduces=4;
select deptno, ename from emp distribute by deptno;
-- 打印内容到文件（调试使用:查看各分区的结果）
insert overwrite local directory '/usr/local/hive-3.1.2/file-distuibute-by'
select deptno, ename from emp distribute by deptno;
deptno ename
20 JONES
20 SCOTT
20 SMITH
20 FORD
20 ADAMS
10 MILLER
30 JAMES
30 TURNER
10 KING
30 BLAKE
30 MARTIN
30 WARD
30 ALLEN
10 CLARK
ll /usr/local/hive-3.1.2/file-distuibute-by/
-rw-r--r-- 1 root root 44 Apr 19 11:51 000000_0
-rw-r--r-- 1 root root 0 Apr 19 11:51 000001_0
-rw-r--r-- 1 root root 82 Apr 19 11:51 000002_0
-rw-r--r-- 1 root root 0 Apr 19 11:51 000003_0
# 数据分区4个，其中两个分区是空的，两个分区有数据，因为一下数据中的 deptno%4 的结果分别是0，2
cat /usr/local/hive-3.1.2/file-distuibute-by/000000_0
20JONES
20SCOTT
20SMITH
20FORD
20ADAMS
cat /usr/local/hive-3.1.2/file-distuibute-by/000000_1
cat /usr/local/hive-3.1.2/file-distuibute-by/000000_2
10MILLER
30JAMES
30TURNER
10KING
30BLAKE
30MARTIN
30WARD
30ALLEN
10CLARK
cat /usr/local/hive-3.1.2/file-distuibute-by/000000_3
1.3 sort by
- sort by 通常结合 distruibute by 使用看到，因为它可以对分区内的字段进行排序
- sort by 单独使用时，会进行随机分区
select deptno, ename from emp distribute by deptno sort by deptno desc;deptno ename 20 ADAMS 20 FORD 20 SCOTT 20 SMITH 20 JONES 30 WARD 30 ALLEN 30 JAMES 30 TURNER 30 BLAKE 30 MARTIN 10 MILLER 10 KING 10 CLARK1.4 cluster by
当 distruibute by 与 sort by 一起使用，而且 by 的字段相同时，可以用 cluster by 代替。（但是 cluster by 不能进行 desc，只能 asc）select deptno, ename from emp cluster by deptno;deptno ename 20 FORD 20 SCOTT 20 JONES 20 ADAMS 20 SMITH 10 MILLER 10 KING 10 CLARK 30 BLAKE 30 MARTIN 30 JAMES 30 WARD 30 ALLEN 30 TURNER2. functions
- 查看自带函数
hive> show functions;
- 查看自带函数用法
hive> desc functions like upper;
- 查看自带函数详细用法
hive> desc function extended upper;
- UDF: 一进一出（普通函数）
- UDAF: 多进一出（聚合函数）
- UDTF: 一进多出（炸裂函数）
3. lateral view 侧写
```sql movie category 《疑犯追踪》 悬疑,动作,科幻,剧情 《Lie to me》 悬疑,警匪,动作,心理,剧情 《战狼 2》 战争,动作,灾难
select movie, category_name from movie_info lateral view explode(split(category, “,”)) movie_info_tmp as category_name;
movie category_name 《疑犯追踪》 悬疑 《疑犯追踪》 动作 《疑犯追踪》 科幻 《疑犯追踪》 剧情 《Lie to me》 悬疑 《Lie to me》 警匪 《Lie to me》 动作 《Lie to me》 心理 《Lie to me》 剧情 《战狼 2》 战争 《战狼 2》 动作 《战狼 2》 灾难
<a name="O3jgs"></a>
# 4. over() 窗口函数
```sql
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
Mark 2017-04-08 62
Mart 2017-04-09 68
Meil 2017-05-10 12
Mart 2017-04-11 75
Meil 2017-06-12 80
Mart 2017-04-13 94
select
name, count(*) over()
from
business
where substring(orderdate, 0, 7) = '2017-04'
group by name;
name count_window_0
Mart 3
Mark 3
Jack 3
select
name, orderdate, cost, sum(cost) over(partition by name, month(orderdate))
from business;
name orderdate cost sum_window_0
Jack 2017-01-08 55 111
Jack 2017-01-01 10 111
Jack 2017-01-05 46 111
Jack 2017-02-03 23 23
Jack 2017-04-06 42 42
Mark 2017-04-08 62 62
Mart 2017-04-13 94 237
Mart 2017-04-11 75 237
Mart 2017-04-09 68 237
Meil 2017-05-10 12 12
Meil 2017-06-12 80 80
Tony 2017-01-04 29 94
Tony 2017-01-02 15 94
Tony 2017-01-07 50 94
select
name, orderdate, cost, sum(cost) over(partition by name order by orderdate)
from business;
name orderdate cost sum_window_0
Jack 2017-01-01 10 10
Jack 2017-01-05 46 56
Jack 2017-01-08 55 111
Jack 2017-02-03 23 134
Jack 2017-04-06 42 176
Mark 2017-04-08 62 62
Mart 2017-04-09 68 68
Mart 2017-04-11 75 143
Mart 2017-04-13 94 237
Meil 2017-05-10 12 12
Meil 2017-06-12 80 92
Tony 2017-01-02 15 15
Tony 2017-01-04 29 44
Tony 2017-01-07 50 94
select
name, orderdate, cost, sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following)
from business;
name orderdate cost sum_window_0
Jack 2017-01-01 10 56
Jack 2017-01-05 46 111
Jack 2017-01-08 55 124
Jack 2017-02-03 23 120
Jack 2017-04-06 42 65
Mark 2017-04-08 62 62
Mart 2017-04-09 68 143
Mart 2017-04-11 75 237
Mart 2017-04-13 94 169
Meil 2017-05-10 12 92
Meil 2017-06-12 80 92
Tony 2017-01-02 15 44
Tony 2017-01-04 29 94
Tony 2017-01-07 50 79
-- lag,lead
select
name, orderdate, lag(orderdate, 1) over(partition by name order by orderdate)
from business;
name orderdate lag_window_0
Jack 2017-01-01 NULL
Jack 2017-01-05 2017-01-01
Jack 2017-01-08 2017-01-05
Jack 2017-02-03 2017-01-08
Jack 2017-04-06 2017-02-03
Mark 2017-04-08 NULL
Mart 2017-04-09 NULL
Mart 2017-04-11 2017-04-09
Mart 2017-04-13 2017-04-11
Meil 2017-05-10 NULL
Meil 2017-06-12 2017-05-10
Tony 2017-01-02 NULL
Tony 2017-01-04 2017-01-02
Tony 2017-01-07 2017-01-04
select
name, orderdate, cost, ntile(5) over(order by orderdate) groupId
from business t1;
name orderdate cost groupid
Jack 2017-01-01 10 1
Tony 2017-01-02 15 1
Tony 2017-01-04 29 1
Jack 2017-01-05 46 2
Tony 2017-01-07 50 2
Jack 2017-01-08 55 2
Jack 2017-02-03 23 3
Jack 2017-04-06 42 3
Mark 2017-04-08 62 3
Mart 2017-04-09 68 4
Mart 2017-04-11 75 4
Mart 2017-04-13 94 4
Meil 2017-05-10 12 5
Meil 2017-06-12 80 5
5. rank()、dense_rank()和row_number()
create table score(name string,subject string,score int)
row format delimited
fields terminated by ';';
name subject score
悟空 语文 87
悟空 数学 95
悟空 英语 68
八戒 语文 94
八戒 数学 56
八戒 英语 84
张飞 语文 64
张飞 数学 86
张飞 英语 84
小乔 语文 65
小乔 数学 85
小乔 英语 78
select *,rank() over(order by score) from score;
name subject score rank_window_0
八戒 数学 56 1
张飞 语文 64 2
小乔 语文 65 3
悟空 英语 68 4
小乔 英语 78 5
张飞 英语 84 6
八戒 英语 84 6
小乔 数学 85 8
张飞 数学 86 9
悟空 语文 87 10
八戒 语文 94 11
悟空 数学 95 12
select *,dense_rank() over(order by score) from score;
name subject score dense_rank_window_0
八戒 数学 56 1
张飞 语文 64 2
小乔 语文 65 3
悟空 英语 68 4
小乔 英语 78 5
张飞 英语 84 6
八戒 英语 84 6
小乔 数学 85 7
张飞 数学 86 8
悟空 语文 87 9
八戒 语文 94 10
悟空 数学 95 11
select *,row_number() over(order by score) from score;
name subject score row_number_window_0
八戒 数学 56 1
张飞 语文 64 2
小乔 语文 65 3
悟空 英语 68 4
小乔 英语 78 5
张飞 英语 84 6
八戒 英语 84 7
小乔 数学 85 8
张飞 数学 86 9
悟空 语文 87 10
八戒 语文 94 11
悟空 数学 95 12
select
name, subject, score
from (
select
*, rank() over(partition by subject order by score desc) rk
from
score) t1
where rk <= 3;
name subject score
悟空 数学 95
张飞 数学 86
小乔 数学 85
八戒 英语 84
张飞 英语 84
小乔 英语 78
八戒 语文 94
悟空 语文 87
小乔 语文 65
6. grouping sets
使用起来的效果，有点类似 mysql 中的 group by xx with roll up.
create table staff(
id string,
name string,
gender string,
deptid string
) row format delimited fields terminated by ',';
1001,zhangsan,male,10
1002,lisi,female,10
1003,bangzhang,female,20
1004,haiwang,male,20
1005,banhua,male,30
1006,sheshe,female,30
select
deptid,gender,count(*)
from staff
group by deptid,gender
grouping sets((deptid,gender),deptid,gender,());
deptid gender _c2
NULL NULL 6
10 NULL 2
20 NULL 2
30 NULL 2
NULL female 3
10 female 1
20 female 1
30 female 1
NULL male 3
10 male 1
20 male 1
30 male 1
若有收获，就点个赞吧
0 人点赞
