Back to Details

sql-pro

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

SQL Pro

SQL Pro

Purpose

用途

Provides expert SQL development capabilities across major database platforms (PostgreSQL, MySQL, SQL Server, Oracle), specializing in complex query design, performance optimization, and database architecture. Masters ANSI SQL standards, platform-specific optimizations, and modern data patterns with focus on efficiency and scalability.
提供跨主流数据库平台（PostgreSQL、MySQL、SQL Server、Oracle）的专业SQL开发能力，专注于复杂查询设计、性能优化和数据库架构。精通ANSI SQL标准、平台特定优化以及现代数据模式，重点关注效率和可扩展性。

When to Use

适用场景

  • Writing complex SQL queries with joins, CTEs, window functions, or recursive queries
  • Designing database schema for new application or refactoring existing schema
  • Optimizing slow SQL queries with execution plan analysis
  • Data migration between different database platforms (MySQL → PostgreSQL)
  • Implementing stored procedures, functions, or triggers
  • Building analytical reports with advanced aggregations and window functions
  • Translating business requirements into SQL query logic
  • Cross-platform SQL compatibility issues (different dialects)
  • 编写包含连接、CTE、窗口函数或递归查询的复杂SQL语句
  • 为新应用设计数据库架构或重构现有架构
  • 通过执行计划分析优化缓慢的SQL查询
  • 在不同数据库平台之间进行数据迁移（如MySQL → PostgreSQL）
  • 实现存储过程、函数或触发器
  • 使用高级聚合和窗口函数构建分析报告
  • 将业务需求转化为SQL查询逻辑
  • 跨平台SQL兼容性问题（不同方言）

Quick Start

快速入门

Invoke this skill when:
  • Writing complex queries with CTEs, window functions, or recursive patterns
  • Designing or refactoring database schemas
  • Optimizing slow queries with execution plan analysis
  • Migrating data between different database platforms
  • Implementing stored procedures, functions, or triggers
  • Building analytical reports with advanced aggregations
Do NOT invoke when:
  • PostgreSQL-specific features needed → Use postgres-pro
  • MySQL-specific administration → Use database-administrator
  • Simple CRUD operations → Use backend-developer
  • ORM query patterns → Use appropriate language skill
当以下情况时调用此技能：
  • 编写包含CTE、窗口函数或递归模式的复杂查询
  • 设计或重构数据库架构
  • 通过执行计划分析优化缓慢的查询
  • 在不同数据库平台之间进行数据迁移
  • 实现存储过程、函数或触发器
  • 使用高级聚合构建分析报告
请勿在以下情况调用：
  • 需要PostgreSQL特定功能 → 使用postgres-pro
  • MySQL特定管理操作 → 使用database-administrator
  • 简单CRUD操作 → 使用backend-developer
  • ORM查询模式 → 使用对应语言技能

Decision Framework

决策框架

CTE vs Subquery vs JOIN Decision Tree

CTE vs 子查询 vs 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)
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)

Window Function vs GROUP BY Decision Matrix

窗口函数 vs GROUP BY 决策矩阵

RequirementSolutionExample
Need aggregation + row-level detailWindow function
SELECT name, salary, AVG(salary) OVER () as avg_salary FROM employees
Only aggregated results neededGROUP BY
SELECT dept, AVG(salary) FROM employees GROUP BY dept
Ranking/row numberingWindow function (ROW_NUMBER, RANK, DENSE_RANK)
ROW_NUMBER() OVER (ORDER BY sales DESC)
Running totals / moving averagesWindow function with frame
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
LAG/LEAD (access previous/next rows)Window function
LAG(price, 1) OVER (ORDER BY date) as prev_price
Percentile / NTILEWindow function
NTILE(4) OVER (ORDER BY score) as quartile
Simple count/sum/avg by groupGROUP BY (more efficient)
SELECT category, COUNT(*) FROM products GROUP BY category
需求解决方案示例
需要聚合+行级细节窗口函数
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

Red Flags → Escalate to Oracle

预警信号 → 升级至Oracle专家

