请注意，您正在查看 Looker 文档。如需查看 Looker Studio 文档，请访问 https://support.google.com/looker-studio。

Looker 实战宝典：充分利用 Looker 中的派生表

派生表提供了大量的高级分析可能性，但使用派生表处理、实施和问题排查可能会令人望而生畏。本实战宝典包含 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 {
…

将 datagroup_trigger 参数添加到视图文件的 derived_table 定义中，然后指定要使用的数据组的名称。在此示例中，数据组为 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 声明都将在单独的传递中执行。

如果数据库方言支持窗口函数，您可以在原生派生表中使用它们。使用包含 window 函数的 sql 形参创建一个 derived_column 形参。引用值时，您应该使用原生派生表中定义的列名称。

以下示例展示了如何创建一个包含 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 运算符可将两个查询的结果集合并在一起。

以下示例展示了使用 UNION 时基于 SQL 的派生表的外观：

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 中的项目文件列表顶部的 +，然后选择创建视图。或者，您也可以在文件夹内创建文件，也可以点击文件夹的菜单，然后选择创建视图。
为视图设置有意义的名称。
（可选）更改列名称、指定派生列并添加过滤条件。

具有汇总认知度的汇总表

在 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 会自动使用汇总表来回答查询。如需查看有关总体认知度的更详细演示，请参阅“汇总认知度”教程。