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

Таблица и данные для иллюстрации

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

CREATE TABLE ContestResults (
    ColID       int             NOT NULL    IDENTITY,
    Category    varchar(10)     NOT NULL,
    Weight      int             NOT NULL,
    Entrant     nvarchar(20)    NOT NULL,
    CONSTRAINT PK_ContestResults PRIMARY KEY CLUSTERED (ColID)
)

Теперь давайте заполним таблицу некоторыми примерными данными:

INSERT INTO ContestResults (Category, Weight, Entrant)
VALUES
    ('Тыква', 716, 'Чад Джонсон'),
    ('Тыква', 679, 'Джордж Копселл'),
    ('Тыква', 679, 'Дэн Гарднер'),
    ('Тыква', 481, 'Джон Сайдам'),
    ('Кабачок', 462, 'Дэн Гарднер'),
    ('Кабачок', 462, 'Харви Зейл'),
    ('Арбуз', 146, 'Марк Бардин'),
    ('Арбуз', 139, 'Кристина Даэ'),
    ('Арбуз', 139, 'Карлотта Джудичелли');

RANK() и DENSE_RANK()

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

Давайте используем эти функции, чтобы определить ранжирование участников на основе их веса:

SELECT Category, Weight, Entrant, RANK() OVER (ORDER BY Weight DESC) AS [Rank], DENSE_RANK() OVER (ORDER BY Weight DESC) AS DenseRank
FROM ContestResults

Результат этого запроса будет отображать категорию, вес, участника, ранг и плотный ранг для каждого участника. Ранг и плотный ранг будут назначены на основе убывающего порядка столбца веса.

Использование PARTITION BY

В некоторых случаях может потребоваться назначить ранги внутри определенных групп или разделов. Это можно сделать с помощью ключевого слова PARTITION BY в сочетании с функциями ранжирования.

Например, если мы хотим определить ранжирование участников в каждой категории, мы можем изменить предыдущий запрос следующим образом:

SELECT Category, Weight, Entrant, RANK() OVER (PARTITION BY Category ORDER BY Weight DESC) AS [Rank], DENSE_RANK() OVER (PARTITION BY Category ORDER BY Weight DESC) AS DenseRank
FROM ContestResults

Этот запрос будет назначать ранги внутри каждой категории отдельно, что позволит нам определить лучших исполнителей в каждой категории.

Использование общих выражений (CTE)

При работе с функциями ранжирования часто полезно сохранять результаты во временной таблице или общем выражении (CTE) для дальнейшего анализа или фильтрации. CTE предоставляют лаконичный и читаемый способ достижения этой цели.

Вот пример использования CTE для сохранения трех лучших исполнителей в каждой категории:

WITH TopPerformers AS (
    SELECT Category, Entrant, DENSE_RANK() OVER (PARTITION BY Category ORDER BY Weight DESC) AS DenseRank
    FROM ContestResults
)
SELECT Category, Entrant, DenseRank
FROM TopPerformers
WHERE DenseRank <= 3
ORDER BY Category, DenseRank, Entrant

Этот запрос создаст CTE с именем TopPerformers, который содержит категорию, участника и плотный ранг для каждого участника. Затем мы выбираем трех лучших исполнителей из CTE и упорядочиваем результаты по категории, плотному рангу и участнику.

Заключение

Функции ранжирования в SQL Server – это мощные инструменты, которые могут быть использованы для назначения рангов или позиций строкам в наборе результатов. Они позволяют легко определить лучших исполнителей, идентифицировать дубликаты или разделить данные на основе определенных критериев. Понимая, как эффективно использовать эти функции, вы можете улучшить свои запросы SQL Server и получить ценные инсайты из своих данных.

В следующем месяце мы рассмотрим функции NTILE() и ROW_NUMBER(), которые также являются полезными функциями ранжирования в SQL Server.

У вас есть интересные случаи использования или проблемы, связанные с функциями ранжирования? Поделитесь своими запросами и объяснениями на форуме!