write-query

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

/write-query - Write Optimized SQL

/write-query - 编写优化的SQL

If you see unfamiliar placeholders or need to check which tools are connected, see CONNECTORS.md.

Write a SQL query from a natural language description, optimized for your specific SQL dialect and following best practices.

如果你看到不熟悉的占位符或需要检查已连接的工具，请查看 CONNECTORS.md。

根据自然语言描述编写SQL查询，针对你的特定SQL方言进行优化并遵循最佳实践。

Usage

使用方法

/write-query <description of what data you need>

/write-query <你需要的数据的描述>

Workflow

工作流程

1. Understand the Request

1. 理解需求

Parse the user's description to identify:

Output columns: What fields should the result include?
Filters: What conditions limit the data (time ranges, segments, statuses)?
Aggregations: Are there GROUP BY operations, counts, sums, averages?
Joins: Does this require combining multiple tables?
Ordering: How should results be sorted?
Limits: Is there a top-N or sample requirement?

解析用户的描述，明确：

输出列：结果应包含哪些字段？
筛选条件：哪些条件会限制数据范围（时间范围、分段、状态等）？
聚合操作：是否有GROUP BY操作、计数、求和、平均值计算？
表连接：是否需要合并多个表？
排序规则：结果应如何排序？
结果限制：是否需要返回前N条或抽样数据？

2. Determine SQL Dialect

2. 确定SQL方言

If the user's SQL dialect is not already known, ask which they use:

PostgreSQL (including Aurora, RDS, Supabase, Neon)
Snowflake
BigQuery (Google Cloud)
Redshift (Amazon)
Databricks SQL
MySQL (including Aurora MySQL, PlanetScale)
SQL Server (Microsoft)
DuckDB
SQLite
Other (ask for specifics)

Remember the dialect for future queries in the same session.

如果用户的SQL方言未知，请询问他们使用的是哪种：

PostgreSQL（包括Aurora、RDS、Supabase、Neon）
Snowflake
BigQuery（Google Cloud）
Redshift（Amazon）
Databricks SQL
MySQL（包括Aurora MySQL、PlanetScale）
SQL Server（Microsoft）
DuckDB
SQLite
其他（请说明具体类型）

在同一会话中保留该方言信息，以便后续查询使用。

3. Discover Schema (If Warehouse Connected)

3. 发现数据库Schema（若已连接数据仓库）

If a data warehouse MCP server is connected:

Search for relevant tables based on the user's description
Inspect column names, types, and relationships
Check for partitioning or clustering keys that affect performance
Look for pre-built views or materialized views that might simplify the query

如果已连接数据仓库MCP服务器：

根据用户描述搜索相关表
检查列名、类型和表间关系
查看影响性能的分区或聚类键
寻找可简化查询的预建视图或物化视图

4. Write the Query

4. 编写查询

Follow these best practices:

Structure:

Use CTEs (WITH clauses) for readability when queries have multiple logical steps
One CTE per logical transformation or data source

Name CTEs descriptively (e.g.,

daily_signups

active_users

revenue_by_product

)

Performance:

Never use
```
SELECT *
```
in production queries -- specify only needed columns
Filter early (push WHERE clauses as close to the base tables as possible)
Use partition filters when available (especially date partitions)
Prefer
```
EXISTS
```
over
```
IN
```
for subqueries with large result sets
Use appropriate JOIN types (don't use LEFT JOIN when INNER JOIN is correct)
Avoid correlated subqueries when a JOIN or window function works
Be mindful of exploding joins (many-to-many)

Readability:

Add comments explaining the "why" for non-obvious logic
Use consistent indentation and formatting
Alias tables with meaningful short names (not just
```
a
```
,
```
b
```
,
```
c
```
)
Put each major clause on its own line

Dialect-specific optimizations:

Apply dialect-specific syntax and functions (see
```
sql-queries
```
skill for details)
Use dialect-appropriate date functions, string functions, and window syntax
Note any dialect-specific performance features (e.g., Snowflake clustering, BigQuery partitioning)

遵循以下最佳实践：

结构规范：

当查询包含多个逻辑步骤时，使用CTE（WITH子句）提升可读性
每个CTE对应一个逻辑转换或数据源
为CTE赋予描述性名称（例如
```
daily_signups
```
、
```
active_users
```
、
```
revenue_by_product
```
）

性能优化：

生产环境查询中切勿使用
```
SELECT *
```
——仅指定需要的列
尽早筛选数据（将WHERE子句尽可能靠近基表）
若有可用的分区筛选（尤其是日期分区）请使用
对于结果集较大的子查询，优先使用
```
EXISTS
```
而非
```
IN
```
使用合适的JOIN类型（当INNER JOIN适用时不要使用LEFT JOIN）
当JOIN或窗口函数可行时，避免使用关联子查询
注意避免爆炸式连接（多对多关系）

可读性：

为非显而易见的逻辑添加注释说明原因
使用一致的缩进和格式
为表赋予有意义的短别名（不要仅用
```
a
```
、
```
b
```
、
```
c
```
）
将每个主要子句单独放在一行

方言特定优化：

应用方言特定的语法和函数（详见
```
sql-queries
```
技能）
使用适合方言的日期函数、字符串函数和窗口语法
注意任何方言特定的性能特性（例如Snowflake聚类、BigQuery分区）

5. Present the Query

5. 呈现查询

Provide:

The complete query in a SQL code block with syntax highlighting
Brief explanation of what each CTE or section does
Performance notes if relevant (expected cost, partition usage, potential bottlenecks)
Modification suggestions -- how to adjust for common variations (different time range, different granularity, additional filters)

提供以下内容：

完整查询：放在带有语法高亮的SQL代码块中
简要说明：每个CTE或部分的作用
性能说明（若相关）：预期成本、分区使用情况、潜在瓶颈
修改建议：如何针对常见变体调整查询（不同时间范围、不同粒度、额外筛选条件）

6. Offer to Execute

6. 提供执行服务

If a data warehouse is connected, offer to run the query and analyze the results. If the user wants to run it themselves, the query is ready to copy-paste.

如果已连接数据仓库，可主动提出运行查询并分析结果。如果用户希望自行运行，该查询可直接复制粘贴使用。

Examples

示例

Simple aggregation:

/write-query Count of orders by status for the last 30 days

Complex analysis:

/write-query Cohort retention analysis -- group users by their signup month, then show what percentage are still active (had at least one event) at 1, 3, 6, and 12 months after signup

Performance-critical:

/write-query We have a 500M row events table partitioned by date. Find the top 100 users by event count in the last 7 days with their most recent event type.

简单聚合查询：

/write-query 统计过去30天内各状态的订单数量

复杂分析查询：

/write-query 同期群留存分析——按用户注册月份分组，然后展示注册后1、3、6和12个月时仍活跃（至少有一个事件）的用户百分比

性能敏感型查询：

/write-query 我们有一个包含5亿行数据的events表，按日期分区。找出过去7天内事件数量排名前100的用户及其最近的事件类型。

Tips

提示

Mention your SQL dialect upfront to get the right syntax immediately
If you know the table names, include them -- otherwise Claude will help you find them
Specify if you need the query to be idempotent (safe to re-run) or one-time
For recurring queries, mention if it should be parameterized for date ranges

提前说明你的SQL方言，以便立即获取正确的语法
如果你知道表名，请包含在内——否则Claude会帮你查找
说明是否需要查询具备幂等性（可安全重复运行）或仅需一次性运行
对于定期重复的查询，请说明是否需要针对日期范围进行参数化