Oracle 分析函數 ROW_NUMBER() 使用

標籤：彙總   統計   https   group by   有一個   www   false   log   分組   

1、row_number() over()排序功能：

（1） row_number() over()分組排序功能：

     在使用 row_number() over()函數時候，over()裡頭的分組以及排序的執行晚於 where group by  order by 的執行。

partition by 用於給結果集分組，如果沒有指定那麼它把整個結果集作為一個分組，它和彙總函式不同的地方在於它能夠返回一個分組中的多條記錄，而彙總函式一般只有一個反映統計值的記錄。

例如：emp,根據部門分組排序。

SQL> select empno,deptno,sal ,row_number() over (partition by deptno order by sal desc) rank from emp;

   EMPNO     DEPTNO     SAL       RANK---------- ---------- ---------- ----------      7839   10    5000  1      7782   10    2450  2      7934   10    1300  3      7788   20    3000  1      7902   20    3000  2      7566   20    2975  3      7876   20    1100  4      7369   20     800  5      7698   30    2850  1      7499   30    1600  2      7844   30    1500  3      7654   30    1250  4      7521   30    1250  5      7900   30     950  614 rows selected.

（2）對查詢結果進行排序：（無分組）

SQL> select empno,deptno,sal ,row_number() over (order by sal desc) rank from emp;

 EMPNO     DEPTNO     SAL       RANK---------- ---------- ---------- ----------      7839   10    5000  1      7902   20    3000  2      7788   20    3000  3      7566   20    2975  4      7698   30    2850  5      7782   10    2450  6      7499   30    1600  7      7844   30    1500  8      7934   10    1300  9      7521   30    1250 10      7654   30    1250 11      7876   20    1100 12      7900   30     950 13      7369   20     800 1414 rows selected.

row_number() over()和rownum差不多，功能更強一點（可以在各個分組內從1開時排序）．

2、rank() over()是跳躍排序，有兩個第二名時接下來就是第四名（同樣是在各個分組內）．

SQL> select empno,deptno,sal ,rank() over (partition by deptno order by sal desc) rank from emp;

 EMPNO     DEPTNO     SAL       RANK---------- ---------- ---------- ----------      7839   10    5000  1      7782   10    2450  2      7934   10    1300  3      7788   20    3000  1      7902   20    3000  1                    7566   20    2975  3          --跳躍了      7876   20    1100  4      7369   20     800  5      7698   30    2850  1      7499   30    1600  2      7844   30    1500  3      7654   30    1250  4      7521   30    1250  4      7900   30     950  614 rows selected.

SQL> select empno,deptno,sal ,rank() over (order by sal desc) rank from emp;

 EMPNO     DEPTNO     SAL       RANK---------- ---------- ---------- ----------      7839   10    5000  1      7902   20    3000  2      7788   20    3000  2         7566   20    2975  4        --跳躍了      7698   30    2850  5      7782   10    2450  6      7499   30    1600  7      7844   30    1500  8      7934   10    1300  9      7521   30    1250 10      7654   30    1250 10      7876   20    1100 12      7900   30     950 13      7369   20     800 1414 rows selected.

3、dense_rank() over()是連續排序，有兩個第二名時仍然跟著第三名。相比之下row_number是沒有重複值的 ．

SQL> select empno,deptno,sal ,dense_rank() over (order by sal desc) rank from emp;

   EMPNO     DEPTNO     SAL       RANK---------- ---------- ---------- ----------      7839   10    5000  1      7902   20    3000  2      7788   20    3000  2      7566   20    2975  3      7698   30    2850  4      7782   10    2450  5      7499   30    1600  6      7844   30    1500  7      7934   10    1300  8      7521   30    1250  9      7654   30    1250  9      7876   20    1100 10      7900   30     950 11      7369   20     800 1214 rows selected.

SQL> select empno,deptno,sal ,dense_rank() over (partition by deptno order by sal desc) rank from emp;

EMPNO     DEPTNO     SAL       RANK---------- ---------- ---------- ----------      7839   10    5000  1      7782   10    2450  2      7934   10    1300  3      7788   20    3000  1      7902   20    3000  1      7566   20    2975  2    --不跳躍      7876   20    1100  3      7369   20     800  4      7698   30    2850  1      7499   30    1600  2      7844   30    1500  3      7654   30    1250  4      7521   30    1250  4      7900   30     950  514 rows selected.

使用ROW_NUMBER重複資料刪除資料 

---假設表TAB中有a,b,c三列，可以使用下列語句刪除a,b,c都相同的重複行。

DELETE FROM (select year,QUARTER,RESULTS,row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO FROM SALE )   

WHERE ROW_NO>1  

參考：https://www.cnblogs.com/alsf/p/6344197.html

Oracle 分析函數 ROW_NUMBER() 使用