SQL 问题中的字段过滤器

学习如何在 SQL 查询中使用 Metabase 字段过滤器来构建更智能的过滤器小部件。

本文展示了如何使用一种特殊的变量类型,称为 **字段过滤器**,将智能 SQL 过滤器小部件添加到 Metabase 中的 SQL 查询中。

字段过滤器简介

A Field Filter is a special type of variable that can wire up a variable in your SQL code to a field (column) in a table, which enables it to create a

对于用 SQL 编写的 Metabase 问题,我们可以使用基本变量类型——文本、数字和日期——来创建简单的 SQL 过滤器小部件。为了创建“更智能”的过滤器小部件,可以显示与过滤列中的数据相关的选项,例如创建值下拉菜单,我们可以使用一种特殊的变量类型,称为字段过滤器

To create a Field Filter, add a variable to your SQL code by enclosing the variable in double braces (Mustache style), and select Field Filter as the Variable type from the Variables sidebar.

字段过滤器最初可能会让一些人感到困惑,因为它们只适用于某些字段,而且人们期望它们表现得像基本输入变量(但它们不是)。然而,字段过滤器非常值得学习,因为您可以使用它们创建更复杂的过滤器小部件。本文将深入探讨字段过滤器,但首先让我们讨论字段过滤器变量与基本文本、数字和日期变量之间的主要区别。

区分字段过滤器与简单的文本、数字和日期变量

  1. 字段过滤器默认是可选的。如果未给定值,SQL 查询将像字段过滤器不存在一样运行。但是,您可以选择要求一个值。
  2. 字段过滤器不适用于表别名。由于字段过滤器依赖于表中列的元数据(以及这些表的特定名称),过滤器无法“知道”您已为表设置了别名。而且,根据您使用的数据库,您可能需要在 FROM 子句中包含完整的模式路径。
  3. 字段过滤器使用特殊语法,以便它们可以在后台处理 SQL 代码。您只需向 WHERE 子句提供字段过滤器(不带列或运算符),字段过滤器将为您管理 SQL 代码。这允许代码考虑人们在过滤器小部件中进行的多个选择。

第 3 点可能特别令人困惑,所以让我们通过一个例子来解释它。

创建带有下拉菜单的 SQL 过滤器小部件

我们将使用 Metabase 中包含的示例数据库,为用 SQL 编写的问题添加一个带有下拉菜单的过滤器小部件。假设我们想创建一个 SQL 问题,它从 Orders 表中获取所有订单,但我们希望人们可以选择按 Products 表中的类别来过滤结果。我们可以创建一个带有基本输入变量的 Products.category 过滤器,如下所示:

SELECT *
FROM Orders
LEFT JOIN Products
ON Orders.product_id = Products.id
[[WHERE Products.category = {{category}}]];

在这种情况下,我们用双括号将 WHERE 子句括起来,使输入变为可选,并使用**变量侧边栏**将变量类型设置为 Text,将过滤器小部件标签设置为 Category。这种方法可行,但并不理想:

  • 为了过滤数据,人们必须知道存在哪些类别(并在输入时正确拼写它们)。
  • 此外,他们不能同时选择多个类别,因为 {{category}} 变量只接受单个值。

相比之下,字段过滤器会将变量映射到实际的列数据。连接到变量的过滤器小部件随后“知道”哪些类别可用,并可以呈现这些类别的下拉菜单,如下所示:

A filter widget created by a Field Filter that

关于小部件的一点说明:下拉菜单只是可用选项之一。对于类型为 Category 的字段,例如 Products 表中的 category 字段,我们还可以将过滤器小部件设置为搜索框或纯文本输入框。管理员可以在**管理面板**的**数据模型选项卡**中配置字段设置。

In the Data Model tab of the Admin Panel, Admins can edit the field settings. For fields of type Category, Admins can select three options for field widgets: Search box, A list of all values (dropdown), or Plain input box.

请注意,如果列中不同值的数量大于 300,即使您选择下拉选项,Metabase 也会自动使用搜索框。请在我们的文档中了解有关编辑元数据的更多信息。

