SQL – Основы выборки данных – Часть 2
Группировка данных и агрегатные функции (Group By)
Иногда требуется узнать информацию не о самих объектах, а об определенных группах, которые они образуют. Для этого используется оператор GROUP BY и агрегатные функции.
Общая структура запроса
SELECT – поля_таблиц
FROM – список_таблиц
GROUP BY -поля_группировки
Пример использования
Для демонстрации работы оператора GROUP BY выведем общую сумму потраченных средств, сгруппированных по членам семьи.
SELECT family_member, SUM(unit_price * amount)
FROM Payments
GROUP BY family_member
Как это работает:
| family_member | unit_price | amount |
| 1 | 2000 | 1 |
| 2 | 2100 | 1 |
| 3 | 20 | 5 |
| 4 | 350 | 1 |
| 4 | 100 | 2 |
| 5 | 150 | 1 |
| 2 | 120 | 1 |
Для того, чтобы лучше понять как происходит группировка по полю, преобразуем исходные данные в новый, сгруппированный вид:
| family_member | unit_price | amount |
| 1 | 2000 | 1 |
| 300 | 1 | |
| 10 | 3 | |
| 7 | 5 | |
| 8 | 8 | |
| 2 | 2100 | 1 |
| 50 | 5 |
После этого мы можем внутри каждой из этих групп применить формулу суммы, которая умножит количество товара на его стоимость, а потом просуммирует все получившиеся значения:
| family_member | SUM(unit_price*amount) |
| 1 | 2504 |
| 2 | 74194 |
| 3 | 3600 |
| 4 | 650 |
| 5 | 1060 |
Агрегатные функции
Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение.
Агрегатные функции применяются для значений, не равных NULL. Исключением является функция COUNT().
| Функция | Описание |
| SUM(поля_таблицы) | Возвращает сумму значений |
| AVG(поля_таблицы) | Возвращает среднее значение |
| COUNT(поля_таблицы) | Возвращает количество записей |
| MIN(поля_таблицы) | Возвращает минимальное значение |
| MAX(поля_таблицы) | Возвращает максимальное значение |
Having
Для фильтрации строк по значениям агрегатных функций используется оператор HAVING.
Общая структура запроса
SELECT – поля_таблиц
FROM – список_таблиц
GROUP BY -поля_группировки
HAVING – условия_на_ограничение_строк_после_группировки
Отличие HAVING от WHERE:
- WHERE — сначала выбираются записи по условию, а затем могут быть сгруппированы, отсортированы и т.д.
- HAVING — сначала группируются записи, а затем выбираются по условию, при этом, в отличие от WHERE, в нём можно использовать значения агрегатных функций
Пример использования
Выведем общую сумму, потраченную на покупки, для каждого члена семьи, где общая сумма покупки меньше, чем 5000
SELECT family_member, SUM(unit_price * amount) AS sum
FROM Payments
GROUP BY family_member
HAVING sum < 5000
Ссылка на задачи для проверки, которые мы проходили во время урока.
Многотабличные запросы, оператор JOIN
До этого мы рассматривали работу только с одной таблице. В реальности, чаще всего приходится делать выборку из нескольких таблиц, каким-то образом объединяя их. Для их объединения используется оператор JOIN.
Общая структура запроса
SELECT – поля_таблиц
FROM – таблица_1
[INNER] | [[LEFT | RIGHT | FULL] [OUTER]] JOIN – таблица_2
ON – условие_соединения
[[INNER] | [[LEFT | RIGHT | FULL] [OUTER]] JOIN – таблица_n
ON – условие_соединения
Соединение бывает внутренним (INNER) или внешним (OUTER), при этом внешнее соединение делится на левое (LEFT), правое (RIGHT) и полное (FULL).
INNER JOIN
По умолчанию, если не указаны какие-либо параметры, JOIN выполняется как INNER JOIN, то есть как внутреннее (перекрёстное) соединение таблиц.Внутреннее соединение — соединение двух таблиц, при котором каждая запись из первой таблицы соединяется с каждой записью второй таблицы, создавая тем самым все возможные комбинации записей обеих таблиц.
Например, объединим таблицы покупок (Payments) и членов семьи (FamilyMembers) таким образом, чтобы дополнить каждую покупку данными о том, кто её совершил.
| payment_id | date | family_member | good | amount | unit_price |
| 1 | 2005-02-12 | 1 | 1 | 1 | 2000 |
| 2 | 2005-03-23 | 2 | 1 | 1 | 2100 |
| 3 | 2005-05-14 | 3 | 4 | 5 | 20 |
| 4 | 2005-07-22 | 4 | 5 | 1 | 350 |
| 5 | 2005-07-26 | 4 | 7 | 2 | 150 |
| member_id | status | member_name | birthday |
| 1 | father | Headley Quincey | 1960-05-13 |
| 2 | mother | Flavia Quincey | 1963-02-16 |
| 3 | son | Andie Quincey | 1983-06-05 |
| 4 | daughter | Lela Quincey | 1985-06-07 |
| 5 | daughter | Annie Quincey | 1988-04-10 |
Для того, чтобы решить поставленную задачу выполним запрос, который объединяет поля строки из одной таблицы с полями другой, если выполняется условие, что покупатель товара (family_member) совпадает с идентификатором члена семьи (member_id):
SELECT *
FROM Payments
JOIN FamilyMembers ON family_member = member_id
В результате можно увидеть, что каждая строка из таблицы Payments дополнилась данными о члене семьи, который совершил покупку.
| payment_id | date | family_member | good | amount | unit_price | member_id | status | member_name | birthday |
| 1 | 2005-02-12 | 1 | 1 | 1 | 2000 | 1 | father | Headley Quincey | 1960-05-13 |
| 2 | 2005-03-23 | 2 | 1 | 1 | 2100 | 2 | mother | Flavia Quincey | 1963-02-16 |
| 3 | 2005-05-14 | 3 | 4 | 5 | 20 | 3 | son | Andie Quincey | 1983-06-05 |
| 4 | 2005-07-22 | 4 | 5 | 1 | 350 | 4 | daughter | Lela Quincey | 1985-06-07 |
| 5 | 2005-07-26 | 4 | 7 | 2 | 150 | 4 | daughter | Lela Quincey | 1985-06-07 |
Для внутреннего соединения таблиц также можно использовать оператор WHERE. Например, вышеприведённый запрос, написанный с помощью INNER JOIN, будет выглядеть так:
SELECT *
FROM Payments, FamilyMembers
WHERE family_member = member_id
Внешнее соединение может быть трёх типов: левое (LEFT), правое (RIGHT) и полное (FULL). По умолчанию оно является полным.
Главным отличием внешнего соединения от внутреннего является то, что оно обязательно возвращает все строки одной (LEFT, RIGHT) или двух таблиц (FULL).
Внешнее левое соединение (LEFT OUTER JOIN)
Для примера получим из базы данных расписание звонков объединённых с соответствующими занятиями в расписании занятий:
SELECT *
FROM Timepair
LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id;
| id | start_pair | end_pair |
| 1 | 08:30:00 | 09:15:00 |
| 2 | 09:20:00 | 10:05:00 |
| 3 | 10:15:00 | 11:00:00 |
| 4 | 11:05:00 | 11:50:00 |
| 5 | 12:50:00 | 13:35:00 |
| id | date | class | number_pair | teacher | subject | classroom |
| 1 | 2019-09-01 | 9 | 1 | 11 | 1 | 47 |
| 4 | 2019-09-02 | 9 | 1 | 4 | 3 | 13 |
| 7 | 2019-09-03 | 9 | 1 | 5 | 6 | 36 |
| 10 | 2019-09-04 | 9 | 1 | 9 | 9 | 39 |
| 13 | 2019-09-05 | 9 | 1 | 3 | 13 | 43 |
В выборку попали все строки из левой таблицы, дополненные данными о занятиях.
| timepair.id | start_pair | end_pair | schedule.id | date | class | number_pair | teacher | subject | classroom |
| 1 | 08:30 | 09:15 | 4 | 2019-09-02 | 9 | 1 | 4 | 3 | 13 |
| 1 | 08:30 | 09:15 | 7 | 2019-09-03 | 9 | 1 | 5 | 6 | 36 |
| 1 | 08:30 | 09:15 | 16 | 2019-08-30 | 9 | 1 | 9 | 4 | 34 |
| 1 | 08:30 | 09:15 | 10 | 2019-09-04 | 9 | 1 | 11 | 9 | 39 |
| 2 | 09:20 | 10:05 | 2 | 2019-09-01 | 9 | 2 | 8 | 1 | 47 |
| 2 | 09:20 | 10:05 | 5 | 2019-09-02 | 9 | 2 | 2 | 13 | 43 |
Внешнее правое соединение (RIGHT OUTER JOIN)
Соединение, которое возвращает все значения из правой таблицы, соединённые с соответствующими значениями из левой таблицы.
Внешнее полное соединение (FULL OUTER JOIN)
Соединение, которое выполняет внутреннее соединение записей и дополняет их левым внешним соединением и правым внешним соединением.
Алгоритм работы полного соединения:
- Формируется таблица на основе внутреннего соединения (INNER JOIN).
- В таблицу добавляются значения не вошедшие в результат формирования из левой таблицы (LEFT OUTER JOIN).
- В таблицу добавляются значения не вошедшие в результат формирования из правой таблицы (RIGHT OUTER JOIN).
Базовые запросы для разных вариантов объединения таблиц
| Схема | Запрос с JOIN |
 | Получение всех данных из левой таблицы, соединённых с соответствующими данными из правой: SELECT поля_таблиц FROM левая_таблица LEFT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ |
 | Получение всех данных из правой таблицы, соединённых с соответствующими данными из левой: SELECT поля_таблиц FROM левая_таблица RIGHT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ |
 | Получение данных, относящихся только к левой таблице: SELECT поля_таблиц FROM левая_таблица LEFT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ WHERE правая_таблица.ключ IS NULL |
 | Получение данных, относящихся только к правой таблице: SELECT поля_таблиц FROM левая_таблица RIGHT JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ WHERE левая_таблица.ключ IS NULL |
 | Получение всех данных, относящихся к левой и правой таблицам, а также их внутреннему соединению: SELECT поля_таблиц FROM левая_таблица FULL OUTER JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ |
 | Получение всех данных, относящихся к левой и правой таблицам, а также их внутреннему соединению: SELECT поля_таблиц FROM левая_таблица FULL OUTER JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ |
 | Получение данных, не относящихся к левой и правой таблицам одновременно (обратное INNER JOIN): SELECT поля_таблиц FROM левая_таблица FULL OUTER JOIN правая_таблица ON правая_таблица.ключ = левая_таблица.ключ WHERE левая_таблица.ключ IS NULL OR правая_таблица.ключ IS NULL |
Ссылка на задачи для проверки, которые мы проходили во время урока.
