測試表emp
RANK
SQL> select EMPNO,DEPTNO,SAL,
2 rank()over(partition by DEPTNO order by SAL) "rank"
3 from emp;
EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
7934 10 1300 1
7782 10 2450 2
7839 10 5000 3
7369 20 800 1
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 4
7900 30 950 1
7654 30 1250 2
7521 30 1250 2
7844 30 1500 4
7499 30 1600 5
7698 30 2850 6
14 rows selected.
按照deptno分組sal升序排列，這裡注意到deptno=30的sal=1250的兩個僱員，並列在該組中排名第2。然後下一個就是第4名。
What we see here is where two people have the same salary they are assigned the same rank. When multiple rows share the same rank the next rank in the sequence is not consecutive
如果兩個人有相同的salary，就分配相同的排名。多行共享了相同的排名之後，下一個排名在順序上是不連續的。
要和後面的dense_rank(),row_number()區分。
DENSE_RANK
The DENSE_RANK function acts like the RANK function except that it assigns consecutive ranks.
DENSE_RANK函數和RANK函數相似，如果有多個人有相同的salary，會分配same rank，不同之處在于下一個排名在順序上是連續的。
SQL> select EMPNO,DEPTNO,SAL,
2 dense_rank()over(partition by DEPTNO order by SAL) "rank"
3 from emp;
EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
7934 10 1300 1
7782 10 2450 2
7839 10 5000 3
7369 20 800 1
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 4
7900 30 950 1
7654 30 1250 2
7521 30 1250 2
7844 30 1500 3
7499 30 1600 4
7698 30 2850 5
14 rows selected.
ROW_NUMBER
row_number函數的區別在於，當多個人有相同的salary的時候并不會共享rank，而是連續的分配rank。not same rank,not consecutive
SQL> select EMPNO,DEPTNO,SAL,
2 row_number()over(partition by DEPTNO order by SAL) "rank"
3 from emp;
EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
7934 10 1300 1
7782 10 2450 2
7839 10 5000 3
7369 20 800 1
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 5
7900 30 950 1
7654 30 1250 2
7521 30 1250 3
7844 30 1500 4
7499 30 1600 5
7698 30 2850 6
14 rows selected.
FIRST and LAST
The FIRST and LAST functions can be used to return the first or last value from an ordered sequence
用於返回first 或者last value
SQL> select EMPNO,DEPTNO,SAL,
2 min(sal)keep(dense_rank first order by sal )over(partition by DEPTNO) "Lowest",
3 max(sal)keep(dense_rank last order by sal )over(partition by DEPTNO) "Highest"
4 from emp;
EMPNO DEPTNO SAL Lowest Highest
---------- ---------- ---------- ---------- ----------
7782 10 2450 1300 5000
7839 10 5000 1300 5000
7934 10 1300 1300 5000
7566 20 2975 800 3000
7902 20 3000 800 3000
7876 20 1100 800 3000
7369 20 800 800 3000
7788 20 3000 800 3000
7521 30 1250 950 2850
7844 30 1500 950 2850
7499 30 1600 950 2850
7900 30 950 950 2850
7698 30 2850 950 2850
7654 30 1250 950 2850
14 rows selected.
下面這種寫法，返回結果和上面一樣。
SQL> select EMPNO,DEPTNO,SAL,
2 min(sal)over(partition by DEPTNO) "Lowest",
3 max(sal)over(partition by DEPTNO) "Highest"
4 from emp;
EMPNO DEPTNO SAL Lowest Highest
---------- ---------- ---------- ---------- ----------
7782 10 2450 1300 5000
7839 10 5000 1300 5000
7934 10 1300 1300 5000
7566 20 2975 800 3000
7902 20 3000 800 3000
7876 20 1100 800 3000
7369 20 800 800 3000
7788 20 3000 800 3000
7521 30 1250 950 2850
7844 30 1500 950 2850
7499 30 1600 950 2850
7900 30 950 950 2850
7698 30 2850 950 2850
7654 30 1250 950 2850
14 rows selected.
2016-04-13