Пошук уроків, статей та іншого контенту
Нумеруйте та ранжуйте рядки у вікнах, розуміючи відмінності між ROW_NUMBER, RANK і DENSE_RANK.
ROW_NUMBER, RANK і DENSE_RANK — це віконні функції PostgreSQL, які призначають номери або ранги рядкам результату.
На відміну від агрегатних функцій, вони не об’єднують рядки в один результат. Кожен рядок залишається окремим, а функція обчислює значення в межах визначеного вікна.
Загальний синтаксис:
функція() OVER (
[PARTITION BY колонка]
ORDER BY колонка
)ORDER BY визначає порядок ранжування.
PARTITION BY розділяє рядки на незалежні групи.
Якщо PARTITION BY не вказано, усі рядки належать одному вікну.
ROW_NUMBERROW_NUMBER() призначає кожному рядку послідовний унікальний номер:
ROW_NUMBER() OVER (ORDER BY score DESC)Навіть якщо кілька рядків мають однакове значення score, вони отримають різні номери.
WITH results(student, score) AS (
VALUES
('Олена', 95),
('Андрій', 90),
('Марія', 90),
('Ігор', 82)
)
SELECT
student,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number
FROM results
ORDER BY row_number;Результат матиме таку логіку:
Олена отримає номер 1;
Андрій та Марія отримають номери 2 і 3 у певному порядку;
Ігор отримає номер 4.
Порядок Андрія та Марії без додаткової умови не гарантований, оскільки їхні значення score однакові.
Щоб зробити порядок детермінованим, додайте додаткову колонку:
WITH results(student, score) AS (
VALUES
('Олена', 95),
('Андрій', 90),
('Марія', 90),
('Ігор', 82)
)
SELECT
student,
score,
ROW_NUMBER() OVER (
ORDER BY score DESC, student
) AS row_number
FROM results
ORDER BY row_number;Тепер після сортування за оцінкою рядки з однаковими оцінками сортуються за іменем.
RANKRANK() призначає однаковий ранг рядкам із однаковими значеннями в ORDER BY.
Після групи однакових значень у ранжуванні з’являється пропуск.
WITH results(student, score) AS (
VALUES
('Олена', 95),
('Андрій', 90),
('Марія', 90),
('Ігор', 82)
)
SELECT
student,
score,
RANK() OVER (ORDER BY score DESC) AS rank
FROM results
ORDER BY score DESC, student;Логіка результату:
Олена: ранг 1;
Андрій: ранг 2;
Марія: ранг 2;
Ігор: ранг 4.
Ранг 3 пропущено, тому що перед Ігорем уже було три рядки.
RANK() підходить, коли потрібно зберегти справжню позицію в рейтингу з урахуванням кількості рядків перед нею. Наприклад, якщо дві команди посіли друге місце, наступна команда посідає четверте місце.
DENSE_RANKDENSE_RANK() також призначає однаковий ранг однаковим значенням, але не залишає пропусків.
WITH results(student, score) AS (
VALUES
('Олена', 95),
('Андрій', 90),
('Марія', 90),
('Ігор', 82)
)
SELECT
student,
score,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM results
ORDER BY score DESC, student;Логіка результату:
Олена: ранг 1;
Андрій: ранг 2;
Марія: ранг 2;
Ігор: ранг 3.
DENSE_RANK() нумерує групи різних значень. У цьому прикладі існує три різні оцінки: 95, 90 і 82, тому ранги — 1, 2 і 3.
Для значень 100, 90, 90, 80 функції повернуть:
ROW_NUMBER(): 1, 2, 3, 4;
RANK(): 1, 2, 2, 4;
DENSE_RANK(): 1, 2, 2, 3.
Основна відмінність:
ROW_NUMBER() — кожному рядку унікальний номер;
RANK() — однакові значення мають однаковий ранг, після повторів є пропуски;
DENSE_RANK() — однакові значення мають однаковий ранг, пропусків немає.
WITH results(student, score) AS (
VALUES
('Олена', 95),
('Андрій', 90),
('Марія', 90),
('Ігор', 82)
)
SELECT
student,
score,
ROW_NUMBER() OVER (
ORDER BY score DESC, student
) AS row_number,
RANK() OVER (
ORDER BY score DESC
) AS rank,
DENSE_RANK() OVER (
ORDER BY score DESC
) AS dense_rank
FROM results
ORDER BY score DESC, student;У ROW_NUMBER() ім’я використано як додатковий критерій лише для стабільного порядку. Для RANK() і DENSE_RANK() ранг визначається тільки оцінкою, тому однакові оцінки отримують однакові ранги.
PARTITION BY створює окреме вікно для кожної групи. Нумерація або ранжування починається знову в кожній групі.
Наприклад, можна пронумерувати працівників окремо в кожному відділі:
WITH employees(department, employee, salary) AS (
VALUES
('Розробка', 'Олена', 5000),
('Розробка', 'Андрій', 4500),
('Розробка', 'Марія', 4500),
('Маркетинг', 'Ігор', 4000),
('Маркетинг', 'Софія', 3500)
)
SELECT
department,
employee,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC, employee
) AS department_position
FROM employees
ORDER BY department, department_position;У кожному відділі працівник із найбільшою зарплатою отримує позицію 1.
Те саме можна зробити для рангів:
WITH employees(department, employee, salary) AS (
VALUES
('Розробка', 'Олена', 5000),
('Розробка', 'Андрій', 4500),
('Розробка', 'Марія', 4500),
('Маркетинг', 'Ігор', 4000),
('Маркетинг', 'Софія', 3500)
)
SELECT
department,
employee,
salary,
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS salary_rank,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS dense_salary_rank
FROM employees
ORDER BY department, salary DESC, employee;PARTITION BY department не видаляє колонку department і не групує рядки в один. Він лише визначає, де саме обчислювати віконну функцію.
Одна з практичних задач — отримати, наприклад, двох найкращих працівників кожного відділу.
Віконну функцію спочатку обчислюють у підзапиті або CTE, а потім фільтрують результат:
WITH employees(department, employee, salary) AS (
VALUES
('Розробка', 'Олена', 5000),
('Розробка', 'Андрій', 4500),
('Розробка', 'Марія', 4500),
('Розробка', 'Ігор', 3900),
('Маркетинг', 'Софія', 4200),
('Маркетинг', 'Петро', 4100),
('Маркетинг', 'Анна', 3800)
),
ranked_employees AS (
SELECT
department,
employee,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC, employee
) AS position
FROM employees
)
SELECT
department,
employee,
salary,
position
FROM ranked_employees
WHERE position <= 2
ORDER BY department, position;Віконні функції не можна безпосередньо використовувати в WHERE того самого рівня SELECT, у якому вони обчислюються. Тому спочатку створено CTE ranked_employees, а потім його результат відфільтровано.
Використовуйте ROW_NUMBER(), якщо:
потрібен унікальний порядковий номер кожного рядка;
потрібно вибрати конкретну кількість рядків у кожній групі;
однакові значення не повинні отримувати однакову позицію.
Використовуйте RANK(), якщо:
потрібен рейтинг зі справжніми позиціями;
однакові результати мають спільне місце;
після однакових позицій потрібні пропуски.
Використовуйте DENSE_RANK(), якщо:
однакові значення мають спільний ранг;
потрібно ранжувати різні значення без пропусків;
ранг означає номер групи однакових значень.
ORDER BYРанжування без порядку не має змістовного критерію:
ROW_NUMBER() OVER ()Такий запис синтаксично допустимий, але порядок нумерації не слід вважати гарантованим. Для ранжування завжди вказуйте ORDER BY.
RANK() і DENSE_RANK()Якщо два рядки мають однаковий ранг, наступне значення:
у RANK() може отримати пропуск;
у DENSE_RANK() завжди йде наступним числом.
ROW_NUMBER() збереже нічиїROW_NUMBER() ніколи не призначає однаковий номер. Якщо значення сортування однакові, додайте детермінований критерій:
ROW_NUMBER() OVER (
ORDER BY score DESC, id
)WHERE замість PARTITION BYWHERE видаляє рядки до обчислення віконної функції. PARTITION BY лише розділяє рядки на незалежні вікна.
Наприклад:
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
)нумерує працівників у кожному відділі, а не видаляє інші відділи.
Цей запит некоректний:
SELECT
employee,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS position
FROM employees
WHERE position <= 3;Псевдонім position і результат віконної функції ще недоступні в WHERE. Використовуйте підзапит або CTE.
ROW_NUMBER() призначає кожному рядку унікальний номер.
RANK() зберігає однакові позиції та залишає пропуски після повторів.
DENSE_RANK() зберігає однакові позиції, але не залишає пропусків.
ORDER BY визначає порядок ранжування.
PARTITION BY запускає незалежне ранжування для кожної групи.
Для фільтрації за результатом віконної функції використовуйте CTE або підзапит.
Щоб порядок ROW_NUMBER() був стабільним, додавайте додаткові критерії сортування.