Связи таблиц: JOIN и группировка GROUP BY
Высший пилотаж SQL: соединять данные из разных таблиц и считать сводные показатели — то, ради чего и создавали базы данных.
JOIN — операция, соединяющая строки двух таблиц по условию (обычно по совпадению ключей), чтобы получить связанные данные вместе.
Зачем это нужно
В прошлом уроке мы разнесли данные по связанным таблицам, чтобы избежать дублирования. Но теперь имена учеников в одной таблице, а их оценки — в другой. Чтобы увидеть «кто какую оценку получил», таблицы нужно соединить. А чтобы ответить на вопросы вроде «средний балл по каждому предмету» или «сколько заказов у каждого клиента», нужны группировка и агрегатные функции. Это вершина базового SQL и самый частый инструмент аналитика. Всё — в живой песочнице.
Здесь раскрывается красота реляционной модели, ради которой всё и затевалось. В прошлом уроке разбиение на таблицы могло показаться усложнением: вместо одной таблицы — три, вместо имени — какой-то номер-ссылка. Но именно благодаря этому разбиению база остаётся аккуратной и непротиворечивой, а JOIN в любой момент собирает разрозненные кусочки обратно в удобную для чтения форму — причём ровно в том виде, который нужен под конкретный вопрос. Это и есть главный принцип: данные хранятся нормализованно (без дублирования), а представляются гибко (как удобно для отчёта). Добавьте сюда группировку, и SQL превращается в инструмент анализа данных: из сырых записей «кто, когда, что» рождаются сводки, средние, рейтинги, тренды. Аналитики данных проводят за такими запросами рабочие дни, и навык, который вы получите в этом уроке, — прямой вход в эту профессию. Это действительно вершина базового SQL: дальше идут лишь нюансы и оптимизация.
Готовим связанные таблицы
Воссоздадим учеников, предметы и оценки. Чтобы каждый блок был самодостаточным, мы каждый раз создаём и наполняем все три таблицы:
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO students VALUES (1,'Анна'),(2,'Борис'),(3,'Вера');
CREATE TABLE subjects (id INTEGER PRIMARY KEY, title TEXT);
INSERT INTO subjects VALUES (1,'Информатика'),(2,'Математика');
CREATE TABLE grades (
id INTEGER PRIMARY KEY,
student_id INTEGER, subject_id INTEGER, mark INTEGER
);
INSERT INTO grades VALUES
(1,1,1,5),(2,1,2,4),(3,2,1,3),(4,2,2,5),(5,3,1,5),(6,3,2,5);
SELECT * FROM grades;Вывод:
id | student_id | subject_id | mark 1 | 1 | 1 | 5 2 | 1 | 2 | 4 3 | 2 | 1 | 3 4 | 2 | 2 | 5 5 | 3 | 1 | 5 6 | 3 | 2 | 5
JOIN: соединяем имена с оценками
Сами по себе оценки — это набор номеров. Чтобы увидеть имена и названия предметов, соединим три таблицы по ключам. Условие соединения пишут в ON: «строки совпадают там, где student_id равен id ученика». Получаем читаемую ведомость:
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO students VALUES (1,'Анна'),(2,'Борис'),(3,'Вера');
CREATE TABLE subjects (id INTEGER PRIMARY KEY, title TEXT);
INSERT INTO subjects VALUES (1,'Информатика'),(2,'Математика');
CREATE TABLE grades (id INTEGER PRIMARY KEY, student_id INTEGER, subject_id INTEGER, mark INTEGER);
INSERT INTO grades VALUES (1,1,1,5),(2,1,2,4),(3,2,1,3),(4,2,2,5),(5,3,1,5),(6,3,2,5);
SELECT students.name, subjects.title, grades.mark
FROM grades
JOIN students ON grades.student_id = students.id
JOIN subjects ON grades.subject_id = subjects.id
ORDER BY students.name;Вывод:
name | title | mark Анна | Информатика | 5 Анна | Математика | 4 Борис | Информатика | 3 Борис | Математика | 5 Вера | Информатика | 5 Вера | Математика | 5
Агрегатные функции: считаем по всем строкам
Агрегатные функции сводят множество строк к одному числу: COUNT считает количество, SUM суммирует, AVG усредняет, MIN и MAX находят крайние значения. Узнаем общую статистику по всем оценкам сразу:
CREATE TABLE grades (id INTEGER PRIMARY KEY, student_id INTEGER, subject_id INTEGER, mark INTEGER);
INSERT INTO grades VALUES (1,1,1,5),(2,1,2,4),(3,2,1,3),(4,2,2,5),(5,3,1,5),(6,3,2,5);
SELECT COUNT(*) AS total,
AVG(mark) AS average,
MIN(mark) AS lowest,
MAX(mark) AS highest
FROM grades;Вывод:
total | average | lowest | highest 6 | 4.5 | 3 | 5
GROUP BY: статистика по группам
Самое мощное — считать агрегаты по группам. GROUP BY разбивает строки на группы по значению поля, и агрегатная функция применяется к каждой группе отдельно. Посчитаем средний балл каждого ученика — соединим имена и сгруппируем:
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO students VALUES (1,'Анна'),(2,'Борис'),(3,'Вера');
CREATE TABLE grades (id INTEGER PRIMARY KEY, student_id INTEGER, subject_id INTEGER, mark INTEGER);
INSERT INTO grades VALUES (1,1,1,5),(2,1,2,4),(3,2,1,3),(4,2,2,5),(5,3,1,5),(6,3,2,5);
SELECT students.name,
COUNT(*) AS grade_count,
AVG(grades.mark) AS avg_mark
FROM grades
JOIN students ON grades.student_id = students.id
GROUP BY students.name
ORDER BY avg_mark DESC;Вывод:
name | grade_count | avg_mark Вера | 2 | 5.0 Анна | 2 | 4.5 Борис | 2 | 4.0
HAVING: фильтр для групп
Условие WHERE фильтрует строки до группировки, а HAVING — группы после. Если нужно оставить только группы, удовлетворяющие условию по агрегату (например, отличников со средним баллом не ниже 4.5), используют именно HAVING:
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO students VALUES (1,'Анна'),(2,'Борис'),(3,'Вера');
CREATE TABLE grades (id INTEGER PRIMARY KEY, student_id INTEGER, subject_id INTEGER, mark INTEGER);
INSERT INTO grades VALUES (1,1,1,5),(2,1,2,4),(3,2,1,3),(4,2,2,5),(5,3,1,5),(6,3,2,5);
SELECT students.name, AVG(grades.mark) AS avg_mark
FROM grades
JOIN students ON grades.student_id = students.id
GROUP BY students.name
HAVING AVG(grades.mark) >= 4.5
ORDER BY avg_mark DESC;Вывод:
name | avg_mark Вера | 5.0 Анна | 4.5
Попробуй сам
Соберите отчёт по предметам: для каждого предмета — число оценок и средний балл, и оставьте только предметы со средним выше 4. Это сразу JOIN + GROUP BY + HAVING — полный набор. Меняйте порог в HAVING.
CREATE TABLE subjects (id INTEGER PRIMARY KEY, title TEXT);
INSERT INTO subjects VALUES (1,'Информатика'),(2,'Математика');
CREATE TABLE grades (id INTEGER PRIMARY KEY, student_id INTEGER, subject_id INTEGER, mark INTEGER);
INSERT INTO grades VALUES (1,1,1,5),(2,1,2,4),(3,2,1,3),(4,2,2,5),(5,3,1,5),(6,3,2,5);
SELECT subjects.title,
COUNT(*) AS marks,
AVG(grades.mark) AS avg_mark
FROM grades
JOIN subjects ON grades.subject_id = subjects.id
GROUP BY subjects.title
HAVING AVG(grades.mark) > 4
ORDER BY avg_mark DESC;Вывод:
title | marks | avg_mark Математика | 3 | 4.666666666666667 Информатика | 3 | 4.333333333333333
Частые ошибки
- Забывают условие ON в JOIN. Без него соединяются все строки со всеми (декартово произведение) — получается мусор.
- Путают WHERE и HAVING. WHERE фильтрует строки до группировки, HAVING — группы после неё по агрегату.
- Выбирают негруппированное поле. В запросе с GROUP BY в SELECT можно брать только поля группировки и агрегаты.
- Ждут целое от AVG. Среднее — дробное число; при необходимости его округляют функцией
ROUND.
Итоги
JOIN … ONсоединяет таблицы по совпадению ключей, собирая связанные данные вместе.- Агрегатные функции (
COUNT,SUM,AVG,MIN,MAX) сводят множество строк к одному числу. GROUP BYприменяет агрегаты к каждой группе строк по значению поля.HAVINGфильтрует уже сгруппированные данные по агрегату (в отличие отWHERE).
