sql开窗函数

写在前面

SQL开窗函数: 目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，mysql暂不支持。

1、介绍

开窗函数用来对行集组进行聚合计算，在 Oracle 中也被称为分析函数。

2、OVER()

如下表，我们想要获取num大于100的数据，并在每条数据后面都显示总条数

先看一种会报错的写法(如下，我们首先想到的可能是这样写)

SELECT ID,NUM,NAME,count(1) FROM AA WHERE num > 100  # 错误写法

再看看我们不用开窗函数的写法：

SELECT 
	ID,NUM,NAME,
	(SELECT count(1) FROM AA WHERE num > 100) 
FROM AA WHERE num > 100

接下来使用开窗函数

SELECT ID,NUM,NAME,count(1) OVER() FROM AA WHERE num > 100

开窗函数 COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。

使用开窗函数是不是要方便很多（下面是查询结果）

3、OVER(PARTITION BY name)

OVER(PARTITION BY name)有点像 GROUP BY name，但是前者会把结果传给每一条数据（不进行聚合）。

SELECT ID,NUM,NAME,count(1) OVER(PARTITION BY NAME) FROM AA WHERE num > 10

查询结果（相当于查询的是满足条件的同名人的个数）

4、OVER(ORDER BY…)

语法：

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

• RANGE表示按照值的范围进行范围的定义
• ROWS表示按照行的范围进行范围的定义

边界规则的可取值见下表：

例,查询从第一行到当前行的工资总和（并且排序显示）：

select fname,
       fcity,
       fage,
       fsalary,
       sum(fsalary) over(order by fsalary rows between unbounded preceding and current row) 到当前行工资求和
from t_person

查询结果

上面sql可以简化成：

select fname,
       fcity,
       fage,
       fsalary,
       sum(fsalary) over(order by fsalary) 到当前行工资求和
from t_person

例2，算工资排名（并且排序显示）

SELECT FName, FSalary,
COUNT(*) OVER(ORDER BY FSalary ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW)
FROM T_Person;

查询结果

5、高级开窗函数

除了可以在开窗函数中使用COUNT()、SUM()、MIN()、MAX()、AVG()等这些聚合函数，
还可以在开窗函数中使用一些高级的函数，有些函数同时被DB2和Oracle同时支持，比如
RANK()、DENSE_RANK()、ROW_NUMBER()，而有些函数只被Oracle支持，比如
RATIO_TO_REPORT()、NTILE()、LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()。