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


Подзапрос во FROM


Результат запроса — тоже таблица

Любой SELECT возвращает таблицу. Значит, его можно поставить в FROM вместо имени таблицы и выбирать из него, как из обычной таблицы.

Производная таблица — подзапрос, записанный в FROM. Его ещё называют подзапросом во FROM.

Агрегат от агрегата

Сколько в среднем оценок приходится на одного ученика (среди тех, у кого они есть)? Хочется написать AVG(COUNT(*)).

Но вложить одну агрегатную функцию в другую нельзя: SQLite ответит misuse of aggregate function COUNT(). Решение — в два шага.

SELECT student_id, COUNT(*) AS cnt
FROM grades
GROUP BY student_id;

Шаг 1 — оценок у каждого ученика:

student_idcnt
16
25
36
45
54
64
74
85
94
104
124
SELECT ROUND(AVG(cnt), 2) AS avg_count
FROM (SELECT student_id, COUNT(*) AS cnt
      FROM grades
      GROUP BY student_id) AS per_student;

Шаг 2 — среднее от этих чисел:

avg_count
4.64

Агрегат от агрегата — через подзапрос во FROM.

Внешний запрос видит подзапрос как таблицу per_student со столбцами student_id и cnt. Поэтому столбцам подзапроса дают понятные имена через AS.

Имя (псевдоним) у подзапроса во FROM в SQLite можно не писать, но в MySQL и в PostgreSQL до версии 16 оно обязательно. Привыкайте писать его всегда.


Соединение с подзапросом

Производную таблицу можно соединять с другими таблицами через JOIN. Выведем средний балл каждого ученика с его именем:

SELECT s.name, ROUND(a.avg_grade, 2) AS avg_grade
FROM students s
JOIN (SELECT student_id, AVG(grade) AS avg_grade
      FROM grades
      GROUP BY student_id) AS a ON a.student_id = s.id
ORDER BY a.avg_grade DESC, s.name;

Результат:

nameavg_grade
Анна Смирнова4.83
Жанна Морозова4.75
Ирина Лебедева4.75
Дина Козлова4.5
Вера Петрова4.0
Кирилл Попов4.0
Максим Егоров4.0
Глеб Соколов3.6
Борис Иванов3.4
Захар Волков3.0
Егор Новиков2.75

Сортировка идёт по точному среднему (a.avg_grade), а выводится округлённое. Если сортировать по округлённому, ученики с близкими средними могут поменяться местами.

Фильтр по вычисленному значению

По столбцам производной таблицы можно фильтровать обычным WHERE. Сколько учеников со средним баллом ниже 3,5?

SELECT COUNT(*) AS weak
FROM (SELECT student_id, AVG(grade) AS avg_grade
      FROM grades
      GROUP BY student_id) AS a
WHERE a.avg_grade < 3.5;

Результат:

weak
3

Коротко

  • Подзапрос во FROM — временная таблица: из неё выбирают, её фильтруют и соединяют с другими таблицами.
  • Он нужен, когда агрегат применяется к результату другого агрегата или фильтр — к вычисленному значению.
  • Давайте подзапросу псевдоним, а его столбцам — имена через AS.

Когда таких подзапросов два-три, запрос становится трудно читать. Следующий урок — о том, как дать подзапросу имя.