Сложные SELECT запросы: JOIN, подзапросы, агрегатные функции

Введение

SELECT — самая важная и часто используемая команда в SQL. Освоение сложных SELECT запросов открывает возможности для глубокого анализа данных, построения отчетов и решения бизнес-задач. В этом уроке мы создадим тестовую базу данных и разберёмся уже на примере как работать с помощью JOIN, подзапросов и агрегатных функций.

Подготовка тестовой базы данных

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

-- Создание базы данных
CREATE DATABASE university;

-- Студенты
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    date_of_birth DATE NOT NULL,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    gpa NUMERIC(3, 2) CHECK (gpa >= 0 AND gpa <= 4.0),
    major VARCHAR(100),
    year INTEGER CHECK (year BETWEEN 1 AND 6),
    city VARCHAR(100),
    country VARCHAR(100) DEFAULT 'Russia'
);

-- Преподаватели
CREATE TABLE professors (
    professor_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department VARCHAR(100),
    hire_date DATE NOT NULL,
    salary NUMERIC(10, 2) CHECK (salary > 0),
    office_number VARCHAR(20)
);

-- Курсы
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_code VARCHAR(20) UNIQUE NOT NULL,
    course_name VARCHAR(200) NOT NULL,
    credits INTEGER CHECK (credits > 0),
    department VARCHAR(100),
    professor_id INTEGER REFERENCES professors(professor_id),
    max_students INTEGER,
    semester VARCHAR(20)
);

-- Регистрация на курсы (студент-курс, многие-ко-многим)
CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER NOT NULL REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INTEGER NOT NULL REFERENCES courses(course_id) ON DELETE CASCADE,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    grade VARCHAR(2) CHECK (grade IN ('A', 'B', 'C', 'D', 'F', 'W')),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'completed', 'dropped')),
    UNIQUE(student_id, course_id)
);

-- Экзамены
CREATE TABLE exams (
    exam_id SERIAL PRIMARY KEY,
    course_id INTEGER NOT NULL REFERENCES courses(course_id),
    exam_date TIMESTAMPTZ NOT NULL,
    exam_type VARCHAR(20) CHECK (exam_type IN ('midterm', 'final', 'quiz')),
    max_score INTEGER NOT NULL,
    duration_minutes INTEGER
);

-- Результаты экзаменов
CREATE TABLE exam_results (
    result_id SERIAL PRIMARY KEY,
    exam_id INTEGER NOT NULL REFERENCES exams(exam_id),
    student_id INTEGER NOT NULL REFERENCES students(student_id),
    score NUMERIC(5, 2) NOT NULL,
    submitted_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(exam_id, student_id)
);

-- Индексы для производительности
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);
CREATE INDEX idx_courses_professor ON courses(professor_id);
CREATE INDEX idx_exam_results_exam ON exam_results(exam_id);
CREATE INDEX idx_exam_results_student ON exam_results(student_id);

-- Вставка тестовых данных
INSERT INTO professors (first_name, last_name, email, department, hire_date, salary, office_number) VALUES
    ('Иван', 'Петров', 'i.petrov@uni.edu', 'Математика', '2010-09-01', 120000, 'A-301'),
    ('Мария', 'Сидорова', 'm.sidorova@uni.edu', 'Информатика', '2012-09-01', 130000, 'B-205'),
    ('Алексей', 'Смирнов', 'a.smirnov@uni.edu', 'Физика', '2008-09-01', 125000, 'C-101'),
    ('Елена', 'Козлова', 'e.kozlova@uni.edu', 'Информатика', '2015-09-01', 110000, 'B-210'),
    ('Дмитрий', 'Васильев', 'd.vasiliev@uni.edu', 'Математика', '2013-09-01', 115000, 'A-305');

INSERT INTO courses (course_code, course_name, credits, department, professor_id, max_students, semester) VALUES
    ('MATH101', 'Математический анализ I', 4, 'Математика', 1, 100, 'Fall 2024'),
    ('MATH102', 'Математический анализ II', 4, 'Математика', 1, 100, 'Spring 2024'),
    ('CS101', 'Введение в программирование', 3, 'Информатика', 2, 80, 'Fall 2024'),
    ('CS201', 'Структуры данных', 4, 'Информатика', 2, 60, 'Spring 2024'),
    ('CS301', 'Базы данных', 4, 'Информатика', 4, 50, 'Fall 2024'),
    ('PHYS101', 'Общая физика', 4, 'Физика', 3, 120, 'Fall 2024'),
    ('MATH201', 'Линейная алгебра', 3, 'Математика', 5, 90, 'Fall 2024'),
    ('CS102', 'Алгоритмы', 3, 'Информатика', 4, 70, 'Spring 2024');

INSERT INTO students (first_name, last_name, email, date_of_birth, gpa, major, year, city, country) VALUES
    ('Анна', 'Иванова', 'anna.ivanova@student.edu', '2003-05-15', 3.85, 'Информатика', 2, 'Москва', 'Russia'),
    ('Петр', 'Смирнов', 'petr.smirnov@student.edu', '2004-03-22', 3.45, 'Математика', 2, 'Санкт-Петербург', 'Russia'),
    ('Ольга', 'Петрова', 'olga.petrova@student.edu', '2002-11-08', 3.92, 'Информатика', 3, 'Москва', 'Russia'),
    ('Сергей', 'Козлов', 'sergey.kozlov@student.edu', '2003-07-30', 2.85, 'Физика', 2, 'Казань', 'Russia'),
    ('Екатерина', 'Новикова', 'ekaterina.novikova@student.edu', '2005-01-12', 3.75, 'Математика', 1, 'Москва', 'Russia'),
    ('Михаил', 'Волков', 'mikhail.volkov@student.edu', '2003-09-18', 3.20, 'Информатика', 2, 'Новосибирск', 'Russia'),
    ('Наталья', 'Морозова', 'natalya.morozova@student.edu', '2004-06-25', 3.65, 'Физика', 2, 'Екатеринбург', 'Russia'),
    ('Александр', 'Лебедев', 'alexander.lebedev@student.edu', '2002-12-03', 3.95, 'Математика', 3, 'Москва', 'Russia'),
    ('Виктория', 'Соколова', 'victoria.sokolova@student.edu', '2005-04-17', 3.55, 'Информатика', 1, 'Санкт-Петербург', 'Russia'),
    ('Игорь', 'Павлов', 'igor.pavlov@student.edu', '2003-10-09', 3.10, 'Физика', 2, 'Нижний Новгород', 'Russia');

