临时表 #

临时表特点
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   生命周期:                                                 │
│   ├── 会话级别: 会话结束自动删除                           │
│   └── 事务级别: 事务结束自动删除 (ON COMMIT DROP)          │
│                                                             │
│   可见性:                                                   │
│   ├── 只对创建它的会话可见                                 │
│   └── 不同会话的同名临时表互不影响                         │
│                                                             │
│   存储位置:                                                 │
│   ├── 存储在临时区域                                       │
│   └── 不持久化到磁盘                                       │
│                                                             │
│   使用场景:                                                 │
│   ├── 中间计算结果                                         │
│   ├── 批量数据处理                                         │
│   ├── 复杂查询优化                                         │
│   └── 临时数据存储                                         │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 创建临时表
CREATE TEMP TABLE temp_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 创建临时表并填充数据
CREATE TEMP TABLE temp_orders AS
SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';

-- 创建临时表不带数据
CREATE TEMP TABLE temp_products AS
SELECT * FROM products
WITH NO DATA;

-- 创建临时表并指定列
CREATE TEMP TABLE temp_summary AS
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY user_id;

-- 会话级临时表 (默认)
CREATE TEMP TABLE session_temp (
    id INT PRIMARY KEY,
    data VARCHAR(100)
);

-- 事务级临时表
CREATE TEMP TABLE transaction_temp (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ON COMMIT DELETE ROWS;  -- 事务结束删除数据

CREATE TEMP TABLE transaction_temp2 (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ON COMMIT DROP;  -- 事务结束删除表

-- 临时表可以与普通表同名
-- 查询时优先使用临时表

CREATE TABLE users (
    id UUID PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TEMP TABLE users (
    id UUID PRIMARY KEY,
    name VARCHAR(100),
    temp_flag BOOLEAN DEFAULT TRUE
);

SELECT * FROM users;  -- 查询临时表
SELECT * FROM public.users;  -- 查询普通表

-- 创建临时表存储中间结果
CREATE TEMP TABLE temp_high_value_orders AS
SELECT * FROM orders
WHERE amount > 10000;

-- 使用临时表进行进一步处理
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM temp_high_value_orders
GROUP BY user_id
ORDER BY total_amount DESC;

-- 复杂查询优化
CREATE TEMP TABLE temp_user_stats AS
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders
GROUP BY user_id;

SELECT 
    u.name,
    tus.order_count,
    tus.total_amount
FROM users u
JOIN temp_user_stats tus ON u.id = tus.user_id
WHERE tus.order_count > 10;

-- 批量导入临时表
CREATE TEMP TABLE temp_import (
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

-- 导入数据
INSERT INTO temp_import (name, email, age) VALUES
    ('Alice', 'alice@example.com', 25),
    ('Bob', 'bob@example.com', 30),
    ('Carol', 'carol@example.com', 28);

-- 处理后导入正式表
INSERT INTO users (name, email, age)
SELECT name, email, age
FROM temp_import
WHERE email LIKE '%@example.com';

-- 更新现有数据
UPDATE users u
SET age = ti.age
FROM temp_import ti
WHERE u.email = ti.email;

-- 多步骤计算
-- 步骤1: 创建基础数据临时表
CREATE TEMP TABLE temp_daily_sales AS
SELECT 
    DATE(created_at) AS sale_date,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
WHERE created_at >= DATE_TRUNC('month', NOW())
GROUP BY DATE(created_at);

-- 步骤2: 创建汇总临时表
CREATE TEMP TABLE temp_monthly_summary AS
SELECT 
    SUM(order_count) AS total_orders,
    SUM(total_amount) AS total_revenue,
    AVG(order_count) AS avg_daily_orders,
    AVG(total_amount) AS avg_daily_revenue
FROM temp_daily_sales;

-- 步骤3: 最终查询
SELECT 
    sale_date,
    order_count,
    total_amount,
    (SELECT total_revenue FROM temp_monthly_summary) AS month_total,
    total_amount / (SELECT total_revenue FROM temp_monthly_summary) * 100 AS pct
FROM temp_daily_sales
ORDER BY sale_date;

-- 查看当前会话的临时表
SELECT 
    table_schema,
    table_name,
    table_type
FROM information_schema.tables
WHERE table_schema LIKE 'pg_temp%';

-- 查看临时表结构
DESCRIBE temp_users;

-- 查看临时表数据
SELECT * FROM temp_users;

-- 手动删除临时表
DROP TABLE temp_users;

-- 删除临时表 (如果存在)
DROP TABLE IF EXISTS temp_users;

-- 截断临时表
TRUNCATE TABLE temp_users;

-- 为临时表创建索引
CREATE TEMP TABLE temp_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 创建索引
CREATE INDEX idx_temp_user ON temp_orders(user_id);
CREATE INDEX idx_temp_created ON temp_orders(created_at);

-- 查看索引
SHOW INDEX FROM temp_orders;

临时表 vs CTE
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   临时表:                                                   │
│   ├── 可以创建索引                                         │
│   ├── 可以多次引用                                         │
│   ├── 可以跨多个查询使用                                   │
│   ├── 适合大数据量                                         │
│   └── 需要显式创建和删除                                   │
│                                                             │
│   CTE:                                                      │
│   ├── 不能创建索引                                         │
│   ├── 只能在定义的查询中使用                               │
│   ├── 单次查询使用                                         │
│   ├── 适合小数据量                                         │
│   └── 自动管理生命周期                                     │
│                                                             │
│   选择建议:                                                 │
│   ├── 简单查询: CTE                                        │
│   ├── 复杂多步骤: 临时表                                   │
│   ├── 需要索引: 临时表                                     │
│   └── 大数据量: 临时表                                     │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 使用 CTE
WITH user_orders AS (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders
    GROUP BY user_id
),
high_value_users AS (
    SELECT * FROM user_orders WHERE cnt > 10
)
SELECT u.name, hu.cnt
FROM users u
JOIN high_value_users hu ON u.id = hu.user_id;

-- 使用临时表
CREATE TEMP TABLE temp_user_orders AS
SELECT user_id, COUNT(*) AS cnt
FROM orders
GROUP BY user_id;

CREATE INDEX idx_temp_cnt ON temp_user_orders(cnt);

SELECT u.name, tuo.cnt
FROM users u
JOIN temp_user_orders tuo ON u.id = tuo.user_id
WHERE tuo.cnt > 10;

DROP TABLE temp_user_orders;

临时表使用建议
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   1. 命名规范                                               │
│      ├── 使用 temp_ 前缀                                   │
│      └── 名称清晰表达用途                                  │
│                                                             │
│   2. 及时清理                                               │
│      ├── 不再使用时删除                                    │
│      └── 避免占用资源                                      │
│                                                             │
│   3. 合理使用索引                                           │
│      ├── 大数据量时创建索引                                │
│      └── 提高查询性能                                      │
│                                                             │
│   4. 选择合适类型                                           │
│      ├── 简单查询用 CTE                                    │
│      └── 复杂处理用临时表                                  │
│                                                             │
│   5. 注意事务范围                                           │
│      ├── ON COMMIT 选项                                    │
│      └── 确保数据一致性                                    │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 完整示例: 数据处理流程
-- 1. 创建临时表
CREATE TEMP TABLE temp_data AS
SELECT * FROM raw_data
WHERE processed = false;

-- 2. 创建索引
CREATE INDEX idx_temp_id ON temp_data(id);

-- 3. 处理数据
CREATE TEMP TABLE temp_processed AS
SELECT 
    id,
    UPPER(name) AS name,
    LOWER(email) AS email,
    CURRENT_TIMESTAMP AS processed_at
FROM temp_data;

-- 4. 导入结果
INSERT INTO processed_data
SELECT * FROM temp_processed;

-- 5. 更新原始数据
UPDATE raw_data
SET processed = true
WHERE id IN (SELECT id FROM temp_data);

-- 6. 清理临时表
DROP TABLE temp_data;
DROP TABLE temp_processed;