Multi Table Databases

Агрегації з JOIN

Використання COUNT, SUM, AVG, MIN, MAX з JOIN, GROUP BY з множинними таблицями, складні аналітичні запити

Агрегації з JOIN

Проблема: Аналітика через множинні таблиці

Задача: Порахувати кількість студентів у кожній групі і показати назву факультету.

Потрібно з'єднати 3 таблиці + агрегація:

Faculties → Groups → Students
          ↓
    COUNT(Students)

Рішення: Комбінація JOIN + GROUP BY!

1. Агрегативні функції: Нагадування

Функція	Опис	NULL handling
COUNT(column)	Кількість не-NULL значень	❌ Пропускає NULL
*COUNT()**	Кількість всіх рядків	✅ Враховує NULL
SUM(column)	Сума значень	❌ Пропускає NULL
AVG(column)	Середнє	❌ Пропускає NULL
MIN(column)	Мінімум	❌ Пропускає NULL
MAX(column)	Максимум	❌ Пропускає NULL

Важливо: COUNT(column) та COUNT(*)різні!

2. COUNT з JOIN

2.1. Базовий приклад

-- Кількість студентів у кожній групі
SELECT
    G.Name AS GroupName,
    COUNT(S.Id) AS StudentsCount  -- COUNT(S.Id) - враховує NULL!
FROM Groups AS G
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name
ORDER BY StudentsCount DESC;

Результат:

GroupName	StudentsCount
31PR11	5
32PR31	3
33PR51	0

LEFT JOIN важливий - інакше групи без студентів не відображаться!

2.2. COUNT(*) vs COUNT(column)

SELECT
    G.Name,
    COUNT(*) AS TotalRows  -- Всі рядки після JOIN
FROM Groups AS G
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name;

-- Група без студентів: COUNT(*) = 1 (сам рядок групи)

SELECT
    G.Name,
    COUNT(S.Id) AS StudentsCount  -- Тільки де S.Id NOT NULL
FROM Groups AS G
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name;

-- Група без студентів: COUNT(S.Id) = 0 ✅

З LEFT JOIN завжди використовуйтеCOUNT(RightTable.Column), НЕCOUNT(*)!

3. GROUP BY з множинними таблицями

3.1. Два рівні (Groups + Faculties)

-- Кількість студентів по групах + факультет
SELECT
    F.Name AS Faculty,
    G.Name AS GroupName,
    COUNT(S.Id) AS StudentsCount
FROM Faculties AS F
INNER JOIN Groups AS G
    ON F.Id = G.FacultyId
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY F.Name, G.Name  -- Групування по обох!
ORDER BY F.Name, StudentsCount DESC;

Результат:

Faculty	GroupName	StudentsCount
Computer Science	31PR11	5
Computer Science	32PR31	3
Computer Science	33PR51	0
Mathematics	41MA11	4

3.2. Агрегація по факультету (один рівень)

-- Загальна кількість студентів НА ФАКУЛЬТЕТІ
SELECT
    F.Name AS Faculty,
    COUNT(S.Id) AS TotalStudents
FROM Faculties AS F
LEFT JOIN Groups AS G
    ON F.Id = G.FacultyId
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY F.Name  -- Тільки по факультету
ORDER BY TotalStudents DESC;

Результат:

Faculty	TotalStudents
Computer Science	8
Mathematics	4
Physics	0

4. SUM, AVG з JOIN

4.1. Середній бал по групах

-- Середній бал студентів в кожній групі
SELECT
    G.Name AS GroupName,
    AVG(S.Grade) AS AvgGrade,
    COUNT(S.Id) AS StudentsCount
FROM Groups AS G
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name
ORDER BY AvgGrade DESC;

Результат:

GroupName	AvgGrade	StudentsCount
32PR31	85.3	3
31PR11	78.2	5
33PR51	NULL	0

AVG(NULL) повертає NULL - група без студентів.

4.2. Загальна сума балів по кафедрах

-- Сума всіх балів студентів викладачів кожної кафедри
SELECT
    D.Name AS Department,
    SUM(A.Grade) AS TotalGrade,
    COUNT(A.Id) AS AssessmentsCount
