高级查询
基础 SELECT 见 基础命令；窗口函数可对照 MySQL 开窗函数。
CTE（公共表表达式）
WITH recent_orders AS (
SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE created_at > NOW() - INTERVAL '30 days'
GROUP BY user_id
)
SELECT u.name, ro.order_count
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id
WHERE ro.order_count > 5;递归 CTE
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.level + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;窗口函数
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;
SELECT
date,
amount,
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
FROM daily_sales;全文搜索
CREATE INDEX idx_articles_tsv ON articles
USING GIN(to_tsvector('english', title || ' ' || content));
SELECT title, ts_rank(to_tsvector('english', title || ' ' || content), query) AS rank
FROM articles, to_tsquery('english', 'postgresql & index') query
WHERE to_tsvector('english', title || ' ' || content) @@ query
ORDER BY rank DESC;专业搜索场景见 Elasticsearch。
分区表
CREATE TABLE orders (
id BIGINT,
created_at TIMESTAMPTZ,
amount NUMERIC
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE user_logs (
user_id BIGINT,
action TEXT
) PARTITION BY HASH (user_id);
CREATE TABLE user_logs_0 PARTITION OF user_logs FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_logs_1 PARTITION OF user_logs FOR VALUES WITH (MODULUS 4, REMAINDER 1);