メトリクスビューでの結合

メトリクスビューでの結合は、関連テーブルの属性を使用してソースデータを強化します。ファクトテーブルからディメンションテーブルへの直接結合（スタースキーマ）、正規化されたディメンションテーブルを跨ぐ多段階結合（スノーフレークスキーマ）、および関連テーブルから事実を集約する一対多結合をサポートしています。default では、すべての結合が多対一であるため、各ソース行は結合されたテーブルの最大1行と一致します。

スタースキーマ結合

スタースキーマでは、sourceはファクトテーブルであり、LEFT OUTER JOINを使用して1つ以上のディメンションテーブルと結合します。メトリクスビューは、選択したフィールドとメジャーに基づいて、特定のクエリーに必要なファクトテーブルとディメンションテーブルを結合します。

結合列は、on句 (Boolean式) または using句 (共有列名) のいずれかを使用して指定します。結合は多対一のリレーションシップに従う必要があります。多対多の場合、エンジンは結合されたディメンションテーブルから最初に一致する行を選択します。

次の例では、ordersファクトテーブルをcustomerディメンションテーブルに、Boolean式を受け入れるon句を使用して結合します。

YAML

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)

結合カラムが両方のテーブルで同じ名前を持つ場合、on 句の代わりに using 句を使用します。using 句は、ソースと結合されたテーブルの両方に存在するカラム名の配列を受け取ります。samples カタログに結合カラム名を共有するデータセットがないため、以下の例では、構文を説明するためにプレースホルダーのテーブル名とカラム名を使用しています。

YAML

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

注記

on 句では、source はメトリクスビューのソーステーブルを参照し、結合 name は結合されたテーブルの列を参照します。例えば、source.o_custkey = customer.c_custkey はソーステーブルの o_custkey 列を customer テーブルの c_custkey 列に結合します。プレフィックスが指定されていない場合、参照は結合されたテーブルにdefaultします。

Snowflakeスキーマの結合

スノーフレークスキーマは、ディメンションテーブルを正規化し、サブディメンションに接続することで、スタースキーマを拡張します。これにより、多階層結合構造が作成されます。

スノーフレークスキーマを定義するには:

1. メトリクスビューを作成します。
2. 第1レベル（スタースキーマ）の結合を追加します。
3. 他のディメンションテーブルと結合します。
4. ビューにフィールドを追加して、ネストされた属性を利用可能にします。

次の例では、TPC-Hデータセットを使用して、注文の地理的階層を示すスノーフレークスキーマについて説明します。この例では、ordersテーブルを顧客に、次にその国（国または地域）に、最後にその地域（大陸）に結合します。TPC-Hデータセットは、Databricksワークスペースのsamplesカタログで利用できます。

YAML

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フィールドは、ソーステーブルと結合テーブル間の関係を制御します。このフィールドは、結合テーブルの列を参照するメジャーをエンジンがどのように処理するかを決定します。

次の表は、サポートされている2つのカーディナリティを比較しています。

属性	many_to_one （デフォルト）	one_to_many
ソース行ごとに一致した行	最大1つ	0個以上
一般的な使用。	ディメンションルックアップ	ファクト展開
許可されている場所 fields	はい	No
許可されている場所 measures	はい	はい

属性	many_to_one （デフォルト）	one_to_many
ソース行ごとに一致した行	最大1つ	0個以上
一般的な使用。	ディメンションルックアップ	ファクト展開
許可されている場所 fields	はい	No
許可されている場所 measures	はい	はい

多対一結合

多対一がdefaultのカーディナリティです。ソース内の各行は、結合されたテーブル内で最大1行と一致するため、結合されたテーブルはディメンションルックアップとして機能します。cardinalityフィールドは多対一結合で省略できます。または、cardinality: many_to_oneを明示的に指定することもできます。

フィールドとメジャーの両方が、ドット表記を使用して多対一結合の列を参照できます（例：customer.c_name）。

結合制約の宣言 rely

rely.at_most_one_match: trueの設定は、結合が「一」側でファンアウトしないことを示しています：

• 多対一の結合では、各ソース行は結合されたテーブル内の最大1つの行に一致します。
• 一対多結合では、結合された各行は最大で1つのソース行に一致します。

この宣言により、エンジンは不要な結合をスキップし、スキャンされるデータを削減できます。特に、結合されたテーブルのフィールドでフィルター処理を行うクエリーの場合に有効です。Databricks は、制約が成立する場合に両方のカーディナリティに rely を設定することを推奨します。

警告

関係が本当に成立する場合にのみ、at_most_one_match: trueを設定してください。このプロパティはランタイム時に検証されません。アサートされた側がファンアウトを生成する場合、メジャーは誤った結果を返します。

以下の例では、relyを有効にしてordersをcustomerに結合します：

YAML

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)

完全なrelyフィールドリファレンスについては、「relyを使用した結合の最適化」を参照してください。

一対多の結合

結合テーブル内で単一のソース行が複数の行に一致するように、cardinality: one_to_manyを設定します。これにより、そのテーブルは、エンジンがソース粒度で個別に集計するファクトソースになります。

注記

多対1の結合には、Databricks Runtime 18.1以降とYAML仕様バージョン1.1が必要です。「メトリクス ビュー機能の可用性」を参照してください。

