创建原生派生表

派生表是一种查询,其结果将派生表用作数据库中的实体表。原生派生表基于您使用 LookML 术语定义的查询。这与基于 SQL 的派生表不同,后者基于您使用 SQL 术语定义的查询。与基于 SQL 的派生表相比,在对数据进行建模时,原生派生表更易于阅读和理解。如需了解详情,请参阅 Looker 中的派生表文档页面的原生派生表和基于 SQL 的派生表部分。

原生表和基于 SQL 的派生表在 LookML 中使用视图级别的 derived_table 参数进行定义。但是,对于原生派生表,您无需创建 SQL 查询。不过,您可以使用 explore_source 参数来指定派生表格、所需列和其他所需特征的“探索”部分。

您还可以让 Looker 根据 SQL Runner 查询创建派生表 LookML,如使用 SQL Runner 创建派生表文档页面中所述。

使用探索开始定义原生派生表

从探索开始,Looker 可以为您的所有或大部分派生表生成 LookML。只需创建一个探索,然后选择要添加到派生表中的所有字段即可。然后,如需生成原生派生表 LookML,请按以下步骤操作:

  1. 选择探索操作齿轮菜单,然后选择获取 LookML

  2. 点击派生表标签页,查看用于为探索创建原生派生表的 LookML。

  3. 复制该 LookML。

现在,您已复制生成的 LookML,请将其粘贴到视图文件中:

  1. 开发模式下,前往项目文件

  2. 在 Looker IDE 中,点击项目文件列表顶部的 +,然后选择创建视图。或者,您也可以点击文件夹的菜单,然后从菜单中选择创建视图,以在文件夹中创建文件

  3. 将视图名称设置为有意义的名称。

  4. (可选)更改列名称、指定派生列以及添加过滤条件。

在探索中使用 type: count度量时,可视化图表会使用视图名称(而非“计数”一词)来标记结果值。为避免混淆,请使用复数形式的视图名称。如需更改视图名称,您可以选择可视化设置中的“系列”下方的显示完整字段名称,也可以使用 view_label 参数和视图名称的复数形式。

在 LookML 中定义原生派生表

无论您使用 SQL 中声明的派生表还是原生 LookML,derived_table 查询的输出都是包含一组列的表。如果派生表使用 SQL 表示,则 SQL 查询暗含输出列名称。例如,以下 SQL 查询将具有输出列 user_idlifetime_number_of_orderslifetime_customer_value

SELECT
  user_id
  , COUNT(DISTINCT order_id) as lifetime_number_of_orders
  , SUM(sale_price) as lifetime_customer_value
FROM order_items
GROUP BY 1

在 Looker 中,查询基于“探索”,包含测量和维度字段,添加了所有适用的过滤条件,还可以指定排序顺序。原生派生表包含所有这些元素,以及列的输出名称。

以下简单示例会生成一个包含三列的派生表:user_idlifetime_customer_valuelifetime_number_of_orders。您无需在 SQL 中手动编写查询,Looker 会使用指定的“探索”order_items 和该“探索”的部分字段(order_items.user_idorder_items.total_revenueorder_items.order_count)为您创建查询。

view: user_order_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: lifetime_number_of_orders {
        field: order_items.order_count
      }
      column: lifetime_customer_value {
        field: order_items.total_revenue
      }
    }
  }
  # Define the view's fields as desired
  dimension: user_id {
    hidden: yes
  }
  dimension: lifetime_number_of_orders {
    type: number
  }
  dimension: lifetime_customer_value {
    type: number
  }
}

使用 include 语句启用引用字段

在原生派生表的视图文件中,您可以使用 explore_source 参数指向探索,并为原生派生表定义列和其他特性。

在原生派生表的视图文件中,您无需使用 include 参数指向包含探索定义的文件。如果您没有 include 语句,Looker IDE 在构建原生派生表时不会自动建议字段名称或验证字段引用。您可以改为使用 LookML 验证程序来验证要在原生派生表中引用的字段。

但是,如果您想在 Looker IDE 中启用自动建议和即时字段验证,或者如果您的复杂 LookML 项目包含多个具有相同名称或潜力的“循环引用”探索,则可以使用 include 参数指向“探索”定义的位置。

探索通常在模型文件中定义,但对于原生派生表,为探索创建一个单独的文件会更加简洁。LookML 探索文件的文件扩展名为 .explore.lkml,如创建探索文件文档中所述。这样,您可以在原生派生表格视图文件中包含单个“探索”文件,而不是整个模型文件。

如果您想创建一个单独的“Explore”文件并使用 include 参数指向原生派生表的视图文件中的“Explore”文件,请确保您的 LookML 文件满足以下要求:

  • 原生派生表的视图文件应包含“探索”的文件。例如:
    • include: "/explores/order_items.explore.lkml"
  • 探索文件应包含所需的视图文件。例如:
    • include: "/views/order_items.view.lkml"
    • include: "/views/users.view.lkml"
  • 该模型应包含“探索”文件。例如:
    • include: "/explores/order_items.explore.lkml"

定义原生派生表列

上述示例所示,您使用 column 指定派生表的输出列。

指定列名称

对于 user_id 列,列名称与原始“探索”中指定字段的名称一致。

您经常需要输出表中的列名称与原始“探索”中的字段名称不同。上述示例使用 order_items“探索”功能按用户计算了生命周期价值。在输出表格中,total_revenue 实际上是客户的 lifetime_customer_value

column 声明支持声明与输入字段不同的输出名称。例如,以下代码会指示 Looker“在字段 order_items.total_revenue 中创建名为 lifetime_value 的输出列”:

