Агрегирование и подзапросы — продолжение (§9–§12)

1.

Агрегирование
и подзапросы
Как база данных отвечает на статистические вопросы: пять функций агрегирования,
группировка, фильтр групп и вложенные запросы

2.

Где мы сейчас
Что уже пройдено в прошлых презентациях — и зачем это нам сегодня.
ПРОЙДЕНО В ПРЕДЫДУЩЕЙ
ПРЕЗЕНТАЦИИ
ВОПРОСЫ БАЗЕ ДАННЫХ
1
«Проектирование»
аномалии однотабличной базы устранены разбиением на шесть таблиц CollegeDB
в 3НФ
2
«Связи и целостность»
FOREIGN KEY, типы связей 1:М, шесть ограничений, каскадные действия
«Соединять таблицы мы научились. А
сколько студентов в каждой группе?
Какая средняя стипендия? Кто получает
максимум?»
3
«Многотабличный SELECT»
соединение через WHERE G.Id = S.GroupId и современный INNER JOIN … ON
На каждый вопрос база должна ответить
одним числом — сегодня учимся задавать
ей именно такие вопросы.
4
«Внешние соединения и цепочки»
LEFT JOIN для студентов «без пары», запрос Students → Grades → Subjects
5
«Первые отчёты»
пробовали GROUP BY и AVG по связанным таблицам:
средний балл по каждому предмету
COUNT
AVG
GROUP BY
HAVING
подзапросы

3.

Что такое агрегирование
Базы данных умеют не только выдавать строки — они умеют подводить итоги.
Функция агрегирования принимает множество строк и возвращает единственное обобщающее значение — именно поэтому её ответ
занимает одну ячейку результата.
COUNT()
AVG()
SUM()
MIN()
MAX()
COUNT(*)
AVG(Grants)
SUM(Grants)
MIN(BirthDate)
MAX(LastName)
количество записей
среднее арифметическое
сумма значений
наименьшее значение
наибольшее значение
ГДЕ РАБОТАЮТ
AVG() и SUM() — только числовые столбцы
COUNT(), MIN(), MAX() — числа и строки
Grants в Students, Grade в Grades; к фамилии среднее не посчитаешь
даты рождения, фамилии: сравнение по кодировке символов

4.

C O U N T ( ) : с ч и т а т ь м о ж н о п о - р а з н о му
Один вопрос — три уточнения: сколько строк, сколько значений, сколько разных.
COUNT
-- все строки, включая NULL и повторы
SELECT COUNT(*) AS [Number of records]
FROM Students;
9 COUNT(*)
каждая строка таблицы, NULL тоже
-- только заполненные
SELECT COUNT(Grants) AS [Number of grants]
FROM Students;
6 COUNT(Grants)
NULL-значения не считаются (Смирнова,
-- без повторов
SELECT COUNT(DISTINCT Grants) AS [Unique grants]
FROM Students;
Grants)
3 COUNT(DISTINCT
2450, 1780, 1200 — повторы схлопнуты
Панов, Жуков)
COUNT(*) и COUNT(столбец) — разные вопросы: «сколько строк» против «сколько заполненных значений». Ключевое
слово DISTINCT убирает дубликаты до подсчёта.

5.

AVG() и SUM(): среднее и сумма
Обе — только для чисел. Обе молча пропускают NULL.
salary
SELECT AVG(Grants) AS [Average grant]
FROM Students;
Средний возраст студентов
SELECT AVG(DATEDIFF(dd, BirthDate,
GETDATE()) / 365.25) AS [Average age]
FROM Students;
-- 2018.33 (шесть стипендий, NULL не учтены)
1 DATEDIFF(dd, …) возвращает разницу в
днях между сегодня и датой рождения
SELECT SUM(Grants) AS [Sum grants]
FROM Students;
2 Деление на 365.25 — учитывает
високосные годы, точнее чем 365
-- 12110.00 (выплаты студентам за месяц)
3 результат ≈ 28.9 года — значение зависит
от текущей даты GETDATE()
AVG и SUM игнорируют NULL автоматически — но если NULL-строк много, «средняя стипендия» может оказаться
средней только среди получающих. Осознавайте, что именно усредняете.

