claude-skills 的 SQL Pro 技能全解析查询优化、窗口函数与多方言数据库实战指南【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skillsSQL Pro 是 claude-skills 仓库中面向语言领域domain: language的专业化技能专门用于 SQL 查询优化、数据库 Schema 设计与性能故障排查。本文以 skills/sql-pro/SKILL.md 为核心骨架系统讲解其五步核心工作流、五大参考指南的实战要点CTE、窗口函数、EXPLAIN 分析、规范化设计、PostgreSQL/MySQL/SQL Server/Oracle 方言差异并结合仓库源码给出可直接复制运行的 SQL 示例读完你将掌握一套可复用的「分析 → 设计 → 优化 → 验证 → 文档」数据库调优方法论。技能定位与适用场景在 claude-skills 的技能体系中SQL Pro 是一个role: specialist、scope: implementation、output-format: code的语言专家技能元数据版本为 1.1.0声明了明确的触发词SQL 优化、查询性能、数据库设计、PostgreSQL、MySQL、SQL Server、窗口函数、CTE、查询调优、EXPLAIN 计划、数据库索引。根据 SKILLS_GUIDE.md 中的决策树当用户请求「Database Work」或「Advanced SQL window functions」时应路由到 SQL Pro而针对 PostgreSQL 专属的深层运维EXPLAIN ANALYZE、JSONB、复制、VACUUM 等则与 Postgres Pro 组合使用相关技能related-skills中还声明了 devops-engineer。从技能描述看SQL Pro 主要在以下场景被调用用户询问查询为何变慢、需要编写复杂 JOIN 或聚合提到数据库性能问题需要设计或迁移 Schema涉及窗口函数、CTE、索引策略、执行计划分析、覆盖索引、递归查询、EXPLAIN/ANALYZE 解读、优化前后基准对比以及跨 PostgreSQL/MySQL/SQL Server/Oracle 的查询移植。五步核心工作流SKILL.md 将 SQL 专家的作业方式收敛为五个可重复的阶段从问题定位到方案交付形成闭环Schema 分析Schema Analysis——审查数据库结构、索引、查询模式与性能瓶颈设计Design——使用 CTE、窗口函数和合适的 JOIN 构建基于集合set-based的操作优化Optimize——分析执行计划、实现覆盖索引、消除大表上的顺序扫描table scans验证Verify——运行EXPLAIN ANALYZE确认大表上不存在顺序扫描若查询未达到100ms 以内的目标则继续迭代索引选择或查询重写直至达标文档Document——提供查询说明、索引设计理由与性能指标。其中「验证」环节是区别于普通代码生成的关键任何优化建议都必须以执行计划的实际输出为准而不是凭直觉。这一点在optimization.md的最佳实践清单中进一步强调「Always run EXPLAIN ANALYZE before optimizing」优化前总是先跑 EXPLAIN ANALYZE。参考指南与按需加载机制SKILL.md 维护了一张参考指南表Agent 依据当前上下文按需加载对应文档而不是一次性读入全部内容主题参考文档加载时机查询模式query-patterns.mdJOIN、CTE、子查询、递归查询窗口函数window-functions.mdROW_NUMBER、RANK、LAG/LEAD、分析函数优化optimization.mdEXPLAIN 计划、索引、统计信息、调优数据库设计database-design.md规范化、键、约束、Schema方言差异dialect-differences.mdPostgreSQL vs MySQL vs SQL Server 专属细节下文将逐项展开这五大主题的核心内容。查询模式从 CTE 到高级 JOIN基础 CTE 与多引用复用CTECommon Table Expression的作用是隔离昂贵的子查询逻辑、提升可读性与复用性。query-patterns.md给出了一个典型示例先定义活跃用户与用户订单两个 CTE再通过 LEFT JOIN 组合出用户生命周期价值注意COALESCE处理无订单用户WITH active_users AS ( SELECT user_id, username, created_at FROM users WHERE is_active true AND last_login CURRENT_DATE - INTERVAL 30 days ), user_orders AS ( SELECT user_id, COUNT(*) as order_count, SUM(total) as total_spent FROM orders WHERE status completed GROUP BY user_id ) SELECT u.username, u.created_at, COALESCE(o.order_count, 0) as orders, COALESCE(o.total_spent, 0) as lifetime_value FROM active_users u LEFT JOIN user_orders o ON u.user_id o.user_id WHERE COALESCE(o.order_count, 0) 0 ORDER BY o.total_spent DESC;CTE 的另一个高级用法是自我引用self-reference以消除重复计算。例如将月度销售数据定义为一个 CTE然后在同一查询中对它自身做 LEFT JOIN 计算环比增长配合NULLIF规避除零错误WITH monthly_sales AS ( SELECT DATE_TRUNC(month, sale_date) as month, product_id, SUM(quantity) as total_quantity, SUM(amount) as total_amount FROM sales WHERE sale_date 2024-01-01 GROUP BY DATE_TRUNC(month, sale_date), product_id ) SELECT current.month, current.product_id, current.total_amount, current.total_amount - COALESCE(previous.total_amount, 0) as growth, ROUND(100.0 * (current.total_amount - COALESCE(previous.total_amount, 0)) / NULLIF(previous.total_amount, 0), 2) as growth_pct FROM monthly_sales current LEFT JOIN monthly_sales previous ON current.product_id previous.product_id AND current.month previous.month INTERVAL 1 month;需要留意的是PostgreSQL 12 默认会对 CTE 做物化materialize参考文档建议在需要时用WITH cte AS MATERIALIZED或NOT MATERIALIZED显式控制物化行为以免优化器被迫将 CTE 作为独立边界计算。递归 CTE组织层级与物料清单递归 CTE 由「锚点成员anchor member 递归成员」两部分组成是遍历层级数据的利器。query-patterns.md提供了两个经典场景。组织架构遍历从顶层管理者manager_id IS NULL出发逐层下钻并用ARRAY[employee_id]记录路径以防止环路WHERE NOT e.employee_id ANY(h.path)WITH RECURSIVE org_hierarchy AS ( SELECT employee_id, name, manager_id, 1 as level, ARRAY[employee_id] as path, name as hierarchy_path FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.employee_id, e.name, e.manager_id, h.level 1, h.path || e.employee_id, h.hierarchy_path || || e.name FROM employees e INNER JOIN org_hierarchy h ON e.manager_id h.employee_id WHERE NOT e.employee_id ANY(h.path) ) SELECT employee_id, REPEAT( , level - 1) || name as indented_name, level, hierarchy_path FROM org_hierarchy ORDER BY path;物料清单BOM爆炸从根部件PRODUCT-123出发沿bill_of_materials表递归乘算数量得到每个组件的总用量WITH RECURSIVE parts_explosion AS ( SELECT part_id, component_id, quantity, 1 as level, ARRAY[part_id] as path FROM bill_of_materials WHERE part_id PRODUCT-123 UNION ALL SELECT pe.part_id, bom.component_id, pe.quantity * bom.quantity, pe.level 1, pe.path || bom.part_id FROM parts_explosion pe INNER JOIN bill_of_materials bom ON pe.component_id bom.part_id WHERE NOT bom.part_id ANY(pe.path) ) SELECT component_id, SUM(quantity) as total_quantity, MAX(level) as max_depth FROM parts_explosion GROUP BY component_id;高级 JOIN 模式自连接找序列缺口orders a LEFT JOIN orders b ON b.order_id a.order_id用MIN(b.order_id) - a.order_id 1找出缺失的 ID 区间LATERAL 连接PostgreSQL替代相关子查询取每客户最近 3 笔订单CROSS JOIN LATERAL内层可以引用外层列并LIMIT 3反连接Anti-join查「有用户无订单」可用LEFT JOIN ... WHERE o.order_id IS NULL查「用户从未下单」用NOT EXISTS更高效大集合场景下 EXISTS 优于 IN。子查询优化query-patterns.md明确指出 SELECT 列表中的标量子查询会引发 N1 问题应改用带聚合的 JOIN而用于过滤的相关子查询如「订单金额高于该客户平均」可以用窗口函数改写得更简洁-- 相关子查询版本 SELECT order_id, customer_id, total FROM orders o1 WHERE total (SELECT AVG(total) FROM orders o2 WHERE o2.customer_id o1.customer_id); -- 窗口函数版本更优 SELECT order_id, customer_id, total FROM ( SELECT order_id, customer_id, total, AVG(total) OVER (PARTITION BY customer_id) as avg_customer_total FROM orders ) x WHERE total avg_customer_total;PIVOT/UNPIVOT 与集合操作旋转透视表有两种路径PostgreSQL 可用tablefunc扩展的crosstab()需先CREATE EXTENSION IF NOT EXISTS tablefunc也可以用手写的SUM(CASE WHEN ...)方式——后者可移植性更好。UNPIVOT 则用多个UNION ALL分支把列转成行。集合操作方面UNION去重、UNION ALL不去重性能更好、INTERSECT求交集、EXCEPT求差集四种语义各司其职。窗口函数无需自连接的组内分析窗口函数是 SQL Pro 的高频技能点window-functions.md系统梳理了全部家族成员。排名函数ROW_NUMBER / RANK / DENSE_RANK / NTILEROW_NUMBER()分区内顺序编号常用于「每组 Top N」RANK()同值同排名但留有间隔DENSE_RANK()同值同排名但无间隔NTILE(n)将结果平分成 n 桶如四分位。三者差异的典型输出score100 并列两名时score100: rank1, dense_rank1, row_num1 score100: rank1, dense_rank1, row_num2 score95: rank3, dense_rank2, row_num3「每客户最新一笔订单」是 ROW_NUMBER 的招牌用法与 SKILL.md 中的 CTE 示例完全一致SELECT * FROM ( SELECT customer_id, order_id, order_date, total, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn FROM orders ) ranked WHERE rn 1;聚合窗口累计值、滚动均值与占比SUM(...) OVER (ORDER BY date)给出累计和AVG(...) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)给出 7 日滚动平均RANGE BETWEEN INTERVAL 7 days PRECEDING AND CURRENT ROW则是按时间值而非行数取窗口。分区内的quantity::FLOAT / SUM(quantity) OVER (PARTITION BY product_id)可直接计算占比。LAG/LEAD前后行对比与会话分析LAG(total)取上一行、LEAD(total)取下一行差值即环比变化。更实用的是会话切分按用户分区后计算相邻动作的时间差EXTRACT(EPOCH FROM ...)/60得到分钟数超过 30 分钟即标记为新会话SELECT user_id, action_time, LAG(action_time) OVER (PARTITION BY user_id ORDER BY action_time) as prev_action, CASE WHEN EXTRACT(EPOCH FROM ( action_time - LAG(action_time) OVER (PARTITION BY user_id ORDER BY action_time) )) / 60 30 THEN 1 ELSE 0 END as new_session FROM user_actions;帧Frame规范与高级分析ROWS按物理行偏移RANGE按逻辑值范围偏移——同样的BETWEEN 2 PRECEDING AND 2 FOLLOWINGRANGE对日期可写INTERVAL 2 daysFIRST_VALUE/LAST_VALUE必须配合ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING才能覆盖整个分区否则默认帧只到当前行PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER ()可直接计算中位数与 p90FILTER (WHERE ...)子句可在窗口内做条件聚合如SUM(quantity) FILTER (WHERE quantity 10) OVER (PARTITION BY product_id ORDER BY sale_date)。参考文档还给出一个性能要点避免多次窗口扫描——把AVG(price) OVER ()、MAX(price) OVER ()合并到一次全表窗口计算中而不是用多个标量子查询对于高频昂贵的窗口计算可物化到CREATE MATERIALIZED VIEW并对结果列建索引。查询优化EXPLAIN、索引与调优全流程optimization.md是技能中篇幅最重、最贴近实战的章节。EXPLAIN 计划解读PostgreSQL 推荐使用带ANALYZE, BUFFERS, VERBOSE的 EXPLAINEXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT c.customer_id, c.name, COUNT(o.order_id) as order_count, SUM(o.total) as lifetime_value FROM customers c LEFT JOIN orders o ON c.customer_id o.customer_id WHERE c.created_at 2024-01-01 GROUP BY c.customer_id, c.name HAVING COUNT(o.order_id) 5;重点观察五类指标Planning Time / Execution Time计划生成与实际运行耗时Seq Scan大表上的顺序扫描性能差的信号Index Scan使用索引好的信号Rows 估算 vs 实际差异过大说明统计信息过期actual rows ≫ estimated rows→ 执行ANALYZE tableBuffersshared hit是缓存命中read是磁盘 I/O高read数往往意味着缺缓存或缺索引。MySQL 使用EXPLAIN FORMATJSONSQL Server 用SET STATISTICS IO ONSET STATISTICS TIME ON并可查询sys.dm_exec_query_stats核对估算/实际行数。索引设计覆盖、复合、部分、表达式与 GINoptimization.md给出了完整的索引谱系-- 覆盖索引索引内包含所有查询列可走 Index Only Scan CREATE INDEX idx_orders_covering ON orders (customer_id, order_date) INCLUDE (total, status); -- 复合索引列顺序关键 CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC); -- 有效: WHERE customer_id X AND order_date Y / WHERE customer_id X -- 无效: WHERE order_date Y无法命中 -- 部分/过滤索引更小更快仅查询带同款过滤条件时命中 CREATE INDEX idx_active_orders ON orders (customer_id, order_date) WHERE status active; -- 表达式索引 CREATE INDEX idx_users_lower_email ON users (LOWER(email)); -- GIN 索引数组/JSONB 包含查询 CREATE INDEX idx_products_tags ON products USING GIN (tags); SELECT * FROM products WHERE tags ARRAY[electronics, sale];索引维护缺失、未用、重复与重建利用 PostgreSQL 的系统视图即可做日常体检-- 找出高频顺序扫描的表疑似缺索引 SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan as avg_seq_read FROM pg_stat_user_tables WHERE seq_scan 0 AND seq_tup_read / seq_scan 10000 ORDER BY seq_tup_read DESC; -- 找出从未被使用的索引浪费写开销与磁盘 SELECT schemaname, tablename, indexname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) as index_size FROM pg_stat_user_indexes WHERE idx_scan 0 AND indexrelname NOT LIKE pg_toast% ORDER BY pg_relation_size(indexrelid) DESC; -- 清理膨胀并刷新统计 REINDEX INDEX CONCURRENTLY idx_orders_customer_date; ANALYZE VERBOSE;查询重写模式用 EXISTS 代替 SELECT DISTINCTSELECT DISTINCT customer_id FROM orders WHERE statusactive会强制排序去重改为对 customers 表做 EXISTS 检查用 NOT EXISTS 代替 NOT INNOT IN遇 NULL 语义易错且性能差过滤条件下推在 CTE/子查询中先WHERE收窄数据再参与 JOIN消除 SELECT 列表标量子查询改用LEFT JOIN GROUP BY一次聚合。分区、物化视图与监控大表参考文档建议超过 1000 万行可考虑分区CREATE TABLE orders ( order_id SERIAL, customer_id INT, order_date DATE NOT NULL, total DECIMAL(10,2) ) PARTITION BY RANGE (order_date); CREATE TABLE orders_2024_q1 PARTITION OF orders FOR VALUES FROM (2024-01-01) TO (2024-04-01);范围RANGE、列表LIST、哈希HASH三种分区策略各有适用场景查询WHERE order_date落在某个分区内时优化器会自动做分区裁剪partition pruning只扫描对应分区。昂贵聚合用物化视图承载并配合唯一索引与REFRESH ... CONCURRENTLY刷新还可用触发器在写入后自动刷新。监控方面pg_stat_statements按mean_exec_time排序找出 Top 慢查询pg_locks多表连接可定位阻塞会话n_dead_tup占比可检测表膨胀。optimization.md最终沉淀为十条最佳实践清单核心包括优化前先跑 EXPLAIN ANALYZE、为外键与 WHERE/JOIN 列建索引、高频查询建覆盖索引、定期 ANALYZE、禁 SELECT *、EXISTS 优先于 IN、尽早过滤晚聚合、大表分区、聚合物化、持续监控慢查询日志。数据库设计从规范化到工程模式database-design.md覆盖了从范式理论到生产落地的设计决策。三范式1NF/2NF/3NF1NF原子值无重复组——不要把多个电话号塞进一个VARCHAR(500)列应拆出customer_phones子表2NF消除对复合主键的部分依赖——order_items_bad中product_name只依赖product_id应拆出products表order_items只保留quantity与下单时快照的unit_price3NF消除传递依赖——地址表中city/state由zip_code决定应拆出zip_codes参考表。参考文档给出工程建议「Normalize to 3NF, then denormalize strategically for performance」先规范化到 3NF再有策略地为性能做反规范化。主外键与约束自然键 vs 代理键country_code CHAR(2)是自然键自增SERIAL是代理键分布式系统可用UUID PRIMARY KEY DEFAULT gen_random_uuid()避免序列冲突级联动作ON DELETE CASCADE删除父记录时清理子记录ON DELETE RESTRICT阻止删除被引用的父记录CHECK 约束可在数据库层强制业务规则如CHECK (email ~* ^[A-Za-z0-9._%-]...)、CHECK (hire_date birth_date INTERVAL 16 years)排除约束Exclusion ConstraintPostgreSQL 独有EXCLUDE USING GIST (room_id WITH , booked_during WITH )可防止同一房间的预约时间重叠唯一索引CREATE UNIQUE INDEX idx_users_active_email ON users(LOWER(email)) WHERE deleted_at IS NULL保证活跃用户邮箱不重复。常见设计模式多态关联commentable_type commentable_id灵活但无法用外键保证完整性参考文档建议拆分为post_comments、photo_comments等带真实外键的表多对多带属性enrollments桥接表除了两个外键还携带enrollment_date、grade、status等属性并用UNIQUE (student_id, course_id)防重复注册自引用层级categories.parent_category_id REFERENCES categories(category_id)配合CHECK (category_id ! parent_category_id)防止自引用。时态数据、软删除与审计SCD2缓慢变化维度类型 2customer_history用valid_from/valid_to/is_current保留完整历史CHECK (valid_to IS NULL OR valid_to valid_from)保证区间合法软删除deleted_at TIMESTAMPNULL 表示活跃并建部分索引CREATE INDEX idx_posts_active ON posts(created_at DESC) WHERE deleted_at IS NULL加速活跃记录查询再用视图封装WHERE deleted_at IS NULL审计日志audit_log表用 JSONB 保存old_values/new_values通过AFTER INSERT OR UPDATE OR DELETE触发器函数自动写入变更记录。方言差异跨数据库移植指南dialect-differences.md是一份难得的移植对照表适合「同一套 SQL 逻辑要在多引擎上运行」的场景。自增主键-- PostgreSQLSERIAL 或 GENERATED ALWAYS AS IDENTITY user_id SERIAL PRIMARY KEY; -- MySQL user_id INT AUTO_INCREMENT PRIMARY KEY; -- SQL Server user_id INT IDENTITY(1,1) PRIMARY KEY; -- Oracle user_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY;字符串拼接、日期与分页拼接PostgreSQL 与 Oracle 用||NULL 敏感PostgreSQL 的CONCAT()是 NULL 安全的MySQL 只有CONCAT()是算术运算SQL Server 用日期运算PostgreSQL INTERVAL 7 days/ MySQLDATE_ADD(order_date, INTERVAL 7 DAY)/ SQL ServerDATEADD(day, 7, order_date)/ Oracle 直接 7分页PostgreSQL 与 MySQLLIMIT 10 OFFSET 20SQL Server 2012 与 Oracle 12c 用OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY老版本分别用ROW_NUMBER()子查询与ROWNUM三层嵌套。布尔、JSON 与大小写布尔PostgreSQL 原生BOOLEANMySQL 用TINYINT(1)BOOLEAN只是别名SQL Server 用BITOracle 表内无布尔类型用NUMBER(1) CHECK (is_active IN (0,1))JSONPostgreSQLJSONB配 GIN 索引包含运算符MySQL 8.0 用JSON_EXTRACTSQL Server 2016 用JSON_VALUEISJSON约束Oracle 12c 用JSON_VALUE/JSON_EXISTS大小写PostgreSQL、Oracle 默认区分大小写可用LOWER()或 PostgreSQL 的ILIKEMySQL、SQL Server 通常不区分可用COLLATE utf8_bin等显式切换。递归 CTE、窗口帧与 UPSERT递归PostgreSQL 与 MySQL 8.0 语法一致SQL Server 省略RECURSIVE关键字Oracle 传统上使用CONNECT BY PRIOR窗口帧PostgreSQL、SQL Server、Oracle 支持RANGE BETWEEN INTERVAL ... PRECEDING而 MySQL 8.0 的 RANGE 帧不支持间隔值需退化为ROWS BETWEEN 6 PRECEDING AND CURRENT ROWUPSERTPostgreSQLON CONFLICT ... DO UPDATEMySQLON DUPLICATE KEY UPDATESQL Server 与 Oracle 用MERGE。数据类型映射速查概念PostgreSQLMySQLSQL ServerOracle整数INT, BIGINTINT, BIGINTINT, BIGINTNUMBER(10), NUMBER(19)小数NUMERIC, DECIMALDECIMALDECIMAL, NUMERICNUMBER(p,s)字符串VARCHAR, TEXTVARCHAR, TEXTVARCHAR, NVARCHARVARCHAR2, CLOB布尔BOOLEANBOOLEAN/TINYINT(1)BITNUMBER(1)JSONJSON, JSONBJSONNVARCHAR(MAX)CLOBUUIDUUIDCHAR(36), BINARY(16)UNIQUEIDENTIFIERRAW(16)约束与输出模板为保证交付质量SKILL.md 明确规定了技能必须遵守的行为边界。MUST DO必须遵守推荐优化方案前先分析执行计划优先基于集合的操作而非逐行处理在查询早期尽可能在 JOIN 之前应用过滤存在性检查用 EXISTS 而非 COUNT在比较与聚合中显式处理 NULL为高频查询创建覆盖索引用生产级数据量进行测试。MUST NOT DO禁止生产查询中使用SELECT *能用集合操作却使用游标面向特定方言时不考虑平台专属优化在忽略数据量与基数的情况下实现方案。当完成一次 SQL 方案交付时输出内容必须包含五要素Output Templates带行内注释的优化后查询、带设计理由的必要索引、执行计划分析、优化前后性能指标、平台专属注意事项如适用。小结SQL Pro 技能的价值在于把数据库调优从「碰运气改写」变成可验证的工程流程先通过 EXPLAIN ANALYZE 建立事实基线再用 CTE、窗口函数和集合化改写压缩查询复杂度用覆盖索引、部分索引与分区消除扫描成本最后以 100ms 目标为验收标准循环迭代。配合 query-patterns.md、window-functions.md、optimization.md、database-design.md 与 dialect-differences.md 五份深度参考再结合 Postgres Pro 处理 PostgreSQL 专属运维场景即可覆盖从 Schema 设计、查询优化到跨引擎移植的完整数据库工作闭环。【免费下载链接】claude-skills67 Specialized Skills for Full-Stack Developers. Transform Claude Code into your expert pair programmer.项目地址: https://gitcode.com/GitHub_Trending/claud/claude-skills创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考