メトリクスビューの高度なテクニック
メトリクスビューの高度なテクニックを使用すると、複雑なビジネスロジックを表現し、セマンティックレイヤー全体で定義を再利用できます。このページでは、2つのテクニックについて説明します：
- **ウィンドウメジャー**：移動平均、累積合計、期間ごとの変更などの時系列計算用です。
- 構成可能性：ロジックを書き換えるのではなく、他のメジャーを参照して複雑なメジャーを構築するため。
このページでは、基本的なメトリクスビューのモデリングの概念に精通していることを前提としています。メトリクスビューをモデリングするを参照してください。
このページの例では、卸売サプライチェーンをモデル化した TPC-H サンプル データセットを使用しています。TPC-H データセットの詳細については、tpchを参照してください。このデータセットとメトリクス ビューを使用したエンドツーエンドのチュートリアルについては、「チュートリアル: 結合とデータ モデリングを使用してメトリクス ビューを構築する」を参照してください。
ウィンドウメジャー
実験段階
この機能は実験的です。
ウィンドウメジャーを使用すると、メトリクスビュー内でウィンドウ関数、累積、または半加算集計によるメジャーを定義できます。これらは、移動平均、期間ごとの変更、累計などの計算をサポートしています。
カタログエクスプローラーエディターまたはYAMLでウィンドウメジャーを追加できます。
エディターでウィンドウメジャーを追加します
メトリクスビューエディターの UI tabで、メジャーを編集しているときに + Window をクリックします。 + Window は、 ビルダー モードと カスタム モードの両方で利用可能です。エディターのウィンドウオプションは、ウィンドウメジャーを定義するで説明されているYAMLフィールドに対応しています。
メジャーの作成と編集の詳細については、「メトリクス ビューを作成する」を参照してください。
ウィンドウメジャーを定義します
ウィンドウメジャーには、次の必須フィールドが含まれます。
-
**order**: ウィンドウの順序を決定するフィールドです。
-
range ：ウィンドウの範囲を定義します。サポートされている値は、
current、cumulative、trailing、leading、およびallです。完全な構文と説明については、「サポートされているrange値」を参照してください。trailingとleadingのinclusiveおよびexclusive修飾子の詳細については、「アンカー行を含めるか除外する」を参照してください。 -
semiadditive : 順序フィールドがクエリーの
GROUP BYに含まれていない場合に、メジャーを集計する方法を指定します。可能な値:firstとlastです。
ウィンドウメジャーは以下のオプションフィールドもサポートしています:
- オフセット ：固定された間隔で、
orderフィールドに沿ってウィンドウフレームを後方または前方にシフトします。月次比較や年次比較などの期間比較メジャーにこれを使用します。構文、サポートされている単位、および制約については、ウィンドウメジャーを参照してください。
offset がウィンドウフレームをシフトする方法
最小コンピュートおよびYAML仕様バージョン要件については、メトリクスビュー機能の可用性を参照してください。
range フィールドはアンカー行を基準としたウィンドウの形状を定義し、offset はそのフレームを order に沿って指定された間隔でスライドさせます。次の表は、アンカー行 t を基準とした、k の offset がある場合とない場合の、各 range 値のフレームを示します。
範囲 | オフセットなしのフレーム | フレームと |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
| パーティション全体 | パーティション全体（変更なし） |
offset semiadditiveとは無関係です。firstまたはlastの選択肢は、orderがクエリーのGROUP BYに含まれていない場合に、メジャーがどのように集約されるかを引き続き制御します。
最良の結果を得るには、offsetをorderの自然な粒度に合わせます。月次データの場合、月と年の算術は可変長の月と閏年を尊重するためoffset: -365 dayよりもoffset: -12 monthが推奨されますが、dayの算術は尊重しないためです。
アンカー行を含めるか除外する
最小コンピュートおよびYAML仕様バージョン要件については、メトリクスビュー機能の可用性を参照してください。
trailingおよびleadingの範囲の場合、オプションのinclusiveまたはexclusiveキーワードは、アンカー行のウィンドウ値（たとえば、今日）がローリングウィンドウの一部であるかどうかを制御します。
キーワード | 意味 | 範囲内の行を固定しますか？ |
|---|---|---|
| アンカー行を**含め**て、 n 単位。 | はい |
| アンカーロウを含まない n 単位です。 | No |
次の例は、inclusive と exclusive が、trailing 3 day を伴うアンカー日付 2025-01-05 のローリングウィンドウにどのように影響するかを示しています。
基になるデータには、次の値を持つ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: datedateフィールドがウィンドウを順序付けることを指定します。range: trailing 7 dayウィンドウを、各日付の7日前（日付自体を除く）と定義します。semiadditive: lastdateがグルーピング列ではない場合、7日間のウィンドウの最後の値を返します。
SQL を使用してメトリクスビューを作成します
Catalog Explorer の外部でこのメトリクスビューを作成するには、YAMLをCREATE OR REPLACE VIEW ... WITH METRICS LANGUAGE YAML ASで囲み、定義を$$デリミターの間に配置します。
CREATE OR REPLACE VIEW catalog.schema.rolling_customers WITH METRICS LANGUAGE YAML AS
$$
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
$$
このページの他の完全な定義も同じパターンに従います。
期間ごとのウィンドウメジャーの例
次の例では、今日の収益（すべての注文価格の合計）を昨日の収益と比較することで、前日比の売上成長を計算します。このメトリクスは、日次売上トレンドを特定し、収益のパーセンテージ変化率を示します。
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_pyoffset: -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: lastdateがクエリーのGROUP BYに含まれていない場合、すべての日付を合計するのではなく、最新の累積値を返します。
期間累計メジャーの例
次の例は、年初来 (YTD) の売上高を計算します。このメジャーは、毎年1月1日から現在までの累積収益を示し、各新年の開始時にResetされます。
version: 1.1
source: samples.tpch.orders
filter: o_orderdate > DATE'1997-01-01'
fields:
- name: date
expr: o_orderdate
- name: month
expr: DATE_TRUNC('MONTH', date)
- name: year
expr: DATE_TRUNC('year', date)
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フィールドは、各新年の開始時にResetされるように累積合計を制限します。monthフィールドとyearフィールドは、順序フィールドdate上の日付階層を形成します。各々は、基になるo_orderdate列ではなく、名前によってdateフィールドで定義されるため、クエリーはこれらのメジャーでグループ化できます。日付階層フィールドでグループ化を参照してください。
半加法メジャーの例
以下の例ではアカウント残高を計算していますが、日付をまたいで合計してはなりません（月曜日の残高を火曜日の残高に加えて合計残高を求めることはできません）。その代わりに、複数日にわたって集計する場合、メジャーは最新の残高を返します。ただし、このメジャーは、特定の日のすべてのアカウントの合計残高を表示するために顧客全体で合計できます。
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複数日にわたって集計する場合、最新の残高を返します。
このウィンドウメジャーは、すべての顧客を合計して1日あたりの全体的な残高を取得します。
ウィンドウメジャーをクエリーします
他のメトリクスビューと同様に、ウィンドウメジャーを含むメトリクスビューをクエリーできます。ウィンドウメジャーは order フィールドに沿ってコンピュートされるため、結果を時間で細分化するクエリーは、直接的またはその上に定義された日付階層フィールドを介して、そのフィールドを参照する必要があります。クエリーが順序フィールドを参照しない場合、半加法メジャーの例に記載されているように、semiadditive キーワードが返される値を決定します。
以下の例では、ウィンドウメジャーをstateで、および注文フィールドdateに対して月式でグループ化します：
SELECT
state,
DATE_TRUNC('month', date),
MEASURE(t7d_customers) as m
FROM my_metric_view
WHERE date >= DATE'2024-06-01'
GROUP BY ALL
日付階層フィールドでグループ化します。
日付階層は、注文フィールドを週、月、年などのより粗い粒度にロールアップします。各レベルを、基になるソース列ではなく、順序フィールドの名前でフィールドとして定義します。
fields:
- name: date
expr: o_orderdate
# Date hierarchy: each level is defined on the order field `date`,
# not on the underlying o_orderdate column.
- name: month
expr: DATE_TRUNC('MONTH', date)
- name: year
expr: DATE_TRUNC('year', date)
ウィンドウメジャーを階層レベルでグループ化すると、その粒度でメジャーが返されます。期間累計の例が、期間累計メジャーの例にあるようにytd_metric_viewとして作成されたと仮定すると、以下のクエリーは、各月の最終日時点の年度累計値（YTD値）を返します。
SELECT month, MEASURE(ytd_sales) AS ytd_sales
FROM ytd_metric_view
GROUP BY month
ORDER BY month;
基になるソース列に、たとえばDATE_TRUNC('MONTH', o_orderdate)のような階層レベルを定義すると、式が同等に見えても、順序フィールドdateへのLinkが切断されます。そのようなフィールドでウィンドウメジャーをグループ化すると、不正な結果が返されます。
構成可能性
メトリクスビューは構成可能です。ロジックをゼロから書き直すのではなく、既存のものを参照する新しいフィールドとメジャーを作成できます。これにより重複が削減され、複雑なメトリクスの定義の維持管理が容易になります。
コンポーザビリティは、単一のメトリクスビュー内、およびあるメトリクスビューが別のメトリクスビューのソースとして使用される場合の、複数のメトリクスビューにわたる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 式よりも分かりやすい。 - セマンティック メタデータを追加 ：ダウンストリームツール向けに、セマンティック メタデータを使用して構成メジャー（たとえば、パーセンテージや通貨など）をフォーマットします。メトリクスビューでのエージェント メタデータを参照してください。