6.

M I N ( ) и M A X () : к р а й н и е з н а ч е н и я
Работают и с числами, и со строками — и даже с датами.
extremes
SELECT MIN(BirthDate) AS [Min date of birth]
FROM Students;
-- 2002-01-27: самый старший студент (Жуков)
Почему «Смирнова»?
Строки сравниваются не «по алфавиту на глаз», а по
числовым кодам символов Unicode — столбец LastName
имеет тип nvarchar.
Б<В<Ж<К<Л<Н<П<Р<С
последняя фамилия по коду и есть максимум
SELECT MAX(LastName) AS [Maximum last name]
FROM Students;
-- Смирнова
✓ MIN/MAX над строками тоже законны — сервер
сравнивает коды.
Тот же приём работает с датами: MIN(BirthDate) — дата самого старшего, MAX(BirthDate) — самого младшего студента фрагмента: 200309-21 (Белова).

7.

Агрегаты в настоящих запросах
WHERE фильтрует строки до подсчёта — и многотабличность работает как прежде.
Агрегат + условие
SELECT COUNT(*) AS [Number of students]
FROM Students
WHERE FirstName LIKE 'М%';
2
— Михаил и Мария
Агрегат + соединение
SELECT COUNT(*) AS [Count of students]
FROM Students AS S, Groups AS G
WHERE G.GroupID = S.GroupId
AND G.GroupName = 'ИС-302';
3 — Новиков, Рябова, Панов
КОНВЕЙЕР ЗАПРОСА
1 FROM
берём строки (одну или
две таблицы)
→
2 WHERE
→
отбрасываем ненужные
строки и связываем таблицы
по PK/FK
3 Агрегат
считаем итог по
выжившим строкам
→
4 Результат
одно значение
Агрегат — всегда последний шаг конвейера: сначала отбор и соединение, потом подсчёт. Поэтому COUNT никогда не «видит» строки,
отброшенные WHERE.

8.

Ошибка: агрегат и столбец
Просим названия групп и количество студентов одним запросом — сервер отказывается.
g r o u p s _ co u n t . s q l
Невозможный результат
GroupName
SELECT GroupName, COUNT(S.GroupId)
AS [Number of students]
ИС-301
≠
NUMBER OF
STUDENTS
ИС-302
9
ИС-303
FROM Groups AS G, Students AS S
ИС-304
WHERE G.GroupID = S.GroupId;
четыре строки названий — и одно число, которое не знает, к какой
строке присоединиться
1 COUNT() всегда возвращает одно значение;
2 GroupName возвращает множество строк — четыре
разных названия;
Msg 8120: Столбец „G.GroupName“ недопустим в списке выбора, поскольку он не содержится ни
в агрегатной функции, ни в предложении GROUP BY.
3 оба в одном SELECT — нельзя: сервер не знает, к
какой группе отнести единственное число.
Подсказка уже в тексте ошибки: столбец должен быть «в агрегатной функции или в предложении GROUP BY». Читаем ошибки — сервер
сам говорит решение.

9.

GROUP BY: решение
Сгруппируем строки по названию группы — и количество станет «по одной штуке на группу».
groupby
Разложить по группам
SELECT GroupName, COUNT(S.GroupId)
AS [Number of students]
FROM Groups AS G, Students AS S
WHERE G.GroupID = S.GroupId
GROUP BY GroupName;
1 строки с одинаковым значением GroupName собираются
в группы — по одной на каждое уникальное название
в каждой
2 Посчитать
COUNT() выполняется не один раз для таблицы, а
отдельно для каждой группы
РЕЗУЛЬТАТ ЗАПРОСА
GroupName
Number of students
ИС-301
3
ИС-302
3
ИС-303
2
ИС-304
1
по строке
3 Отдать
в результат попадает по одной строке на группу: название
+ число
Золотое правило SELECT: каждый столбец должен быть либо в GROUP BY, либо внутри агрегатной функции. Иначе — Msg 8120.

10.

