Ранжирование с исключением дубликатов записей
Функция

Определяет ранг записей раздела выборки данных с исключением дубликатов записей.

Спецификация
   
< ранжирование с исключением дубликатов >::=
Синтаксические правила
  1. < Значимые выражения > в < OVER-спецификации > задают столбцы выборки, по которым выполняется разбивка выборки на разделы.

  2. < Имена столбцов > в < ORDER BY-спецификации > внутри < OVER-спецификации > задают имена ранжируемых столбцов.

Общие правила
  1. Ранжирование записей – это присвоение им определенных значений (рангов).

  2. Рангом текущей записи с исключением дубликатов является количество уже ранжированных уникальных (без дубликатов) записей (без учета текущей записи), плюс единица, т.е. значение ранга характеризует количество всех уникальных предшествующих или последующих записей другого ранга (более высокого или низкого – в зависимости от заданного упорядочивания) раздела выборки по ранжируемому значению, например:

    Значение ранжируемого столбца записи Ранг записи в выборке
    100 1
    200 2
    200 2
    200 2
    500 3
    500 3
    1000 4

    В приведенном примере значение ранга 1 свидетельствует о том, что у данной записи в выборке нет предшествующих записей; значение ранга 2 – у текущей записи есть одна предшествующая уникальная запись другого ранга (более высокого или низкого – в зависимости от заданного упорядочивания); значение ранга 3 – у текущей записи есть две уникальные предшествующие записи другого ранга и т.д.

  3. Дубликатами считаются записи, имеющие одинаковые значения в ранжируемых столбцах.

  4. Функция DENSE_RANK, в отличие от функции RANK, выполняет «плотное» (т.е. без промежутков) ранжирование записей.

  5. Функция выполняет ранжирование записей только внутри разделов, определяемых с помощью < OVER-спецификации >.

    create or replace table rank_exmp(i int, j int);
    insert into rank_exmp (i,j) values (1, 100);
    insert into rank_exmp (i,j) values (1, 200);
    insert into rank_exmp (i,j) values (1, 200);
    insert into rank_exmp (i,j) values (1, 200);
    insert into rank_exmp (i,j) values (2, 500);
    insert into rank_exmp (i,j) values (2, 500);
    insert into rank_exmp (i,j) values (2, 1000);
    select i, j, dense_rank() over (partition by i order by j)
      from rank_exmp;
    
     -           -           -
    |          1|        100|          1|
    |          1|        200|          2|
    |          1|        200|          2|
    |          1|        200|          2|
    |          2|        500|          1|
    |          2|        500|          1|
    |          2|       1000|          2|
  6. Данные в разделе сортируются в соответствии с < ORDER BY-спецификацией >, а затем каждой записи присваивается числовой ранг, начиная с 1.

  7. Ранг вычисляется при каждом изменении значений выражений, входящих в < ORDER BY-спецификацию >.

  8. Если не задана опция ORDER BY, функция DENSE_RANK считает все записи в разделе одинаковыми и, соответственно, всем им присваивает ранг 1.

  9. При ранжировании NULL-значения считаются одинаковыми.

  10. Алгоритм функции DENSE_RANK в случае, когда не заданы столбцы для группировки записей и/или столбцы для ранжирования записей, аналогичен алгоритму функции RANK при этих же условиях (см. описание функции RANK).

  11. В < OVER-спецификации > можно задавать выражение, по которому должны формироваться разделы ранжируемых значений. Например, так можно проранжировать все записи таблицы в обратном порядке, не создавая в ней специального столбца, по которому выполняется разбивка выборки на разделы (на примере приведенной выше таблицы rank_tst):

    select dense_rank() over (partition by 'aaa' order by i desc)
      from rank_tst;
    |          4|
    |          3|
    |          3|
    |          3|
    |          2|
    |          2|
    |          1|