SQL VIEW и временные таблицы: где хранить промежуточный расчёт

Чем различаются CTE, VIEW, временная таблица и материализованное представление?

Все четыре конструкции дают промежуточному набору строк имя. Отличают их три вещи: хранятся ли строки, когда они пересчитываются и кто их видит. CTE здесь — точка отсчёта, он разобран в статье о WITH. Два последних столбца и строка о материализованном представлении описывают PostgreSQL: в DuckDB нет ни ролей с правами, ни материализованных представлений.

Как создать представление в SQL?

Команда — CREATE VIEW имя AS запрос. Ниже представление «дневные метрики»: одна строка на день, DAU из событий и выручка из оплат. Тот же расчёт собран одним запросом в статье о витринах, и числа сходятся.

В блоке стоит CREATE OR REPLACE TEMP VIEW. OR REPLACE позволяет запускать блок повторно: определение просто заменится. TEMP делает представление временным: оно исчезнет вместе с сессией, и ему хватает права TEMPORARY. Без TEMP представление останется в схеме, пока его не удалят, и потребует права CREATE в схеме.

В представлении 91 строка, по одной на день с 1 июня по 30 августа. Последний день неполный: события обрываются в 12:59, DAU за 30 августа — 223, поэтому запросы ниже берут дни по 29-е. В DuckDB команды те же.

CREATE OR REPLACE TEMP VIEW demo_daily_metrics AS
WITH activity AS (
  SELECT CAST(event_time AS date) AS day, count(DISTINCT user_id) AS dau
  FROM events
  GROUP BY CAST(event_time AS date)
),
revenue AS (
  SELECT paid_at AS day, sum(amount) AS revenue
  FROM payments
  GROUP BY paid_at
)
SELECT a.day, a.dau, coalesce(r.revenue, 0) AS revenue
FROM activity AS a
LEFT JOIN revenue AS r ON r.day = a.day;

SELECT day, dau, revenue
FROM demo_daily_metrics
WHERE day >= DATE '2026-08-24' AND day < DATE '2026-08-30'
ORDER BY day;

Что происходит при запросе к представлению?

База подставляет текст представления в ваш запрос и планирует всё вместе. Поставьте перед запросом выше EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF): в плане нет узла с именем demo_daily_metrics, там стоят Seq Scan on events и Seq Scan on payments. PostgreSQL прочитал все 35 341 событие; условие по дням он перенёс внутрь представления, и его прошли 3 788 строк. Как читать план — в разборе EXPLAIN ANALYZE.

Следствий два: представление всегда показывает текущие данные и ничего не ускоряет — запрос к нему стоит столько же, сколько его определение.

Стоимость прячется, когда представления строят друг на друге. demo_weekly_metrics сворачивает дневные метрики в недели. Чтобы поставить рядом выручку прошлой недели, его соединяют с самим собой — и в плане появляются два Seq Scan on events и два Seq Scan on payments: имя написано в запросе дважды, и база дважды прошла по обеим таблицам. Имя demo_daily_metrics в таком плане остаётся подписью узла Subquery Scan, но под ним то же чтение таблиц.

Второй запрос блока считает то же через lag(): те же четыре строки, каждая таблица прочитана один раз. Строки совпадают, потому что 13 недель идут без пропусков: lag() берёт предыдущую строку, а не неделю семью днями раньше. Третий способ — CTE поверх представления, WITH weeks AS (SELECT * FROM demo_weekly_metrics): если на CTE ссылаются дважды, PostgreSQL считает его один раз.

На 35 тысячах строк это миллисекунды. На большой таблице событий самосоединение удвоит чтение, а увидеть это можно только в плане: в тексте запроса исходных таблиц нет. В неделе с 24 августа шесть дней: неполное 30-е исключено в определении.

-- то же определение, что в первом блоке: так блок выполнится и в новом подключении
CREATE OR REPLACE TEMP VIEW demo_daily_metrics AS
WITH activity AS (
  SELECT CAST(event_time AS date) AS day, count(DISTINCT user_id) AS dau
  FROM events
  GROUP BY CAST(event_time AS date)
),
revenue AS (
  SELECT paid_at AS day, sum(amount) AS revenue
  FROM payments
  GROUP BY paid_at
)
SELECT a.day, a.dau, coalesce(r.revenue, 0) AS revenue
FROM activity AS a
LEFT JOIN revenue AS r ON r.day = a.day;

