-- 由ROW_NUMBER()函数所想到的一点点东东 --

Question

-- 近几天，观察了一下公司的统计平台，经常看到类似如下的ROW_NUMBER()函数相关的SQL语句：SELECT ...
       ROW_NUMBER() OVER (PARTITION BY T74.WEEK_NAME, T5816.SESSION_ID ORDER BY T74.WEEK_NAME DESC, T5816.SESSION_ID DESC)
       ...
FROM   ...-- 很明显：ROW_NUMBER() OVER()函数中的 ORDER BY 部分完全与 PARTITION BY 部分相同。
-- 我当时很疑惑：这样查询出来的结果能否准确呢？是否是你想要的数据呢？-- 个人提示：用 ROW_NUMBER() OVER()函数的时候，其ORDER BY 所指定的字段，不应该与 PARTITION BY 所指定的字段完全相同，
--           准确地说：已经在PARTITION BY 字段中指定的字段，就不应该在后续的 ORDER BY 字段中出现。-- 举例：
-- Emp表字段说明：
 -----------------------------------------------------------------------------
 EMPNO       NOT NULL NUMBER(4)       -- 员工号
 ENAME       VARCHAR2(10)    -- 员工名称
 JOB       VARCHAR2(9)     -- 员工职位
 MGR       NUMBER(4)       -- 员工上级领导工号
 HIREDATE       DATE            -- 员工入职日期
 SAL       NUMBER(7,2)     -- 员工薪水
 COMM       NUMBER(7,2)     -- 员工提成
 DEPTNO        NUMBER(2)       -- 员工所在部门的部门号scott@TDODS> SELECT * FROM EMP;     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 17-DEC-80     800     20
      7499 ALLEN      SALESMAN       7698 20-FEB-81    1600        300    30
      7521 WARD       SALESMAN       7698 22-FEB-81    1250        500    30
      7566 JONES      MANAGER       7839 02-APR-81    2975     20
      7654 MARTIN     SALESMAN       7698 28-SEP-81    1250       1400    30
      7698 BLAKE      MANAGER       7839 01-MAY-81    2850     30
      7782 CLARK      MANAGER       7839 09-JUN-81    2450     10
      7788 SCOTT      ANALYST       7566 19-APR-87    3000     20
      7839 KING       PRESIDENT     17-NOV-81    5000     10
      7844 TURNER     SALESMAN       7698 08-SEP-81    1500   0    30
      7876 ADAMS      CLERK       7788 23-MAY-87    1100     20
      7900 JAMES      CLERK       7698 03-DEC-81     950     30
      7902 FORD       ANALYST       7566 03-DEC-81    3000     20
      7934 MILLER     CLERK       7782 23-JAN-82    1300     1014 rows selected.SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
       ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO DESC) AS cnt
FROM EMP;     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO      CNT
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER       7839 09-JUN-81    2450     10        1
      7839 KING       PRESIDENT     17-NOV-81    5000     10        2
      7934 MILLER     CLERK       7782 23-JAN-82    1300     10        3
      7566 JONES      MANAGER       7839 02-APR-81    2975     20        1
      7902 FORD       ANALYST       7566 03-DEC-81    3000     20        2
      7876 ADAMS      CLERK       7788 23-MAY-87    1100     20        3
      7369 SMITH      CLERK       7902 17-DEC-80     800     20        4
      7788 SCOTT      ANALYST       7566 19-APR-87    3000     20        5
      7521 WARD       SALESMAN       7698 22-FEB-81    1250        500    30        1
      7844 TURNER     SALESMAN       7698 08-SEP-81    1500   0    30        2
      7499 ALLEN      SALESMAN       7698 20-FEB-81    1600        300    30        3
      7900 JAMES      CLERK       7698 03-DEC-81     950     30        4
      7698 BLAKE      MANAGER       7839 01-MAY-81    2850     30        5
      7654 MARTIN     SALESMAN       7698 28-SEP-81    1250       1400    30        6-- 上面的查询，我一个一个仔细看，这个 ORDER BY DEPTNO DESC 是起作用啦，将查询的整体结果按DEPTNO升序排序，也不符合要求：ORDER BY DEPTNO DESC
--  进一步分析上面的结果：CNT 字段到底是按什么顺序排序的呢？
-- 是按 EMPNO    排序？ NO
-- 是按 ENAME    排序？ NO
-- 是按 JOB      排序？ NO
-- 是按 MGR      排序？ NO
-- 是按 HIREDATE 排序？ NO
-- 是按 SAL      排序？ NO
-- 是按 COMM     排序？ NO-- 除了整体结果是按的DEPTNO 升序排序外，看不出什么其他规律。-- 那么：是不是按照 rowid排序的呢？我们再来验证一下：-- 验证是否是按照rowid全局排序：
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
       rowid
