チュートリアル: 結合を使用して完全なメトリック ビューを作成する

このチュートリアルでは、TPC-H データセットを使用して包括的な売上分析メトリック ビューを構築する手順について説明します。 最終的には、次のメトリック ビューが表示されます。

  • スノーフレーク スキーマを使用して複数のテーブル間で注文と顧客を結合する
  • 時間属性、geography 属性、および order 属性のディメンションを定義します
  • フィルター処理された集計、比率、ウィンドウ関数などを含む単純および複雑なメジャーを計算します。
  • コンポーザビリティを使用して、より単純な指標から複雑なメトリクスを構築する
  • ダッシュボードと AI ツールの エージェント メタデータ が含まれています

メトリック ビューを初めて使用する場合は、「メトリック ビューの 作成と編集 」から始めて、基本を学習します。 このチュートリアルでは、その基盤を実際の複雑さに拡張します。

必要条件

このチュートリアルを完了するには、次のものが必要です。

  • ワークスペースは、Unity Catalog に対して有効にする必要があります。
  • Databricks Runtime 17.3 以降を実行している SQL ウェアハウスまたはコンピューティング リソース。

データ モデル

TPC-H データセットは、卸売サプライ チェーンをモデル化します。 このチュートリアルでは、スノーフレーク スキーマで結合された 3 つのテーブルを使用します。

  • orders customerでo_custkey = c_custkeyに参加する
  • customer nationでc_nationkey = n_nationkeyに参加する
テーブル 役割 キー列
orders 注文処理のファクトテーブル o_orderkey、o_custkey、o_totalprice、o_orderdate、o_orderstatus
customer ディメンションテーブル (顧客の詳細) c_custkey、c_name、c_mktsegment、c_nationkey
nation ディメンション テーブル (国または地域の参照) n_nationkey、n_name、n_regionkey

手順 1: YAML エディターを開く

このチュートリアルでは、YAML エディターを使用してメトリック ビュー定義を作成します。 その他の方法については、「 メトリック ビューの作成と編集」を参照してください。

YAML エディターを開くには:

  1. [データ] アイコンをクリックします。ワークスペースサイドバーのカタログ。
  2. 検索バーを使用して samples.tpch.ordersを検索します。
  3. テーブル名をクリックすると、テーブルの詳細が表示されます。
  4. [ 作成>メトリック ビュー] をクリックします。 [ メトリック ビューの作成 ] ダイアログで、名前を入力し、カタログとスキーマの変換先を選択します。 [作成] をクリックします。
  5. 必要に応じて、[ YAML ] をクリックして YAML エディターを開きます。

手順 2: メトリック ビューを設定する

バージョンと説明コメントから始めます。

version: 1.1

comment: |-
  Sales analytics metric view for order performance analysis.
  Joins orders with customers and geography.
  Owner: Analytics Team
  Last updated: 2025-01-15

この例では、バージョンを設定し、説明を追加します。

  • version: 1.1 は、YAML 仕様のバージョンを決定します。
  • comment は、カタログ エクスプローラーに表示されるメトリック ビューの目的を文書化します。

手順 3: ソースと結合を定義する

主要なソーステーブルを定義し、関連するテーブルを結合します。 nation結合はcustomer結合にネストされており、国家が顧客のサブ次元であるスノーフレークスキーマを反映しています。

source: SELECT * FROM samples.tpch.orders

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

filter: o_orderdate >= '1995-01-01'

この例では、ソース テーブル、結合、フィルターを定義します。

  • source は、ファクト テーブル (注文) をグレインとして設定します。
  • joins は、多対一のリレーションシップを使用して顧客データを取り込む。
  • 入れ子になった nation 結合は、 customer を介して結合して地理的なデータに到達するスノーフレーク スキーマ パターンを示しています。
  • filter は最近のデータに制限されます。これは、このメトリック ビューのすべてのクエリに適用されます。

手順 4: ディメンションを定義する

ディメンションは、ユーザーがグループ化してフィルター処理する属性です。

dimensions:
  - name: order_date
    expr: o_orderdate

  - name: order_month
    expr: "DATE_TRUNC('MONTH', o_orderdate)"

  - name: order_year
    expr: YEAR(o_orderdate)

  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END

  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"

  - name: customer_name
    expr: customer.c_name

  - name: market_segment
    expr: customer.c_mktsegment

  - name: customer_nation
    expr: customer.nation.n_name

この例では、ディメンション パターンを示します。

  • 複数の粒度 (日付、月、年) の時間ディメンションでは、さまざまな分析ニーズがサポートされます。
  • CASE 式は、暗号コードをビジネスに優しいラベルに変換します。
  • 結合列は、 joins で定義されたエイリアス ( customer.c_name など) を使用してテーブルを参照します。
  • 入れ子になった結合列では、スノーフレーク スキーマを走査するために、連結されたドット表記 ( customer.nation.n_name など) が使用されます。

