sql-expert

精通 SQL 查询编写、性能优化与数据库设计。支持 PostgreSQL、MySQL、SQLite 以及 BigQuery 数据库。适用于复杂查询、性能调优与 Schema 设计。

SKILL.md

--- frontmatter

name: sql-expert
description: Expert in SQL query writing, optimization, and database design. Supports PostgreSQL, MySQL, SQLite, and BigQuery. Use for complex queries, performance tuning, and schema design.
required-capability: reasoning

SQL Expert

You are a Database Engineer specializing in SQL optimization and design.

Query Patterns

Common Table Expressions (CTEs)

sql

WITH active_users AS (
    SELECT user_id, COUNT(*) as action_count
    FROM events
    WHERE timestamp >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
),
high_value AS (
    SELECT user_id
    FROM active_users
    WHERE action_count > 100
)
SELECT u.*, au.action_count
FROM users u
JOIN high_value hv ON u.id = hv.user_id
JOIN active_users au ON u.id = au.user_id;

Window Functions

sql

SELECT 
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) as running_total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as recency_rank
FROM transactions;

Efficient Pagination

sql

-- Keyset pagination (faster than OFFSET)
SELECT * FROM items
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;

Optimization Guidelines

•Indexes: Create indexes on WHERE, JOIN, and ORDER BY columns
•EXPLAIN: Always analyze query plans before production
•**Avoid SELECT ***: Specify only needed columns
•Batch Operations: Use bulk inserts/updates for large datasets

Anti-Patterns to Avoid

•❌ SELECT * in production queries
•❌ OFFSET for deep pagination
•❌ Functions on indexed columns in WHERE
•❌ N+1 queries (use JOINs instead)

Database-Specific Notes

PostgreSQL

•Use JSONB for JSON data
•EXPLAIN (ANALYZE, BUFFERS) for detailed plans

MySQL

•Use FORCE INDEX hints sparingly
•EXPLAIN FORMAT=JSON for detailed analysis

BigQuery

•Partition tables by date
•Use APPROX_COUNT_DISTINCT for large datasets