В чем разница между функциями RANK () и DENSE_RANK () в Oracle?

98

Чем отличается функция RANK() и DENSE_RANK()? Как узнать n-ю зарплату в следующей таблице emptbl?

DEPTNO  EMPNAME    SAL
------------------------------
10       rrr    10000.00
11       nnn    20000.00
11       mmm    5000.00
12       kkk    30000.00
10       fff    40000.00
10       ddd    40000.00
10       bbb    50000.00
10       ccc    50000.00

Если в данных таблицы есть nulls, что произойдет, если я хочу узнать nth зарплата?

Теги:
window-functions

7 ответов

170
Лучший ответ

RANK дает вам рейтинг в вашем упорядоченном разделе. Связи присваиваются одинаковому рангу, а следующий рейтинг пропускается. Итак, если у вас есть 3 предмета в ранге 2, следующий ранг будет считаться 5.

DENSE_RANK снова дает вам рейтинг в вашем упорядоченном разделе, но ряды последовательно. Никакие ранги не пропущены, если есть ряды с несколькими элементами.

Что касается нулей, это зависит от предложения ORDER BY. Вот простой тест script, с которым вы можете играть, чтобы узнать, что произойдет:

with q as (
select 10 deptno, 'rrr' empname, 10000.00 sal from dual union all
select 11, 'nnn', 20000.00 from dual union all
select 11, 'mmm', 5000.00 from dual union all
select 12, 'kkk', 30000 from dual union all
select 10, 'fff', 40000 from dual union all
select 10, 'ddd', 40000 from dual union all
select 10, 'bbb', 50000 from dual union all
select 10, 'xxx', null from dual union all
select 10, 'ccc', 50000 from dual)
select empname, deptno, sal
     , rank() over (partition by deptno order by sal nulls first) r
     , dense_rank() over (partition by deptno order by sal nulls first) dr1
     , dense_rank() over (partition by deptno order by sal nulls last) dr2
 from q; 

EMP     DEPTNO        SAL          R        DR1        DR2
--- ---------- ---------- ---------- ---------- ----------
xxx         10                     1          1          4
rrr         10      10000          2          2          1
fff         10      40000          3          3          2
ddd         10      40000          3          3          2
ccc         10      50000          5          4          3
bbb         10      50000          5          4          3
mmm         11       5000          1          1          1
nnn         11      20000          2          2          2
kkk         12      30000          1          1          1

9 rows selected.

Здесь ссылка на хорошее объяснение и некоторые примеры.

  • 10
    очень хорошая идея использовать select union all from dual для генерации выборочных данных без создания какой-либо таблицы
  • 0
    @ Jean-ChristopheBlanchard, хотя вы также можете легко использовать предложение values .
Показать ещё 4 комментария
57

Эта статья здесь прекрасно объясняет это. По существу, вы можете смотреть на нее как таковой:

CREATE TABLE t AS
SELECT 'a' v FROM dual UNION ALL
SELECT 'a'   FROM dual UNION ALL
SELECT 'a'   FROM dual UNION ALL
SELECT 'b'   FROM dual UNION ALL
SELECT 'c'   FROM dual UNION ALL
SELECT 'c'   FROM dual UNION ALL
SELECT 'd'   FROM dual UNION ALL
SELECT 'e'   FROM dual;

SELECT
  v,
  ROW_NUMBER() OVER (ORDER BY v) row_number,
  RANK()       OVER (ORDER BY v) rank,
  DENSE_RANK() OVER (ORDER BY v) dense_rank
FROM t
ORDER BY v;

Вышеприведенное даст:

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |
+---+------------+------+------------+

В словах

  • ROW_NUMBER() присваивает уникальное значение каждой строке
  • RANK() присваивает одинаковый номер строки одному и тому же значению, оставляя "дыры"
  • DENSE_RANK() связывает тот же номер строки с тем же значением, не оставляя "дыр"
  • 1
    Это так хорошо :)
  • 0
    Ошибка: ошибка SQL: ORA-00923: ключевое слово FROM не найдено там, где ожидается
Показать ещё 1 комментарий
6
SELECT empno,
       deptno,
       sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
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


SELECT empno,
       deptno,
       sal,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"
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
4

rank(): используется для ранжирования записи в группе строк.

dense_rank(): функция DENSE_RANK действует как функция RANK, за исключением того, что она назначает последовательные ранги.

Запрос -

select 
    ENAME,SAL,RANK() over (order by SAL) RANK
from 
    EMP;

Выход -

+--------+------+------+
| ENAME  | SAL  | RANK |
+--------+------+------+
| SMITH  |  800 |    1 |
| JAMES  |  950 |    2 |
| ADAMS  | 1100 |    3 |
| MARTIN | 1250 |    4 |
| WARD   | 1250 |    4 |
| TURNER | 1500 |    6 |
+--------+------+------+

Запрос -

select 
    ENAME,SAL,dense_rank() over (order by SAL) DEN_RANK
from 
    EMP;

Выход -

+--------+------+-----------+
| ENAME  | SAL  |  DEN_RANK |
+--------+------+-----------+
| SMITH  |  800 |         1 |
| JAMES  |  950 |         2 |
| ADAMS  | 1100 |         3 |
| MARTIN | 1250 |         4 |
| WARD   | 1250 |         4 |
| TURNER | 1500 |         5 |
+--------+------+-----------+
1

Единственная разница между функциями RANK() и DENSE_RANK() - это случаи, когда есть "связь"; то есть в случаях, когда несколько значений в наборе имеют одинаковое ранжирование. В таких случаях RANK() назначает непересекающиеся "ранги" значениям в наборе (что приводит к разрыву между значениями целочисленного ранжирования при наличии связи), тогда как DENSE_RANK() назначает последовательные ранги значениям в set (поэтому не будет пробелов между целыми значениями ранжирования в случае привязки).

Например, рассмотрим множество {25, 25, 50, 75, 75, 100}. Для такого набора RANK() вернет {1, 1, 3, 4, 4, 6} (обратите внимание, что значения 2 и 5 пропущены), тогда как DENSE_RANK() вернет {1,1,2,3, 3,4}.

1
select empno
       ,salary
       ,row_number() over(order by salary desc) as Serial
       ,Rank() over(order by salary desc) as rank
       ,dense_rank() over(order by salary desc) as denseRank
from emp ;

Row_number() → Используется для генерации серийного номера

Dense_rank() даст непрерывный ранг, но ранг пропустит ранг в случае столкновения ранга.

0

Функция Rank() SQL генерирует ранг данных в упорядоченном наборе значений, но следующий ранг после предыдущего ранга является номером строки этой конкретной строки. С другой стороны, функция Dense_Rank() SQL генерирует следующее число вместо генерации row_number. Ниже приведен пример SQL, который прояснит концепцию:

Select ROW_NUMBER() over (order by Salary) as RowNum, Salary, 
RANK() over (order by Salary) as Rnk, 
DENSE_RANK() over (order by Salary) as DenseRnk from (
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 1000 as Salary union all
Select 2000 as Salary union all
Select 3000 as Salary union all
Select 3000 as Salary union all
Select 8000 as Salary union all
Select 9000 as Salary) A

Он будет генерировать следующий результат:

----------------------------
RowNum  Salary  Rnk DenseRnk
----------------------------
1       1000    1   1
2       1000    1   1
3       1000    1   1
4       2000    4   2
5       3000    5   3
6       3000    5   3
7       8000    7   4
8       9000    8   5

Ещё вопросы

Сообщество Overcoder
Наверх
Меню