纳入 SQL 并引用 LookML 对象

若要编写强大的 LookML，您需要能够引用现有维度、测量值、视图或派生表，即使它们不在当前范围内也是如此。您还需要引用底层表中的列，并使用数据库方言的函数调用来操控这些值。

替换运算符 ($)

替换运算符 $ 可让 LookML 代码更具可重用性和模块化，让您能够引用其他视图和派生表、SQL 表中的列或 LookML 维度和测量。这样做有两个好处。首先，您可能已经解决了一个非常棘手的维度或测量，而无需再次编写所有复杂内容。其次，如果您更改维度或测量指标的某些内容，相应更改可能会传播到依赖于该维度或测量指标的所有其他内容。

您可以通过多种方式使用替换运算符：

${TABLE}.column_name 引用表中与您正在处理的视图相关联的列。例如：

dimension: customer_id {
  type: number
  sql: ${TABLE}.customer_id ;;
}

${field_name} 引用您所处理视图中的维度或测量值。例如：

measure: total_population {
  type: sum
  sql: ${population} ;;
}

${view_name.field_name} 引用其他视图中的维度或测量。例如：

dimension: lifetime_orders {
  type: number
  sql: ${user_order_facts.lifetime_orders} ;;
}

${view_name.SQL_TABLE_NAME} 引用其他视图或派生表。请注意，此引用中的 SQL_TABLE_NAME 是字面量字符串；您无需将其替换为任何内容。例如：

explore: trips {
  view_label: "Long Trips"
  # This will ensure that we only see trips that are longer than average!
  sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}

${view_name.SQL_TABLE_NAME} 不适用于与 datagroups 一起使用的 sql_trigger 参数。

范围和命名

您可以为探索、视图、字段和集命名。这些 Looker 标识符在编写时不带引号。

LookML 字段和集合具有完整名称和短名称：

  • 完整名称的格式为 <view>.<field-name | set-name>。左侧表示范围，即包含字段或集合的视图。右侧指定特定字段或集名称。
  • 简称采用简单的 <field-name | set-name> 格式，不带分隔的英文句号。Looker 会根据短名称的使用范围将其展开为全名。

以下示例展示了许多形式的名称和作用域。这组字段并不现实，但展示它们是为了演示各种可能的范围限定表达式。

view: orders {                   # "orders" becomes the containing scope
  measure: count {               # short name, equivalent to orders.count
    type: count
  }
  dimension: customer_id {       # short name, equivalent to orders.customer_id
    type: number
    sql: ${TABLE}.customer_id ;;
  }
  dimension: customer_address {  # short name, equivalent to orders.customer_address
    sql: ${customer.address} ;;  # full name, references a field defined in the "customer" view
  }
  set: drill_fields {            # short name, equivalent to orders.drill_fields
    fields: [
      count,                     # short name, equivalent to orders.count
      customer.id                # full name, references a field defined in the "customer" view
    ]
  }
}

请注意，在 dimension: customer_address 声明中，SQL 代码块的底层视图 (customer) 不同于封闭的视图作用域 (orders)。当您需要比较两个不同视图之间的字段时，这可能会很有用。

当一个视图（我们将其称为“视图 A”）引用另一个视图（我们将其称为“视图 B”）中定义的字段时，请注意以下几点：

  1. 视图 B 文件必须与视图 A 包含在同一模型中，并使用 include 参数。
  2. 视图 B 必须与一个或多个探索中的视图 A 联接。如需了解联接，请参阅我们的 在 LookML 中使用联接页面。

SQL 方言

Looker 支持许多数据库类型，例如 MySQL、Postgres、Redshift、BigQuery 等。每个数据库支持的功能集略有不同，函数名称也不同，称为 SQL 方言。

LookML 旨在与所有 SQL 方言搭配使用，并且不会偏向于某种方言。不过，您需要在某些 LookML 参数中添加 SQL 代码表达式（称为 SQL 块）。使用这些参数后，Looker 会将 SQL 表达式直接传递给您的数据库，因此您必须使用与数据库匹配的 SQL 方言。例如，如果您使用 SQL 函数，则该函数必须是数据库支持的函数。

SQL 块

某些 LookML 参数需要您提供原始 SQL 表达式，以便 Looker 了解如何从数据库中检索数据。

以 sql_ 开头的 LookML 参数需要某种形式的 SQL 表达式。示例：sql_always_where、sql_on 和 sql_table_name。SQL 块最常见的 LookML 参数是 sql，用于在维度和测量字段定义中指定用于定义维度或测量的 SQL 表达式。

您在 SQL 块中指定的代码可以像单个字段名称一样简单，也可以像相关联的子选择一样复杂。内容可能非常复杂，可满足您在原始 SQL 中表达自定义查询逻辑的几乎所有需求。请注意，您在 SQL 块中使用的代码必须与数据库使用的 SQL 方言一致。

维度和测量的 SQL 代码块示例

以下是维度和测量的 SQL 块示例。LookML 替换运算符 ($) 可能会使这些 sql 声明看起来与 SQL 不符。不过，替换完成后，生成的字符串是纯 SQL，Looker 会将其注入到查询的 SELECT 子句中。

