Основные оконные функции: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

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

Функция ROW_NUMBER()

Функция ROW_NUMBER() возвращает уникальный номер для каждой строки в пределах окна. Это просто нумерация строк в порядке, определенном в ORDER BY.

Синтаксис:

ROW_NUMBER() OVER ([PARTITION BY column] ORDER BY column)

Где:

• PARTITION BY column (опционально): делит данные на группы. Если пропустить, нумерация будет глобальной по всему набору.
• ORDER BY column: определяет порядок строк для нумерации.

Пример. Нумерация строк в таблице

Разберем таблицу students, содержащую информацию о студентах и их оценках.

SELECT * FROM students;

Теперь давайте пронумеруем строки по порядку убывания их оценок (score):

SELECT
    name, 
    score, 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

Результат:

Каждая строка получила уникальный порядковый номер — с учётом сортировки по убыванию оценок.

Это простая, но мощная операция — добавить номер строки в результат запроса. В классическом SELECT сделать это невозможно без оконных функций.

Функция RANK()

Функция RANK() очень похожа на ROW_NUMBER(), но она учитывает одинаковые значения. Если строки имеют одинаковое значение в сортировке, они получат одинаковый ранг, а следующий пропустится.

Синтаксис:

RANK() OVER ([PARTITION BY column] ORDER BY column)

Пример. Ранжирование студентов по их оценкам

Давайте используем RANK() для тех же данных:

SELECT
    name, 
    score, 
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

Результат:

Здесь строки с одинаковыми значениями (95 и 87) получили одинаковый ранг, а последующие ранги пропущены.

Функция DENSE_RANK()

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

Синтаксис:

DENSE_RANK() OVER ([PARTITION BY column] ORDER BY column)

Пример. Плотное ранжирование

Используем DENSE_RANK() для тех же данных:

SELECT
    name, 
    score, 
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

Результат:

Здесь, в отличие от RANK(), растут только значения рангов без пропусков.

Функция NTILE()

Функция NTILE() разбивает строки на равные группы (квантили) и присваивает каждой строке номер группы.

Синтаксис:

NTILE(n) OVER ([PARTITION BY column] ORDER BY column)

• n: количество групп, на которые нужно разбить данные.

Пример. Разбиение студентов на 3 группы

Давайте разделим студентов на 3 группы по убыванию оценок:

SELECT
    name, 
    score, 
    NTILE(3) OVER (ORDER BY score DESC) AS group_num
FROM students;

Результат:

Обратите внимание: если строки нельзя разделить на группы идеально поровну, лишние строки попадают в первые группы. В этом примере первые две группы содержат по две строки, а последняя — одну.

Когда какую функцию использовать?

• ROW_NUMBER(): для уникальной нумерации строк в порядке сортировки.
• RANK(): для ранжирования с учетом одинаковых значений и пропуском следующего ранга.
• DENSE_RANK(): для ранжирования с учетом одинаковых значений без пропуска рангов.
• NTILE(): Ддя равномерного разбиения строк на группы.

Все эти функции помогают анализировать данные на совершенно новом уровне. Используйте их там, где требуется гибкость в вычислении порядковых номеров или делении данных.

2

Задача

SQL SELF, 29 уровень, 1 лекция

Недоступна

Нумерация строк с использованием `ROW_NUMBER`

Нумерация строк с использованием `ROW_NUMBER`

Открыть