现在,让我们回到我们的问题。这是 Products.category 字段过滤器的语法。请注意 WHERE 子句中变量前省略了列和运算符——我们将在下面详细讨论字段过滤器语法。

SELECT *
FROM orders
LEFT JOIN products
ON orders.product_id = products.id
WHERE {{category}};

WHERE 子句中设置好变量后,我们可以使用**变量侧边栏**将变量连接为字段过滤器。我们将设置:

  • Variable type(变量类型)为 Field Filter(字段过滤器)。
  • Field to map to(映射到字段)为 Products → Category。此设置告诉 Metabase 将我们 SQL 代码中的变量连接到 Products 表的 category 列。
  • Field widget type(字段小部件类型)为 category
  • Field widget label(字段小部件标签)为 category

Setting a variable

我们不会要求此变量,因此无需默认值。如果查询在过滤器小部件中没有指定值的情况下运行,查询将返回所有类别的记录。

请注意,WHERE 子句没有指定变量应该等于哪一列。这种隐式语法(隐藏的 SQL 代码)允许字段过滤器在后台处理 SQL 代码以适应多项选择。

为日期字段创建复杂的 SQL 过滤器小部件

我们可以创建类型为 Date 的基本输入变量,它将添加一个带有简单日期过滤器的 SQL 过滤器小部件。如果改为使用字段过滤器变量,我们可以将该变量连接到包含日期的字段(列),从而解锁更多配置过滤器小部件的选项。以下是 SQL:

SELECT *
FROM ORDERS
WHERE {{created_at}}

Setting the Field to map to option to a field containing dates will open up a range of Filter widget types: Month and Year, Quarter and Year, Single Date, Date Range, Relative Date, and Date Filter.

以下是映射到日期字段的字段过滤器的不同小部件类型:

  • 月份和年份
  • 季度和年份
  • 单日
  • 日期范围
  • 相对日期
  • 日期过滤器

每种小部件类型都提供了不同的方式供人们过滤结果。以下是三个 SQL 字段过滤器示例:

The Month and Year widget type.

The Relative Date widget type.

The Date Filter widget type.

日期过滤器小部件类型提供了最大的灵活性,允许人们按相对日期和范围进行过滤。

字段过滤器陷阱

在尝试实现字段过滤器时,人们通常会在以下几个方面遇到困难。

字段过滤器与别名不兼容

如上所述,如果您在 SQL 查询中使用别名,字段过滤器将无法工作。例如,此代码(带有别名)将无法工作:

-- DON'T DO THIS
SELECT *
FROM orders AS o
LEFT JOIN products AS p
ON o.product_id = p.id
WHERE {{category}};

而这段没有别名的代码会工作:

SELECT *
FROM orders
LEFT JOIN products
ON orders.product_id = products.id
WHERE {{category}};

原因是字段过滤器通过分析关于您数据的元数据(例如,您表格的列名)来工作,而该元数据不包括您在 SQL 代码中创建的别名。请注意,某些数据库要求在 FROM 子句中包含模式。Oracle 的一个示例是 FROM "schema"."table"。在 BigQuery 中,需要使用反引号:FROM `dataset_name.table`

省略 WHERE 子句中的直接赋值

如上所述,围绕字段过滤器的 SQL 代码并非完全符合常规。您可能会倾向于这样写:

-- DON'T DO THIS
WHERE category = {{ category }}

因为这是标准 SQL 中 WHERE 子句的正确语法。但该语法不适用于字段过滤器。字段过滤器的正确语法省略了 = 运算符:

WHERE {{ category }}

这种简写的原因是,Metabase 可以在幕后插入 SQL 代码,以处理多选等情况,例如当用户从下拉菜单中选择多个类别时。

只有某些字段与字段过滤器兼容

这是兼容字段列表

Some fields are incompatible with Field Filters. For incompatible fields, you

您可以在我们关于字段过滤器的文档中找到不兼容字段类型的列表。

了解更多关于 SQL 过滤器和变量的信息

请查阅我们关于基本 SQL 输入变量 - 文本、数字和日期的指南。

您还可以阅读我们的文档:

下一步:代码片段:重用和共享代码

了解关于在原生代码中使用代码片段所需的一切。

下一篇文章
© . All rights reserved.