メトリクス ビューの高度なテクニック
メトリクスビューの高度なテクニックを使用すると、複雑なビジネスロジックを表現し、セマンティックレイヤー全体で定義を再利用することができます。このページでは、そのような2つのテクニックについて説明します。
- **ウィンドウメジャー**：移動平均、累積合計、期間ごとの変化などの時系列計算。
- 構成可能性 ：ロジックを書き直すのではなく、他のメジャーを参照して複雑なメジャーを構築します。
このページは、基本的なメトリクス ビュー モデリングの概念を理解していることを前提としています。 「メトリクス ビューのモデル化」を参照してください。
このページに掲載されている例では、卸売サプライチェーンをモデル化したTPC-Hサンプルデータセットを使用しています。TPC-H データセットの詳細については、 「 tpch 」を参照してください。 メトリクス ビューでこのデータセットを使用するエンドツーエンドのチュートリアルについては、 「チュートリアル: 結合を使用して完全なメトリクス ビューを構築する」を参照してください。
ウィンドウメジャー
実験段階
この機能は試験的なものです。
ウィンドウメジャーを使用すると、メトリクスビューでウィンドウベース、累積、またはsemiadditive集計を使用してメジャーを定義でき、移動平均、期間ごとの変化、累計などの計算をサポートします。
ウィンドウメジャーを定義します
ウィンドウメジャーには、以下の必須フィールドが含まれます。
-
order ：ウィンドウの順序を決定するフィールド。
-
range ：ウィンドウの範囲を定義します。サポートされている値は、
current、cumulative、trailing、leading、およびallです。構文と説明の詳細は、 「サポートされているrange値」を参照してください。trailingとleadingにあるinclusiveおよびexclusive修飾子の詳細については、「アンカー行を含める、または除外する」をご参照ください。 -
半加法 ：クエリの
GROUP BYに順序フィールドが含まれていない場合に、メジャーがどのように集計されるかを指定します。指定できる値：firstとlastです。
ウィンドウメジャーは、以下のオプションフィールドもサポートしています。
- 「オフセット」：ウィンドウフレームを
orderフィールドに沿って前後に一定の間隔で移動させます。月次や年次といった期間ごとの測定にご利用ください。構文、サポートされている単位、および制約については、ウィンドウの測定値を参照してください。
offset がウィンドウフレームをシフトさせる方法
Databricks Runtime 18.1 と YAML バージョン 1.1 以降が必要です。「バージョン要件」を参照してください。
rangeフィールドは、アンカー行を基準としたウィンドウの形状を定義し、offsetは指定された間隔でそのフレームをorderに沿ってスライドさせます。以下の表は、アンカー行tを基準として、kのoffsetがある場合とない場合の各rangeの値のフレームを示しています。
範囲 | オフセットなしのフレーム | 〜で枠付け |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
| パーティション全体 | パーティション全体（変更なし） |
offset semiadditive とは無関係です。first または last の選択肢は、order がクエリのGROUP BYに含まれていない場合に、メジャーがどのように集計されるかを制御します。
最良の結果を得るには、offsetをorderの自然な特性に合わせてください。月次データの場合、offset: -12 monthはoffset: -365 dayよりも推奨されます。月と年の算術は可変長の月やうるう年を考慮しますが、dayの算術は考慮しないためです。
アンカー行を含めるか除外する
Databricks Runtime 18.1 と YAML バージョン 1.1 以降が必要です。「バージョン要件」を参照してください。
trailing と leading の範囲では、オプションの inclusive または exclusive キーワードは、アンカー行のウィンドウ値（例: 今日）がローリングウィンドウの一部であるかどうかを制御します。
キーワード | 意味 | 行は範囲内ですか？ |
|---|---|---|
| アンカー行を**含む**N単位 | はい |
| n単位にはアンカー行は含まれません。 | No |
次の例は、inclusiveとexclusiveが、アンカー日付2025-01-05のローリングウィンドウにtrailing 3 dayでどのように影響するかを示します。
基になるデータは、1日あたり1行で、次の値を持つとします。
Date | Value |
|---|---|
|
|
|
|
|
|
|
|
各修飾子は、アンカーを基準に3日分の行を選択し、それらの値を合計します。
変更 | ウィンドウ内の日付 | 値 | 合計 |
|---|---|---|---|
|
|
|
|
|
|
|
|
leading 範囲は逆方向でも同じロジックに従います。
後続、移動、または先行するウィンドウメジャーの例
以下の例では、注文を行ったユニーク顧客の直近7日間のローリングカウントを計算します。このメトリクスは、各日付までの 1 週間に購入を行った個別の顧客数を表示することで、長期にわたる顧客エンゲージメントの傾向を追跡します。
version: 1.1
source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'
fields:
- name: date
expr: o_orderdate
measures:
- name: t7d_customers
expr: COUNT(DISTINCT o_custkey)
window:
- order: date
range: trailing 7 day
semiadditive: last
この例では、以下の設定が適用されます。
order: date指定されたdateフィールドがウィンドウを順序付けます。range: trailing 7 dayウィンドウは各日付の7日前（日付自体を除く）として定義されます。semiadditive: lastdateがグループ化列ではない場合、7日間のウィンドウの最後の値が返されます。
期間ごとのウィンドウメジャーの例
以下の例では、今日の収益（すべての注文価格の合計）を昨日の収益と比較することで、前日比売上成長を計算します。このメトリクスは日次売上トレンドを特定し、収益の変化率を示します。
version: 1.1
source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'
fields:
- name: date
expr: o_orderdate
measures:
- name: previous_day_sales
expr: SUM(o_totalprice)
window:
- order: date
range: trailing 1 day
semiadditive: last
- name: current_day_sales
expr: SUM(o_totalprice)
window:
- order: date
range: current
semiadditive: last
- name: day_over_day_growth
expr: (MEASURE(current_day_sales) - MEASURE(previous_day_sales)) / MEASURE(previous_day_sales) * 100
この例では、以下の設定が適用されます。
- 2つのウィンドウメジャーが使用されます。1つは前日の総売上高を計算するためのもので、もう1つは当日の総売上高を計算するためのものです。
- 3つ目の指標は、当日と前日の間の変化率（成長率）を算出するものです。
前年比ウィンドウメジャーの例 offset
offset 修飾子は期間ごとのメジャーの構成要素です。ベースメジャーのシフトされたコピーを定義し、その2つを構成して、メトリクスビューでデルタ、比率、または成長率を直接表現します。
以下の例は、各月の売上を前年の同月と比較することで、前年比売上成長率を計算します。シフトメジャーは、month フィールドに沿って12か月間を遡るために offset: -12 month を使用します。
version: 1.1
source: main.default.monthly_sales
fields:
- name: month
expr: month
- name: category
expr: category
measures:
- name: monthly_sales
expr: SUM(sales)
window:
- order: month
range: current
semiadditive: last
- name: monthly_sales_py
expr: SUM(sales)
window:
- order: month
range: current
semiadditive: last
offset: -12 month
- name: yoy_growth
expr: MEASURE(monthly_sales) - MEASURE(monthly_sales_py)
- name: yoy_growth_pct
expr: (MEASURE(monthly_sales) - MEASURE(monthly_sales_py))
/ NULLIF(MEASURE(monthly_sales_py), 0)
この例では、以下の設定が適用されます。
monthly_sales今月の売上を集計する基本メジャーです。monthly_sales_pyは、offset: -12 monthを使用して12か月分後方にずらした同じ指標です。2025年1月については、2024年1月の値を返します。yoy_growthそして、yoy_growth_pctは絶対的変化とパーセンテージ変化を表す2つのメジャーを構成NULLIFを使用することで、前年度の値がゼロの場合にゼロ除算エラーを回避できます。
累積（実行）合計値の例
以下の例では、データセットの開始時点から各日付までの累計売上高を計算します。この累計額は、時間の経過とともにどれだけの総収益が生み出されたかを示しており、年間収益目標の達成状況を追跡したり、長期的な成長パターンを分析したりするのに役立ちます。
version: 1.1
source: samples.tpch.orders
filter: o_orderdate > DATE'1998-01-01'
fields:
- name: date
expr: o_orderdate
- name: customer
expr: o_custkey
measures:
- name: running_total_sales
expr: SUM(o_totalprice)
window:
- order: date
range: cumulative
semiadditive: last
この例では、以下の設定が適用されます。
order: dateウィンドウを時系列に順序付けます。range: cumulativeデータセットの先頭から各日付までを含むすべてのデータをウィンドウとして定義します。semiadditive: lastクエリのGROUP BYにdateが含まれていない場合、すべての日付にわたって合計するのではなく、最新の累積値を返します。
期間累計メジャーの例
次の例では、年初来（YTD）の売上収益を計算します。このメジャーは、各年1月1日から現在の日付までに発生した累積収益を表し、各新年の初めにリセットされます。
version: 1.1
source: samples.tpch.orders
filter: o_orderdate > DATE'1997-01-01'
fields:
- name: date
expr: o_orderdate
- name: year
expr: DATE_TRUNC('year', o_orderdate)
measures:
- name: ytd_sales
expr: SUM(o_totalprice)
window:
- order: date
range: cumulative
semiadditive: last
- order: year
range: current
semiadditive: last
この例では、以下の設定が適用されます。
- 2つのウィンドウ指定が使用されています。1つは
dateフィールドの累積合計用で、もう1つは合計をcurrent年に限定するためのものです。 yearフィールドは、毎年のはじめにリセットされるように累積合計を制限します。
半加法メジャーの例
以下の例ではアカウント残高を計算していますが、日付をまたいで合計してはいけません（月曜日の残高を火曜日の残高に加えて合計残高を求めることはできません）。 その代わりに、複数日にわたって集計する場合、このメジャーは最新の残高を返します。ただし、このメジャーを顧客全体で合計して、特定の日のすべてのアカウントの合計残高を表示することはできます。
version: 1.1
fields:
- name: date
expr: date
- name: customer
expr: customer_id
measures:
- name: semiadditive_balance
expr: SUM(balance)
window:
- order: date
range: current
semiadditive: last
この例では、以下の設定が適用されます。
order: dateウィンドウを時系列に順序付けます。range: currentウィンドウは1日に制限され、日をまたいだ集計は行われません。semiadditive: last複数日にわたって集計する場合、最新の残高を返します。
このウィンドウ測定は、引き続き顧客全体の合計を算出し、日ごとの合計残高を取得します。
ウィンドウメジャーを照会する
他のメトリクス ビューと同様に、ウィンドウ メジャーを使用してメトリクス ビューをクエリできます。 次の例では、メトリクス ビューをクエリします。
SELECT
state,
DATE_TRUNC('month', date),
MEASURE(t7d_customers) as m
FROM my_metric_view
WHERE date >= DATE'2024-06-01'
GROUP BY ALL
構成可能性
メトリクスビューは構成可能です。ロジックをゼロから書き直すのではなく、既存のものを参照する新しいフィールドとメジャーを構築することができます。これにより、重複が減り、複雑なメトリクスの定義を維持しやすくなります。
構成可能性は、単一のメトリクス ビュー内と、あるメトリクス ビューが別のメトリクス ビューのソースとして使用される場合の複数のメトリクス ビューにまたがって、2つのレベルで機能します。
構成可能性は、以下の参照パターンをサポートしています。
- 新しいフィールドにおける以前のフィールド。
- 新しいメジャーにおけるフィールドと以前のメジャー
- 新しいフィールドのソースとして使用されるメトリクスビューからのフィールドです。
- メトリクスビューのフィールドと測定値は、新しい測定値のソースとして使用されます。
構成可能性を考慮したメジャーを定義する
measuresセクションでは、ソース メトリクス ビューのメジャー、または同じメトリクス ビューで以前に定義されたメジャーを参照できます。 このアプローチにより、セマンティックレイヤーの一貫性、監査可能性、および保守性が向上します。
メジャーのタイプ | 説明 | 例 |
|---|---|---|
Atomic | ソース列に対するシンプルで直接的な集計。これらは構成要素となる。 |
|
Composed |
|
|
例：平均注文額（AOV）
次の例では、 total_revenue （注文価格の合計）とorder_count （注文数）という2つのAtomicメジャーを使用して平均注文額（AOV）を定義します。avg_order_valueメジャーは、両方のAtomicメジャーを参照します。
version: 1.1
source: samples.tpch.orders
measures:
# Total Revenue
- name: total_revenue
expr: SUM(o_totalprice)
# Order Count
- name: order_count
expr: COUNT(1)
# Composed Measure: Average Order Value (AOV)
- name: avg_order_value
# Defines AOV as Total Revenue divided by Order Count
expr: MEASURE(total_revenue) / MEASURE(order_count)
total_revenue定義が変更された場合（例えば、税金を除外する場合）、 avg_order_value自動的に更新された定義を使用します。
条件付きロジックによる合成可能性
構成可能性を利用することで、単純な期間比較計算にウィンドウ関数に頼ることなく、複雑な比率、条件付きパーセンテージ、成長率を作成できます。
例：履行率
次の例では、履行率、つまりステータスが'F' （履行済み）の注文の割合を計算します。このメジャーは、処理済みの注文数を総注文数で割ったものです。
version: 1.1
source: samples.tpch.orders
measures:
# Total Orders (denominator)
- name: total_orders
expr: COUNT(1)
# Fulfilled Orders (numerator)
- name: fulfilled_orders
expr: COUNT(1) FILTER (WHERE o_orderstatus = 'F')
# Composed Measure: Fulfillment Rate (Ratio)
- name: fulfillment_rate
expr: MEASURE(fulfilled_orders) / MEASURE(total_orders)
format:
type: percentage
構成可能性に関するベストプラクティス
- まずAtomicメジャーを定義します : それらを参照するメジャーを定義する前に、基本メジャー (
SUM、COUNT、AVG) を確立します。 - 参照には
MEASURE()使用します 。exprで別の小節を参照する場合は、MEASURE()関数を使用します。集計ロジックを手動で繰り返さないでください。例えば、両方の値に対するメジャーが既に存在する場合は、SUM(a) / COUNT(b)を避けてください。 - 読みやすさを優先する ：明確な数式を用いてメジャーを作成する。例えば、
MEASURE(gross_profit) / MEASURE(total_revenue)は単一の複雑な SQL 式よりも分かりやすい。 - セマンティックメタデータを追加する ：セマンティックメタデータを使用して、複合的な測定値（例えば、パーセンテージや通貨）を下流のツール向けにフォーマットします。「メトリクス ビューのエージェント メタデータ」を参照してください。