# Write queries

Use Sigma to compose custom queries in standard ANSI SQL.

Anyone on your account with [permission to view reports](https://support.stripe.com/questions/can-i-invite-other-team-members-or-my-developer-to-use-my-stripe-account) can use the [Sigma query editor](https://dashboard.stripe.com/sigma/queries) to compose new or modify existing queries. Our large language model (LLM)-based query assistant can even help you generate the SQL you need from a natural language prompt.

## Query resources 

编辑器左侧的导航窗口提供了一组帮助您构建查询的工具。您可以看到：

- Your previously saved queries.
- Queries saved by your team.
- The table schema to search for data sources.
- Stripe query templates.

### Saved queries and templates 

**Reports > Saved** shows the lists of queries previously saved by you and your team. **Templates** provides a set of example queries representing the most common metrics and reports. Selecting a saved query or template loads it into the query editor, where you can click **Run** to regenerate and view the results.

To use a saved query as a starting point for your own custom report, either click its overflow menu (⋯) and choose **Make a copy** or load it into the editor and click **Make a copy**. This lets you modify the content in the editor and [save](https://docs.stripe.com/data/write-queries.md#saving-queries) your changes as a new query.

## Compose a query 

打开查询编辑器时，您可以：

- 将标准 ANSI SQL 直接写入编辑器。
- 从之前保存的查询或 Stripe 模板中选择一个现有查询，并在编辑器中修改它，以微调返回的数据。

The following query uses the `balance_transactions` table to get information about the five most recent balance transactions related to refunds:

```sql
select
  date_format(created, '%Y-%m-%d') as day,
  id,
  amount,
  currency,
  source_id
from balance_transactions
where type = 'refund'
order by day desc
limit 5
```

点击**运行**来执行查询，并在编辑器下方的表格中查看结果。查询越复杂，完成和显示结果所用的时间可能会越长。尝试运行无效查询会生成一条错误消息，其中包含错误的行号和位置。

The results of our sample query returns five rows, where each row corresponds to a particular balance transaction item, along with the requested information about them.

| day | id | amount | currency | source_id |
| --- | --- | --- | --- | --- |
| 2026/11/21 | txn_orWziM4j7CiRL8J | -1,000 | usd | re_orWziM4j7CiRL8J |
| 2026/11/21 | txn_orWziM4j7CiRL8J | -1,000 | usd | re_orWziM4j7CiRL8J |
| 2026/11/21 | txn_orWziM4j7CiRL8J | -1,000 | usd | re_orWziM4j7CiRL8J |
| 2026/11/21 | txn_orWziM4j7CiRL8J | -1,000 | eur | re_orWziM4j7CiRL8J |
| 2026/11/21 | txn_orWziM4j7CiRL8J | -1,000 | usd | re_orWziM4j7CiRL8J |

### Join tables

You can join columns of type **Primary key** or **Foreign key** to similar columns in other tables:

- **Primary key**: Represents the unique identifier (ID) for each record in a table.
- **Foreign key**: Represents data that refers to the primary key of another table.

例如，您可以将 `disputes` 表（外键）中的 `charge_id` 一列组合到 `charges` 表（主键）的 `id` 列。
![](https://b.stripecdn.com/docs-statics-srv/assets/disputes.f293434123d316ff4fafe2524e9b2b0d.png)

使用组合表格功能，您可以在数据集中返回更丰富的结果。例如，您可以修改我们的余额交易查询，使其与 `refunds` 表组合，从而提供进一步信息。

```sql
select
  date_format(date_trunc('day', balance_transactions.created), '%Y-%m-%d') as day,
  balance_transactions.amount,
  balance_transactions.currency,
  balance_transactions.source_id,
  refunds.charge_id
from
  balance_transactions
inner join refunds -- Joining these tables to retrieve additional information
on balance_transactions.source_id=refunds.id
where balance_transactions.type = 'refund'
order by day desc
limit 5
```

此扩展查询现在返回与退款相关的原始收款 ID。

| day | amount | currency | source_id | charge.id |
| --- | --- | --- | --- | --- |
| 2026/11/21 | -1,000 | usd | re_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J |
| 2026/11/21 | -1,000 | usd | re_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J |
| 2026/11/21 | -1,000 | usd | re_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J |
| 2026/11/21 | -1,000 | eur | re_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J |
| 2026/11/21 | -1,000 | usd | re_orWziM4j7CiRL8J | ch_orWziM4j7CiRL8J |

## Use the assistant 

Stripe 的查询编辑器有一个内置的 LLM 助手，可根据自然语言提示输出标准的 ANSI SQL。编辑器可以执行以下模式：

- **生成**模式使用提示问题编写新的查询，覆盖编辑器窗口中已经加载的任何 SQL。
- **编辑**模式使用提示问题在查询编辑器中修改 SQL。

> #### Check your mode
> 
> 为了获得最准确的建议，请务必选择正确的提示模式。当编辑器包含内容时，会自动切换到**编辑**模式。如果您决定在不更改模式的情况下提示新的查询，则可能会发生意外，生成的建议可能会受到编辑器中已有内容的限制。

### Prompt the assistant

1. 在管理平台中打开 [Sigma 查询编辑器](https://dashboard.stripe.com/sigma/queries)。

2. 选择**生成**模式，并在提示字段中输入问题。助手：

   - 将查询建议加载到编辑器中。
   - 显示描述建议的摘要。
   - 运行建议的查询，并在编辑器下方的表中返回结果。
   - 切换到**编辑**模式，以便根据需要询问另一个问题来对查询进行微调。

3. 继续提示助手并查看结果数据，直到获得您想要的信息。

The following screenshot demonstrates how a prompt generated the same SQL as the example query shown at the beginning of this document:
![助手和结果提示演示。](https://b.stripecdn.com/docs-statics-srv/assets/sigma-assistant.5f0ca3dae331a32c4c58cf6ceecdbf65.png)

有关修改建议的其他问题可能包括：

- 按金额对这些结果进行排序。
- 不要包含 automatic_transfer 或 reporting_category 列。
- 我可以看到每笔交易的客户吗？

> #### Explicitly request Connect data
> 
> When asking for data about connected accounts, explicitly mention connected accounts in your query. For example: *How many new subscriptions did I have from connected accounts last month?*

### View chat history

Sigma 会将您与助手的每次互动保存在该查询的聊天记录中。点击编辑器右上角的 :history: 按钮，打开聊天记录滑块。

在聊天记录中，您可以看到针对查询生命周期（不仅仅是当前会话）给予助手的每个提示以及助手的响应。点击**查看 SQL**，查看与任何提示有关的 SQL 建议。打开后，点击**在编辑器中查看**，将建议重新加载到主编辑器窗口中。

### Limitations

- 查询编辑器助手仅用英语回答问题。
- 查询编辑器助手只回答与 Stripe 模型相关的问题。它不能回答一般性的问题，比如 _Stripe 的 Logo 是什么颜色？*或者\_今天旧金山的天气怎么样？*

### Leave feedback 

回答编辑器和结果表格之间的**此回答有帮助吗？**，帮助我们继续改进助手使用体验：

1. Click **Yes** or **No**.

2. 具体描述助手的表现情况以及我们可以在哪些地方进行改进。我们欢迎所有意见，无论是关于建议的准确性，用户界面，还是有关助手使用体验的任何其他方面。

### Training data consent

使用 Sigma 助手，即表示您同意 Stripe 可以记录和使用您的聊天记录来训练和提高 Sigma 助手的性能。如果您不想将您的聊天记录用于此目的，则可以在[设置](https://dashboard.stripe.com/settings/sigma)中选择退出。

## View and download query results 

You can [schedule your queries](https://docs.stripe.com/data/schedule-queries.md) on a daily, weekly, or monthly basis. We send results in an email or a *webhook* (A configurable endpoint that executes when it receives certain event notifications from an event destination) event.

查询结果在编辑器下方的表格中显示。您可以：

- 最多查看 1,000 个结果。
- 点击列标题以对结果进行排序。
- 调整每列的大小，以便更容易阅读结果。

You can also create a chart visualization for queries with fewer than 10,000 results. You can:

- Create line or bar charts with custom y-axes and x-axes.
- Group by any table column

When you save a chart to a query, the chart appears with your chosen settings every time you run the query. Charts are only editable by the author of the query.

Amounts are expressed in the lowest available currency unit, such as cents for USD or yen for JPY. For example, an amount of `1,000` with a currency of `usd` equates to 10 USD.

点击**下载 CSV**，导出您的结果，将其用于电子表格应用程序或其他报告工具。下载的 CSV 中会包含所有查询结果，因此可以查看 1,000 条以上的结果。

## Run queries from the API  (Public preview)

You can save queries and execute them programmatically in your own environment with the [Queries API](https://docs.stripe.com/data/api/queries.md), which doesn’t require a Sigma subscription.

## Save queries 

After you run a query, click **Save**. Stripe automatically generates an editable title for your query.

### Share queries 

The queries you save are added to **Reports > Saved** and made available to every team member on your account. Each saved query is given a unique URL you can share by clicking **Share**. You can use this link as a shortcut to a particular report you regularly use, or share it directly with other team members on the Stripe account.

您只能与团队成员分享查询。分享的查询为只读，因此其他团队成员不能修改您创建的查询。如果他们想要修改您的查询，可以保存一份副本并对其进行相应编辑。
