Hive - 开窗函数

『概述』

开窗函数是Hive SQL中一种非常强大的功能，它允许你在不折叠分组的情况下，对一组相关的行（称为 “窗口” ）进行计算。这与普通的聚合函数（如SUM，AVG）不同：

• 普通聚合函数：将多行数据聚合成一行结果，原始行的细节会丢失。
• 开窗函数：为每一行都返回一个结果，同时保留原始行的所有细节。你可以在同一查询中既看到明细数据，又看到基于窗口的聚合值。

核心思想：OVER() 子句是开窗函数的标志，它定义了计算所使用的数据窗口。

『示例』

Part00：准备工作

先创建hive表，并写入测试数据

-- 建表 
create table student_scores(
 id int comment '序号',
 studentId int comment '学号',
 language int comment '语文成绩',
 math int comment '数学成绩',
 english int comment '英语成绩',
 classId string comment '班级', 
 departmentId string comment '系'
);

-- 写表
insert into table student_scores values (1,111,68,69,90,'class1','department1');
insert into table student_scores values (2,112,73,80,96,'class1','department1');
insert into table student_scores values (3,113,90,74,75,'class1','department1');
insert into table student_scores values (4,114,89,94,93,'class1','department1');
insert into table student_scores values (5,115,99,93,89,'class1','department1');
insert into table student_scores values (6,121,96,74,79,'class2','department1');
insert into table student_scores values (7,122,89,86,85,'class2','department1');
insert into table student_scores values (8,123,70,78,61,'class2','department1');
insert into table student_scores values (9,124,76,70,76,'class2','department1');
insert into table student_scores values (10,211,89,93,60,'class1','department2');
insert into table student_scores values (11,212,76,83,75,'class1','department2');
insert into table student_scores values (12,213,71,94,90,'class1','department2');
insert into table student_scores values (13,214,94,94,66,'class1','department2');
insert into table student_scores values (14,215,84,82,73,'class1','department2');
insert into table student_scores values (15,216,85,74,93,'class1','department2');
insert into table student_scores values (16,221,77,99,61,'class2','department2');
insert into table student_scores values (17,222,80,78,96,'class2','department2');
insert into table student_scores values (18,223,79,74,96,'class2','department2');
insert into table student_scores values (19,224,75,80,78,'class2','department2');
insert into table student_scores values (20,225,82,85,63,'class2','department2');

Part01：count

select studentId,math,departmentId,classId,
count(math) over() as count1,
count(math) over(partition by classId) as count2,
count(math) over(partition by classId order by math) as count3,
count(math) over(partition by classId order by math rows between 1 preceding and 2 following) as count4
from student_scores where departitionId = 'department1';

• count1：我们系一共有多少学生参加了数学考试

• count2：我们系每班有多少学生参加了数学考试

• count3：我们系每班学生数学考试成绩的排名（升序，分数从低到高）

• count4：前面一名+自己+后面两名总共多少人。

• 前面没人，那就是自己和后面两人，共计三人。

• 如果后面没人，那就是自己和前面一名，共计两人。

Part02：sum

select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
sum(math) over() as sum1,
-- 以按classId分组的所有行作为窗口
sum(math) over(partition by classId) as sum2,
-- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
sum(math) over(partition by classId order by math) as sum3,
-- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
sum(math) over(partition by classId order by math rows between 1 preceding and 2 following) as sum4
from student_scores where departmentId='department1';

• sum1：计算整个查询结果中所有学生的数学成绩总和。

• sum2：计算每个班级内所有学生的数学成绩总和。

• sum3：在每个班级内，按数学成绩从低到高排序，计算到当前行为止的累计总分。

• sum4：在每个班级内，计算当前学生及其前后相邻学生的成绩总和。

• 具体范围：当前学生的前1个同学 + 当前学生自己 + 当前学生的后2个同学

Part03：min

