Over by partition: Предложение OVER (Transact-SQL) — SQL Server

Что делает SQL PARTITION BY?

4 комментария
/ Последнее обновление: 26 сентября 2022 г.

Вы когда-нибудь видели ключевое слово PARTITION BY, используемое в запросах SQL? Если да, то знаете ли вы, что он делает и как его использовать? Узнайте о ключевом слове SQL PARTITION BY и посмотрите несколько примеров в этой статье.

Содержание

Что такое ключевое слово SQL PARTITION BY?

PARTITION BY — это ключевое слово, которое можно использовать в агрегированных запросах SQL, таких как SUM и COUNT.

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

Это немного похоже на предложение GROUP BY, но немного отличается.

Простой пример PARTITION BY

Давайте рассмотрим пример.

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

Допустим, вы хотите найти идентификаторы предметов, идентификаторы учителей и количество предметов, которые преподает учитель, используя COUNT.

 ВЫБОР
subject_id,
учитель_id,
COUNT(*) OVER (РАЗДЕЛ ПО ИД_учителя) КАК УЧИТЕЛЬ_СЧЕТ
ОТ предмета; 

Результат:

В этой таблице показаны все значения subject_id и Teacher_id. Он также показывает учителя_счет, который представляет собой количество предметов для каждого учителя для каждой строки.

Вы можете видеть, что в нескольких последних записях значение Teacher_count равно 2, потому что есть 2 записи с этим id_учителя. Значение повторяется для этой строки.

Другие записи, которые имеют другое значение для Teacher_id, имеют другое значение счетчика.

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

Почему бы не использовать подзапрос?

Конечно, вы также можете использовать подзапрос для получения тех же результатов. Однако подзапрос будет:

• Медленнее (вероятно)
• Труднее писать

Как бы вы сделали это в подзапросе?

 ВЫБОР
s.subject_id,
s.teacher_id,
sub.teacher_count
ОТ предмета s
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (
  ВЫБРАТЬ
  учитель_id,
  COUNT(*) КАК СЧЕТ_учителей
  ОТ предмета
  СГРУППИРОВАТЬ ПО ИД_учителя)
sub ON sub. teacher_id = s.teacher_id
ЗАКАЗАТЬ ПО s.teacher_id, s.subject_id; 

Результат:

Как видите, результаты те же, но запрос длиннее. Он также может работать медленнее, потому что вы просматриваете таблицу дважды (один раз во внешнем запросе и один раз во внутреннем запросе, который к ней присоединен), а не только один раз.

Почему следует использовать PARTITION BY вместо GROUP BY?

Мы можем попробовать использовать обычный запрос с GROUP BY, чтобы получить те же результаты.

 ВЫБОР
subject_id,
учитель_id,
СЧИТАТЬ(*)
ОТ предмета
СГРУППИРОВАТЬ ПО ИД_субъекта, ИД_учителя; 

Результат:

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

Как насчет того, чтобы изменить GROUP BY на группу только по учителю?

 ВЫБОР
subject_id,
учитель_id,
СЧИТАТЬ(*)
ОТ предмета
СГРУППИРОВАТЬ ПО ИД_учителя; 

Результат (если мы запустим это в Oracle):

 ORA-00979: не выражение GROUP BY 

Эта ошибка возникает из-за того, как работает GROUP BY. Вам нужно указать каждый столбец в предложении SELECT, который не является агрегатной функцией.

Итак, мы видим, что использование GROUP BY не даст нам желаемого результата. Для этой цели мы можем использовать подзапрос (как мы делали ранее) или использовать ключевое слово PARTITION BY.

Заключение

Надеюсь, это ответит на ваши вопросы о ключевом слове SQL PARTITION BY и о том, как его использовать.

У вас есть вопросы по ключевому слову PARTITION BY? Оставьте их в разделе комментариев ниже.

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

MySQL | РАЗДЕЛ ПО ПУНКТУ

Улучшить статью

Сохранить статью

Предложение PARTITION BY используется для разделения строк таблицы на группы. Это полезно, когда нам нужно выполнить расчет для отдельных строк группы, используя другие строки этой группы.

• Всегда используется внутри предложения OVER().
• Раздел, образованный пунктом раздела, также известен как Окно .
• Этот пункт работает только с функциями Windows. Например, RANK(), LEAD(), LAG() и т. д.
• Если это предложение опущено в предложении OVER(), вся таблица рассматривается как один раздел.

Синтаксис:
Синтаксис для предложения Partition:

 Window_function ( выражение )
       Over ( раздел по выражению [order_clause] [frame_clause] )
 

Здесь order_clause и frame_clause необязательны.

expr может быть именем столбца или встроенными функциями в MySQL.

Но стандартный SQL допускает только имена столбцов в expr.

Examples:

Consider, a table “ Hacker “:

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