指标视图中的联接

本页介绍如何在指标视图中使用连接，利用相关表中的属性来丰富源数据。

指标视图中的联接支持从事实表直接联接到维度表（星型架构）、跨规范化维度表的多跳联接（雪花架构），以及可从相关表聚合事实数据的一对多联接。 默认情况下，所有联接都是多对一的，这意味着每个源行最多匹配联接表中的一行。

星型架构联接

在星型架构中，source是事实数据表，并使用LEFT OUTER JOIN与一个或多个维度表连接。 指标视图根据所选字段和度量值联接特定查询所需的事实表和维度表。

使用 on 子句（布尔表达式）或 using 子句（共享列名）指定联接列。 联接应遵循多对一关系。 在多对多关系的情况下，从联接维度表中选择第一个匹配的行。

以下示例使用 orders 子句将 customer 事实表联接到 on 维度表，该子句接受一个布尔表达式：

version: 1.1
source: samples.tpch.orders

joins:
  # The on clause supports a Boolean expression
  - name: customer
    source: samples.tpch.customer
    on: source.o_custkey = customer.c_custkey

fields:
  # Field referencing a join column using dot notation
  - name: Customer name
    expr: customer.c_name
  - name: Customer market segment
    expr: customer.c_mktsegment

measures:
  # Measure referencing a join column
  - name: Total revenue
    expr: SUM(o_totalprice)
  - name: Order count
    expr: COUNT(1)

当联接列在两个表中名称相同时，请使用 using 子句，而不要使用 on 子句。 using 子句接受一个数组，其中包含同时存在于源表和联接表中的列名。 在 samples 目录中，没有任何数据集包含共享同一联接列名称的表，因此以下示例使用占位符表名和列名来演示语法：

joins:
  - name: customer
    source: catalog.schema.customer
    using:
      - customer_id

Snowflake 架构联接

雪花架构通过标准化维度表并将其连接到子维度来扩展星型架构。 这会创建多级联接结构。

定义雪花架构：

1. 创建指标视图。
2. 添加第一级（星型架构）联接。
3. 与其他维度表联接。
4. 通过在视图中添加字段来公开嵌套属性。

以下示例使用 TPC-H 数据集来说明显示订单的地理层次结构的雪花架构。 该示例先将订单表联接到客户表，然后联接到客户所在的国家或地区，最后联接到这些国家或地区所属的区域（洲）。 TPC-H 数据集在Azure Databricks工作区的 samples 目录中可用。

source: samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    on: source.o_custkey = customer.c_custkey
    joins:
      - name: nation
        source: samples.tpch.nation
        on: customer.c_nationkey = nation.n_nationkey
        joins:
          - name: region
            source: samples.tpch.region
            on: nation.n_regionkey = region.r_regionkey

fields:
  - name: clerk
    expr: o_clerk
  - name: customer
    expr: customer
    comment: returns the full customer row as a struct
  - name: customer_name
    expr: customer.c_name
  - name: nation
    expr: customer.nation
  - name: nation_name
    expr: customer.nation.n_name

联接基数

联接 cardinality 上的字段控制源表与联接表之间的关系。 此字段确定引擎如何处理引用联接表中的列的度量值。

下表比较了两个支持的基数：

多对一连接

多对一关系是默认基数。 源中的每个行最多匹配联接表中的一行，因此联接表充当维度查找。 对于多对一连接，可以省略 cardinality 字段，或显式指定 cardinality: many_to_one。

字段和度量值都可以通过点表示法（例如，customer.c_name）引用多对一联接中的列。

使用 rely 声明联接约束

设置 rely.at_most_one_match: true 表示该联接在“一”侧没有扇出：

• 在多对一联接中，每个源行最多与被联接表中的一行匹配。
• 在一对多联接上，每个联接行最多匹配一个源行。

此声明允许引擎跳过不必要的联接并减少扫描的数据，尤其是对于筛选联接表中字段的查询。 Databricks 建议在该约束成立时，对两个基数都设置 rely。

以下示例在启用orders的情况下将customer连接到rely：

version: 1.1
source: samples.tpch.orders

joins:
  - name: customer
    source: samples.tpch.customer
    on: source.o_custkey = customer.c_custkey
    rely:
      at_most_one_match: true

fields:
  - name: Customer name
    expr: customer.c_name
  - name: Customer market segment
    expr: customer.c_mktsegment

measures:
  - name: Total revenue
    expr: SUM(o_totalprice)
  - name: Order count
    expr: COUNT(1)

