Модуль: Подзапросы и CTE


Подзапросы: зачем и на чём учимся


Один вопрос — несколько шагов

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

«Кто учится лучше среднего по своему классу?» Сначала нужно посчитать средний балл каждого класса, потом сравнить с ним каждого ученика. Результат одного запроса нужен другому.

Подзапрос — это запрос SELECT, записанный внутри другого запроса. С подзапросами такие вопросы записываются одной командой.

Где это нужно в работе

Подзапросы — повседневный инструмент аналитика данных и бэкенд-разработчика. Вот вопросы, которые они решают теми же приёмами, что и в этом модуле:

  • интернет-магазин: покупатели, которые ничего не заказывали за последний месяц, — список для рассылки;
  • сервис доставки: курьеры, у которых среднее время доставки больше среднего по городу;
  • банк: клиенты, у которых все платежи прошли без просрочки;
  • любая компания: вся цепочка начальников сотрудника — для согласования документов.

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

Карта модуля

  1. Скалярный подзапрос — одно значение внутри запроса.
  2. IN и NOT IN — сравнение со списком из подзапроса и ловушка с NULL.
  3. Коррелированные подзапросы и EXISTS.
  4. Подзапрос во FROM — агрегат от агрегата.
  5. WITH — подзапросы с именами.
  6. WITH RECURSIVE — деревья и последовательности.
  7. Итоги.

После каждого урока — вопрос на понимание и задачи на SQL. Задачу проверяет сайт: выполняет ваш запрос на учебной базе и сравнивает вывод с правильным строка в строку.

Правильный вывод показан на странице задачи в блоке «Примеры». Сверяйте с ним свой результат, но запрос пишите честно: вывод должен получаться из данных.


Учебная база «Школа»

Во всех примерах и задачах модуля одна и та же база «Школа». В задачах она уже создана: вы пишете только запрос.

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

ТаблицаЧто хранитСтолбцы
classesклассыid, name — название («7А»), teacher_id — классный руководитель (ссылка на staff.id)
studentsученикиid, name — имя и фамилия, class_id — класс (ссылка на classes.id)
subjectsпредметыid, name
gradesоценкиid, student_id — чья оценка, subject_id — по какому предмету, grade — оценка от 2 до 5
staffсотрудники школыid, name, position — должность, boss_id — непосредственный начальник (ссылка на staff.id этой же таблицы)

Связи между таблицами:

  • students.class_id → classes.id — в каком классе учится ученик;
  • grades.student_id → students.id и grades.subject_id → subjects.id — чья оценка и по какому предмету;
  • classes.teacher_id → staff.id — классный руководитель;
  • staff.boss_id → staff.id — кто чей начальник. У директора начальника нет, его boss_id — NULL.

Данные

classes

idnameteacher_id
17А4
27Б6
38А5
49А7

students

idnameclass_id
1Анна Смирнова1
2Борис Иванов1
3Вера Петрова1
4Глеб Соколов2
5Дина Козлова2
6Егор Новиков2
7Жанна Морозова3
8Захар Волков3
9Ирина Лебедева3
10Кирилл Попов3
11Лиза ОрловаNULL
12Максим Егоров2

subjects

idname
1Математика
2Русский язык
3Информатика
4История

staff

idnamepositionboss_id
1Ольга НиколаевнадиректорNULL
2Ирина Павловназавуч1
3Сергей Петровичзавуч1
4Анна Викторовнаучитель математики2
5Павел Андреевичучитель информатики3
6Мария Сергеевнаучитель русского языка2
7Наталья Ивановнаучитель истории3
8Дмитрий Олеговичлаборант5

grades — 51 оценка. Чтобы их было удобно читать, ниже они сгруппированы по ученикам:

  • Анна Смирнова (id 1) — математика: 5, 4, 5; русский язык: 5, 5; информатика: 5
  • Борис Иванов (id 2) — математика: 3, 4; русский язык: 3, 3; информатика: 4
  • Вера Петрова (id 3) — математика: 4, 4; русский язык: 5, 4; информатика: 3, 4
  • Глеб Соколов (id 4) — математика: 2, 3; русский язык: 3; информатика: 5, 5
  • Дина Козлова (id 5) — математика: 5, 5; русский язык: 4; информатика: 4
  • Егор Новиков (id 6) — математика: 3; русский язык: 2, 3; информатика: 3
  • Жанна Морозова (id 7) — математика: 4, 5; русский язык: 5; информатика: 5
  • Захар Волков (id 8) — математика: 3, 3; русский язык: 4; информатика: 2, 3
  • Ирина Лебедева (id 9) — математика: 5; русский язык: 5, 5; информатика: 4
  • Кирилл Попов (id 10) — математика: 4; русский язык: 3; информатика: 5, 4
  • Лиза Орлова (id 11) — оценок нет
  • Максим Егоров (id 12) — математика: 4, 4; русский язык: 4; информатика: 4