INSERT INTO enrollments (student_id, course_id, grade, status) VALUES
    -- Анна (student_id=1)
    (1, 3, 'A', 'completed'), (1, 4, 'A', 'active'), (1, 5, NULL, 'active'),
    -- Петр (student_id=2)
    (2, 1, 'B', 'completed'), (2, 2, NULL, 'active'), (2, 7, 'B', 'active'),
    -- Ольга (student_id=3)
    (3, 4, 'A', 'completed'), (3, 5, 'A', 'active'), (3, 8, NULL, 'active'),
    -- Сергей (student_id=4)
    (4, 6, 'C', 'completed'), (4, 1, 'D', 'completed'),
    -- Екатерина (student_id=5)
    (5, 1, 'A', 'active'), (5, 3, NULL, 'active'), (5, 7, NULL, 'active'),
    -- Михаил (student_id=6)
    (6, 3, 'B', 'completed'), (6, 5, NULL, 'active'),
    -- Наталья (student_id=7)
    (7, 6, 'B', 'completed'), (7, 1, 'C', 'active'),
    -- Александр (student_id=8)
    (8, 2, 'A', 'active'), (8, 7, 'A', 'completed'), (8, 8, NULL, 'active'),
    -- Виктория (student_id=9)
    (9, 3, 'A', 'active'), (9, 1, NULL, 'active'),
    -- Игорь (student_id=10)
    (10, 6, 'C', 'completed'), (10, 4, 'D', 'dropped');

INSERT INTO exams (course_id, exam_date, exam_type, max_score, duration_minutes) VALUES
    (1, '2024-10-15 10:00:00', 'midterm', 100, 120),
    (1, '2024-12-20 10:00:00', 'final', 100, 180),
    (3, '2024-10-20 14:00:00', 'midterm', 100, 90),
    (3, '2024-12-18 14:00:00', 'final', 100, 120),
    (5, '2024-11-05 10:00:00', 'quiz', 50, 60),
    (5, '2024-12-15 10:00:00', 'final', 100, 150);

INSERT INTO exam_results (exam_id, student_id, score) VALUES
    (1, 2, 78.5), (1, 5, 92.0), (1, 7, 65.5), (1, 9, 88.0),
    (3, 1, 95.0), (3, 5, 87.5), (3, 6, 76.0), (3, 9, 91.0),
    (5, 1, 45.0), (5, 3, 48.5), (5, 6, 38.0);

JOIN операции в деталях

JOIN — это оператор, который позволяет объединять данные из двух или более таблиц в один результат на основе какого-то условия (обычно по общим ключам / полям).

Без JOIN вы можете выбрать данные только из одной таблицы. С JOIN — получаете информацию сразу из нескольких связанных таблиц.

Зачем нужны JOIN-ы?

• Показать имя клиента + название заказа + дату (таблицы clients и orders)
• Вывести сотрудников и их отделы (таблицы employees и departments)
• Получить все товары в заказе с их ценами и категориями
• Сравнить данные за разные периоды (self-join)
• Собрать отчёт по продажам с информацией о товарах, клиентах и менеджерах одновременно

INNER JOIN — Внутреннее объединение

INNER JOIN возвращает только те строки, для которых найдено совпадение в обеих таблицах.

-- Базовый INNER JOIN
SELECT 
    s.first_name,
    s.last_name,
    c.course_name,
    e.grade
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

-- С условием WHERE
SELECT 
    s.first_name || ' ' || s.last_name AS student_name,
    c.course_name,
    e.grade
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id
WHERE e.status = 'completed' 
  AND e.grade IN ('A', 'B')
ORDER BY s.last_name, c.course_name;

-- Множественные JOIN с агрегацией
SELECT 
    p.first_name || ' ' || p.last_name AS professor,
    p.department,
    COUNT(DISTINCT c.course_id) AS courses_teaching,
    COUNT(DISTINCT e.student_id) AS total_students
FROM professors p
INNER JOIN courses c ON p.professor_id = c.professor_id
INNER JOIN enrollments e ON c.course_id = e.course_id
WHERE e.status = 'active'
GROUP BY p.professor_id, p.first_name, p.last_name, p.department
ORDER BY total_students DESC;

Порядок JOIN

Порядок JOIN важен для читаемости, но PostgreSQL оптимизатор может изменить порядок выполнения для лучшей производительности. Пишите JOIN в логическом порядке для понимания.

LEFT JOIN — Левое внешнее объединение

LEFT JOIN возвращает все строки из левой таблицы и совпадающие из правой (или NULL).

-- Все студенты и их записи на курсы (включая тех, кто не записан)
SELECT 
    s.student_id,
    s.first_name || ' ' || s.last_name AS student_name,
    s.major,
    c.course_name,
    e.grade
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id
ORDER BY s.student_id;

-- Найти студентов БЕЗ записей на курсы
SELECT 
    s.student_id,
    s.first_name,
    s.last_name,
    s.email,
    s.major
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
WHERE e.enrollment_id IS NULL;

-- Профессора и количество студентов (включая профессоров без студентов)
SELECT 
    p.first_name || ' ' || p.last_name AS professor,
    p.department,
    COUNT(DISTINCT e.student_id) AS student_count,
    COALESCE(STRING_AGG(DISTINCT c.course_name, ', '), 'No courses') AS courses
FROM professors p
LEFT JOIN courses c ON p.professor_id = c.professor_id
LEFT JOIN enrollments e ON c.course_id = e.course_id AND e.status = 'active'
GROUP BY p.professor_id, p.first_name, p.last_name, p.department
ORDER BY student_count DESC;

RIGHT JOIN — Правое внешнее объединение

RIGHT JOIN возвращает все строки из правой таблицы и совпадающие из левой.

-- Все курсы и записанные студенты (включая курсы без студентов)
SELECT 
    c.course_name,
    c.course_code,
    COUNT(e.student_id) AS enrolled_students,
    c.max_students,
    c.max_students - COUNT(e.student_id) AS available_seats
FROM enrollments e
RIGHT JOIN courses c ON e.course_id = c.course_id
WHERE e.status = 'active' OR e.status IS NULL
GROUP BY c.course_id, c.course_name, c.course_code, c.max_students
ORDER BY enrolled_students DESC;

-- Эквивалентно LEFT JOIN с обратным порядком
SELECT 
    c.course_name,
    COUNT(e.student_id) AS enrolled_students
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id AND e.status = 'active'
GROUP BY c.course_id, c.course_name;

RIGHT JOIN vs LEFT JOIN

