[Oracle SQL] rank()分析函數用法

分析函數簡介

RANK(), DENSE_RANK(), ROW_RANK()都是 Oracle SQL 的分析函數，可根據分群、排序依據分配序列給每一筆資料，可以套用在成績、業績排名、或是群組內排名等等，以下使用員工薪水作為範例，範例會有兩張表格分別為 EMPLOYEE, DEPARTMENT，實作部分會告訴各位三種函數有何差別。

TABLE SCHEMA

CREATE TABLE DEPARTMENT (
  DEPT_ID     NUMBER            NOT NULL
, DEPT_NAME   VARCHAR2(10 CHAR)
, CONSTRAINT  DEPARTMENT_PK PRIMARY KEY(DEPT_ID)
);

CREATE TABLE EMPLOYEE (
  EMPLOYEE_ID NUMBER NOT NULL
, DEPT_ID     NUMBER NOT NULL
, NAME        VARCHAR2(20 CHAR)
, SALARY      NUMBER
, CONSTRAINT EMPLOYEE_PK   PRIMARY KEY (EMPLOYEE_ID)
, CONSTRAINT DEPARTMENT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)
);

INSERT INTO DEPARTMENT VALUES( 1, 'IT');
INSERT INTO DEPARTMENT VALUES( 2, 'ACCT');
INSERT INTO DEPARTMENT VALUES( 3, 'MAINT' );

INSERT INTO EMPLOYEE VALUES( 1, 1, 'Terry' , 50000);
INSERT INTO EMPLOYEE VALUES( 2, 1, 'Emily' , 50000);
INSERT INTO EMPLOYEE VALUES( 3, 2, 'Joyce' , 38600);
INSERT INTO EMPLOYEE VALUES( 4, 2, 'Bob'   , 32800);
INSERT INTO EMPLOYEE VALUES( 5, 2, 'Phoebe', 32800);
INSERT INTO EMPLOYEE VALUES( 6, 3, 'Olive' , 28000);
COMMIT;

語法使用

ROW_NUM()、RANK()、DENSE_RANK()是分析函數用來分派序列給每一筆資料，ORDER BY 欄位名稱 [DESC|ASC]指的是要依據什麼欄位進行遞減[增]排序，此為必填子句，[PARTITION BY 欄位名稱]指的是要依照什麼欄位進行分群，例如：班級、單位內排序，此為選填子句，若沒有這段子句，則會針對所有的資料分派序列。

[ROW_NUM()|RANK()|DENSE_RANK()]
OVER([PARTITION BY 欄位名稱] ORDER BY 欄位名稱 [DESC|ASC])

RANK()使用方法

範例是依據員工薪水高低做排名，若排序資料值趨近一樣，RANK()函數會將排序名次並列，並列後的排名則佔用名次，再往後排序。

SELECT EM.EMPLOYEE_ID                   AS EMPLOYEE_ID
      ,EM.NAME                          AS EMPLOYEE_NAME
      ,EM.SALARY                        AS EMPLOYEE_SALARY
      ,RANK() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

查詢結果

EMPLOYEE_ID	EMPLOYEE_NAME	EMPLOYEE_SALARY	SALARY_RANK
1	Terry	50000	1
2	Emily	50000	1
3	Joyce	38600	3
4	Bob	32800	4
5	Phoebe	32800	4
6	Olive	28000	6

DENSE_RANK()使用方法

DENSE_RANK()函數與 RANK()函數使用方式很相似，兩者差別在於並列後的名次並不會佔用。

SELECT EM.EMPLOYEE_ID                              AS EMPLOYEE_ID
      ,EM.NAME                                     AS EMPLOYEE_NAME
      ,EM.SALARY                                   AS EMPLOYEE_SALARY
      ,DENSE_RANK() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

查詢結果

EMPLOYEE_ID	EMPLOYEE_NAME	EMPLOYEE_SALARY	SALARY_RANK
1	Terry	50000	1
2	Emily	50000	1
3	Joyce	38600	2
4	Bob	32800	3
5	Phoebe	32800	3
6	Olive	28000	4

ROW_NUMBER()使用方法

ROW_NUMBER()函數是並不考慮排序並列的情況，序列依序分派下來。

SELECT EM.EMPLOYEE_ID                              AS EMPLOYEE_ID
      ,EM.NAME                                     AS EMPLOYEE_NAME
      ,EM.SALARY                                   AS EMPLOYEE_SALARY
      ,ROW_NUMBER() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

查詢結果

EMPLOYEE_ID	EMPLOYEE_NAME	EMPLOYEE_SALARY	SALARY_RANK
1	Terry	50000	1
2	Emily	50000	2
3	Joyce	38600	3
4	Bob	32800	4
5	Phoebe	32800	5
6	Olive	28000	6

分群排名

以下範例依據部門分群排名

