Сложные отчёты: JOIN, агрегаты, подзапросы

Сложные отчёты: JOIN, агрегаты, подзапросы

Финальный урок курса. Здесь вы объединяете всё изученное: JOIN, агрегатные функции, подзапросы, CTE, оконные функции — в реальных аналитических отчётах. Это те запросы, которые пишут backend-разработчики, аналитики и DBA в production-системах.

Отчёт 1: Топ авторов по активности

Рейтинг авторов: количество постов, суммарные лайки, суммарные комментарии к их постам:

SELECT
    u.username,
    COUNT(DISTINCT p.id)   AS post_count,
    COALESCE(SUM(likes_cnt.cnt), 0)     AS total_likes,
    COALESCE(SUM(comments_cnt.cnt), 0)  AS total_comments,
    COALESCE(SUM(likes_cnt.cnt), 0) +
    COALESCE(SUM(comments_cnt.cnt), 0)  AS engagement_score
FROM users u
LEFT JOIN posts p ON p.author_id = u.id AND p.published = TRUE
LEFT JOIN (
    SELECT post_id, COUNT(*) AS cnt FROM likes GROUP BY post_id
) likes_cnt ON likes_cnt.post_id = p.id
LEFT JOIN (
    SELECT post_id, COUNT(*) AS cnt FROM comments GROUP BY post_id
) comments_cnt ON comments_cnt.post_id = p.id
GROUP BY u.id, u.username
ORDER BY engagement_score DESC;

Три LEFT JOIN: посты автора, лайки к каждому посту (через производную таблицу), комментарии (через производную таблицу). COALESCE(..., 0) заменяет NULL нулём для авторов без постов.

Отчёт 2: Популярные посты с деталями

Каждый пост с полной информацией: автор, категория, теги, количество лайков и комментариев:

WITH post_metrics AS (
    SELECT
        p.id,
        COUNT(DISTINCT l.user_id)  AS like_count,
        COUNT(DISTINCT c.id)       AS comment_count
    FROM posts p
    LEFT JOIN likes    l ON l.post_id = p.id
    LEFT JOIN comments c ON c.post_id = p.id
    GROUP BY p.id
),
post_tag_list AS (
    SELECT pt.post_id,
           STRING_AGG(t.name, ', ' ORDER BY t.name) AS tags
    FROM post_tags pt
    JOIN tags t ON t.id = pt.tag_id
    GROUP BY pt.post_id
)
SELECT
    p.title,
    u.username      AS author,
    c.name          AS category,
    COALESCE(ptl.tags, 'без тегов') AS tags,
    pm.like_count,
    pm.comment_count,
    p.published_at
FROM posts p
JOIN users u           ON u.id = p.author_id
JOIN categories c      ON c.id = p.category_id
JOIN post_metrics pm   ON pm.id = p.id
LEFT JOIN post_tag_list ptl ON ptl.post_id = p.id
WHERE p.published = TRUE
ORDER BY pm.like_count DESC, pm.comment_count DESC;

STRING_AGG(t.name, ', ' ORDER BY t.name) собирает теги в строку через запятую. Это удобно для отчётов — не нужно делать отдельный запрос за тегами.

Отчёт 3: Активность по месяцам

Динамика публикаций и вовлечённости по месяцам:

SELECT
    DATE_TRUNC('month', p.published_at) AS month,
    COUNT(p.id)                          AS posts_published,
    SUM(COUNT(p.id)) OVER (ORDER BY DATE_TRUNC('month', p.published_at))
                                         AS cumulative_posts
FROM posts p
WHERE p.published = TRUE
GROUP BY DATE_TRUNC('month', p.published_at)
ORDER BY month;

SUM(...) OVER (ORDER BY ...) — оконная функция для нарастающего итога. Результат: каждый месяц плюс накопленное количество постов от начала.

Отчёт 4: Читатели без собственных постов

Пользователи, которые только комментируют и лайкают, но не публикуют:

