Собрать весь курс воедино: от спроектированной схемы до работающей аналитики
Вы — разработчик, которому поставили задачу: «сделай нам дашборд, чтобы видеть, что происходит». Дашборд — набор показателей, который руководитель открывает утром и понимает состояние дел.
Работа состоит из четырёх частей:
Разбор эталонной работы на учебной базе. Ваша задача — сделать аналогичное на своей предметной области, а не повторить эти запросы.
-- Сводка одной строкой: сколько всего чего в системе
SELECT (SELECT count(*) FROM users) AS users_total,
(SELECT count(*) FROM communities) AS communities_total,
(SELECT count(*) FROM posts) AS posts_total,
(SELECT count(*) FROM likes) AS likes_total,
(SELECT count(DISTINCT user_id) FROM likes) AS users_active;SELECT p.title,
c.name AS community,
p.score,
count(l.id) AS likes_count
FROM posts p
JOIN communities c ON c.id = p.community_id
LEFT JOIN likes l ON l.post_id = p.id
GROUP BY p.id, p.title, c.name, p.score
ORDER BY likes_count DESC, p.score DESC
LIMIT 5;Здесь соединение с likes одно, поэтому агрегат корректен. Группировка идёт по p.id — первичному ключу, что позволяет выводить остальные столбцы таблицы (раздел 10).
-- Два независимых счётчика — агрегируем ДО соединения
WITH likes_agg AS (
SELECT user_id, count(*) AS cnt FROM likes GROUP BY user_id
),
views_agg AS (
SELECT user_id, count(*) AS cnt,
count(DISTINCT community_id) AS communities
FROM views GROUP BY user_id
)
SELECT u.name,
u.city,
coalesce(l.cnt, 0) AS likes_count,
coalesce(v.cnt, 0) AS views_count,
coalesce(v.communities, 0) AS communities_visited,
CASE
WHEN coalesce(l.cnt, 0) = 0 THEN 'пассивный'
WHEN l.cnt <= 2 THEN 'обычный'
ELSE 'активный'
END AS segment
FROM users u
LEFT JOIN likes_agg l ON l.user_id = u.id
LEFT JOIN views_agg v ON v.user_id = u.id
ORDER BY likes_count DESC, u.name;-- Помесячная активность; generate_series даёт месяцы без пропусков
WITH months AS (
SELECT generate_series(
date_trunc('month', (SELECT min(liked_at) FROM likes)),
date_trunc('month', (SELECT max(liked_at) FROM likes)),
interval '1 month'
)::date AS month
),
monthly AS (
SELECT date_trunc('month', liked_at)::date AS month,
count(*) AS likes_count,
count(DISTINCT user_id) AS active_users
FROM likes
GROUP BY 1
)
SELECT m.month,
coalesce(x.likes_count, 0) AS likes_count,
coalesce(x.active_users, 0) AS active_users
FROM months m
LEFT JOIN monthly x ON x.month = m.month
ORDER BY m.month;generate_series решает частую проблему отчётов: месяц без данных просто исчезает из результата, и на графике образуется разрыв, который читается как «данных нет», а не как «активности не было». Сетка периодов строится отдельно, факты подтягиваются к ней через LEFT JOIN.-- Топ-2 поста в каждом сообществе
WITH ranked AS (
SELECT c.name AS community,
p.title,
p.score,
row_number() OVER (PARTITION BY c.id ORDER BY p.score DESC, p.id) AS rn,
round(avg(p.score) OVER (PARTITION BY c.id)) AS community_avg
FROM posts p
JOIN communities c ON c.id = p.community_id
)
SELECT community, title, score, community_avg
FROM ranked
WHERE rn <= 2
ORDER BY community, rn;GROUP BY. Это позволяет в одной строке показать и значение самой записи, и показатель по её группе. Задача «топ-N внутри каждой категории» без оконных функций решается заметно тяжелее. В программе курса эта тема не значится, поэтому здесь она даётся как дополнительная возможность.-- Каждый показатель — представление с говорящим именем
CREATE VIEW v_dashboard_totals AS ...;
CREATE VIEW v_dashboard_top_posts AS ...;
CREATE VIEW v_dashboard_user_activity AS ...;
-- Тяжёлый показатель — материализованное представление с обновлением по расписанию
CREATE MATERIALIZED VIEW mv_dashboard_monthly AS ...;
CREATE UNIQUE INDEX uq_mv_dashboard_monthly ON mv_dashboard_monthly (month);
-- Права: аналитик читает витрину, но не исходные таблицы
GRANT SELECT ON v_dashboard_totals TO app_readonly;Куда двигаться дальше: оконные функции и аналитический SQL, PL/pgSQL и триггеры (приложение А1), репликация и отказоустойчивость, партиционирование больших таблиц, углублённая оптимизация запросов, полнотекстовый поиск и работа с jsonb.