ИТ.03 - 10 - Вложенные запросы: общее понимание

Введение

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

В этой лекции рассмотрим:

• типы подзапросов и ситуации, где они применяются;
• правила использования подзапросов в SELECT, WHERE, FROM;
• приёмы с одной таблицей: вычисления по агрегатам, динамические списки значений и коррелированные проверки.

Что такое вложенный запрос

Подзапрос — это оператор SELECT, заключённый в круглые скобки и встроенный в другой SQL-запрос. Он может появляться в блоках SELECT, WHERE, HAVING, FROM, а также в списке значений для INSERT.

-- Внешний запрос (уровень 0)
SELECT ...
FROM (
  -- Подзапрос уровня 1
  SELECT ...
  FROM (
    -- Подзапрос уровня 2 (самый глубокий)
    SELECT ...
    FROM источники_данных
    WHERE ...
  ) AS level_2
) AS level_1;

Учебная база данных «Trainer Workouts»

CREATE TABLE
  workouts (
    id INTEGER PRIMARY KEY,
    athlete TEXT NOT NULL,
    session_type TEXT NOT NULL,
    duration INTEGER NOT NULL,
    calories INTEGER NOT NULL,
    effort TEXT NOT NULL CHECK (effort IN ('low', 'medium', 'high')),
    training_date TEXT NOT NULL
  )
;

INSERT INTO
  workouts (
    id,
    athlete,
    session_type,
    duration,
    calories,
    effort,
    training_date
  )
VALUES
  (1, 'Анна П.', 'Бег по парку', 45, 520, 'medium', '2024-02-12'),
  (2, 'Денис Р.', 'Велопрогулка', 30, 360, 'high', '2024-02-12'),
  (3, 'Мария К.', 'Силовая тренировка', 55, 610, 'high', '2024-02-13'),
  (4, 'Илья С.', 'Плавание', 45, 470, 'medium', '2024-02-14'),
  (5, 'Анна П.', 'Йога', 50, 280, 'low', '2024-02-15'),
  (6, 'Денис Р.', 'Бег по парку', 38, 450, 'medium', '2024-02-16'),
  (7, 'Мария К.', 'Велопрогулка', 65, 700, 'high', '2024-02-16'),
  (8, 'Илья С.', 'Бег по парку', 30, 510, 'high', '2024-02-17'),
  (9, 'Павел Т.', 'Бег по парку', 38, 540, 'high', '2024-02-17')
;

Чтобы убедиться в исходных данных, выполните простой SELECT:

SELECT * FROM workouts;

Типы подзапросов по возвращаемому результату

• Скалярный — возвращает одну строку и один столбец. Подходит для сравнений вида duration > (SELECT AVG(...)).
• Списковый — возвращает одну колонку, но несколько строк. Используется с IN.
• Табличный — возвращает таблицу с несколькими столбцами. Подставляем во FROM и обязательно задаём псевдоним.
• Коррелированный — подзапрос, который ссылается на текущую строку внешнего запроса. Выполняется повторно для каждой строки.

Скалярные подзапросы

Скалярным подзапросом удобно вычислять агрегаты и сразу использовать результат.

Пример: тренировки длиннее средней

SELECT athlete, session_type, duration
FROM workouts
WHERE duration > (
  SELECT AVG(duration)
  FROM workouts
);

Внутренний SELECT возвращает одно число — среднюю длительность. Внешний запрос получает готовый порог и сравнивает с ним каждую тренировку.

Пример: максимальные калории

SELECT athlete, session_type, calories
FROM workouts
WHERE calories = (
  SELECT MAX(calories)
  FROM workouts
);

Если несколько тренировок сожгли одинаковое максимальное количество калорий, условие вернёт их все без сортировки и LIMIT.

Списковые подзапросы (IN)

Когда подзапрос возвращает одну колонку и несколько строк, мы получаем динамический список значений. Его можно использовать как фильтр.

Пример: повторяющиеся длительности

Найдём тренировки, у которых длительность встречается минимум два раза.

SELECT athlete, session_type, duration
FROM workouts
WHERE duration IN (
  SELECT duration
  FROM workouts
  GROUP BY duration
  HAVING COUNT(*) > 1
)
ORDER BY duration, training_date;

Сначала формируем список значений длительностей с повторами, затем подставляем его во внешний WHERE.

Пример: активные форматы тренировок

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

SELECT athlete, session_type, calories
FROM workouts
WHERE session_type IN (
  SELECT session_type
  FROM workouts
  GROUP BY session_type
  HAVING AVG(calories) > 500
)
ORDER BY session_type, calories DESC;

Внутренний GROUP BY сгруппировал строки по типу занятий и оставил только те форматы, где средние калории выше порога.

Табличные подзапросы (FROM)

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

Пример: сводная статистика по длительности и калориям