SELECT u.username, u.email,
       COUNT(DISTINCT c.id)    AS comments_written,
       COUNT(DISTINCT l.post_id) AS posts_liked
FROM users u
LEFT JOIN posts p    ON p.author_id = u.id AND p.published = TRUE
LEFT JOIN comments c ON c.author_id = u.id
LEFT JOIN likes l    ON l.user_id = u.id
WHERE p.id IS NULL    -- нет опубликованных постов
GROUP BY u.id, u.username, u.email
HAVING COUNT(DISTINCT c.id) + COUNT(DISTINCT l.post_id) > 0
ORDER BY comments_written DESC;

LEFT JOIN posts ... WHERE p.id IS NULL — паттерн «найти записи без совпадений» из модуля 6. HAVING фильтрует пользователей, у которых есть хоть какая-то активность.

Отчёт 5: Комментаторы поста с ранжированием

Топ-3 комментатора для каждого поста (кто комментировал чаще):

WITH comment_ranks AS (
    SELECT
        p.title                   AS post_title,
        u.username                AS commenter,
        COUNT(c.id)               AS comment_count,
        RANK() OVER (
            PARTITION BY p.id
            ORDER BY COUNT(c.id) DESC
        ) AS rnk
    FROM posts p
    JOIN comments c ON c.post_id = p.id
    JOIN users u    ON u.id = c.author_id
    GROUP BY p.id, p.title, u.id, u.username
)
SELECT post_title, commenter, comment_count, rnk
FROM comment_ranks
WHERE rnk <= 3
ORDER BY post_title, rnk;

RANK() OVER (PARTITION BY p.id ORDER BY ...) — ранжирование внутри каждого поста. Оконная функция в этом запросе работает после GROUP BY: сначала агрегация, потом ранжирование агрегатов.

Отчёт 6: Посты без комментариев, опубликованные давно

Посты-«сироты»: опубликованы больше недели назад, но никто не прокомментировал:

SELECT p.title, u.username AS author,
       p.published_at,
       NOW() - p.published_at AS age
FROM posts p
JOIN users u ON u.id = p.author_id
WHERE p.published = TRUE
  AND p.published_at < NOW() - INTERVAL '7 days'
  AND NOT EXISTS (
      SELECT 1 FROM comments c WHERE c.post_id = p.id
  )
ORDER BY p.published_at;

NOT EXISTS безопаснее NOT IN (нет проблем с NULL). NOW() - p.published_at возвращает INTERVAL — это удобно для отображения «как давно».

Отчёт 7: Сравнение категорий

Какие категории привлекают больше всего читателей (суммарные лайки и комментарии):

SELECT
    c.name AS category,
    COUNT(DISTINCT p.id)           AS posts_count,
    COALESCE(SUM(l.like_count), 0) AS total_likes,
    COALESCE(SUM(cm.comment_count), 0) AS total_comments
FROM categories c
LEFT JOIN posts p ON p.category_id = c.id AND p.published = TRUE
LEFT JOIN (
    SELECT post_id, COUNT(*) AS like_count
    FROM likes GROUP BY post_id
) l ON l.post_id = p.id
LEFT JOIN (
    SELECT post_id, COUNT(*) AS comment_count
    FROM comments GROUP BY post_id
) cm ON cm.post_id = p.id
GROUP BY c.id, c.name
ORDER BY total_likes + total_comments DESC;

Этот запрос показывает категории, включая те, у которых нет постов (через LEFT JOIN) — чтобы не упустить «пустые» категории.

Как отлаживать сложные запросы

Когда запрос не работает — разбивайте на части:

-- Шаг 1: проверить каждый CTE отдельно
WITH post_metrics AS (
    SELECT p.id, COUNT(DISTINCT l.user_id) AS like_count
    FROM posts p
    LEFT JOIN likes l ON l.post_id = p.id
    GROUP BY p.id
)
SELECT * FROM post_metrics LIMIT 5;  -- Проверяем промежуточный результат

