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

Запросы с групповыми операциями

Вы будете перенаправлены на Автор24

Оператор GROUP BY

В базе данных priem имеется таблица abiturient следующего содержания:

Необходимо подсчитать количество абитуриентов мужского и женского пола.

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

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

Общий вид запроса с групповой операцией выглядит следующим образом:

Группирующее_поле – это поле по которому следует группировать данные. В приведенном примере это будет поле age.

Групповая_операция (или статистическая операция) – это встроенная функция, которая вычисляет некоторые статистические данные по группе. В данном случае это будет функция, которая подсчитывает количество записей в каждой группе. Такая функция в SQL называется Count().

Некоторое_поле – это поле, по которому применяется групповая статистическая операция. В приведенном примере это может быть поле idabiturient.

Инструкция AS позволяет дать новому вычисленному полю удобный псевдоним.

Итак, чтобы решить поставленную задачу нужно написать запрос:

SELECT gender, count(idabiturient) AS num_abitur FROM abiturient GROUP BY gender;

Готовые работы на аналогичную тему

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

SELECT fio, gender, count(idabiturient) AS num_abitur FROM abiturient GROUP BY gender;

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

Групповые операции

Во всех «диалектах» SQL имеется стандартный набор групповых операций:

Найти средний возраст абитуриента в мужской и женской группе.

SELECT gender, AVG(age) AS avg_age FROM abiturient GROUP BY gender;

Сложная группировка

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

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

SELECT gender, age, count(idabiturient) AS num_abitur FROM abiturient GROUP BY gender, age;

Отбор в групповых запросах

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

Но эти инструкции не идентичны.

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

НAVING выполняет отбор после выполнения групповых операций и позволяет накладывать условия на поля с групповой операцией.

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

SELECT age, count(idabiturient) AS num_abitur FROM abiturient WHERE gender = ‘male’ GROUP BY age;

В этом примере сначала будут отобраны записи, где в поле gender стоит значение ‘male’. Потом результат будет сгруппирован по полю age и выполнится функция COUNT().

Теперь рассмотрим запрос с использованием HAVING. Необходимо показать только те возрастные группы, где больше двух абитуриентов.

SELECT age, count(idabiturient) AS num_abitur FROM abiturient GROUP BY age HAVING num_abitur > 2;

В этом случае сначала будет выполнена группировка по возрасту с вычислением функции COUNT(). А у же потом в полученном результате будут отфильтрованы строки, где число абитуриентов превысило 2.

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

Источник

Порядок выполнения SQL-операций

Перевод статьи «SQL Order of Operations».

Мы привыкли, что компьютер выполняет команды программиста последовательно, в том порядке, который указал автор кода. Однако SQL относится к декларативным языкам, то есть SQL-запрос описывает ожидаемый результат, а не способ его получения.

Давайте разберём, в какой последовательности выполняются шесть операций в SQL: SELECT, FROM, WHERE, GROUP BY, HAVING и ORDER BY.

База данных выполняет команды в строгой очерёдности, о которой полезно знать любому разработчику. Залог оптимального запроса тот же, что и залог успеха в приготовлении вкусного блюда: важно знать не только ингредиенты, но и когда каждый из них должен попасть в блюдо. Если база данных отойдет от стандартного сценария выполнения команд, то ее производительность может сильно пострадать.

База данных сотрудников

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

EMPLOYEE

DEPARTMENT

Проанализировать порядок выполнения команд в запросах помогут типичные задачи:

Начнем с получения имён сотрудников отдела IT:

В первую очередь выполняется FROM EMPLOYEE:

Затем наступает очередь WHERE DEPARTMENT = ‘IT’, который фильтрует колонку DEPARTMENT:

Наконец, SELECT FIRST_NAME, LAST_NAME скрывает ненужные колонки и возвращает финальный результат:

Отлично! После первого препарирования выяснилось, что простой запрос с операторами SELECT, FROM, и WHERE выполняется по следующей схеме:

Влияние ORDER BY на план выполнения запроса

Допустим, что начальнику не понравился отчет, основанный на предыдущем запросе, потому что он хочет видеть имена в алфавитном порядке. Исправим это с помощью ORDER BY:

Выполняться такой запрос будет так же, как и предыдущий. Только в конце ORDER BY отсортирует строки в алфавитном порядке по колонке FIRST_NAME:

Таким образом, команды SELECT, FROM, WHERE и ORDER BY выполняются в следующей последовательности:

GROUP BY и HAVING

Усложним задачу. Посчитаем количество сотрудников каждого отдела с зарплатой выше 80 000 и остортируем результат по убыванию. Нам подойдёт следующий запрос:

Как обычно, в первую очередь выполнится FROM EMPLOYEE и вернет сырые данные:

После выполнения WHERE SALARY > 80000 выборка сузится:

