oracle over的用法;

傅老师课堂:Oracle高级查询之OVER (PARTITION BY ..) 为了方便大家学习和测试,所有的例子都是在Oracle自带用户Scott下建立的。 注:标题中的红色order by是说明在使用该方法的时候必须要带上order by。 一、rank()/dense_rank() over(partition by ...order by ...) 现在客户有这样一个需求,查询每个部门工资最高的雇员的信息,相信有一定oracle应用知识的同学都能写出下 阅读详情

对于通过OVER来做聚合,已经基本掌握了。但是要详细解释其原理,还是感觉有些说不清晰。所以只能结合实例来看了。在我们的查询中用到了一张表SalesOrder。下面是表结构和创建该表的T-SQL语句。

字段名称数据类型说明
SalesOrderIDbigintidentity primary key
SalesIDbigintnot null
CustomerIDbigintnot null
SalesValuedecimal(18,2) 
OrderDatedate 
?
创建SalesOrder表的TSQL
1
2
3
4
5
6
7
8
9
10
11
12
IF OBJECT_ID(N 'dbo.SalesOrder' ,N 'U' ) IS NOT NULL
   DROP TABLE dbo.SalesOrder
GO
CREATE TABLE [dbo].[SalesOrder]
(
     [SalesOrderID] [ bigint ] IDENTITY(1,1) NOT NULL Primary Key ,
     [SalesID] [ bigint ] NOT NULL ,
     [CustomerID] [ bigint ] NOT NULL ,
     [SalesValue] [ decimal ](18, 2) NOT NULL ,
     [OrderDate] [ date ] NOT NULL ,
)
GO

首先说聚集函数。聚集函数是指那些对一组数据进行运算,然后输出标量值的函数。例如SUM,COUNT,AVG,MIN和MAX等等。我们通常将聚集函数和GROUP BY子句联合使用。例如,下面的T-SQL可以用来查询[每人每天的销售额占当天销售额的比例]

?
使用CTE和Join的查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH SP AS
(
   --计算每天的销售额总和
   SELECT    OrderDate, SUM (SalesValue) AS SumSales
   FROM      dbo.[SalesOrder]
   GROUP BY  OrderDate
)
SELECT  O.OrderDate,O.SalesID,
         O.SalesValue AS PersonalDailySales,
         SP.SumSales AS DailyTotalSales,
         CONVERT ( decimal (18,2),100*O.SalesValue/SP.SumSales)
       AS PersonalContribution
FROM    dbo.[SalesOrder] O
JOIN SP ON O.OrderDate=SP.OrderDate

 

在上面的查询中,CTE表达式通过在OrderDate上做Group By操作,来实现按天统计销售额的功能。然后将CTE和SalesOrder表做Join 才能取得最后的结果。这个查询看起来有点复杂,读起来也不直观。使用OVER子句,就是为了简化这种TSQL写法。

必须要说明的是,OVER有好几种用法,这里列举的只是其中一种:在SELECT阶段和聚集函数合作。以前一直以为聚集函数只能和Group By合作。其实也可以和OVER合作。OVER的本质是定义了一个窗口函数。实质上就是将数据行划分为组。这样就和聚集函数的要求匹配上了。

下面的TSQL和上面给出的TSQL执行效果是一样的。而且在SQL SERVER中显示查询计划也完全一样。

?
使用OVER的查询
1
2
3
4
5
6
7
SELECT  OrderDate,SalesID,
     SalesValue as PersonalDailySales,
     SUM (SalesValue) OVER(PARTITION BY OrderDate) AS DailyTotalSales,
     CONVERT ( decimal (18,2),
         100*SalesValue/ SUM (SalesValue) OVER(PARTITION BY OrderDate))
       AS PersonalContribution
FROM    dbo.[SalesOrder]
**********************************************

sql over的作用及用法

RANK ( ) OVER ( [query_partition_clause] order_by_clause )

