Уроки, извлеченные из 8-недельного sql challenge: оконные функции и ранжирование


👩🏼💻 использование синтаксиса Google BigQuery SQL
📁 проверьте репозиторий Github для контекста

Долгое время у меня были отношения любви/ненависти с функциями WINDOW в SQL, но недавно наступил момент, когда я наконец-то начал видеть свет.

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

Давайте рассмотрим пример, который поможет понять суть дела.

Приведенный ниже запрос …

SELECT 
s.customer_id,
s.order_date,
m.product_name, 
ROW_NUMBER () OVER (PARTITION 
  BY customer_id ORDER BY order_date ASC) AS row_number,
RANK () OVER (PARTITION 
  BY customer_id ORDER BY order_date ASC) AS rank,
DENSE_RANK () OVER(PARTITION 
  BY customer_id ORDER BY order_date ASC) AS dense_rank
FROM sqlchallenge_week1.sales AS s
  JOIN sqlchallenge_week1.menu AS m
    ON s.product_id = m.product_id
ORDER BY customer_id;
Войти в полноэкранный режим Выйти из полноэкранного режима

… выдаст результат, показанный ниже.

Можете ли вы понять по изображению выше, как на самом деле ведет себя функция WINDOW и чем она отличается от стандартной функции AGGREGATE?

  1. Функция WINDOW создает раздел таблицы на основе набора параметров. В нашем случае раздел создается для каждого идентификатора клиента, записи в этом разделе сортируются по дате заказа по возрастанию.
  2. Далее мы вызываем функцию для этого раздела, где для каждой строки генерируется значение, которое попадает в новый столбец, добавляемый в таблицу.

Когда вы посмотрите на пример нашей таблицы, вы заметите, что результаты не сгруппированы, то есть в таблицу вошло столько же строк (14), сколько и вышло. Вот чем функции WINDOW (также называемые аналитическими функциями) отличаются от функций AGGREGATE. В нашем примере функция СЛОЖЕНИЕ, например COUNT(), сгруппировала бы все строки по идентификатору клиента, затем подсчитала бы строки в каждой группе и вернула бы одно значение по идентификатору клиента, что привело бы к результату, показанному ниже.

Теперь давайте вернемся к нашим функциям WINDOW и посмотрим, чем RANK, DENSE_RANK и ROW_NUMBER отличаются друг от друга.

НОМЕР СТРОКИ
Каждому ряду в разделе или окне присваивается номер, который начинается с 1 – это первый ряд в упорядоченном разделе и так далее. Вы заметили, что каждому ряду в окне присваивается уникальный номер, а порядок этих номеров является последовательным с шагом в 1.

RANK
При присвоении номера каждому ряду в разделе или окне мы следуем той же логике, что и в ROW_NUMBER. Единственное отличие заключается в том, что строки с одинаковой датой заказа получают одинаковый номер ранга, а номер ранга для каждой строки в следующем подмножестве увеличивается не на 1, а на 1 + количество строк в предыдущем подмножестве.

DENSE_RANK
Для DENSE_RANK каждой строке в нашем подмножестве в разделе присваивается уникальный номер, который увеличивается на 1 для следующего подмножества.

Надеюсь, вы нашли это полезным, дайте мне знать в комментариях, если возникнут вопросы.

📚 Для более широкого взгляда на оконные функции я могу порекомендовать следующие статьи блога:

Простое руководство по расширенным оконным функциям SQL

Превратите свой SQL из хорошего в отличный: Часть 4 (Оконные функции)

Оцените статью
Procodings.ru
Добавить комментарий