FROM EMP
ORDER BY rowid ASC;     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO ROWID
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------------
      7369 SMITH      CLERK       7902 17-DEC-80     800     20 AAAR3xAAEAAAACXAAA
      7499 ALLEN      SALESMAN       7698 20-FEB-81    1600        300    30 AAAR3xAAEAAAACXAAB
      7521 WARD       SALESMAN       7698 22-FEB-81    1250        500    30 AAAR3xAAEAAAACXAAC
      7566 JONES      MANAGER       7839 02-APR-81    2975     20 AAAR3xAAEAAAACXAAD
      7654 MARTIN     SALESMAN       7698 28-SEP-81    1250       1400    30 AAAR3xAAEAAAACXAAE
      7698 BLAKE      MANAGER       7839 01-MAY-81    2850     30 AAAR3xAAEAAAACXAAF
      7782 CLARK      MANAGER       7839 09-JUN-81    2450     10 AAAR3xAAEAAAACXAAG
      7788 SCOTT      ANALYST       7566 19-APR-87    3000     20 AAAR3xAAEAAAACXAAH
      7839 KING       PRESIDENT     17-NOV-81    5000     10 AAAR3xAAEAAAACXAAI
      7844 TURNER     SALESMAN       7698 08-SEP-81    1500   0    30 AAAR3xAAEAAAACXAAJ
      7876 ADAMS      CLERK       7788 23-MAY-87    1100     20 AAAR3xAAEAAAACXAAK
      7900 JAMES      CLERK       7698 03-DEC-81     950     30 AAAR3xAAEAAAACXAAL
      7902 FORD       ANALYST       7566 03-DEC-81    3000     20 AAAR3xAAEAAAACXAAM
      7934 MILLER     CLERK       7782 23-JAN-82    1300     10 AAAR3xAAEAAAACXAAN14 rows selected.-- 验证是否是按照DEPTNO分组，然后各组按照rowid排序：
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
       ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY rowid DESC) AS cnt
FROM EMP;     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO      CNT
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7934 MILLER     CLERK       7782 23-JAN-82    1300     10        1
      7839 KING       PRESIDENT     17-NOV-81    5000     10        2
      7782 CLARK      MANAGER       7839 09-JUN-81    2450     10        3
      7902 FORD       ANALYST       7566 03-DEC-81    3000     20        1
      7876 ADAMS      CLERK       7788 23-MAY-87    1100     20        2
      7788 SCOTT      ANALYST       7566 19-APR-87    3000     20        3
      7566 JONES      MANAGER       7839 02-APR-81    2975     20        4
      7369 SMITH      CLERK       7902 17-DEC-80     800     20        5
      7900 JAMES      CLERK       7698 03-DEC-81     950     30        1
      7844 TURNER     SALESMAN       7698 08-SEP-81    1500   0    30        2
      7698 BLAKE      MANAGER       7839 01-MAY-81    2850     30        3
      7654 MARTIN     SALESMAN       7698 28-SEP-81    1250       1400    30        4
      7521 WARD       SALESMAN       7698 22-FEB-81    1250        500    30        5
      7499 ALLEN      SALESMAN       7698 20-FEB-81    1600        300    30        614 rows selected.-- 果然也不是按照 rowid 排序的，我们都清楚，rowid 只是行记录的一个物理地址，所以就算是按照rowid这样排序出来的CNT字段已经失去了意义。-- 综上所述：
-- 所以：用 ROW_NUMBER() OVER()函数的时候，其ORDER BY 所指定的字段，不应该与 PARTITION BY 所指定的字段完全相同，
--       准确地说：已经在PARTITION BY 字段中指定的字段，就不应该在后续的 ORDER BY 字段中出现。-- 再举例：例如：我要查询员工的详细信息，且按部门分组，按工资排名：SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
       ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY sal DESC) AS cnt
FROM EMP;     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO      CNT
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7839 KING       PRESIDENT     17-NOV-81    5000     10        1
      7782 CLARK      MANAGER       7839 09-JUN-81    2450     10        2
      7934 MILLER     CLERK       7782 23-JAN-82    1300     10        3
      7788 SCOTT      ANALYST       7566 19-APR-87    3000     20        1
      7902 FORD       ANALYST       7566 03-DEC-81    3000     20        2
      7566 JONES      MANAGER       7839 02-APR-81    2975     20        3
      7876 ADAMS      CLERK       7788 23-MAY-87    1100     20        4
      7369 SMITH      CLERK       7902 17-DEC-80     800     20        5
      7698 BLAKE      MANAGER       7839 01-MAY-81    2850     30        1
      7499 ALLEN      SALESMAN       7698 20-FEB-81    1600        300    30        2
      7844 TURNER     SALESMAN       7698 08-SEP-81    1500   0    30        3
      7654 MARTIN     SALESMAN       7698 28-SEP-81    1250       1400    30        4
      7521 WARD       SALESMAN       7698 22-FEB-81    1250        500    30        5
      7900 JAMES      CLERK       7698 03-DEC-81     950     30        614 rows selected.-- 可以看到: 数据非常准确： 
  10号部门工资排在第1位的是 KING  员工，薪水是5000；
  20号部门工资排在第1位的是 SCOTT 员工，薪水是3000；
  30号部门工资排在第1位的是 BLAKE 员工，薪水是2850；