--当OVER 后的内容一样是,编号一样,即会重复

或者是

row_number() 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的值进行分组排序。

例如:employees表中,有两个部门的记录:department_id =10和20
select department_id,rank() over(partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名,在部门20中进行薪水排名。如果是partition by org_id,则是在整个公司内进行排名。

以下是个人见解:

sql中的over函数和row_numbert()函数配合使用,可生成行号。可对某一列的值进行排序,对于相同值的数据行进行分组排序。如下表:

sql over - 李世林 - 追求

执行语句:select row_number() over(order by AID DESC) as rowid,* from bb后的结果如下:

sql over - 李世林 - 追求

rowid标识行号有了,同时AID也按降序排列。AID有重复的记录,如果要删除rowid为2所对应的记录则可以:

with [a] as

(select row_number() over(order by AID desc) as rowid,* from bb)

delete from [a] where rowid=2

如果查看rowid 为5所对应的记录的信息,可以:

with [b] as

(select row_number() over(order by AID desc) as rowid,* from bb)

select * from [b] where rowid=5

注意:

over里的order只能查查询里的原始数据进行操作,不会对计算出的新值或新字段起作用。

msdn中的说法如下:

<ORDER BY 子句> 只能引用通过 FROM 子句可用的列。<ORDER BY 子句>不能与聚合窗口函数一起使用。

**********************************************************************************************************************************************************************

PLSQL中over(partition by .. order by ..)的使用

SQL> select deptno,ename,sal
   2   from emp
   3   order by deptno;

DEPTNO ENAME          SAL
---------- ---------- ----------
       10 CLARK          2450
          KING          5000
          MILLER           1300

       20 SMITH          800
          ADAMS          1100
          FORD          3000
          SCOTT          3000
          JONES          2975

       30 ALLEN          1600
          BLAKE          2850
          MARTIN           1250
          JAMES          950
          TURNER           1500
          WARD          1250


已选择14行。

 

2.先来一个简单的,注意over(...)条件的不同,
使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,
注意over (order   by ename)如果没有order by 子句,求和就不是“连续”的,
放在一起,体会一下不同之处:

SQL> select deptno,ename,sal,
   2   sum(sal) over (order by ename) 连续求和,
   3   sum(sal) over () 总和,                -- 此处sum(sal) over () 等同于sum(sal)
   4   100*round(sal/sum(sal) over (),4) "份额(%)"
   5   from emp
   6   /

DEPTNO ENAME          SAL 连续求和    总和 份额(%)
---------- ---------- ---------- ---------- ---------- ----------
       20 ADAMS          1100    1100    29025    3.79
       30 ALLEN          1600    2700    29025    5.51
       30 BLAKE          2850    5550    29025    9.82
       10 CLARK          2450    8000    29025    8.44
       20 FORD          3000    11000    29025    10.34
       30 JAMES          950    11950    29025    3.27
       20 JONES          2975    14925    29025    10.25
       10 KING          5000    19925    29025    17.23
       30 MARTIN           1250    21175    29025    4.31
       10 MILLER           1300    22475    29025    4.48
       20 SCOTT          3000    25475    29025    10.34
       20 SMITH          800    26275    29025    2.76
       30 TURNER           1500    27775    29025    5.17
       30 WARD          1250    29025    29025    4.31

已选择14行。


3.使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同,
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno,ename) 不按部门“连续”求总和
sum(sal) over () 不按部门,求所有员工总和,效果等同于sum(sal)。

SQL> select deptno,ename,sal,
   2   sum(sal) over (partition by deptno order by ename) 部门连续求和,--各部门的薪水"连续"求和
   3   sum(sal) over (partition by deptno) 部门总和,   -- 部门统计的总和,同一部门总和不变
   4   100*round(sal/sum(sal) over (partition by deptno),4) "部门份额(%)",
   5   sum(sal) over (order by deptno,ename) 连续求和, --所有部门的薪水"连续"求和
   6   sum(sal) over () 总和,   -- 此处sum(sal) over () 等同于sum(sal),所有员工的薪水总和
   7   100*round(sal/sum(sal) over (),4) "总份额(%)"
   8   from emp
   9   /

