SQL 问题中的字段过滤器
学习如何在 SQL 查询中使用 Metabase 字段过滤器来构建更智能的过滤器小部件。
本文展示了如何使用一种特殊的变量类型,称为 **字段过滤器**,将智能 SQL 过滤器小部件添加到 Metabase 中的 SQL 查询中。
字段过滤器简介
对于用 SQL 编写的 Metabase 问题,我们可以使用基本变量类型——文本、数字和日期——来创建简单的 SQL 过滤器小部件。为了创建“更智能”的过滤器小部件,可以显示与过滤列中的数据相关的选项,例如创建值下拉菜单,我们可以使用一种特殊的变量类型,称为字段过滤器。
字段过滤器最初可能会让一些人感到困惑,因为它们只适用于某些字段,而且人们期望它们表现得像基本输入变量(但它们不是)。然而,字段过滤器非常值得学习,因为您可以使用它们创建更复杂的过滤器小部件。本文将深入探讨字段过滤器,但首先让我们讨论字段过滤器变量与基本文本、数字和日期变量之间的主要区别。
区分字段过滤器与简单的文本、数字和日期变量
- 字段过滤器默认是可选的。如果未给定值,SQL 查询将像字段过滤器不存在一样运行。但是,您可以选择要求一个值。
- 字段过滤器不适用于表别名。由于字段过滤器依赖于表中列的元数据(以及这些表的特定名称),过滤器无法“知道”您已为表设置了别名。而且,根据您使用的数据库,您可能需要在
FROM
子句中包含完整的模式路径。 - 字段过滤器使用特殊语法,以便它们可以在后台处理 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}}
变量只接受单个值。
相比之下,字段过滤器会将变量映射到实际的列数据。连接到变量的过滤器小部件随后“知道”哪些类别可用,并可以呈现这些类别的下拉菜单,如下所示:
关于小部件的一点说明:下拉菜单只是可用选项之一。对于类型为 Category
的字段,例如 Products
表中的 category
字段,我们还可以将过滤器小部件设置为搜索框或纯文本输入框。管理员可以在**管理面板**的**数据模型选项卡**中配置字段设置。
请注意,如果列中不同值的数量大于 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
。
我们不会要求此变量,因此无需默认值。如果查询在过滤器小部件中没有指定值的情况下运行,查询将返回所有类别的记录。
请注意,WHERE
子句没有指定变量应该等于哪一列。这种隐式语法(隐藏的 SQL 代码)允许字段过滤器在后台处理 SQL 代码以适应多项选择。
为日期字段创建复杂的 SQL 过滤器小部件
我们可以创建类型为 Date 的基本输入变量,它将添加一个带有简单日期过滤器的 SQL 过滤器小部件。如果改为使用字段过滤器变量,我们可以将该变量连接到包含日期的字段(列),从而解锁更多配置过滤器小部件的选项。以下是 SQL:
SELECT *
FROM ORDERS
WHERE {{created_at}}
以下是映射到日期字段的字段过滤器的不同小部件类型:
- 月份和年份
- 季度和年份
- 单日
- 日期范围
- 相对日期
- 日期过滤器
每种小部件类型都提供了不同的方式供人们过滤结果。以下是三个 SQL 字段过滤器示例:
日期过滤器小部件类型提供了最大的灵活性,允许人们按相对日期和范围进行过滤。
字段过滤器陷阱
在尝试实现字段过滤器时,人们通常会在以下几个方面遇到困难。
字段过滤器与别名不兼容
如上所述,如果您在 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 代码,以处理多选等情况,例如当用户从下拉菜单中选择多个类别时。
只有某些字段与字段过滤器兼容
这是兼容字段列表。
您可以在我们关于字段过滤器的文档中找到不兼容字段类型的列表。
了解更多关于 SQL 过滤器和变量的信息
请查阅我们关于基本 SQL 输入变量 - 文本、数字和日期的指南。
您还可以阅读我们的文档:
下一步:代码片段:重用和共享代码
了解关于在原生代码中使用代码片段所需的一切。