RIGHT JOIN используется реже, так как любой RIGHT JOIN можно переписать как LEFT JOIN, поменяв таблицы местами. LEFT JOIN более интуитивен и чаще используется.

FULL OUTER JOIN — Полное внешнее объединение

FULL OUTER JOIN возвращает все строки из обеих таблиц, совпадающие и несовпадающие.

-- Все студенты и все курсы (показать связи и отсутствие связей)
SELECT 
    s.first_name || ' ' || s.last_name AS student,
    c.course_name,
    e.grade,
    e.status
FROM students s
FULL OUTER JOIN enrollments e ON s.student_id = e.student_id
FULL OUTER JOIN courses c ON e.course_id = c.course_id
WHERE s.student_id IS NULL OR c.course_id IS NULL
ORDER BY s.last_name, c.course_name;

-- Найти несоответствия
SELECT 
    CASE 
        WHEN s.student_id IS NULL THEN 'Курс без студентов'
        WHEN c.course_id IS NULL THEN 'Студент без курсов'
        ELSE 'OK'
    END AS status,
    COALESCE(s.first_name || ' ' || s.last_name, 'N/A') AS student,
    COALESCE(c.course_name, 'N/A') AS course
FROM students s
FULL OUTER JOIN enrollments e ON s.student_id = e.student_id
FULL OUTER JOIN courses c ON e.course_id = c.course_id
WHERE s.student_id IS NULL OR c.course_id IS NULL;

CROSS JOIN — Декартово произведение

CROSS JOIN создает все возможные комбинации строк из двух таблиц.

-- Все комбинации студентов и курсов
SELECT 
    s.first_name || ' ' || s.last_name AS student,
    c.course_name
FROM students s
CROSS JOIN courses c
WHERE s.major = 'Информатика' 
  AND c.department = 'Информатика'
ORDER BY s.last_name, c.course_name
LIMIT 20;

-- Генерация всех возможных расписаний
SELECT 
    p.first_name || ' ' || p.last_name AS professor,
    c.course_name,
    s.semester
FROM professors p
CROSS JOIN courses c
CROSS JOIN (VALUES ('Fall 2024'), ('Spring 2025')) AS s(semester)
WHERE p.department = c.department
ORDER BY professor, semester;

-- Практическое применение: найти студентов, которые могут быть объединены в группы
SELECT 
    s1.first_name || ' ' || s1.last_name AS student1,
    s2.first_name || ' ' || s2.last_name AS student2,
    s1.major
FROM students s1
CROSS JOIN students s2
WHERE s1.student_id < s2.student_id  -- Избежать дубликатов (A-B и B-A)
  AND s1.major = s2.major
  AND s1.city = s2.city
ORDER BY s1.major, s1.city;

SELF JOIN — Самообъединение

SELF JOIN — это объединение таблицы с самой собой, полезно для иерархических данных.

-- Добавим поле mentor_id в таблицу студентов
ALTER TABLE students ADD COLUMN mentor_id INTEGER REFERENCES students(student_id);

UPDATE students SET mentor_id = 3 WHERE student_id IN (1, 5, 9);  -- Ольга - ментор
UPDATE students SET mentor_id = 8 WHERE student_id IN (2, 6);     -- Александр - ментор

-- Найти студентов и их менторов
SELECT 
    s.first_name || ' ' || s.last_name AS student,
    s.year AS student_year,
    m.first_name || ' ' || m.last_name AS mentor,
    m.year AS mentor_year
FROM students s
LEFT JOIN students m ON s.mentor_id = m.student_id
ORDER BY mentor, student;

-- Студенты одного курса одной специальности
SELECT 
    s1.first_name || ' ' || s1.last_name AS student1,
    s2.first_name || ' ' || s2.last_name AS student2,
    s1.major,
    s1.year
FROM students s1
INNER JOIN students s2 ON s1.major = s2.major 
                       AND s1.year = s2.year
                       AND s1.student_id < s2.student_id
ORDER BY s1.major, s1.year;

Комплексные JOIN запросы

-- Детальный отчет по студенту: курсы, оценки, экзамены
SELECT 
    s.first_name || ' ' || s.last_name AS student,
    s.major,
    s.gpa,
    c.course_name,
    c.credits,
    p.first_name || ' ' || p.last_name AS professor,
    e.grade AS final_grade,
    e.status,
    AVG(er.score) AS avg_exam_score,
    COUNT(DISTINCT ex.exam_id) AS exams_taken
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id
INNER JOIN professors p ON c.professor_id = p.professor_id
LEFT JOIN exams ex ON c.course_id = ex.course_id
LEFT JOIN exam_results er ON ex.exam_id = er.exam_id 
                          AND er.student_id = s.student_id
WHERE s.student_id = 1
GROUP BY s.student_id, s.first_name, s.last_name, s.major, s.gpa,
         c.course_name, c.credits, p.first_name, p.last_name, 
         e.grade, e.status
ORDER BY c.course_name;

-- Топ студентов по среднему баллу на экзаменах
SELECT 
    s.first_name || ' ' || s.last_name AS student,
    s.major,
    s.gpa AS official_gpa,
    COUNT(DISTINCT er.exam_id) AS exams_taken,
    ROUND(AVG(er.score / ex.max_score * 100), 2) AS avg_exam_percentage,
    STRING_AGG(DISTINCT c.course_name, ', ') AS courses
FROM students s
INNER JOIN exam_results er ON s.student_id = er.student_id
INNER JOIN exams ex ON er.exam_id = ex.exam_id
INNER JOIN courses c ON ex.course_id = c.course_id
GROUP BY s.student_id, s.first_name, s.last_name, s.major, s.gpa
HAVING COUNT(DISTINCT er.exam_id) >= 2
ORDER BY avg_exam_percentage DESC
LIMIT 10;

Подзапросы (Subqueries)

Подзапросы — это запрос внутри другого запроса. По сути, это SELECT, который вложен в другой SELECT (или INSERT/UPDATE/DELETE), и его результат используется как часть внешнего (основного) запроса.

Подзапросы часто называют вложенными запросами или внутренними запросами.

Зачем нужны подзапросы? (когда их используют вместо JOIN или вместе с ними)

• Получить одно значение (например, среднюю цену, максимальную дату) и сравнить с ней
• Найти записи, которые больше/меньше/равны какому-то вычисленному значению
• Проверить существование связанных записей (EXISTS / NOT EXISTS)
• Получить список id/значений и отфильтровать по нему (IN / NOT IN / ANY / ALL)
• Разбить сложную логику на шаги (особенно когда JOIN получается слишком громоздким)
• Вычислить что-то для каждой строки отдельно (коррелированный подзапрос)

