派生表可实现各种高级分析，但其方法、实现和问题排查可能会让人望而却步。此实用指南包含 Looker 中派生表的最常见用例。
本页包含以下示例：
- 每天凌晨 3 点构建表格
- 将新数据附加到大型表格
- 使用 SQL 窗口函数
- 为计算值创建派生列
- 优化策略
- 使用 PDT 测试优化效果
UNION两个表- 对总和求和（将指标纳入维度）
- 汇总包含汇总认知度的表格
派生表资源
这些实用指南假定您对 LookML 和派生表有初步了解。您应该能够自如地创建视图和修改模型文件。如果您想重温一下上述任何主题，请查看以下资源：
每天凌晨 3 点构建表
在此示例中，数据每天凌晨 2 点到达。无论是在凌晨 3 点还是晚上 9 点运行，针对此数据的查询结果都将相同。因此，每天构建一次表并让用户从缓存中提取结果是有意义的。
在模型文件中添加数据组后，您就可以在多个表和探索中重复使用该数据组。此数据组包含一个 sql_trigger_value 参数，用于告知数据组何时触发并重建派生表。
如需查看更多触发表达式示例，请参阅 sql_trigger_value 文档。
## in the model file
datagroup: standard_data_load {
sql_trigger_value: SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24)) ;;
max_cache_age: "24 hours"
}
explore: orders {
…
在视图文件中向 derived_table 定义添加 datagroup_trigger 参数，并指定要使用的数据组的名称。在此示例中，数据组为 standard_data_load。
view: orders {
derived_table: {
indexes: ["id"]
datagroup_trigger: standard_data_load
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
…
}
将新数据附加到大型表
增量 PDT 是一种永久性派生表，Looker 通过将最新数据附加到该表来构建该表，而不是完全重新构建该表。
下一个示例基于 orders 表示例，展示了如何以增量方式构建表。每天都会有新的订单数据，当您添加 increment_key 参数和 increment_offset 参数时，可以将这些数据附加到现有表中。
view: orders {
derived_table: {
indexes: ["id"]
increment_key: "created_at"
increment_offset: 3
datagroup_trigger: standard_data_load
distribution_style: all
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
dimension: id {
primary_key: yes
type: number
sql: ${TABLE}.id ;; }
…
}
increment_key 值设置为 created_at，这是在此示例中应查询并附加到 PDT 的新数据的时间增量。
increment_offset 值设置为 3，用于指定重新构建的先前时间段（以增量键的粒度为单位）的数量，以考虑迟到的数据。
使用 SQL 窗口函数
某些数据库方言支持窗口函数，尤其是在创建序列号、主键、运行总数和累计总数以及其他有用的多行计算时。在执行主查询后，系统会在单独的传递中执行所有 derived_column 声明。
如果您的数据库方言支持窗口函数，您可以在原生派生表中使用它们。创建一个 derived_column 参数，其中包含您的窗口函数的 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
}
}
为计算值创建派生列
您可以添加 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: users.id
}
column: lifetime_number_of_orders {
field: order_items.count
}
column: lifetime_customer_value {
field: order_items.total_profit
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
优化策略
由于 PDT 存储在数据库中，因此您应根据所用方言支持的策略，通过以下策略优化 PDT：
例如，如需添加持久性，您可以将 PDT 设置为在数据组 orders_datagroup 触发时重建，然后您可以同时在 customer_id 和 first_order 上添加索引，如下所示：
view: customer_order_summary {
derived_table: {
explore_source: orders {
...
}
datagroup_trigger: orders_datagroup
indexes: ["customer_id", "first_order"]
}
}
如果您未添加索引（或与您的方言等效的索引），Looker 会警告您应添加索引以提高查询性能。
使用 PDT 测试优化效果
您可以使用 PDT 来测试不同的索引、分布和其他优化选项，而无需 DBA 或 ETL 开发者提供大量支持。
假设您有一个表，但想测试不同的索引。视图的初始 LookML 可能如下所示：
view: customer {
sql_table_name: warehouse.customer ;;
}
如需测试优化策略，您可以使用 indexes 参数向 LookML 添加索引，如下所示：
view: customer {
# sql_table_name: warehouse.customer
derived_table: {
sql: SELECT * FROM warehouse.customer ;;
persist_for: "8 hours"
indexes: [customer_id, customer_name, salesperson_id]
}
}
查询视图一次以生成 PDT。然后，运行测试查询并比较结果。如果结果理想，您可以让 DBA 或 ETL 团队将索引添加到原始表中。
UNION 两个表
如果您的 SQL 方言支持，您可以在两个派生表中执行 SQL UNION 或 UNION ALL 运算符。UNION 和 UNION ALL 运算符用于合并两个查询的结果集。
此示例展示了基于 SQL 的派生表在包含 UNION 时会是什么样子：
view: first_and_second_quarter_sales {
derived_table: {
sql:
SELECT * AS sales_records
FROM sales_records_first_quarter
UNION
SELECT * AS sales_records
FROM sales_records_second_quarter ;;
}
}
sql 参数中的 UNION 语句会生成一个派生表，其中包含这两个查询的结果。
UNION 和 UNION ALL 之间的区别在于，UNION ALL 不会移除重复行。使用 UNION 与 UNION ALL 时，需要注意性能方面的考虑因素，因为数据库服务器必须执行额外的工作来移除重复行。
计算总和的总和（将衡量指标纳入维度）
根据 SQL 中的一般规则（以及 Looker 中的相应规则），您无法按聚合函数（在 Looker 中表示为度量）的结果对查询进行分组。您只能按未汇总的字段（在 Looker 中表示为维度）进行分组。
如需按汇总进行分组（例如，计算总和的总和），您需要将指标“维度化”。一种方法是使用派生表，这样可以有效地创建汇总的子查询。
从探索开始，Looker 可以为所有或大多数派生表生成 LookML。只需创建探索，然后选择要纳入派生表中的所有字段即可。然后，如需生成原生（或基于 SQL 的）派生表 LookML，请按照以下步骤操作：
点击探索的齿轮菜单，然后选择获取 LookML。
如需查看用于为探索创建原生派生表的 LookML，请点击派生表标签页。
复制 LookML。
现在，您已复制生成的 LookML，请按照以下步骤将其粘贴到视图文件中：
在 Looker IDE 中点击项目文件列表顶部的 +，然后选择 Create View。或者，如需在文件夹内创建文件，请点击文件夹的菜单，然后选择创建视图。
将视图名称设置为有意义的名称。
您可以选择更改列名称、指定派生列和添加过滤条件。
具有汇总感知能力的汇总表
在 Looker 中，您可能会经常遇到非常大的数据集或表，为了获得良好的性能，需要使用汇总表或汇总。
借助 Looker 的汇总感知功能，您可以预先构建不同粒度、维度和汇总级别的汇总表；并且可以告知 Looker 如何在现有探索中利用这些汇总表。然后，Looker 会在认为适当的情况下使用这些汇总表，而无需任何用户输入。这样可以减小查询规模、缩短等待时间并提升用户体验。
以下示例展示了 Looker 模型中的一个非常简单的实现，用于演示轻量级汇总感知功能。假设数据库中有一个航班表，其中包含美国联邦航空管理局 (FAA) 记录的每趟航班对应的一行数据，您可以在 Looker 中使用自己的视图和探索来对该表进行建模。以下是您可以为探索定义的汇总表的 LookML：
explore: flights {
aggregate_table: flights_by_week_and_carrier {
query: {
dimensions: [carrier, depart_week]
measures: [cancelled_count, count]
}
materialization: {
sql_trigger_value: SELECT CURRENT-DATE;;
}
}
}
有了此汇总表，用户就可以查询 flights 探索，而 Looker 会自动使用汇总表来回答查询。如需详细了解汇总感知，请参阅汇总感知教程。