在 LookML 中使用联接

借助联接,您可以关联不同的视图,以便同时探索多个视图中的数据,并了解数据的不同部分之间的相互关系。

例如,您的数据库可能包含表 order_itemsordersusers。您可以使用联接同时探索所有表中的数据。本页面介绍了 LookML 中的联接,包括特定的联接参数和联接模式。

用户加入时先进行探索

联接在模型文件中定义,用于建立探索视图之间的关系。联接可将一个或多个视图直接或通过其他联接的视图连接到单个“探索”。

请考虑两个数据库表:order_itemsorders。为这两个表生成视图后,请在模型文件的 explore 参数下声明其中一个或多个表:

explore: order_items { ... }

当您从 order_items 探索运行查询时,order_items 会显示在生成的 SQL 的 FROM 子句中:

SELECT ...
FROM order_items

您可以将更多信息加入 order_items 的“探索”专区。例如,您可以使用以下示例 LookML 将 orders 视图联接到 order_items 探索:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
}

之前显示的 LookML 会完成两项工作。首先,您可以在探索字段选择器中查看 ordersorder_items 中的字段:

“订单商品探索”包含“订单商品”视图中的字段以及来自合并的订单视图的字段。

其次,LookML 介绍了如何将 ordersorder_items 联接在一起。该 LookML 会转换为以下 SQL:

SELECT ...
FROM order_items
LEFT JOIN orders
ON order_items.order_id = orders.id

以下各部分将更详细地介绍这些 LookML 参数。

联接参数

有四个主要参数用于联接:jointyperelationshipsql_on

第 1 步:开始探索

首先,创建 order_items 探索:

explore: order_items { ... }

第 2 步:join

如需联接表,必须先在视图中声明表。在此示例中,假设 orders 是模型中现有的视图。

然后,使用 join 参数声明您想将 orders 视图联接到 order_items“探索”:

explore: order_items {
  join: orders { ... }
}

第 3 步:type

考虑要执行哪种类型的联接。Looker 支持 LEFT JOININNER JOINFULL OUTER JOINCROSS JOIN。这些变量对应于 left_outerinnerfull_outercrosstype 参数值。

explore: order_items {
  join: orders {
    type: left_outer
  }
}

type 的默认值为 left_outer

第 4 步:relationship

定义 order_items 探索和 orders 视图之间的联接关系。正确声明联接的关系对于 Looker 计算准确的测量值非常重要。关系是从 order_items 的“Explore” orders 视图定义的。可能的选项包括 one_to_onemany_to_oneone_to_manymany_to_many

在此示例中,一个订单可以有多个订单商品。order_items“探索”与 orders 视图之间的关系为 many_to_one

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
  }
}

如果您没有在联接中添加 relationship 参数,则 Looker 默认为 many_to_one

如需有关正确为联接定义 relationship 参数的更多提示,请参阅正确获取 relationship 参数

第 5 步:sql_on

声明如何将 order_items 表和 orders 表与 sql_on 参数或 foreign_key 参数联接起来。

sql_on 参数等效于查询生成的 SQL 中的 ON 子句。借助此参数,您可以声明应匹配哪些字段以执行联接:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
}

您还可以编写更复杂的联接。例如,您可能希望仅联接 id 大于 1000 的订单:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} AND ${orders.id} > 1000 ;;
  }
}

如需详细了解这些示例中的 ${ ... } 语法,请参阅替换运算符文档

第 6 步:测试

前往订单项“探索”部分,测试此联接是否按预期运行。您应该会看到 order_itemsorders 中的字段。

如需详细了解如何在“探索”中测试 LookML 更改,请参阅测试“探索”中的字段

通过其他视图加入

您可以通过另一个视图将一个视图联接到“探索”。在联接参数示例中,您通过 order_id 字段将 orders 联接到了 order_items。我们可能还希望将名为 users 的视图中的数据联接到 order_items“探索”部分,即使它们没有共享公共字段也是如此。这可以通过通过 orders 视图进行联接来实现。

使用 sql_on 参数或 foreign_key 参数将 users 视图与 orders 视图(而不是 order_items 探索)联接起来。为此,您可以将 orders 中的字段正确限定为 orders.user_id

下面是一个使用 sql_on 参数的示例:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
  join: users {
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.user_id} = ${users.id} ;;
  }
}

