sql开窗函数

￡神魔★判官ぃ 2023-10-08 12:57 34阅读 0赞

#### 文章目录 ####

*  写在前面
 *   *  1、介绍
     *  2、OVER()
     *  3、OVER(PARTITION BY name)
     *  4、OVER(ORDER BY...)
     *  5、高级开窗函数

## 写在前面 ##

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

### 1、介绍 ###

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

### 2、OVER() ###

如下表，我们想要获取num大于100的数据，并在每条数据后面都显示总条数  
![在这里插入图片描述][20210706094138897.png]  
先看一种会报错的写法(如下，我们首先想到的可能是这样写)

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()对于查询结果的每一行都返回所有符合条件的行的条数。

使用开窗函数是不是要方便很多（下面是查询结果）  
![在这里插入图片描述][20210706094603129.png]

### 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

查询结果（相当于查询的是满足条件的同名人的个数）  
![在这里插入图片描述][20210706095734833.png]

### 4、OVER(ORDER BY…) ###

语法：

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

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

边界规则的可取值见下表：  
![在这里插入图片描述][20210706100435490.png]  
例,查询从第一行到当前行的工资总和（并且排序显示）：

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

查询结果  
![在这里插入图片描述][20210706100652154.png]  
上面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;

查询结果  
![在这里插入图片描述][2021070610123649.png]

### 5、高级开窗函数 ###

除了可以在开窗函数中使用COUNT()、SUM()、MIN()、MAX()、AVG()等这些聚合函数，  
还可以在开窗函数中使用一些高级的函数，有些函数同时被DB2和Oracle同时支持，比如  
RANK()、DENSE\_RANK()、ROW\_NUMBER()，而有些函数只被Oracle支持，比如  
RATIO\_TO\_REPORT()、NTILE()、LEAD()、LAG()、FIRST\_VALUE()、LAST\_VALUE()。

[20210706094138897.png]: https://img-blog.csdnimg.cn/20210706094138897.png
[20210706094603129.png]: https://img-blog.csdnimg.cn/20210706094603129.png
[20210706095734833.png]: https://img-blog.csdnimg.cn/20210706095734833.png
[20210706100435490.png]: https://img-blog.csdnimg.cn/20210706100435490.png
[20210706100652154.png]: https://img-blog.csdnimg.cn/20210706100652154.png
[2021070610123649.png]: https://img-blog.csdnimg.cn/2021070610123649.png