05-PostgreSQL 进阶¶

-- 连接数据库
psql -U username -d database_name -h localhost -p 5432

-- 查看版本
SELECT version();

-- 列出所有数据库
\l

-- 切换数据库
\c database_name

-- 列出所有表
\dt

-- 查看表结构
\d table_name

-- 查看所有用户
\du

-- 执行SQL文件
\i /path/to/script.sql

-- 退出
\q

-- 数组类型
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    scores INT[],                    -- 整数数组
    tags TEXT[]                     -- 字符串数组
);

-- 插入数组数据
INSERT INTO students (name, scores, tags)
VALUES ('张三', ARRAY[85, 90, 78], ARRAY['AI', 'Python']);

-- 数组查询
SELECT * FROM students WHERE 'AI' = ANY(tags);
SELECT * FROM students WHERE scores @> ARRAY[90];  -- 包含90分

-- 范围类型
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    duration TSRANGE                 -- 时间戳范围
);

INSERT INTO events (name, duration)
VALUES ('会议', '[2024-01-15 09:00, 2024-01-15 11:00)');

-- 查询重叠
SELECT * FROM events
WHERE duration && '[2024-01-15 10:00, 2024-01-15 12:00)';

-- UUID类型
CREATE TABLE api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    key_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 枚举类型
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'banned');

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    status user_status DEFAULT 'active'
);

-- 点
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    coord POINT
);

INSERT INTO locations (name, coord)
VALUES ('北京', point(116.4074, 39.9042));

-- 查询距离
SELECT * FROM locations
WHERE coord <-> point(116.4074, 39.9042) < 1;

-- 创建表
CREATE TABLE ml_experiments (
    exp_id SERIAL PRIMARY KEY,
    exp_name VARCHAR(100),
    config JSONB,                    -- 实验配置
    metrics JSONB,                   -- 实验指标
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入数据
INSERT INTO ml_experiments (exp_name, config, metrics) VALUES
('exp_001',
 '{"model": "ResNet50", "lr": 0.001, "batch_size": 32, "epochs": 100}',
 '{"train_acc": 0.95, "val_acc": 0.92, "test_acc": 0.91}'
),
('exp_002',
 '{"model": "VGG16", "lr": 0.0001, "batch_size": 64, "epochs": 150}',
 '{"train_acc": 0.93, "val_acc": 0.90, "test_acc": 0.89}'
);

-- 查询JSONB字段
-- 1. 提取字段
SELECT exp_name, config->>'model' as model FROM ml_experiments;

-- 2. 条件查询
SELECT * FROM ml_experiments
WHERE config->>'model' = 'ResNet50';

-- 3. 数值比较
SELECT * FROM ml_experiments
WHERE (config->>'lr')::float < 0.01;

-- 4. 嵌套查询
SELECT * FROM ml_experiments
WHERE metrics->>'val_acc' > '0.91';

-- 5. 检查键是否存在
SELECT * FROM ml_experiments
WHERE config ? 'optimizer';  -- 是否有optimizer键

-- 6. 检查多个键
SELECT * FROM ml_experiments
WHERE config ?& ARRAY['model', 'lr'];  -- 同时有这两个键

-- 7. 数组元素查询
SELECT * FROM ml_experiments
WHERE config->'layers' @> '["conv1", "conv2"]';

-- 更新JSONB
-- 1. 更新整个字段
UPDATE ml_experiments
SET config = '{"model": "ResNet101", "lr": 0.001}'
WHERE exp_id = 1;

-- 2. 更新特定键
UPDATE ml_experiments
SET config = jsonb_set(config, '{lr}', '0.0005')
WHERE exp_id = 1;

-- 3. 添加新键
UPDATE ml_experiments
SET config = config || '{"optimizer": "Adam"}'
WHERE exp_id = 1;

-- 4. 删除键
UPDATE ml_experiments
SET config = config - 'optimizer'
WHERE exp_id = 1;

-- GIN索引（通用倒排索引）
CREATE INDEX idx_config_gin ON ml_experiments USING GIN (config);

-- 特定路径的GIN索引
CREATE INDEX idx_config_model ON ml_experiments USING GIN ((config->'model'));

-- 表达式索引（数值字段）
CREATE INDEX idx_config_lr ON ml_experiments (((config->>'lr')::float));

-- 查询使用索引
EXPLAIN ANALYZE  -- EXPLAIN查看查询执行计划
SELECT * FROM ml_experiments
WHERE config @> '{"model": "ResNet50"}';

-- 创建表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    search_vector TSVECTOR
);

-- 插入数据
INSERT INTO articles (title, content) VALUES
('PostgreSQL入门教程', 'PostgreSQL是一个强大的开源关系型数据库...'),
('Python机器学习', '使用Python进行机器学习开发，需要掌握NumPy、Pandas...'),
('深度学习框架对比', 'TensorFlow和PyTorch是目前最流行的深度学习框架...');

-- 创建搜索向量（中文需要额外配置）
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);

-- 简单搜索
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'Python');

-- 多词搜索
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'machine & learning');

-- 或搜索
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'TensorFlow | PyTorch');

-- 短语搜索
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'deep <-> learning');

-- 带排名的搜索
SELECT
    id,
    title,
    ts_rank(search_vector, to_tsquery('english', 'learning')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'learning')
ORDER BY rank DESC;

-- 创建函数
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN  -- 事务保证操作原子性
    NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trigger_update_search_vector
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();