FROM Departments AS D
LEFT JOIN Teachers AS T
    ON D.Id = T.DepartmentId
LEFT JOIN Assessments AS A
    ON T.Id = A.TeacherId
GROUP BY D.Name
ORDER BY TotalGrade DESC;

5. MIN, MAX з JOIN

5.1. Найкращий та найгірший бал

-- Найвищий та найнижчий бал в кожній групі
SELECT
    G.Name AS GroupName,
    MAX(S.Grade) AS MaxGrade,
    MIN(S.Grade) AS MinGrade,
    AVG(S.Grade) AS AvgGrade
FROM Groups AS G
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name
HAVING MAX(S.Grade) IS NOT NULL  -- Виключити групи без студентів
ORDER BY AvgGrade DESC;

Результат:

GroupName	MaxGrade	MinGrade	AvgGrade
32PR31	95	75	85.3
31PR11	90	65	78.2

6. HAVING з JOIN

6.1. Фільтрація після агрегації

-- Групи, де середній бал > 80
SELECT
    G.Name AS GroupName,
    AVG(S.Grade) AS AvgGrade,
    COUNT(S.Id) AS StudentsCount
FROM Groups AS G
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name
HAVING AVG(S.Grade) > 80  -- HAVING - після GROUP BY!
ORDER BY AvgGrade DESC;

Результат:

GroupName	AvgGrade	StudentsCount
32PR31	85.3	3

WHERE vs HAVING:

WHERE - фільтр ДО GROUP BY (окремі рядки)
HAVING - фільтр ПІСЛЯ GROUP BY (групи)

6.2. Комбінація WHERE + HAVING

-- Факультети "Computer Science" з >5 студентів
SELECT
    F.Name AS Faculty,
    COUNT(S.Id) AS StudentsCount
FROM Faculties AS F
INNER JOIN Groups AS G
    ON F.Id = G.FacultyId
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
WHERE F.Name = 'Computer Science'  -- WHERE: до GROUP BY
GROUP BY F.Name
HAVING COUNT(S.Id) > 5;  -- HAVING: після GROUP BY

7. Проблеми duplicate rows при JOIN

7.1. Проблема подвійного COUNT

-- ❌ ПРОБЛЕМА: Подвоєння рядків!
SELECT
    G.Name,
    COUNT(S.Id) AS StudentsCount,
    COUNT(T.Id) AS TeachersCount  -- Помилка!
FROM Groups AS G
LEFT JOIN Students AS S ON G.Id = S.GroupId
LEFT JOIN TeachersGroups AS TG ON G.Id = TG.GroupId
LEFT JOIN Teachers AS T ON TG.TeacherId = T.Id
GROUP BY G.Name;

Проблема: Якщо у групі 3 студенти та 2 викладачі, після JOIN буде 6 рядків (3×2)!

Група → 3 студенти
Група → 2 викладачі
      ↓ CROSS JOIN effect
   6 рядків!

7.2. Рішення 1: DISTINCT COUNT

-- ✅ Використовуємо COUNT(DISTINCT)
SELECT
    G.Name,
    COUNT(DISTINCT S.Id) AS StudentsCount,
    COUNT(DISTINCT T.Id) AS TeachersCount
FROM Groups AS G
LEFT JOIN Students AS S ON G.Id = S.GroupId
LEFT JOIN TeachersGroups AS TG ON G.Id = TG.GroupId
LEFT JOIN Teachers AS T ON TG.TeacherId = T.Id
GROUP BY G.Name;

7.3. Рішення 2: Окремі підзапити

-- ✅ Альтернатива: Окремі підзапити
SELECT
    G.Name,
    (SELECT COUNT(*) FROM Students WHERE GroupId = G.Id) AS StudentsCount,
    (SELECT COUNT(*) FROM TeachersGroups WHERE GroupId = G.Id) AS TeachersCount
FROM Groups AS G;

8. Складні аналітичні запити

8.1. Топ-3 групи по середньому балу

SELECT TOP 3
    G.Name AS GroupName,
    F.Name AS Faculty,
    AVG(S.Grade) AS AvgGrade,
    COUNT(S.Id) AS StudentsCount