Основные места, куда можно вставить подзапрос

Место	Тип подзапроса	Что обычно возвращает	Пример оператора
WHERE	Скалярный / список / EXISTS	одно значение / список / true	=, >, IN, EXISTS, ANY/ALL
SELECT	Скалярный	одно значение на строку	в столбце выборки
FROM	Таблица (derived table)	набор строк и столбцов	как виртуальная таблица
HAVING	Скалярный / список	для фильтрации после GROUP BY	аналогично WHERE

Короткое сравнение: JOIN vs Подзапрос

Задача	Обычно лучше	Почему
Связать две таблицы и взять поля	JOIN	быстрее, понятнее
Проверить существование	EXISTS	часто быстрее IN
Сравнить с агрегатом (ср. цена и т.д.)	Подзапрос или CTE	удобно
Сложная фильтрация по подмножеству	Подзапрос / CTE / LATERAL	гибкость
Очень большие данные	JOIN или LATERAL	оптимизатор лучше справляется

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

Скалярный подзапрос возвращает одно значение (одна строка, одна колонка).

-- Студенты с GPA выше среднего
SELECT 
    first_name,
    last_name,
    gpa,
    (SELECT AVG(gpa) FROM students) AS avg_gpa,
    gpa - (SELECT AVG(gpa) FROM students) AS difference
FROM students
WHERE gpa > (SELECT AVG(gpa) FROM students)
ORDER BY gpa DESC;

-- Курсы с количеством студентов выше среднего
SELECT 
    c.course_name,
    COUNT(e.student_id) AS enrolled,
    (SELECT AVG(course_count) 
     FROM (
         SELECT COUNT(student_id) AS course_count 
         FROM enrollments 
         WHERE status = 'active'
         GROUP BY course_id
     ) AS avg_calc) AS avg_enrollment
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id 
WHERE e.status = 'active' OR e.status IS NULL
GROUP BY c.course_id, c.course_name
HAVING COUNT(e.student_id) > (
    SELECT AVG(course_count) 
    FROM (
        SELECT COUNT(student_id) AS course_count 
        FROM enrollments 
        WHERE status = 'active'
        GROUP BY course_id
    ) AS avg_calc
);

-- Подзапрос в SELECT для каждой строки
SELECT 
    s.first_name || ' ' || s.last_name AS student,
    s.major,
    (SELECT COUNT(*) 
     FROM enrollments e 
     WHERE e.student_id = s.student_id) AS total_enrollments,
    (SELECT COUNT(*) 
     FROM enrollments e 
     WHERE e.student_id = s.student_id 
       AND e.status = 'completed') AS completed_courses,
    (SELECT AVG(er.score)
     FROM exam_results er
     WHERE er.student_id = s.student_id) AS avg_exam_score
FROM students s
ORDER BY s.last_name;

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

Подзапрос в FROM создает временную таблицу (производную таблицу).

-- Статистика по специальностям
SELECT 
    major,
    student_count,
    avg_gpa,
    max_gpa,
    min_gpa
FROM (
    SELECT 
        major,
        COUNT(*) AS student_count,
        ROUND(AVG(gpa), 2) AS avg_gpa,
        MAX(gpa) AS max_gpa,
        MIN(gpa) AS min_gpa
    FROM students
    GROUP BY major
) AS major_stats
WHERE student_count > 2
ORDER BY avg_gpa DESC;

-- Сложная аналитика с несколькими подзапросами
SELECT 
    cs.course_name,
    cs.enrolled_count,
    es.avg_score,
    ps.professor_name,
    ps.department
FROM (
    -- Подзапрос: курсы и количество студентов
    SELECT 
        c.course_id,
        c.course_name,
        c.professor_id,
        COUNT(e.student_id) AS enrolled_count
    FROM courses c
    LEFT JOIN enrollments e ON c.course_id = e.course_id 
    WHERE e.status = 'active' OR e.status IS NULL
    GROUP BY c.course_id, c.course_name, c.professor_id
) AS cs
LEFT JOIN (
    -- Подзапрос: средний балл по курсам
    SELECT 
        c.course_id,
        ROUND(AVG(er.score / ex.max_score * 100), 2) AS avg_score
    FROM courses c
    JOIN exams ex ON c.course_id = ex.course_id
    JOIN exam_results er ON ex.exam_id = er.exam_id
    GROUP BY c.course_id
) AS es ON cs.course_id = es.course_id
JOIN (
    -- Подзапрос: информация о профессоре
    SELECT 
        professor_id,
        first_name || ' ' || last_name AS professor_name,
        department
    FROM professors
) AS ps ON cs.professor_id = ps.professor_id
ORDER BY cs.enrolled_count DESC;

IN и NOT IN

-- Студенты, записанные на курсы информатики
SELECT 
    first_name,
    last_name,
    major
FROM students
WHERE student_id IN (
    SELECT DISTINCT e.student_id
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
    WHERE c.department = 'Информатика'
)
ORDER BY last_name;

-- Профессора, не ведущие курсы в текущем семестре
SELECT 
    first_name || ' ' || last_name AS professor,
    department
FROM professors
WHERE professor_id NOT IN (
    SELECT professor_id
    FROM courses
    WHERE semester = 'Fall 2024'
      AND professor_id IS NOT NULL
);

-- Студенты, которые НЕ сдавали экзамены
SELECT 
    s.first_name,
    s.last_name,
    s.major,
    COUNT(e.course_id) AS enrolled_courses
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
WHERE s.student_id NOT IN (
    SELECT DISTINCT student_id 
    FROM exam_results
)
GROUP BY s.student_id, s.first_name, s.last_name, s.major
ORDER BY enrolled_courses DESC;

NOT IN с NULL

Будьте осторожны с NOT IN когда подзапрос может вернуть NULL. Это может привести к неожиданным результатам. Используйте NOT EXISTS или добавьте WHERE column IS NOT NULL в подзапросе.

EXISTS и NOT EXISTS

-- Студенты, у которых ЕСТЬ оценка 'A'
SELECT 
    s.first_name,
    s.last_name,
    s.gpa
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.student_id
      AND e.grade = 'A'
)
ORDER BY s.gpa DESC;

-- Курсы, на которые НЕТ записей
SELECT 
    c.course_name,
    c.course_code,
    c.max_students,
    p.first_name || ' ' || p.last_name AS professor