CREATE OR REPLACE TEMP VIEW demo_weekly_metrics AS
SELECT CAST(date_trunc('week', day) AS date) AS week_start,
       count(*) AS days,
       round(avg(dau), 1) AS avg_dau,
       sum(revenue) AS revenue
FROM demo_daily_metrics
WHERE day < DATE '2026-08-30'  -- учебная база кончается 30 августа; в работе здесь current_date
GROUP BY CAST(date_trunc('week', day) AS date);

-- самосоединение: имя demo_weekly_metrics написано дважды
SELECT cur.week_start, cur.days, cur.avg_dau, cur.revenue,
       prev.revenue AS prev_revenue
FROM demo_weekly_metrics AS cur
LEFT JOIN demo_weekly_metrics AS prev
  ON prev.week_start = cur.week_start - 7
WHERE cur.week_start >= DATE '2026-08-03'
ORDER BY cur.week_start;

-- те же строки через lag(): одно чтение каждой таблицы
SELECT week_start, days, avg_dau, revenue, prev_revenue
FROM (
  SELECT week_start, days, avg_dau, revenue,
         lag(revenue) OVER (ORDER BY week_start) AS prev_revenue
  FROM demo_weekly_metrics
) AS weeks
WHERE week_start >= DATE '2026-08-03'
ORDER BY week_start;

Как создать временную таблицу из запроса?

Временная таблица — обычная таблица, которую база удалит сама, когда сессия закроется. Чаще всего её создают сразу из запроса: CREATE TEMP TABLE имя AS SELECT …. Запись одинакова в PostgreSQL и DuckDB; типы столбцов и CREATE TABLE AS разобраны в статье о CREATE TABLE.

Пример — зрелые когорты регистраций. Первая оплата в учебной базе приходит через 3–20 дней после регистрации, оплаты записаны по 30 августа, поэтому конверсию в оплату можно считать у пришедших не позже 9 августа. Нижняя граница — 1 июля: в этот день запущен paid_search, и раньше каналы сравнивать не с чем. В таблице 2 165 строк, по одной на пользователя, с датой первой оплаты или NULL.

DROP TABLE IF EXISTS перед созданием нужен для повторного запуска в той же сессии. В DuckDB то же пишется короче, CREATE OR REPLACE TEMP TABLE; PostgreSQL 14 отвечает на такую запись синтаксической ошибкой. Зачем после заполнения стоит ANALYZE — в разделе о ловушках.

DROP TABLE IF EXISTS demo_signup_cohort;

CREATE TEMP TABLE demo_signup_cohort AS
SELECT u.user_id, u.channel, u.signup_date, min(p.paid_at) AS first_paid_at
FROM users AS u
LEFT JOIN payments AS p ON p.user_id = u.user_id
WHERE u.signup_date >= DATE '2026-07-01'
  AND u.signup_date <  DATE '2026-08-10'
GROUP BY u.user_id, u.channel, u.signup_date;

ANALYZE demo_signup_cohort;

SELECT channel,
       count(*) AS users,
       count(first_paid_at) AS payers,
       round(100.0 * count(first_paid_at) / count(*), 1) AS paid_pct
FROM demo_signup_cohort
GROUP BY channel
ORDER BY channel;

Что даёт временная таблица при втором запросе?

Смысл временной таблицы — во втором и третьем запросе. Соединение users с payments и группировка уже выполнены, следующий вопрос читает 2 165 готовых строк. Например, как быстро когорта платит: за первые 7 дней оплатили 127 человек (5,9%), за 14 дней — 318 (14,7%), за все 20 — 474 (21,9%).

В блоке таблица создаётся с IF NOT EXISTS: в той же сессии команда ничего не сделает. В новом подключении временной таблицы нет, и запрос к ней упал бы с relation "demo_signup_cohort" does not exist; так же её потеряет инструмент, который открывает соединение на каждый запрос. Обратная сторона: IF NOT EXISTS не обновит таблицу, если под этим именем остались старые данные.

В ноутбуке ту же роль играет DataFrame в переменной: посчитан один раз, живёт, пока работает ядро, виден только вам.

