Oracle: порядок/группировка/наличие предложений и оконных функций

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

Постараюсь кратко и быстро объяснить все детали в двух частях. Статья скорее всего будет полезна новичкам.

Часть 1
Упорядочить, Группировать, Иметь

СОРТИРОВАТЬ ПО

Предложение ORDER BY позволяет сортировать выходные значения, т. е. сортировать полученное значение по определенному столбцу. Сортировка также может применяться по псевдониму столбца, который определяется с помощью предложения.

Преимущество ORDER BY в том, что его можно применять как к числовым, так и к строковым столбцам. Строковые столбцы обычно сортируются в алфавитном порядке.

По умолчанию применяется сортировка по возрастанию (ASC). Для сортировки по убыванию используется дополнительное предложение DESC.

Синтаксис:

SELECT column1, column2,... (indicates the name
FROM table_name
ORDER BY column1, column2... ASC | DESC;

Рассмотрим примеры:

В первой таблице мы получаем все данные и сортируем их в порядке возрастания по столбцу ID.

Во втором мы также получаем все данные, отсортированные по столбцу ID в порядке убывания с помощью DESC.

Третья таблица использует несколько полей для сортировки. Сначала сортируем по отделам. Если первый оператор равен для полей с одним и тем же отделом, применяется второе условие сортировки; в нашем случае это зарплата.

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

ГРУППА ПО

В SQL предложение GROUP BY собирает данные, полученные из определенных групп в базе данных. Группировка делит все данные на логические наборы, чтобы статистические расчеты можно было выполнять отдельно в каждой группе.

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

Использование GROUP BY тесно связано с использованием агрегатных функций и оператора HAVING. Агрегатная функция в SQL — это функция, которая возвращает одно значение по набору значений столбца. Например: COUNT (), MIN (), MAX (), AVG (), SUM ().

Синтаксис:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

GROUP BY появляется после условного предложения WHERE в запросе SELECT. При желании вы можете использовать ORDER BY для сортировки выходных значений.

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

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

Второй пример короче и читабельнее, хотя выполняет те же функции, что и первый.

Как здесь работает GROUP BY: сначала разбивает два отдела на группы QA и dev, а затем ищет максимальную зарплату для каждого из них.

НАЛИЧИЕ

HAVING — это инструмент фильтрации. Он показывает результат выполнения агрегатных функций. Предложение HAVING используется, когда мы не можем использовать ключевое слово WHERE, т. е. с агрегатными функциями.

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

Синтаксис:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition

Сначала мы определяем отделы со средней зарплатой выше 4000, а затем с помощью фильтрации определяем максимальную зарплату.

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

План запроса

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

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

При выполнении любого оператора SQL в Oracle извлекается так называемый план выполнения. Этот план выполнения запроса представляет собой описание того, как Oracle будет извлекать данные в соответствии с выполняемым оператором SQL. План представляет собой дерево, содержащее последовательность шагов и отношения между ними.

Инструменты, позволяющие нам получить предполагаемый план выполнения запроса, включают Toad, SQL Navigator, PL/SQL Developer и другие. Эти инструменты предоставляют ряд показателей ресурсоемкости запроса, среди которых основными являются: стоимость — стоимость выполнения и количество элементов (или строк). сильный>).

Чем выше значение этих показателей, тем менее эффективен запрос.

Ниже приведен анализ плана запроса. В первом решении используется подзапрос; второй использует группировку. Обратите внимание, что в первом случае обрабатывается 22 строки, во втором — 15.

Другой анализ плана запроса, использующий два подзапроса:

Это пример неэффективного использования инструментов SQL, и мы не рекомендуем использовать его в своих запросах.

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

Часть 2
Функции окна

Оконные функции появились в Microsoft SQL Server 2005. Они выполняют вычисления в заданном диапазоне строк в предложении SELECT. Короче говоря, «окно» — это набор строк, в которых происходят вычисления. «Окно» позволяет нам уменьшить объем данных и лучше их обработать. Такая функция позволяет нам разбить весь набор данных на окна.

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

Синтаксис:

SELECT column_name(s) Aggregate function (column for calculation) OVER ([PARTITION BY column to the group] FROM table_name [ORDER BY column to sort] [ROWS or RANGE to delimit rows within a group])

OVER PARTITION BY — это свойство для определения размера окна. Здесь вы можете указать дополнительную информацию; например, номер строки.

Рассмотрим следующий пример: в нашу таблицу добавился еще один отдел, теперь там 15 строк. Попробуем вывести сотрудников, их зарплату и максимальную зарплату организации.

В первое поле берем ФИО, во второе — оклад. Далее используем оконную функцию over() для получения максимальной зарплаты по всей организации, так как размер окна не указан. Над () с пустыми скобками применяется ко всему выбору. Поэтому максимальная зарплата составляет 10 000. Результат оконной функции добавляется к каждой строке.

Если убрать упоминание об оконной функции из четвертой строки запроса, т. е. останется только max (salary), запрос работать не будет. Максимальную зарплату просто невозможно было рассчитать. Так как данные будут обрабатываться построчно, и на момент вызова max (salary) будет только одно значение текущей строки, т.е. текущего сотрудника. Вот где вы можете увидеть преимущество оконной функции. В момент звонка работает со всем окном и со всеми доступными данными.

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

Здесь мы устанавливаем рамки для окна (набор строк, на которых работает оконная функция), разделяя его на отделы. У нас есть три отдела: dev, QA и sales.

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

Вот как работает PARTITION BY.

Вывод

SQL не так прост, как кажется на первый взгляд. Все описанное выше является базовым функционалом оконных функций. С их помощью вы можете «упростить» свои запросы. Но в них скрыт гораздо больший потенциал: есть служебные предложения (например, ROWS или RANGE), которые можно комбинировать, чтобы добавить больше функциональности запросам.

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

Первоначально опубликовано на https://intexsoft.com 31 августа 2020 г.