请参阅Optimize joins with rely，获取完整的rely字段参考信息。

一对多联接

设置为 cardinality: one_to_many 允许单个源行匹配联接表中的多个行。 这会将该表转换为一个事实来源，可供引擎以源粒度独立进行聚合。

一对多连接使单个指标视图能够度量处于不同粒度的事实数据，例如每位客户的订单数或每个账户的事件数，而无需在查询结果中重复源行。 源充当维度主干：无论连接表中存在多少个匹配行，每个实体都只会出现一次。

一对多联接示例

以下示例以customer为源，并将orders与cardinality: one_to_many联接起来。 与many_to_one的nation联接提供nation_name字段。 使用 source. 限定每个联接条件的源侧，以便该引用解析到指标视图的源表。 两个联接都设置为 rely.at_most_one_match: true：在加入时 nation ，它断言每个客户最多有一个国家，在加入时 orders ，它断言每个订单最多属于一个客户。 请参阅使用rely声明联接约束。

version: 1.1
source: samples.tpch.customer

joins:
  - name: nation
    source: samples.tpch.nation
    on: nation.n_nationkey = source.c_nationkey
    rely:
      at_most_one_match: true
  - name: orders
    source: samples.tpch.orders
    on: orders.o_custkey = source.c_custkey
    cardinality: one_to_many
    rely:
      at_most_one_match: true

fields:
  - name: customer_name
    expr: c_name
  - name: nation_name
    expr: nation.n_name

measures:
  - name: customer_count
    expr: count(*)
  - name: order_count
    expr: count(orders.o_orderkey)
  - name: total_order_revenue
    expr: sum(orders.o_totalprice)

在此视图中， customer_count 对源 customer 表中的行进行计数，同时 order_count 对 total_order_revenue 分支中的 orders 行进行聚合。 一个拥有两个订单的客户返回的 order_count 值为 2，而 customer_count 仍为 1，这确认了源行没有被重复。 没有订单的客户仍会显示在结果中，其 order_count 为 0，并带有 NULLtotal_order_revenue。

嵌套一对多连接

若要度量位于源下方两层或更多层级的事实，请嵌套一对多联接。 一对多子树中的所有联接都必须具有相同的基数关系，因此，一对多父节点不能有多对一的子节点。 通过联接名称引用嵌套联接中的列及其完整点路径。

以下示例嵌套 lineitem 在下方 orders ，以便单个客户粒度视图可以同时对订单和行项进行计数：

version: 1.1
source: samples.tpch.customer

joins:
  - name: orders
    source: samples.tpch.orders
    on: orders.o_custkey = source.c_custkey
    cardinality: one_to_many
    joins:
      - name: lineitem
        source: samples.tpch.lineitem
        on: lineitem.l_orderkey = orders.o_orderkey
        cardinality: one_to_many

fields:
  - name: customer_name
    expr: c_name

measures:
  - name: order_count
    expr: count(distinct orders.o_orderkey)
  - name: line_item_count
    expr: count(orders.lineitem.l_linenumber)
  - name: total_line_revenue
    expr: sum(orders.lineitem.l_extendedprice)

度量值通过联接名称引用嵌套列及其完整点路径，例如 orders.lineitem.l_extendedprice，因为 lineitem 只能通过 orders 来访问。 对订单数量使用 count(distinct orders.o_orderkey)，而不是普通的 count：每个订单都会展开为多个订单行项目，因此普通计数会按每个订单行项目将同一订单计算一次。

同级一对多联接

在同一级别定义多个一对多联接，以在单一视图中衡量彼此独立的事实来源。 同级联接会先分别聚合，再进行混合，因此其行数绝不会交叉相乘。 顶层同级项可以自由混合不同基数，因此，many_to_one 维度联接和 one_to_many 事实联接可以在同一级别并存。

以下示例以 nation 为源，并添加了两个相互独立的一对多分支，即 customer 和 supplier：

version: 1.1
source: samples.tpch.nation

joins:
  - name: customer
    source: samples.tpch.customer
    on: customer.c_nationkey = source.n_nationkey
    cardinality: one_to_many
  - name: supplier
    source: samples.tpch.supplier
    on: supplier.s_nationkey = source.n_nationkey
    cardinality: one_to_many

fields:
  - name: nation_name
    expr: n_name