ObservationWhy EscalateExample
Cartesian product in execution planUnintended cross join causing exponential rows"Query returning millions of rows"
Complex multi-level recursive CTE performanceAdvanced optimization needed"Recursive CTE traversing 10+ levels with 100K nodes"
Cross-platform migration with incompatible featuresPlatform-specific feature mapping"Migrating Oracle CONNECT BY to PostgreSQL recursive CTE"
Query with 10+ joins and complex logicArchitecture smell, potential redesign"Single query joining 15 tables"
Temporal query with complex time-series logicAdvanced analytical pattern"SCD Type 2 with historical snapshots"
现象升级原因示例
执行计划中出现笛卡尔积意外的交叉连接导致行数呈指数级增长"查询返回数百万行数据"
复杂多层递归CTE性能问题需要高级优化"递归CTE遍历10+层级、10万+节点"
包含不兼容特性的跨平台迁移需要平台特定特性映射"将Oracle CONNECT BY迁移至PostgreSQL递归CTE"
包含10+个连接和复杂逻辑的查询架构问题，可能需要重构"单个查询连接15张表"
包含复杂时间序列逻辑的时态查询高级分析模式需求"带历史快照的SCD Type 2"

Core Capabilities

核心能力

Advanced Query Patterns

高级查询模式

  • Common Table Expressions (CTEs) and recursive queries
  • Window functions: ROW_NUMBER, RANK, LEAD, LAG, aggregate windows
  • PIVOT/UNPIVOT operations for data transformation
  • Hierarchical queries for tree/graph structures
  • Temporal queries for time-based analysis
  • 通用表表达式（CTE）和递归查询
  • 窗口函数：ROW_NUMBER、RANK、LEAD、LAG、聚合窗口
  • PIVOT/UNPIVOT数据转换操作
  • 用于树/图结构的层级查询
  • 用于基于时间分析的时态查询

Query Optimization

查询优化

  • Execution plan analysis and interpretation
  • Index selection strategies and covering indexes
  • Statistics management and maintenance
  • Query hints and plan guides (when necessary)
  • Parallel query execution tuning
  • 执行计划分析与解读
  • 索引选择策略与覆盖索引
  • 统计信息管理与维护
  • 查询提示与计划指南（必要时）
  • 并行查询执行调优

Index Design Patterns

索引设计模式

  • Clustered vs. non-clustered indexes
  • Covering indexes for query optimization
  • Filtered/partial indexes for selective queries
  • Function-based/indexes on expressions
  • Composite index column ordering
  • 聚集索引 vs 非聚集索引
  • 用于查询优化的覆盖索引
  • 针对选择性查询的过滤/部分索引
  • 基于表达式的函数型索引
  • 复合索引列排序

Quality Checklist

质量检查清单

Query Performance:
  • Execution time meets requirements (OLTP: <100ms, Analytics: <5s)
  • EXPLAIN ANALYZE reviewed for all complex queries
  • No sequential scans on large tables (unless intended)
  • Indexes utilized effectively (check execution plan)
  • No N+1 query patterns (correlated subqueries eliminated)
SQL Quality:
  • Only necessary columns in SELECT (no SELECT *)
  • Explicit table aliases used in multi-table queries
  • Proper NULL handling (COALESCE, IS NULL vs = NULL)
  • Data types match in comparisons (no implicit conversions)
  • Parameterized queries used (SQL injection prevention)
Optimization:
  • Window functions used instead of self-joins where applicable
  • EXISTS used instead of NOT IN for better NULL handling
  • Covering indexes suggested for frequent queries
  • Query rewritten to eliminate correlated subqueries
Documentation:
  • Complex query logic explained in comments
  • CTE names descriptive and self-documenting
  • Expected output format documented
  • Performance characteristics documented
查询性能：
  • 执行时间符合要求（OLTP：<100ms，分析型：<5s）
  • 所有复杂查询均已通过EXPLAIN ANALYZE审核
  • 大表无全表扫描（除非有意为之）
  • 索引得到有效利用（检查执行计划）
  • 消除了N+1查询模式（关联子查询已移除）
SQL质量：
  • SELECT语句仅包含必要列（无SELECT *）
  • 多表查询中使用明确的表别名
  • 正确处理NULL值（使用COALESCE、IS NULL而非= NULL）
  • 比较操作中数据类型匹配（无隐式转换）
  • 使用参数化查询（防止SQL注入）
优化项：
  • 适用场景下使用窗口函数替代自连接
  • 使用EXISTS而非NOT IN以更好处理NULL值
  • 为频繁查询建议覆盖索引
  • 重写查询以消除关联子查询
文档：
  • 复杂查询逻辑已在注释中说明
  • CTE名称具有描述性且自文档化
  • 已记录预期输出格式
  • 已记录性能特征

Additional Resources

额外资源

  • Detailed Technical Reference: See REFERENCE.md
  • Code Examples & Patterns: See EXAMPLES.md
  • 详细技术参考：参见REFERENCE.md
  • 代码示例与模式：参见EXAMPLES.md