-- 现在插入数据会自动更新search_vector
INSERT INTO articles (title, content)
VALUES ('新文章', '这是新文章的内容...');

-- 安装zhparser（需要编译安装）
-- CREATE EXTENSION zhparser;
-- CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);

-- 或使用pg_jieba
-- 更简单的方法是使用ngram

-- 使用ngram进行中文搜索
CREATE TEXT SEARCH CONFIGURATION chinese_ngram (COPY = simple);

ALTER TEXT SEARCH CONFIGURATION chinese_ngram
ALTER MAPPING FOR asciiword, word WITH simple;

-- 创建ngram索引
CREATE INDEX idx_articles_search ON articles
USING GIN (search_vector);

-- 实际项目中建议使用ElasticSearch或专门的中文搜索方案

-- 示例数据
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product VARCHAR(50),
    category VARCHAR(50),
    amount DECIMAL(10,2),
    sale_date DATE
);

INSERT INTO sales (product, category, amount, sale_date) VALUES
('iPhone', '手机', 5999.00, '2024-01-01'),
('iPad', '平板', 3999.00, '2024-01-01'),
('MacBook', '电脑', 12999.00, '2024-01-02'),
('iPhone', '手机', 5999.00, '2024-01-03'),
('AirPods', '配件', 1999.00, '2024-01-03'),
('iPad', '平板', 3999.00, '2024-01-04');

-- 1. ROW_NUMBER() - 行号
SELECT
    product,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS rank
FROM sales;

-- 2. RANK() - 排名（允许并列）
SELECT
    product,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS rank
FROM sales;

-- 3. DENSE_RANK() - 密集排名
SELECT
    product,
    amount,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS rank
FROM sales;

-- 4. 分组窗口函数
SELECT
    product,
    category,
    amount,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS category_rank
FROM sales;

-- 5. 累计求和
SELECT
    product,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sum
FROM sales;

-- 6. 分组累计
SELECT
    product,
    category,
    amount,
    SUM(amount) OVER (PARTITION BY category ORDER BY sale_date) AS category_cumulative
FROM sales;

-- 7. 移动平均
SELECT
    product,
    amount,
    AVG(amount) OVER (ORDER BY sale_date ROWS 2 PRECEDING) AS moving_avg
FROM sales;

-- 8. LAG/LEAD - 前后行
SELECT
    product,
    amount,
    LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_amount,
    LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_amount
FROM sales;

-- 9. FIRST_VALUE/LAST_VALUE
SELECT
    product,
    amount,
    FIRST_VALUE(amount) OVER (ORDER BY amount) AS min_amount,
    LAST_VALUE(amount) OVER (ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_amount
FROM sales;

-- 10. NTILE - 分桶
SELECT
    product,
    amount,
    NTILE(4) OVER (ORDER BY amount) AS quartile
FROM sales;

-- 实验指标趋势分析
CREATE TABLE experiment_metrics (
    exp_id INT,
    step INT,
    loss DECIMAL(10,6),
    accuracy DECIMAL(5,4)
);

-- 计算滑动平均（平滑曲线）
SELECT
    exp_id,
    step,
    loss,
    AVG(loss) OVER (  -- 窗口函数：在结果集上滑动计算
        PARTITION BY exp_id
        ORDER BY step
        ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
    ) AS smoothed_loss
FROM experiment_metrics;

-- 计算改进幅度
SELECT
    exp_id,
    step,
    accuracy,
    accuracy - LAG(accuracy, 10) OVER (PARTITION BY exp_id ORDER BY step) AS improvement
FROM experiment_metrics;

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

-- Hash索引（等值查询）
CREATE INDEX idx_hash ON users USING HASH (name);

-- GiST索引（地理数据、范围）
CREATE INDEX idx_coord ON locations USING GiST (coord);

-- GIN索引（数组、JSONB、全文搜索）
CREATE INDEX idx_tags ON articles USING GIN (tags);
CREATE INDEX idx_config ON ml_experiments USING GIN (config);

-- BRIN索引（大块数据，时序）
CREATE INDEX idx_time 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(name, age);

-- 唯一索引
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 1. 分析表
ANALYZE users;

-- 2. 查看执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

-- 3. 查看索引使用情况
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
WHERE tablename = 'users';

-- 4. 清理未使用的索引
-- 先监控一段时间，然后删除idx_scan为0的索引

-- 查看可用扩展
SELECT * FROM pg_available_extensions;

-- 查看已安装扩展
SELECT * FROM pg_extension;

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- 生成UUID
SELECT uuid_generate_v4();

-- 密码哈希
SELECT crypt('password', gen_salt('bf'));

-- 验证密码
SELECT crypt('password', stored_hash) = stored_hash;

-- 安装pgvector
CREATE EXTENSION vector;

-- 创建表
CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536)  -- OpenAI嵌入维度
);

-- 插入向量
INSERT INTO items (content, embedding) VALUES
('示例文本', '[0.1, 0.2, 0.3, ...]');  -- 1536维向量

-- 相似度搜索（余弦相似度）
SELECT *, embedding <=> '[0.1, 0.2, ...]' AS distance
FROM items
ORDER BY embedding <=> '[0.1, 0.2, ...]'
LIMIT 5;

-- 创建IVFFlat索引
CREATE INDEX idx_embedding ON items  -- INDEX索引加速查询
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);  -- CTE公共表表达式：临时命名结果集