{"id":"sql-pro","name":"sql-pro","summary":"SQLクエリの最適化、データベーススキーマの設計、パフォーマンス問題のトラブルシューティングを行います。","body":"# SQL Pro\n\n## Core Workflow\n\n1. **Schema Analysis** - Review database structure, indexes, query patterns, performance bottlenecks\n2. **Design** - Create set-based operations using CTEs, window functions, appropriate joins\n3. **Optimize** - Analyze execution plans, implement covering indexes, eliminate table scans\n4. **Verify** - Run `EXPLAIN ANALYZE` and confirm no sequential scans on large tables; if query does not meet sub-100ms target, iterate on index selection or query rewrite before proceeding\n5. **Document** - Provide query explanations, index rationale, performance metrics\n\n## Reference Guide\n\nLoad detailed guidance based on context:\n\n| Topic | Reference | Load When |\n|-------|-----------|-----------|\n| Query Patterns | `references/query-patterns.md` | JOINs, CTEs, subqueries, recursive queries |\n| Window Functions | `references/window-functions.md` | ROW_NUMBER, RANK, LAG/LEAD, analytics |\n| Optimization | `references/optimization.md` | EXPLAIN plans, indexes, statistics, tuning |\n| Database Design | `references/database-design.md` | Normalization, keys, constraints, schemas |\n| Dialect Differences | `references/dialect-differences.md` | PostgreSQL vs MySQL vs SQL Server specifics |\n\n## Quick-Reference Examples\n\n### CTE Pattern\n```sql\n-- Isolate expensive subquery logic for reuse and readability\nWITH ranked_orders AS (\n    SELECT\n        customer_id,\n        order_id,\n        total_amount,\n        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn\n    FROM orders\n    WHERE status = 'completed'          -- filter early, before the join\n)\nSELECT customer_id, order_id, total_amount\nFROM ranked_orders\nWHERE rn = 1;                           -- latest completed order per customer\n```\n\n### Window Function Pattern\n```sql\n-- Running total and rank within partition — no self-join required\nSELECT\n    department_id,\n    employee_id,\n    salary,\n    SUM(salary)  OVER (PARTITION BY department_id ORDER BY hire_date) AS running_payroll,\n    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank\nFROM employees;\n```\n\n### EXPLAIN ANALYZE Interpretation\n```sql\n-- PostgreSQL: always use ANALYZE to see actual row counts vs. estimates\nEXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)\nSELECT *\nFROM orders o\nJOIN customers c ON c.id = o.customer_id\nWHERE o.created_at > NOW() - INTERVAL '30 days';\n```\nKey things to check in the output:\n- **Seq Scan on large table** → add or fix an index\n- **actual rows ≫ estimated rows** → run `ANALYZE <table>` to refresh statistics\n- **Buffers: shared hit** vs **read** → high `read` count signals missing cache / index\n\n### Before / After Optimization Example\n```sql\n-- BEFORE: correlated subquery, one execution per row (slow)\nSELECT order_id,\n       (SELECT SUM(quantity) FROM order_items oi WHERE oi.order_id = o.id) AS item_count\nFROM orders o;\n\n-- AFTER: single aggregation join (fast)\nSELECT o.order_id, COALESCE(agg.item_count, 0) AS item_count\nFROM orders o\nLEFT JOIN (\n    SELECT order_id, SUM(quantity) AS item_count\n    FROM order_items\n    GROUP BY order_id\n) agg ON agg.order_id = o.id;\n\n-- Supporting covering index (includes all columns touched by the query)\nCREATE INDEX idx_order_items_order_qty\n    ON order_items (order_id)\n    INCLUDE (quantity);\n```\n\n## Constraints\n\n### MUST DO\n- Analyze execution plans before recommending optimizations\n- Use set-based operations over row-by-row processing\n- Apply filtering early in query execution (before joins where possible)\n- Use EXISTS over COUNT for existence checks\n- Handle NULLs explicitly in comparisons and aggregations\n- Create covering indexes for frequent queries\n- Test with production-scale data volumes\n\n### MUST NOT DO\n- Use SELECT * in production queries\n- Use cursors when set-based operations work\n- Ignore platform-specific optimizations when targeting a specific dialect\n- Implement solutions without considering data volume and cardinality\n\n## Output Templates\n\nWhen implementing SQL solutions, provide:\n1. Optimized query with inline comments\n2. Required indexes with rationale\n3. Execution plan analysis\n4. Performance metrics (before/after)\n5. Platform-specific notes if applicable\n\n[Documentation](https://jeffallan.github.io/claude-skills/skills/language/sql-pro/)","author":"@Jeffallan","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/Jeffallan/claude-skills/tree/main/skills/sql-pro","license":"MIT","category":"writing","lang":"en","tokens":973,"stars":0,"calls30d":2,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[{"path":"references/database-design.md","size":12540,"sha256":"b8d0676ac91213999c28e03dae2a7685d78dda05b6abe3f6b34690d2ae19d0f6"},{"path":"references/dialect-differences.md","size":11951,"sha256":"5eac1a75d44e65dab3877353bf9e5963fa95dd990b5b1d853b17f744761f9586"},{"path":"references/optimization.md","size":10820,"sha256":"dd197a02491cda9e1d26e2f0fdff4a5a90f4a54ccf4f6d57e5041a62c06be9a6"},{"path":"references/query-patterns.md","size":7439,"sha256":"19c3b2e803336b5de9edb9fb663d2942f010459bf50f0d2961d5b0c1da6e3a8f"},{"path":"references/window-functions.md","size":8860,"sha256":"23f1987aa8223d256502f9ef1504b5aff35999ad6917d85a62c6df4ede7b1769"}],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":false,"networkEndpoints":["jeffallan.github.io"]}}