Group by where: SQL GROUP BY Statement

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

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

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

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

Photo by Akua Sencherey on Unsplash

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

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

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

Таблица EMPLOYEE:

EMPLOYEE

Таблица DEPARTMENT:

DEPARTMENT

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

• Найти имена сотрудников отдела IT
• Посчитать количество сотрудников каждого отдела с зарплатой выше 80 000.

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

SELECT LAST_NAME, FIRST_NAME
  FROM EMPLOYEE
 WHERE DEPARTMENT = 'IT'

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

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

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

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

• FROM (выбор таблицы)
• WHERE (фильтрация строк)
• SELECT (возврат результирующего датасета).

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

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

SELECT LAST_NAME, FIRST_NAME
    FROM EMPLOYEE
   WHERE DEPARTMENT = 'IT'
ORDER BY FIRST_NAME

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

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

1. FROM (выбор таблицы)
2. WHERE (фильтрация строк)
3. SELECT (возврат результирующего датасета)
4. ORDER BY (сортировка)

GROUP BY и HAVING

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

SELECT DEPARTMENT, COUNT(*)
    FROM EMPLOYEES
   WHERE SALARY > 80000
GROUP BY DEPARTMENT
ORDER BY COUNT(*) DESC

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

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

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

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

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

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

1. FROM (выбор таблицы)
2. WHERE (фильтрация строк)
3. GROUP BY (агрегирование данных)
4. SELECT (возврат результирующего датасета)
5. ORDER BY (сортировка).

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

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

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

SELECT DEPARTMENT
    FROM EMPLOYEES
   WHERE DEPARTMENT <> 'SALES'
GROUP BY DEPARTMENT
  HAVING AVG(SALARY) > 80000

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

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

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

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

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

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

1. FROM (выбор таблицы)
2. WHERE (фильтрация строк)
3. GROUP BY (агрегирование данных)
4. HAVING (фильтрация агрегированных данных)
5. SELECT (возврат результирующего датасета).

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

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

SELECT EMPLOYEE_ID, LAST_NAME
  FROM EMPLOYEES
  JOIN DEPARTMENT
    ON DEPARTMENT = DEPT_NAME
 WHERE BUDGET > 275000

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

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

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

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

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

1. FROM (выбор таблицы)
2. JOIN (комбинация с подходящими по условию данными из второй таблицы)
3. WHERE (фильтрация строк)
4. SELECT (возврат результирующего датасета).

Итог

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

1. FROM (выбор таблицы)
2. JOIN (комбинация с подходящими по условию данными из других таблиц)
3. WHERE (фильтрация строк)
4. GROUP BY (агрегирование данных)
5. HAVING (фильтрация агрегированных данных)
6. SELECT (возврат результирующего датасета)
7. ORDER BY (сортировка).

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

Группировать по, иметь и где предложения в SQL

Введение

В этом блоге мы обсудим, как работать с предложениями GROUP BY, WHERE и HAVING в SQL, и простым способом объясним эту концепцию на примере. Я надеюсь, что это будет очень полезно для начинающих и продолжающих, чтобы помочь им понять основную концепцию.

Группировка по статье

Предложение Group by часто используется для организации идентичных повторяющихся данных в группы с оператором select для группировки набора результатов по одному или нескольким столбцам. Это предложение работает с определенным списком элементов select, и мы можем использовать предложения HAVING и ORDER BY. Предложение group by всегда работает с агрегатной функцией, такой как MAX, MIN, SUM, AVG, COUNT.

Давайте обсудим предложение group by на примере. У нас есть таблица VehicleProduction, в которой есть несколько моделей с ценой, и в ней есть дублирующиеся данные. Мы хотим отнести эти данные к другой группе с соответствующей общей ценой.

Пример

1. Создать таблицу VehicleProduction    
2. (    
3. Id int первичный ключ Идентификация,     
4. Модель varchar(50),    
5. Цена деньги    
6. )    
7.     
8. Вставить в VehicleProduction значения(‘L551’, 850000),(‘L551’, 850000),(‘L551’, 850000),(‘L551’, 750000),    
9. (‘L538’, 650000),(‘L538’, 650000),(‘L538’, 550000),(‘L530’, 450000),(‘L530’,350000), (‘L545’, 250000)    
10.     
11. Выберите * из VehicleProduction

Выход

Агрегирующие функции

Функция MAX()- возвращает максимальное значение числового столбца указанных критериев.

Пример

1. Выберите max(Price) As MaximumCostOfModel из VehicleProduction

Выход

Функция MIN()- возвращает минимум числового столбца указанных критериев.

Пример

1. Выберите Min(Price) как MinimumCostOfModel из VehicleProduction

Выход

Функция SUM()- возвращает общую сумму числового столбца указанных критериев.

Пример

1. Выберите SUM(Price) как SumCostOfAllModel из VehicleProduction

Выход