FROM courses c
JOIN professors p ON c.professor_id = p.professor_id
WHERE NOT EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.course_id = c.course_id
)
ORDER BY c.course_name;

-- Студенты, сдававшие хотя бы один экзамен с результатом выше 90%
SELECT 
    s.first_name || ' ' || s.last_name AS student,
    s.major,
    s.gpa
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM exam_results er
    JOIN exams ex ON er.exam_id = ex.exam_id
    WHERE er.student_id = s.student_id
      AND (er.score / ex.max_score * 100) >= 90
)
ORDER BY s.gpa DESC;

-- EXISTS vs IN: производительность
-- EXISTS обычно быстрее для больших наборов данных
-- Эквивалентные запросы:

-- С EXISTS (предпочтительно)
SELECT first_name, last_name
FROM students s
WHERE EXISTS (
    SELECT 1 FROM enrollments e 
    WHERE e.student_id = s.student_id
);

-- С IN
SELECT first_name, last_name
FROM students
WHERE student_id IN (
    SELECT student_id FROM enrollments
);

ANY и ALL

-- Студенты с GPA выше, чем у ЛЮБОГО студента физики
SELECT 
    first_name,
    last_name,
    major,
    gpa
FROM students
WHERE gpa > ANY (
    SELECT gpa FROM students WHERE major = 'Физика'
)
  AND major != 'Физика'
ORDER BY gpa DESC;

-- = ANY эквивалентно IN
SELECT first_name, last_name
FROM students
WHERE major = ANY (ARRAY['Информатика', 'Математика']);

-- Студенты с GPA выше, чем у ВСЕХ студентов физики
SELECT 
    first_name,
    last_name,
    major,
    gpa
FROM students
WHERE gpa > ALL (
    SELECT gpa FROM students WHERE major = 'Физика'
)
ORDER BY gpa DESC;

-- Курсы, где ВСЕ оценки — A или B
SELECT 
    c.course_name,
    COUNT(e.enrollment_id) AS total_grades
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.grade IS NOT NULL
GROUP BY c.course_id, c.course_name
HAVING NOT EXISTS (
    SELECT 1
    FROM enrollments e2
    WHERE e2.course_id = c.course_id
      AND e2.grade NOT IN ('A', 'B')
      AND e2.grade IS NOT NULL
);

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

Коррелированный подзапрос ссылается на столбцы внешнего запроса.

-- Студенты с GPA выше среднего по их специальности
SELECT 
    s.first_name,
    s.last_name,
    s.major,
    s.gpa,
    (SELECT ROUND(AVG(gpa), 2) 
     FROM students s2 
     WHERE s2.major = s.major) AS major_avg_gpa
FROM students s
WHERE s.gpa > (
    SELECT AVG(gpa)
    FROM students s2
    WHERE s2.major = s.major
)
ORDER BY s.major, s.gpa DESC;

-- Для каждого курса — студент с наивысшим баллом на экзамене
SELECT 
    c.course_name,
    (SELECT s.first_name || ' ' || s.last_name
     FROM exam_results er
     JOIN students s ON er.student_id = s.student_id
     JOIN exams ex ON er.exam_id = ex.exam_id
     WHERE ex.course_id = c.course_id
     ORDER BY er.score DESC
     LIMIT 1) AS top_student,
    (SELECT MAX(er.score)
     FROM exam_results er
     JOIN exams ex ON er.exam_id = ex.exam_id
     WHERE ex.course_id = c.course_id) AS highest_score
FROM courses c
WHERE EXISTS (
    SELECT 1 FROM exams WHERE course_id = c.course_id
)
ORDER BY c.course_name;

-- Курсы с количеством студентов выше среднего по факультету
SELECT 
    c.course_name,
    c.department,
    COUNT(e.student_id) AS enrolled,
    (SELECT ROUND(AVG(course_count))
     FROM (
         SELECT COUNT(e2.student_id) AS course_count
         FROM courses c2
         LEFT JOIN enrollments e2 ON c2.course_id = e2.course_id
         WHERE c2.department = c.department
           AND (e2.status = 'active' OR e2.status IS NULL)
         GROUP BY c2.course_id
     ) AS dept_avg) AS dept_avg_enrollment
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id AND e.status = 'active'
GROUP BY c.course_id, c.course_name, c.department
HAVING COUNT(e.student_id) > (
    SELECT AVG(course_count)
    FROM (
        SELECT COUNT(e2.student_id) AS course_count
        FROM courses c2
        LEFT JOIN enrollments e2 ON c2.course_id = e2.course_id
        WHERE c2.department = c.department
          AND (e2.status = 'active' OR e2.status IS NULL)
        GROUP BY c2.course_id
    ) AS dept_avg
);

Функции

Функции — это один из самых мощных инструментов для расширения возможностей базы данных. Они позволяют писать переиспользуемый код, который можно вызывать в запросах, триггерах, политиках безопасности и т.д.

PostgreSQL различает встроенные функции (их сотни: now(), sum(), jsonb_build_object(), string_agg() и т.д.) и пользовательские функции (user-defined functions, UDF), которые ты создаёшь сам с помощью CREATE FUNCTION.

Основные виды пользовательских функций (по типу возвращаемого значения)

Вид функции	Что возвращает	Ключевое слово в RETURNS	Когда использовать (примеры)	Пример вызова
Скалярная (обычная)	Одно значение (число, строка, дата…)	RETURNS тип	Вычисления, форматирование, проверки	SELECT calc_discount(price, 0.15);
SET OF / TABLE	Набор строк (таблицу)	RETURNS SETOF тип | RETURNS TABLE	Возвращать несколько строк, как подзапрос или view	SELECT * FROM get_active_users();
Таблица с колонками	Таблицу с именованными столбцами	RETURNS TABLE (col1 тип, col2 тип)	Возвращать структурированный результат (как CTE)	SELECT * FROM get_orders_summary();
Триггерная	TRIGGER	RETURNS TRIGGER	Автоматика: BEFORE/AFTER INSERT/UPDATE/DELETE	Автоматически в триггере
Агрегатная	Одно значение по группе строк	RETURNS тип + CREATE AGGREGATE	Собственный агрегат (медиана, mode, json_merge и т.д.)	SELECT my_median(salary) FROM emp;
Оконная (window)	Значение для каждой строки с окном	RETURNS тип + window function	Ранжирование, running total, lag/lead с кастомной логикой	В OVER (...)

Примеры

1. Простая скалярная функция (sql)

