模板化过滤条件和 Liquid 参数

本主题属于高级主题,适用于对 SQL 和 LookML 有较深的了解的用户。

Looker 会自动为用户提供创建过滤条件的功能,以便用户根据维度和测量参数来操控查询。虽然这种方法可以满足许多用例,但并不能满足所有分析需求。模板化过滤器和 Liquid 参数极大地扩展了您支持的可能用例。

从 SQL 的角度来看,维度和测量只能改变查询中最外层的 WHEREHAVING 子句。不过,您可能需要让用户操控 SQL 的其他部分。您可以使用模板化过滤条件和 Liquid 参数启用部分功能,包括调整派生表的一部分、调整要查询的数据库表或创建多用途维度和过滤条件。

模板化过滤器和 Liquid 参数利用 Liquid 模板语言将用户输入插入到 SQL 查询中。首先,使用 LookML 参数创建一个字段以供用户与之互动。接下来,您将使用 Liquid 变量将用户输入注入 SQL 查询。

示例

我们来看几个示例来说明模板化过滤器和 Liquid 参数的值。

使用模板化过滤条件创建动态派生表

假设有一个派生表,用于计算客户在东北地区的生命周期支出:

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_idlifetime_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
  • 对于 Liquid 参数,请添加 parameter

无论是哪种情况,用户都会在字段选择器的仅限过滤条件的字段部分看到这些字段。

filterparameter 字段都可以接受一系列子参数,以便您自定义它们的操作方式。如需查看完整列表,请参阅字段参数文档页面。有两个选项特别适用于 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 %}
  • conditionendcondition 这两个词始终不变。
  • filter_name 替换为您在第一步中创建的过滤条件的名称。如果您未创建仅用于过滤的字段,也可以使用维度。
  • sql_or_lookml_reference 替换为应设置为与用户输入“相等”的 SQL 或 LookML(本部分稍后会对此进行详细说明)。如果使用 LookML,请使用 ${view_name.field_name} LookML 语法。

在前面的示例(使用模板过滤条件创建动态派生表)中,我们使用了:

{% condition order_region %} order.region {% endcondition %}

请务必了解 Liquid 标记与您在标记之间编写的 SQL 之间的交互。这些基于模板的过滤条件标记始终会转换为逻辑表达式。例如,如果用户在 order_region 过滤器中输入“东北”,Looker 会将这些代码转换为以下代码:

order.region = 'Northeast'

换句话说,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 参数

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
  }

在模板过滤条件和 Liquid 参数之间进行选择

虽然基于模板的过滤条件和 Liquid 参数相似,但二者之间存在一个重要区别:

如果您希望为用户提供更灵活的输入方式(例如使用各种日期范围或字符串搜索),请尽可能尝试使用模板化过滤器。Looker 可以解读用户输入并在后台编写适当的 SQL。这样一来,您就不必将每一种可能的用户输入类型都考虑在内。

如果无法插入逻辑语句,或者您知道用户可能会输入的一组有限选项,请使用 Liquid 参数。