PostgreSQL 完全学习指南

# 使用 Homebrew
brew install postgresql@14

# 启动服务
brew services start postgresql@14

# 初始化数据库（如果首次安装）
initdb /usr/local/var/postgres

# 创建数据库
createdb mydb

# 连接
psql mydb

# 连接设置
listen_addresses = '*'
port = 5432
max_connections = 200

# 内存设置
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 1GB

# WAL设置
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB

# 日志设置
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_statement = 'all'

# 查询性能
random_page_cost = 1.1
effective_io_concurrency = 200

-- ROW_NUMBER、RANK、DENSE_RANK
SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
    RANK() OVER (ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

-- 分区内排名
SELECT 
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

-- 累计统计
SELECT 
    date,
    sales,
    SUM(sales) OVER (ORDER BY date) as cumulative_sales,
    AVG(sales) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM daily_sales;

-- LAG/LEAD
SELECT 
    date,
    sales,
    LAG(sales, 1) OVER (ORDER BY date) as prev_sales,
    LEAD(sales, 1) OVER (ORDER BY date) as next_sales
FROM daily_sales;

-- 递归查询树形结构
WITH RECURSIVE categories AS (
    -- 锚点：顶级分类
    SELECT id, name, parent_id, 0 as level
    FROM category 
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归：子分类
    SELECT c.id, c.name, c.parent_id, cat.level + 1
    FROM category c
    INNER JOIN categories cat ON c.parent_id = cat.id
)
SELECT * FROM categories;

-- 路径追踪
WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id, name as path
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, 
           eh.path || ' > ' || e.name
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

-- B-tree索引（默认）
CREATE INDEX idx_email ON users(email);

-- GiST索引（地理数据）
CREATE INDEX idx_location ON places USING GiST (location);

-- GIN索引（JSONB、数组）
CREATE INDEX idx_specs ON products USING GIN (specs);
CREATE INDEX idx_tags ON products USING GIN (tags);

-- BRIN索引（大表）
CREATE INDEX idx_timestamp ON logs USING BRIN (created_at);

-- 部分索引
CREATE INDEX idx_active_users ON users(email) 
WHERE status = 'active';

-- 表达式索引
CREATE INDEX idx_lower_email ON users(lower(email));

-- 复合索引
CREATE INDEX idx_name_age ON users(lastname, firstname, age);

-- 创建存储过程
CREATE OR REPLACE FUNCTION get_user_orders(
    user_id INTEGER,
    OUT order_count INTEGER,
    OUT total_amount NUMERIC
) AS $$
BEGIN
    SELECT COUNT(*), SUM(amount)
    INTO order_count, total_amount
    FROM orders 
    WHERE user_id = get_user_orders.user_id;
END;
$$ LANGUAGE plpgsql;

-- 调用存储过程
SELECT * FROM get_user_orders(1);

-- 创建触发器
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 商品表（使用JSONB存储规格）
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price NUMERIC(10,2) NOT NULL,
    stock INTEGER DEFAULT 0,
    specs JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_specs ON products USING GIN (specs);

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total_amount NUMERIC(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);

-- 按月统计销售额（窗口函数）
SELECT 
    DATE_TRUNC('month', created_at) as month,
    SUM(total_amount) as monthly_sales,
    AVG(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', created_at)
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE_TRUNC('month', created_at);

-- Top 10 用户（子查询）
SELECT u.username, o.total_spent
FROM (
    SELECT user_id, SUM(total_amount) as total_spent
    FROM orders
    GROUP BY user_id
    ORDER BY total_spent DESC
    LIMIT 10
) o
JOIN users u ON o.user_id = u.id;

-- 商品推荐（基于共同购买）
SELECT DISTINCT p2.id, p2.name
FROM order_items oi1
JOIN order_items oi2 ON oi1.order_id = oi2.order_id
JOIN products p1 ON oi1.product_id = p1.id
JOIN products p2 ON oi2.product_id = p2.id
WHERE p1.id = 123 AND p2.id != p1.id
LIMIT 10;