企业官网建设流程全解析

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-skills

SQL 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 专家的作业方式收敛为五个可重复的阶段，从问题定位到方案交付形成闭环：

1. Schema 分析（Schema Analysis）——审查数据库结构、索引、查询模式与性能瓶颈；
2. 设计（Design）——使用 CTE、窗口函数和合适的 JOIN 构建基于集合（set-based）的操作；
3. 优化（Optimize）——分析执行计划、实现覆盖索引、消除大表上的顺序扫描（table scans）；
4. 验证（Verify）——运行EXPLAIN ANALYZE，确认大表上不存在顺序扫描；若查询未达到100ms 以内的目标，则继续迭代索引选择或查询重写，直至达标；
5. 文档（Document）——提供查询说明、索引设计理由与性能指标。

其中「验证」环节是区别于普通代码生成的关键：任何优化建议都必须以执行计划的实际输出为准，而不是凭直觉。这一点在optimization.md的最佳实践清单中进一步强调：「Always run EXPLAIN ANALYZE before optimizing」（优化前总是先跑 EXPLAIN ANALYZE）。

参考指南与按需加载机制

SKILL.md 维护了一张参考指南表，Agent 依据当前上下文按需加载对应文档，而不是一次性读入全部内容：

下文将逐项展开这五大主题的核心内容。

查询模式：从 CTE 到高级 JOIN

基础 CTE 与多引用复用

CTE（Common 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 列表中的标量子查询会引发 N+1 问题，应改用带聚合的 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 / NTILE

• ROW_NUMBER()：分区内顺序编号，常用于「每组 Top N」；
• RANK()：同值同排名但留有间隔；
• DENSE_RANK()：同值同排名但无间隔；
• NTILE(n)：将结果平分成 n 桶（如四分位）。

三者差异的典型输出（score=100 并列两名时）：

score=100: rank=1, dense_rank=1, row_num=1 score=100: rank=1, dense_rank=1, row_num=2 score=95: rank=3, dense_rank=2, row_num=3

「每客户最新一笔订单」是 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 FOLLOWING，RANGE对日期可写INTERVAL '2 days'；
• FIRST_VALUE/LAST_VALUE必须配合ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING才能覆盖整个分区（否则默认帧只到当前行）；
• PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER ()可直接计算中位数与 p90；
• FILTER (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的 EXPLAIN：

EXPLAIN (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 <table>）；
• Buffers：shared hit是缓存命中，read是磁盘 I/O，高read数往往意味着缺缓存或缺索引。

MySQL 使用EXPLAIN FORMAT=JSON；SQL Server 用SET STATISTICS IO ON+SET STATISTICS TIME ON，并可查询sys.dm_exec_query_stats核对估算/实际行数。

索引设计：覆盖、复合、部分、表达式与 GIN

optimization.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 DISTINCT：SELECT DISTINCT customer_id FROM orders WHERE status='active'会强制排序去重，改为对 customers 表做 EXISTS 检查；
• 用 NOT EXISTS 代替 NOT IN：NOT 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/3NF）

• 1NF：原子值，无重复组——不要把多个电话号塞进一个VARCHAR(500)列，应拆出customer_phones子表；
• 2NF：消除对复合主键的部分依赖——order_items_bad中product_name只依赖product_id，应拆出products表，order_items只保留quantity与下单时快照的unit_price；
• 3NF：消除传递依赖——地址表中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 Constraint）：PostgreSQL 独有，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（缓慢变化维度类型 2）：customer_history用valid_from/valid_to/is_current保留完整历史，CHECK (valid_to IS NULL OR valid_to > valid_from)保证区间合法；
• 软删除：deleted_at TIMESTAMP（NULL 表示活跃），并建部分索引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 逻辑要在多引擎上运行」的场景。

自增主键

-- PostgreSQL（SERIAL 或 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 20；SQL Server 2012+ 与 Oracle 12c+ 用OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY；老版本分别用ROW_NUMBER()子查询与ROWNUM三层嵌套。

布尔、JSON 与大小写

• 布尔：PostgreSQL 原生BOOLEAN；MySQL 用TINYINT(1)（BOOLEAN只是别名）；SQL Server 用BIT；Oracle 表内无布尔类型，用NUMBER(1) CHECK (is_active IN (0,1))；
• JSON：PostgreSQLJSONB配 GIN 索引（@>包含运算符）；MySQL 8.0+ 用JSON_EXTRACT；SQL Server 2016+ 用JSON_VALUE+ISJSON约束；Oracle 12c+ 用JSON_VALUE/JSON_EXISTS；
• 大小写：PostgreSQL、Oracle 默认区分大小写（可用LOWER()或 PostgreSQL 的ILIKE）；MySQL、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 ROW；
• UPSERT：PostgreSQLON CONFLICT ... DO UPDATE；MySQLON DUPLICATE KEY UPDATE；SQL Server 与 Oracle 用MERGE。

数据类型映射速查

约束与输出模板

为保证交付质量，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