メインコンテンツまでスキップ
に最終更新

メトリクス ビューで詳細レベル (LOD) 式を使用する

詳細レベル (LOD) 式を使用すると、クエリー内のフィールド (ディメンションとも呼ばれます) とは独立して、集計を計算する粒度を指定できます。これにより、分母がクエリーよりも粗い粒度で集計される、合計に対する割合などのメトリクスをコンピュートできます。

詳細レベル表現とは何ですか？​

レベルオブディテール式を使用すると、クエリーに存在するフィールドに関係なく、集計を計算するときにどのフィールドを使用するかを正確に指定できます。これにより、計算の範囲をきめ細かく制御できます。

詳細レベルの式には、次の3つのタイプがあります。

  • 固定詳細レベル ：式自体で指定された定義済みのフィールドセットを集計し、クエリー内の他のフィールドを無視します。
  • **より粗い詳細レベル**：特定のフィールドを**グループ**化から除外することで、**クエリー**よりも粗い粒度で集計します。
  • 詳細度を細かくする ：追加のフィールドを含めることでクエリーよりも細かい粒度で集計し、その後、結果をクエリーの粒度に戻して再集計します。

固定詳細レベル​

固定詳細レベル式は、クエリ内のフィールドを無視して、定義した粒度で集計を計算します。メトリクスビューでは、固定LOD式が、列の定義のexprフィールドで、SQLウィンドウ関数とPARTITION BY句を使用して直接定義されます。

固定詳細レベルを使用する場合​

以下が必要な場合は、固定詳細レベル式を使用します。

  • クエリーのグループ化に依存しません ：すべての用途で静的パーティショニングを使用するメトリクス。
  • データセットレベルの集計： 行レベルのグループ化と比較したグローバル集計（たとえば、優先度別の総売上高のパーセンテージ）。
  • **多階層**: 詳細レベルとロールアップレベルのメトリクスが同じメトリクスビューで利用可能です。

構文​

固定LOD式は、定義された粒度で集計を計算するためにSQLウィンドウ関数を使用します。ウィンドウ関数をフィールド定義の expr フィールドに直接配置します：

YAML
fields:
  - name: <lod_name>
    expr: <AGGREGATE_FUNCTION>(<column>) OVER (PARTITION BY <dim1>, <dim2>, ...)

データセット全体を集計するには、PARTITION BY 句を省略し、OVER の後に空の括弧を残します。

例：注文優先度別の合計売上​

各注文の売上をその優先度グループの総売上と並行して比較できるメトリクスビューを定義したいとします。次の例では、ソースクエリー内でpriority_total_priceをコンピュートし、IDフィールドとして公開します：

YAML
version: 1.1

source: samples.tpch.orders

fields:
  - name: order_priority
    expr: o_orderpriority
  - name: order_date
    expr: o_orderdate
  - name: priority_total_price
    expr: SUM(o_totalprice) OVER (PARTITION BY o_orderpriority)

measures:
  - name: total_sales
    expr: SUM(o_totalprice)

  - name: pct_of_priority_total
    expr: SUM(o_totalprice) / ANY_VALUE(priority_total_price)

priority_total_priceフィールドは、各優先度グループの固定合計をそのexprフィールドに直接定義します。pct_of_priority_totalメジャーは、個々の注文売上をその固定合計で割って割合を算出します。これはクエリーが結果をどのようにグループ化するかに関わらない動作です。

注記

メジャー式で固定詳細レベルフィールドを参照する場合、集計関数で囲みます。前の例のように、グループ内の値が定数である場合は ANY_VALUE を使用します。

SQL を使用してメトリクスビューを作成します

Catalog Explorer の外部でこのメトリクスビューを作成するには、YAMLをCREATE OR REPLACE VIEW ... WITH METRICS LANGUAGE YAML ASで囲み、定義を$$デリミターの間に配置します。

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

  source: samples.tpch.orders

  fields:
    - name: order_priority
      expr: o_orderpriority
    - name: order_date
      expr: o_orderdate
    - name: priority_total_price
      expr: SUM(o_totalprice) OVER (PARTITION BY o_orderpriority)

  measures:
    - name: total_sales
      expr: SUM(o_totalprice)

    - name: pct_of_priority_total
      expr: SUM(o_totalprice) / ANY_VALUE(priority_total_price)
$$

このページに記載されている、より粗い詳細レベルの例も同様のパターンに従います。

固定詳細レベル式でのフィルタリング​

固定レベルオブディテール式は、クエリー時フィルターが適用される前に計算されます。固定LOD計算にフィルターを適用するには、CASE ステートメントまたは FILTER 句を使用して、ウィンドウ関数式内にフィルター条件を含めます。

より粗い詳細レベル​

より粗い詳細レベル式は、パーティションから1つ以上のフィールドを除外することで、クエリーよりも粗い粒度で集計されます。メトリクス ビューでは、より粗いLOD式が、allの範囲指定とともにウィンドウメジャーを使用して実装されます。

より粗い詳細レベルを使用する場合​

