Другие функции — SQL: Оконные функции

• Функции RANK DENSE_RANK
• ROWS и RANGE
• Выводы

Ранжирующая функция - это функция, в которой стоит присвоение последовательности при определенной группировке и сортировке. Помимо ранжирующих функций NTILE() и ROW_NUMBER() существуют и другие. Мы изучим их в этом уроке.

Функции RANK DENSE_RANK

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

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

Рассмотрим на примере. Посмотрим, как продались товары и какое место они занимают в рейтинге продаж:

WITH product_sums AS (
    SELECT
        p.name,
        SUM(s.quantity * s.price) AS amount
    FROM
        sales AS s
    INNER JOIN product AS p
        ON
            s.product_id = p.product_id
    GROUP BY
        p.name
    ORDER BY
        SUM(s.quantity * s.price) DESC
)

SELECT
    p.name,
    p.amount,
    ROW_NUMBER() OVER (ORDER BY p.amount) AS rn,
    RANK() OVER (ORDER BY p.amount) AS r,
    DENSE_RANK() OVER (ORDER BY p.amount) AS dr
FROM
    product_sums AS p;

View on DB Fiddle

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

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

ROWS и RANGE

Инструкции ROWS и RANGE используются вместе с функциями оконного анализа, такими как RANK() и DENSE_RANK(), для определения диапазона строк, на которые должна распространяться аналитическая функция. ROWS определяет физические границы диапазона. Например, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING означает текущую строку и две соседние строки, в то время как RANGE работает не со строками, а со значениями столбцов, отсортированных ORDER BY.

Например, вы можете использовать ROWS для вычисления суммы значений в столбце по диапазону строк, определенному ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, или использовать RANGE для вычисления среднего значения в столбце по логическому диапазону значений.

Ключевые слова ROWS и RANGE:

• UNBOUNDED PRECEDING указывает, что окно начинается с первой строки группы
• UNBOUNDED FOLLOWING с помощью данной инструкции можно указать, что окно заканчивается на последней строке группы
• CURRENT ROW инструкция указывает, что окно начинается или заканчивается на текущей строке
• BETWEEN <граница окна> AND <граница окна> указывает нижнюю и верхнюю границу окна
• <значение> PRECEDING определяет число строк перед текущей строкой (не допускается в предложении RANGE)
• <значение> FOLLOWING определяет число строк после текущей строки (не допускается в предложении RANGE)

Рассмотрим пример по поиску среднего значения продажи между предыдущей, текущей и следующей покупкой

SELECT
    s.id,
    s.sale_date,
    (s.quantity * s.price) AS amount,
    SUM(s.quantity * s.price)
        OVER (ORDER BY s.id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
    AVG(s.quantity * s.price)
        OVER (ORDER BY s.id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
    COUNT(s.id) OVER ()
FROM
    sales AS s
ORDER BY
    s.sale_date;

View on DB Fiddle

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

Выводы

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