チュートリアル: 結合を使用して完全なメトリクスビューを構築する

このチュートリアルでは、TPC-H データセットを使用して包括的な販売アナリティクスメトリクスビューを構築する手順を説明します。最終的には、次のようなメトリクスビューが得られます。

Snowflakeスキーマを使用して、複数のテーブル間で注文と顧客を結合します。
時間、地理、および注文属性の次元を定義します。
比率、フィルタリングされた集計、ウィンドウメジャーなど、単純なメジャーと複雑なメジャーを計算します。
構成可能性を利用して、より単純な測定値から複雑なメトリクスを構築します
ダッシュボードと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エディタを開くには：

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

ステップ 2: メトリクスビューをセットアップする

バージョンと説明的なコメントから始めましょう。

YAML
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結合の下にネストされており、国が顧客のサブディメンションであるスノーフレークスキーマを反映しています。

YAML
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: 寸法を定義する

ディメンションとは、ユーザーがグループ化やフィルタリングを行う際に使用する属性のことです。

YAML
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()関数で以前に定義された尺度を参照する複雑なメトリクスを構築します。

YAML
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の精度を向上させます。これらのプロパティをディメンションとメジャーに追加して、メトリクスにビジネスコンテキストを与えます。

YAML
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 定義

メトリクスビューの完全な定義は次のとおりです。

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全体を$$区切り文字の間に貼り付けてください。

SQL
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: メトリクスビューをクエリする

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

SQL
-- 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;

SQL
-- 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;

あなたが学んだこと

以下を示すメトリクスビューを構築しました。

機能	例
Snowflakeスキーマ結合	顧客から国への注文（ネストされた多対一結合）
時間的側面	日付、月、年の粒度
変換された次元	`CASE` ステートメント、 `SPLIT`関数
簡単な対策	`COUNT`, `SUM`
構成可能性	`avg_order_value` そして`revenue_per_customer` 、以前に定義された尺度を参照します `MEASURE()`
フィルタリングされた測定値	`FILTER (WHERE ...)` 条件付き集計の場合
窓の寸法	ローリング7日間の顧客数 `trailing 7 day`
エージェントメタデータ	`display_name`、`format`、 `synonyms`

次のステップ

移動平均と年初来累計を計算するための期間設定。
大規模なデータセットのクエリパフォーマンスを向上させるためのメトリクスビューの具体化。
AI/BIダッシュボードでメトリクスビューを使用するには、メトリクスビューを使用します。

要件​

データモデル​

ステップ 1: YAML エディターを開く​

ステップ 2: メトリクス ビューをセットアップする​

ステップ 3: ソースと結合を定義する​

ステップ 4: 寸法を定義する​

ステップ 5: 対策を定義する​

ステップ 6: エージェントのメタデータを追加する​

ステップ 7: 完全な YAML 定義​

ステップ 8: メトリクス ビューをクエリする​

あなたが学んだこと​

次のステップ​

要件