必要な場合は、より粗い詳細レベルの式を使用します。

  • 動的グループ化 ：クエリーのグループ化に適応する集計（例えば、選択したフィールドの合計に対するパーセンテージ）。
  • フィルター対応集計 ：クエリー時のフィルターを考慮しながら、より粗い粒度でコンピュートします。

構文​

パーティションから除外する各フィールドについて、range: allを使用してウィンドウメジャーを定義します：

YAML
measures:
  - name: <measure_name>
    expr: <AGGREGATE_EXPRESSION>
    window:
      - order: <field_to_exclude>
        range: all
        semiadditive: last

複数のフィールドを除外するには、フィールドごとにwindow配列にエントリを追加してください。

例：総売上高の割合​

各注文の優先順位ごとの総売上高の割合を計算するには：

YAML
version: 1.1

source: samples.tpch.orders

fields:
  - name: order_priority
    expr: o_orderpriority

measures:
  - name: total_sales
    expr: SUM(o_totalprice)

  - name: all_priorities_sales
    expr: SUM(o_totalprice)
    window:
      - order: order_priority
        range: all
        semiadditive: last

  - name: pct_of_total_sales
    expr: SUM(o_totalprice) / MEASURE(all_priorities_sales)

この例では：

  • total_sales クエリーのグループ化レベルで集計します。
  • all_priorities_sales すべての注文優先度で合計を**コンピュート**するためにrange: allを使用しますが、**クエリー**のorder_priorityフィールドは無視されます。
  • pct_of_total_sales 優先度レベルの売上を合計で割って、パーセンテージを算出します。

より詳細なレベル​

詳細レベル式は、クエリーよりも細かい粒度で集計を計算し、その結果をクエリーの粒度まで再集計します。内側の集計は、クエリーのフィールドに含めた 1 つ以上の追加フィールドを加えた状態で評価され、外側の集計によってそれらの値がロールアップされます。メトリクスビューにおいて、細かい LOD 式は、include リストと outer_aggregate 関数を含む partition ブロックを使用するメジャーです。

より詳細なレベルを使用する場合​

次が必要な場合は、より細かい詳細度の式を使用します：

  • 集計の集計 ：顧客ごとの総収益などのエンティティごとの集計を計算し、クエリーの粒度で顧客全体の平均などの要約を行います。
  • 粒度の独立したサマリー ：クエリーよりも細かい粒度で内部のメトリクスをコンピュートし、クエリーの粒度の変化に合わせて自動的にロールアップします。

構文​

partition ブロックを使用してメジャーを定義します。partition ブロックは、インナー集計のためにクエリーのグレインにフィールドを追加し、結果をロールアップする集計を指定します。

YAML
measures:
  - name: <measure_name>
    expr: <INNER_AGGREGATE_EXPRESSION>
    partition:
      include: [<field1>, <field2>, ...]
      outer_aggregate: <AGGREGATE_FUNCTION>

この構文：

  • expr は内部集計です。これは、クエリーのグレインに include 内のフィールドを追加したもので評価されます。
  • include 内側の集計を計算するときにクエリーの粒度に追加する1つ以上のフィールドをリスト表示します。メトリクスビューで定義されたフィールドを各エントリーに指定する必要があります。
  • outer_aggregate は、内部結果をクエリーのグレインにロールアップする集計関数です。
注記

outer_aggregate は、単一引数の集計関数（SUM、AVG、MIN、MAX、COUNT、または MEDIAN）である必要があります。COUNT(DISTINCT ...) および PERCENTILE などの複数引数を取る関数は、外側の集計としてはサポートされていませんが、expr で使用することはできます。より詳細なレベルのメジャーでは window ブロックを使用することもできず、他の詳細メジャーやウィンドウメジャーを参照することもできません。

例：顧客あたりの平均収益​

注文優先度など、クエリーで使用される任意の粒度で集計された、顧客あたりの平均収益を求めると仮定します。内側の集計として顧客ごとの総収益を計算し、外側の集計で顧客全体にわたって平均します：

YAML
version: 1.1

source: samples.tpch.orders

fields:
  - name: order_priority
    expr: o_orderpriority
  - name: customer
    expr: o_custkey

measures:
  - name: total_revenue
    expr: SUM(o_totalprice)

  - name: avg_revenue_per_customer
    expr: SUM(o_totalprice)
    partition:
      include: [customer]
      outer_aggregate: AVG

この例では：

  • total_revenue クエリーのグループ化レベルで集計します。
  • avg_revenue_per_customer （order_priority、customer）の粒度で SUM(o_totalprice) のコンピュートを行い、プライオリティ内の各顧客の総収益を算出した後、outer_aggregate: AVG を使用してそれらの顧客ごとの合計をクエリーの粒度まで平均化して戻します。

他のメジャーと同様に、メジャーをクエリーします。内部集計がコンピュートされている間のみ、includeフィールドがクエリーのグレインに追加されます。

SQL
SELECT order_priority, MEASURE(avg_revenue_per_customer)
FROM catalog.schema.orders_mv
GROUP BY order_priority

各行は、その注文優先度に対する顧客あたりの平均収益を返します。

その他のリソース​

このページの見出し