目錄

• 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 FROMSCOTT.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()函數內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！