← 返回首页 ❓ 面试题库
📑 本篇章节导航 (共4章,点击直达)
一、窗口函数深入(1-10)二、CTE与递归查询(11-18)三、JSONB与PostgreSQL高级(19-30)四、SQL高级技巧(31-42)

Q39 - SQL高级与PostgreSQL深入


一、窗口函数深入(1-10)

窗口函数的执行原理?

★★★★★

ROW_NUMBER vs RANK vs DENSE_RANK?

★★★★★

LAG和LEAD函数?

★★★★★


SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  ROUND((revenue - LAG(revenue,1) OVER (ORDER BY month))
    / LAG(revenue,1) OVER (ORDER BY month) * 100, 2) AS growth_rate
FROM monthly_sales;

累计求和和移动平均?

★★★★


SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

SELECT date, amount,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d
FROM daily_stats;

连续登录/连续事件问题?

★★★★★


-- 连续登录7天的用户
SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS days
FROM (
  SELECT user_id, login_date,
    login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)::int AS grp
  FROM user_logins
) t GROUP BY user_id, grp HAVING COUNT(*) >= 7;

Top-N问题?

★★★★


-- 每个类别销量前3的商品
SELECT * FROM (
  SELECT category, product_name, sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rnk
  FROM products
) t WHERE rnk <= 3;

FIRST_VALUE和LAST_VALUE?

★★★★

NTILE分组?

★★★

窗口函数的性能优化?

★★★★

MySQL 8.0 vs PostgreSQL窗口函数?

★★★★


二、CTE与递归查询(11-18)

CTE(公共表表达式)基础?

★★★★


WITH active_users AS (
  SELECT user_id FROM users WHERE status = 'ACTIVE'
)
SELECT o.* FROM orders o JOIN active_users au ON o.user_id = au.user_id;

递归CTE(WITH RECURSIVE)?

★★★★★


WITH RECURSIVE org_tree AS (
  -- 锚点查询(非递归部分)
  SELECT id, name, parent_id, 1 AS level, CAST(name AS VARCHAR(1000)) AS path
  FROM departments WHERE parent_id IS NULL
  UNION ALL
  -- 递归部分
  SELECT d.id, d.name, d.parent_id, ot.level + 1,
    ot.path || ' > ' || d.name
  FROM departments d JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, path;

递归CTE的经典场景?

★★★★★

PostgreSQL的物化CTE?

★★★★


WITH expensive_query AS MATERIALIZED (
  SELECT ... FROM ... -- 复杂查询,物化一次复用
)
SELECT * FROM expensive_query WHERE ...
UNION ALL
SELECT * FROM expensive_query WHERE ...;

CTE vs 子查询 vs 临时表?

★★★★

递归CTE的深度控制和防止无限递归?

★★★★


WITH RECURSIVE tree AS (
  SELECT id, parent_id, 1 AS lvl FROM dept WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.parent_id, t.lvl + 1
  FROM dept d JOIN tree t ON d.parent_id = t.id
  WHERE t.lvl < 10  -- 最多递归10层
)

CTE在数据清洗中的应用?

★★★★

MySQL 8.0的CTE限制?

★★★★


三、JSONB与PostgreSQL高级(19-30)

PostgreSQL的JSONB类型?

★★★★★

JSONB的索引?

★★★★★


CREATE INDEX idx_data_gin ON products USING GIN (data);
CREATE INDEX idx_data_tags ON products USING GIN ((data->'tags'));

CREATE INDEX idx_data_contains ON products USING GIN (data jsonb_path_ops);

CREATE INDEX idx_data_price ON products ((data->>'price')::numeric);

JSONB的查询和更新?

★★★★


SELECT * FROM users WHERE data->>'name' = '张三';
SELECT * FROM products WHERE data->'tags' @> '["Java"]';
SELECT data->'address'->>'city' AS city FROM users;

UPDATE users SET data = jsonb_set(data, '{phone}', '"13800138000"');
UPDATE users SET data = data || '{"status": "active"}';  -- 合并
UPDATE users SET data = data - 'temp_field';  -- 删除字段

JSONB vs MySQL JSON?

★★★★★

维度PostgreSQL JSONBMySQL JSON
存储二进制(预解析)文本+校验
查询支持GIN索引,快需要Generated Column+索引
操作符丰富(->/->>/@>/?)JSON_EXTRACT/JSON_CONTAINS
更新jsonb_set/jsonb_insertJSON_SET/JSON_REPLACE
全文检索JSONB+tsvector结合不支持
面试要点PostgreSQL的JSONB远优于MySQLMySQL JSON适合简单场景

PostgreSQL的全文检索?

★★★★


ALTER TABLE articles ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('simple', title || ' ' || content)) STORED;
CREATE INDEX idx_tsv ON articles USING GIN(tsv);

