【Hive】hive窗口函数/分析函数 over子句

文章目录

• over子句介绍
• • 1. 什么是over子句
  • 2. over子句的开窗范围
  • 3. window clause
  • 4. over子句默认值
  • • 4.1 order by
    • 4.2 partition by
    • 4.3 partition by + order by
• 几个常用开窗函数
• • 案例引出
  • row_number
  • rank
  • dense_rank
  • 案例答案

over子句介绍

over子句参考链接 https://blog.csdn.net/czr11616/article/details/101645693

1. 什么是over子句

我们可以形象的把over()子句理解成开窗子句，即打开一个窗口，窗口内包含多条记录，over()会给每一行开一个窗口。如下图，总共有5条记录，每一行代表一条记录，over()在每一条记录的基础上打开一个窗口，给r1记录打开w1窗口，窗口内只包含自己，给r2打开w2窗口，窗口内包含r1、r2，给r3打开w3窗口，窗口内包含r1、r2、r3，以此类推…

由上我们不难发现，在使用over()子句进行查询的时候， 不仅可以查询到每条记录的信息，还可以查询到这条记录对应窗口内的所有记录的聚合信息，所以我们通常结合聚合函数和over()子句一起使用。

那么over()是如何进行开窗的呢？即每条记录对应的窗口内应该包含哪些记录呢？这些都是在over()子句的括号内进行定义。

2. over子句的开窗范围

• current row代表查询的当前行
• 1 preceding代表前一行
• 1 following代表后一行
• unbounded preceding代表第一行
• unbounded following代表最后一行。

（注意这里的第一行和最后一行并不是严格的第一行和最后一行，根据具体情况而定）

3. window clause

over()子句的开窗范围可以通过window 子句（window clause）在over()的括号中定义，window clause的规范如下：

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
           

例如 ：

select *,sum(column_name) over( rows between unbounded preceding and unbounded following) from table_name 
           

表示查询每一行的所有列值，同时给每一行打开一个从第一行到最后一行的窗口，并统计窗口内所有记录的column_name列值的和。最后给每一行输出该行的所有属性以及该行对应窗口内记录的聚合值。

4. over子句默认值

如果over()子句中什么都不写的话，默认开窗范围是：rows between unbounded preceding and unbounded following

4.1 order by

如果over()子句中接order by，例如：over(order by date)，
则默认的开窗范围为根据date排序后的rows between unbounded preceding and current row，
即第一行到当前行，
意思是over(order by date)和over(order by date rows between unbounded preceding and current row)的效果是一样的。

           

4.2 partition by

如果over子句中接partition by（和group by类似，都是根据列值对行进行分组），
例如over(partition by month(date))，
则每一行的默认的开窗范围为当前行所在分组的所有记录。
注意partition by子句不能单独和window clause子句一起使用，必须结合order by子句，下面会讨论。

           

4.3 partition by + order by

先分组，再排序，即组内排序。
同样的，如果 order by后不接window clause，则每一行的默认的开窗范围为：当前行所在分组的第一行到当前行，
即over(partition by (month(date)) order by orderdate)和over(partition by (month(date)) order by orderdate rows between undounded preceding and current row)是一样的。

           

几个常用开窗函数

案例引出

有学生信息表：

hive> select * from stu_managed;
OK
1		张三dg	un	23	IS
95002	刘晨		女	19	IS
95017	王风娟	女	18	IS
95018	王一		女	19	IS
95013	冯伟		男	21	CS
95014	王小丽	女	19	CS
95019	邢小丽	女	19	IS
95020	赵钱		男	21	IS
95003	王敏		女	22	MA
95004	张立		男	19	IS
95012	孙花		女	20	CS
95010	孔小涛	男	19	CS
95005	刘刚		男	18	MA
95006	孙庆		男	23	CS
95007	易思玲	女	19	MA
95008	李娜		女	18	CS
95021	周二		男	17	MA
95022	郑明		男	20	MA
95001	李勇		男	20	CS
95011	包小柏	男	18	MA
95009	梦圆圆	女	18	MA
95015	王君		男	18	MA

           

求：学生信息表中 每个部门中年龄最大的前两个

分析：

将数据按照部门分组

在在每一个部门中进行局部排序 倒序

每个部门中输出2个

