sql over() 函数

本文深入讲解SQL中的窗口函数,包括over函数的写法、开窗的窗口范围、与over()函数结合使用的各种函数如rank()、dense_rank()、sum()、first_value()、last_value()等,以及它们在查询班级成绩排名、求班级总分等场景的应用。

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

1、over函数的写法:

  over(partition by class order by sroce) 按照sroce排序进行累计,order by是个默认的开窗函数,按照class分区。

  2、开窗的窗口范围:

  over(order by sroce range between 5 preceding and 5 following):窗口范围为当前行数据幅度减5加5后的范围内的。

  over(order by sroce rows between 5 preceding and 5 following):窗口范围为当前行前后各移动5行。

  3、与over()函数结合的函数的介绍

  (1)、查询每个班的第一名的成绩:如下 

1 SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

  结果为:

复制代码

1 得到的结果是:
2 dss        1        95        1
3 ffd        1        95        1
4 gds        2        92        1
5 gf         3        99        1
6 ddd        3        99        1

复制代码

  注意:在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果。

1 SELECT * FROM (select t.name,t.class,t.sroce,row_number() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;

  结果为:

dss      1        95        1  
gfs      2        92        1
ddd      3        99        1 

  可以看出,本来第一名是两个人的并列,结果只显示了一个。

  (2)、rank()和dense_rank()可以将所有的都查找出来,rank可以将并列第一名的都查找出来;rank()和dense_rank()区别:rank()是跳跃排序,有两个第二名时接下来就是第四名。

  求班级成绩排名:

1 select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

  查询结果:

复制代码

dss        1        95        1
ffd        1        95        1
fda        1        80        3
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        3
asdf       3        55        4
adf        3        45        5

复制代码

  dense_rank()l是连续排序,有两个第二名时仍然跟着第三名

1 select t.name,t.class,t.sroce,dense_rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

  查询结果:

复制代码

dss        1        95        1
ffd        1        95        1
fda        1        80        2 
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        2
asdf       3        55        3
adf        3        45        4

复制代码

  3、sum()over()的使用

  根据班级进行分数求和

1 select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

复制代码

dss        1        95        190  --由于两个95都是第一名,所以累加时是两个第一名的相加
ffd        1        95        190 
fda        1        80        270  --第一名加上第二名的
gds        2        92        92
cfe        2        74        166
gf         3        99        198
ddd        3        99        198
3dd        3        78        276
asdf       3        55        331
adf        3        45        376

复制代码

  4、first_value() over()和last_value() over()的使用 

1 select t.name,t.class,t.sroce,first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
2 select t.name,t.class,t.sroce,last_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

  分别求出第一个和最后一个成绩。

  5、sum() over()的使用

1 select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

  求出班级的总分。

  下面还有很多用法,就不一一列举了,简单介绍一下,和上面用法类似:

 

  count() over(partition by ... order by ...):求分组后的总数。
  max() over(partition by ... order by ...):求分组后的最大值。
  min() over(partition by ... order by ...):求分组后的最小值。
  avg() over(partition by ... order by ...):求分组后的平均值。
  lag() over(partition by ... order by ...):取出前n行数据。  

  lead() over(partition by ... order by ...):取出后n行数据。

  ratio_to_report() over(partition by ... order by ...):Ratio_to_report() 括号中就是分子,over() 括号中就是分母。

  percent_rank() over(partition by ... order by ...):

  6、over partition by与group by的区别:

  group by是对检索结果的保留行进行单纯分组,一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。partition by虽然也具有分组功能,但同时也具有其他的高级功能。

解读了一部分,不照搬就是最好的理解。

### 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` 子句则是实现这些功能的核心部分之一。无论是简单的汇总统计还是复杂的相对位置评估都可以借助于这一机制完成。
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

我笔记

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值