这是一个高级主题,假设您已经对 SQL 和 LookML 了如指掌。
Looker 通过基于维度和度量方式创建过滤器,自动让用户能够操控查询。虽然这种简单的方法可以满足许多用例的要求,但无法满足所有分析需求。模板化过滤条件和液体参数极大地扩展了您可以支持的可能用例。
从 SQL 的角度来看,维度和指标只能更改查询最外层的 WHERE
或 HAVING
子句。不过,您可能想要允许用户操控 SQL 的其他部分。您可以使用模板化过滤条件和液体参数来启用一些功能,例如调整部分派生表、调整要查询的数据库表或创建多用途维度和过滤条件。
模板化过滤器和液体参数利用液体模板语言将用户输入插入到 SQL 查询中。首先,您使用 LookML 参数创建一个字段供用户与之互动。接下来,使用 Liquid 变量将用户输入注入 SQL 查询。
示例
我们来看几个示例来演示模板化过滤器和液体参数值。
使用模板化过滤条件创建动态派生表
假设有一个计算客户的东北地区生命周期支出的派生表:
view: customer_facts {
derived_table: {
sql:
SELECT
customer_id, -- Can be made a dimension
SUM(sale_price) AS lifetime_spend -- Can be made a dimension
FROM
order
WHERE
region = 'northeast' -- Can NOT be made a dimension
GROUP BY 1
;;
}
}
在此查询中,您可以根据 customer_id
和 lifetime_spend
创建维度。不过,假设您希望用户能够指定 region
,而不是将其硬编码为“northeast”。region
无法作为维度公开,因此用户无法像往常一样对其进行过滤。
一种选择是使用模板化过滤条件,如下所示:
view: customer_facts {
derived_table: {
sql:
SELECT
customer_id,
SUM(sale_price) AS lifetime_spend
FROM
order
WHERE
{% condition order_region %} order.region {% endcondition %}
GROUP BY 1
;;
}
filter: order_region {
type: string
}
}
请参阅下文了解详情。
如果派生表使用模板化过滤条件,则无法使表持久化。
使用 Liquid 参数进行动态测量
考虑一项被过滤的措施,将所售裤子的数量相加:
measure: pants_count {
filters: [category: "pants"]
}
这很简单,但如果有几十个类别,那么为每个类别创建一个衡量指标会非常繁琐。此外,这可能会让用户对探索体验感到困惑。
另一种方法是创建如下所示的动态衡量指标:
measure: category_count {
type: sum
sql:
CASE
WHEN ${category} = '{% parameter category_to_count %}'
THEN 1
ELSE 0
END
;;
}
parameter: category_to_count {
type: string
}
请参阅下文了解详情。
基本用法
第 1 步:创建可供用户与之互动的内容
- 对于模板化过滤条件,请添加
filter
。 - 对于液体参数,请添加
parameter
。
无论采用上述哪种方式,这些字段字段都会显示在字段选择器的仅过滤字段部分下:
filter
和 parameter
字段都可以接受一系列子参数,从而允许您自定义它们的运行方式。如需查看完整列表,请参阅字段参数文档页面。对于 parameter
字段,有两点需要特别注意。
首先,parameter
字段可以使用一种特殊类型(不带英文引号):
parameter: table_name {
type: unquoted
}
这种类型允许将值插入 SQL 而不用引号括起来,就像字符串一样。如果您需要插入表名称等 SQL 值,这会非常有用。
其次,parameter
字段有一个名为允许的值的选项,可用于将用户易于理解的名称与要插入的值相关联。例如:
parameter: sale_price_metric_picker {
description: "Use with the Sale Price Metric measure"
type: unquoted
allowed_value: {
label: "Total Sale Price"
value: "SUM"
}
allowed_value: {
label: "Average Sale Price"
value: "AVG"
}
allowed_value: {
label: "Maximum Sale Price"
value: "MAX"
}
allowed_value: {
label: "Minimum Sale Price"
value: "MIN"
}
}
第 2 步:应用用户输入
第二步是使用 Liquid 根据需要添加模板化过滤条件或 Liquid 参数。
模板化过滤条件
模板化过滤器的语法如下所示:
{% condition filter_name %} sql_or_lookml_reference {% endcondition %}
- 字词
condition
和endcondition
永远不会更改。 - 将
filter_name
替换为您在第一步中创建的过滤条件的名称。如果您没有创建过滤器专用字段,也可以使用维度。 - 将
sql_or_lookml_reference
替换为应设置为“等于”用户输入的 SQL 或 LookML(请参阅下文了解详情)。如果使用 LookML,请使用${view_name.field_name}
LookML 语法。
在上文的示例中,我们使用的是:
{% condition order_region %} order.region {% endcondition %}
务必了解 Liquid 标记与您在其中编写的 SQL 之间的交互。模板化过滤条件标记始终会转换为逻辑表达式。例如,如果用户在 order_region
过滤条件中输入了“Northeast”,Looker 会将这些标记转换为:order.region = 'Northeast'
。换言之,Looker 可以理解用户输入并生成相应的逻辑表达式。
这通常会导致 Looker 开发者感到困惑。模板化过滤器总是会产生某种逻辑表达式,而不是用户输入的单个值。
由于模板化过滤器会返回逻辑表达式,因此您可以将其与 SQL WHERE
语句中有效的其他逻辑运算符和逻辑表达式一起使用。对于上面的示例,如果您要返回用户选择的区域之外的所有值,可以在 WHERE
语句中使用以下代码:
NOT ({% condition order_region %} order.region {% endcondition %})
使用 LookML 字段作为过滤条件也是有效的。直接应用于 LookML 字段的任何过滤器都将确定 WHERE
语句的值:
view: customer_facts {
derived_table: {
sql:
SELECT
customer_id,
SUM(sale_price) AS lifetime_spend
FROM
order
WHERE
{% condition region %} order.region {% endcondition %}
GROUP BY 1
;;
}
dimension: region {
type: string
sql: ${TABLE}.region ;;
}
液体参数
Liquid 参数的语法如下所示:
{% parameter parameter_name %}
parameter
一词不会改变。- 将
parameter_name
替换为您在第一步中创建的parameter
名称。
例如,如需在第 1 步中应用 parameter
字段的输入,您可以创建如下测量值:
measure: sale_price_metric {
description: "Use with the Sale Price Metric Picker filter-only field"
type: number
label_from_parameter: sale_price_metric_picker
sql: {% parameter sale_price_metric_picker %}(${sale_price}) ;;
value_format_name: usd
}
在模板过滤条件和液体参数之间进行选择
虽然模板化过滤条件和液体参数很相似,但两者之间存在一项重要区别:
如果您希望为用户提供更灵活的输入(例如使用各种日期范围或字符串搜索),请尽量使用模板化过滤条件。Looker 可以解读用户输入,并在后台编写适当的 SQL。这样一来,您就不必将每种可能的用户输入类型都考虑在内。
如果无法插入逻辑语句,或者知道用户可能会输入的一组有限选项,请使用流式参数。