sql-query-generation

SQL 查询生成

此技能可让 AI Agent 将自然语言问题转换为正确、高效的 SQL 查询。Agent 会将用户意图映射到合适的查询构造——连接、聚合、窗口函数、CTE 和子查询，同时遵循目标数据库架构。它还会使用 EXPLAIN 执行计划分析查询性能，并建议索引、谓词下推和查询重构等优化方法。

工作流

1. 解析自然语言请求。 提取分析意图：询问的指标是什么、涉及哪些实体、适用哪些筛选条件，以及结果应如何排序或分组。区分请求的是聚合摘要还是行级详细信息。

2. 映射到数据库架构。 根据架构识别相关表和列。消解模糊引用（例如，“sales”可能指 orders 表，也可能指 revenue 列）。使用外键关系确定表之间的连接路径，避免不必要的连接导致结果集膨胀。

3. 选择合适的查询构造。 根据复杂度，在简单聚合、窗口函数、CTE 或子查询之间进行选择。对于多步计算，使用 CTE 提高可读性。对于分区内的累计总计、排名和比较，使用窗口函数。优先使用显式 JOIN，而非隐式的逗号分隔连接。

4. 生成 SQL 查询。 编写语法正确的 SQL，并保持格式一致：关键字大写、标识符小写、表使用别名，子句缩进。为复杂逻辑添加注释。始终为计算表达式指定列别名。

5. 验证并优化。 对生成的查询运行 EXPLAIN（或 EXPLAIN ANALYZE），检查执行计划。留意全表扫描、大型表上的哈希连接，以及对未建立索引的列进行排序等情况。如果估算成本较高，则建议添加索引或重写查询。

6. 返回结果并附上说明。 展示查询，并以通俗易懂的语言说明查询功能、预期输出格式，以及对架构或数据所做的任何假设。

支持的技术

• PostgreSQL — CTE、窗口函数、LATERAL 连接、EXPLAIN ANALYZE
• MySQL — 公用表表达式（8.0+）、窗口函数（8.0+）、EXPLAIN FORMAT=JSON
• SQLite — 轻量级查询、窗口函数（3.25+）
• SQL Server — T-SQL 扩展、cross apply、查询存储分析
• BigQuery — STRUCT/ARRAY 类型、QUALIFY 子句、基于 slot 的成本估算

用法

向 Agent 提供一个自然语言问题和数据库架构（DDL、架构图，或表和列的列表）。还可以选择指定目标 SQL 方言、性能限制，以及是否需要包含 EXPLAIN 分析。

示例

示例 1：根据自然语言生成复杂分析查询

请求：“显示过去 12 个月总消费额最高的 5 位客户，以及他们下单最多的产品类别，并比较他们的消费额与前一个 12 个月周期。”

WITH current_period AS (
    SELECT
        c.customer_id,
        c.customer_name,
        SUM(oi.quantity * oi.unit_price) AS total_spent,
        COUNT(DISTINCT o.order_id) AS order_count
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY c.customer_id, c.customer_name
),
previous_period AS (
    SELECT
        c.customer_id,
        SUM(oi.quantity * oi.unit_price) AS prev_total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '24 months'
      AND o.order_date < CURRENT_DATE - INTERVAL '12 months'
    GROUP BY c.customer_id
),
top_categories AS (
    SELECT DISTINCT ON (c.customer_id)
        c.customer_id,
        pc.category_name AS favorite_category,
        COUNT(*) AS category_orders
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    JOIN product_categories pc ON p.category_id = pc.category_id
    WHERE o.order_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY c.customer_id, pc.category_name
    ORDER BY c.customer_id, COUNT(*) DESC
)
SELECT
    cp.customer_name,
    cp.total_spent,
    cp.order_count,
    tc.favorite_category,
    pp.prev_total_spent,
    ROUND(
        (cp.total_spent - COALESCE(pp.prev_total_spent, 0))
        / NULLIF(pp.prev_total_spent, 0) * 100, 1
    ) AS spending_change_pct
