本页面适用于任何尝试使用 LookML 在 Looker 中构建探索的用户。如果您精通 SQL,则该页面会更容易理解,特别是在您了解内联接和外联接之间的区别之后。如需简要了解内联接和外部联接的差异,请参阅这篇关于 SQL 联接的 w3schools 文章。
Looker 可以作为贵公司的强大 SQL 引擎。借助 LookML 中的抽象建模,数据和 IT 团队可以构建始终适用的通用规则,从而释放业务分析师的自由,让他们可以自然而然地构建始终正确的查询,即使数据团队从未预测到此类查询的需求也是如此。此功能的核心驱动因素是对称聚合算法,它解决了整个行业的 SQL 联接问题。但是,必须正确完成两件事才能利用该算法:每个包含测量的视图(通常是所有视图)中的主键都必须准确无误,并且每个联接中的 relationship
参数都必须正确无误。
主键
从许多方面来说,了解表的主键与了解表是什么以及可以用它来做什么基本相同。只需注意,您选择作为主键的列(或串联的列)不得包含重复值。
relationship
参数
现在您已经验证了主键,接下来可以确定联接的 relationship
参数的正确值。relationship
参数的用途是告知 Looker 在联接写入到 SQL 查询时是否调用对称聚合。这里可能采用的方法是告知 Looker 始终调用它们,这样总是会产生准确的结果。但是,这样做会降低性能,因此最好谨慎使用对称聚合。
内联接和外联接的确定正确值的过程略有不同。
内联接
例如,假设您有一个订单表,其中主键为 order_id
:
order_id | amount | customer_id |
---|---|---|
1 | $25.00 | 1 |
2 | $50.00 | 1 |
3 | $75.00 | 2 |
4 | $35.00 | 3 |
假设您还有一个主键为 customer_id
的客户表:
customer_id | first_name | last_name | 访问次数 |
---|---|---|---|
1 | 阿米莉亚 | 耳朵 | 2 |
2 | 贝西 | 科尔曼 | 2 |
3 | 威尔伯 | Wright | 4 |
您可以通过 customer_id
字段联接这些表,该字段存在于两个表中。此联接在 LookML 中表示为如下形式:
explore: orders { join: customers { type: inner sql_on: ${orders.customer_id} = ${customers.customer_id} ;; relationship: many_to_one } }
此 LookML 联接的结果可表示为单个联接表,如下所示:
order_id | amount | customer_id | customer_id | first_name | last_name | 访问次数 |
---|---|---|---|---|---|---|
1 | $25.00 | 1 | 1 | 阿米莉亚 | 耳朵 | 2 |
2 | $50.00 | 1 | 1 | 阿米莉亚 | 耳朵 | 2 |
3 | $75.00 | 2 | 2 | 贝西 | 科尔曼 | 2 |
4 | $35.00 | 3 | 3 | 威尔伯 | Wright | 4 |
这里的 many_to_one
关系是指联接字段 (customer_id
) 的一个值在每个表中表示的次数。在 orders
表(左表)中,一个客户 ID 表示了多次(在本例中,这是指 ID 为 1
的客户,它显示在多行中)。
在 customers
表(右侧表)中,每个客户 ID 仅表示一次,因为 customer_id
是该表的主键。因此,orders
表中的记录可能会有多个与 customers
表中的单个值匹配的记录。如果 customer_id
在 customers
表的每一行中都不是唯一的,则关系将为 many_to_many
。
您可以通过检查主键来按照以下步骤以编程方式确定正确的关系值:
- 首先将
many_to_many
写为关系。只要您的主键正确无误,这就会始终生成准确的结果,因为 Looker 始终会触发对称聚合算法并强制执行准确性。不过,由于该算法会使查询复杂化并增加运行时间,因此最好尝试将其中一端或两端更改为one
而不是many
。 - 查看左表
sql_on
子句中的一个或多个字段。如果一个或多个字段构成左侧表的主键,您可以将relationship
参数的左侧更改为one
。否则,它通常必须保持many
。(有关特殊情况的信息,请参阅本页面后面的注意事项部分)。 - 接下来,查看
sql_on
子句中表示右侧表的一个或多个字段。如果一个或多个字段构成了正确表的主键,您可以将右侧字段更改为one
。
编写 sql_on
短语时,最好从左表(位于等号左侧)和右表(位于右侧)开始。sql_on
参数中条件的顺序无关紧要,除非顺序与数据库的 SQL 方言相关。尽管 sql_on
参数不要求您以这种方式对字段进行排序,但排列 sql_on
条件让等号的左侧和右侧与 relationship
参数的从左到右读取的方式一致也有助于您确定这种关系。以这种方式对字段排序,也有助于更轻松地一眼就能看出“探索”中的哪个现有表与新表相联接。
外部联接
对于外部联接,您还需要考虑到在联接期间添加 null 记录时可能会出现扇出的情况。这一点尤为重要,因为左外联接是 Looker 中的默认联接。虽然 null 记录不会影响总和或平均值,但会影响 Looker 衡量 type: count
的方式。如果操作不当,系统会对 null 记录进行计数(这种情况并不可取)。
在全外部联接中,如果任意一个表中的联接键缺少另一个表中的值,则可将 null 记录添加到另一个表中。如下例所示,该示例涉及一个 orders
表:
order_id | amount | customer_id |
---|---|---|
1 | $25.00 | 1 |
2 | $50.00 | 1 |
3 | $75.00 | 2 |
4 | $35.00 | 3 |
在本示例中,假设您还具有以下 customers
表:
customer_id | first_name | last_name | 访问次数 |
---|---|---|---|
1 | 阿米莉亚 | 耳朵 | 2 |
2 | 贝西 | 科尔曼 | 2 |
3 | 威尔伯 | Wright | 4 |
4 | Charles | 耶格尔 | 3 |
联接这些表后,联接的表可表示如下:
order_id | amount | customer_id | customer_id | first_name | last_name | 访问次数 |
---|---|---|---|---|---|---|
1 | $25.00 | 1 | 1 | 阿米莉亚 | 耳朵 | 2 |
2 | $50.00 | 1 | 1 | 阿米莉亚 | 耳朵 | 2 |
3 | $75.00 | 2 | 2 | 贝西 | 科尔曼 | 2 |
4 | $35.00 | 3 | 3 | 威尔伯 | Wright | 4 |
null | null | null | 4 | Charles | 耶格尔 | 3 |
就像在内联接中一样,表的主键之间的关系为 many_to_one
。但是,添加的 null 记录迫使左侧表也需要对称汇总。因此,您必须将 relationship
参数更改为 many_to_many
,因为执行此联接会中断左表的计数。
如果此示例属于左外联接,则不会添加 null 行,并丢弃额外的客户记录。在这种情况下,关系仍为 many_to_one
。这是 Looker 默认值,因为它假定基表定义了分析。在这种情况下,您分析的是订单,而不是客户。如果客户表格位于左侧,则情况会有所不同。
多级联接
在某些探索中,基表会联接到一个或多个视图,而这些视图又需要联接到一个或多个其他视图。在下面的示例中,这意味着表将联接到客户表。在这些情况下,在评估 relationship
参数时,最好只查看正在写入的单个联接。即使受影响的视图不在实际创建扇出的联接中,Looker 都会了解下游扇出会影响查询的情况。
Looker 对我有何帮助?
Looker 中有一些机制可帮助确保关系值正确无误。一种是检查主键的唯一性。每当需要扇出和对称聚合来计算测量值时,Looker 都会检查所使用的主键的唯一性。如果它不唯一,则查询运行时将出现错误(但是,不存在 LookML 验证器错误)。
此外,如果 Looker 无法处理扇出(通常是因为没有指示主键),那么该视图的“探索”中将不会显示任何测量。要更正此问题,只需指定一个字段作为主键,即可让您的测量结果进入“探索”部分。
注意事项
对称聚合支持方言
Looker 可与某些不支持对称聚合的方言连接。如需查看方言列表以及这些方言对对称聚合的支持,请参阅 symmetric_aggregates
文档页面。
特殊情况
本页面前面的 内联接部分指出,要确定正确的关系值,您应该查看左表 sql_on
子句中的一个或多个字段:“如果一个或多个字段构成了左表的主键,您可以将 relationship
参数的左侧更改为 one
。否则,其值通常必须保持为 many
。”除非表中包含多个没有重复记录的列。在这种情况下,您可以在构建关系时将任何此类列视为主键,即使该列不是指定的 primary_key: yes
列也是如此。
确保存在某种软件规则,可以确保指定列的上一段声明始终为 true,十分方便。如果是这样,请照做这样处理,并在视图文件中记下它的特殊属性,以供其他人将来参考(填写 SQL Runner 链接以证明这一点)。但请注意,当某个字段被指定为主键时,Looker 会确认隐式唯一性这一事实,但对于其他字段,系统不会这样做。它不会调用对称聚合算法。