CREATE OR REPLACE FUNCTION discount_price(p numeric, percent numeric)
RETURNS numeric AS $$
    SELECT p * (1 - percent / 100);
$$ LANGUAGE sql IMMUTABLE;

2. Функция, возвращающая таблицу (plpgsql)

CREATE OR REPLACE FUNCTION get_recent_orders(days int DEFAULT 30)
RETURNS TABLE (
    order_id int,
    customer_name text,
    total numeric
) AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, u.name, SUM(oi.qty * p.price)
    FROM orders o
    JOIN users u ON o.user_id = u.id
    JOIN order_items oi ON oi.order_id = o.id
    JOIN products p ON oi.product_id = p.id
    WHERE o.created_at >= current_date - days * interval '1 day'
    GROUP BY o.id, u.name;
END;
$$ LANGUAGE plpgsql STABLE;

3. Триггерная функция (обновление updated_at)

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_update_ts
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

Триггеры

Триггеры — это специальные механизмы (по сути, автоматические "хуки"), которые запускают заданную функцию при наступлении определённых событий в таблице или представлении.

Они позволяют автоматически выполнять действия в ответ на операции:

• INSERT
• UPDATE
• DELETE
• (реже) TRUNCATE

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

Основные типы по времени срабатывания

Тип	Когда срабатывает	Что можно делать в функции	Самые частые применения
BEFORE	До выполнения операции (до проверки constraint)	Изменять NEW (вставляемые/обновляемые значения), возвращать NULL — отменить операцию для строки	Валидация, автозаполнение (updated_at), нормализация данных
AFTER	После успешного выполнения операции	Читать OLD/NEW, но нельзя менять строку; можно вставить/обновить другие таблицы	Аудит (логи изменений), обновление счётчиков, уведомления
INSTEAD OF	Вместо операции (только на views)	Полностью заменяет INSERT/UPDATE/DELETE на view	Updatable views, сложные представления

Уровень выполнения

• FOR EACH ROW — срабатывает для каждой изменённой строки (самый распространённый)
• FOR EACH STATEMENT — срабатывает один раз на весь запрос (удобно для больших операций, transition tables в PostgreSQL 10+)

Простой пример

-- 1. Функция триггера (PL/pgSQL)
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;  -- обязательно для BEFORE!
END;
$$ LANGUAGE plpgsql;

-- 2. Сам триггер
CREATE TRIGGER trig_update_timestamp
BEFORE UPDATE ON users                  -- или orders, products и т.д.
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Теперь любое UPDATE на таблице users автоматически обновит поле updated_at.

Ещё 3 популярных примера

1. Аудит изменений (AFTER UPDATE/INSERT/DELETE)

CREATE TRIGGER audit_log
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION log_order_changes();

Функция вставляет запись в таблицу audit с OLD/NEW значениями, кто изменил и когда.

2. Запрет удаления (BEFORE DELETE)

CREATE TRIGGER prevent_delete_paid
BEFORE DELETE ON orders
FOR EACH ROW
WHEN (OLD.status = 'paid')
EXECUTE FUNCTION raise_paid_order_delete_error();

Функция делает RAISE EXCEPTION, если пытаются удалить оплаченный заказ.

3. Синхронизация счётчика (AFTER INSERT/DELETE)

CREATE TRIGGER update_user_order_count
AFTER INSERT OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION recalc_user_orders();

Функция обновляет поле orders_count в таблице users.

Агрегатные функции

Агрегатные функции (aggregate functions) — это специальные функции, которые берут набор строк (много значений) и возвращают одно единственное значение — итог, сводку, статистику.

Они отвечают на вопросы типа:

• Сколько всего заказов?
• Какая средняя цена товара?
• Самый дорогой заказ?
• Общая сумма продаж за месяц?

Без агрегатных функций ты можешь получить только отдельные строки. С ними — сводные показатели.

Полезные правила и ловушки

• Агрегаты нельзя использовать в WHERE → используй HAVING для фильтрации после группировки

-- правильно
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 10000;

• COUNT(*) считает все строки, COUNT(column) — только не-NULL
• Если группа пустая → большинство агрегатов вернут NULL (кроме COUNT(*) → 0)
• Можно комбинировать несколько агрегатов в одном запросе
• DISTINCT внутри: COUNT(DISTINCT user_id)

Основные агрегатные функции

-- COUNT — подсчет
SELECT 
    COUNT(*) AS total_students,
    COUNT(DISTINCT major) AS unique_majors,
    COUNT(mentor_id) AS students_with_mentors,  -- Не считает NULL
    COUNT(*) - COUNT(mentor_id) AS without_mentors
FROM students;

-- SUM — сумма
SELECT 
    department,
    COUNT(*) AS professor_count,
    SUM(salary) AS total_payroll,
    ROUND(SUM(salary) / COUNT(*), 2) AS avg_salary_check
FROM professors
GROUP BY department
ORDER BY total_payroll DESC;

-- AVG — среднее значение
SELECT 
    major,
    COUNT(*) AS student_count,
    ROUND(AVG(gpa), 3) AS avg_gpa,
    ROUND(AVG(EXTRACT(YEAR FROM AGE(date_of_birth))), 1) AS avg_age
FROM students
GROUP BY major
ORDER BY avg_gpa DESC;

-- MIN и MAX — минимум и максимум
SELECT 
    major,
    MIN(gpa) AS lowest_gpa,
    MAX(gpa) AS highest_gpa,
    MAX(gpa) - MIN(gpa) AS gpa_range,
    MIN(enrollment_date) AS earliest_enrollment,
    MAX(enrollment_date) AS latest_enrollment
FROM students
GROUP BY major;

-- Комбинация всех агрегатных функций
SELECT 
    c.department,
    COUNT(DISTINCT c.course_id) AS courses_offered,
    COUNT(DISTINCT e.student_id) AS unique_students,
    COUNT(e.enrollment_id) AS total_enrollments,
    ROUND(AVG(c.credits), 2) AS avg_credits,
    MIN(c.credits) AS min_credits,
    MAX(c.credits) AS max_credits,
    SUM(c.credits) AS total_credits
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.department
ORDER BY unique_students DESC;

GROUP BY с множественными колонками

-- Группировка по нескольким полям
SELECT 
    major,
    year,
    city,
    COUNT(*) AS student_count,
    ROUND(AVG(gpa), 2) AS avg_gpa
FROM students
GROUP BY major, year, city
ORDER BY major, year, city;

