在仪表板和工作表中筛选查询结果

您可以使用系统筛选器（面向 Snowflake 中的所有角色提供）在仪表板和 SQL 工作表中筛选查询结果，也可以使用管理员创建的自定义筛选器。

创建自定义筛选器

借助自定义筛选器，您无需直接编辑查询即可更改查询的结果。

Filters are implemented as special keywords that resolve as a subquery or list of values, which are then used in the execution of a query. As a result, there are some limitations when using a filter in a SQL query. See 在 SQL 查询中指定筛选器.

Note

创建自定义筛选器后，您账户中的任何人都可以查看和使用该筛选器。自定义筛选器具有相关角色，但该角色不会限制筛选器的可见性。

授予创建自定义筛选器的权限

To let a user create custom filters, a user with the ACCOUNTADMIN role must grant the relevant permissions to a role granted to that user. You can only use Snowsight to grant roles the ability to create custom filters.

要授予角色为账户创建自定义筛选器的权限，请执行以下操作：

  1. Sign in to Snowsight.
  2. In the navigation menu, select Projects » Dashboards or another project tool.
  3. Select Show or hide filter and, if in a worksheet, select Manage Filters.
  4. In the dialog that appears, select Edit Permission.
  5. In the Filter Permissions dialog, select the roles you want to grant the ability to create filters to.
  6. Select Save.

创建自定义筛选器

You must use Snowsight to create a filter, and you must use a role with permissions to create custom filters.

要创建自定义筛选器，请执行以下操作：

  1. Sign in to Snowsight.

  2. In the navigation menu, select Projects » Dashboards or another project tool.

  3. Select Show or hide filter and, if in a worksheet, select Manage Filters.

  4. In the Filters dialog that appears, select + Filter.

  5. 要添加筛选器，请完成以下操作：

    1. For Display Name, enter a name for the filter. This name appears on the filter when selecting the filter on a worksheet or dashboard.
    2. For SQL Keyword, enter a unique keyword to insert into queries. Use the format :<string>, without spaces. For example: :page_path.
    3. For Description, enter a description of the filter.
    4. For Role, select a role to associate with the filter and run the query used to populate filter values, if the filter is based on a query. Only roles with permissions to create custom filters appear in the drop-down list. See 管理自定义筛选器的所有权 for more details.
    5. For Warehouse, select a warehouse to use to refresh filter values, if the filter is based on a query. The owner role for the filter must have the USAGE privilege on the warehouse you select. If you want to run and validate your query as part of these steps, the warehouse must be running.
    6. For Options via, choose whether the filter values are populated by a query or a list:
      • If you select Query, select Write Query and see 编写查询以填充筛选器 for guidance writing a filter query.
      • If you select List, do the following:
        1. Select Edit List.
        2. Optionally, for Name, enter a name for the list item. The name appears in the drop-down list for the filter. If you do not provide a name, the Value is used.
        3. For Value, enter the value of the column name to use in the filter.
        4. Continue adding name and value pairs until your list is complete, then select Save.

  6. In the Add Filter dialog, for Value Type, choose whether the list items are Text or Number types of data.

  7. If you want users to be able to select multiple items in the drop-down list of filter options, turn on the toggle for Multiple values can be selected.

  8. If you want users to be able to see results for all items in the column, turn on the toggle for Include an “All” option, then select how you want the All option to function:

    • Select Any value to have the All in the filter mean that the column to which the filter applies can have any value in the results, whether or not the value exists in the filter list.
    • Select Any value in list of options to have All in the filter mean that the column to which the filter applies contains any item in the filter list.

  9. If you want users to be able to see results for items not specified in the filter, turn on the toggle for Include an “Other” option.

  10. Select Save.

  11. Select Done to close the Filters dialog.

编写查询以填充筛选器

要使用查询填充筛选器选项列表，查询必须遵循某些准则：

  • Must return the columns name and value.
  • Can return the optional column description.
  • 可以返回其他列，但这些列不会显示在下拉筛选器列表中。

A filter can only run one query at a time. You cannot run multiple queries to generate the list of filter options, for example by running one query to return the name column and a second query to return the value column.

Note

用于填充筛选器选项列表的查询以创建（或最后修改）筛选器的用户身份运行。由于您账户中的任何人都可以在创建自定义筛选器后查看和使用该筛选器，因此请确保查询生成的筛选器选项列表不包含受保护或敏感的数据。