dimension: id {
  primary_key: yes
  sql: ${TABLE}.id ;;   # Specify the primary key, id
}
measure: average_cost {
  type: average
  value_format: "0.00"
  sql: ${order_items.cost} ;;   # Specify the field that you want to average
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: int
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

如最后两个维度所示，SQL 块可以使用底层数据库支持的函数（例如本例中的 MySQL 函数 CONCAT 和 DATEDIFF）。

包含相关子选择的 SQL 块示例

您可以在字段的 SQL 块中放置任何 SQL 语句，包括相关联的子选择。下面给出了一个示例：

view: customers {
  dimension: id {
    primary_key: yes
    sql: ${TABLE}.id ;;
  }
  dimension: first_order_id {
    sql: (SELECT MIN(id) FROM orders o WHERE o.customer_id=customers.id) ;;
         # correlated subselect to derive the value for "first_order_id"
  }
}

派生表的 SQL 代码块示例

派生表使用 SQL 块来指定派生表的查询。下面给出了一个示例：

view: user_order_facts {
  derived_table: {
    sql:            # Get the number of orders for each user
      SELECT
        user_id
        , COUNT(*) as lifetime_orders
      FROM orders
      GROUP BY 1 ;;
  }
  # later, dimension declarations reference the derived column(s)

  dimension: lifetime_orders {
    type: number
  }
}

LookML 字段类型引用

在另一个字段中引用现有 LookML 字段时，您可以使用双冒号 (::) 后跟所需类型，指示 Looker 将引用的字段视为特定数据类型。例如，如果您在其他字段中引用 orders.created_date 维度，则可以使用语法 ${orders.created_date::date} 来确保 created_date 字段在 Looker 生成的 SQL 中被视为日期字段，而不是被转换为字符串。

您可以在引用中使用的数据类型取决于您引用的原始字段的数据类型。例如，如果您引用的是字符串字段，则只能指定 ::string 这一数据类型。以下是可用于每种字段类型的允许字段类型引用的完整列表：

  • 在对字符串字段的引用中，您可以使用 ::string。
  • 在对数字字段的引用中，您可以使用 ::string 和 ::number。
  • 在对日期或时间字段的引用中，您可以使用 ::string、::date 和 ::datetime。

    使用 ::string 和 ::date 的引用会返回查询时区中的数据，而使用 ::datetime 的引用会返回数据库时区中的数据。
  • 在对是/否字段的引用中，您可以使用 ::string、::number 和 ::boolean。

    不支持布尔值数据类型的数据库方言不支持使用 ::boolean 类型的字段引用。
  • 在对位置字段的引用中，您可以使用 ::latitude 和 ::longitude。

将 LookML 字段类型引用与日期字段搭配使用

例如，假设您有一个 enrollment_month 维度和一个 graduation_month 维度，这两个维度都是在 type: time 的维度组中创建的。在本示例中，enrollment_month 维度由 type: time 的以下维度组生成：


dimension_group: enrollment {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.enrollment_date ;;
}

同样，graduation_month 维度由 type: time 的以下维度组创建：


dimension_group: graduation {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.graduation_date ;;
}

您可以使用 enrollment_month 和 graduation_month 维度创建type: duration 维度组，从而计算学生从入学到毕业所经历的月数或年数。不过，由于某些日期字段会在 Looker 生成的 SQL 中转换为字符串，因此将 enrollment_month 和 graduation_month 维度设置为 sql_start 和 sql_end 的值可能会导致错误。

为避免因将这些时间字段转换为字符串而导致错误，一种方法是创建一个维度组 type: duration，在 sql_start 和 sql_end 参数中引用 enrollment 和 graduation 维度组中的 raw 时间范围：


dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_raw} ;;
  sql_end: ${graduation_raw} ;;
}

在“探索”界面中，这会生成一个名为“注册时长”的维度组，其中包含“注册月数”和“注册年数”两个单独的维度。

与在 type: duration 的维度组中使用 raw 时间范围相比，更简单的替代方法是为 sql_start 和 sql_end 参数中引用的字段指定 ::date 或 ::datetime 引用类型。


dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_month::date} ;;
  sql_end: ${graduation_month::date} ;;
}

此示例中的 LookML 还会创建一个注册时长维度组，但使用 ::date 引用后，您无需使用 raw 时间范围或使用 SQL 将其转换为字符串，即可使用 enrollment_month 和 graduation_month 维度。

如需有关如何使用 LookML 字段类型引用创建 type: duration 的自定义维度组的其他示例，请参阅 dimension_group 参数文档页面。

此语法不适用于 type: list 衡量标准，因为自 Looker 6.8 起，该衡量标准已无法引用。

LookML 常量

借助 constant 参数，您可以指定一个可在整个 LookML 项目中使用的常量。借助 LookML 常量，您只需定义一次值，即可在接受字符串的项目任何部分引用该值，从而减少 LookML 代码中的重复。

常量必须在项目清单文件中声明，并且常量的值必须为字符串。例如，您可以按如下方式定义值为 "Okayama" 的常量 city：

constant: city {
  value: "Okayama"
}

然后，您可以在整个项目中使用语法 @{city} 引用 city 常量。例如，您可以在 users 探索中将 city 常量与 label 参数搭配使用：


explore: users {
  label: "@{city} Users"
}

然后，Looker 会在探索菜单和“探索”标题中显示“冈山用户”，而不是默认的“用户”。

如需详细了解如何使用 LookML 常量编写可重复使用的代码，以及相关示例，请参阅 constant 参数文档页面。