Answer 1

-- 当然：这里我们没有考虑相同部门薪水相同时的排名问题，如果相同部门存在多个人的薪水一样，那么排名应该相同的话，
-- 这里就不应该用ROW_NUMBER()函数啦，详细处理方法这里略。-- 如果按提成降序排序(COMM字段排序，结果又会怎么样呢？)SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
       ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY comm DESC) AS cnt
FROM EMP;     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO      CNT
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER       7839 09-JUN-81    2450     10        1
      7839 KING       PRESIDENT     17-NOV-81    5000     10        2
      7934 MILLER     CLERK       7782 23-JAN-82    1300     10        3
      7566 JONES      MANAGER       7839 02-APR-81    2975     20        1
      7902 FORD       ANALYST       7566 03-DEC-81    3000     20        2
      7876 ADAMS      CLERK       7788 23-MAY-87    1100     20        3
      7369 SMITH      CLERK       7902 17-DEC-80     800     20        4
      7788 SCOTT      ANALYST       7566 19-APR-87    3000     20        5
      7900 JAMES      CLERK       7698 03-DEC-81     950     30        1
      7698 BLAKE      MANAGER       7839 01-MAY-81    2850     30        2
      7654 MARTIN     SALESMAN       7698 28-SEP-81    1250       1400    30        3
      7521 WARD       SALESMAN       7698 22-FEB-81    1250        500    30        4
      7499 ALLEN      SALESMAN       7698 20-FEB-81    1600        300    30        5
      7844 TURNER     SALESMAN       7698 08-SEP-81    1500   0    30        614 rows selected.-- 我们由DEPTNO=30的数据可以看到：COMM为空的排在非空数据的前面。-- 所以：如果我们把COMM为空的，看作提成为0的话，我们应该还需要进一步处理，排名才相对正确：SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
       ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY nvl(comm,0) DESC) AS cnt
FROM EMP;     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM     DEPTNO      CNT
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER       7839 09-JUN-81    2450     10        1
      7839 KING       PRESIDENT     17-NOV-81    5000     10        2
      7934 MILLER     CLERK       7782 23-JAN-82    1300     10        3
      7566 JONES      MANAGER       7839 02-APR-81    2975     20        1
      7902 FORD       ANALYST       7566 03-DEC-81    3000     20        2
      7876 ADAMS      CLERK       7788 23-MAY-87    1100     20        3
      7369 SMITH      CLERK       7902 17-DEC-80     800     20        4
      7788 SCOTT      ANALYST       7566 19-APR-87    3000     20        5
      7654 MARTIN     SALESMAN       7698 28-SEP-81    1250       1400    30        1
      7521 WARD       SALESMAN       7698 22-FEB-81    1250        500    30        2
      7499 ALLEN      SALESMAN       7698 20-FEB-81    1600        300    30        3
      7844 TURNER     SALESMAN       7698 08-SEP-81    1500   0    30        4
      7900 JAMES      CLERK       7698 03-DEC-81     950     30        5
      7698 BLAKE      MANAGER       7839 01-MAY-81    2850     30        614 rows selected.

Answer 2

罗老湿，你这9年义务教育是一个月才帮我们上一次课？long time no see!

Answer 3

ROW_NUMBER：
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。 DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。 RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

Answer 4

写的不错，学习下～
以前没有这么写过，开始以为是以sys_guid()排序，结果发现也不是。
又做了下面的测试,新建一个和emp一样的表emp2INSERT INTO EMP2 SELECT * FROM EMP ORDER BY dbms_random.value()SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
       ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY DEPTNO DESC) AS cnt
FROM EMP2;
结果发现，使用相同sql检索emp和emp2的结果是不一样的，看来和插入记录的循序还是有关系的，具体按什么排序不得而知了。

Answer 5

ORDER BY 后字段与PARTITION BY 完全相同
其实就是对分组后无针对的取一条记录
平时都是用order by dbms_random.value来完成的
ROW_NUMBER() OVER (PARTITION BY T74.WEEK_NAME, T5816.SESSION_ID ORDER BY T74.WEEK_NAME DESC, T5816.SESSION_ID DESC)
改为
ROW_NUMBER() OVER (PARTITION BY T74.WEEK_NAME, T5816.SESSION_ID ORDER BY dbms_random.value)
或者直接就
ROW_NUMBER() OVER (PARTITION BY T74.WEEK_NAME, T5816.SESSION_ID ORDER BY 1)