视图与物化视图

-- 准备数据
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price NUMERIC(10,2),
    stock INTEGER,
    is_active BOOLEAN DEFAULT true
);

INSERT INTO products (name, category, price, stock, is_active) VALUES
    ('笔记本电脑', '电子产品', 5999, 50, true),
    ('手机', '电子产品', 3999, 200, true),
    ('耳机', '电子产品', 299, 500, true),
    ('运动鞋', '服饰', 599, 300, true),
    ('T恤', '服饰', 99, 100, false),
    ('咖啡机', '家居', 1299, 50, true);

-- 创建一个活跃产品视图
CREATE VIEW active_products AS
SELECT id, name, category, price, stock
FROM products
WHERE is_active = true;

-- 使用视图（就像使用表一样）
SELECT * FROM active_products WHERE category = '电子产品'
ORDER BY price DESC;

-- 创建常用分析视图
CREATE VIEW customer_order_summary AS
SELECT 
    c.customer_id,
    c.name,
    c.city,
    c.member_level,
    COUNT(o.order_id) AS 订单数,
    COALESCE(SUM(o.total_amount), 0) AS 总消费,
    COALESCE(AVG(o.total_amount), 0) AS 平均客单价,
    MAX(o.order_date) AS 最近订单日期,
    COALESCE(SUM(o.total_amount) FILTER (
        WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
    ), 0) AS 近30天消费
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = '已完成'
GROUP BY c.customer_id;

-- 使用起来非常简洁
SELECT * FROM customer_order_summary 
WHERE 总消费 > 5000
ORDER BY 总消费 DESC;

-- 只暴露部分列给特定用户
CREATE VIEW employee_public AS
SELECT name, email, department
FROM employees;

-- 不暴露工资等敏感信息
-- REVOKE ALL ON employees FROM some_user;
-- GRANT SELECT ON employee_public TO some_user;

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT 
    product,
    DATE_TRUNC('month', order_date) AS 月份,
    SUM(amount) AS 销售额,
    COUNT(*) AS 订单数,
    SUM(quantity) AS 销量
FROM big_orders
WHERE status = '已完成'
GROUP BY product, DATE_TRUNC('month', order_date)
ORDER BY 月份, 产品;

-- 查询物化视图（速度非常快）
SELECT * FROM mv_monthly_sales WHERE 月份 = '2025-02-01';

-- 完全刷新（阻塞读，重建全部数据）
REFRESH MATERIALIZED VIEW mv_monthly_sales;

-- 并发刷新（不阻塞读，但需要唯一索引）
CREATE UNIQUE INDEX idx_mv_monthly ON mv_monthly_sales (product, 月份);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

-- 创建简单视图
CREATE VIEW active_products_v AS
SELECT * FROM products WHERE is_active = true;

-- 可以直接更新（会操作底层表）
UPDATE active_products_v SET stock = stock + 10 WHERE id = 1;

-- 插入
INSERT INTO active_products_v (name, category, price, stock)
VALUES ('新商品', '测试', 100, 20);

CREATE OR REPLACE FUNCTION update_product_view()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO products (name, category, price, stock)
        VALUES (NEW.name, NEW.category, NEW.price, NEW.stock);
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE products 
        SET name = NEW.name, 
            category = NEW.category,
            price = NEW.price,
            stock = NEW.stock
        WHERE id = OLD.id;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM products WHERE id = OLD.id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tg_product_view
INSTEAD OF INSERT OR UPDATE OR DELETE ON active_products_v
FOR EACH ROW EXECUTE FUNCTION update_product_view();

-- 查看所有视图
\dv

-- 查看视图定义
\d+ active_products

-- 查看视图的 SQL 定义
SELECT definition FROM pg_views WHERE viewname = 'active_products';

-- 修改视图
CREATE OR REPLACE VIEW active_products AS
SELECT id, name, category, price, stock, created_at
FROM products
WHERE is_active = true;

-- 删除视图
DROP VIEW IF EXISTS active_products;

-- 删除物化视图
DROP MATERIALIZED VIEW IF EXISTS mv_monthly_sales;

-- 1. 日常销售报表（物化视图，每小时刷新）
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT 
    DATE_TRUNC('day', sale_date) AS 日期,
    category,
    product,
    SUM(amount) AS 销售额,
    COUNT(*) AS 订单数,
    SUM(quantity) AS 销量,
    COUNT(DISTINCT salesperson) AS 参与销售人数
FROM monthly_sales
GROUP BY 1, 2, 3
ORDER BY 日期, 销售额 DESC;

CREATE UNIQUE INDEX idx_mv_daily ON mv_daily_sales (日期, category, product);

-- 2. 客户分层视图
CREATE VIEW v_customer_rfm AS
SELECT 
    c.customer_id,
    c.name,
    c.member_level,
    COALESCE(MAX(o.order_date), '1970-01-01') AS last_order,
    COALESCE(COUNT(o.order_id), 0) AS frequency,
    COALESCE(SUM(o.total_amount), 0) AS monetary,
    CASE
        WHEN COALESCE(MAX(o.order_date), '1970-01-01') >= CURRENT_DATE - INTERVAL '30 days'
            AND COUNT(o.order_id) >= 3
            AND SUM(o.total_amount) >= 5000 THEN '高价值'
        WHEN COALESCE(MAX(o.order_date), '1970-01-01') >= CURRENT_DATE - INTERVAL '90 days'
            THEN '活跃'
        WHEN COALESCE(MAX(o.order_date), '1970-01-01') >= CURRENT_DATE - INTERVAL '180 days'
            THEN '沉默'
        ELSE '流失'
    END AS 分层
FROM customers c
LEFT JOIN order_items oi ON c.customer_id = oi.customer_id
LEFT JOIN orders o ON oi.order_id = o.order_id
GROUP BY c.customer_id;

-- 3. 使用视图做分析
SELECT 分层, COUNT(*) AS 客户数, ROUND(AVG(monetary), 2) AS 人均消费
FROM customer_rfm
GROUP BY 分层
ORDER BY 人均消费;