Während der Entwicklung in einem Projekt wird möglicherweise ein Fehler wie der folgende in einem Explore oder im LookML Validator angezeigt:
Measures with Looker aggregations (sum, average, min, max, list types) may not reference other measures.
Dieser Fehler wird durch einen zusammengefassten Messwert verursacht, der auf eine andere Aggregation oder einen anderen Messwert eines beliebigen Typs in seiner LookML-Definition verweist, z. B.:
SELECT AVG((COUNT(*)) ) AS users.average_count FROM demo_db.users AS users
SQL-Anweisungen wie diese generieren eine doppelte oder verschachtelte Aggregation in SQL. Die meisten SQL-Dialekte können Aggregationen nicht doppelt aggregieren oder verschachteln, sodass ein solcher Versuch den Fehler auslöst.
Lösungen
Es gibt zwei mögliche Lösungen:
- Verwenden Sie nicht aggregierte Kennzahlen, um nicht aggregierende SQL-Anweisungen zwischen Kennzahlen auszuführen.
- Verwenden Sie eine abgeleitete Tabelle, um Aggregationen zu verschachteln oder doppelt zu aggregieren.
Nicht aggregierte Messwerte verwenden
Nicht aggregierte Messwerte wie type: yesno
und type: number
sind die einzigen Messwerte, die auf andere Messwerte oder Aggregationen verweisen können. Bei nicht aggregierten Messungen erfolgt keine Aggregation und daher keine doppelte oder verschachtelte Aggregation. Messwerte von type: number
oder type: yesno
dienen als Platzhalter, sodass darin auf andere Messwerte oder Kombinationen von Messwerten verwiesen werden kann.
Beispielsweise werden die Messwerte type: number
verwendet, um Berechnungen zwischen Messwerten durchzuführen und alle gültigen SQL-Ausdrücke zu verwenden, die zu einer Zahl oder einer Ganzzahl führen.
Im folgenden Beispiel wird mithilfe von type: number
der Prozentsatz aller stornierten Bestellungen berechnet:
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) ;; }
Abgeleitete Tabellen für doppelte oder verschachtelte Aggregationen verwenden
Aber was ist, wenn eine verschachtelte Aggregation für die Durchführung einer Analyse erforderlich ist? Wie gehen Sie beispielsweise vor, wenn Sie den durchschnittlichen Betrag ermitteln möchten, den Kunden während ihrer Customer Lifetime ausgeben („durchschnittlicher Customer Lifetime Value“)? Dazu sind zwei Ebenen – eine Verdoppelung oder Verschachtelung – von Aggregationen erforderlich, darunter:
-
Summe der Verkäufe, gruppiert nach Kunde
-
Ein Durchschnitt dieser Summe
Um dies mit LookML zu erreichen, könnten Sie Folgendes versuchen:
measure: total_revenue { type: sum sql: ${sale_price} ;; } measure: avg_customer_lifetime_value { type: average sql: ${total_revenue} ;; }
Dadurch wird jedoch der Fehler ausgelöst, da der Messwert avg_customer_lifetime_value
eine Aggregation für den Messwert total_revenue
durchführt, der bereits eine Aggregation ist. Wie bereits erwähnt, lösen die meisten SQL-Dialekte einen Fehler aus, wenn in einer Abfrage doppelte oder verschachtelte Aggregate verwendet werden.
Um in SQL einen Durchschnitt der Summe von total_revenue
zu erhalten, ist eine Unterabfrage wie die folgende erforderlich:
SELECT AVG(s.sum) FROM (SELECT SUM(x) as sum FROM ...) AS s
Die entsprechende Lösung in Looker besteht darin, eine abgeleitete Tabelle zu erstellen, um sie zu vereinfachen. den Messwert total_lifetime_value
in ein Feld umwandeln, das aggregiert werden kann. In Looker wird dies als Dimensionieren eines Messwerts bezeichnet. In einer abgeleiteten Tabelle wird das total_lifetime_value
-Maß zu einer Dimension. Sie können dann einen Messwert von type: average
erstellen, der auf die Dimension customer_lifetime_value
verweist:
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} ;; } }
Sobald die abgeleitete Tabelle customer_facts
mit einem Explore zusammengeführt wurde, kann das Maß average_customer_lifetime_value
verwendet werden, um die gewünschte Analyse in einem Explore durchzuführen, ohne dass ein Fehler auftritt.