DEPTNO ENAME SAL 部门连续求和 部门总和 部门份额(%) 连续求和 总和   总份额(%)
------ ------ ----- ------------ ---------- ----------- ---------- ------ ----------
10 CLARK 2450       2450    8750       28    2450   29025    8.44
   KING 5000       7450    8750    57.14    7450   29025    17.23
   MILLER   1300       8750    8750    14.86    8750   29025    4.48

20 ADAMS 1100       1100    10875    10.11    9850   29025    3.79
   FORD 3000       4100    10875    27.59    12850   29025    10.34
   JONES 2975       7075    10875    27.36    15825   29025    10.25
   SCOTT 3000        10075    10875    27.59    18825   29025    10.34
   SMITH 800        10875    10875        7.36    19625   29025    2.76

30 ALLEN 1600       1600    9400    17.02    21225   29025    5.51
   BLAKE 2850       4450    9400    30.32    24075   29025    9.82
   JAMES 950       5400    9400    10.11    25025   29025    3.27
   MARTIN   1250       6650    9400        13.3    26275   29025    4.31
   TURNER   1500       8150    9400    15.96    27775   29025    5.17
   WARD 1250       9400    9400        13.3    29025   29025    4.31


已选择14行。

 

4.来一个综合的例子,求和规则有按部门分区的,有不分区的例子
SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
   2   sum(sal) over (order by deptno,sal) sum
   3   from emp;

DEPTNO ENAME          SAL DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
       10 MILLER           1300    1300    1300
          CLARK          2450    3750    3750
          KING          5000    8750    8750

       20 SMITH          800        800    9550
          ADAMS          1100    1900    10650
          JONES          2975    4875    13625
          SCOTT          3000    10875    19625
          FORD          3000    10875    19625

       30 JAMES          950        950    20575
          WARD          1250    3450    23075
          MARTIN           1250    3450    23075
          TURNER           1500    4950    24575
          ALLEN          1600    6550    26175
          BLAKE          2850    9400    29025


已选择14行。

 

5.来一个逆序的,即部门从大到小排列,部门里各员工的薪水从高到低排列,累计和的规则不变。

SQL> select deptno,ename,sal,
   2   sum(sal) over (partition by deptno order by deptno desc,sal desc) dept_sum,
   3   sum(sal) over (order by deptno desc,sal desc) sum
   4   from emp;

DEPTNO ENAME          SAL DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
       30 BLAKE          2850    2850    2850
          ALLEN          1600    4450    4450
          TURNER           1500    5950    5950
          WARD          1250    8450    8450
          MARTIN           1250    8450    8450
          JAMES          950    9400    9400

       20 SCOTT          3000    3000    15400            3000    6000   15400
          FORD          3000    6000    15400
          JONES          2975    8975    18375
          ADAMS          1100    10075    19475
          SMITH          800    10875    20275

       10 KING          5000    5000    25275
          CLARK          2450    7450    27725
          MILLER           1300    8750    29025


已选择14行。

 

6.体会:在"... from emp;"后面不要加order   by 子句,使用的分析函数的(partition by deptno order by sal)
里已经有排序的语句了,如果再在句尾添加排序子句,一致倒罢了,不一致,结果就令人费劲了。如:

SQL> select deptno,ename,sal,sum(sal) over (partition by deptno order by sal) dept_sum,
   2   sum(sal) over (order by deptno,sal) sum
   3   from emp
   4   order by deptno desc;