-- Группировка с вычисляемыми полями
SELECT 
    EXTRACT(YEAR FROM enrollment_date) AS enrollment_year,
    major,
    COUNT(*) AS students_enrolled,
    ROUND(AVG(gpa), 2) AS avg_gpa
FROM students
GROUP BY EXTRACT(YEAR FROM enrollment_date), major
ORDER BY enrollment_year DESC, students_enrolled DESC;

-- Группировка по диапазонам
SELECT 
    CASE 
        WHEN gpa >= 3.7 THEN 'Excellent (3.7+)'
        WHEN gpa >= 3.3 THEN 'Good (3.3-3.69)'
        WHEN gpa >= 3.0 THEN 'Satisfactory (3.0-3.29)'
        ELSE 'Below Average (<3.0)'
    END AS performance_category,
    major,
    COUNT(*) AS student_count,
    ROUND(AVG(gpa), 2) AS avg_gpa_in_category
FROM students
GROUP BY 
    CASE 
        WHEN gpa >= 3.7 THEN 'Excellent (3.7+)'
        WHEN gpa >= 3.3 THEN 'Good (3.3-3.69)'
        WHEN gpa >= 3.0 THEN 'Satisfactory (3.0-3.29)'
        ELSE 'Below Average (<3.0)'
    END,
    major
ORDER BY major, avg_gpa_in_category DESC;

HAVING — Фильтрация групп

-- Базовый HAVING
SELECT 
    major,
    COUNT(*) AS student_count,
    ROUND(AVG(gpa), 2) AS avg_gpa
FROM students
GROUP BY major
HAVING COUNT(*) >= 3
ORDER BY avg_gpa DESC;

-- HAVING с множественными условиями
SELECT 
    c.department,
    COUNT(DISTINCT c.course_id) AS course_count,
    COUNT(DISTINCT e.student_id) AS student_count,
    ROUND(AVG(c.credits), 2) AS avg_credits
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.status = 'active'
GROUP BY c.department
HAVING COUNT(DISTINCT c.course_id) >= 2
   AND COUNT(DISTINCT e.student_id) >= 5
ORDER BY student_count DESC;

-- WHERE vs HAVING
SELECT 
    major,
    year,
    COUNT(*) AS student_count,
    ROUND(AVG(gpa), 2) AS avg_gpa
FROM students
WHERE city = 'Москва'  -- Фильтр ДО группировки
GROUP BY major, year
HAVING AVG(gpa) > 3.5  -- Фильтр ПОСЛЕ группировки
ORDER BY avg_gpa DESC;

-- HAVING с подзапросами
SELECT 
    p.department,
    COUNT(*) AS professor_count,
    ROUND(AVG(p.salary), 2) AS avg_salary
FROM professors p
GROUP BY p.department
HAVING AVG(p.salary) > (
    SELECT AVG(salary) FROM professors
)
ORDER BY avg_salary DESC;

Агрегатные функции для строк

-- STRING_AGG — объединение строк
SELECT 
    c.course_name,
    STRING_AGG(s.first_name || ' ' || s.last_name, ', ' ORDER BY s.last_name) AS enrolled_students
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
JOIN students s ON e.student_id = s.student_id
WHERE e.status = 'active'
GROUP BY c.course_id, c.course_name
ORDER BY c.course_name;

-- ARRAY_AGG — создание массива
SELECT 
    major,
    ARRAY_AGG(first_name || ' ' || last_name ORDER BY gpa DESC) AS students,
    ARRAY_AGG(gpa ORDER BY gpa DESC) AS gpas
FROM students
GROUP BY major;

-- JSON_AGG — создание JSON массива
SELECT 
    p.first_name || ' ' || p.last_name AS professor,
    JSON_AGG(
        JSON_BUILD_OBJECT(
            'course_name', c.course_name,
            'course_code', c.course_code,
            'credits', c.credits,
            'students', (
                SELECT COUNT(*) 
                FROM enrollments 
                WHERE course_id = c.course_id 
                  AND status = 'active'
            )
        ) ORDER BY c.course_name
    ) AS courses
FROM professors p
LEFT JOIN courses c ON p.professor_id = c.professor_id
GROUP BY p.professor_id, p.first_name, p.last_name;

Статистические агрегатные функции

-- STDDEV — стандартное отклонение
-- VARIANCE — дисперсия
SELECT 
    major,
    COUNT(*) AS students,
    ROUND(AVG(gpa), 2) AS avg_gpa,
    ROUND(STDDEV(gpa), 3) AS stddev_gpa,
    ROUND(VARIANCE(gpa), 3) AS variance_gpa,
    MIN(gpa) AS min_gpa,
    MAX(gpa) AS max_gpa
FROM students
GROUP BY major
HAVING COUNT(*) >= 3
ORDER BY avg_gpa DESC;

-- PERCENTILE_CONT — процентили (непрерывное распределение)
-- PERCENTILE_DISC — процентили (дискретное распределение)
SELECT 
    major,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY gpa) AS gpa_25th,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY gpa) AS gpa_median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY gpa) AS gpa_75th,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY gpa) AS gpa_90th
FROM students
GROUP BY major
ORDER BY gpa_median DESC;

-- MODE — мода (наиболее частое значение)
SELECT 
    MODE() WITHIN GROUP (ORDER BY major) AS most_common_major,
    MODE() WITHIN GROUP (ORDER BY city) AS most_common_city,
    MODE() WITHIN GROUP (ORDER BY year) AS most_common_year
FROM students;

FILTER — Условная агрегация

-- FILTER для условных агрегаций (PostgreSQL 9.4+)
SELECT 
    major,
    COUNT(*) AS total_students,
    COUNT(*) FILTER (WHERE gpa >= 3.7) AS excellent_students,
    COUNT(*) FILTER (WHERE gpa >= 3.3 AND gpa < 3.7) AS good_students,
    COUNT(*) FILTER (WHERE gpa < 3.0) AS struggling_students,
    ROUND(AVG(gpa) FILTER (WHERE year >= 3), 2) AS senior_avg_gpa
FROM students
GROUP BY major
ORDER BY total_students DESC;

-- Сравнение с CASE
-- Эквивалентные запросы:

-- С FILTER (современный способ)
SELECT 
    department,
    COUNT(*) FILTER (WHERE salary > 120000) AS high_earners,
    COUNT(*) FILTER (WHERE salary <= 120000) AS regular_earners
FROM professors
GROUP BY department;

-- С CASE (старый способ)
SELECT 
    department,
    COUNT(CASE WHEN salary > 120000 THEN 1 END) AS high_earners,
    COUNT(CASE WHEN salary <= 120000 THEN 1 END) AS regular_earners
