Oracle中ROW_NUMBER()OVER()函数用法实例讲解

目录
  • 1. 说明:
  • 2. 原理:
  • 3.语法:
  • 4.示例一:
  • 5. 示例二
  • 总结

Oracle中ROW_NUMBER() OVER()函数用法

1. 说明:

ROW_NUMBER() OVER() 函数的作用:分组排序

2. 原理:

row_number() over() 函数,over() 里的分组以及排序的执行晚于 where、group by、order by 的执行。

3.语法:

row_number() over( partition by 分组列 order by 排序列 desc )

4.示例一:

查询表:SELECT * FROM SCOTT.EMP ;

使用Row_number() over() 函数,排序

SELECT EMPNO,ENAME,SAL,DEPTNO,Row_number() over( order by sal) rs FROM
SCOTT.EMP ;

根据工资排序并添加序号

5. 示例二

1.建立测试学生数据表

create table Students
(
id int,
name varchar2(100),
classid int,
score int
);

insert into Students values(1, '学生1', 1, 88);
insert into Students values(2, '学生2', 3, 68);
insert into Students values(3, '学生3', 1, 78);
insert into Students values(4, '学生4', 2, 87);
insert into Students values(5, '学生5', 1, 89);
insert into Students values(6, '学生6', 2, 91);
insert into Students values(7, '学生7', 3, 67);
insert into Students values(8, '学生8', 1, 77);
insert into Students values(9, '学生9', 3, 77);
commit;

2.查学生数据根据班级分组,再根据分数排名。获取到每个班级的学生分数排名

select id, name, classid, score, row_number() over(partition by classid order by score desc) rank from Students;

3. 获取到每个班级分数排名第一的学生

select * from (select id, name, classid, score, row_number() over(partition by classid order by score desc) rank from Students) where rank = 1;

重点说明:

  1. parttion by 是 Oracle 中分析性函数的一部分,用于给结果集进行分区,它和聚合函数 group by
    不同的地方在于它只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而 group by
    是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。
  2. over() 必须有 ORDER BY 语句
  3. 分组内从 1开始排序
  4. over() 中的排序字段为空,会被排到第一

例如:

将学生1的分数设置为 null,再获取到分组班级的学生分数排名

select id, name, classid, score, row_number() over(partition by classid order by score desc) rank from Students;

修改学生一在该班级的排序,分数最低排最后即可修正这个问题

select id, name, classid, score, row_number() over(partition by classid order by score desc nulls last) rank from Students;

2. 分析函数的例子二:

2.1 分析函数的形式:

分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by),排序(order by), 窗口(rows),他们的使用形式如下:

over(partition by xxx order by yyy rows between zzz)
– 例如在scott.emp表中:xxx为deptno, yyy为sal,
– zzz为unbounded preceding and unbounded following

分析函数的例子:

显示各部门员工的工资,并附带显示该部分的最高工资。

SQL如下:

SELECT DEPTNO, EMPNO, ENAME, SAL, LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO
ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
FROM EMP;

注: current row 表示当前行

    unbounded preceding  表示第一行

    unbounded following    表示最后一行

last_value(sal) 的结果与 order by sal 排序有关。如果排序为order by sal desc, 则最终的结果为分组排序后sal的最小值(分组排序后的最后一个值), 当deptno为10时,max_sal为1300

2.2 两个order by 的执行机制

分析函数是在整个SQL查询结束后(SQL语句中的order by 的执行比较特殊)再进行的操作,也就是说SQL语句中的order by也会影响分析函数的执行结果:

两者一致:如果SQL语句中的order by 满足分析函数分析时要求的排序,那么SQL语句中的排序将先执行,分析函数在分析时就不必再排序。
两者不一致:如果SQL语句中的order by 不满足分析函数分析时要求的排序,那么SQL语句中的排序将最后在分析函数分析结束后执行排序。

2.3 分析函数中的分组、排序、窗口

分析函数包含三个分析子句:分组(partition by)、排序(order by)、窗口(rows)。

窗口就是分析函数分析时要处理的数据范围,就拿sum来说,它是sum窗口中的记录而不是整个分组中的记录。因此我们在想得到某个栏位的累计值时,我们需要把窗口指定到该分组中的第一行数据到当前行,如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样,即整个组的总和。

窗口子句中我们经常用到指定第一行,当前行,最后一行这样的三个属性:

第一行是 unbounded preceding

当前行是 current row

最后一行是 unbounded following

窗口子句不能单独出现,必须有order by 子句时才能出现,如:

LAST_VALUE(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )

以上示例指定窗口为整个分组.

而出现order by 子句的时候,不一定要有窗口子句,但效果会不一样,此时窗口默认是当前组的第一行到当前行!

SQL语句为:

SELECT DEPTNO, EMPNO, ENAME, SAL,
last_value(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) MAX_SAL FROM EMP;

等价于:

SELECT DEPTNO, EMPNO, ENAME, SAL,last_value(SAL) OVER(PARTITION BY DEPTNO
ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) MAX_SAL FROM EMP;

结果如下图:

当省略窗口子句时:

  • 如果存在order by, 则默认的窗口是 unbounded preceding and current row.
  • 如果同时省略order by, 则默认的窗口是 unbounded preceding and unbounded following.