Г р у п п и р о вк а п о н е с к о л ь к и м с т о л б ц а м
Группы образуются по уникальному сочетанию значений всех столбцов группировки.
groupby2.sql
SELECT GroupName, Grants,
COUNT(S.GroupId)
AS [Number of students]
FROM Groups AS G, Students AS S
WHERE G.GroupID = S.GroupId
GROUP BY GroupName, Grants;
РЕЗУЛЬТАТ ЗАПРОСА — 8 СТРОК
GroupName
Grants
Number of students
ИС-301
2450.00
2
ИС-301
NULL
1
ИС-302
2450.00
1
ИС-302
1780.00
1
ИС-302
NULL
1
ИС-303
1200.00
1
→
ИС-303
NULL
1
ИС-304
1780.00
1
Восемь строк вместо четырёх — группы помельче
NULL-значения равны. GROUP BY интерпретирует все NULL как одно и то же значение — студенты без стипендии образуют
отдельную подгруппу внутри своей группы, хотя NULL ≠ NULL в обычных сравнениях.
Комбинация столбцов. Число студентов больше единицы появляется только там, где внутри группы совпал и размер стипендии: ИС-301,
2450.00 — два студента.

11.

HAVING: где стоит и зачем
Синтаксическая позиция — после GROUP BY, до ORDER BY. Назначение — условия на агрегаты.
t e mp l a te . s q l
Первый пример
ПРИМЕР
1
SELECT columnName1, columnName2, …
2
FROM tableName
3
[WHERE condition]
4
[GROUP BY columnName1, …]
Белова | 1200.00 — единственная пара, где средняя не выше
1200
1 GROUP BY собрал пары «фамилия + стипендия»;
5
6
SELECT LastName, Grants
FROM Students
GROUP BY LastName, Grants
HAVING AVG(Grants) <= 1200
ORDER BY LastName;
HAVING condition
2 HAVING отбросил группы, где AVG выше порога;
[ORDER BY columnName1 ASC | DESC, …];
3 ORDER BY отсортировал выжившие.
Наличие WHERE, GROUP BY и ORDER BY необязательно — HAVING может работать и без них. Но без GROUP BY весь набор
обрабатывается как одна большая группа.

12.

HAVING: три приёма
Фильтр по агрегату, список значений и HAVING без GROUP BY.
1 По агрегату
2 По списку значений
T -S Q L
3 Без GROUP BY
T -SQ L
SELECT GroupName, COUNT(*)
SELECT FirstName, LastName
FROM Groups AS G, Students AS S
FROM Students
WHERE G.GroupID = S.GroupId
GROUP BY LastName, FirstName
GROUP BY GroupName
HAVING LastName IN
HAVING COUNT(S.GroupId) > 2;
('Козлов', 'Жуков', 'Дроздов');
ИС-301, ИС-302 — группы, где учатся
больше двух человек
Козлов Михаил, Жуков Павел —
только фамилии из списка
T -SQ L
SELECT MIN(LastName)
FROM Students
HAVING AVG(Grants) > 1100;
Белова — средняя стипендия 2018.33
прошла порог
Без GROUP BY в SELECT можно писать только агрегаты — любой голый столбец потребует GROUP BY (Msg 8120).
Если поднять порог до 2500 — ни одна группа не пройдёт, и результат будет пустым набором: это не ошибка, это честный ответ сервера.

13.

WHERE против HAVING
Похожи синтаксисом, различаются моментом работы и правами на агрегаты.
WHERE
HAVING
С ТР О К И · Д О Г Р У П П И Р О В К И
— Работает с отдельными строками ДО группировки;
— Соединяет таблицы и отбрасывает ненужные
VS
→
работает с группами ПОСЛЕ GROUP BY;
→
условие может содержать агрегаты;
→
без GROUP BY обрабатывает весь набор как одну
группу.
строки;
—
Агрегатные функции запрещены.
WHERE AVG(Grants) <= 1200→ Msg 130: агрегатная
функция недопустима в WHERE — среднее нельзя посчитать
для строки
ПОРЯДОК ИСПОЛНЕНИЯ
FROM
Г Р У П П Ы · П О С Л Е GR O U P B Y
HAVING AVG(Grants) <= 1200→ корректно: среднее
считается для группы
→ WHERE → GROUP BY → HAVING → SELECT
→ ORDER BY
один запрос может содержать оба: WHERE отбирает строки, HAVING — группы

