简介
也称分析函数,OLAP函数(Online Anallytical Processing,联机分析函数),窗口函数与分组聚合函数类似,但是每一行数据都生成一个结果。
适用数据平台:hive,Spark,sql server, Oracle, mysql8.0,PostgreSQL等
mysql8.0官方说明:https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
说明:可以查看实例,有直观的感受;
基本语法:
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名> Windows_clause)
说明:窗口函数带有一个开窗函数over(),包含三个分析子句
分组:partition by
排序:order by
窗口:windows_clause
场景
排序开窗:row_number, rank, dense_rank等 实现分组排名(topN问题)
聚合开窗:如sum, avg, count, max, min等
函数
函数名 | 功能描述 |
---|---|
row_number() | 返回分区中当前行的编号 |
rank() | 返回分区中当前行的排名,排名可能不连续 |
dense_rank() | 返回分区中当前行的排名,并且排名是连续的 |
first_value() | 当前窗口中第一行的表达式值 |
last_value() | 当前窗口中最后一行的表达式值 |
lag() | 分区中当前行前面第 N 行的表达式值 |
lead() | 分区中当前行后面第 N 行的表达式值 |
ntile() | 将分区划分为 N 桶,为分区中的每一行分配桶号 |
cume_dist() | 返回一组值中的累积分布 |
nth_value() | 当前窗口中第 N 行的表达式值 |
示例:(说明:可查看mysql8.0提供的示例,用法基本一致)
url:https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
mysql> SELECT
val,
ROW_NUMBER() OVER w AS ‘row_number’,
RANK() OVER w AS ‘rank’,
DENSE_RANK() OVER w AS ‘dense_rank’
FROM numbers
WINDOW w AS (ORDER BY val);
±-----±----------------±-------±-----------------+
| val | row_number | rank | dense_rank |
±-----±----------------±-------±-----------------+
| 1 | 1 | 1 | 1 |
| 1 | 2 | 1 | 1 |
| 2 | 3 | 3 | 2 |
| 3 | 4 | 4 | 3 |
| 3 | 5 | 4 | 3 |
| 3 | 6 | 4 | 3 |
| 4 | 7 | 7 | 4 |
| 4 | 8 | 7 | 4 |
| 5 | 9 | 9 | 5 |
±-----±----------------±-------±-----------------+