如果省略分组,则把全部记录当成一个组:

  • 如果存在order by 则默认窗口是unbounded preceding and current row
  • 如果这时省略order by 则窗口默认为 unbounded preceding and unbounded following

可参考:https://www.jb51.net/article/282335.htm

总结

到此这篇关于Oracle中ROW_NUMBER()OVER()函数用法的文章就介绍到这了,更多相关Oracle中ROW_NUMBER()OVER()函数内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Oracle row_number() over()解析函数高效实现分页

    复制代码 代码如下: create table T_NEWS ( ID NUMBER, N_TYPE VARCHAR2(20), N_TITLE VARCHAR2(30), N_COUNT NUMBER ) prompt Disabling triggers for T_NEWS... alter table T_NEWS disable all triggers; prompt Loading T_NEWS... insert into T_NEWS (ID, N_TYPE, N_TITLE,

  • 深入探讨:oracle中row_number() over()分析函数用法

    row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的). 与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序,而此函数在包含排序从句后是先排序再计算行号码. row_number()和rownum差不多,功能更强一点(可以在各个分组内从1开始排序). rank()是跳跃排序,有两个第二名

  • Oracle中ROW_NUMBER()OVER()函数用法实例讲解

    目录 1. 说明: 2. 原理: 3.语法: 4.示例一: 5. 示例二 总结 Oracle中ROW_NUMBER() OVER()函数用法 1. 说明: ROW_NUMBER() OVER() 函数的作用:分组排序 2. 原理: row_number() over() 函数,over() 里的分组以及排序的执行晚于 where.group by.order by 的执行. 3.语法: row_number() over( partition by 分组列 order by 排序列 desc )

  • C#中异步回调函数用法实例

    本文实例讲述了C#中异步回调函数用法.分享给大家供大家参考.具体如下: static void Main(string[] args) { Func<string,string> showMessage = ShowMessage; //设置了回调函数Completed,不能有返回值 IAsyncResult result = showMessage.BeginInvoke("测试异步委托",new AsyncCallback(Completed),null); //半段异

  • vc中SendMessage自定义消息函数用法实例

    本文实例讲述了vc中SendMessage自定义消息函数用法,分享给大家供大家参考.具体如下: SendMessage的基本结构如下: 复制代码 代码如下: SendMessage(     HWND hWnd,  //消息传递的目标窗口或线程的句柄.     UINT Msg, //消息类别(这里可以是一些系统消息,也可以是自己定义,下文具体介绍,)     WPARAM wParam, //参数1 (WPARAM 其实是与UINT是同种类型的,   //在vc编译器中右键有个"转到WPARA

  • Oracle中dbms_output.put_line的用法实例

    目录 1.enable: 2.disable: 3.put: 4.put_line: 5.new_line: 6.get_line(value, index): 7.get_lines(array, index): 总结 dbms_output.put_line的用法 涉及到的知识点如下: 1.enable: 在serveroutput on的情况下,用来使dbms_output生效(默认即打开) set serveroutput on --将output 服务打开 2.disable: 在se

  • PHP fopen函数用法实例讲解

    php fopen()函数用于打开文件或者 URL. php fopen()函数 语法 作用:打开文件或者 URL. 语法: fopen(filename,mode,include_path,context) 参数: filename 必需.规定要打开的文件或 URL. mode 必需.规定要求到该文件/流的访问类型. include_path 可选.如果也需要在 include_path 中检索文件的话,可以将该参数设为 1 或 TRUE. context 可选.规定文件句柄的环境.Conte

  • 基于多线程中join()的用法实例讲解

    Thread中,join()方法的作用是调用线程等待该线程完成后,才能继续用下运行. public class TestThread5 { public static void main(String[] args) throws InterruptedException { Runner0 run5 = new Runner0(); Thread th5 = new Thread(run5); th5.start(); th5.join();//join()方法用在此处是为了等待主线程结束后运

  • Oracle中的translate函数和replace函数的用法详解

    translate函数语法: translate(expr, from_strimg, to_string) 简介: translate返回expr,其中from_string中的每个字符的所有出现都被to_string中的相应字符替换.expr中不在from_string中的字符不会被替换.如果expr是一个字符串,那么你必须把它放在单引号中. from_string的参数可以包含比to_string更多的字符.在这种情况下,from_string末尾的多余字符在to_string中没有对应的

  • Oracle中case when函数的用法

    一.case when 的基本用法见: PL/SQL 使用控制流程 二.case when在select语句中的用法 SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 ELSE NULL END) 男生数, COUNT (CASE WHEN sex = 2 THEN 1 ELSE NULL END) 女生数 FROM students GROUP BY grade; 1.在Case函数中,可以使用BETWEEN,LIKE,IS NULL,IN,EXIST

  • Oracle中游标Cursor基本用法详解

    查询 SELECT语句用于从数据库中查询数据,当在PL/SQL中使用SELECT语句时,要与INTO子句一起使用,查询的 返回值被赋予INTO子句中的变量,变量的声明是在DELCARE中.SELECT INTO语法如下: SELECT [DISTICT|ALL]{*|column[,column,...]} INTO (variable[,variable,...] |record) FROM {table|(sub-query)}[alias] WHERE............ PL/SQL

随机推荐