select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
min(math) over() as min1,
-- 以按classId分组的所有行作为窗口
min(math) over(partition by classId) as min2,
-- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
min(math) over(partition by classId order by math) as min3,
-- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
min(math) over(partition by classId order by math rows between 1 preceding and 2 following) as min4
from student_scores where departmentId='department1';

• min1：整个查询结果中所有学生的最低数学成绩。

• min2：每个班级内部所有学生的最低数学成绩。

• min3：在每个班级内，按数学成绩从低到高排序，计算到当前行为止的最低分。

• min4：在每个班级内，找出当前学生及其前后相邻学生中的最低分。

• 具体范围：当前学生的前1个同学 + 当前学生自己 + 当前学生的后2个同学

Part04：max

select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
max(math) over() as max1,
-- 以按classId分组的所有行作为窗口
max(math) over(partition by classId) as max2,
-- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
max(math) over(partition by classId order by math) as max3,
-- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
max(math) over(partition by classId order by math rows between 1 preceding and 2 following) as max4
from student_scores where departmentId='department1';

• max1：整个查询结果中所有学生的最高数学成绩。

• max2：每个班级内部所有学生的最高数学成绩。

• max3：在每个班级内，按数学成绩从低到高排序，计算到当前行为止的最高分。

• max4：在每个班级内，找出当前学生及其前后相邻学生中的最高分。

• 具体范围：当前学生的前1个同学 + 当前学生自己 + 当前学生的后2个同学

Part05：avg

select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
avg(math) over() as avg1,
-- 以按classId分组的所有行作为窗口
avg(math) over(partition by classId) as avg2,
-- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
avg(math) over(partition by classId order by math) as avg3,
-- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
avg(math) over(partition by classId order by math rows between 1 preceding and 2 following) as avg4
from student_scores where departmentId='department1';

• max1：整个查询结果中所有学生的数学平均成绩。

• max2：每个班级内部所有学生的数学平均成绩。

• max3：在每个班级内，按数学成绩从低到高排序，计算到当前行为止的数学平均成绩。

• max4：在每个班级内，找出当前学生及其前后相邻学生中的数学平均成绩。

• 具体范围：当前学生的前1个同学 + 当前学生自己 + 当前学生的后2个同学

Part06：first_value

select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
first_value(math) over() as first_value1,
-- 以按classId分组的所有行作为窗口
first_value(math) over(partition by classId) as first_value2,
-- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
first_value(math) over(partition by classId order by math) as first_value3,
-- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
first_value(math) over(partition by classId order by math rows between 1 preceding and 2 following) as first_value4
from student_scores where departmentId='department1';

• first_value1：看看全年级第一个学生的数学成绩是多少。

• first_value2：看看每个班的第一个学生数学成绩是多少。

• first_value3：看看每个班数学最差的学生考了多少分。

• first_value4：看看你前后附近这几个同学中，数学最差的那个考了多少分。

• 具体范围：当前学生的前1个同学 + 当前学生自己 + 当前学生的后2个同学

Part07：last_value

select studentId,math,departmentId,classId,
-- 以符合条件的所有行作为窗口
last_value(math) over() as last_value1,
-- 以按classId分组的所有行作为窗口
last_value(math) over(partition by classId) as last_value2,
-- 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口
last_value(math) over(partition by classId order by math) as last_value3,
-- 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口
last_value(math) over(partition by classId order by math rows between 1 preceding and 2 following) as last_value4
from student_scores where departmentId='department1';

• last_value1：看看全年级最后一个学生的数学成绩是多少。

• last_value2：看看每个班的最后一个学生数学成绩是多少。

• last_value3：随着成绩从低到高排列，显示到当前位置为止的最高分数。

• last_value4：看看你前后附近这几个同学中，数学最好的那个考了多少分。

• 具体范围：当前学生的前1个同学 + 当前学生自己 + 当前学生的后2个同学

Part08：lag

