sql-pro

SQL Pro

用途

提供跨主流数据库平台（PostgreSQL、MySQL、SQL Server、Oracle）的专业 SQL 开发能力，专注于复杂查询设计、性能优化和数据库架构。精通 ANSI SQL 标准、平台特定优化和现代数据模式，注重效率和可扩展性。

适用场景

• 编写包含联接、CTE、窗口函数或递归查询的复杂 SQL 查询
• 为新应用设计数据库架构，或重构现有架构
• 通过执行计划分析优化运行缓慢的 SQL 查询
• 在不同数据库平台之间迁移数据（MySQL → PostgreSQL）
• 实现存储过程、函数或触发器
• 使用高级聚合和窗口函数生成分析报告
• 将业务需求转化为 SQL 查询逻辑
• 解决跨平台 SQL 兼容性问题（不同方言）

快速开始

在以下情况下调用此技能：

• 编写包含 CTE、窗口函数或递归模式的复杂查询
• 设计或重构数据库架构
• 通过执行计划分析优化运行缓慢的查询
• 在不同数据库平台之间迁移数据
• 实现存储过程、函数或触发器
• 使用高级聚合生成分析报告

在以下情况下请勿调用：

• 需要 PostgreSQL 专属功能 → 使用 postgres-pro
• MySQL 专属管理任务 → 使用 database-administrator
• 简单 CRUD 操作 → 使用 backend-developer
• ORM 查询模式 → 使用相应的语言技能

决策框架

CTE、子查询与 JOIN 决策树

Query Requirement Analysis
│
├─ Need to reference result multiple times?
│  └─ YES → Use CTE (avoids duplicate subquery evaluation)
│     WITH user_totals AS (SELECT ...)
│     SELECT * FROM user_totals WHERE ...
│     UNION ALL
│     SELECT * FROM user_totals WHERE ...
│
├─ Recursive data traversal (hierarchy, graph)?
│  └─ YES → Use Recursive CTE (ONLY option for recursion)
│     WITH RECURSIVE tree AS (
│       SELECT ... -- anchor
│       UNION ALL
│       SELECT ... FROM tree ... -- recursive
│     )
│
├─ Simple lookup or filter?
│  └─ Use JOIN (most optimizable by query planner)
│     SELECT u.*, o.total
│     FROM users u
│     JOIN orders o ON u.id = o.user_id
│
├─ Correlated subquery in WHERE clause?
│  ├─ Checking existence → Use EXISTS (stops at first match)
│  │  WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id)
│  │
│  └─ Value comparison → Use JOIN instead
│     -- BAD: WHERE (SELECT COUNT(*) FROM orders WHERE user_id = users.id) > 5
│     -- GOOD: JOIN (SELECT user_id, COUNT(*) as cnt FROM orders GROUP BY user_id)
│
└─ Readability vs Performance trade-off?
   ├─ Complex logic, readability critical → CTE
   │  (Easier to understand, debug, maintain)
   │
   └─ Performance critical, simple logic → Subquery or JOIN
      (Query planner can inline and optimize)

窗口函数与 GROUP BY 决策矩阵

需求	解决方案	示例
需要聚合结果和行级明细	窗口函数	SELECT name, salary, AVG(salary) OVER () as avg_salary FROM employees
只需要聚合结果	GROUP BY	SELECT dept, AVG(salary) FROM employees GROUP BY dept
排名／行编号	窗口函数（ROW_NUMBER、RANK、DENSE_RANK）	ROW_NUMBER() OVER (ORDER BY sales DESC)
累计总计／移动平均值	带有框架的窗口函数	SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
LAG／LEAD（访问前一行／后一行）	窗口函数	LAG(price, 1) OVER (ORDER BY date) as prev_price
百分位数／NTILE	窗口函数	NTILE(4) OVER (ORDER BY score) as quartile
按组进行简单计数／求和／求平均值	GROUP BY（效率更高）	SELECT category, COUNT(*) FROM products GROUP BY category

危险信号 → 升级交由 Oracle 处理

观察结果	升级原因	示例
执行计划中出现笛卡尔积	意外的交叉联接导致行数呈指数级增长	“查询返回数百万行”
多层递归 CTE 性能复杂	需要高级优化	“递归 CTE 遍历 10 层以上、包含 10 万个节点”
跨平台迁移涉及不兼容功能	需要映射平台特定功能	“将 Oracle CONNECT BY 迁移为 PostgreSQL 递归 CTE”
查询包含 10 个以上联接和复杂逻辑	架构存在问题，可能需要重新设计	“单个查询联接 15 张表”
时态查询包含复杂的时间序列逻辑	需要高级分析模式	“带历史快照的 SCD Type 2”

核心能力

高级查询模式

• 公用表表达式（CTE）和递归查询
• 窗口函数：ROW_NUMBER、RANK、LEAD、LAG、聚合窗口
• PIVOT／UNPIVOT 数据转换操作
• 用于树形／图结构的层次查询
• 用于时间分析的时态查询

查询优化

• 执行计划分析与解读
• 索引选择策略和覆盖索引
• 统计信息管理与维护
• 查询提示和计划指南（必要时）
• 并行查询执行调优

索引设计模式

• 聚簇索引与非聚簇索引
• 用于优化查询的覆盖索引
• 用于筛选性查询的过滤索引／部分索引
• 基于函数／表达式的索引
• 复合索引的列顺序

质量检查清单

查询性能：

• 执行时间符合要求（OLTP：<100ms，分析：<5s）
• 已审查所有复杂查询的 EXPLAIN ANALYZE
• 大型表上没有顺序扫描（除非有意为之）
• 索引得到有效利用（检查执行计划）
• 没有 N+1 查询模式（已消除相关子查询）

SQL 质量：

• SELECT 中只包含必要的列（不使用 SELECT *）
• 多表查询中使用了显式表别名
• 正确处理 NULL（COALESCE、IS NULL 与 = NULL）
• 比较中的数据类型匹配（没有隐式转换）
• 使用了参数化查询（防止 SQL 注入）

优化：

• 在适用时使用窗口函数代替自连接
• 使用 EXISTS 代替 NOT IN，以更好地处理 NULL
• 为频繁执行的查询建议使用覆盖索引
• 重写查询以消除相关子查询

文档：

• 在注释中说明复杂查询逻辑
• CTE 名称描述清晰且能自我说明
• 已记录预期输出格式
• 已记录性能特征

更多资源

• 详细技术参考：参见 REFERENCE.md
• 代码示例与模式：参见 EXAMPLES.md