PostgreSQL 高级特性

HSXISAWD2026/3/20大约 15 分钟

PostgreSQL 高级特性

一、窗口函数

1.1 窗口函数概述

窗口函数在一组相关的行上执行计算，同时保留原始行。与 GROUP BY 不同，窗口函数不会合并行。

1.2 窗口函数语法

-- 基本语法
function_name(args) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC|DESC] [NULLS {FIRST|LAST}]]
    [frame_clause]
)

-- 窗口帧子句
ROWS BETWEEN frame_start AND frame_end
RANGE BETWEEN frame_start AND frame_end
GROUPS BETWEEN frame_start AND frame_end

-- frame_start / frame_end 选项
UNBOUNDED PRECEDING   -- 分区开始
N PRECEDING           -- 当前行前 N 行
CURRENT ROW          -- 当前行
N FOLLOWING          -- 当前行后 N 行
UNBOUNDED FOLLOWING  -- 分区结束

-- 默认窗口帧
-- 有 ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- 无 ORDER BY: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

1.3 排名函数

-- 准备示例数据
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 5000),
('Bob', 'Sales', 6000),
('Charlie', 'Sales', 5000),
('David', 'Tech', 8000),
('Eve', 'Tech', 7000),
('Frank', 'Tech', 8000);

-- ROW_NUMBER(): 唯一序号
SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;
-- Sales: Bob(1), Alice(2), Charlie(3)  -- 相同薪资也有不同序号
-- Tech:  David(1), Frank(2), Eve(3)

-- RANK(): 跳跃排名
SELECT
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
-- Sales: Bob(1), Alice(2), Charlie(2)  -- 相同薪资相同排名
-- Tech:  David(1), Frank(1), Eve(3)    -- 排名跳跃

-- DENSE_RANK(): 连续排名
SELECT
    name, department, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
-- Sales: Bob(1), Alice(2), Charlie(2)
-- Tech:  David(1), Frank(1), Eve(2)    -- 排名不跳跃

-- NTILE(n): 分组
SELECT
    name, salary,
    NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;
-- 将数据分成 4 组

-- PERCENT_RANK(): 百分比排名
SELECT
    name, salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
FROM employees;
-- (rank - 1) / (total rows - 1)

-- CUME_DIST(): 累计分布
SELECT
    name, salary,
    CUME_DIST() OVER (ORDER BY salary) AS cume_dist
FROM employees;
-- 小于等于当前值的行数 / 总行数

1.4 聚合窗口函数

-- 聚合函数作为窗口函数
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,
    MIN(salary) OVER (PARTITION BY department) AS dept_min,
    MAX(salary) OVER (PARTITION BY department) AS dept_max
FROM employees;

-- 累计求和
SELECT
    name, salary,
    SUM(salary) OVER (ORDER BY id) AS running_total
FROM employees;

