Hive系列 (三)：开窗函数详解

Hive系列文章

Hadoop完全分布式搭建（腾讯云服务器+阿里云服务器）

Hive系列 (一)：Hive搭建

Hive系列 (二)：Hive基础知识

Hive系列 (三)：开窗函数详解

Hive系列 (四)：自定义函数UDF UDTF UDAF

Hive系列 (五)：Hive数据类型

Hive系列 (六)：Hive数据类型转换

Hive系列 (七)：Hive常用函数

Hive系列 (八)：Hive中的explode 与 lateral view

Hive系列 (九)：Hive数据存储

Hive系列 (十)：Hive调优

开窗函数简介

普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。

因此,普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。

简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。
开窗函数一般分为两类,聚合开窗函数和排序开窗函数。

准备数据

建立学生成绩表

0: jdbc:hive2://master:10000> use myhive;
OK
No rows affected (1.61 seconds)
0: jdbc:hive2://master:10000> create table stu_scores(
. . . . . . . . . . . . . . > id int,
. . . . . . . . . . . . . . > stu_id int,
. . . . . . . . . . . . . . > chinese int,
. . . . . . . . . . . . . . > math int,
. . . . . . . . . . . . . . > english int,
. . . . . . . . . . . . . . > class_id string,
. . . . . . . . . . . . . . > dept_Id string
. . . . . . . . . . . . . . > )row format delimited fields terminated by '/t';
OK
No rows affected (4.162 seconds)
0: jdbc:hive2://master:10000> show tables;
OK
+---------------------------+
|         tab_name          |
+---------------------------+
| bucket                    |
| bucket_com                |
| employee                  |
| mix_partition             |
| salary_dynamic_partition  |
| static_mix                |
| stu                       |
| stu_scores                |
+---------------------------+
8 rows selected (1.314 seconds)
0: jdbc:hive2://master:10000> 

插入数据，数据样例如下stu_scores_input.txt

1       10001   69      70      71      C1      D1
2       10002   66      71      56      C1      D1
3       10003   60      72      57      C1      D1
4       10004   56      78      58      C1      D1
5       10005   98      79      59      C1      D1
6       10006   56      80      60      C2      D1
7       10007   100     81      61      C2      D1
8       10008   78      82      76      C2      D1
9       10009   90      80      77      C2      D1
10      10010   87      81      78      C2      D1
11      10011   66      82      79      C1      D1
12      10012   98      83      80      C1      D2
13      10013   97      84      81      C1      D2
14      10014   87      85      85      C1      D2
15      10015   98      94      86      C1      D2
16      10016   100     95      87      C2      D2
17      10017   68      96      88      C2      D2
18      10018   50      97      89      C1      D2
19      10019   42      98      90      C1      D2
20      10020   59      99      91      C1      D2

上传到hdfs

[hadoop@master sql_script]$ hdfs dfs -put stu_scores_input.txt

导入数据

load data inpath '/user/hadoop/stu_scores_input.txt' into table stu_scores;

聚合开窗函数

count开窗函数

执行下面脚本

select 
stu_id
,math
,dept_id
-- 以符合条件的所有行作为窗口
,count(math) over() as ct1
-- 以按class_id分组、按math排序的所有行作为窗口
,count(math) over(partition by class_id) as ct2
-- 以按class_id分组、按math排序的所有行作为窗口
,count(math) over(partition by class_id order by math) as ct3
-- 以按class_id分组、按math排序、按 当前行+往前1行+往后2行的行作为窗口
,count(math) over(partition by class_id order by math rows between 1 preceding and 2 following) as ct4
from myhive.stu_scores
where dept_id = 'D1';

结果：

+---------+-------+----------+------+------+------+------+
| stu_id  | math  | dept_id  | ct1  | ct2  | ct3  | ct4  |
+---------+-------+----------+------+------+------+------+
| 10001   | 70    | D1       | 11   | 6    | 1    | 3    |
| 10002   | 71    | D1       | 11   | 6    | 2    | 4    |
| 10003   | 72    | D1       | 11   | 6    | 3    | 4    |
| 10004   | 78    | D1       | 11   | 6    | 4    | 4    |
| 10005   | 79    | D1       | 11   | 6    | 5    | 3    |
| 10011   | 82    | D1       | 11   | 6    | 6    | 2    |
| 10009   | 80    | D1       | 11   | 5    | 2    | 3    |
| 10006   | 80    | D1       | 11   | 5    | 2    | 4    |
| 10010   | 81    | D1       | 11   | 5    | 4    | 4    |
| 10007   | 81    | D1       | 11   | 5    | 4    | 3    |
| 10008   | 82    | D1       | 11   | 5    | 5    | 2    |
+---------+-------+----------+------+------+------+------+

解释：

在dept_id = 'D1'的条件下，对于stu_id = '10010'
ct1:统计所有行数位11
ct2:统计class_id = 'C2' 进行分组的个数5，
ct3:统计class_id = 'C2'，math <= 81的个数4,
ct4:10010所在行，向前（上）+1行，向后（下）+2行，统计math个数4

sum开窗函数

示例代码

select 
stu_id
,math
,class_id
-- 以符合条件的所有行作为窗口
,sum(math) over() as ct1
-- 以按class_id分组、按math排序的所有行作为窗口
,sum(math) over(partition by class_id) as ct2
-- 以按class_id分组、按math排序的所有行作为窗口
,sum(math) over(partition by class_id order by math) as ct3
-- 以按class_id分组、按math排序、按 当前行+往前1行+往后2行的行作为窗口
,sum(math) over(partition by class_id order by math rows between 1 preceding and 2 following) as ct4
from myhive.stu_scores
where dept_id = 'D1';

结果：

+---------+-------+-----------+------+------+------+------+
| stu_id  | math  | class_id  | ct1  | ct2  | ct3  | ct4  |
+---------+-------+-----------+------+------+------+------+
| 10001   | 70    | C1        | 856  | 452  | 70   | 213  |
| 10002   | 71    | C1        | 856  | 452  | 141  | 291  |
| 10003   | 72    | C1        | 856  | 452  | 213  | 300  |
| 10004   | 78    | C1        | 856  | 452  | 291  | 311  |
| 10005   | 79    | C1        | 856  | 452  | 370  | 239  |
| 10011   | 82    | C1        | 856  | 452  | 452  | 161  |
| 10009   | 80    | C2        | 856  | 404  | 160  | 241  |
| 10006   | 80    | C2        | 856  | 404  | 160  | 322  |
| 10010   | 81    | C2        | 856  | 404  | 322  | 324  |
| 10007   | 81    | C2        | 856  | 404  | 322  | 244  |
| 10008   | 82    | C2        | 856  | 404  | 404  | 163  |
+---------+-------+-----------+------+------+------+------+

min开窗函数

示例代码：

0: jdbc:hive2://master:10000> SELECT 
. . . . . . . . . . . . . . > stu_id
. . . . . . . . . . . . . . > ,math
. . . . . . . . . . . . . . > ,class_id
. . . . . . . . . . . . . . > -- 以符合条件的所有