Back to Details

query-builder

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Query Builder

查询生成器

Convert natural language questions into SQL queries using the database schema.
利用数据库架构将自然语言问题转换为SQL查询。

When to Use

使用场景

Activate when user asks questions like:
  • "Show me all users who signed up last month"
  • "Find orders greater than $100"
  • "Which products have low inventory?"
  • "Get the top 10 customers by total spend"
当用户提出以下类型的问题时激活：
  • "显示所有上月注册的用户"
  • "查找金额超过100美元的订单"
  • "哪些产品库存不足？"
  • "获取消费总额排名前十的客户"

Workflow

工作流程

1. Understand the Schema

1. 理解数据库架构

Before generating SQL, always check the table structure:
whodb_tables(connection="...") → Get available tables
whodb_columns(table="relevant_table") → Get column names and types
生成SQL之前，务必先检查表结构：
whodb_tables(connection="...") → 获取可用表
whodb_columns(table="relevant_table") → 获取列名和类型

2. Identify Intent

2. 识别意图

Parse the natural language request:
  • Subject: What entity? (users, orders, products)
  • Filter: What conditions? (last month, > $100, active)
  • Aggregation: Count, sum, average, max, min?
  • Grouping: By what dimension?
  • Ordering: Sort by what? Ascending/descending?
  • Limit: How many results?
解析自然语言请求：
  • 主体：涉及什么实体？（用户、订单、产品）
  • 筛选条件：有哪些条件？（上月、>100美元、活跃状态）
  • 聚合操作：计数、求和、平均值、最大值、最小值？
  • 分组：按什么维度分组？
  • 排序：按什么排序？升序/降序？
  • 限制：返回多少条结果？

3. Map to Schema

3. 映射到架构

  • Match entities to table names
  • Match attributes to column names
  • Identify foreign key joins needed
  • 将实体匹配到表名
  • 将属性匹配到列名
  • 确定所需的外键关联

4. Generate SQL

4. 生成SQL

Build the query following SQL best practices:
sql
SELECT columns
FROM table
[JOIN other_table ON condition]
WHERE filters
[GROUP BY columns]
[HAVING aggregate_condition]
ORDER BY column [ASC|DESC]
LIMIT n;
遵循SQL最佳实践构建查询：
sql
SELECT columns
FROM table
[JOIN other_table ON condition]
WHERE filters
[GROUP BY columns]
[HAVING aggregate_condition]
ORDER BY column [ASC|DESC]
LIMIT n;

5. Execute and Present

5. 执行并展示

whodb_query(query="generated SQL")
whodb_query(query="generated SQL")

Translation Patterns

翻译模式

Natural LanguageSQL Pattern
"last week/month/year"
WHERE date_col >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
"more than X" / "greater than X"
WHERE col > X
"top N"
ORDER BY col DESC LIMIT N
"how many"
SELECT COUNT(*)
"total" / "sum of"
SELECT SUM(col)
"average"
SELECT AVG(col)
"for each" / "by"
GROUP BY col
"between X and Y"
WHERE col BETWEEN X AND Y
"contains" / "like"
WHERE col LIKE '%term%'
"starts with"
WHERE col LIKE 'term%'
"is empty" / "is null"
WHERE col IS NULL
"is not empty"
WHERE col IS NOT NULL
自然语言SQL模式
"上周/上月/去年"
WHERE date_col >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
"超过X" / "大于X"
WHERE col > X
"排名前N的"
ORDER BY col DESC LIMIT N
"有多少"
SELECT COUNT(*)
"总计" / "求和"
SELECT SUM(col)
"平均值"
SELECT AVG(col)
"每个" / "按..."
GROUP BY col
"在X和Y之间"
WHERE col BETWEEN X AND Y
"包含" / "类似"
WHERE col LIKE '%term%'
"以...开头"
WHERE col LIKE 'term%'
"为空" / "是NULL"
WHERE col IS NULL
"不为空"
WHERE col IS NOT NULL

Date Handling by Database

不同数据库的日期处理

PostgreSQL

PostgreSQL

sql
WHERE created_at >= NOW() - INTERVAL '7 days'
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
sql
WHERE created_at >= NOW() - INTERVAL '7 days'
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)

MySQL

MySQL

sql
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
sql
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')

SQLite

SQLite

sql
WHERE created_at >= DATE('now', '-7 days')
WHERE created_at >= DATE('now', 'start of month')
sql
WHERE created_at >= DATE('now', '-7 days')
WHERE created_at >= DATE('now', 'start of month')

Examples

示例

"Show me users who signed up this month"

"显示本月注册的用户"

sql
SELECT * FROM users
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
ORDER BY created_at DESC;
sql
SELECT * FROM users
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
ORDER BY created_at DESC;

"Find the top 5 products by sales"

"查找销量排名前五的产品"

sql
SELECT p.name, SUM(oi.quantity) as total_sold
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY total_sold DESC
LIMIT 5;
sql
SELECT p.name, SUM(oi.quantity) as total_sold
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY total_sold DESC
LIMIT 5;

"How many orders per customer?"

"每个客户有多少订单？"

sql
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;
sql
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;

Safety Rules

安全规则

  • Always use LIMIT for exploratory queries (default: 100)
  • Never generate DELETE, UPDATE, or DROP unless explicitly requested
  • Warn if query might return large result sets
  • Use table aliases for readability in JOINs
  • 探索性查询始终使用LIMIT（默认值：100）
  • 除非明确要求，否则绝不能生成DELETE、UPDATE或DROP语句
  • 如果查询可能返回大量结果集，需发出警告
  • 在关联查询中使用表别名以提升可读性