-- 移动平均（窗口帧）
SELECT
    name, salary,
    AVG(salary) OVER (
        ORDER BY id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM employees;

-- 部门内占比
SELECT
    name, department, salary,
    round(salary * 100.0 / SUM(salary) OVER (PARTITION BY department), 2) AS pct_of_dept
FROM employees;

1.5 值函数

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

-- NTH_VALUE
SELECT
    name, department, salary,
    NTH_VALUE(name, 2) OVER (
        PARTITION BY department ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest
FROM employees;

-- LAG / LEAD: 访问前后行
SELECT
    name, salary,
    LAG(salary, 1) OVER (ORDER BY id) AS prev_salary,
    LEAD(salary, 1) OVER (ORDER BY id) AS next_salary,
    salary - LAG(salary, 1) OVER (ORDER BY id) AS diff_from_prev
FROM employees;

-- 带默认值
SELECT
    name, salary,
    LAG(salary, 1, 0) OVER (ORDER BY id) AS prev_salary
FROM employees;

1.6 窗口函数实战

-- 1. 每个部门薪资前三名
SELECT * FROM (
    SELECT
        name, department, salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
) t WHERE rn <= 3;

-- 2. 同比增长计算
CREATE TABLE monthly_sales (
    month DATE,
    amount NUMERIC(10, 2)
);

INSERT INTO monthly_sales VALUES
('2024-01-01', 10000),
('2024-02-01', 12000),
('2024-03-01', 11000),
('2024-04-01', 15000);

SELECT
    month,
    amount,
    LAG(amount, 1) OVER (ORDER BY month) AS prev_month,
    round((amount - LAG(amount, 1) OVER (ORDER BY month)) * 100.0 /
        LAG(amount, 1) OVER (ORDER BY month), 2) AS growth_rate
FROM monthly_sales;

-- 3. 连续登录天数
CREATE TABLE user_logins (
    user_id INT,
    login_date DATE
);

WITH login_groups AS (
    SELECT
        user_id,
        login_date,
        login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)::INT AS grp
    FROM user_logins
)
SELECT
    user_id,
    MIN(login_date) AS start_date,
    MAX(login_date) AS end_date,
    COUNT(*) AS consecutive_days
FROM login_groups
GROUP BY user_id, grp
ORDER BY user_id, start_date;

-- 4. 累计百分比（帕累托分析）
SELECT
    name,
    salary,
    SUM(salary) OVER (ORDER BY salary DESC) AS cumulative_salary,
    round(SUM(salary) OVER (ORDER BY salary DESC) * 100.0 /
        SUM(salary) OVER (), 2) AS cumulative_pct
FROM employees;

二、CTE（公共表表达式）

2.1 基本 CTE

-- CTE 基本语法
WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

-- 多个 CTE
WITH
    sales_cte AS (
        SELECT user_id, SUM(amount) AS total_sales
        FROM orders
        GROUP BY user_id
    ),
    user_cte AS (
        SELECT id, name FROM users
    )
SELECT u.name, s.total_sales
FROM user_cte u
JOIN sales_cte s ON u.id = s.user_id;

-- CTE 替代子查询（更易读）
-- 子查询写法
SELECT * FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) t WHERE total > 1000;

-- CTE 写法
WITH order_totals AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT * FROM order_totals WHERE total > 1000;

2.2 递归 CTE

-- 递归 CTE 语法
WITH RECURSIVE cte_name AS (
    -- 初始查询（非递归部分）
    SELECT ...
    UNION [ALL]
    -- 递归查询（引用 CTE 自身）
    SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;

-- 示例 1：生成数字序列
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;
-- 结果：1, 2, 3, ..., 10

-- 示例 2：组织架构树
CREATE TABLE employees_tree (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT REFERENCES employees_tree(id)
);

INSERT INTO employees_tree VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, 'CFO', 1),
(4, 'Dev Manager', 2),
(5, 'Developer', 4),
(6, 'Developer', 4);

