エラー: Looker 集計（sum、average、min、max、listのタイプ）を含む測定は他の測定を参照できません。

プロジェクトでの開発中に、Explore や LookML バリデータに次のようなエラーが表示されることがあります。

  Measures with Looker aggregations (sum, average, min, max, list types) may not reference other measures.

このエラーは、集計測定が LookML 定義内の任意のタイプの別の集計または測定を参照することによって発生します。次に例を示します。

    SELECT AVG((COUNT(*)) ) AS users.average_count FROM demo_db.users  AS users

このような SQL ステートメントでは、SQL で二重またはネストされた集計が生成されます。ほとんどの SQL 言語では二重集計やネスト集計を行うことはできないので、このような操作を行うとエラーがトリガーされます。

ソリューション

考えられる解決法は次の 2 つです。

  1. 非集計測定を使用して、測定間で非集計の SQL ステートメントを実行する。
  2. 派生テーブルを使用して、集計をネストするか、二重集計を行います。

非集計測定の使用

非集計測定（type: yesno や type: number など）は、他の測定または集計を参照できる唯一の測定まです。非集計測定では集計が行われないため、二重集計またはネストされた集計は行われません。type: number または type: yesno の測定はプレースホルダとして機能するので、他の測定または測定の組み合わせをそれらの内で参照できます。

たとえば、type: number の測定は、測定間で計算を実行し、数値または整数をもたらす有効な SQL 式を取得するために使用されます。

次の例では、type: number を使用して、キャンセルされたすべての注文の割合を計算します。

measure: order_count { # Base measure #1
    type: count
    sql: ${order_id} ;;
}

measure: cancelled_orders { # Base measure #2
    type: count
    filters: [status: "Cancelled"]
}

measure: percent_cancelled_orders { # New measure
    type: number
    sql: (1.0*${cancelled_orders})/ISNULL(${order_count},0) ;;
}

二重集計またはネストされた集計に派生テーブルを使用する

しかし、分析を行うためにネストされた集計が必要な場合はどうでしょうか。たとえば、顧客が費やすライフタイムの平均値（「顧客の平均ライフタイム バリュー」）を知る必要がある場合はどうでしょうか。これには、次のような 2 つのレベル（二重またはネスト）の集計が必要です。

  1. 顧客別にグループ化された売上の合計

  2. その合計の平均値

LookML でこれを実現するには、次のようにします。 

measure: total_revenue {
    type: sum
    sql: ${sale_price} ;;
}

measure: avg_customer_lifetime_value {
    type: average
    sql: ${total_revenue} ;;
}

ただし、すでに集計されている、total_revenue 測定での集計を avg_customer_lifetime_value 測定が実行しているので、エラーが発生します。前述したように、ほとんどの SQL 言語では、二重集計またはネストされた集計がクエリで使用されると、エラーが発生します。

SQL で total_revenue の合計の平均を求めるには、次のようなサブクエリが必要です。

  SELECT AVG(s.sum) FROM (SELECT SUM(x) as sum FROM ...) AS s

Looker での同等のソリューションは、派生テーブルを作成して total_lifetime_value 測定を集計可能なフィールドに「フラット化」することです。Looker では、これは測定のディメンション化と言います。派生テーブルの場合、total_lifetime_value 測定がディメンションになります。これで、customer_lifetime_value ディメンションを参照する type: average の測定を作成できます。

view: customer_facts {
    derived_table: {
        sql:
        SELECT
            user_id,
            COALESCE(SUM(sale_price), 0) AS customer_lifetime_value
        FROM orders
        GROUP BY user_id;;
    }

    dimension: customer_lifetime_value {
        type: number
        sql: ${TABLE}."customer_lifetime_value" ;;
    }

    measure: average_customer_lifetime_value {
        type: average
        sql: ${customer_lifetime_value} ;;
    }
}

customer_facts 派生テーブルを Explore に結合したら、average_customer_lifetime_value メジャーを使用して、Explore でエラーを発生させることなく必要な分析を実行できます。