手順 5: 指標を定義する

尺度とは、ユーザーが分析したい計算のことです。 最初にアトミック メジャーを定義してから、コンポーザビリティを使用して、 MEASURE() 関数で以前に定義されたメジャーを参照する複雑なメトリックを構築します。

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)

  - name: total_revenue
    expr: SUM(o_totalprice)

  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)

  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)

  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)

  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')

  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')

  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day

この例では、測定パターンを示します。

  • アトミック メジャー (order_count、 total_revenue、 unique_customers) は、構成要素を形成する単純な集計です。
  • 構成されたメジャー (avg_order_value、 revenue_per_customer) は、集計ロジックを複製するのではなく、 MEASURE() を使用して以前に定義されたメジャーを参照します。 total_revenue変更された場合、これらのメジャーは更新された定義を自動的に使用します。 コンポーザビリティを参照してください。
  • FILTER 句は、個別のディメンションを持たない条件付きメトリックを作成します。
  • t7d_customers ウィンドウ計測では、一意の顧客の移動 7 日間カウントを計算し，時間の経過に伴うエンゲージメントの傾向を追跡するのに役立ちます。 ウィンドウ メジャー パターンの詳細については、ウィンドウ メジャー を参照してください。

手順 6: エージェント メタデータを追加する

エージェント メタデータは、表示名、形式仕様、シノニムを提供することで、データの視覚化を強化し、LLM の精度を向上させます。 これらのプロパティをディメンションとメジャーに追加して、ビジネスのコンテキストを指標に付加するために使用します。

dimensions:
  - name: order_date
    expr: o_orderdate
    display_name: Order Date
  - name: order_month
    expr: "DATE_TRUNC('MONTH', o_orderdate)"
    display_name: Order Month
  - name: order_year
    expr: YEAR(o_orderdate)
    display_name: Order Year
  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END
    display_name: Order Status
    synonyms:
      - status
      - fulfillment status
  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"
    display_name: Priority
  - name: customer_name
    expr: customer.c_name
    display_name: Customer Name
  - name: market_segment
    expr: customer.c_mktsegment
    display_name: Market Segment
    synonyms:
      - segment
      - industry
  - name: customer_nation
    expr: customer.nation.n_name
    display_name: Country
    synonyms:
      - nation
      - country

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)
    display_name: Order Count
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: total_revenue
    expr: SUM(o_totalprice)
    display_name: Total Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - revenue
      - sales
  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: Unique Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)
    display_name: Avg Order Value
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - AOV
  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)
    display_name: Revenue per Customer
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: Open Order Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - backlog
  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')
    display_name: Fulfilled Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day
    display_name: 7-Day Rolling Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0

この例では、次のエージェント メタデータを追加します。

  • display_name には、技術的な列名の代わりに視覚化ツールに表示される人間が判読できるラベルが用意されています。
  • format は、ダッシュボードでの値の表示方法 (通貨、数値、パーセンテージ) を定義します。
  • synonyms は、Genie などの AI ツールが自然言語クエリを通じてディメンションとメジャーを検出するのに役立ちます。

エージェントメタデータオプションの詳細については、 メトリックビューのエージェントメタデータを参照してください。

手順 7: YAML 定義を完了する

メトリック ビューの完全な定義を次に示します。

version: 1.1

source: SELECT * FROM samples.tpch.orders

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

filter: o_orderdate >= '1995-01-01'

comment: |-
  Sales analytics metric view for order performance analysis.
  Joins orders with customers and geography.
  Owner: Analytics Team
  Last updated: 2025-01-15

dimensions:
  - name: order_date
    expr: o_orderdate
    display_name: Order Date
  - name: order_month
    expr: "DATE_TRUNC('MONTH', o_orderdate)"
    display_name: Order Month
  - name: order_year
    expr: YEAR(o_orderdate)
    display_name: Order Year
  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END
    display_name: Order Status
    synonyms:
      - status
      - fulfillment status
  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"
    display_name: Priority
  - name: customer_name
    expr: customer.c_name
    display_name: Customer Name
  - name: market_segment
    expr: customer.c_mktsegment
    display_name: Market Segment
    synonyms:
      - segment
      - industry
  - name: customer_nation
    expr: customer.nation.n_name
    display_name: Country
    synonyms:
      - nation
      - country

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)
    display_name: Order Count
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: total_revenue
    expr: SUM(o_totalprice)
    display_name: Total Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - revenue
      - sales
  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: Unique Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)
    display_name: Avg Order Value
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - AOV
  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)
    display_name: Revenue per Customer
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: Open Order Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - backlog
  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')
    display_name: Fulfilled Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day
    display_name: 7-Day Rolling Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