After you write your filter query and add it in the New filter dialog, do the following to finish setting up your query filter:

  1. Select Done to save your filter query and return to the Add Filter dialog.
  2. Optionally change the default refresh option from Refresh hourly to Never refresh or Refresh daily. For details and considerations for filter refresh options, see 管理自定义筛选器的刷新频率.
  3. Return to the steps for creating a custom filter to finish creating your filter. See 创建自定义筛选器.

查看和管理账户中的自定义筛选器

To review custom filters in your account, open a worksheet or dashboard and then select Show or hide filter.

To make changes to any filters, such as changing the refresh frequency for the query used to populate a custom filter list, you must have the ACCOUNTADMIN role or a role with permissions to manage filters. See 管理自定义筛选器的刷新频率.

管理自定义筛选器的所有权

每个自定义筛选器都有一个关联角色。具有该角色的任何人都可以编辑或删除筛选器。具有 ACCOUNTADMIN 角色的用户可以查看和编辑账户中的每个筛选器。

If the role associated with a filter is dropped, the role dropping the filter role does not inherit ownership of the custom filter. Instead, a user with the ACCOUNTADMIN role can edit the filter and change the role associated with the filter.

管理自定义筛选器的刷新频率

由 SQL 查询填充的自定义筛选器也有刷新频率。刷新频率可以是每小时、每天，或者从不。

筛选器的运行基于其保存时间以及运行刷新筛选器选项的查询所需的时间。

例如，如果您在 10:07 AM 保存了一个具有每小时查询刷新频率的筛选器，则第一个刷新查询在 11:07 AM 或之后运行。如果最终计划同时运行大量筛选器刷新查询，则查询将排队以限制同时运行的筛选器刷新查询的数量。下一次筛选器刷新基于上次刷新完成的时间。在此示例中，如果查询在 11:07 AM 刷新需要 20 分钟完成，则下一个刷新查询将在 12:27 PM 或之后运行。

Filter refreshes run as the user that created or last modified the filter, and are visible in Query History as one of the types of Queries executed by user tasks. See Monitor query activity with Query History for details on using Query History.

要确定哪个筛选器负责筛选查询刷新，必须打开筛选器列表并打开每个筛选器以查看详细信息。

Note

Setting a custom filter’s refresh frequency can lead to increased consumption on your virtual warehouse. The virtual warehouse will run the underlying query according to the configured schedule, even if no user has the filter open in their web browser. The cost incurred depends on the query complexity and the refresh schedule that you set.

排查筛选器查询刷新失败的问题

筛选器查询刷新可能会因以下原因失败：

  • 已在 Snowflake 中删除或禁用创建或最后修改筛选器的用户。
  • 用户处于非活动状态，因为其已 3 个月没有登录。

It is not possible to see which users created or last modified a given filter. If you have filters that are failing to refresh, you might see successful authentication attempts by the WORKSHEETS_APP_USER user followed by failed authentication attempts from a user in the LOGIN_HISTORY view view of the ACCOUNT_USAGE schema in the shared SNOWFLAKE database.

例如，您可以使用以下查询来识别前两天使用 OAuth 访问令牌的登录活动：

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE
    FIRST_AUTHENTICATION_FACTOR = 'OAUTH_ACCESS_TOKEN'
    AND
    REPORTED_CLIENT_TYPE = 'SNOWFLAKE_UI'
    AND
    EVENT_TIMESTAMP > DATEADD('DAY', -2, CURRENT_DATE())
ORDER BY
    EVENT_TIMESTAMP DESC;

与失败查询刷新频率相关的失败身份验证尝试将在每天或每小时的同一时间发生，具体取决于自定义筛选器刷新频率。

在 SQL 查询中指定筛选器

You can use a system filter or a custom filter in a SQL query. You cannot use a filter in a stored procedure or a user-defined function (UDF).

要将筛选器添加到 SQL 查询，请使用下列格式之一：

  • Specify the filter as part of a SELECT statement, like SELECT :<filter_name>(<col_name>).
  • 使用等号作为比较器来指定筛选器。例如：
    • WHERE <col_name> = :<filter_name>
    • WHERE <:filter_name> = <col_name>
    • <value_a>:<value_b>::string = <:filter_name>

You can only use an equals sign as the comparator for a filter, and as such, cannot use a filter with LIKE or CONTAINS.