CREATE TEMP TABLE IF NOT EXISTS demo_signup_cohort AS
SELECT u.user_id, u.channel, u.signup_date, min(p.paid_at) AS first_paid_at
FROM users AS u
LEFT JOIN payments AS p ON p.user_id = u.user_id
WHERE u.signup_date >= DATE '2026-07-01'
  AND u.signup_date <  DATE '2026-08-10'
GROUP BY u.user_id, u.channel, u.signup_date;

SELECT count(*) AS users,
       count(*) FILTER (WHERE first_paid_at - signup_date <= 7)  AS paid_d7,
       count(*) FILTER (WHERE first_paid_at - signup_date <= 14) AS paid_d14,
       count(first_paid_at) AS paid_d20
FROM demo_signup_cohort;

-- 2165 | 127 | 318 | 474

Как работает материализованное представление и REFRESH?

Материализованное представление — запрос с именем, результат которого база сохранила на диск. Читается оно как таблица, на него можно создать индекс, но данные в нём — на момент создания или последнего REFRESH MATERIALIZED VIEW. Эта команда выполняет запрос заново, целиком. Обычный REFRESH на время пересчёта может блокировать чтение представления; REFRESH MATERIALIZED VIEW CONCURRENTLY читателей не блокирует, но требует уникального индекса.

Блок создаёт копию оплат без последнего дня, строит по ней выручку августа и дозагружает 30 августа. Копия — постоянная таблица: поверх временной PostgreSQL материализованное представление не создаст и ответит «materialized views must not use temporary tables or views». Поэтому блоку нужно право CREATE в схеме. Он идёт в транзакции и кончается ROLLBACK, так что в базе ничего не остаётся.

В таблице за август уже 642 оплаты на 15 788 $, а представление до REFRESH показывает 613 и 15 067 $. Не хватает 29 оплат и 721 $ — 4,6% выручки месяца. Ошибки при этом нет: запрос отработал и вернул число. Поэтому рядом с материализованным представлением нужен ответ на вопрос «когда его обновляли»: расписание после загрузки данных или столбец со временем расчёта.

В других СУБД устроено иначе.

BEGIN;

-- копия оплат без последнего дня: состояние «на вчера»
CREATE TABLE demo_payments AS
SELECT paid_at, amount FROM payments WHERE paid_at < DATE '2026-08-30';

CREATE MATERIALIZED VIEW demo_august_revenue AS
SELECT count(*) AS payments, sum(amount) AS revenue
FROM demo_payments
WHERE paid_at >= DATE '2026-08-01';

-- в таблицу пришли оплаты за 30 августа
INSERT INTO demo_payments
SELECT paid_at, amount FROM payments WHERE paid_at = DATE '2026-08-30';

SELECT 1 AS step, 'таблица после загрузки' AS source,
       count(*) AS payments, sum(amount) AS revenue
FROM demo_payments
WHERE paid_at >= DATE '2026-08-01'
UNION ALL
SELECT 2, 'представление до REFRESH', payments, revenue
FROM demo_august_revenue
ORDER BY step;
-- 1 | таблица после загрузки   | 642 | 15788
-- 2 | представление до REFRESH | 613 | 15067

REFRESH MATERIALIZED VIEW demo_august_revenue;

SELECT payments, revenue FROM demo_august_revenue;
-- 642 | 15788

ROLLBACK;  -- таблица и представление исчезают

SELECT count(*) AS objects_left
FROM pg_class
WHERE relname IN ('demo_payments', 'demo_august_revenue');
-- 0

• DuckDB: материализованных представлений нет, CREATE MATERIALIZED VIEW даёт синтаксическую ошибку. Замена — CREATE OR REPLACE TABLE … AS, которую перезапускают по расписанию.
• MySQL: в FAQ документации на вопрос «Does MySQL have materialized views?» стоит ответ «No».
• SQL Server: аналог — индексированное представление. По документации оно хранится как таблица с кластерным индексом и обновляется при каждом изменении исходных таблиц.
• ClickHouse: инкрементальное материализованное представление, по документации, — триггер на вставку: запрос выполняется над каждым вставленным блоком данных, вся таблица не пересчитывается. Есть и обновляемые представления (refreshable): они выполняют запрос по всем данным по расписанию.

