Модуль 02 — Агрегация и группировка данных

Агрегатные функции вычисляют одно значение для набора строк: подсчёт, сумму, среднее, минимум, максимум.

-- Количество строк в таблице
SELECT COUNT(*) AS total_users
FROM   users;

-- COUNT(column) — не считает NULL
SELECT COUNT(city) AS with_city
FROM   users;

-- Сумма, среднее, минимум, максимум score постов
SELECT
    SUM(score)              AS total_score,
    ROUND(AVG(score), 2)   AS avg_score,
    MIN(score)              AS min_score,
    MAX(score)              AS max_score
FROM   posts;

GROUP BY разделяет строки на группы по значениям указанных столбцов, а затем применяет агрегатные функции к каждой группе.

-- Количество просмотров для каждого пользователя
SELECT user_id,
       COUNT(*) AS count_of_views
FROM   views
GROUP BY user_id
ORDER BY count_of_views DESC, user_id ASC;

-- Метрики по каждому сообществу: лайки и статистика score
SELECT p.community_id,
       COUNT(*) AS count_of_likes,
       ROUND(AVG(p.score), 2) AS average_score,
       MAX(p.score) AS max_score,
       MIN(p.score) AS min_score
FROM   likes l
JOIN   posts p ON p.id = l.post_id
GROUP BY p.community_id
ORDER BY p.community_id;

WHERE фильтрует строки до группировки. HAVING — фильтрует готовые группы после GROUP BY.

-- Пользователи, просмотревшие сообщества более 2 раз
SELECT user_id,
       COUNT(*) AS count_of_views
FROM   views
GROUP BY user_id
HAVING   COUNT(*) > 2;

-- Сообщества со средним score постов выше 900
SELECT community_id,
       ROUND(AVG(score), 2) AS avg_score
FROM   posts
GROUP BY community_id
HAVING   AVG(score) > 900;

Объединим знания — посчитаем метрики сообществ по лайкам И по просмотрам:

-- Топ-3 сообщества по количеству лайков
-- ORDER BY + LIMIT внутри UNION требуют скобок!
(SELECT c.name, COUNT(*) AS count, 'like' AS action_type
 FROM   likes l
 JOIN   posts p       ON p.id  = l.post_id
 JOIN   communities c ON c.id  = p.community_id
 GROUP BY c.name
 ORDER BY count DESC
 LIMIT  3)

UNION ALL

-- Топ-3 сообщества по количеству просмотров
(SELECT c.name, COUNT(*) AS count, 'view' AS action_type
 FROM   views vw
 JOIN   communities c ON c.id = vw.community_id
 GROUP BY c.name
 ORDER BY count DESC
 LIMIT  3);

-- Суммарная активность по каждому сообществу (лайки + просмотры)
WITH liked AS (
    SELECT c.name, COUNT(*) AS cnt
    FROM   likes l
    JOIN   posts p       ON p.id  = l.post_id
    JOIN   communities c ON c.id  = p.community_id
    GROUP BY c.name
),
viewed AS (
    SELECT c.name, COUNT(*) AS cnt
    FROM   views vw
    JOIN   communities c ON c.id = vw.community_id
    GROUP BY c.name
)
SELECT
    COALESCE(l.name, v.name) AS name,
    COALESCE(l.cnt, 0) + COALESCE(v.cnt, 0) AS total_count
FROM   liked  l
FULL JOIN viewed v ON v.name = l.name
ORDER BY total_count DESC, name ASC;

-- Явное приведение типа
SELECT CAST(42 AS text) AS str_value;

-- Краткая запись в PostgreSQL
SELECT 42::text AS str_value;

-- Формула: max_age - (min_age / max_age), как дробное число
SELECT
    city,
    ROUND(
        MAX(age) - MIN(age)::numeric / MAX(age),
        2
    ) AS formula,
    ROUND(AVG(age), 2) AS average
FROM   users
GROUP BY city
ORDER BY city;