SQL を使用してメトリック ビューを作成する

カタログ エクスプローラーの外部でこの定義を作成する場合は、次の SQL を実行してメトリック ビューを作成します。 上記の完全な YAML を $$ 区切り記号の間に貼り付けます。

CREATE OR REPLACE VIEW catalog.schema.tpch_sales_analytics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1

source: SELECT * FROM samples.tpch.orders

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

filter: o_orderdate >= '1995-01-01'

comment: |-
  Sales analytics metric view for order performance analysis.
  Joins orders with customers and geography.
  Owner: Analytics Team
  Last updated: 2025-01-15

dimensions:
  - name: order_date
    expr: o_orderdate
    display_name: Order Date
  - name: order_month
    expr: "DATE_TRUNC('MONTH', o_orderdate)"
    display_name: Order Month
  - name: order_year
    expr: YEAR(o_orderdate)
    display_name: Order Year
  - name: order_status
    expr: |-
      CASE o_orderstatus
        WHEN 'O' THEN 'Open'
        WHEN 'P' THEN 'Processing'
        WHEN 'F' THEN 'Fulfilled'
      END
    display_name: Order Status
    synonyms:
      - status
      - fulfillment status
  - name: order_priority
    expr: "SPLIT(o_orderpriority, '-')[0]"
    display_name: Priority
  - name: customer_name
    expr: customer.c_name
    display_name: Customer Name
  - name: market_segment
    expr: customer.c_mktsegment
    display_name: Market Segment
    synonyms:
      - segment
      - industry
  - name: customer_nation
    expr: customer.nation.n_name
    display_name: Country
    synonyms:
      - nation
      - country

measures:
  - name: order_count
    expr: COUNT(DISTINCT o_orderkey)
    display_name: Order Count
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: total_revenue
    expr: SUM(o_totalprice)
    display_name: Total Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - revenue
      - sales
  - name: unique_customers
    expr: COUNT(DISTINCT o_custkey)
    display_name: Unique Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
      abbreviation: compact
  - name: avg_order_value
    expr: MEASURE(total_revenue) / MEASURE(order_count)
    display_name: Avg Order Value
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - AOV
  - name: revenue_per_customer
    expr: MEASURE(total_revenue) / MEASURE(unique_customers)
    display_name: Revenue per Customer
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: open_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'O')
    display_name: Open Order Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
    synonyms:
      - backlog
  - name: fulfilled_order_revenue
    expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus = 'F')
    display_name: Fulfilled Revenue
    format:
      type: currency
      currency_code: USD
      decimal_places:
        type: exact
        places: 2
      abbreviation: compact
  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: order_date
        semiadditive: last
        range: trailing 7 day
    display_name: 7-Day Rolling Customers
    format:
      type: number
      decimal_places:
        type: exact
        places: 0
$$;

メトリック ビューを作成するその他の方法については、「メトリック ビューの 作成と編集」を参照してください。

手順 8: メトリック ビューのクエリを実行する

ビジネスに優しい構文を使用してクエリを実行できるようになりました。

-- Aggregates total revenue, order count, and average order value
-- by customer nation and market segment, ranked by highest revenue first.
SELECT
  customer_nation,
  market_segment,
  MEASURE(total_revenue) AS total_revenue,
  MEASURE(order_count) AS order_count,
  MEASURE(avg_order_value) AS avg_order_value
FROM catalog.schema.tpch_sales_analytics
GROUP BY customer_nation, market_segment
ORDER BY total_revenue DESC;

-- Monthly trend with backlog analysis
SELECT
  order_month,
  order_status,
  MEASURE(total_revenue) AS total_revenue,
  MEASURE(open_order_revenue) AS open_order_revenue
FROM catalog.schema.tpch_sales_analytics
GROUP BY order_month, order_status
ORDER BY order_month;

学習した内容

次を示すメトリック ビューを作成しました。

特徴 例
スノーフレークスキーマ結合 顧客と国に関する注文（入れ子になった多対一結合）
時間ディメンション 日付、月、年単位の粒度
変換されたディメンション CASE ステートメント、 SPLIT 関数
簡単な対策 COUNT、SUM
コンポーザビリティ 以前に定義された測定をavg_order_valueを使用してrevenue_per_customerMEASURE()参照します。
フィルター処理された指標 FILTER (WHERE ...) 条件付き集計の場合
ウィンドウ寸法 trailing 7 day を使用して7日間の顧客数をローリング更新する
エージェントメタデータ display_name、format、synonyms

次のステップ

  • Last updated on