FROM professors
GROUP BY department;

-- Сложная аналитика с FILTER
SELECT 
    c.course_name,
    COUNT(DISTINCT e.student_id) AS total_enrolled,
    COUNT(DISTINCT e.student_id) FILTER (WHERE e.status = 'completed') AS completed,
    COUNT(DISTINCT e.student_id) FILTER (WHERE e.status = 'dropped') AS dropped,
    COUNT(DISTINCT e.student_id) FILTER (WHERE e.grade IN ('A', 'B')) AS ab_grades,
    ROUND(
        COUNT(DISTINCT e.student_id) FILTER (WHERE e.grade IN ('A', 'B'))::NUMERIC / 
        NULLIF(COUNT(DISTINCT e.student_id) FILTER (WHERE e.status = 'completed'), 0) * 100,
        2
    ) AS success_rate
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name
ORDER BY success_rate DESC NULLS LAST;

Продвинутые техники SELECT

DISTINCT ON — Уникальные записи по критерию

-- Первая запись для каждого студента (последняя по дате)
SELECT DISTINCT ON (student_id)
    student_id,
    course_id,
    enrollment_date,
    grade,
    status
FROM enrollments
ORDER BY student_id, enrollment_date DESC;

-- Последний экзамен каждого типа для каждого курса
SELECT DISTINCT ON (course_id, exam_type)
    c.course_name,
    e.exam_type,
    e.exam_date,
    e.max_score
FROM exams e
JOIN courses c ON e.course_id = c.course_id
ORDER BY course_id, exam_type, exam_date DESC;

-- Лучший результат каждого студента на экзаменах
SELECT DISTINCT ON (er.student_id)
    s.first_name || ' ' || s.last_name AS student,
    c.course_name,
    e.exam_type,
    er.score,
    e.max_score,
    ROUND(er.score / e.max_score * 100, 2) AS percentage
FROM exam_results er
JOIN students s ON er.student_id = s.student_id
JOIN exams e ON er.exam_id = e.exam_id
JOIN courses c ON e.course_id = c.course_id
ORDER BY er.student_id, (er.score / e.max_score) DESC;

LATERAL JOIN — Боковое объединение

LATERAL позволяет подзапросу ссылаться на колонки из предыдущих таблиц в FROM.

-- Для каждого профессора — топ-3 студента по среднему баллу на экзаменах
SELECT 
    p.first_name || ' ' || p.last_name AS professor,
    top_students.student_name,
    top_students.avg_score,
    top_students.exams_taken
FROM professors p
CROSS JOIN LATERAL (
    SELECT 
        s.first_name || ' ' || s.last_name AS student_name,
        ROUND(AVG(er.score), 2) AS avg_score,
        COUNT(er.exam_id) AS exams_taken
    FROM courses c
    JOIN exams ex ON c.course_id = ex.course_id
    JOIN exam_results er ON ex.exam_id = er.exam_id
    JOIN students s ON er.student_id = s.student_id
    WHERE c.professor_id = p.professor_id
    GROUP BY s.student_id, s.first_name, s.last_name
    ORDER BY AVG(er.score) DESC
    LIMIT 3
) AS top_students
ORDER BY professor, top_students.avg_score DESC;

-- Для каждого курса — последние 3 записи
SELECT 
    c.course_name,
    recent_enrollments.student_name,
    recent_enrollments.enrollment_date
FROM courses c
CROSS JOIN LATERAL (
    SELECT 
        s.first_name || ' ' || s.last_name AS student_name,
        e.enrollment_date
    FROM enrollments e
    JOIN students s ON e.student_id = s.student_id
    WHERE e.course_id = c.course_id
    ORDER BY e.enrollment_date DESC
    LIMIT 3
) AS recent_enrollments
ORDER BY c.course_name, recent_enrollments.enrollment_date DESC;

-- LATERAL с LEFT JOIN (для всех профессоров, даже без студентов)
SELECT 
    p.first_name || ' ' || p.last_name AS professor,
    COALESCE(top_student.student_name, 'No students') AS best_student,
    top_student.avg_score
FROM professors p
LEFT JOIN LATERAL (
    SELECT 
        s.first_name || ' ' || s.last_name AS student_name,
        ROUND(AVG(er.score), 2) AS avg_score
    FROM courses c
    JOIN exams ex ON c.course_id = ex.course_id
    JOIN exam_results er ON ex.exam_id = er.exam_id
    JOIN students s ON er.student_id = s.student_id
    WHERE c.professor_id = p.professor_id
    GROUP BY s.student_id, s.first_name, s.last_name
    ORDER BY AVG(er.score) DESC
    LIMIT 1
) AS top_student ON TRUE
ORDER BY professor;

UNION, INTERSECT, EXCEPT

-- UNION — объединение результатов (без дубликатов)
SELECT first_name, last_name, 'Student' AS role, major AS department
FROM students
WHERE city = 'Москва'
UNION
SELECT first_name, last_name, 'Professor' AS role, department
FROM professors
ORDER BY last_name;

-- UNION ALL — с дубликатами (быстрее)
SELECT course_id, 'Enrollment' AS source, COUNT(*) AS count
FROM enrollments
GROUP BY course_id
UNION ALL
SELECT course_id, 'Exam' AS source, COUNT(*) AS count
FROM exams
GROUP BY course_id
ORDER BY course_id, source;

-- INTERSECT — пересечение (общие записи)
SELECT student_id
FROM enrollments
WHERE course_id IN (
    SELECT course_id FROM courses WHERE department = 'Информатика'
)
INTERSECT
SELECT student_id
FROM enrollments
WHERE course_id IN (
    SELECT course_id FROM courses WHERE department = 'Математика'
)
ORDER BY student_id;

-- EXCEPT — разность (в первом, но не во втором)
SELECT student_id
FROM students
WHERE major = 'Информатика'
EXCEPT
SELECT DISTINCT student_id
FROM enrollments
WHERE course_id IN (
    SELECT course_id FROM courses WHERE course_code LIKE 'CS%'
)
ORDER BY student_id;

-- Комбинация операторов с подзапросами
(SELECT 'High GPA Students' AS category, COUNT(*) AS count
 FROM students WHERE gpa >= 3.7)
UNION ALL
(SELECT 'Courses with Exams', COUNT(DISTINCT course_id)
 FROM exams)
UNION ALL
(SELECT 'Active Enrollments', COUNT(*)
 FROM enrollments WHERE status = 'active');