Затем применяется GROUP BY. При этом генерируется по одной записи для каждого отдельного значения в указанной колонке. В нашем примере мы создаем по одной записи для каждого отдельного значения колонки DEPARTMENT:

После этого применяется SELECT с COUNT(*), производя промежуточный результат:

Применение ORDER BY завершает выполнение запроса и возвращает конечный результат:

План выполнения данного запроса следующий:

Добавим выражение HAVING

HAVING — это аналог WHERE для GROUP BY. С его помощью можно фильтровать агрегированные данные.

Давайте применим HAVING и определим, в каких отделах (за исключением отдела продаж) средняя зарплата сотрудников больше 80 000.

По уже известной нам схеме сначала выберем все данные из таблицы при помощи FROM EMPLOYEE:

Затем конструкция WHERE избавит нас от данных по отделу SALES:

GROUP BY сгенерирует следующие записи:

HAVING AVG(SALARY) > 80000 ограничит список:

А SELECT вернет финальный результат:

Порядок выполнения для данного запроса следующий:

Новый оператор — JOIN

До этого момента мы имели дело с одной таблицей. А что если воспользоваться JOIN и добавить ещё одну? Выясним фамилии и ID сотрудников, работающих в отделе с бюджетом более 275 000:

FROM EMPLOYEE как обычно запрашивает данные из таблицы EMPLOYEES:

А теперь JOIN запросит сырые данные из DEPARTMENT и скомбинирует данные двух таблиц по условию ON DEPARTMENT = DEPT_NAME:

Потом применяем WHERE BUDGET > 275000:

SELECT EMPLOYEE_ID, LAST_NAME покажет финальный результат:

Для этого запроса план выполнения следующий:

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

Помните, что если исключить из этого списка один из операторов, то план выполнения может измениться.

Источник

Группировки и оконные функции в Oracle

Привет, Хабр! В компании, где я работаю, часто проходят (за мат извините) митапы. На одном из них выступал мой коллега с докладом об оконных функциях и группировках Oracle. Эта тема показалась мне стоящей того, чтобы сделать о ней пост.

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

Всю необходимую информацию я постараюсь кратко и доступно объяснить в двух частях. Пост скорее будет полезен начинающим разработчикам. Кому интересно — добро пожаловать под кат.

Часть 1: предложения Order by, Group by, Having

Здесь мы поговорим о сортировке — Order by, группировке — Group by, фильтрации — Having и о плане запроса. Но обо всем по-порядку.

Order by

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

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

Сортировка по возрастанию применяется по умолчанию. Если хотите отсортировать столбцы по убыванию — используйте дополнительный оператор DESC.

SELECT column1, column2, … (указывает на название)
FROM table_name
ORDER BY column1, column2… ASC|DESC;

Давайте все рассмотрим на примерах:

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

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

В третьей таблице используется несколько полей для сортировки. Сначала идет сортировка по отделу. При равенстве первого оператора для полей с одинаковым отделом применяется второе условие сортировки; в нашем случае — это зарплата.

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

Group by

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

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

С использованием оператора SQL Group by тесно связано использование агрегатных функций и оператор SQL 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, чтобы отсортировать выходные значения.

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

Решение 1 (без использования группировки):

Решение 2 (с использованием группировки):

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

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

Как у нас работает Group by: сначала разбивает два отдела на группы qa и dev. Потом для каждого из них ищет максимальную зарплату.

Having

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

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

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

Решение 1 (без использования GROUP BY и HAVING):

Решение 2 (с использованием GROUP BY и HAVING):

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

План запроса

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

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

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

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

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

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

Анализ плана запроса:

Ещё один анализ плана запроса, в котором применяется два подселекта:

Этот пример приведен как вариант нерационального использования средств SQL и я не рекомендую вам его использовать в своих запросах.

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

Часть 2: Оконные функции

Оконные функции появились ещё в Microsoft SQL Server 2005. Они осуществляют вычисления в заданном диапазоне строк внутри предложения Select. Если говорить кратко, то “окно” — это набор строк, в рамках которого происходит вычисление. “Окно” позволяет уменьшить данные и более качественно их обработать. Такая функция позволяет разбивать весь набор данных на окна.

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

SELECT column_name(s)
Агрегирующая функция (столбец для вычислений)
OVER ([PARTITION BY столбец для группировки]
FROM table_name
[ORDER BY столбец для сортировки]
[ROWS или RANGE выражение для ограничения строк в пределах группы])

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

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

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

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

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

Фактически мы задаем рамки для “окна”, разбивая его на отделы. В качестве ранжирующего примера мы указываем department. У нас есть три отдела: dev, qa и sales.

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

В предыдущем примере в скобках после over не было указано. Здесь мы использовали PARTITION BY, которое позволило задать размеры нашего окна. Здесь можно указывать какую-то доп информацию, передавать служебные команды, например, номер строки.

Заключение

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

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

Источник