Перейти к основному содержимому

3.07. Представления SQL

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВНЕ ДЛЯ НОВИЧКОВВ РАЗРАБОТКЕ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Представления SQL​

О представлениях​

Порой бывает так, что для просмотра данных нам нужно несколько таблиц, мы, допустим, группируя, объединяя через JOIN, UNION, собираем, агрегируем и делаем каждый раз запрос. К примеру - начальству нужен отчёт, и мы выполняем запрос - но если такой запрос нужен каждый день, нам не обязательно выполнять такой запрос снова и снова - можно сохранить его как представление.

Представления (Views) – это виртуальные таблицы, которые не хранят данные, а только отображают результаты SQL запроса.

Представление – это сохранённый SQL-запрос, который ведёт себя как таблица:

  • не содержит данных (только определение запроса);
  • всегда показывает актуальные данные из базовых таблиц;
  • можно использовать в SELECT, JOIN, WHERE как обычную таблицу.

Это снимок данных, который автоматически обновляется при изменении исходных таблиц.

Это нужно, когда необходимо упростить сложные запросы, и можно скрыть сложную логику за простым именем представления. К примеру, вы написали сложный запрос с вычислением, функциями, джойнами и вывели результат таблицей. Сохранив это как представление с именем, можно будет потом просто обращаться к нему как к таблице, указывая поля из результата. Представления также позволяют дать пользователям доступ только к определённым столбцам или строкам, «скрывая» оригинальные таблицы. И не нужно каждый раз писать один и тот же сложный запрос, просто сохранив его. Если структура таблиц меняется, то можно оставить View со старым именем или структурой.

Синтаксис:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Пример, создание простого представления:

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE is_active = TRUE;

Теперь можно запрашивать:

SELECT * FROM active_users;

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

Материализованные представления​

В некоторых СУБД (PostgreSQL, Oracle) есть материализованные представления (Materialized Views), которые физически хранят данные, требуют явного обновления, но работают быстрее для сложных запросов:

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT product_id, SUM(amount) as total
FROM sales
GROUP BY product_id;

-- Обновление данных
REFRESH MATERIALIZED VIEW monthly_sales;

Обычное представление - это виртуальная таблица, которая не хранит данные, а каждый раз при запросе выполняет свой SQL-набор команд. Это удобно, когда скрывается сложность или обеспечивается безопасность. Но есть проблема, когда запрос внутри представления будет тяжелый (множество JOIN, агрегаций, подзапросов, миллионы строк) - тогда каждый вызов View будет медленным. Иногда просто не можем позволить себе пересчитывать сложную аналитику каждый раз, когда кто-то захочет посмотреть отчёт. Вот тут на помощь и приходит материализованное представление.

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

Давайте рассмотрим отличия:

ХарактеристикаОбычное представлениеМатериализованное представление
Хранит данные?Нет. Только определение запроса.Да. Данные физически сохранены на диске.
Скорость чтенияЗависит от сложности запроса (может быть медленной).Очень быстрая — как у таблицы с индексами.
АктуальностьВсегда актуальна (выполняет запрос при обращении).Устаревает со временем — нужно обновлять вручную.
Использование в SELECT, JOINДа, как обычная таблица.Да, как обычная таблица.
Поддержка индексовНет (индексы работают на базовых таблицах).Да. Можно создавать индексы на самом MV.
РесурсыЛёгкое, но нагрузка при каждом вызове.Требует дискового пространства и времени на обновление.

Материализованные представления решают одну главную задачу - ускорение сложных запросов за счёт предварительного расчёта. Это стоит использовать для следующих ситуаций:

  1. Аналитика и отчёты. Вы каждый день строите отчёт «Продажи по регионам за последние 6 месяцев с группировкой по категориям и агрегацией по среднему чеку». Запрос выполняется 8 секунд. Сделайте материализованное представление и обновляйте его раз в ночь, и отчёт будет грузиться за 0.1 секунды.
  2. Сложные JOIN’ы и агрегации. У вас 5 таблиц, связанных через JOIN, и вы каждый раз считаете SUM, AVG, и прочее. Вынесите это в MV. Добавьте индексы на ключевые поля.
  3. Интеграции и внешние системы. Внешняя система (например, BI-панель) вредит производительности, если каждый раз дергает сложный запрос. Подайте ей MV. Она читает быстро, а вы контролируете актуальность.
  4. Кеширование тяжёлых вычислений. Например, рекурсивные запросы (WITH RECURSIVE), работа с иерархиями (деревья, графы), сложные JSON-обработки. Результат можно материализовать и обновлять по расписанию.

Словом, если чувствуете, что работа скрипта медленная, попробуйте вариант с MV.

Разберём на PostgreSQL.

Для создания используется команда CREATE MATERIALIZED VIEW, затем нужно указать SELECT, FROM, JOIN и прочее - результат запроса будет представлять собой то самое представление:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    region,
    product_category,
    SUM(sales_amount) AS total_sales,
    AVG(order_value) AS avg_order,
    COUNT(*) AS order_count
FROM 
    sales 
    JOIN products ON sales.product_id = products.id
    JOIN regions ON sales.region_id = regions.id
GROUP BY 
    region, product_category;

После этого с такой sales_summary можно работать как с полноценной таблицей:

SELECT * FROM sales_summary WHERE region = 'Москва';

Поскольку MV — это настоящая таблица, вы можете ускорить её ещё сильнее:

CREATE INDEX idx_sales_summary_region ON sales_summary (region);
CREATE INDEX idx_sales_summary_sales ON sales_summary (total_sales DESC);

Теперь даже сложные WHERE, ORDER BY, JOIN будут работать мгновенно.