select studentId,math,departmentId,classId,
--窗口内 往上取第二个 取不到时赋默认值60
lag(math,2,60) over(partition by classId order by math) as lag1,
--窗口内 往上取第二个 取不到时赋默认值NULL
lag(math,2) over(partition by classId order by math) as lag2
from student_scores where departmentId='department1';

• lag1：看看你前面隔一个人的同学数学考了多少分，如果前面人不够，就当他是60分。

• lag2：看看你前面隔一个人的同学数学考了多少分，如果前面人不够，就显示空值。

Part09：lead

select studentId,math,departmentId,classId,
--窗口内 往下取第二个 取不到时赋默认值60
lead(math,2,60) over(partition by classId order by math) as lead1,
--窗口内 往下取第二个 取不到时赋默认值NULL
lead(math,2) over(partition by classId order by math) as lead2
from student_scores where departmentId='department1';

• lead1：看看你后面隔一个人的同学数学考了多少分，如果后面人不够，就当他是60分。

• lead2：看看你后面隔一个人的同学数学考了多少分，如果后面人不够，就显示空值。

Part10：cume_dist

select studentId,math,departmentId,classId,
-- 统计小于等于当前分数的人数占总人数的比例
cume_dist() over(order by math) as cume_dist1,
-- 统计大于等于当前分数的人数占总人数的比例
cume_dist() over(order by math desc) as cume_dist2,
-- 统计分区内小于等于当前分数的人数占总人数的比例
cume_dist() over(partition by classId order by math) as cume_dist3
from student_scores where departmentId='department1';

• cume_dist1：你的分数超过了百分之多少的人（从低分往高分算）。

• cume_dist2：你的分数被百分之多少的人超过（从高分往低分算）。

• cume_dist3：你在班级内超过了百分之多少的同学

Part11：rank

select *,
-- 对全部学生按数学分数排序 
rank() over(order by math) as rank1,
-- 对院系 按数学分数排序
rank() over(partition by departmentId order by math) as rank2,
-- 对每个院系每个班级 按数学分数排序
rank() over(partition by departmentId,classId order by math) as rank3
from student_scores;

• rank1：你在全校的数学成绩排名是多少（并列名次会占用位置）。

• rank2：你在自己院系内的数学成绩排名是多少。

• rank3：你在自己班级内的数学成绩排名是多少

Part12：dense_rank

select *,
-- 对全部学生按数学分数排序
dense_rank() over(order by math) as dense_rank1,
-- 对院系 按数学分数排序
dense_rank() over(partition by departmentId order by math) as dense_rank2,
-- 对每个院系每个班级 按数学分数排序
dense_rank() over(partition by departmentId,classId order by math) as dense_rank3
from student_scores;

• dense_rank1：你在全校的数学成绩排名是多少（并列名次会占用位置）。

• dense_rank2：你在自己院系内的数学成绩排名是多少。

• dense_rank3：你在自己班级内的数学成绩排名是多少

Part13：ntile

 select *,
 -- 对分区内的数据分成两组
 ntile(2) over(partition by departmentid order by math) as ntile1,
 -- 对分区内的数据分成三组
 ntile(3) over(partition by departmentid order by math) as ntile2
 from student_scores;

• ntile1：你在院系里按数学成绩被分到了前50%还是后50%。

• ntile2：你在院系里按数学成绩被分到了前1/3、中间1/3还是后1/3。

Part14：row_number

select studentid,departmentid,classid,math,
-- 对分区departmentid,classid内的数据按math排序
row_number() over(partition by departmentid,classid order by math) as row_number
from student_scores;

• row_number：各班学生按照数学成绩从低到高的排名。

Part15：percent_rank

select studentid,departmentid,classid,math,
row_number() over(partition by departmentid,classid order by math) as row_number,
percent_rank() over(partition by departmentid,classid order by math) as percent_rank
from student_scores;

• ntile1：你你在班里的具体名次（第几名）

• percent_rank：你在班里的相对水平（超过百分之多少的人）