DEPTNO ENAME          SAL DEPT_SUM        SUM
---------- ---------- ---------- ---------- ----------
       30 JAMES          950        950    20575
          WARD          1250    3450    23075
          MARTIN           1250    3450    23075
          TURNER           1500    4950    24575
          ALLEN          1600    6550    26175
          BLAKE          2850    9400    29025

       20 SMITH          800        800    9550
          ADAMS          1100    1900    10650
          JONES          2975    4875    13625
          SCOTT          3000    10875    19625
          FORD          3000    10875    19625

       10 MILLER           1300    1300    1300
          CLARK          2450    3750    3750
          KING          5000    8750    8750


已选择14行


Oracle 中rank() over()的用法 原文地址:http://www.cnblogs.com/mycoding/archive/2010/05/29/1747065.html ---------------------------------------- 创建一个test表,并插入6条数据。 CREATE TABLE test ( a INT, b INT, c CHAR ) INSERT INTO 阅读详情

相关推荐

网易 广州 梦幻西游端游 客户端社招笔试题 2024

4道算法题,时间2小时,牛客网笔试注:采用OJ模式,需要自己处理输入、输出。

z2014z的博客 1824

Oracle的over() 函数使用

Oracle的over() 函数使用: 表及数据: Sql代码   "FONT-SIZE: large">create table STUDENT   (     STUDENT_ID   NUMBER not null,     STUDENT_NAM

xiaobluesky的专栏 460

告别云端推理!用llama.cpp+树莓派5搭建本地AI问答机器人(完整配置流程)

本文详细介绍了如何在树莓派5上利用llama.cpp部署本地AI问答机器人,实现完全离线的智能对话。通过模型量化与格式转换技术,成功将MiniCPM-2B等大模型压缩并运行于资源受限的边缘设备,解决了隐私、延迟和成本问题,为物联网和硬件开发者提供了完整的配置流程与实践指南。

t1u2v的博客 509

SQL窗口函数OVER的使用

OVER的定义 OVER用于为行定义一个窗口,它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。 OVER的语法 OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] ) PARTITION BY 子句进行分组; ORDER BY 子句进行排序。 窗口函数OVER()指定一组行,开窗函数计算从窗口函数输出的结果集中各行的值。 开窗函数不需要使用GROUP BY就可以对数据进行分组,还可以同时返回基础行的

weixin_44889014的博客 703

【SPSS实战】手动实现Quade非参数协方差分析:从数据准备到结果解读

本文详细介绍了在SPSS中手动实现Quade非参数协方差分析的实战方法。当数据不满足正态分布等参数检验假设时,通过将协变量和因变量转换为秩,并利用秩回归残差进行Kruskal-Wallis组间比较,提供了一种清晰、可操作的近似解决方案。该方法有效解决了非正态数据的协变量调整问题,适用于实验研究和调查数据分析。

bean的博客 469

【自然语言处理】情感分析(一):基于 NLTK 的 Naive Bayes 实现

朴素贝叶斯(Naive Bayes)分类器可以用来确定输入文本属于某一组类别的概率。例如,预测评论是正面的还是负面的。它是 “朴素的”,它假设文本中的单词是独立的(但在现实的自然人类语言中,单词的顺序传达了上下文信息)。尽管有这些假设,但朴素贝叶斯在使用少量训练集预测类别时具有很高的准确性。

Data · Model · Agent · Repeat 2173

oracle within over,分析函数over 后面如何加where 条件

看到:http://topic.youkuaiyun.com/t/20050407/17/3917652.html的问题如下:有一个职员的数据表,字段如下:Name, Salary, AgeA,900, 20B,900, 21C,1000, 18D,950, 20现在我想做一个报表,用到了sum() over(),SQL如下:select Name, Salary, s...

weixin_32511247的博客 1100

【020】C++ 动态内存管理解析:new 和 delete 的正确使用