Чтобы обновлять данные, используется команда REFRESH MATERIALIZED VIEW. Поскольку данные в MV не обновляются автоматически, нужно вручную синхронизировать их с источником. CONCURRENTLY требует уникального индекса на MV (например, по первичному ключу или уникальному полю). Без него — ошибка.

-- Полное перестроение (блокирует чтение на время обновления)
REFRESH MATERIALIZED VIEW sales_summary;

-- Без блокировки (доступно для чтения во время обновления)
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Удаляется материализованное представление через команду DROP. CASCADE — удаляет зависимости, например, индексы и другие View, которые на него ссылаются:

DROP MATERIALIZED VIEW sales_summary;

-- Или, если хотите сохранить данные, но убрать MV:
DROP MATERIALIZED VIEW IF EXISTS sales_summary CASCADE;

Можно создавать триггеры на одну из базовых таблиц, которые будут вызывать REFRESH при изменении данных. К примеру, при любом изменении в таблице employees (вставка, обновление, удаление) MV автоматически будет обновляться. Для этого создаётся функция-обновление:

CREATE OR REPLACE FUNCTION refresh_employee_hierarchy()
RETURNS TRIGGER AS $$
BEGIN
    -- Обновляем MV без блокировки (если есть уникальный индекс)
    REFRESH MATERIALIZED VIEW CONCURRENTLY employee_hierarchy;
    RETURN NULL; -- Для триггеров AFTER не нужен возврат
END;
$$ LANGUAGE plpgsql;

А затем добавляется триггер на таблице:

CREATE TRIGGER trigger_refresh_employee_hierarchy
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH STATEMENT
EXECUTE FUNCTION refresh_employee_hierarchy();

В итоге при любом INSERT, UPDATE, DELETE в employees — employee_hierarchy автоматически обновится. Актуальность данных — почти в реальном времени. Но REFRESH — тяжёлая операция, особенно для больших MV. Если у вас высокая частота изменений — лучше обновлять по расписанию, а не на каждое изменение.

Другие виды представлений​

Групповое представление​

Групповое представление — это представление, которое содержит агрегатные функции (SUM, COUNT, AVG и т.д.) и/или предложение GROUP BY. Оно позволяет абстрагировать сложную логику агрегации и предоставлять упрощённый доступ к сводным данным.

Пример:

CREATE VIEW department_stats AS
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Такое представление может использоваться для аналитики:

SELECT * FROM department_stats WHERE avg_salary > 80000;

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

Ограничения представлений​

Представления имеют ряд ограничений, зависящих от СУБД, но общие принципы следующие:

  1. Нельзя напрямую модифицировать агрегированные данные. Представления с GROUP BY, DISTINCT, агрегатами нельзя обновлять.
  2. Ограничения на состав запроса. Наличие подзапросов в SELECT может сделать представление необновляемым.Использование UNION, LIMIT, HAVING часто исключает возможность обновления.
  3. Зависимость от базовой структуры. Если изменяется структура базовой таблицы (например, удаляется столбец), представление может перестать работать.
  4. Производительность. Представления не хранят данные физически (если только это не материализованные представления). Каждый вызов выполняет исходный запрос, что может быть медленно при сложной логике.
  5. Безопасность. Представления могут использоваться для ограничения доступа к данным (например, скрытие конфиденциальных столбцов), но сами по себе не заменяют полноценного управления правами.

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

Обновляемое представление — такое, через которое можно выполнять операции INSERT, UPDATE, DELETE, и изменения применяются к базовой таблице.

Условия, при которых представление считается обновляемым (в общем случае):

  • Не содержит GROUP BY, DISTINCT, агрегатных функций.
  • Не использует UNION, INTERSECT, EXCEPT.
  • Включает все обязательные столбцы базовой таблицы (например, NOT NULL без DEFAULT).
  • Основано на одной таблице или на соединении, допускающем однозначное сопоставление строк.

Пример:

CREATE VIEW active_employees AS
SELECT id, name, salary, department_id
FROM employees
WHERE status = 'active';

Теперь можно обновлять:

UPDATE active_employees SET salary = 90000 WHERE id = 101;

Изменение применится к строке в таблице employees.

Если представление основано на нескольких таблицах, оно может быть обновляемым только при условии, что изменение затрагивает столбцы одной таблицы, и СУБД может однозначно определить целевую строку.

Проверка представлений (WITH CHECK OPTION)​

Ключевая фраза WITH CHECK OPTION гарантирует, что любые изменения через представление не нарушают условия его фильтрации.

Пример:

CREATE VIEW senior_employees AS
SELECT id, name, salary, experience
FROM employees
WHERE experience >= 5
WITH CHECK OPTION;

Попытка:

INSERT INTO senior_employees (id, name, salary, experience) 
VALUES (200, 'Ivan', 70000, 3);

...будет отклонена, так как experience = 3 не удовлетворяет условию experience >= 5. Это обеспечивает целостность данных, особенно при использовании представлений как интерфейса ввода.

Исключаемые поля​

Под термином "исключаемые поля" в контексте представлений обычно понимаются столбцы базовой таблицы, которые намеренно не включены в представление. Это делается для:

  • Сокрытия чувствительных данных.
  • Упрощения интерфейса. Исключение технических полей (created_at, updated_by, version) от пользователей, которым они не нужны.
  • Оптимизации производительности. Исключение больших полей (BLOB, TEXT), если они не требуются в текущем контексте.

Пример:

CREATE VIEW public_employee_view AS
SELECT id, name, department_id
FROM employees; -- salary, email, phone скрыты

Важно: если представление исключает столбец, который является NOT NULL и не имеет значения по умолчанию, то вставка через это представление будет невозможна, если только представление не определено как обновляемое с явным указанием способа заполнения пропущенных полей (что зависит от СУБД).