-- 查询组织架构
WITH RECURSIVE org_tree AS (
    -- 根节点
    SELECT id, name, manager_id, 1 AS level, name::TEXT AS path
    FROM employees_tree
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归：子节点
    SELECT e.id, e.name, e.manager_id, t.level + 1, t.path || ' -> ' || e.name
    FROM employees_tree e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY path;

-- 示例 3：查找所有下属
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees_tree
    WHERE id = 2  -- CTO

    UNION ALL

    SELECT e.id, e.name, e.manager_id
    FROM employees_tree e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

-- 示例 4：物料清单展开（BOM）
CREATE TABLE bom (
    parent_id INT,
    child_id INT,
    quantity INT
);

WITH RECURSIVE bom_tree AS (
    SELECT child_id AS component_id, quantity, 1 AS level
    FROM bom
    WHERE parent_id = 1  -- 产品 ID

    UNION ALL

    SELECT b.child_id, b.quantity * t.quantity, t.level + 1
    FROM bom b
    JOIN bom_tree t ON b.parent_id = t.component_id
    WHERE t.level < 10  -- 防止无限递归
)
SELECT component_id, SUM(quantity) AS total_quantity
FROM bom_tree
GROUP BY component_id;

2.3 CTE 物化与优化

-- PostgreSQL 12+ 支持 MATERIALIZED / NOT MATERIALIZED

-- 强制物化（结果集被缓存，可能多次使用时更快）
WITH cte AS MATERIALIZED (
    SELECT * FROM large_table WHERE ...
)
SELECT * FROM cte WHERE ...
UNION
SELECT * FROM cte WHERE ...;

-- 不物化（内联到主查询，优化器可以优化）
WITH cte AS NOT MATERIALIZED (
    SELECT * FROM large_table WHERE ...
)
SELECT * FROM cte WHERE id = 1;

-- 默认行为
-- 如果 CTE 只被引用一次，可能内联
-- 如果 CTE 被引用多次，通常会物化

-- 使用 EXPLAIN 查看 CTE 是否被物化
EXPLAIN WITH cte AS (
    SELECT * FROM users
)
SELECT * FROM cte WHERE id = 1;

三、全文搜索

3.1 全文搜索基础

-- 基本概念
-- TSVECTOR: 文档的词法表示
-- TSQUERY: 搜索查询

-- 创建 TSVECTOR
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

-- 创建 TSQUERY
SELECT to_tsquery('english', 'quick & fox');
-- 'quick' & 'fox'

SELECT plainto_tsquery('english', 'quick fox');
-- 'quick' & 'fox'

SELECT phraseto_tsquery('english', 'quick fox');
-- 'quick' <-> 'fox' (相邻)

SELECT websearch_to_tsquery('english', '"quick fox" -lazy');
-- 'quick' <-> 'fox' & !'lazi'

-- 匹配查询
SELECT to_tsvector('english', 'The quick brown fox') @@
       to_tsquery('english', 'quick & fox');
-- true

SELECT to_tsvector('english', 'The quick brown fox') @@
       to_tsquery('english', 'quick & dog');
-- false

3.2 全文搜索实践

-- 创建带全文搜索的表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    -- 存储 TSVECTOR
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(body, '')), 'B')
    ) STORED
);

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

-- 插入数据
INSERT INTO articles (title, body) VALUES
('PostgreSQL Full Text Search', 'This article covers full text search in PostgreSQL database'),
('MySQL vs PostgreSQL', 'A comparison of MySQL and PostgreSQL databases'),
('Database Performance', 'Tips for optimizing database performance');

-- 基本搜索
SELECT title, body
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql');

-- 搜索并排序
SELECT
    title,
    ts_rank(search_vector, query) AS rank
FROM articles,
    to_tsquery('english', 'postgresql & database') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 带权重的排序
SELECT
    title,
    ts_rank_cd(search_vector, query, 32) AS rank  -- 32: 考虑词频
FROM articles,
    to_tsquery('english', 'postgresql') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 高亮显示
SELECT
    title,
    ts_headline('english', body, to_tsquery('english', 'postgresql'),
        'StartSel=<mark>, StopSel=</mark>, MaxFragments=2') AS highlighted
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql');

3.3 中文全文搜索

-- 使用 zhparser 或 pg_jieba 扩展
-- 安装 zhparser（需要单独安装）
CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese ADD MAPPING FOR n,v,a,i,e,l WITH simple;

-- 测试中文分词
SELECT to_tsvector('chinese', '深入理解 PostgreSQL 数据库系统');

-- 中文全文搜索表
CREATE TABLE articles_cn (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector TSVECTOR
);

