Что такое временная таблица и как она используется?

Временные таблицы в SQL

Определение

Временная таблица (Temporary Table) — это таблица, которая существует только на время сеанса БД или транзакции, а затем автоматически удаляется. Используются для хранения промежуточных данных при обработке сложных запросов.

Основные характеристики

• Видимость: доступна только в текущем сеансе (в PostgreSQL) или только текущему пользователю (в SQL Server)
• Жизненный цикл: удаляется при завершении сеанса
• Производительность: быстрее обычных таблиц (в памяти или на временном диске)
• Структура: может иметь индексы, как обычная таблица

Синтаксис в разных СУБД

PostgreSQL

CREATE TEMP TABLE temp_sales AS
SELECT 
    product_id,
    SUM(amount) as total_sales,
    COUNT(*) as sale_count
FROM sales
WHERE year = 2024
GROUP BY product_id;

-- Временная таблица автоматически удалится при закрытии сеанса

SQL Server

CREATE TABLE #temp_sales (
    product_id INT,
    total_sales DECIMAL(10, 2),
    sale_count INT
);

INSERT INTO #temp_sales
SELECT 
    product_id,
    SUM(amount),
    COUNT(*)
FROM sales
GROUP BY product_id;

-- # означает локальную временную таблицу
-- ## означает глобальную временную таблицу

MySQL

CREATE TEMPORARY TABLE temp_users AS
SELECT user_id, email, registration_date
FROM users
WHERE status = 'active';

Типы временных таблиц

1. Локальные временные таблицы

• Видны только в текущем сеансе
• Автоматически удаляются при закрытии соединения

2. Глобальные временные таблицы (SQL Server)

• Видны всем сеансам
• Удаляются, когда последний сеанс закрывается

3. Таблицы в памяти (In-Memory)

• Хранятся в оперативной памяти для максимальной скорости
• Используются в MySQL: ENGINE=MEMORY

Когда использовать временные таблицы

1. Разбиение сложного запроса на этапы

-- Промежуточный результат: активные пользователи
CREATE TEMP TABLE active_users AS
SELECT user_id, email
FROM users
WHERE last_login > NOW() - INTERVAL 30 DAY;

-- Промежуточный результат: их последние покупки
CREATE TEMP TABLE recent_purchases AS
SELECT u.user_id, p.product_id, p.purchase_date
FROM active_users u
JOIN purchases p ON u.user_id = p.user_id
WHERE p.purchase_date > NOW() - INTERVAL 7 DAY;

-- Финальный результат с анализом
SELECT 
    u.user_id,
    COUNT(DISTINCT rp.product_id) as products_bought,
    MAX(rp.purchase_date) as last_purchase
FROM active_users u
LEFT JOIN recent_purchases rp ON u.user_id = rp.user_id
GROUP BY u.user_id;

2. Работа с большими наборами данных

-- Загрузить данные один раз, использовать многократно
CREATE TEMP TABLE large_dataset AS
SELECT * FROM fact_sales
WHERE year = 2024
AND status = 'completed';

-- Теперь можем несколько раз запрашивать быстро
SELECT dimension, COUNT(*) FROM large_dataset GROUP BY dimension;
SELECT SUM(amount) FROM large_dataset WHERE category = 'X';

3. Подготовка данных перед импортом

-- Валидация и очистка данных
CREATE TEMP TABLE staging_data AS
SELECT 
    TRIM(name) as clean_name,
    LOWER(email) as clean_email,
    CAST(age AS INTEGER) as age
FROM raw_import_data
WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'
AND age IS NOT NULL;

-- Проверка уникальности
SELECT clean_email, COUNT(*) 
FROM staging_data 
GROUP BY clean_email 
HAVING COUNT(*) > 1;

4. Рекурсивные запросы (CTE вместо временных таблиц)

WITH RECURSIVE category_tree AS (
    -- Базовый случай
    SELECT category_id, parent_id, name, 1 as level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Рекурсивный случай
    SELECT c.category_id, c.parent_id, c.name, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree ORDER BY level, name;

Преимущества и недостатки

Преимущества

• Лучше читаемость кода (разбиение на логические этапы)
• Повторное использование промежуточных результатов
• Автоматическое удаление (не нужна очистка)
• Быстрее обычных таблиц

Недостатки

• Требуют дополнительную память
• Не существуют после закрытия сеанса
• Усложняют отладку в некоторых случаях

Альтернатива: Common Table Expressions (CTE)

Вместо временных таблиц часто лучше использовать CTE (WITH clauses):

WITH active_users AS (
    SELECT user_id FROM users WHERE status = 'active'
),
user_purchases AS (
    SELECT au.user_id, p.amount
    FROM active_users au
    JOIN purchases p ON au.user_id = p.user_id
)
SELECT user_id, SUM(amount) as total
FROM user_purchases
GROUP BY user_id;

CTE часто предпочтительнее, так как:

• Автоматически оптимизируются планировщиком
• Более читаемы
• Не требуют явного удаления

Выводы

Временные таблицы — это инструмент для управления промежуточными данными в сложных запросах. Выбирайте между временными таблицами и CTE в зависимости от сложности и требований к производительности.