14.

П о ч е му н у же н п о д з а п р о с
Задача: вывести студентов, получающих максимальную стипендию. Две наивные попытки — и обе мимо.
Задача. Вывести фамилию, имя, группу и стипендию студентов, которые получают максимум.
Попытка 1 — агрегат в HAVING
Попытка 2 — магическое число
НЕВЕРНО
SELECT LastName, FirstName, GroupName, Grants
FROM Students AS S, Groups AS G
WHERE G.GroupID = S.GroupId
GROUP BY LastName, FirstName,
GroupName, Grants
HAVING Grants = MAX(Grants);
каждая группа — это одна строка, поэтому MAX(Grants) внутри
группы сравнивает значение само с собой — условие почти
всегда истина
группы с NULL-стипендией отбрасываются (сравнение с NULL
только через IS NULL)
итог — вернулись все шесть студентов со стипендией, а не трое
НЕВЕРНО
WHERE Grants = 2450
результат верный — но значение зашито в код
при любом изменении стипендий запрос молча начнёт врать,
его придётся править руками
Вывод. Максимум нужно вычислять в момент выполнения запроса — и подставлять в условие. Для этого и существует подзапрос.

15.

С к а л я р н ый п о д з а п р о с
Подзапрос в круглых скобках выполняется первым и возвращает одно значение.
ma x_ g r a nt. sql
SELECT LastName, FirstName, GroupName, Grants
скобки
1 Круглые
по синтаксису SQL подзапрос всегда в них
FROM Students AS S, Groups AS G
Выполняется первым
2 сервер считает MAX(Grants) = 2450, потом подставляет
WHERE G.GroupID = S.GroupId
это число во внешний WHERE
AND Grants = (SELECT MAX(Grants)
значение
3 Одно
это скалярный подзапрос: результат можно сравнивать
FROM Students);
через =
РЕЗУЛЬТАТ ЗАПРОСА
LastName
FirstName
GroupName
Grants
Волкова
Елена
ИС-301
2450.00
Козлов
Михаил
ИС-301
2450.00
Новиков
Дмитрий
ИС-302
2450.00
в WHERE запрещены — подзапрос
4 Агрегаты
нет
именно так обходится ограничение Msg 130
Максимум больше нигде не зашит: изменим стипендии — запрос сам пересчитает ответ. В отличие от магического числа, он не устаревает.

16.

П о д з а п р о с в е р ну л н е о д н о з н а ч е н и е
Задача: вывести всех студентов групп 30-й серии. Равно не работает — работает IN.
ОШИБКА
SELECT LastName, FirstName, GroupId
FROM Students
WHERE GroupId = (SELECT GroupID
FROM Groups
WHERE GroupName LIKE 'ИС-30%');
четыре группы ИС-30х — четыре Id, а «=» умеет
сравнивать только с одним
2 Что вернул подзапрос
Msg 512: Подзапрос возвратил более одного значения — запрещено сравнивать подзапрос,
возвращающий несколько строк, с единственным значением.
ИСПРАВЛЕНО
WHERE GroupId IN (SELECT GroupID
FROM Groups
WHERE GroupName LIKE 'ИС-30%');
РЕЗУЛЬТАТ
1 Почему ошибка
в с е 9 с туд ен тов — п о од н ому и з к а жд ой гр уп п ы -совпад ения
GroupName
Id
ИС-301
1
ИС-302
2
ИС-303
3
ИС-304
4
LIKE 'ИС-30%' — четыре строки, четыре Id
IN — оператор вхождения
3 GroupId сверяется со списком (1, 2, 3, 4):
совпадение — строка остаётся в выборке
Правило выбора оператора. Подзапрос возвращает одно значение — сравнивайте через =. Возвращает множество строк — используйте
IN: он проверяет вхождение в список. Та же логика, что и у агрегатов: одно значение против множества.

17.

