data-sql

Data SQL Skill

版本： 1.0 技术栈： SQL（PostgreSQL、Redshift、Athena、Spark SQL）

SQL 是唯一一种可运行的查询可能比等效的可运行查询慢 1,000 倍的语言。缺少索引会让一次 50ms 的查找变成耗时 50 秒的全表扫描。添加列时，SELECT * 会导致下游代码出错。相关子查询会悄无声息地执行 N+1 模式，这种问题在开发环境中不明显，却会拖垮生产环境。查询规划器不会警告你——它只会照你要求的执行。

编写 SQL 时考虑索引、明确指定列并使用 CTE，不仅能加快执行速度，也更容易调试。清晰度与性能相辅相成。

---

范围与边界

此技能涵盖：

• 查询优化模式
• 架构设计与规范化
• 数据建模（星型模式、缓慢变化维度）
• CTE 与查询组织
• 索引策略
• 窗口函数

交由其他技能处理：

• security：防止 SQL 注入、参数化查询
• data-pipelines：何时使用 SQL，何时使用应用程序代码

使用此技能的场景： 编写 SQL 查询或设计架构。

---

核心原则

1. 使用 CTE 提高可读性 — 将复杂查询拆分为命名步骤。
2. 尽早筛选 — 尽可能将 WHERE 子句放在靠近数据源的位置。
3. 明确指定列 — 生产代码中绝不使用 SELECT *。
4. 编写索引感知型查询 — 了解哪些列已建立索引，并编写能利用这些索引的查询。
5. 幂等操作 — INSERT/UPDATE 应支持安全重试。

---

模式

CTE 组织

-- Good - logical steps, easy to debug
WITH active_users AS (
    SELECT user_id, email
    FROM users
    WHERE status = 'active'
),
recent_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE created_at > CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    u.email,
    COALESCE(o.order_count, 0) as recent_orders
FROM active_users u
LEFT JOIN recent_orders o USING (user_id);

Upsert 模式（PostgreSQL）

INSERT INTO inventory (sku, quantity, updated_at)
VALUES ('ABC123', 100, NOW())
ON CONFLICT (sku) DO UPDATE SET
    quantity = EXCLUDED.quantity,
    updated_at = EXCLUDED.updated_at;

窗口函数

-- Running total and row number
SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) as running_total,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rank
FROM daily_sales;

---

反模式

反模式	问题	修复方法
SELECT *	架构变更时会出错，并浪费带宽	明确列出各列
相关子查询	N+1 查询行为	使用 JOIN 或窗口函数
JOIN 条件中使用 OR	阻碍索引使用	调整结构或使用 UNION
对索引列使用函数	WHERE YEAR(date) = 2024	使用范围：date >= '2024-01-01'
缺少 GROUP BY 列	行为未定义	包含所有非聚合列

---

检查清单

• 生产查询中不使用 SELECT *
• 复杂查询使用 CTE
• WHERE 子句尽早筛选
• JOIN 优先使用已建立索引的列
• Upsert 操作具有幂等性
• 使用 EXPLAIN 测试大型查询

---

参考资料

• references/query-optimization.md — 索引使用、JOIN 优化、性能
• references/window-functions.md — 窗口函数模式与示例

资源

• assets/query-patterns.md — 常见 SQL 模式与模板