一、窗口函数的知识点

1.1 窗户函数的定义

窗口函数可以拆分为【窗口+函数】。窗口函数官网指路：

LanguageManual WindowingAndAnalytics – Apache Hive – Apache Software Foundationhttps://cwiki.apache.org/confluence/display/Hive/LanguageManual%20WindowingAndAnalytics

  • 窗口：限定函数的计算范围（窗口函数：针对分组后的数据，从逻辑角度指定计算的范围，并没有从物理上真正的切分，只有group by 是物理分组，真正意义上的分组）
  • 函数：计算逻辑
  • 窗口函数的位置：跟sql里面聚合函数的位置一样，from -> join -> on -> where -> group by->select 后面的普通字段，窗口函数 -> having -> order by -> lmit 。 窗口函数不能跟聚合函数同时出现。聚合函数包括count、sum、 min、max、avg。
  • sql 执行顺序：from -> join -> on -> where -> group by->select 后面的普通字段，聚合函数-> having -> order by -> limit

1.2 窗户函数的语法

window_name over ( [partition by 字段…] [order by 字段…] [窗口子句] )

  • window_name：给窗口指定一个别名。
  • over：用来指定函数执行的窗口范围，如果后面括号中什么都不写，即over() ，意味着窗口包含满足where 条件的所有行，窗口函数基于所有行进行计算。
  • 符号[] 代表：可选项； | ： 代表二选一
  • partition by 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行。分组间互相独立。
  • order by 子句：每个partition内部按照哪些字段进行排序，如果没有partition ，那就直接按照最大的窗口排序，且默认是按照升序（asc）排列。
  • 窗口子句：显示声明范围（不写窗口子句的话，会有默认值）。常用的窗口子句如下：
rows between unbounded preceding andunbounded following; -- 上无边界到下无边界（一般用于求 总和）rows between unbounded preceding and current row;--上无边界到当前记录(累计值)rows between 1 preceding and current row; --从上一行到当前行rows between 1 preceding and 1 following; --从上一行到下一行rows between current row and 1 following; --从当前行到下一行

ps: over()里面有order by子句，但没有窗口子句时 ，即： over ( partition by 字段… order by 字段… )，此时窗口子句是有默认值的—-> rows between unbounded preceding and current row (上无边界到当前行)。

此时窗口函数语法： over ( partition by 字段… order by 字段… ) 等价于

over ( partition by 字段… order by 字段… rows between unbounded preceding and current row)
需要注意有个特殊情况：当order by 后面跟的某个字段是有重复行的时候， over ( partition by 字段… order by 字段… ) 不写窗口子句的情况下，窗口子句的默认值是：range between unbounded preceding and current row(上无边界到当前相同行的最后一行)。

因此，遇到order by 后面跟的某个字段出现重复行，且需要计算【上无边界到当前行】，那就需要手动指定窗口子句 rows between unbounded preceding and current row ，偷懒省略窗口子句会出问题~

ps: 窗口函数的执行顺序是在where之后，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行。

【例如】求出登录记录出现间断的用户Id

selectidfrom ( select id, login_date, lead(login_date, 1, '9999-12-31')over (partition by id order by login_date) next_login_date --窗口函数 lead(向后取n行) --lead(column1,n,default)over(partition by column2 order by column3) 查询当前行的后边第n行数据，如果没有就为null from (--用户在同一天可能登录多次，需要去重selectid,date_format(`date`, 'yyyy-MM-dd') as login_datefrom user_loggroup by id, date_format(`date`, 'yyyy-MM-dd')) tmp1 ) tmp2wheredatediff(next_login_date, login_date) >=2group by id;

1.3 窗口函数分类

哪些函数可以是窗口函数呢？(放在over关键字前面的)

  • 聚合函数
sum(column) over (partition by .. order by .. 窗口子句);count(column) over (partition by .. order by .. 窗口子句);max(column) over(partition by .. order by .. 窗口子句);min(column) over (partition by .. order by .. 窗口子句);avg(column) over (partition by .. order by .. 窗口子句);
ps : 高级聚合函数：

collect_list 收集并形成list集合，结果不去重;

collect_set收集并形成set集合，结果去重;

举例：

--每个月的入职人数以及姓名 select month(replace(hiredate,'/','-')),count(*) as cnt,collect_list(name) as name_listfrom employeegroup by month(replace(hiredate,'/','-'));/*输出结果monthcnname_list42["宋青书","周芷若"]61["黄蓉"]71["郭靖"]82["张无忌","杨过"]92["赵敏","小龙女"]*/
  • 排序函数

row_number() 、rank()、dense_rank()函数不支持自定义窗口子句。

--顺序排序——1、2、3row_number() over(partition by .. order by .. )--并列排序，跳过重复序号——1、1、3（横向加）rank() over(partition by .. order by .. )-- 并列排序，不跳过重复序号——1、1、2（纵向加）dense_rank()over(partition by .. order by .. )
  • 前后函数

lag和lead函数不支持自定义窗口子句。

-- 取得column列的前n行，如果存在则返回，如果不存在，返回默认值defaultlag(column,n,default) over(partition by.. order by...) as lag_test-- 取得column列的后n行，如果存在则返回，如果不存在，返回默认值defaultlead(column,n,default) over(partition by.. order by...) as lead_test
  • 头尾函数
first_value(column,true)---当前窗口column列的第一个数值，如果有null值，则跳过first_value(column,false) ---当前窗口column列的第一个数值，如果有null值，不跳过last_value(column,true)--- 当前窗口column列的最后一个数值，如果有null值，则跳过last_value(column,false) --- 当前窗口column列的最后一个数值，如果有null值,不跳过

1.4排序函数

rank/dense_rank/row_number 函数，一般用于求分组topN。

--顺序排序——1、2、3row_number() over(partition by .. order by .. )--并列排序，跳过重复序号——1、1、3（横向加）rank() over(partition by .. order by .. )-- 并列排序，不跳过重复序号——1、1、2（纵向加）dense_rank()over(partition by .. order by .. )

二、实际案例

2.1 每个学生成绩第二高的科目

0 问题描述

根据学生成绩表，求出每个学生成绩第二高的科目。

1 数据准备

create table if not exists table5(class string comment '学科',student string comment '学生姓名',score int comment '成绩')comment '学生成绩表';insert overwrite table table5 values('a','吱吱1',100),('a','吱吱2',60),('b','吱吱1',80),('b','吱吱2',70),('c','吱吱2',50),('c','吱吱3',90);

2 数据分析

3种排序函数的区别： row_number （行号）-- 1 2 3 ; rank （重复跳过）--1 1 3; dense_rank （重复不跳过） --1 1 2
selectclass,studentfrom ( select class, student, score, dense_rank()over (partition by student order by score desc) rn from table5 ) tmp1where rn = 2;

3 小结

排序函数在分组tpoN场景应用十分广泛，需要注意的是在sql语句中，窗口函数的执行顺序是在where过滤条件之后，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行。