Функция AVG()- возвращает среднее значение числового столбца указанных критериев.

Пример

1. Выберите AVG(Price) как AverageCostOfModel из VehicleProduction

Выход

Функция COUNT()- возвращает количество строк, соответствующих заданным критериям.

Пример

1. Выберите «Количество (цена)» как «TotalVehicleModels» из VehicleProduction

Выход

Особая оговорка

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

Пример

1. Выберите Отличительные (Модель),  Цена из Производства Автомобиля

Выход

Предложение Group by

Предложение Group by часто используется для организации идентичных повторяющихся данных в группы с помощью оператора select. Это предложение работает с выбором определенного списка элементов, для этого мы можем использовать предложения HAVING и ORDER BY.

Синтаксис

1. ВЫБЕРИТЕ Столбец1, Столбец2    
2. ОТ ИмяТаблицы    
3. ГРУППИРОВАТЬ ПО столбцу 1, столбцу 2

Пример

1. Выберите * из VehicleProduction     
2.     
3. Выберите Модель, Цена из VehicleProduction     
4. группа по модели, цене

Выход

Давайте рассмотрим пример GROUP BY с агрегатными функциями.

GROUP BY с агрегатными функциями

Пример

1. Выберите Model, Price, Count(*) As QtyOfModel, Sum(Price) As TotPriceOfModel  из VehicleProduction     
2. группа по модели, цене

Выход

Где пункт

Предложение Where работает с предложением select, но не работает с условием функции group by или агрегата.

Пример 1

1. Выберите модель, цену от VehicleProduction     
2. , где Модель != ‘L530’    
3. группа по модели, цене

Выход

 

Пример 2

 

Мы не можем использовать предложение where после группировки по предложению

1. Выберите модель, цену от производителя автомобиля    
2. группа по модели, цене     
3. , где Модель != L530.

Выход

 

Наличие пункта

 

Предложение Наличие работает с предложением group by, но особенно работает с условием агрегатной функции.

 

Пример

1. Выберите модель, цену от VehicleProduction     
2. Группировка по модели, цене     
3. Сумма (Цена)  > 600000,00

Выход

 

ORDER BY пункт

 

Предложение Order By показывает записи в порядке возрастания или убывания определенного условия.

 

Пример


1. Выберите модель, цену от производителя автомобиля    
2. Группировка по модели, цене     
3. Сумма (Цена)  > 400000,00     
4. заказ по цене по описанию

Выход

 

Резюме

 

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

Разница между предложениями Where и Group By

Where и Group By используются для фильтрации строк, возвращаемых запросом, на основе условия. Ниже приведены различия. Предложение WHERE определяет условия поиска для строк, возвращаемых запросом, и ограничивает количество строк определенным набором строк. Если в таблице огромное количество записей и если кто-то хочет получить определенные записи, полезно использовать предложение «где». GROUP BY пункт суммирует идентичные строки в одну/отдельную группу и возвращает одну строку со сводкой для каждой группы, используя соответствующую функцию агрегирования в списке SELECT, такую ​​как COUNT(), AVG(), SUM(), MIN(), MAX. () и т. д.

Вариант использования: Предположим, что некоторая торговая компания хочет получить список клиентов, которые купили некоторое количество товаров в прошлом году, чтобы они могли продать им больше товаров в этом году. Существует таблица SalesOrder со столбцами CustomerId, SalesOrderId, Order_Date, OrderNumber, OrderItem, UnitPrice, OrderQty Теперь нам нужно получить клиентов, которые сделали заказы в прошлом году, т.е. 2017 Использование предложения Where —

 SELECT *
ОТ [Продажи].[Заказы]
ГДЕ Order_Date >= '2017-01-01 00:00:00.000'
AND Order_Date < '2018-01-01 00:00:00. 000' 

Это вернет набор строк со всеми клиентами и соответствующими заказами 2017 года. Использование предложения Group By –

 SELECT CustomerID, COUNT(*) AS OrderNumbers
ОТ [Продажи].[Заказы]
ГДЕ Order_Date >= '2017-01-01 00:00:00.000'
И Order_Date < '2018-01-01 00:00:00.000'
СГРУППИРОВАТЬ ПО CustomerId 

Это вернет набор строк клиентов (CustomerId), которые сделали заказы в 2017 году, и общее количество заказов, сделанных каждым клиентом. Использование предложения «Имея» — Предложение «Имея» используется для фильтрации значений в предложении «Группировать по». Приведенный ниже запрос отфильтровывает некоторые строки

 SELECT SalesOrderID,
         SUM(UnitPrice* OrderQty) AS TotalPrice
ОТ Sales.SalesOrderDetail
СГРУППИРОВАТЬ ПО SalesOrderID
HAVING TotalPrice > 5000 

Поскольку видимость предложения WHERE — одна строка за раз, у него нет возможности оценить СУММУ по всем SalesOrderID. Предложение HAVING оценивается после создания группировки.