Oracle中ROW_NUMBER()OVER()函數(shù)用法實(shí)例講解

Oracle中ROW_NUMBER() OVER()函數(shù)用法

1. 說明：

ROW_NUMBER() OVER() 函數(shù)的作用：分組排序

2. 原理：

row_number() over() 函數(shù)，over() 里的分組以及排序的執(zhí)行晚于 where、group by、order by 的執(zhí)行。

3.語法：

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

4.示例一：

查詢表：SELECT * FROM SCOTT.EMP ;

使用Row_number() over() 函數(shù),排序

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

根據(jù)工資排序并添加序號(hào)

5. 示例二

1.建立測(cè)試學(xué)生數(shù)據(jù)表

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

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

2.查學(xué)生數(shù)據(jù)根據(jù)班級(jí)分組，再根據(jù)分?jǐn)?shù)排名。獲取到每個(gè)班級(jí)的學(xué)生分?jǐn)?shù)排名

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

3. 獲取到每個(gè)班級(jí)分?jǐn)?shù)排名第一的學(xué)生

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

重點(diǎn)說明：

例如：

將學(xué)生1的分?jǐn)?shù)設(shè)置為 null，再獲取到分組班級(jí)的學(xué)生分?jǐn)?shù)排名

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

修改學(xué)生一在該班級(jí)的排序，分?jǐn)?shù)最低排最后即可修正這個(gè)問題

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

2. 分析函數(shù)的例子二：

2.1 分析函數(shù)的形式：

分析函數(shù)帶有一個(gè)開窗函數(shù)over(),包含三個(gè)分析子句：分組(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

分析函數(shù)的例子：

顯示各部門員工的工資，并附帶顯示該部分的最高工資。

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 表示當(dāng)前行

unbounded preceding 表示第一行

unbounded following 表示最后一行

last_value(sal) 的結(jié)果與 order by sal 排序有關(guān)。如果排序?yàn)閛rder by sal desc, 則最終的結(jié)果為分組排序后sal的最小值(分組排序后的最后一個(gè)值), 當(dāng)deptno為10時(shí)，max_sal為1300

2.2 兩個(gè)order by 的執(zhí)行機(jī)制

分析函數(shù)是在整個(gè)SQL查詢結(jié)束后(SQL語句中的order by 的執(zhí)行比較特殊)再進(jìn)行的操作，也就是說SQL語句中的order by也會(huì)影響分析函數(shù)的執(zhí)行結(jié)果：

兩者一致：如果SQL語句中的order by 滿足分析函數(shù)分析時(shí)要求的排序，那么SQL語句中的排序?qū)⑾葓?zhí)行，分析函數(shù)在分析時(shí)就不必再排序。
兩者不一致：如果SQL語句中的order by 不滿足分析函數(shù)分析時(shí)要求的排序，那么SQL語句中的排序?qū)⒆詈笤诜治龊瘮?shù)分析結(jié)束后執(zhí)行排序。

2.3 分析函數(shù)中的分組、排序、窗口

分析函數(shù)包含三個(gè)分析子句：分組(partition by)、排序(order by)、窗口(rows)。

窗口就是分析函數(shù)分析時(shí)要處理的數(shù)據(jù)范圍，就拿sum來說，它是sum窗口中的記錄而不是整個(gè)分組中的記錄。因此我們?cè)谙氲玫侥硞€(gè)欄位的累計(jì)值時(shí)，我們需要把窗口指定到該分組中的第一行數(shù)據(jù)到當(dāng)前行，如果你指定該窗口從該分組中的第一行到最后一行，那么該組中的每一個(gè)sum值都會(huì)一樣，即整個(gè)組的總和。

窗口子句中我們經(jīng)常用到指定第一行，當(dāng)前行，最后一行這樣的三個(gè)屬性：

第一行是 unbounded preceding

當(dāng)前行是 current row

最后一行是 unbounded following

窗口子句不能單獨(dú)出現(xiàn)，必須有order by 子句時(shí)才能出現(xiàn)，如：

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

以上示例指定窗口為整個(gè)分組.

而出現(xiàn)order by 子句的時(shí)候，不一定要有窗口子句，但效果會(huì)不一樣，此時(shí)窗口默認(rèn)是當(dāng)前組的第一行到當(dāng)前行！

SQL語句為：

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

等價(jià)于：

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;

結(jié)果如下圖：

當(dāng)省略窗口子句時(shí)：

• 如果存在order by, 則默認(rèn)的窗口是 unbounded preceding and current row.
• 如果同時(shí)省略order by, 則默認(rèn)的窗口是 unbounded preceding and unbounded following.

如果省略分組，則把全部記錄當(dāng)成一個(gè)組：

• 如果存在order by 則默認(rèn)窗口是unbounded preceding and current row
• 如果這時(shí)省略order by 則窗口默認(rèn)為 unbounded preceding and unbounded following

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

總結(jié)

關(guān)于Oracle中ROW_NUMBER()OVER()函數(shù)用法的文章就介紹至此，更多相關(guān)Oracle中ROW_NUMBER()OVER()函數(shù)內(nèi)容請(qǐng)搜索碩編程以前的文章，希望以后支持碩編程！