C++中的动态内存分配是在程序运行时根据需要进行内存空间的分配和释放,与静态内存分配相比,具有更大的灵活性和适应性。动态内存分配可以通过new和delete或者malloc()和free()等函数实现。在程序运行时无法确定所需的内存大小。需要在多个函数之间共享数据,并且数据可能被频繁地创建和销毁。静态数组无法满足需求,例如需要支持可变长度数组。如果在动态分配内存后忘记了释放它,则会导致内存泄漏。为避免内存泄漏,可以使用智能指针或手动管理内存的方式来释放动态分配的内存。

Lion_Long的博客 1471

oracle 查询 over,Oracle高级查询之OVER

注释:为了方便大家学习和测试,所有的例子都是在Oracle自带用户Scott下建立的oracel的高级用法:rank()/dense_rank() over(partition by ...order by ...)现在客户有这样一个需求,查询每个部门工资最高的雇员的信息方法一:常用方法select e.ename, e.job, e.sal, e.deptnofrom scott.emp e,(...

weixin_33326218的博客 478

Open3D 从体素网格构建八叉树【2025最新版】

从体素网格构建八叉树,博客长期更新,本文最近更新时间为:2025年1月1日。

点云侠的博客 34万+

【知识分享】单端到混合S参数转换指南

信号完整性(SI)工程师几乎总是必须使用S参数,因为许多的高速接口和行业标准都正在转向串行链路架构,并使用基于S参数测量的频域限制指标,了解其工作过程将会很有意义。

一只豌豆象的博客 1581

oracle中over()方法的用法

SQL> select deptno,ename,sal 2 from emp 3 order by deptno; DEPTNO ENAME SAL 10 CLARK 2450 KING 5000 MILLER 1300 20 SMITH 80...

weixin_38392079的博客 2万+

手把手教你学 GPU KMD--1.2:UMD、KMD 与 DDK 的协作关系(下)——权限提升与安全边界

本文探讨了UMD、KMD与DDK协作中的权限提升与安全边界问题。GPU作为强大DMA引擎,必须通过分层权限模型(用户态/内核态)隔离风险,KMD作为守门人对UMD请求进行严格校验。文章详细分析了权限提升路径、安全边界设计(GPU虚拟内存隔离、命令白名单、IOMMU强制隔离等),并列举历史漏洞案例说明安全边界的重要性。现代增强方案包括虚拟化与可信执行环境。核心安全原则是"永远不信任用户态数据",需对所有地址、命令、资源和DMA进行严格管控。

amy_mhd的博客 325

ORACLE 中的 ROW_NUMBER() OVER() 分析函数的用法

ORACLE 中的 ROW_NUMBER() OVER() 分析函数的用法 ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内是连续且唯一的)。 举例: SQL> DESC T1; Name

iw1210的专栏 8万+

NTscan扫描工具

ntscan是一个扫描器,可以扫描没有密码和弱密码的共享,先到配置文件中填写猜测的用户名和密码,扫描过程中右侧出现的是扫描日志。

温度PID控制PLC程序

温度PID稳定控制程序,适用于欧姆龙PLC,对其他PLC也具有很大参考价值

oracle中over()分析函数的用法

本文详细介绍了Oracle中OVER()分析函数的用法。该函数可与SUM()、RANK()等函数结合使用,通过PARTITION BY进行分组统计,ORDER BY实现累计计算。文章以scott用户的emp表为例,演示了基础用法、累计求和、分级排名等场景,并提供了综合实战示例(如ROW_NUMBER()排名和LAG()差值计算)和常见面试题解析。OVER()函数能显著增强SQL的数据分析能力,适用于薪酬分析、销售趋势等多种业务场景,是Oracle数据库中强大的分析工具。

ywl470812087的博客 11万+

Devexpress16.2.6破解工具

Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具Devexpress16.2.6破解工具

ORACLE-over()函数用法

ORACLE-over()函数用法 select annual_rate, invest_amount, self_amt from busi_order_bak; SQL> select annual_rate, invest_amount, self_amt from busi_order_bak;ANNUAL_RATE INVEST_AMOUNT SELF_AMT