Соберём средние значения по каждому уровню усилий, а затем сравним их с общей средней длительностью.

SELECT
  effort,
  ROUND(avg_duration, 1) AS avg_duration,
  ROUND(avg_calories, 1) AS avg_calories,
  ROUND(
    avg_duration - (
      SELECT AVG(duration)
      FROM workouts
    ),
    1
  ) AS duration_delta
FROM (
  SELECT
    effort,
    AVG(duration) AS avg_duration,
    AVG(calories) AS avg_calories
  FROM workouts
  GROUP BY effort
) AS stats
ORDER BY duration_delta DESC;

Теперь подзапрос stats создаёт мини-таблицу со средними значениями по каждому уровню нагрузки. Внешний запрос обращается к этой таблице и для каждой строки вызывает скалярный подзапрос с общей средней длительностью, чтобы вычислить отклонение duration_delta и понять, какие тренировки заметно длиннее средней.

Пример: расчёт интенсивности тренировки

Пусть тренер оценивает эффективность занятия показателем intensity = calories / duration, то есть сколько килокалорий сжигается за одну минуту. Сначала посчитаем интенсивность каждого занятия, а затем оставим только те, что выше средней.

SELECT athlete, session_type, ROUND(intensity, 2) AS intensity
FROM (
  SELECT
    athlete,
    session_type,
    1.0 * calories / duration AS intensity
  FROM workouts
) AS energy
WHERE intensity > (
  SELECT AVG(1.0 * calories / duration)
  FROM workouts
)
ORDER BY intensity DESC;

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

Коррелированные подзапросы (EXISTS)

Коррелированный подзапрос зависит от текущей строки внешнего запроса. Он полезен, когда нужно проверить наличие «пары» в той же таблице.

Пример: форматы, популярные у нескольких спортсменов

SELECT DISTINCT
  outer_w.session_type,
  (
    SELECT COUNT(DISTINCT inner_w.athlete)
    FROM workouts AS inner_w
    WHERE inner_w.session_type = outer_w.session_type
  ) AS athlete_count
FROM workouts AS outer_w
WHERE EXISTS (
  SELECT 1
  FROM workouts AS inner_w
  WHERE inner_w.session_type = outer_w.session_type
    AND inner_w.athlete <> outer_w.athlete
)
ORDER BY athlete_count DESC, outer_w.session_type;

Для каждой строки внешнего запроса выполняется внутренний поиск. Как только находится другой спортсмен с тем же типом занятия, условие EXISTS срабатывает. Дополнительно скалярный подзапрос в списке SELECT считает количество уникальных спортсменов для каждого типа тренировки, чтобы сразу увидеть масштаб популярности.

Ограничения и рекомендации

• Не забывайте проверять тип результата подзапроса. Если нужен один столбец, но вы выбрали SELECT *, СУБД вернёт ошибку «подзапрос возвращает более одной колонки».
• Чем глубже вложенность, тем сложнее читаемость. Делите большую конструкцию на несколько подзапросов и давайте им осмысленные псевдонимы.
• Подзапросы позволяют выразить сложную логику, оставаясь в рамках одной таблицы.

Подведем итоги

• Подзапрос — это обычный SELECT, который выполняется внутри другого запроса и передаёт ему результат.
• Тип подзапроса определяется количеством столбцов и строк, которые он возвращает.
• В следующих темах мы расширим этот подход на несколько таблиц.

Самопроверка

Практические задания

Задание 1. Скалярный подзапрос по максимальной длительности

-- Ваш код можете писать тут

SELECT athlete, session_type, training_date, duration
FROM workouts
WHERE training_date = (
  SELECT training_date
  FROM workouts
  ORDER BY duration DESC
  LIMIT 1
)
ORDER BY athlete;

Задание 2. Списковый подзапрос IN по средним значениям

-- Ваш код можете писать тут

SELECT athlete, session_type, calories
FROM workouts
WHERE session_type IN (
  SELECT session_type
  FROM workouts
  GROUP BY session_type
  HAVING AVG(calories) > 500
)
ORDER BY calories DESC;

Задание 3. Табличный подзапрос и отбор по агрегату

-- Ваш код можете писать тут

SELECT training_date, total_duration
FROM (
  SELECT training_date, SUM(duration) AS total_duration
  FROM workouts
  GROUP BY training_date
) AS day_stats
WHERE total_duration > 60
ORDER BY training_date;

Задание 4. Коррелированный подзапрос для сравнения с максимумом

-- Ваш код можете писать тут

SELECT outer_w.athlete, outer_w.session_type, outer_w.duration
FROM workouts AS outer_w
WHERE EXISTS (
  SELECT 1
  FROM workouts AS inner_w
  WHERE inner_w.athlete = outer_w.athlete
    AND inner_w.duration > outer_w.duration
)
ORDER BY outer_w.athlete, outer_w.duration;