Связи таблиц: JOIN и группировка GROUP BY

~10 минут · 6 примеров · 3 вопроса

Высший пилотаж 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).

Проверь себя

Три вопроса по уроку. Ответы никуда не идут — это для тебя.

1Что делает операция JOIN?

2Чем HAVING отличается от WHERE?

3Какая функция посчитает количество строк в группе?

Следующий урок
20 · Электронные таблицы: формулы, ссылки и анализ данных Открыть урок 20
Урок 18: Язык SQL: SELECT, WHERE и сортировка