FROM Groups AS G
INNER JOIN Faculties AS F
    ON G.FacultyId = F.Id
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name, F.Name
HAVING COUNT(S.Id) > 0  -- Виключити порожні групи
ORDER BY AvgGrade DESC;

8.2. Кафедри з найбільшою кількістю предметів

SELECT
    D.Name AS Department,
    COUNT(DISTINCT TS.SubjectId) AS SubjectsCount,
    COUNT(DISTINCT T.Id) AS TeachersCount
FROM Departments AS D
LEFT JOIN Teachers AS T
    ON D.Id = T.DepartmentId
LEFT JOIN TeachersSubjects AS TS
    ON T.Id = TS.TeacherId
GROUP BY D.Name
ORDER BY SubjectsCount DESC;

8.3. Порівняльний аналіз (percentiles)

-- Групи з avg більше за загальний середній
SELECT
    G.Name,
    AVG(S.Grade) AS GroupAvg,
    (SELECT AVG(Grade) FROM Students) AS OverallAvg,
    AVG(S.Grade) - (SELECT AVG(Grade) FROM Students) AS Difference
FROM Groups AS G
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name
HAVING AVG(S.Grade) > (SELECT AVG(Grade) FROM Students)
ORDER BY Difference DESC;

9. Performance Optimization

9.1. Індекси для JOIN + GROUP BY

-- Індекси на FK (для JOIN)
CREATE INDEX IX_Students_GroupId ON Students(GroupId);
CREATE INDEX IX_Groups_FacultyId ON Groups(FacultyId);

-- Індекс на GROUP BY column
CREATE INDEX IX_Students_Grade ON Students(Grade);

9.2. Covering Index

-- Включити всі потрібні стовпці в індекс
CREATE INDEX IX_Students_Covering
ON Students(GroupId, Grade)
INCLUDE (FirstName, LastName);

9.3. Уникайте SELECT *

-- ❌ Повільно
SELECT *, COUNT(S.Id)
FROM Groups G
LEFT JOIN Students S ON G.Id = S.GroupId
GROUP BY G.Id, G.Name, ...;  -- Всі стовпці!

-- ✅ Швидше
SELECT G.Name, COUNT(S.Id)
FROM Groups G
LEFT JOIN Students S ON G.Id = S.GroupId
GROUP BY G.Name;

10. Практичні приклади

10.1. Звіт по факультетах

-- Детальна статистика по факультетах
SELECT
    F.Name AS Faculty,
    COUNT(DISTINCT G.Id) AS GroupsCount,
    COUNT(DISTINCT S.Id) AS StudentsCount,
    AVG(S.Grade) AS AvgGrade,
    MAX(S.Grade) AS MaxGrade,
    MIN(S.Grade) AS MinGrade
FROM Faculties AS F
LEFT JOIN Groups AS G ON F.Id = G.FacultyId
LEFT JOIN Students AS S ON G.Id = S.GroupId
GROUP BY F.Name
ORDER BY StudentsCount DESC;

10.2. Активність викладачів

-- Викладачі з кількістю предметів та груп
SELECT
    T.FirstName + ' ' + T.LastName AS Teacher,
    D.Name AS Department,
    COUNT(DISTINCT TS.SubjectId) AS SubjectsCount,
    COUNT(DISTINCT TS.GroupId) AS GroupsCount
FROM Teachers AS T
INNER JOIN Departments AS D
    ON T.DepartmentId = D.Id
LEFT JOIN TeachersSubjects AS TS
    ON T.Id = TS.TeacherId
GROUP BY T.FirstName, T.LastName, D.Name
ORDER BY SubjectsCount DESC, GroupsCount DESC;

10.3. Оцінки по предметах

-- Статистика оцінок по кожному предмету
SELECT
    S.Name AS Subject,
    COUNT(A.Id) AS AssessmentsCount,
    AVG(A.Grade) AS AvgGrade,
    MAX(A.Grade) AS MaxGrade,
    MIN(A.Grade) AS MinGrade,
    COUNT(DISTINCT A.StudentId) AS UniqueStudents
FROM Subjects AS S
LEFT JOIN Assessments AS A
    ON S.Id = A.SubjectId
GROUP BY S.Name
HAVING COUNT(A.Id) > 0  -- Тільки предмети з оцінками
ORDER BY AvgGrade DESC;