多次加入观看

users 数据视图包含买方卖方的数据。要将此数据视图中的数据联接到 order_items 中,但针对买方和卖方分别进行此操作,您可以使用 from 参数联接两次不同名称的 users

借助 from 参数,您可以指定要在联接中使用的视图,并为联接指定一个唯一名称。例如:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
  }
  join: buyers {
    from: users
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.buyer_id} = ${buyers.id} ;;
  }
  join: sellers {
    from: users
    type: left_outer
    relationship: many_to_one
    sql_on: ${orders.seller_id} = ${sellers.id} ;;
  }
}

在本例中,只有买方数据会作为 buyers 进行联接,而只有卖方数据会作为 sellers 进行联接。

注意users 视图现在必须通过联接中的别名 buyerssellers 来引用。

限制联接中的字段

借助 fields 参数,您可以指定从联接传入探索中的字段。默认情况下,视图中的所有字段会在联接时被引入。不过,您可能只想导入部分字段。

例如,将 orders 联接到 order_items 时,您可能希望仅通过联接带来 shippingtax 字段:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
    fields: [shipping, tax]
  }
}

您还可以引用一组字段,例如 [set_a*]每个集合都是在视图中使用 set 参数定义的。假设您在 orders 视图中定义了以下集:

set: orders_set {
  fields: [created_date, shipping, tax]
}

orders 联接到 order_items 时,您可以选择仅导入这三个字段:

explore: order_items {
  join: orders {
    type: left_outer
    relationship: many_to_one
    sql_on: ${order_items.order_id} = ${orders.id} ;;
    fields: [orders_set*]
  }
}

对称汇总

Looker 使用一种名为“对称聚合”的功能正确计算聚合(例如求和与平均值),即使联接导致扇出也是如此。如需详细了解对称汇总,请参阅了解对称汇总。对称聚合所解决的扇出问题在 SQL 扇出问题社区帖子中进行了解释。

必须提供主键

如需通过联接获取测量结果(汇总),您必须在涉及联接的所有视图中定义主键。

为此,请将 primary_key 参数添加到每个数据视图的主键字段定义中:

dimension: id {
  type: number
  primary_key: yes
}

支持的 SQL 语言

为了让 Looker 支持 Looker 项目中的对称汇总,您的数据库方言也必须支持对称汇总。下表显示了哪些方言在最新版本的 Looker 中支持对称聚合:

方言 是否支持?
Actian Avalanche
Amazon Athena
Amazon Aurora MySQL
Amazon Redshift
Apache Druid
Apache Druid 0.13+
Apache Druid 0.18 及更高版本
Apache Hive 2.3 及更高版本
Apache Hive 3.1.2 及更高版本
Apache Spark 3 及更高版本
ClickHouse
Cloudera Impala 3.1 及更高版本
带有原生驱动程序的 Cloudera Impala 3.1+
带有原生驱动程序的 Cloudera Impala
DataVirtuality
Databricks
Denodo 7 购物中心
Denodo 8
Dremio
Dremio 11+
Exasol
Firebolt
Google BigQuery 旧版 SQL
Google BigQuery 标准 SQL
Google Cloud PostgreSQL
Google Cloud SQL
Google Spanner
Greenplum
HyperSQL
IBM Netezza
MariaDB
Microsoft Azure PostgreSQL
Microsoft Azure SQL 数据库
Microsoft Azure Synapse Analytics
Microsoft SQL Server 2008 及更高版本
Microsoft SQL Server 2012 及更高版本
Microsoft SQL Server 2016
Microsoft SQL Server 2017 及更高版本
MongoBI
MySQL
MySQL 8.0.12 及更高版本
Oracle
Oracle ADWC
PostgreSQL 9.5 及更高版本
9.5 之前的 PostgreSQL
PrestoDB
PrestoSQL
SAP HANA 2 及更高版本
SingleStore
单一商店 7+
Snowflake
TeraData
Trino
矢量
Vertica

如果您的方言不支持对称聚合,请在 Looker 中执行联接时小心,因为某些类型的联接可能会导致汇总不准确(例如求和与平均值)。SQL 扇出问题社区帖子中详细介绍了此问题及其解决方法。

详细了解联接

如需详细了解 LookML 中的联接参数,请参阅联接参考文档