Модуль 05 — Представления: VIEW и MATERIALIZED VIEW

База данных — это не только таблицы. По аналогии с объектно-ориентированным программированием, в СУБД есть уровни абстракции. Архитектурный паттерн ANSI/SPARK делит объекты на три уровня:

-- Два представления: пользователи-женщины и пользователи-мужчины
CREATE VIEW v_users_female AS
    SELECT * FROM users
    WHERE  gender = 'female';

CREATE VIEW v_users_male AS
    SELECT * FROM users
    WHERE  gender = 'male';

-- Использование: выглядит как обычная таблица
SELECT name FROM v_users_female
UNION
SELECT name FROM v_users_male
ORDER BY name;

-- VIEW для генерации дат (январь 2026)
CREATE VIEW v_generated_dates AS
    SELECT gs::date AS generated_date
    FROM   generate_series(
        '2026-01-01'::date,
        '2026-01-31'::date,
        '1 day'
    ) gs
    ORDER BY generated_date;

-- Использование для поиска пропущенных дней
SELECT gd.generated_date AS missing_date
FROM   v_generated_dates gd
LEFT JOIN views vw ON vw.viewed_at = gd.generated_date
WHERE  vw.id IS NULL
ORDER BY missing_date;

-- VIEW: пользователь, заголовок поста, оригинальный score и score -10%
CREATE VIEW v_score_with_discount AS
    SELECT
        u.name,
        p.title,
        p.score,
        CAST(p.score * 0.9 AS integer) AS discounted_score
    FROM   likes l
    JOIN   users u ON u.id = l.user_id
    JOIN   posts p ON p.id = l.post_id
    ORDER BY u.name, p.title;

-- Проверяем
SELECT * FROM v_score_with_discount;

VIEW может инкапсулировать сложные операции. Пример: симметричная разность двух множеств — (R−S) ∪ (S−R).

-- Пользователи, просматривавшие ленту 2 января, но не 6 января, и наоборот
CREATE VIEW v_symmetric_union AS
(
    SELECT user_id FROM views WHERE viewed_at = '2026-01-02'
    EXCEPT
    SELECT user_id FROM views WHERE viewed_at = '2026-01-06'
)
UNION
(
    SELECT user_id FROM views WHERE viewed_at = '2026-01-06'
    EXCEPT
    SELECT user_id FROM views WHERE viewed_at = '2026-01-02'
);

Materialized View сохраняет результат запроса физически. Идеален для ресурсоёмких запросов, которые выполняются часто, а данные меняются редко.

-- Создать MV: сообщества, которые Dmitriy смотрел 8 января 2026
-- и в которых есть посты со score < 800
CREATE MATERIALIZED VIEW mv_dmitriy_views_and_likes AS
    SELECT c.name
    FROM   views vw
    JOIN   users       u ON u.id  = vw.user_id
    JOIN   communities c ON c.id  = vw.community_id
    JOIN   posts       p ON p.community_id = c.id
    WHERE  u.name       = 'Dmitriy'
      AND  vw.viewed_at = '2026-01-08'
      AND  p.score < 800
WITH DATA;  -- сразу наполнить данными

-- Добавим новый просмотр Дмитрия
INSERT INTO views (id, user_id, community_id, viewed_at)
SELECT
    (SELECT MAX(id)+1 FROM views),
    (SELECT id FROM users       WHERE name = 'Dmitriy'),
    (SELECT id FROM communities WHERE name = 'TravelDiaries'),
    '2026-01-08';

-- Данные в mv_dmitriy_views_and_likes УСТАРЕЛИ — нужно обновить
REFRESH MATERIALIZED VIEW mv_dmitriy_views_and_likes;

-- Проверяем
SELECT * FROM mv_dmitriy_views_and_likes;

-- Изменить существующий VIEW
CREATE OR REPLACE VIEW v_users_female AS
    SELECT id, name, age FROM users
    WHERE  gender = 'female';

-- Удалить VIEWs
DROP VIEW v_users_female;
DROP VIEW v_users_male;
DROP VIEW v_generated_dates;
DROP VIEW v_symmetric_union;
DROP VIEW v_score_with_discount;

-- Удалить MATERIALIZED VIEW
DROP MATERIALIZED VIEW mv_dmitriy_views_and_likes;

-- Каскадное удаление (удалит зависимые объекты)
DROP VIEW v_users_female CASCADE;