プロジェクトでの開発中に、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 つです。
非集計測定の使用
非集計測定(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 つのレベル(二重またはネスト)の集計が必要です。
-
顧客別にグループ化された売上の合計
-
その合計の平均値
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 でエラーを発生させることなく必要な分析を実行できます。