天籁 1594

计算机控制技术及应用

《计算机控制技术及应用》通过总结作者的科研成果和吸收国内外的先进理论、方法和技术,以计算机控制系统的网络化、开放化、智能化和集成化发展趋势为主线索,主要介绍计算机控制系统的基本概念和计算机控制系统的典型形式,计算机控制系统的数学描述与基本分析方法,以及数字PID控制器、最少拍控制系统、达林算法、状态变量设计法等设计方法及其各种改进算法。重点突出计算机控制系统输入输出通道接口的设计方法和典型控制系统的设计案例与实现方法等。

oracle over,Oracle over() 函数的实际用法

以下的文章主要介绍的是Oracleover() 函数的实际用法,在一个网站上我无意中看见了这个函数,才对Oracle分析函数的相关内容有所了解,而且Oracle over() 函数的实际用法又尤为的特别,所以将自己的研究结果记录一下。个人理解:over() 函数是对分析函数的一种条件解释,直接点就是 给分析函数加条件吧。在网上看见比较常用的就是 与 sum()、rank() 函数使用。接下来就用分...

weixin_28677005的博客 239

【Oracle】Over函数的用法

一、问题,如果一张表中有n个数据,分别属于a个小组,现在我们要求每个组里某个【字段】的最大值,最小值,和,差等,该怎么操作? 举个具体点的例子,有一个表名为demo的oracle数据库表,有字段如ID【id】,名字【name】,年龄【age】,国家【country】4个字段,我们要求不同的国家,最大年纪的人的信息; demo表的建表语句如下: create table DEMO( id varch...

陶洲川的博客 5783

Oracle中分析函数Over、partition的用法

一、常用函数结构整理 row_number() over(partition by … order by …) rank() over(partition by … order by …) dense_rank() over(partition by … order by …) count() over(partition by … order by …) max() over(partitio...

weixin_40939076的博客 3411

oracle over的用法

oracle wm_concat(column)实现字段合并

sujiangliu的专栏 887

Oracle开窗函数over()的用法

Oracle开窗函数over()的用法

weixin_45936406的博客 2457

oracle within over,Oracle分析函数OVER的用法

Oracle分析函数OVER使用方法举例---创建测试表SQL> desc t_test;名称 是否为空? 类型----------------------------------------- -------- --------------------------T_ID ...

weixin_36087577的博客 211

oracle 分析函数之 over 用法

Oracle 语法之 OVER (PARTITION BY ..)select * from test数据:A B C 1 1 1 1 2 2 1 3 3 2 2 5 3 4 6 ---将B列值相同的对应的C 列值加总select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sumfrom testA B C C_SUM 1 1 1 1 1 2 2 7 2 2

枫$叶的专栏 5391

oracle SQL中 over 的用法

在最近的oracle学习中,看到了over这个函数的用法,以前看到这个是很回避,因为搞不懂为什么要用over这个子句。今天用了一下,其实也不是我想象的那么难: 例子:create table tt (stud_id number(18), class integer, grade number(5,2));insert into tt(stud_id, class, grade) values(1

CygX_1的专栏 1685

oracle+中over关键字,Oracle 中Over的用法

Oracle 中Over的用法更新时间:2017/2/2 7:43:00浏览量:635手机版随便GOOGLE一把ORACLE分析函数,就能找到很多Oracle 分析函数的使用Oracle从8.1.6开始提供分析函数,分析函数用于计算基于组的某种聚合值,它和聚合函数的不同之处是对于每个组返回多行,而聚合函数对于每个组只返回一行。下面例子中使用的表来自Oracle自带的HR用户下的表,如果没有...

weixin_35476698的博客 352
上一篇: Log4J输出日志到WEB工程目录的实现方法
下一篇: cmd取UTF-8编码的数据显示乱码
zljjava
博客等级 码龄15年 155粉丝 15原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明:

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

余额充值