-- 触发器自动更新
CREATE OR REPLACE FUNCTION update_search_vector() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('chinese', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('chinese', COALESCE(NEW.content, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_cn_search_update
    BEFORE INSERT OR UPDATE ON articles_cn
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();

CREATE INDEX idx_articles_cn_search ON articles_cn USING GIN (search_vector);

3.4 模糊搜索与相似度

-- pg_trgm 扩展（三元组）
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 相似度查询
SELECT similarity('postgresql', 'postgres');
-- 0.6153846

SELECT word_similarity('post', 'postgresql');
-- 0.5

-- 模糊匹配
SELECT * FROM articles WHERE title % 'postgre';  -- 相似度 > 0.3
SELECT * FROM articles WHERE title <-> 'postgre' < 0.7;  -- 距离

-- 创建索引支持模糊查询
CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);

-- 支持 LIKE 和 ILIKE
SELECT * FROM articles WHERE title LIKE '%postgre%';
SELECT * FROM articles WHERE title ILIKE '%POSTGRE%';

-- 自动补全
SELECT DISTINCT title
FROM articles
WHERE title % 'post'
ORDER BY similarity(title, 'post') DESC
LIMIT 10;

四、分区表

4.1 分区类型

-- PostgreSQL 支持三种分区类型
-- 1. 范围分区（RANGE）
-- 2. 列表分区（LIST）
-- 3. 哈希分区（HASH）

-- 范围分区（最常用）
CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT,
    amount NUMERIC(10, 2),
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- 默认分区（接收不匹配的数据）
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- 列表分区
CREATE TABLE sales (
    id SERIAL,
    region TEXT NOT NULL,
    amount NUMERIC(10, 2)
) PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales
    FOR VALUES IN ('Beijing', 'Tianjin', 'Hebei');
CREATE TABLE sales_east PARTITION OF sales
    FOR VALUES IN ('Shanghai', 'Jiangsu', 'Zhejiang');
CREATE TABLE sales_south PARTITION OF sales
    FOR VALUES IN ('Guangdong', 'Fujian', 'Hainan');

-- 哈希分区（均匀分布）
CREATE TABLE logs (
    id BIGSERIAL,
    user_id BIGINT,
    message TEXT,
    created_at TIMESTAMPTZ
) PARTITION BY HASH (user_id);

CREATE TABLE logs_0 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE logs_1 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE logs_2 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE logs_3 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 3);

4.2 分区表管理

-- 查看分区表
SELECT
    parent.relname AS parent,
    child.relname AS partition,
    pg_get_expr(child.relpartbound, child.oid) AS partition_expression
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders';

-- 分离分区（不删除数据）
ALTER TABLE orders DETACH PARTITION orders_2024_01;

-- 附加分区
ALTER TABLE orders ATTACH PARTITION orders_2024_01
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 删除分区
DROP TABLE orders_2024_01;

-- 分区索引
-- 在父表上创建索引会自动应用到所有分区
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 分区表主键（必须包含分区键）
ALTER TABLE orders ADD PRIMARY KEY (id, created_at);

-- 分区表唯一约束
-- 唯一约束必须包含分区键
CREATE UNIQUE INDEX idx_orders_unique ON orders(id, created_at);

4.3 分区裁剪

-- 查询优化：分区裁剪（Partition Pruning）
-- 优化器会排除不相关的分区

EXPLAIN SELECT * FROM orders WHERE created_at = '2024-02-15';
-- 只扫描 orders_2024_02

-- 确保分区裁剪生效
SET enable_partition_pruning = on;  -- 默认开启

-- 动态分区裁剪（参数化查询）
PREPARE q1 AS SELECT * FROM orders WHERE created_at = $1;
EXECUTE q1('2024-02-15');
-- 执行时根据参数选择分区

-- 跨分区查询
EXPLAIN SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-15' AND '2024-02-15';
-- 扫描 orders_2024_01 和 orders_2024_02

4.4 自动分区管理

-- 自动创建分区（使用 pg_partman 扩展）
CREATE EXTENSION pg_partman;

-- 或者使用触发器/定时任务手动管理
-- 创建下月分区的函数
CREATE OR REPLACE FUNCTION create_next_month_partition()
RETURNS void AS $$
DECLARE
    next_month DATE := DATE_TRUNC('month', NOW() + INTERVAL '1 month');
    partition_name TEXT;
    start_date TEXT;
    end_date TEXT;
BEGIN
    partition_name := 'orders_' || TO_CHAR(next_month, 'YYYY_MM');
    start_date := TO_CHAR(next_month, 'YYYY-MM-DD');
    end_date := TO_CHAR(next_month + INTERVAL '1 month', 'YYYY-MM-DD');

    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF orders
        FOR VALUES FROM (%L) TO (%L)',
        partition_name, start_date, end_date);