Что сломается, если в представлении стоит SELECT *?

SELECT * в представлении раскрывается в список столбцов один раз, при создании. Блок ниже делает таблицу с двумя столбцами, представление SELECT * над ней и добавляет третий столбец: в таблице их теперь три, в представлении — два.

Дальше движки расходятся. PostgreSQL на SELECT * FROM demo_src_view молча вернёт два столбца: country в представление не попал, и документация CREATE VIEW говорит об этом прямо. DuckDB 1.4 на любом запросе к этому представлению, даже SELECT count(*), падает: «Contents of view were altered: types don't match!» — пока представление не пересоздадут.

С удалением тоже по-разному. PostgreSQL следит за зависимостями и не даст убрать столбец, на который ссылается представление: «cannot drop column channel of table demo_src because other objects depend on it». DuckDB удалит и столбец, и всю таблицу, а представление сломается на следующем запросе.

У CREATE OR REPLACE VIEW в PostgreSQL есть ограничение: новый запрос может только дописать столбцы в конец. На попытку убрать столбец он ответит «cannot drop columns from view» — нужен DROP VIEW и новое создание. DuckDB заменит определение на любое. В обоих движках надёжнее перечислять столбцы явно.

DROP VIEW IF EXISTS demo_src_view;
DROP TABLE IF EXISTS demo_src;

CREATE TEMP TABLE demo_src AS
SELECT user_id, channel FROM users;

CREATE TEMP VIEW demo_src_view AS
SELECT * FROM demo_src;

ALTER TABLE demo_src ADD COLUMN country text;

SELECT
  (SELECT count(*) FROM information_schema.columns
   WHERE table_name = 'demo_src')      AS table_columns,
  (SELECT count(*) FROM information_schema.columns
   WHERE table_name = 'demo_src_view') AS view_columns;

-- 3 | 2

Какие ловушки у временных таблиц?

Первая — имя. Временная таблица может называться так же, как постоянная, и тогда перекрывает её: временные объекты стоят в пути поиска первыми. Блок создаёт временную users из одного канала, и SELECT count(*) FROM users возвращает 1 015 вместо 4 613; полное имя public.users по-прежнему даёт 4 613. Таблица создана с ON COMMIT DROP, поэтому подмена не переживёт транзакцию. В DuckDB перекрытие работает так же, а к постоянной таблице ведёт полное имя — memory.main.users для базы в памяти. Запрос не падает и возвращает правдоподобное число, поэтому префикс tmp_ или demo_ — дешёвая страховка.

Вторая — статистика. Автоочистка PostgreSQL (autovacuum) не видит временных таблиц; документация советует запускать ANALYZE вручную после заполнения. Создайте demo_signup_cohort без строки ANALYZE и выполните EXPLAIN SELECT * FROM demo_signup_cohort WHERE channel = 'paid_search'. Без статистики планировщик угадывает: в PostgreSQL 14 со стандартными настройками он оценил таблицу в 1 582 строки, а фильтр — в 8, одну двухсотую, как если бы в столбце было 200 разных значений. Настоящие числа — 2 165 и 621. На другой версии первая оценка может отличаться, порядок ошибки — нет. Здесь ошибка безвредна, но в запросе с большой таблицей по этой оценке планировщик выбирает способ соединения.

Третья — индексы: на временной таблице их нет, пока вы не создадите их сами, — см. как устроены индексы.

BEGIN;

CREATE TEMP TABLE users ON COMMIT DROP AS
SELECT * FROM users WHERE channel = 'paid_search';

SELECT (SELECT count(*) FROM users)        AS by_name,
       (SELECT count(*) FROM public.users) AS with_schema;
-- 1015 | 4613

COMMIT;  -- ON COMMIT DROP: временная users удалена вместе с транзакцией

SELECT count(*) AS after_commit FROM users;
-- 4613

Что из этого может создать аналитик с доступом только на чтение?

Право TEMPORARY на базу в PostgreSQL по умолчанию есть у всех ролей, поэтому временные таблицы и временные представления обычно доступны и тому, кому выдали только SELECT. Для обычного и материализованного представления нужно право CREATE в схеме, а в базах, созданных на 15-й версии и новее, в схеме public его по умолчанию нет ни у кого, кроме владельца базы.