-- Шаг 2: добавить JOIN
-- Шаг 3: добавить фильтры
-- Шаг 4: добавить сортировку

Итеративное построение сложного запроса — стандартная практика. Не пишите весь запрос сразу; добавляйте часть за частью, проверяя промежуточные результаты.

EXPLAIN ANALYZE для финального запроса:

EXPLAIN ANALYZE
WITH post_metrics AS (...)
SELECT ...;

Ищите Seq Scan на больших таблицах — это сигнал добавить индекс. Nested Loop с большим rows — сигнал оптимизировать JOIN.

Финальные мысли о курсе

Вы прошли путь от первого SELECT до проектирования схемы и написания аналитических отчётов. Что изучили:

Основы DQL: SELECT, фильтрация (WHERE), сортировка (ORDER BY), ограничение (LIMIT/OFFSET), агрегации (GROUP BY, HAVING).

Многотабличные запросы: все виды JOIN (INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF), подзапросы (scalar, IN, correlated, EXISTS, ANY/ALL), UNION/UNION ALL, CTE.

DML: INSERT, UPDATE, DELETE, UPSERT (ON CONFLICT).

DDL: CREATE TABLE, типы данных, ограничения (PK, FK, NOT NULL, UNIQUE, CHECK, DEFAULT), ALTER TABLE, DROP TABLE, TRUNCATE.

Производительность: индексы (B-tree, частичные, составные, покрывающие), EXPLAIN ANALYZE.

Транзакции и надёжность: BEGIN/COMMIT/ROLLBACK, ACID, уровни изоляции, MVCC.

Проектирование: нормальные формы, типы связей, ER-диаграммы, антипаттерны.

Что дальше в SQL

Темы для дальнейшего изучения:

  • Оконные функции подробнее (LEAD, LAG, FIRST_VALUE, NTILE)
  • Полнотекстовый поиск в PostgreSQL (tsvector, tsquery, GIN)
  • JSON/JSONB операторы и функции
  • Партиционирование больших таблиц
  • Репликация и высокая доступность
  • pg_cron для задач по расписанию

Язык SQL прост в базе, но глубок в деталях. Ключ к мастерству — практика на реальных данных и изучение плана выполнения EXPLAIN ANALYZE.

Паттерны для запоминания

Из всех отчётов этого урока — несколько паттернов, которые встречаются снова и снова:

«Присоединить агрегат через производную таблицу»:

LEFT JOIN (SELECT post_id, COUNT(*) AS cnt FROM likes GROUP BY post_id) l
    ON l.post_id = p.id

Лучше, чем COUNT(l.id) с GROUP BY — особенно когда нужно агрегировать несколько разных таблиц к одному посту.

«CTE как именованный промежуточный шаг»:

WITH metrics AS (...), rankings AS (...)
SELECT ... FROM posts JOIN metrics ... JOIN rankings ...

Каждый CTE — один слой абстракции. Итоговый SELECT читается почти как prose.

«LEFT JOIN + WHERE IS NULL = исключение»:

LEFT JOIN posts p ON p.author_id = u.id
WHERE p.id IS NULL  -- пользователи без постов

Элегантнее NOT EXISTS в некоторых случаях, хотя NOT EXISTS безопаснее с NULL.

Краткий итог модуля

  • Проект: 7 таблиц, типы связей 1:N и M:N, правильные ON DELETE
  • Тестовые данные: 4 пользователя, 6 постов, 8 комментариев, 10 лайков
  • Аналитика: TOP-авторы, популярные посты, активность по месяцам
  • Паттерны: LEFT JOIN + WHERE IS NULL, NOT EXISTS, STRING_AGG, оконные функции
  • CTE делают сложные отчёты читаемыми: каждый шаг — именованный запрос

Попробуйте интерактивную версию

Практические задачи, квизы и AI-наставник — бесплатный старт без карты

Перейти к практике