筛选器适用的列还必须与筛选器期望的值类型相匹配：

  • For a custom filter set to use a value type of text, the column must be a text string or cast to a text string in the query. See Data types for text strings.
  • For a custom filter set to use a value type of number, the column must be a numeric data type. See Numeric data types.
  • For a system filter, the column must be a TIMESTAMP data type. See Date & time data types.

向 SQL 查询添加筛选器，然后使用下拉列表选择筛选器选项时，查询的 SQL 语法将会更改。有关选择列表中的不同选项时 SQL 语法会如何变化的详细信息，请参阅下表：

Filter SQL reference

Filter option selectedSQL used
List item<col> = <list_item>
Multiple list items selected<col> IN (<list_item>, <list_item>)
All, with Any value specifiedtrue
All, with Any value in list of options specified<col> IN (<list_item>, <list_item>, ... )
Other<col> NOT IN(<list_item>, <list_item>, ... )

应用和保存筛选器

When you change the options selected in a filter, the option to apply your changes appears. When you select Apply, the worksheet or dashboard runs and updated filtered results appear, letting you review the changes without saving.

After you apply changes to a filter on a dashboard, the option to save your changes appears. When you select Save, the changes you made to the dashboard are saved and available to other users of the dashboard.

For example, you might select Apply to change a filter to see results from All Time, but you don’t want the dashboard to run over such a large volume of data the next time someone opens the dashboard, so you do not select Save. After you run your dashboard over all time, you change the date range filter to Last 7 days, select Apply to run the dashboard, and then select Save to save that default filter value for dashboard users.

Snowsight 系统筛选器

以下系统筛选器可用于所有角色：

  • :daterange

    • Filters a column by a date range, such as Last day, Last 7 days, Last 28 days, Last 3 months, Last 6 months, Last 12 months, All time, or a custom date range.

      Note

      The date range filter always uses the UTC time zone and is not affected by the TIMESTAMP_INPUT_FORMAT parameter.

      Defaults to Last day.

  • :datebucket

    • Groups aggregate data by a period of time, such as Second, Minute, Hour, Day, Week, Month, Quarter in calendar months, or Year.

      Defaults to Day.

这些筛选器无法编辑或删除。

示例：使用日期筛选器

例如，给定一个包含订单数据的表（如 SNOWFLAKE_SAMPLE_DATA 数据库和 TPCH_SF1 架构中的 ORDERS 表），您可能希望查询表并按特定时间桶（例如按天或按周）对结果进行分组，并指定要检索结果的特定日期范围。

为此，您可以编写如下查询：

SELECT
    COUNT(O_ORDERDATE) as orders,
    :datebucket(O_ORDERDATE) as bucket
FROM
    SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
WHERE
    O_ORDERDATE = :daterange
GROUP BY
    :datebucket(O_ORDERDATE)
ORDER BY
    bucket;

在此示例中，您执行以下操作：

  • 计算订单数量并从 ORDERS 表中检索有关订单日期的详细信息。
  • Filter your results by a specific date range by including the :daterange system filter in your WHERE clause.
  • Group your results by a specific period of time by including the :datebucket system filter in your GROUP BY clause.
  • Sort the results from earliest to latest time period by including the ORDER BY clause.

向查询中添加筛选器时，相应的筛选器按钮在工作表或仪表板的顶部显示：

When the filter buttons appear, they follow the show and hide filters button in the tab order.

要处理从查询中看到的结果，请使用筛选器选择特定的值。

For this example, set the Group by filter, which corresponds to the date bucket filter, to group by Day. Set the other filter, which corresponds to the date range filter, to All time.

When you select Apply and apply the filter to your results, the results are grouped by day and results like the following output appear:

+--------+------------+
| orders |  buckets   |
+--------+------------+
|    621 | 1992-01-01 |
|    612 | 1992-01-02 |
|    598 | 1992-01-03 |
|    670 | 1992-01-04 |
+--------+------------+

You can select a different date bucket to show a different grouping of data. For example, to view weekly order data, set the Group by filter to Week and select Apply. Results like the following output appear:

+--------+------------+
| orders |  buckets   |
+--------+------------+
|   3142 | 1991-12-30 |
|   4404 | 1992-01-06 |
|   4306 | 1992-01-13 |
|   4284 | 1992-01-20 |
+--------+------------+