PostgreSQL进阶
窗口函数
-- 排名函数
SELECT name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rank,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- 聚合作为窗口函数
SELECT name, salary, dept,
SUM(salary) OVER (PARTITION BY dept) AS dept_total,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
salary - AVG(salary) OVER (PARTITION BY dept) AS vs_avg
FROM employees;
-- 累计求和 / 移动平均
SELECT date, amount,
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM sales;
-- LAG / LEAD（访问相邻行）
SELECT date, revenue,
LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
LEAD(revenue, 1) OVER (ORDER BY date) AS next_day,
revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_change
FROM daily_revenue;
JSONB 操作
-- 基本 JSONB 访问
SELECT data->>'name' AS name, -- 文本
data->'address' AS address, -- jsonb
data#>>'{address,city}' AS city -- 嵌套文本
FROM users;
-- JSONB 内查询
SELECT * FROM products
WHERE data @> '{"category": "electronics"}'; -- 包含查询
SELECT * FROM products
WHERE data ? 'discount'; -- key 存在
-- 修改 JSONB
UPDATE users
SET data = jsonb_set(data, '{address,zip}', '"100000"')
WHERE id = 42;
UPDATE users SET data = data || '{"verified": true}'; -- 合并
UPDATE users SET data = data - 'temp_token'; -- 删除 key
-- JSONB 索引
CREATE INDEX idx_users_data ON users USING GIN(data);
CREATE INDEX idx_users_email ON users ((data->>'email'));
全文搜索
-- 基本全文搜索
SELECT title, ts_rank(search_vec, query) AS rank
FROM articles,
to_tsquery('chinese', '数据库 & 优化') AS query
WHERE search_vec @@ query
ORDER BY rank DESC;
-- 创建 tsvector 生成列（自动更新）
ALTER TABLE articles
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('chinese', coalesce(title,'')), 'A') ||
setweight(to_tsvector('chinese', coalesce(body,'')), 'B')
) STORED;
CREATE INDEX idx_articles_fts ON articles USING GIN(search_vec);
-- 高亮匹配结果
SELECT ts_headline('chinese', body, to_tsquery('性能'),
'StartSel=, StopSel=, MaxWords=30') AS snippet
FROM articles;
CTE 与横向连接
-- CTE（公共表表达式）
WITH monthly_totals AS (
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(amount) AS total
FROM orders GROUP BY 1
),
ranked AS (
SELECT *, RANK() OVER (ORDER BY total DESC) AS rk
FROM monthly_totals
)
SELECT * FROM ranked WHERE rk <= 3;
-- 递归 CTE（树形遍历）
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth + 1
FROM categories c JOIN category_tree t ON c.parent_id = t.id
)
SELECT * FROM category_tree ORDER BY depth, name;
-- LATERAL 连接（每用户取最新3篇）
SELECT u.name, recent.title, recent.created_at
FROM users u
CROSS JOIN LATERAL (
SELECT title, created_at FROM posts
WHERE user_id = u.id
ORDER BY created_at DESC LIMIT 3
) AS recent;