SELECT EM.EMPLOYEE_ID                                                      AS EMPLOYEE_ID
      ,EM.NAME                                                             AS EMPLOYEE_NAME
      ,DP.DEPT_NAME                                                        AS DEPT_NAME
      ,EM.SALARY                                                           AS EMPLOYEE_SALARY
      ,DENSE_RANK() OVER (PARTITION BY EM.DEPT_ID ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM
 INNER JOIN DEPARTMENT DP
         ON EM.DEPT_ID = DP.DEPT_ID;

查詢結果

EMPLOYEE_ID	EMPLOYEE_NAME	DEPT_NAME	EMPLOYEE_SALARY	SALARY_RANK
1	Terry	IT	50000	1
2	Emily	IT	50000	1
3	Joyce	ACCT	38600	1
4	Bob	ACCT	32800	2
5	Phoebe	ACCT	32800	2
6	Olive	MAINT	28000	1

完整程式碼

CREATE OR REPLACE PROCEDURE PROC_DROP_TAB_IF_EXISTS(
    v_table_name IN VARCHAR2 --TABLE NAME
) IS
    --INITIALIZE ROW NUMBER VARIABLE
    v_count NUMBER;
BEGIN
    v_count := 0;
    SELECT COUNT(1)
      INTO v_count
      FROM USER_TABLES
     WHERE TABLE_NAME = v_table_name;

    IF v_count > 0 THEN
        EXECUTE IMMEDIATE 'DROP TABLE '|| v_table_name ||' CASCADE CONSTRAINTS PURGE';
    END IF;
END;

EXECUTE PROC_DROP_TAB_IF_EXISTS('DEPARTMENT');
EXECUTE PROC_DROP_TAB_IF_EXISTS('EMPLOYEE');


CREATE TABLE DEPARTMENT (
  DEPT_ID     NUMBER            NOT NULL
, DEPT_NAME   VARCHAR2(10 CHAR)
, CONSTRAINT  DEPARTMENT_PK PRIMARY KEY(DEPT_ID)
);

CREATE TABLE EMPLOYEE (
  EMPLOYEE_ID NUMBER NOT NULL
, DEPT_ID     NUMBER NOT NULL
, NAME        VARCHAR2(20 CHAR)
, SALARY      NUMBER
, CONSTRAINT EMPLOYEE_PK   PRIMARY KEY (EMPLOYEE_ID)
, CONSTRAINT DEPARTMENT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID)
);

INSERT INTO DEPARTMENT VALUES( 1, 'IT');
INSERT INTO DEPARTMENT VALUES( 2, 'ACCT');
INSERT INTO DEPARTMENT VALUES( 3, 'MAINT' );

INSERT INTO EMPLOYEE VALUES( 1, 1, 'Terry' , 50000);
INSERT INTO EMPLOYEE VALUES( 2, 1, 'Emily' , 50000);
INSERT INTO EMPLOYEE VALUES( 3, 2, 'Joyce' , 38600);
INSERT INTO EMPLOYEE VALUES( 4, 2, 'Bob'   , 32800);
INSERT INTO EMPLOYEE VALUES( 5, 2, 'Phoebe', 32800);
INSERT INTO EMPLOYEE VALUES( 6, 3, 'Olive' , 28000);
COMMIT;

SELECT EM.EMPLOYEE_ID                   AS EMPLOYEE_ID
      ,EM.NAME                          AS EMPLOYEE_NAME
      ,EM.SALARY                        AS EMPLOYEE_SALARY
      ,RANK() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

SELECT EM.EMPLOYEE_ID                   AS EMPLOYEE_ID
      ,EM.NAME                          AS EMPLOYEE_NAME
      ,EM.SALARY                        AS EMPLOYEE_SALARY
      ,ROW_NUMBER() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

SELECT EM.EMPLOYEE_ID                   AS EMPLOYEE_ID
      ,EM.NAME                          AS EMPLOYEE_NAME
      ,EM.SALARY                        AS EMPLOYEE_SALARY
      ,DENSE_RANK() OVER (ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM;

SELECT EM.EMPLOYEE_ID                                                      AS EMPLOYEE_ID
      ,EM.NAME                                                             AS EMPLOYEE_NAME
      ,DP.DEPT_NAME                                                        AS DEPT_NAME
      ,EM.SALARY                                                           AS EMPLOYEE_SALARY
      ,DENSE_RANK() OVER (PARTITION BY EM.DEPT_ID ORDER BY EM.SALARY DESC) AS SALARY_RANK
  FROM EMPLOYEE EM
 INNER JOIN DEPARTMENT DP
         ON EM.DEPT_ID = DP.DEPT_ID;

參考

Oracle ROW_NUMBER

https://www.oracletutorial.com/oracle-analytic-functions/oracle-row_number/

Oracle PL/SQL: Rank 排名次查詢

https://tomkuo139.blogspot.com/2009/04/plsql-rank.html

Oracle 中 rank() over, dense_rank(), row_number() 的区别

https://blog.csdn.net/baidu_37107022/article/details/78033513