Временная таблица не получится в двух случаях: администратор отозвал TEMPORARY или вам выдали доступ к реплике (hot standby), где запрещён любой CREATE, включая временные таблицы. То же в транзакции только для чтения: «cannot execute CREATE TABLE AS in a read-only transaction». Остаются CTE, выгрузка в pandas или локальный DuckDB.

У прав есть полезная сторона. По умолчанию представление читает таблицы с правами своего владельца, а не того, кто к нему обращается. Инженер может выдать аналитику SELECT на представление с агрегатами и не открывать таблицу с персональными данными. В PostgreSQL 14 REFRESH MATERIALIZED VIEW выполняет только владелец; с 17-й версии, по документации, это право выдают отдельно — привилегией MAINTAIN.

Что выбрать в рабочей ситуации?

Выбор определяют три вопроса: сколько раз нужен результат, кому он нужен и насколько свежим должен быть.

Когда пересчитывать всё целиком становится долго и хочется дописывать только новые дни, материализованное представление перестаёт подходить: REFRESH считает запрос заново. Следующий шаг — таблица-витрина, которую пополняют через INSERT … SELECT.

Частые вопросы

Можно ли изменить данные через представление? В PostgreSQL через простое — одна таблица, без GROUP BY, DISTINCT и WITH — можно: UPDATE уйдёт в исходную таблицу. Через demo_daily_metrics нельзя: «cannot update view … Views containing WITH are not automatically updatable». DuckDB не обновляет через представления совсем: «Can only update base table!».

Как посмотреть запрос, который стоит за представлением? В PostgreSQL — SELECT pg_get_viewdef('demo_weekly_metrics'::regclass). В DuckDB — SELECT sql FROM duckdb_views() WHERE view_name = 'demo_weekly_metrics'.

Как создать временную таблицу в SQL Server и MySQL? В SQL Server временной её делает имя: #name видна только текущей сессии, ##name — всем сессиям, обе лежат в tempdb. В MySQL пишут CREATE TEMPORARY TABLE, и по документации она тоже скрывает постоянную таблицу с тем же именем.

Когда удаляется временная таблица? При закрытии сессии или раньше, по DROP TABLE. В PostgreSQL срок можно сократить до транзакции: CREATE TEMP TABLE … ON COMMIT DROP. Это работает только внутри BEGIN … COMMIT: без открытой транзакции таблица исчезнет сразу после создания. DuckDB 1.4 такую запись принимает, но таблицу не удаляет.

Как убрать за собой и что читать дальше?

Временные объекты исчезнут вместе с сессией; блок ниже убирает их раньше. Порядок удаления важен: в PostgreSQL DROP VIEW demo_daily_metrics при живом demo_weekly_metrics отвечает «cannot drop view demo_daily_metrics because other objects depend on it». Сначала удаляют верхнее представление; CASCADE снимет ограничение, но удалит всё зависимое разом.

Песочница симулятора принимает только SELECT и WITH. Запросы из определений в ней выполняются: оберните тело представления в WITH demo_daily_metrics AS (…) и получите те же числа. Сами CREATE выполняйте в своём PostgreSQL или DuckDB на своих таблицах: поведение будет тем же, числа — вашими.

Проверьте себя в песочнице: запишите запрос зрелых когорт как WITH signup_cohort AS (…) и посчитайте по каналам, сколько человек оплатили за первые 7 дней. Должно получиться: organic 47 из 780 (6,0%), paid_search 19 из 621 (3,1%), partner 18 из 315 (5,7%), referral 43 из 449 (9,6%) — в сумме те же 127.

DROP VIEW IF EXISTS demo_weekly_metrics;
DROP VIEW IF EXISTS demo_daily_metrics;
DROP VIEW IF EXISTS demo_src_view;
DROP TABLE IF EXISTS demo_src;
DROP TABLE IF EXISTS demo_signup_cohort;

SELECT count(*) AS objects_left
FROM information_schema.tables
WHERE table_name IN ('demo_weekly_metrics', 'demo_daily_metrics',
                     'demo_src_view', 'demo_src', 'demo_signup_cohort');

-- 0