Develop

PostgreSQL 高级特性：窗口函数、CTE 与全文搜索实战

2026-06-26 1 阅读 0 评论
✎ -- 字 🕐 -- 分钟
字号

窗口函数——SQL 的瑞士军刀

窗口函数让你在不改变行数的前提下做聚合计算：

-- 每类商品按价格排名
SELECT name, category, price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) as rank,
  price - LAG(price) OVER (PARTITION BY category ORDER BY price) as price_diff,
  SUM(price) OVER (PARTITION BY category) as category_total
FROM products;

递归 CTE

处理树形结构（组织架构、评论嵌套）的利器：

WITH RECURSIVE comment_tree AS (
  SELECT id, parent_id, content, 1 as depth
  FROM comments WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.content, ct.depth + 1
  FROM comments c
  JOIN comment_tree ct ON c.parent_id = ct.id
)
SELECT * FROM comment_tree ORDER BY depth, id;

全文搜索

-- 创建 GIN 索引
ALTER TABLE articles ADD COLUMN tsv tsvector;
UPDATE articles SET tsv = to_tsvector('english', title || ' ' || content);
CREATE INDEX idx_tsv ON articles USING GIN(tsv);

-- 搜索（支持词干提取、排名）
SELECT title, ts_rank(tsv, query) as rank
FROM articles, to_tsquery('english', 'database & performance') query
WHERE tsv @@ query
ORDER BY rank DESC LIMIT 10;

JSONB 灵活存储

-- JSONB 索引
CREATE INDEX idx_props ON products USING GIN(properties jsonb_path_ops);

-- 查询
SELECT * FROM products WHERE properties @> '{"color":"red"}';

-- 更新字段
UPDATE products SET properties = jsonb_set(properties, '{price}', '99');

性能优化要点

  • EXPLAIN ANALYZE 分析查询计划
  • BRIN 索引适合物理有序的大表
  • Partial Index 对特定查询场景极致优化
  • 物化视图缓存复杂聚合结果