П о д з а п р о с в FROM и в HAVING
Не только WHERE: подзапросы работают в SELECT, FROM и HAVING.
В FROM — виртуальная таблица
→
В HAVING — сравнение с общим средним
T-SQL
SELECT G.GroupName, A.AvgGrant
FROM Groups AS G,
(SELECT GroupId, AVG(Grants) AS AvgGrant
FROM Students
GROUP BY GroupId
HAVING AVG(Grants) > 2000) AS A
WHERE G.GroupID = A.GroupId;
РЕЗУЛЬТАТ
ИС -3 01 | 2 4 50.00 · ИС -3 02 | 2 1 15.00
→ подзапрос формирует виртуальную таблицу средних стипендий
по группам
→ к ней обязателен псевдоним через AS — без него сервер не
примет
→ дальше соединяем её с Groups как обычную таблицу
→
T-SQL
SELECT GroupName, AVG(S.Grants) AS AvgGrant
FROM Groups AS G, Students AS S
WHERE G.GroupID = S.GroupId
GROUP BY GroupName
HAVING AVG(S.Grants) >
(SELECT AVG(Grants) FROM Students);
РЕЗУЛЬТАТ
ИС -3 01 (2 4 50) и ИС -3 02 (2 1 15) — в ы ш е об щ его с р ед него
2 0 18.33
→ подзапрос вернул среднюю стипендию по всем студентам
→ HAVING сравнил средние по группам с этим значением
→ ИС-303 (1200) и ИС-304 (1780) не прошли порог
Подзапросы возможны во всех четырёх секциях: SELECT, FROM, WHERE, HAVING. В FROM — обязательно с псевдонимом, в
HAVING — обычно скалярные.

18.

Принцип работы и коррелированные подзапросы
Обычные выполняются изнутри наружу один раз; связанные — для каждой строки внешнего запроса.
Вложенность: изнутри наружу
→
Связанный (коррелированный)
→
T-SQL
SELECT GroupName
FROM Groups
WHERE GroupID IN
(SELECT GroupId
FROM Students WHERE Grants =
(SELECT MAX(Grants)
FROM Students));
3
2
1
ИС -3 01, ИС -3 02 — гр уп п ы с туд ен тов с ма к с имальной
с ти пенд ией
→ подзапросы могут вкладываться друг в друга
→ выполнение начинается с самого глубокого
SELECT Sub.SubjectName,
(SELECT MAX(G.Grade)
FROM Grades AS G
WHERE Sub.SubjectID = G.SubjectID) AS Maximum
FROM Subjects AS Sub;
РЕЗУЛЬТАТ
ПОРЯДОК ВЫПОЛНЕНИЯ: 1 → 2 → 3 — СНАЧАЛА САМЫЙ ГЛУБОКИЙ
РЕЗУЛЬТАТ
T-SQL
Name
Maximum
SQL Server
5
Матанализ
3
→ подзапрос ссылается на столбец внешнего запроса (Sub.Id) —
зависит от текущей строки
→ поэтому выполняется заново для каждой строки Subjects и не
может быть вынесен наружу
→ в SELECT допустим, только если возвращает одно значение —
агрегаты подходят идеально
Сколько строк в Subjects — столько раз отработает связанный подзапрос: виртуальная таблица результата будет ровно по строке на
предмет.

19.

П р а к т и ч ес ко й з а д а н и н е
▤ Практической задание
Пять функций
1 COUNT · AVG · SUM · MIN · MAX: одна функция — одно значение;
NULL молча игнорируются, кроме COUNT(*)
BY
2 GROUP
столбец в SELECT живёт либо в GROUP BY, либо в агрегате; NULLзначения при группировке равны
3
4
HAVING
фильтр групп по агрегатам; WHERE — до группировки, HAVING —
после
Подзапросы
скалярные (через =), множественные (через IN), в FROM с
псевдонимом, в HAVING; коррелированные выполняются
построчно
Написать запросы:
количество преподавателей по каждой
✓ предмету
✓ число занимающихся в каждой группе в
заданное время
✓ студенты по домену почты
✓ студенты с фамилиями из заданного списка
✓ одинаковые имена у одного преподавателя
✓ студенты с минимальной стипендией
(подзапрос!)
✓ студенты и группы за первое полугодие года
✓ всего студентов за прошлый год