Доступные версии документации

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

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

Спецификация
   
<ранжирование с учетом дубликатов>::=
Синтаксические правила
  1. <Значимое выражение> должно иметь целочисленное значение в диапазоне [1-3] или приводиться к нему. При этом:

    RANK(1) = RANK()

    RANK(2) = DENSE_RANK()

    RANK(3) = ROW_NUMBER()

    Для всех остальных значений <значимого выражения> функция RANK возвращает NULL.

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

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

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

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

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

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

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

  4. Функция RANK использует логическое группирование. Это значит, что когда две или более записи в разделе имеют одинаковое значение в ранжируемом столбце, то такие записи будут иметь одинаковый ранг. Логическое группирование приводит к тому, что числа, соответствующие рангам, идут не подряд, а с промежутками.

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

    create or replace table rank_example(i char(1), j int);
    insert into rank_example (i,j) values ('a', 100);
    insert into rank_example (i,j) values ('a', 200);
    insert into rank_example (i,j) values ('b', 200);
    insert into rank_example (i,j) values ('b', 200);
    insert into rank_example (i,j) values ('b', 500);
    insert into rank_example (i,j) values ('c', 500);
    insert into rank_example (i,j) values ('c', 1000);
    
    select i, j, rank() over (partition by i order by j)
      from rank_example;
    
     I J
     - -
    |a|        100|          1|
    |a|        200|          2|
    |b|        200|          1|
    |b|        200|          1|
    |b|        500|          3|
    |c|        500|          1|
    |c|       1000|          2|

    В этом примере: а) раздел 1:

    |          a|        100|          1|
    |          a|        200|          2|

    Т.к. значения записей различны, то и ранги их в разделе различны.

    б) раздел 2:

    |          b|        200|          1|
    |          b|        200|          1|
    |          b|        500|          3|

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

    в) раздел 3:

    |c|        500|          1|
    |c|       1000|          2|

    Т.к. значения записей различны, то и ранги их в разделе различны.

    В этом примере все записи внутри разделов одинаковые (в сортируемом столбце), поэтому их ранги равны 1:

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

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

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

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

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

    select rank() over () from rank_tst;
    |          1|
    |          1|
    |          1|
    |          1|
    |          1|
    |          1|
    |          1|
  11. Если столбцы для группировки заданы, а для ранжирования нет, то по умолчанию ранжирование выполняется по столбцам группировки, а т.к. в этом случае все записи внутри раздела будут иметь одинаковые значения в ранжируемых столбцах, то ранги всех записей будут равны 1:

    select rank() over (partition by i)   from rank_tst;
    |          1|
    |          1|
    |          1|
    |          1|
    |          1|
    |          1|
    |          1|
  12. В <OVER-спецификации> можно задавать выражение, по которому должны формироваться разделы ранжируемых значений. Например, так можно проранжировать все записи таблицы, не создавая в ней специального столбца, по которому выполняется разбивка выборки на разделы (на примере приведенной выше таблицы rank_tst):

    select rank() over (partition by 'aaa' order by i)  from rank_tst;
    |          1|
    |          2|
    |          2|
    |          2|
    |          5|
    |          5|
    |          7|
Примеры
  1. Оценить цветовые предпочтения владельцев автомобилей производства фирмы GENERAL MOTORS.

    select distinct a, b, c
      from (select make a,
                   color b,
                   rank() over (partition by make order by color) c
              from auto
             where make ='GENERAL MOTORS')
     order by a;
     A                 B       C
     -                 -       -
    | GENERAL MOTORS | BLACK  |   1 |
    | GENERAL MOTORS | BLUE   |  67 |
    | GENERAL MOTORS | BROWN  |  99 |
    | GENERAL MOTORS | GREEN  | 111 |
    | GENERAL MOTORS | GREY   | 125 |
    | GENERAL MOTORS | RED    | 165 |
    | GENERAL MOTORS | WHITE  | 188 |
    | GENERAL MOTORS | YELLOW | 269 |
  2. Определить трех самых высокооплачиваемых сотрудников в каждом отделе.

    CREATE OR REPLACE TABLE employee (employee_id INT, dept_id  INT, first_name VARCHAR(25), last_name VARCHAR(25), salary INT, job VARCHAR(20));
    INSERT INTO employee VALUES (1111, 10, 'Martha', 'White', 4400, 'IT_PROG');
    INSERT INTO employee VALUES (1112, 10, 'John', 'Black', 8800, 'IT_PROG');
    INSERT INTO employee VALUES (1113, 20, 'Bill', 'Austin', 7600, 'MK_REP');
    INSERT INTO employee VALUES (1114, 20, 'Diana', 'Kimes', 4300, 'MK_MAN');
    INSERT INTO employee VALUES (1115, 20, 'David', 'Peters', 7600, 'IT_PROG');
    INSERT INTO employee VALUES (1116, 30, 'Sibille', 'Peterson',12000,'AX_ASST');
    INSERT INTO employee VALUES (1117, 30, 'Jack', 'Klein', 9900, 'MK_REP');
    INSERT INTO employee VALUES (1118, 30, 'Alex', 'Armstrong', 8500, 'MK_REP');
    INSERT INTO employee VALUES (1119, 30, 'Jennifer', 'May', 6700, 'AX_ASST');
    INSERT INTO employee VALUES (1120, 40, 'Roy', 'Hunt', 9900, 'IT_PROG');
    INSERT INTO employee VALUES (1121, 40, 'Wendy', 'Blunt', 8800, 'AX_ASST');
    INSERT INTO employee VALUES (1122, 50, 'Valli', 'Begg', 7900, 'MK_MAN');
    INSERT INTO employee VALUES (1123, 50, 'Pat', 'Donaldson', 4900, 'MK_MAN');
    
    SELECT dept_id, last_name, salary, dept_rank
      FROM (SELECT dept_id,
                   job,
                   last_name,
                   salary,
                   RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) dept_rank
              FROM employee) AS part_dept
     WHERE dept_rank <= 3
     ORDER BY dept_id, dept_rank;
    
Заметили ошибку?
Выделите текст и нажмите Ctrl + Enter