FROM current_period cp
LEFT JOIN previous_period pp ON cp.customer_id = pp.customer_id
LEFT JOIN top_categories tc ON cp.customer_id = tc.customer_id
ORDER BY cp.total_spent DESC
LIMIT 5;

-- Expected output:
-- customer_name | total_spent | order_count | favorite_category | prev_total_spent | spending_change_pct
-- Acme Corp     | 284,500.00  | 47          | Electronics       | 198,200.00       | 43.5
-- GlobalTech    | 231,800.00  | 38          | Software          | 245,100.00       | -5.4
-- ...

示例 2：使用 EXPLAIN 分析优化慢查询

原始慢查询（在 5M 行数据上耗时 12.4 秒）：

SELECT product_name, SUM(quantity * unit_price) AS revenue
FROM order_items oi, products p, orders o
WHERE oi.product_id = p.product_id
  AND oi.order_id = o.order_id
  AND o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY product_name
ORDER BY revenue DESC;

EXPLAIN ANALYZE 输出（问题迹象）：

Seq Scan on orders o  (cost=0.00..98456.00 rows=1245000)
  Filter: (order_date >= '2024-01-01' AND order_date <= '2024-12-31')
  Rows Removed by Filter: 3755000
Hash Join  (cost=98456.00..245678.00 rows=3200000)
Sort  (cost=312456.00..312460.00 rows=8500)
  Sort Method: external merge  Disk: 4096kB

发现的问题：

1. 对 orders 进行顺序扫描——order_date 未建立索引
2. 隐式连接语法使优化器无法明确连接顺序
3. work_mem 不足，导致排序溢出到磁盘

优化后的查询：

-- Step 1: Create index (one-time)
CREATE INDEX idx_orders_date ON orders (order_date)
    INCLUDE (order_id);

-- Step 2: Rewrite with explicit joins and date index hint
SELECT
    p.product_name,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY p.product_name
ORDER BY revenue DESC;

-- After optimization: 0.34 seconds (36x faster)
-- EXPLAIN now shows:
-- Index Scan on idx_orders_date (rows=1245000, actual=1243892)
-- Merge Join (cost reduced by 85%)
-- Sort Method: quicksort  Memory: 512kB

最佳实践

• 始终使用显式 JOIN 语法，而不是逗号分隔的隐式连接——这样意图更清晰，也能避免意外的交叉连接。
• 为每个表和每个计算列都设置别名，以提高可读性并避免复杂查询中的歧义。
• 使用 CTE 将复杂查询拆分为有名称、逻辑清晰的步骤，而不是深层嵌套子查询。
• 尽早筛选：在驱动表上设置 WHERE 条件，以便在连接导致行数增加之前缩小数据集。
• 对连接后的表，优先使用 COUNT(DISTINCT col)，而不是 COUNT(*)，以避免一对多关系导致计数虚高。
• 在将生成的查询作为最终结果提交前，始终根据实际架构进行测试——自然语言描述中的列名和类型通常与实际 DDL 不同。

边缘情况

• 列名有歧义。 多个表存在同名列（例如 id、name、status）时，始终使用表别名限定列名。如果自然语言请求确实存在歧义，请向用户确认。
• 聚合中的 NULL 处理。 SUM、AVG 和 COUNT(col) 会静默忽略 NULL。如果 NULL 有实际含义（例如“无销售额”），请在聚合前使用 COALESCE(col, 0)，并注明这一假设。
• 计算指标中除以零。 使用 NULLIF(denominator, 0) 包装分母，以返回 NULL 而不是报错，然后在展示层处理 NULL。
• 日期／时区不匹配。 按时间戳列筛选日期时，请明确边界：使用 >= '2024-01-01' AND < '2025-01-01'，而不是 BETWEEN，因为后者会包含结束边界的午夜时刻。
• 结果集过大。 探索性查询始终要包含 LIMIT。对于生产查询，请使用 OFFSET／FETCH 分页或键集分页，以提升深页查询的性能。