END;
$$ LANGUAGE plpgsql;

-- 使用 pg_cron 定时执行
-- SELECT cron.schedule('create_monthly_partition', '0 0 25 * *', 'SELECT create_next_month_partition()');

-- 删除旧分区
CREATE OR REPLACE FUNCTION drop_old_partitions(retention_months INT)
RETURNS void AS $$
DECLARE
    partition RECORD;
    cutoff_date DATE := DATE_TRUNC('month', NOW() - make_interval(months => retention_months));
BEGIN
    FOR partition IN
        SELECT child.relname AS name
        FROM pg_inherits
        JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
        JOIN pg_class child ON pg_inherits.inhrelid = child.oid
        WHERE parent.relname = 'orders'
        AND child.relname < 'orders_' || TO_CHAR(cutoff_date, 'YYYY_MM')
    LOOP
        EXECUTE format('DROP TABLE %I', partition.name);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

五、JSON 高级操作

5.1 JSON 路径查询

-- PostgreSQL 12+ 支持 SQL/JSON 路径语言

-- 准备数据
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

INSERT INTO products (data) VALUES
('{"name": "iPhone", "price": 999, "specs": {"color": "black", "storage": "128GB"}, "tags": ["phone", "apple"]}'),
('{"name": "MacBook", "price": 1999, "specs": {"color": "silver", "storage": "512GB"}, "tags": ["laptop", "apple"]}');

-- 路径查询
SELECT jsonb_path_query(data, '$.name') FROM products;
SELECT jsonb_path_query(data, '$.specs.color') FROM products;
SELECT jsonb_path_query(data, '$.tags[0]') FROM products;
SELECT jsonb_path_query(data, '$.tags[*]') FROM products;  -- 所有标签

-- 条件过滤
SELECT jsonb_path_query(data, '$.specs ? (@.storage == "128GB")') FROM products;
SELECT * FROM products
WHERE jsonb_path_exists(data, '$.price ? (@ > 1000)');

-- 路径查询函数
SELECT jsonb_path_query_array(data, '$.tags[*]') FROM products;
SELECT jsonb_path_query_first(data, '$.tags[*]') FROM products;

-- 带变量的路径查询
SELECT jsonb_path_query(data, '$.price ? (@ > $min)', '{"min": 500}') FROM products;

5.2 JSON 聚合与构造

-- 构造 JSON 对象
SELECT jsonb_build_object(
    'user_id', id,
    'user_name', name,
    'created', created_at
) FROM users;

-- 构造 JSON 数组
SELECT jsonb_build_array(id, name, email) FROM users;

-- 聚合为 JSON 数组
SELECT jsonb_agg(name) FROM users;
SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name)) FROM users;

-- 聚合为 JSON 对象
SELECT jsonb_object_agg(id, name) FROM users;

-- 行转 JSON
SELECT to_jsonb(users) FROM users;
SELECT row_to_json(users) FROM users;

-- JSON 表函数（展开 JSON）
SELECT * FROM jsonb_to_record('{"name": "John", "age": 30}'::JSONB)
AS x(name TEXT, age INT);

SELECT * FROM jsonb_to_recordset('[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]'::JSONB)
AS x(name TEXT, age INT);

-- 嵌套 JSON 展开
SELECT
    p.id,
    t.tag
FROM products p,
    jsonb_array_elements_text(p.data->'tags') AS t(tag);

