窗口函数的定义、分类及用法

下山化缘的DJ

已于 2023-04-23 10:15:41 修改

阅读量2.5k

点赞数

分类专栏： Hive 文章标签： hive

于 2020-07-16 11:51:57 首次发布

本文链接：https://blog.csdn.net/qq_39150361/article/details/107378227

版权

Hive 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

开窗函数的使用

定义：

开窗函数也叫分析函数，有两类：一类是聚合开窗函数，一类是排序开窗函数。

格式：函数名(列) OVER(partition by 列名 order by列名)

（1）OVER 关键字：

OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。
开窗函数 COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。
聚合函数每组只返回一个值，开窗函数每组可返回多个值。

（2）PARTITION BY 子句：
开窗函数的 OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独
立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。

2、开窗函数分类
1>聚合开窗函数使用：一般和聚合函数使用，组成开窗函数，
常用聚合函数：
count()求个数
sum() 求和
avg()求平均数
min()最小值
max()最大值
需要注意的是：
（1）这些函数常与group by子句连用。除了 COUNT 以外，聚合函数忽略空值。
（2）聚合开窗函数只能使用PARTITION BY子句或都不带任何语句，聚合开窗函数不能和order by 一起使用。
over() 的另一常用情景是与 row_number() 一起用于分页。
（3）窗口函数OVER（）指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。
（4）开窗函数不需要使用GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。
注意：
聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。比如0004号，在使用sum窗口函数后的结果，是对0001，0002，0003，0004号的成绩求和，若是0005号，则结果是0001号~0005号成绩的求和，如：test01表中

uid	class_id	score
0001	1	86
0002	1	95
0003	2	89
0004	1	83
0005	2	86
0006	3	92
0007	3	86
0008	1	88

select  uid,class_id,score,
 sum(score) over(partition by class_id order by uid) sum_score
 ,avg(score) over(partition by class_id order by uid) avg_score
 ,count(score) over(partition by class_id order by uid) count_score
 from test02;

结果：
在这里插入图片描述
比如求用户最近7天的支付记录且导入历史数据：

select date_add(dt,1)dt,order_money,sum(order_money) over( rows between 6 preceding and current row )day7_order_money 
from (
     select date(create_time)dt,sum(order_money) order_money
     from dwd.dwd_order_standard_order_summary
     where create_time< current_date
     and create_time>=date_add(current_date,-49)
     and order_belong_type=1
     group by date(create_time)
     order by dt
)

2> 排序开窗函数的使用
ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排序函数。
排序开窗函数可以单独使用ORDER BY 语句，也可以和PARTITION BY同时使用。
PARTITION BY用于将结果集进行分组，开窗函数应用于每一组。
ORDER BY 指定排名开窗函数的顺序。在排名开窗函数中必须使用ORDER BY语句。

rank函数说明：如共有1，2，2，4四个数字排序。
RANK() 排序相同时会重复，总数不会变，如： 1，2，2，4
DENSE_RANK() 排序相同时会重复，总数会减少，如：
1，2，2，3
ROW_NUMBER() 会根据顺序计算 1，2，3，4

3、窗口函数的大小
1>over（）：默认的窗口大小就是当前整个结果集的大小,
开窗函数是对每一条数据都开一条窗口。假如有（1，1，2，2，3，3），对这些数据开窗
count(*) over(partition by num)，则会开六个窗口，结果为：
1，2
1，2
2，2
2，2
3，2
3，2

2>over （partition by 字段）：在窗口函数中做了分区，默认的窗口大小就是每个分区的大小。对于每个窗口中的每条数据来讲，都会开一个窗口。
3>over(ORDER BY 字段 ) : 在窗口函数中只用到了order by 排序, 也会对每条数据开一个窗口，默认的窗口大小就是从数据集的开始位置到当前处理的数据的位置。

几个关键字总结:
1>建表: partitioned by(分区表) clustered by(分桶)

2>查询: order by(查询中做全局排序) distribute by(查询做分区) sort by(查询做排序) cluster by (查询分区排序)

3>窗口函数: partition by(窗口函数中做分区) order by(窗口函数中做排序)