sql over的作用及用法

最新推荐文章于 2024-10-09 11:22:56 发布
原创 最新推荐文章于 2024-10-09 11:22:56 发布 · 126 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文深入探讨了SQL中的RANK()、DENSE_RANK()函数及其OVER子句的高级用法。通过实例演示如何实现数据分组排序，并在相同分组内对结果集进行有序排列，为数据库查询提供了更灵活和精确的数据筛选手段。
RANK ( ) OVER ( [query_partition_clause] order_by_clause )
DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )
可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序,
其中PARTITION BY 为分组字段，ORDER BY 指定排序字段
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
FlinkSql系列4之OVER聚合
feiyangailing的博客
03-30 1946
系列文章目录 前言 本节主要记录学习flinksql中的OVER聚合的使用，我们知道，对于GROUP BY来说，我们只能保留我们分组的字段，其他的字段是无法保留的，而对于OVER聚合来说我们可以实现保留全部的字段，不过在实际应用中，这个并不常用。 一、Over聚合实际测试 1.时间区间聚合 创建源表 CREATE TABLE source_table3( --订单id `order_id` BIGINT, --产品 `product` BIGINT, --金额 `amount` BIGINT, --支付时
SQL：Over 与 rank(),dense_rank(),row_number()
weixin_30685029的博客
10-16 481
RANK ( ) OVER ( [query_partition_clause] order_by_clause ) DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clau...
SQL over的作用及用法
dichengyan0013的博客
09-05 606
sql over的作用及用法 RANK ( ) OVER ( [query_partition_clause] order_by_clause ) DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序, 其中PARTITION B...
sql窗口函数 over
weixin_48485132的博客
07-24 1522
OVER子句使得窗口函数变得非常强大和灵活，它允许在不缩减结果集的情况下进行各种复杂的计算和分析操作。通过使用ORDER BY和窗口帧定义，可以对数据进行详细且复杂的分析，适用于诸如累积总和、移动平均、行排名等各种场景。了解和掌握OVER子句的使用，将大大提升您在SQL数据查询和分析中的能力。请给我举几个例子当然，下面是一些使用OVER子句的常见窗口函数的例子。
SQL中distinct 和 row_number() over() 的区别及用法
12-16
在咱们编写 SQL 语句操作数据库中的数据的时候，有可能会遇到一些不太爽的问题，例如对于同一字段拥有相同名称的记录，我们只需要显示一条，但实际上数据库中可能含有多条拥有相同名称的记录，从而在检索的时候，...
Sql Server 开窗函数Over()的使用实例详解
12-16
【Sql Server 开窗函数Over()的使用实例详解】 在SQL Server中，`OVER()`函数是一个非常强大的工具，它允许我们在不使用`GROUP BY`的情况下进行分组计算，...掌握好`OVER()`函数的用法，对于提升SQL编程能力至关重要。
sql server如何利用开窗函数over()进行分组统计
09-10
SQL Server中的开窗函数`OVER()`是一个非常强大的工具，它允许我们在不使用`GROUP BY`的情况下对数据进行分组计算，同时还能保留原始行的...在实际工作中，熟练掌握`OVER()`的用法能极大地提升SQL查询的效率和复杂性。
sql中having和where区别
天高任鸟飞，海阔凭鱼跃
01-07 201
having和where区别： 1.不能在where子句中过滤组，where子句仅用于过滤行。过滤组必须使用having子句。 2.不能在where子句中使用组函数，having子句才可以使用组函数。 where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。 having 子句的作用是...
SQL中的开窗函数over()
岁月静好
07-30 4804
上面我们额外介绍了聚合函数和排序函数的与OVER结合的使用方法，此外还有很多与OVER一起使用的函数，比如LEAD函数，LAG函数，STRING_AGG函数等等都会使用到开窗函数OVER，其使用方法也要务必掌握。ROW_NUMBER()是排序，当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()则不一样。RANK()是1-1-3-3-5-6，而ROW_NUMBER()则还是1-2-3-4-5-6，这就是RANK()和ROW_NUMBER()的区别了。...
sql语句关键字over的作用及用法
【设计改变世界】github地址：https://github.com/guochunyang2004
02-28 5360
在应用具体的聚合函数、排名函数前对行集的分区和排序。over子句，用于支持窗口的计算,所以一般与排名开窗函数、聚合开窗函数一起使用。   窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。 以前使用聚合函数必须分组，即便没有group by 子句，也是默认将表中所有的数据分成了1组，来聚合。通过使用over子句可以在不分组的情况下实现聚合运算，在查询结果集中既包含基础行的数据也包
js遍历树形结构方法
Cute_new_rookie的博客
09-12 1331
let nodeList = [ {id: '1-1', children: [{id: '1-2-1'}, {id: '1-2-2'}]}, {id: '2-1'}, {id: '3-1', children: [{id: '3-2-1', children: [{id: '3-3-1'}]}]} 这个方法比较常见，第一点就是你需要用额外的变量保存最后的结果，第二点就是可递归的条件是当前访问节点具有子节点，nodeList的children属性便是记录了子节点数据，我们不仅要判
SQL窗口函数OVER详细用法，一学就会
扬帆天涯
12-11 5921
SQL窗口函数OVER详细用法，一学就会
SQL：窗口函数之OVER()
qq_40178216的博客
03-22 3789
窗口函数 通用格式 “函数 OVER (PARTITION BY 分组 ORDER BY 排序依据 升降序)”。 这里记录下OVER()的使用方法 current row 当前行 n preceding:往前n行 n following:往后n行 unbounded： 起点，对应前面的n
SQL窗口函数OVER用法整理
热门推荐
m0_37953759的博客
02-22 2万+
SQL窗口函数OVER用法整理 OVER的定义 OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。 语法 OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] [ROWS|RANGE BETWEEN 边界规则1 and 边界规则2]) PARTITION BY 子句进行分组； ORDER BY 子句进行排序； ROWS|RANGE 框架是对窗口进行进一步的分区，框架有两种
SQL之什么是窗口函数OVER
LP'S Blog
10-09 731
OVER 用于为行定义一个窗口，它对一组值进行操作，不需要使用 GROUP BY 子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
SQL中的开窗函数
weixin_43997319的博客
05-25 9926
OVER的定义 OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。 OVER的语法 OVER ([ PARTITION BYcolumn ][ ORDER BY culumn ]) PARTITION BY 子句进行分组； ORDER BY 子句进行排序。 窗口函数OVER()指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。 开窗函数不需要使用GROUP BY就可以对数据进行分组，...
SQL中over的用法
最新发布
09-04
<think> 我们正在讨论SQL中的OVER关键字。根据引用内容，OVER关键字通常用于开窗函数（Window Functions），它定义了函数计算的数据窗口或分区。 引用中提到的内容： 引用[1]：Oracle中OVER的用法，提到了rows between unbounded preceding and unbounded following，即窗口从第一行到最后一行。 引用[2]：开窗函数的OVER关键字后可以使用PARTITION BY子句定义分区，与GROUP BY不同，它不影响结果集的行数，只是为聚合计算提供分区。 引用[3]：示例中使用了row_number() over(partition by orgid order by userid)，表示按orgid分区，然后在分区内按userid排序生成行号。 引用[4]：展示了在SQL Server中使用OVER(PARTITION BY ...)进行聚合计算，同时保留了原始行。 因此，OVER关键字的用法总结如下： 1. 基本作用：OVER()用于定义一个窗口（一组行），窗口函数在这个窗口上执行计算。窗口函数不会将多行合并为一行，而是为每一行返回一个值（比如聚合值、排名等）。 2. 语法结构： 窗口函数 OVER ( [PARTITION BY 列1, 列2, ...] -- 将数据分成多个分区（组），函数在每个分区内独立计算 [ORDER BY 列1 [ASC|DESC], ...] -- 指定分区内的排序方式 [ROWS/RANGE 子句] -- 定义窗口的起始和结束边界（框架） ) 3. 窗口函数类型： - 聚合函数：如SUM(), AVG(), COUNT(), MAX(), MIN()等。 - 排名函数：如ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()等。 - 分析函数：如LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()等。 4. 窗口框架（ROWS/RANGE）： 用来进一步限制窗口的范围，常见的选项有： - ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: 从分区第一行到当前行（常用作累计计算） - ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: 整个分区 - ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 当前行前一行到后一行 5. 注意： - 当OVER()内为空，则窗口为整个查询结果集。 - 当只有ORDER BY，没有ROWS/RANGE子句时，默认窗口为：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（注意：这与ROWS的行为略有不同，RANGE是基于排序值范围，ROWS是物理行数）。 举例说明： 示例1：计算每个订单中每个产品的数量，同时显示订单总数量（每个订单的总数量在每一行重复） SELECT SalesOrderID, ProductID, OrderQty, SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS TotalOrderQty FROM Sales.SalesOrderDetail; 示例2：计算每个订单中每个产品的数量占比 SELECT SalesOrderID, ProductID, OrderQty, OrderQty * 1.0 / SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Ratio FROM Sales.SalesOrderDetail; 示例3：按部门分区，按薪水排序，给每个部门的员工按薪水排名 SELECT department_id, employee_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank FROM employees; 示例4：计算累计薪水（按员工ID排序，计算从第一个员工到当前员工的薪水累加） SELECT employee_id, hire_date, salary, SUM(salary) OVER (ORDER BY employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary FROM employees; 示例5：移动平均（计算当前行及前两行的平均） SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; 注意事项： - 不同数据库对窗口函数的支持略有不同，但基本语法一致（Oracle, SQL Server, PostgreSQL, MySQL 8.0+等都支持）。 - ROWS和RANGE的区别：ROWS是按物理行（行位置），RANGE是按逻辑值（排序字段的值）。例如，如果排序字段有重复值，使用RANGE时，相同的值会被视为同一框架。 因此，OVER关键字使得我们可以进行复杂的分组计算，同时保留原始行的细节，这是它比GROUP BY更灵活的地方。 生成相关问题： 1. OVER子句中PARTITION BY和GROUP BY的区别? 2. ROWS和RANGE在窗口函数中的区别? 3. 如何利用OVER子句实现累计求和? 4. 开窗函数中ORDER BY的作用是什么? 5. 在MySQL中如何使用窗口函数? </think> ### SQL中`OVER()`关键字的用法详解 `OVER()`是SQL中的**窗口函数（Window Function）**核心语法，用于在特定数据窗口上执行计算而不聚合结果集。它保留原始行细节，同时支持分组计算、排序和动态分区。以下是核心用法： --- #### 一、基础语法结构 ```sql 函数() OVER ( [PARTITION BY 列1, 列2, ...] -- 将数据分组（类似GROUP BY但保留所有行） [ORDER BY 列 [ASC|DESC]] -- 组内排序 [ROWS/RANGE 窗口范围定义] -- 指定计算范围 ) ``` --- #### 二、核心功能与示例 ##### 1. **分区计算（`PARTITION BY`）** 在指定列的分区内独立运算： ```sql -- 计算每个销售订单的总数量（保留明细行） SELECT SalesOrderID, ProductID, OrderQty, SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS TotalPerOrder FROM Sales.SalesOrderDetail; ``` ✅ **效果**： - 相同`SalesOrderID`的分区分别计算`SUM` - 结果保留所有原始行（不合并）[^4] ##### 2. **动态排序（`ORDER BY`）** 组内按指定列排序后运算： ```sql -- 按部门分组，给员工按薪资排名 SELECT orgid, username, salary, ROW_NUMBER() OVER(PARTITION BY orgid ORDER BY salary DESC) AS salary_rank FROM au_userinfo; ``` ✅ **输出**： | orgid | username | salary | salary_rank | |-------|----------|--------|-------------| | 101 | Alice | 9000 | 1 | | 101 | Bob | 8000 | 2 | | 102 | Carol | 8500 | 1 | [^3] ##### 3. **窗口范围控制（`ROWS/RANGE`）** 精确控制计算涉及的行范围： | **范围定义** | **含义** | |--------------------------------------|------------------------------| | `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` | 从分区首行到当前行（累计计算） | | `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING` | 当前行前1行到后1行（滑动窗口） | | `RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` | 整个分区（默认）[^1] | **示例**：计算累计销售额 ```sql SELECT order_date, daily_sales, SUM(daily_sales) OVER(ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales FROM sales_data; ``` --- #### 三、常用窗口函数类型 | **函数类别** | **典型函数** | **用途** | |--------------------|----------------------------------|-------------------------------| | 聚合函数 | `SUM()`, `AVG()`, `COUNT()` | 分区内聚合计算（保留明细行） | | 排名函数 | `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()` | 组内排序编号 | | 分析函数 | `LEAD()`, `LAG()`, `FIRST_VALUE()` | 跨行数据访问（如前/后行值） | --- #### 四、`OVER()` vs `GROUP BY` | **特性** | `OVER()` | `GROUP BY` | |---------------------------|---------------------------------|--------------------------| | 输出行数 | 保留所有原始行 | 每组仅一行（聚合结果） | | 计算粒度 | 行级计算 | 组级计算 | | 能否混合原始列与聚合值 | ✅ 是（如`SUM() OVER()`） | ❌ 需搭配`GROUP BY`所有非聚合列 | > ✅ **适用场景**： > - 需保留明细行的聚合计算（如计算占比、累计值） > - 组内排名（如销售TOP N） > - 时间序列分析（如移动平均） --- ### 总结 `OVER()`的核心价值在于： 1. **灵活分区**：通过`PARTITION BY`定义计算范围而不折叠数据 2. **动态排序**：结合`ORDER BY`实现组内有序计算（如排名） 3. **精细控制**：`ROWS/RANGE`精确指定计算窗口 4. **高效分析**：单次扫描完成复杂计算（如累计值、滑动平均） > 支持数据库：SQL Server, Oracle, PostgreSQL, MySQL 8.0+, BigQuery等
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值