5.3 JSON 更新操作

-- 更新 JSON 字段
-- 设置值
UPDATE products SET data = jsonb_set(data, '{price}', '1099') WHERE id = 1;
UPDATE products SET data = jsonb_set(data, '{specs, memory}', '"16GB"') WHERE id = 1;

-- 添加新键
UPDATE products SET data = data || '{"stock": 100}' WHERE id = 1;

-- 删除键
UPDATE products SET data = data - 'stock' WHERE id = 1;
UPDATE products SET data = data #- '{specs, color}' WHERE id = 1;

-- 数组操作
UPDATE products SET data = jsonb_set(data, '{tags}',
    (data->'tags') || '"new_tag"') WHERE id = 1;

-- 删除数组元素
UPDATE products SET data = jsonb_set(data, '{tags}',
    (data->'tags') - 0) WHERE id = 1;  -- 删除第一个

-- 条件更新
UPDATE products SET data = jsonb_set(data, '{discount}', 'true')
WHERE (data->>'price')::INT > 1000;

六、物化视图

6.1 物化视图基础

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
    DATE_TRUNC('day', created_at) AS day,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders
GROUP BY DATE_TRUNC('day', created_at)
WITH DATA;  -- 立即填充数据

-- 不填充数据（稍后刷新）
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
WITH NO DATA;

-- 创建索引（物化视图支持索引）
CREATE UNIQUE INDEX idx_mv_daily_sales_day ON mv_daily_sales (day);

-- 查询物化视图
SELECT * FROM mv_daily_sales WHERE day >= '2024-01-01' ORDER BY day;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW mv_daily_sales;

-- 并发刷新（不阻塞查询，需要唯一索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

-- 删除物化视图
DROP MATERIALIZED VIEW mv_daily_sales;

6.2 物化视图刷新策略

-- 定时刷新（使用 pg_cron）
-- 安装 pg_cron 扩展
CREATE EXTENSION pg_cron;

-- 每小时刷新
SELECT cron.schedule('refresh_mv_daily', '0 * * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales');

-- 每天凌晨刷新
SELECT cron.schedule('refresh_mv_monthly', '0 0 * * *',
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales');

-- 查看定时任务
SELECT * FROM cron.job;

-- 手动触发刷新的函数
CREATE OR REPLACE FUNCTION refresh_all_mv()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;
END;
$$ LANGUAGE plpgsql;

-- 基于触发器的增量刷新（伪实现）
-- 注意：PostgreSQL 不支持真正的增量刷新
-- 这里演示一种手动维护的方法
CREATE TABLE mv_order_counts (
    day DATE PRIMARY KEY,
    order_count BIGINT,
    total_amount NUMERIC(12, 2)
);

CREATE OR REPLACE FUNCTION update_order_counts()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO mv_order_counts (day, order_count, total_amount)
    VALUES (DATE_TRUNC('day', NEW.created_at), 1, NEW.amount)
    ON CONFLICT (day)
    DO UPDATE SET
        order_count = mv_order_counts.order_count + 1,
        total_amount = mv_order_counts.total_amount + NEW.amount;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_insert_trigger
    AFTER INSERT ON orders
    FOR EACH ROW EXECUTE FUNCTION update_order_counts();

七、总结

高级特性选择指南

特性	适用场景
窗口函数	排名、累计、移动计算、分组内计算
CTE	复杂查询分解、递归查询、层次数据
全文搜索	文本搜索、关键词匹配、搜索引擎
分区表	大表管理、数据归档、时间序列
JSONB	灵活 schema、文档存储、API 数据
物化视图	聚合缓存、报表加速、复杂计算

使用建议

窗口函数：优先于子查询进行分组内计算
CTE：提高复杂查询可读性，递归查询首选
全文搜索：大量文本搜索优于 LIKE
分区表：TB 级大表必备
JSONB：需要灵活 schema 时使用
物化视图：报表查询必备优化手段