高级特性

窗口函数

基本语法

SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

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
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

聚合窗口函数

SELECT
    name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    COUNT(*) OVER (PARTITION BY department) AS dept_count
FROM employees;

LAG 和 LEAD

SELECT
    name,
    salary,
    LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
    LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employees;

FIRST_VALUE 和 LAST_VALUE

SELECT
    name,
    department,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS max_in_dept,
    LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min_in_dept
FROM employees;

JSON 操作

JSON 类型

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    data JSON,
    profile JSONB
);

插入 JSON 数据

INSERT INTO users (data, profile) VALUES
    ('{"name": "张三", "age": 25}', '{"name": "张三", "age": 25}'),
    ('{"name": "李四", "skills": ["Java", "Python"]}', '{"name": "李四", "skills": ["Java", "Python"]}');

提取 JSON 值

SELECT data->>'name' FROM users;
SELECT data->'age' FROM users;
SELECT profile->'skills'->0 FROM users;
SELECT data#>>'{skills,0}' FROM users;

JSON 操作符

| 操作符 | 说明 | | ------ | -------------------------- | ---------- | | -> | 获取 JSON 对象字段（JSON） | | ->> | 获取 JSON 对象字段（文本） | | #> | 获取 JSON 路径（JSON） | | #>> | 获取 JSON 路径（文本） | | @> | 包含 | | <@ | 被包含 | | ? | 键存在 | | ? | | 任一键存在 | | ?& | 所有键存在 |

JSON 函数

SELECT json_typeof(data) FROM users;
SELECT json_array_length(data->'skills') FROM users;
SELECT json_extract_path_text(data, 'name') FROM users;
SELECT json_build_object('name', '张三', 'age', 25);
SELECT json_agg(name) FROM users;
SELECT json_object_agg(name, age) FROM users;

JSONB 操作

UPDATE users SET profile = profile || '{"city": "北京"}'::jsonb WHERE id = 1;
UPDATE users SET profile = profile - 'city' WHERE id = 1;
UPDATE users SET profile = profile || '{"age": 26}'::jsonb WHERE id = 1;

数组操作

创建数组

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    tags VARCHAR[]
);

INSERT INTO posts (title, tags) VALUES ('文章1', ARRAY['技术', '数据库']);
INSERT INTO posts (title, tags) VALUES ('文章2', '{"开发", "编程"}');

访问数组

SELECT tags[1] FROM posts;
SELECT tags[1:2] FROM posts;
SELECT array_length(tags, 1) FROM posts;

数组操作符

| 操作符 | 说明 | | ------ | ------ | --- | ---- | | = | 等于 | | <> | 不等于 | | @> | 包含 | | <@ | 被包含 | | && | 重叠 | | | | | 连接 |

数组函数

SELECT unnest(tags) FROM posts;
SELECT array_append(tags, '新标签') FROM posts;
SELECT array_remove(tags, '技术') FROM posts;
SELECT array_position(tags, '技术') FROM posts;
SELECT cardinality(tags) FROM posts;

数组查询

SELECT * FROM posts WHERE '技术' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['技术'];
SELECT * FROM posts WHERE tags && ARRAY['技术', '开发'];

全文搜索

创建全文搜索索引

ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
    to_tsvector('english', title || ' ' || content);

CREATE INDEX idx_search ON articles USING GIN(search_vector);

全文搜索查询

SELECT * FROM articles
WHERE search_vector @@ to_tsquery('postgresql & database');

SELECT * FROM articles
WHERE search_vector @@ plainto_tsquery('PostgreSQL database');

SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('PostgreSQL OR database');

全文搜索函数

SELECT to_tsvector('hello world');
SELECT to_tsquery('hello & world');
SELECT plainto_tsquery('hello world');
SELECT ts_rank(search_vector, to_tsquery('postgresql')) FROM articles;
SELECT ts_headline(content, to_tsquery('postgresql')) FROM articles;

表继承

创建继承表

CREATE TABLE persons (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50)
);

CREATE TABLE employees (
    salary DECIMAL(10, 2),
    department VARCHAR(50)
) INHERITS (persons);

查询继承表

SELECT * FROM persons;
SELECT * FROM ONLY persons;
SELECT * FROM employees;

插入数据

INSERT INTO persons (name, city) VALUES ('张三', '北京');
INSERT INTO employees (name, city, salary, department)
VALUES ('李四', '上海', 10000, 'IT');

视图

创建视图

CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, age FROM users WHERE status = 'active';

物化视图

CREATE MATERIALIZED VIEW user_stats AS
SELECT
    user_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
GROUP BY user_id;

REFRESH MATERIALIZED VIEW user_stats;
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

删除视图

DROP VIEW active_users;
DROP MATERIALIZED VIEW user_stats;

触发器

创建触发器函数

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器

CREATE TRIGGER update_users_modtime
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

删除触发器

DROP TRIGGER update_users_modtime ON users;

存储过程

创建函数

CREATE OR REPLACE FUNCTION get_user_by_id(user_id INTEGER)
RETURNS TABLE(id INTEGER, name VARCHAR, email VARCHAR) AS $$
BEGIN
    RETURN QUERY SELECT u.id, u.name, u.email FROM users u WHERE u.id = user_id;
END;
$$ LANGUAGE plpgsql;

调用函数

SELECT * FROM get_user_by_id(1);

创建存储过程

CREATE OR REPLACE PROCEDURE transfer_money(
    from_id INTEGER,
    to_id INTEGER,
    amount DECIMAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    COMMIT;
END;
$$;

调用存储过程

CALL transfer_money(1, 2, 100);

下一步学习

• 索引优化 - 学习索引优化
• 事务处理 - 掌握事务处理