一対多結合により、単一のメトリクスビューで、顧客ごとの注文やアカウントごとのイベントなど、異なる粒度で存在するファクトを、クエリー結果のソース行を重複させることなく測定できます。ソースはディメンションの骨格として機能します。結合されたテーブルに一致する行がいくつ存在するかに関係なく、各エンティティは正確に1回表示されます。

一対多結合の例

次の例では、customerをソースとして使用し、ordersとcardinality: one_to_manyを結合します。nationへのmany_to_one結合は、nation_nameフィールドを提供します。各結合条件のソース側をsource.で修飾し、参照がメトリクスビューのソーステーブルに解決されるようにします。両方の結合はrely.at_most_one_match: trueを設定します。nation結合では、各顧客が最大1つの国を持つことをアサートし、orders結合では、各注文が最大1つの顧客に属することをアサートします。「relyを使用して結合制約を宣言する」を参照してください。

YAML

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 Branchの行を集計します。注文が2件の顧客はorder_countが2を返し、customer_countが1のままになるため、ソース行が重複していないことが確認されます。注文がない顧客も、order_countが0、NULL total_order_revenueの結果に表示されます。

ネストされた一対多の結合

ソースから2つ以上のレベル下にあるファクトを測定するには、一対多の結合をネストします。一対多のサブツリー内のすべての結合は同じカーディナリティを共有する必要があるため、一対多の親は多対一の子を持つことはできません。ネストされた結合内の列を、結合名を介した完全なドットパスで参照します。

次の例では、ordersの下にlineitemをネストして、単一の顧客粒度ビューが注文と明細項目の両方をカウントできるようにします。

YAML

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)

メジャーは、lineitemがordersを介してのみ到達可能であるため、結合名を通る完全なドットパスでネストされた列を参照します（例：orders.lineitem.l_extendedprice）。注文数には、単純なcountではなくcount(distinct orders.o_orderkey)を使用します。各注文が複数の明細項目に展開されるため、単純なカウントでは1つの注文が明細項目ごとに1回カウントされます。

兄弟の一対多結合

単一のビューから独立したファクトソースを測定するために、複数の1対多結合を同じレベルで定義します。エンジンは兄弟結合を個別に集計し、それらをブレンドするため、行が相互に掛け合わされることはありません。トップレベルの兄弟はカーディナリティを自由に混在させることができるため、many_to_oneディメンション結合とone_to_manyファクト結合が同じレベルで共存できます。

次の例では、nationをソースとして使用し、2つの独立した一対多のBranch、customerとsupplierを追加します:

YAML

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メジャーは、エンジンがそれぞれをクエリー粒度に合わせてブレンドした後、2つの独立した集計を分割します。算術演算を使用して異なるソースからのメジャーを結合できますが、単一の集計関数は1つのソースからの列のみを参照する必要があります。

複数のファクトテーブルとブリッジテーブルの接続

メトリクスビューは、ディメンションテーブルに結合された単一のファクトテーブルをモデル化します。粒度が異なる 2 つ以上のファクトテーブルからメジャーを結合するには、ファクトが共有するディメンションの有効な組み合わせを列挙するブリッジを、メトリクスビューの source 内で直接定義します。たとえば、samples.tpch 出荷ファクト lineitem（粒度：注文明細）と供給ファクト partsupp（粒度：部品とサプライヤー）は、いずれも部品ディメンションとサプライヤーディメンションを共有しています。

ブリッジにより有効なディメンションの組み合わせのセットが明示的になるため、クエリー結果の予測可能性が維持されます。メトリクスビューは、クエリーごとに推論するのではなく、有効と宣言した組み合わせのみを返します。各ファクト結合でcardinality: one_to_manyを設定して、エンジンがファンアウトや重複カウントなしで、共有ブリッジに対して各ファクトを独立して集計するようにします。

ブリッジを構築するには、メトリクスビューsourceでSQLクエリーとして定義し、各ファクトテーブルを共有列で結合し、共有ディメンション列にフィールドを宣言し、各ファクトにメジャーを宣言します。共有ディメンションのすべての組み合わせが有効な場合にCROSS JOINを使用します。

YAML

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」）にスワップして、各ファクトがそのメンバーを提供できるようにします。joins、fields、およびmeasuresは変更されません。

YAML

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

一対多結合の制限

• フィールドは一対多結合を参照できません : フィールドはソース行ごとに正確に1つの値に解決される必要があります。一対多の列はソース行ごとに複数の値を持つことができるため、fields定義で使用することはできません。そのような列をフィールドとして使用するには、そのテーブルをソースとして指定し、元のソースをmany_to_one結合として結合します。
• 単一の集計で複数のソースにまたがることはできません : 各集計関数は、1つのソースからの列を参照する必要があります。2つの集計結果間の算術演算（count(orders.o_orderkey) / count(*) など）は許可されますが、単一の関数で2つのソースからの列を組み合わせることはできません。
• 結合サブツリーはカーディナリティを混在できません ：一対多結合のすべての子孫も一対多である必要があり、多対一結合のすべての子孫は多対一である必要があります。トップレベルの兄弟要素のみがカーディナリティを混在させることができます。

追加のリソース

• メトリクスビューのモデリング
• メトリクスビューの高度なテクニック
• メトリクスビューYAML 構文リファレンス
• チュートリアル：結合とデータモデリングを使用してメトリクスビューを構築する