Особые случаи пригодятся

В базе специально есть «неудобные» данные. На них видно, чем отличаются разные способы записать один и тот же запрос.

  • Лиза Орлова — новенькая: её ещё не записали в класс (class_id = NULL), и оценок у неё нет.
  • 9А — класс, в котором пока нет ни одного ученика.
  • История — предмет, по которому ещё не поставлено ни одной оценки.

Названия классов записаны русскими буквами: в «7А» и «8А» — русская «А». Условие name = '8A' с латинской A ничего не найдёт.


Как поработать с базой самостоятельно

Запросы из уроков полезно запускать и менять самому.

  1. Откройте онлайн SQL-тренажёр.
  2. Скопируйте скрипт ниже, вставьте в редактор и выполните — база «Школа» будет создана.
  3. Пишите запросы и смотрите результат.

Скрипт базы «Школа»:

CREATE TABLE classes (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    teacher_id INTEGER
);
INSERT INTO classes (id, name, teacher_id) VALUES
    (1, '7А', 4),
    (2, '7Б', 6),
    (3, '8А', 5),
    (4, '9А', 7);

CREATE TABLE students (
    id       INTEGER PRIMARY KEY,
    name     TEXT NOT NULL,
    class_id INTEGER
);
INSERT INTO students (id, name, class_id) VALUES
    (1,  'Анна Смирнова',  1),
    (2,  'Борис Иванов',   1),
    (3,  'Вера Петрова',   1),
    (4,  'Глеб Соколов',   2),
    (5,  'Дина Козлова',   2),
    (6,  'Егор Новиков',   2),
    (7,  'Жанна Морозова', 3),
    (8,  'Захар Волков',   3),
    (9,  'Ирина Лебедева', 3),
    (10, 'Кирилл Попов',   3),
    (11, 'Лиза Орлова',    NULL),
    (12, 'Максим Егоров',  2);

CREATE TABLE subjects (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO subjects (id, name) VALUES
    (1, 'Математика'),
    (2, 'Русский язык'),
    (3, 'Информатика'),
    (4, 'История');

CREATE TABLE grades (
    id         INTEGER PRIMARY KEY,
    student_id INTEGER NOT NULL,
    subject_id INTEGER NOT NULL,
    grade      INTEGER NOT NULL
);
INSERT INTO grades (id, student_id, subject_id, grade) VALUES
    (1,  1, 1, 5), (2,  1, 1, 4), (3,  1, 1, 5), (4,  1, 2, 5), (5,  1, 2, 5), (6,  1, 3, 5),
    (7,  2, 1, 3), (8,  2, 1, 4), (9,  2, 2, 3), (10, 2, 2, 3), (11, 2, 3, 4),
    (12, 3, 1, 4), (13, 3, 1, 4), (14, 3, 2, 5), (15, 3, 2, 4), (16, 3, 3, 3),
    (17, 4, 1, 2), (18, 4, 1, 3), (19, 4, 2, 3), (20, 4, 3, 5), (21, 4, 3, 5),
    (22, 5, 1, 5), (23, 5, 1, 5), (24, 5, 2, 4), (25, 5, 3, 4),
    (26, 6, 1, 3), (27, 6, 2, 2), (28, 6, 2, 3), (29, 6, 3, 3),
    (30, 7, 1, 4), (31, 7, 1, 5), (32, 7, 2, 5), (33, 7, 3, 5),
    (34, 8, 1, 3), (35, 8, 1, 3), (36, 8, 2, 4), (37, 8, 3, 2), (38, 8, 3, 3),
    (39, 9, 1, 5), (40, 9, 2, 5), (41, 9, 2, 5), (42, 9, 3, 4),
    (43, 10, 1, 4), (44, 10, 2, 3), (45, 10, 3, 5), (46, 10, 3, 4),
    (47, 12, 1, 4), (48, 12, 1, 4), (49, 12, 2, 4), (50, 12, 3, 4),
    (51, 3, 3, 4);

CREATE TABLE staff (
    id       INTEGER PRIMARY KEY,
    name     TEXT NOT NULL,
    position TEXT NOT NULL,
    boss_id  INTEGER
);
INSERT INTO staff (id, name, position, boss_id) VALUES
    (1, 'Ольга Николаевна',  'директор',             NULL),
    (2, 'Ирина Павловна',    'завуч',                1),
    (3, 'Сергей Петрович',   'завуч',                1),
    (4, 'Анна Викторовна',   'учитель математики',   2),
    (5, 'Павел Андреевич',   'учитель информатики',  3),
    (6, 'Мария Сергеевна',   'учитель русского языка', 2),
    (7, 'Наталья Ивановна',  'учитель истории',      3),
    (8, 'Дмитрий Олегович',  'лаборант',             5);