measures:
  - name: customer_count
    expr: count(customer.c_custkey)
  - name: supplier_count
    expr: count(supplier.s_suppkey)
  - name: customers_per_supplier
    expr: count(customer.c_custkey) / count(supplier.s_suppkey)

在引擎将每个聚合混合到查询粒度后，customers_per_supplier 度量值会将两个独立聚合相除。 可以将来自不同源的度量值与算术相结合，但单个聚合函数必须仅引用来自一个源的列。

将多个事实表与桥接表连接

指标视图对一个与维度表联接的单个事实表进行建模。 若要合并两个或更多个处于不同粒度的事实表中的度量值，请直接在指标视图的 source 中定义一个桥接表，用于枚举这些事实表共享维度的有效组合。 例如， samples.tpch 发货事实 lineitem （谷物：订单线）和供应事实 partsupp （谷物：部件和供应商）共享部件和供应商维度。

桥使有效维度组合集显式，因此查询结果保持可预测性。 指标视图仅返回声明有效组合，而不是为每个查询推断它们。 在每个事实联接上设置 cardinality: one_to_many ，以便引擎针对共享桥独立聚合每个事实，而无需扇出和双计数。

若要构建桥接表，请在指标视图 source 中将其定义为 SQL 查询，基于共享列将每个事实表与其联接，然后在共享维度列上声明字段，并在各事实表上声明度量。 当共享维度的所有组合都有效时，请使用 CROSS JOIN：

version: 1.1
source: SELECT * FROM samples.tpch.part CROSS JOIN samples.tpch.supplier
filter: s_suppkey IN (11315, 42920) AND p_partkey IN (30419, 80418)

joins:
  - name: lineitem
    source: samples.tpch.lineitem
    on: source.p_partkey = lineitem.l_partkey AND source.s_suppkey = lineitem.l_suppkey
    cardinality: one_to_many
  - name: partsupp
    source: samples.tpch.partsupp
    on: source.p_partkey = partsupp.ps_partkey AND source.s_suppkey = partsupp.ps_suppkey
    cardinality: one_to_many

fields:
  - name: part_name
    expr: p_name
  - name: part_brand
    expr: p_brand
  - name: part_type
    expr: p_type
  - name: part_size
    expr: p_size
  - name: manufacturer
    expr: p_mfgr
  - name: supplier_name
    expr: s_name

measures:
  - name: lineitem_count
    expr: COUNT(lineitem.*)
  - name: total_quantity_sold
    expr: SUM(lineitem.l_quantity)
  - name: gross_revenue
    expr: SUM(lineitem.l_extendedprice)
  - name: net_revenue
    expr: SUM(lineitem.l_extendedprice * (1 - lineitem.l_discount))
  - name: distinct_orders
    expr: COUNT(DISTINCT lineitem.l_orderkey)
  - name: available_quantity
    expr: SUM(partsupp.ps_availqty)
  - name: avg_supply_cost
    expr: AVG(partsupp.ps_supplycost)
  - name: total_supply_value
    expr: SUM(partsupp.ps_availqty * partsupp.ps_supplycost)

事实数据表上的度量值仅计算其共享维度值出现在网桥中的记录。 桥不包含的组合不会对结果造成影响。

如果你只想要实际出现的组合，就将 source 替换为每个事实中的不同配对的 UNION（或 FULL OUTER JOIN），这样每个事实都会贡献其唯一成员。 ，joinsfields并measures保持不变：

source: |
  SELECT DISTINCT l_partkey AS p_partkey, l_suppkey AS s_suppkey FROM samples.tpch.lineitem
  UNION
  SELECT DISTINCT ps_partkey AS p_partkey, ps_suppkey AS s_suppkey FROM samples.tpch.partsupp

一对多联接限制

• 字段不能引用一对多联接：一个字段对于每个源行都必须恰好对应一个值。 由于一对多列每个源行可以有多个值，因此不能在 fields 定义中使用它。 要将此类列用作字段，请将表设为源，并改为将原始来源联接为 many_to_one 联接。
• 单个聚合不能跨越源：每个聚合函数必须引用一个源中的列。 允许在两个聚合的结果之间进行算术，例如 count(orders.o_orderkey) / count(*)，但单个函数不能合并来自两个源的列。
• 联接子树不能混合基数：一对多联接的所有后代也必须是一对多，而多对一联接的所有后代必须是多对一。 只有顶级兄弟姐妹才能混合基数。

其他资源

• 模型指标视图
• 指标视图的高级技术
• 指标视图 YAML 语法参考
• 教程：使用联接生成完整的指标视图