SELECT *, ts_rank(tsv, query) AS rank
FROM articles, plainto_tsquery('Java 并发编程') query
WHERE tsv @@ query ORDER BY rank DESC;

PostgreSQL的物化视图?

★★★★


CREATE MATERIALIZED VIEW mv_order_stats AS
SELECT product_id, SUM(quantity) AS total_qty, AVG(price) AS avg_price
FROM orders GROUP BY product_id;

REFRESH MATERIALIZED VIEW mv_order_stats;  -- 全量刷新(锁表)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_stats;  -- 增量刷新(不锁表,需要唯一索引)

PostgreSQL的五大索引类型?

★★★★

PostgreSQL的MVCC实现?

★★★★★

PostgreSQL的扩展生态?

★★★★

pgvector向量搜索?

★★★★★


CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

SELECT id, content, 1 - (embedding <=> $query_vector) AS similarity
FROM documents ORDER BY embedding <=> $query_vector LIMIT 10;

PostgreSQL vs MySQL的选型?

★★★★★

维度MySQLPostgreSQL
OLTP优秀(高并发读写)良好
OLAP弱(复杂查询慢)强(窗口函数/CTE/物化视图)
JSON8.0增强但仍弱JSONB+GIN索引,极强
GIS不支持PostGIS,业界标准
全文检索不原生支持原生tsvector/tsquery
向量搜索需外挂pgvector扩展
复制主从复制成熟逻辑复制+流复制
社区国内更大全球增长快

PostgreSQL的性能调优?

★★★★


四、SQL高级技巧(31-42)

EXISTS vs IN vs JOIN的性能对比?

★★★★★

行转列(PIVOT)?

★★★★


-- 原始数据:student, subject, score
-- 目标:student, 语文, 数学, 英语
SELECT student,
  MAX(CASE WHEN subject='语文' THEN score END) AS 语文,
  MAX(CASE WHEN subject='数学' THEN score END) AS 数学,
  MAX(CASE WHEN subject='英语' THEN score END) AS 英语
FROM scores GROUP BY student;

列转行(UNPIVOT)?

★★★★


-- 原始:student, 语文, 数学, 英语
-- 目标:student, subject, score
SELECT student, subject, score FROM (
  SELECT student,
    UNNEST(ARRAY['语文','数学','英语']) AS subject,
    UNNEST(ARRAY[语文, 数学, 英语]) AS score
  FROM wide_table
) t;  -- PostgreSQL

-- MySQL用UNION ALL
SELECT student, '语文' AS subject, 语文 AS score FROM wide_table
UNION ALL
SELECT student, '数学', 数学 FROM wide_table
UNION ALL
SELECT student, '英语', 英语 FROM wide_table;

分组拼接(STRING_AGG/GROUP_CONCAT)?

★★★★


SELECT dept, STRING_AGG(name, ',') AS members
FROM employees GROUP BY dept;

删除重复数据?

★★★★★


-- 方法一:窗口函数(推荐)
DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) t WHERE rn > 1
);

-- 方法二:自连接
DELETE u1 FROM users u1 JOIN users u2
  ON u1.email = u2.email AND u1.id > u2.id;

分页查询的优化?

★★★★★

SQL的执行顺序?

★★★★★

执行计划(EXPLAIN)怎么看?

★★★★★

SQL注入防御?

★★★★★


// 正确
PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE name = ?");
ps.setString(1, userName);

// 错误(SQL注入)
String sql = "SELECT * FROM users WHERE name = '" + userName + "'";

慢SQL的排查和优化流程?

★★★★★

跨数据库查询(DBLink/FDW)?

★★★★


CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.1.100', dbname 'analytics');
CREATE USER MAPPING FOR current_user SERVER remote_db OPTIONS (user 'reader', password 'xxx');
IMPORT FOREIGN SCHEMA public LIMIT TO (orders) FROM SERVER remote_db INTO local_schema;

SQL面试必会题总结?

★★★★★