派生表是一种查询,其结果就像派生表是数据库中的物理表一样。原生派生表基于您使用 LookML 术语定义的查询。这与基于 SQL 的派生表不同,后者基于您使用 SQL 术语定义的查询。与基于 SQL 的派生表相比,在为数据建模时,原生派生表更易于阅读和理解。如需了解详情,请参阅 Looker 中的派生表文档页面的原生派生表和基于 SQL 的派生表部分。
原生表和基于 SQL 的派生表均在 LookML 中使用视图级别的 derived_table
参数进行定义。但是,对于原生派生表,您无需创建 SQL 查询。不过,您可以使用 explore_source
参数来指定派生表所依据的探索、所需的列和其他所需的特征。
您还可以让 Looker 根据 SQL Runner 查询创建派生表 LookML,如使用 SQL Runner 创建派生表文档页面中所述。
使用“探索”工具开始定义原生派生表
从“探索”开始,Looker 可以为您的所有或大部分派生表生成 LookML。只需创建一个探索,然后选择要添加到派生表中的所有字段即可。然后,如需生成原生派生表 LookML,请按以下步骤操作:
选择 Explore Actions 齿轮菜单,然后选择 Get LookML。
点击 Derived Table 标签页,查看用于为探索创建原生派生表的 LookML。
复制该 LookML。
现在,您已复制生成的 LookML,请将其粘贴到视图文件中:
点击 Looker IDE 中的项目文件列表顶部的 +,然后选择创建视图。或者,您也可以点击文件夹的菜单,然后从菜单中选择创建视图,在文件夹内创建该文件。
为视图设置有意义的名称。
(可选)更改列名称、指定派生列并添加过滤条件。
当您在“探索”中使用
type: count
的测量时,可视化图表会使用视图名称(而不是单词“Count”)来标记结果值。为避免混淆,请使用复数形式的视图名称。要更改视图名称,您可以在可视化设置中选择数据系列下的显示完整字段名称,也可以将view_label
参数与视图名称的复数形式搭配使用。
在 LookML 中定义原生派生表
无论您使用在 SQL 中声明的派生表还是在原生 LookML 中声明的派生表,derived_table
查询的输出都是包含一组列的表。当以 SQL 表示派生表时,SQL 查询会隐含输出列名称。例如,以下 SQL 查询将包含输出列 user_id
、lifetime_number_of_orders
和 lifetime_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_id
、lifetime_customer_value
和 lifetime_number_of_orders
。您无需在 SQL 中手动编写查询,Looker 会使用指定的探索 order_items
和该探索的部分字段(order_items.user_id
、order_items.total_revenue
和 order_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
参数指向探索,并为原生派生表定义所需的列和其他所需的特征。由于您是在原生派生表的视图文件中指向某个探索,因此还必须包含包含探索定义的文件。探索通常在模型文件中定义,但对于原生派生表,使用 .explore.lkml
文件扩展名为探索创建单独的文件会更简洁,如创建探索文件文档中所述。这样,在原生派生的表格视图文件中,您便可以添加单个“探索”文件,而不是添加整个模型文件。在这种情况下:
- 原生派生表的视图文件应包含探索的文件。例如:
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_value
和 lifetime_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
声明都将在单独的传递中执行。
如果您的数据库方言支持 window 函数,那么您可以在原生派生表中使用它们。创建一个 derived_column
形参以及包含所需 window 函数的 sql
形参。引用值时,您应该使用原生派生表中定义的列名称。
以下示例创建了一个包含 user_id
、order_id
和 created_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_source
的 dev_filters
子参数用于原生派生表。借助 dev_filters
参数,您可以指定 Looker 仅适用于开发版派生表的过滤条件,这意味着您可以构建经过过滤的较小表版本进行迭代和测试,而无需等待每次更改后完整表进行构建。
dev_filters
参数可与 filters
参数结合使用,以便将所有过滤条件应用于表的开发版。如果 dev_filters
和 filters
为同一列指定了过滤条件,则 dev_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_source
的 bind_all_filters
子参数,将“探索”中的所有运行时过滤条件传递到原生派生表子查询。如需了解详情,请参阅 explore_source
参数文档页面。
对原生派生表进行排序和限制其数量
sorts: [order_items.count: desc]
limit: 10
请注意,探索的显示顺序可能与底层排序不同。
将原生派生表转换为不同时区
您可以使用 timezone
子参数为原生派生表指定时区:
timezone: "America/Los_Angeles"
使用 timezone
子参数时,原生派生表中所有基于时间的数据都将转换为您指定的时区。如需查看受支持时区的列表,请参阅 timezone
值文档页面。
如果您没有在原生派生表定义中指定时区,则原生派生表将不会对基于时间的数据执行任何时区转换,基于时间的数据将默认使用您的数据库时区。
如果原生派生表不是永久性表,您可以将时区值设置为 "query_timezone"
,以自动使用当前正在运行的查询的时区。