MySQL窗口函数学习笔记（一）

窗口函数的理论理解1 示例

窗口函数的语句大概类似于这样的：

select sum(字段) over ( partition by 字段 order by 字段 ) as 新字段 from table

该语句可以拆成以下几个内容：

有如下表格：

想要按性别获取平均GPA，得到如下结果：

代码如下：

select Gender, avg(GPA) as avg_gpa from students group by Gender;

如果我们想要得到如下结果：

可以通过join将以上两个表连接查询，但是sql语句中join和order by性能很低，耗时长。

select a.* ,b.avg_gpa 
from students a 
inner join
(select Gender, avg(GPA) as avg_gpa from students group by Gender) b
on a.Gender=b.Gender;

使用窗口函数

select *,
avg(GPA) over(partition by Gender) as avg_gpa
from students;

翻译成大白话：按性别对数据进行分区，计算每种性别的平均GPA。然后，创建一个名为avg_gpa的新列，为每行附加关联的平均GPA。

2 窗口函数类型

通过上面的示例可以发现，其实窗口函数也可以使用聚合函数，窗口函数基本有以下几类：

2.1 “聚合函数”名称描述

sum( )

求和

min( )

求最小值

count( )

统计

avg( )

求平均

2.2 序号函数名称描述

row_number( )

为分区中的每一行分配一个整数

rank( )

按照值排序时产生一个自增编号，值相等时会重复，会产生空位（如：1、2、3、3、5）

dense_rank( )

按照值排序时产生一个自增编号，值相等时会重复，不会产生空位（如：1、2、3、3、4）

2.3 分布函数名称描述

percent_rank( )

计算分区或结果集中行的百分数等级

cume_dist( )

计算一组值中一个值的累积分布

2.4 前后函数名称描述

lag( )

返回分区中当前行之前的第N行的值；如果不存在前一行，则返回NULL

lead( )

返回分区中当前行之后的第N行的值；如果不存在后续行，则返回NULL

2.5 头尾函数名称描述

first_value

返回相对于窗口框架第一行的指定表达式的值

last_value

返回相对于窗口框架中最后一行的指定表达式的值

2.6 其他函数名称描述

nth_value( )

从窗口框架的第N行返回参数的值

ntile

将每个窗口分区的行分配到指定数量的排名组中

3 窗口函数语法

窗口函数的相关语法：

select field1,field2,...,
window_function_name(window_name/expression)
over (
     [partiton_defintion]
     [order_definition]
     [frame_definition]
)
as new_field
from table_name;

光看概念有点抽象，后面实践就明白了。就是over( )括号中的内容都是根据查询要求而定的，可以没有内容（原表），但是必须要加上括号。

3.1 frame子句语法

frame_unit {<frame_start>|<frame_between>}

frame_unit有两种，分别是rows和range，由rows定义的frame是由开始和结束位置的行确定 的，由range定义的frame由在某个值区间的行确定。

3.1.1 基于rows

通常使用between frame_start and frame_end语法来表示行范围，frame_start和frame_end支持如下关键字，来确定不同的动态行记录：

current row 边界是当前行，一般和其他范围关键字一起使用
unbounded preceding 边界是分区中的第一行
unbounded following 边界是分区中的最后一行
expr preceding 当前行之前的expr(数字或表达式)行
expr following 当前行之后的expr(数字或表达式)行
比如，下面都是合法的范围：
rows between 1 preceding and 1 following 窗口范围是当前行、前一行、后一行一共三行记录。
rows unbounded following 窗口范围是当前行到分区中的最后一行
rows between unbounded preceding and unbounded following 窗口范围是当前分区中所有行，等同于不写。

3.1.2 基于range

和基于行类似MySQL 函数，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：interval 7 day preceding。

如果frame_definition未在over子句中指定，则MySQL默认使用以下框架：

range between unbounded preceding and current row;

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!