直接写比较困难，借助分析函数

窗口函数+over子句

over子句： 用于指定分组（分区）或排序规则的 行号|排名规则

#over子句中的语法：
over(distribute by + sort by )
over(partition by + order by )
           

row_number

1. 对学生信息数据 按照部门分 在每个部门中加行号

   hive> select
       > dept,name,row_number() over(distribute by dept)
       > from stu_managed;
   Total MapReduce CPU Time Spent: 0 msec
   OK
   CS	孙庆		1
   CS	冯伟		2
   CS	王小丽	3
   CS	李勇		4
   CS	孙花		5
   CS	孔小涛	6
   CS	李娜		7
   IS	张三dg	1
   IS	张立		2
   IS	赵钱		3
   IS	邢小丽	4
   IS	王一		5
   IS	王风娟	6
   IS	刘晨		7
   MA	梦圆圆	1
   MA	王君		2
   MA	易思玲	3
   MA	刘刚		4
   MA	周二		5
   MA	郑明		6
   MA	包小柏	7
   MA	王敏		8
   Time taken: 1.959 seconds, Fetched: 22 row(s)
              

2. 相对每一个部门的每个人的年龄排序

   hive> select
       > dept,name,age,row_number() over(distribute by dept sort by age desc)
       > from stu_managed;
   Total MapReduce CPU Time Spent: 0 msec
   OK
   CS	孙庆		23	1
   CS	冯伟		21	2
   CS	李勇		20	3
   CS	孙花		20	4
   CS	王小丽	19	5
   CS	孔小涛	19	6
   CS	李娜		18	7
   IS	张三dg	23	1
   IS	赵钱		21	2
   IS	王一		19	3
   IS	刘晨		19	4
   IS	张立		19	5
   IS	邢小丽	19	6
   IS	王风娟	18	7
   MA	王敏		22	1
   MA	郑明		20	2
   MA	易思玲	19	3
   MA	梦圆圆	18	4
   MA	包小柏	18	5
   MA	刘刚		18	6
   MA	王君		18	7
   MA	周二		17	8
       
              

rank

1. 在每个部门中按照年龄进行添加排名

hive> select
    > dept,name,age,rank() over(partition by dept order by age)
    > from stu_managed;
Total MapReduce CPU Time Spent: 0 msec
OK
CS	李娜		18	1
CS	孔小涛	19	2
CS	王小丽	19	2
CS	李勇		20	4
CS	孙花		20	4
CS	冯伟		21	6
CS	孙庆		23	7
IS	王风娟	18	1
IS	张立		19	2
IS	刘晨		19	2
IS	王一		19	2
IS	邢小丽	19	2
IS	赵钱		21	6
IS	张三dg	23	7
MA	周二		17	1
MA	刘刚		18	2
MA	包小柏	18	2
MA	梦圆圆	18	2
MA	王君		18	2
MA	易思玲	19	6
MA	郑明		20	7
MA	王敏		22	8
           

dense_rank

dense_rank 与 rank的区别是不会累加并列

hive> select dept,name,age,dense_rank() over(partition by dept order by age) from stu_managed;
Total MapReduce CPU Time Spent: 0 msec
OK
CS	李娜		18	1
CS	孔小涛	19	2
CS	王小丽	19	2
CS	李勇		20	3
CS	孙花		20	3
CS	冯伟		21	4
CS	孙庆		23	5
IS	王风娟	18	1
IS	张立		19	2
IS	刘晨		19	2
IS	王一		19	2
IS	邢小丽	19	2
IS	赵钱		21	3
IS	张三dg	23	4
MA	周二		17	1
MA	刘刚		18	2
MA	包小柏	18	2
MA	梦圆圆	18	2
MA	王君		18	2
MA	易思玲	19	3
MA	郑明		20	4
MA	王敏		22	5

           

row_number rank dense_rank 使用场景：结合over子句 MR中分组求topN

案例答案

hive> select * from (select dept,name,age,row_number() over(distribute by dept sort by age desc) index from stu_managed) a where index<=2;
Total MapReduce CPU Time Spent: 0 msec
OK
CS	孙庆		23	1
CS	冯伟		21	2
IS	张三dg	23	1
IS	赵钱		21	2
MA	王敏		22	1
MA	郑明		20	2