sql的over函数的使用

本文详细介绍了SQL中的窗口函数,包括over()的基本语法,以及partitionby和orderby的使用,重点讲解了ROW_NUMBER(),RANK(),DENSE_RANK()和NTILE()的功能区别和应用场景,帮助读者理解如何在数据库查询中进行分组、排序和排名。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

开窗函数over:是对一组值进行操作。

over语法有:

1、partition by对子句进行分组

2、order by对子句进行排序
例:
1、对partition by后面的列进行分组,分组后求sum函数中列的和
select sum(列) over (partition by 组)
2、对partition by后面的列分组,然后按order by后面的列排序,最后进行对列1进行累加
select sum(列1) over(partition by 列2 order by 列3)
3、对order by后面的列排序,然后对列1进行累加
select sum(列1)over(order by 列2)
4、对列1进行汇总
select sum(列1)over ()

row_number()函数:ROW_NUMBER()函数作用就是将SELECT查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,一般多用于分页查询,比如查询前10个 查询10-100个学生。ROW_NUMBER()必须与ORDER BY一起使用,否则会报错。
例:对学生成绩排序
1、班内排序:select row_number()over(partition by 班名列 order by 分数列  desc) from 表 where 条件
2、总排序:select row_number() over(order by 分数列 desc)from 表 where 条件

rank()函数:RANK()函数,顾名思义排名函数,可以对某一个字段进行排名,这里和ROW_NUMBER()有什么不一样呢?ROW_NUMBER()是排序,当存在相同成绩的学生时,ROW_NUMBER()会依次进行排序,他们序号不相同,而Rank()则不一样。如果出现相同的,他们的排名是一样的
例:
使用row_number排序: select row_number()over (order by 分数列 desc)from 表
使用rank()排序:select rank()over (order by 分数列 desc)from 表 


dense_rank()函数:DENSE_RANK()函数也是排名函数,和RANK()功能相似,也是对字段进行排名,那它和RANK()到底有什么不同那?特别是对于有成绩相同的情况,DENSE_RANK()排名是连续的,RANK()是跳跃的排名,一般情况下用的排名函数就是RANK() 
例:
使用rank():select rank()over(order by 分数列 desc)from 表
使用dense_rank():select dense_rank() over(order by 分数列 desc)from 表


ntile()函数:NTILE()函数是将有序分区中的行分发到指定数目的组中,各个组有编号,编号从1开始,就像我们说的'分区'一样 ,分为几个区,一个区会有多少个
例:
把所有数据分为一个区:select ntile(2) over(order by 分数列 desc)from 表
把所有数据分为两个区:select ntile(2) over(order by 分数列 desc)from 表
把所有数据分为三个区:select ntile(3) over(order by 分数列 desc)from 表

### SQL 窗口函数 OVER 的用法 `OVER` 是 SQL 中的关键字,用于定义一个窗口或帧,在该窗口上执行特定的操作。它通常与聚合函数(如 `SUM()`、`AVG()`)或者排序函数(如 `ROW_NUMBER()`、`RANK()`)一起使用。 #### 基本语法 以下是 `OVER` 函数的基本语法结构: ```sql function_name (expression) OVER ( [PARTITION BY partition_expression] [ORDER BY sort_expression] [window_frame_clause] ) ``` - **`PARTITION BY`**: 将数据划分为多个分区,类似于 `GROUP BY`,但在同一查询中可以保留每一行的数据。 - **`ORDER BY`**: 定义每一分区内的排序方式。 - **`window_frame_clause`**: 可选参数,指定窗口框架的范围,默认情况下会考虑当前行及其之前的所有行。 --- #### 使用示例 ##### 示例 1: 计算累计销售额 假设有一个销售表 `Sales`,其中包含字段 `ProductID`, `SaleDate`, 和 `Amount`。可以通过以下语句计算每个产品的累计销售额: ```sql SELECT ProductID, SaleDate, Amount, SUM(Amount) OVER(PARTITION BY ProductID ORDER BY SaleDate) AS CumulativeSum FROM Sales; ``` 此查询通过 `PARTITION BY ProductID` 对产品进行分组,并按日期顺序累加金额[^2]。 --- ##### 示例 2: 排名功能 如果需要对学生的成绩进行排名,则可使用 `RANK()` 或 `DENSE_RANK()` 函数。例如: ```sql SELECT StudentName, Score, RANK() OVER(ORDER BY Score DESC) AS RankScore FROM Scores; ``` 在此例子中,学生按照分数降序排列并分配相应的排名[^4]。 --- ##### 示例 3: 获取前一行/后一行的值 利用 `LAG()` 和 `LEAD()` 函数可以从相邻记录获取信息。下面展示如何获得前一天的销量作为比较依据: ```sql SELECT SaleDate, Amount, LAG(Amount, 1) OVER(ORDER BY SaleDate) AS PreviousDayAmount FROM DailySales; ``` 这里展示了如何基于时间序列访问前后行的内容[^3]。 --- ### 总结 SQL 窗口函数提供了强大的工具来处理复杂分析需求,而 `OVER` 子句则是实现这些功能的核心部分之一。无论是简单的汇总统计还是复杂的相对位置评估都可以借助于这一机制完成。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值