MySQL——开窗函数

全栈程序员-用户IM • 2021年5月20日上午10:00 • 未分类

开窗函数格式：函数名（列） over (选项）

SQL标准允许将所有聚合函数用作开窗函数，使用over关键字来区分这两种用法。

PARTITION BY 子句

与group by子句不同，partition by子句创建的分区是独立于结果集的，partition by创建的分区只是供进行聚合运算的。

--显示每一个人员的信息以及所属城市的人员数
select fname,fcity,fage,fsalary,
count(*) over(partition by fcity) 所在城市人数 from t_person

在同一个SELECT语句中可以同时使用多个开窗函数，而且这些开窗函数并不会相互干扰。比如下面的SQL语句用于显示每一个人员的信息、所属城市的人员数以及同龄人的人数：

--显示每一个人员的信息、所属城市的人员数以及同龄人的人数：
select fname,
       fcity,
       fage,
       fsalary,
       count(*) over(partition by fcity) 所属城市的人个数,
       count(*) over(partition by fage) 同龄人个数
  from t_person

ORDER BY子句

使用ORDER BY子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。ORDER BY子句的语法为：

ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2

RANGE表示按照值的范围进行范围的定义，
ROWS表示按照行的范围进行范围的定义；
边界规则的可取值见下表：

例子一：查询从第一行到当前行的工资总和
select fname,
	fcity,
	fage,
	fsalary,
	sum(salary) over(order by fsalary rows between unbounded preceding and current row) 到当前工资求和
from t_person

可以简化为：
select fname,
       fcity,
       fage,
       fsalary,
       sum(fsalary) over(order by fsalary) 到当前行工资求和
from t_person

在这里插入图片描述

例子二：把例子程序一的row换成了range，是按照范围进行定位的
select fname,
       fcity,
       fage,
       fsalary,
       sum(fsalary) over(order by fsalary range between unbounded preceding and current row) 到当前行工资求和
  from t_person

在这里插入图片描述
高级开窗函数/ 排名的实现ROW_NUMBER();rank() ,dense_rank()

SELECT FName, FSalary,FAge,
	RANK() OVER(ORDER BY fsalary desc) f_RANK,
	DENSE_RANK() OVER(ORDER BY fsalary desc) f_DENSE_RANK,
	ROW_NUMBER() OVER(ORDER BY fsalary desc) f_ROW_NUMBER
FROM T_Person;

在这里插入图片描述

dence_rank在并列关系是，相关等级不会跳过。rank则跳过
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。

【语法】
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） 
row_number() 返回的主要是“行”的信息，并没有排名