11. Best Practices

1. LEFT JOIN для COUNT

-- ✅ Показати всі групи
FROM Groups G
LEFT JOIN Students S
    ON G.Id = S.GroupId

-- COUNT(S.Id) поверне 0 для порожніх

2. COUNT(column) vs COUNT(\*)

-- ✅ З LEFT JOIN
COUNT(RightTable.Column)

-- ❌ З LEFT JOIN
COUNT(*)  -- Поверне >= 1 завжди!

3. DISTINCT для multiple JOINs

-- При кількох JOIN
COUNT(DISTINCT S.Id)
COUNT(DISTINCT T.Id)

4. HAVING для груп

-- Фільтр після агрегації
GROUP BY ...
HAVING COUNT(*) > 5

12. Практичні завдання

Показати всі групи з кількістю студентів та середнім балом.

💡 Розв'язок

SELECT
    G.Name AS GroupName,
    COUNT(S.Id) AS StudentsCount,
    AVG(S.Grade) AS AvgGrade
FROM Groups AS G
LEFT JOIN Students AS S
    ON G.Id = S.GroupId
GROUP BY G.Name
ORDER BY AvgGrade DESC;

Знайти кафедри з >3 викладачами, відсортувати по кількості.

💡 Розв'язок

SELECT
    D.Name AS Department,
    COUNT(T.Id) AS TeachersCount
FROM Departments AS D
LEFT JOIN Teachers AS T
    ON D.Id = T.DepartmentId
GROUP BY D.Name
HAVING COUNT(T.Id) > 3
ORDER BY TeachersCount DESC;

Факультети, де середній бал студентів вище за загальний середній.

💡 Розв'язок

SELECT
    F.Name AS Faculty,
    AVG(S.Grade) AS FacultyAvg,
    (SELECT AVG(Grade) FROM Students) AS OverallAvg
FROM Faculties AS F
LEFT JOIN Groups AS G ON F.Id = G.FacultyId
LEFT JOIN Students AS S ON G.Id = S.GroupId
GROUP BY F.Name
HAVING AVG(S.Grade) > (SELECT AVG(Grade) FROM Students);

Для кожного предмету: кількість викладачів, груп, студентів (DISTINCT!).

💡 Розв'язок

SELECT
    Sub.Name AS Subject,
    COUNT(DISTINCT TS.TeacherId) AS TeachersCount,
    COUNT(DISTINCT TS.GroupId) AS GroupsCount,
    COUNT(DISTINCT S.Id) AS StudentsCount
FROM Subjects AS Sub
LEFT JOIN TeachersSubjects AS TS
    ON Sub.Id = TS.SubjectId
LEFT JOIN Students AS S
    ON TS.GroupId = S.GroupId
GROUP BY Sub.Name
ORDER BY StudentsCount DESC;

Резюме

Ключові моменти агрегацій з JOIN:

COUNT з LEFT JOIN:
- Використовуйте COUNT(RightTable.Column), НЕ COUNT(*)
- Поверне 0 для рядків без збігів
GROUP BY з JOIN:
- Групуйте по всім не-агрегованим стовпцям
- Можна групувати по кільком таблицям
HAVING:
- Фільтрація після агрегації
- WHERE - до, HAVING - після GROUP BY
Duplicate rows проблема:
- При множинних JOIN використовуйте COUNT(DISTINCT)
- Або окремі підзапити
Performance:
- Індекси на FK та GROUP BY columns
- SELECT тільки потрібні стовпці
- Уникайте SELECT *

Типові запити:

Статистика по групах/факультетах
Топ-N груп/студентів/викладачів
Порівняння з середніми значеннями
Знайти аномалії (групи без студентів, предмети без оцінок)

Попередня тема: Підзапити (Subqueries)
Попередній розділ: Базові SELECT запити

Edit this pageorReport an issue

Підзапити (Subqueries)

Детальний розбір підзапитів (subqueries) - scalar, table, correlated, EXISTS, IN, позиції в SELECT/FROM/WHERE

Функції агрегування в MS SQL Server

Детальне дослідження агрегатних функцій SQL Server - COUNT, SUM, AVG, MIN, MAX з практичними прикладами та best practices