column: lifetime_value {
  field: order_items.total_revenue
}

隐含的列名称

如果列声明中省略了 field 参数,系统会假定该参数为 <explore_name>.<field_name>。例如,如果您指定了 explore_source: order_items,则

column: user_id {
  field: order_items.user_id
}

等效于

column: user_id {}

为计算值创建派生列

您可以添加 derived_column 参数,以指定 explore_source 参数的“探索”部分中不存在的列。每个 derived_column 形参都有一个 sql 形参,用于指定如何构造值。

sql 计算可使用您通过 column 参数指定的任何列。派生列不能包含聚合函数,但可以包含可对表中的单个行执行的计算。

以下示例生成的派生表与前面的示例相同,只是添加了计算的 average_customer_order 列,该列是根据原生派生表中的 lifetime_customer_valuelifetime_number_of_orders 列计算得出的。

view: user_order_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: lifetime_number_of_orders {
        field: order_items.order_count
      }
      column: lifetime_customer_value {
        field: order_items.total_revenue
      }
      derived_column: average_customer_order {
        sql:  lifetime_customer_value / lifetime_number_of_orders ;;
      }
    }
  }
  # Define the view's fields as desired
  dimension: user_id {
    hidden: yes
  }
  dimension: lifetime_number_of_orders {
    type: number
  }
  dimension: lifetime_customer_value {
    type: number
  }
  dimension: average_customer_order {
    type: number
  }
}

使用 SQL 窗口函数

某些数据库方言支持窗口函数,特别是用于创建序列号、主键、运行和累计总计以及其他有用的多行计算。在主查询执行完毕后,任何 derived_column 声明都会单独执行。

如果您的数据库方言支持窗口函数,则可以在原生派生表中使用这些函数。创建一个包含所需窗口函数的 derived_column 参数和 sql 参数。引用值时,应该按照原生派生表中定义的列名。

以下示例会创建一个包含 user_idorder_idcreated_time 列的原生派生表。然后,使用包含 SQL ROW_NUMBER() 窗口函数的派生列,计算包含客户订单序列号的列。

view: user_order_sequences {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: order_id {
        field: order_items.order_id
      }
      column: created_time {
        field: order_items.created_time
      }
      derived_column: user_sequence {
        sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
      }
    }
  }
  dimension: order_id {
    hidden: yes
  }
  dimension: user_sequence {
    type: number
  }
}

向原生派生表添加过滤条件

假设您要基于客户过去 90 天的价值构建一个派生表,您希望执行与上例中相同的计算,但只包含过去 90 天内的购买交易。

您只需为 derived_table 添加一个过滤条件,用于过滤过去 90 天内的交易。派生表的 filters 参数使用与创建过滤后的测量值时所用的语法相同。

view: user_90_day_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: number_of_orders_90_day {
        field: order_items.order_count
      }
      column: customer_value_90_day {
        field: order_items.total_revenue
      }
      filters: [order_items.created_date: "90 days"]
    }
  }
  # Add define view's fields as desired
  dimension: user_id {
    hidden: yes
  }
  dimension: number_of_orders_90_day {
    type: number
  }
  dimension: customer_value_90_day {
    type: number
  }
}

当 Looker 为派生表写入 SQL 时,会将过滤条件添加到 WHERE 子句。

此外,您还可以将 explore_sourcedev_filters 子参数用于原生派生表。借助 dev_filters 参数,您可以指定 Looker 仅适用于派生表开发版本的过滤条件,这意味着您可以构建过滤后的表较小版本以进行迭代和测试,而无需等待每次更改后都构建完整表。

dev_filters 参数与 filters 参数结合使用,以便将所有过滤条件应用于表格的开发版本。如果 dev_filtersfilters 都为同一列指定了过滤条件,则对于表的开发版本,dev_filters 优先于 filters

如需了解详情,请参阅在开发模式下更快地工作

使用模板过滤器

您可以使用 bind_filters 添加模板过滤器

bind_filters: {
  to_field: users.created_date
  from_field: filtered_lookml_dt.filter_date
}

这与在 sql 块中使用以下代码本质上相同:

{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}

to_field 是应用过滤条件的字段。to_field 必须是底层 explore_source 中的一个字段。

from_field 指定从中获取过滤条件的字段(如果运行时有过滤条件)。

在前面的 bind_filters 示例中,Looker 会获取应用于 filtered_lookml_dt.filter_date 字段的任何过滤条件,并将该过滤条件应用于 users.created_date 字段。

您还可以使用 explore_sourcebind_all_filters 子参数,将所有运行时过滤条件从“探索”传递到原生派生表子查询。如需了解详情,请参阅 explore_source 参数文档页面。

对原生派生表进行排序和限制

您还可以根据需要对派生表进行排序限制

sorts: [order_items.count: desc]
limit: 10

请注意,探索会以与底层排序不同的顺序显示行。

将原生派生表转换为不同的时区

您可以使用 timezone 子参数为原生派生表指定时区:

timezone: "America/Los_Angeles"

使用 timezone 子参数时,原生派生表中的所有基于时间的数据都将转换为您指定的时区。如需查看支持的时区列表,请参阅 timezone文档页面。

如果您未在原生派生表定义中指定时区,则原生派生表不会对基于时间的数据执行任何时区转换,而是将基于时间的数据默认为您的数据库时区

如果原生派生表不是永久性的,您可以将时区值设置为 "query_timezone",以自动使用当前运行查询的时区。