Fehler: Messwerte mit Looker-Aggregationen (Summe, Mittelwert, Minimum, Maximum, Listentypen) dürfen nicht auf andere Messwerte verweisen

Während der Entwicklung in einem Projekt wird in einem Explore oder im LookML-Validator möglicherweise ein Fehler wie der folgende angezeigt:

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

Dieser Fehler wird durch einen Aggregatmesswert verursacht, der in seiner LookML-Definition auf eine andere Aggregation oder einen anderen Messwert verweist, z. B.:

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

SQL-Anweisungen wie diese erzeugen eine doppelte oder verschachtelte Aggregation in SQL. Die meisten SQL-Dialekte können keine doppelte Aggregation oder verschachtelte Aggregationen ausführen. Daher löst ein solcher Versuch den Fehler aus.

Lösungen

Es gibt zwei mögliche Lösungen:

1. Verwenden Sie nicht aggregierte Maße, um nicht aggregierte SQL-Anweisungen zwischen Maßen auszuführen.
2. Verwenden Sie eine abgeleitete Tabelle, um Aggregationen zu verschachteln oder doppelt zu aggregieren.

Nicht zusammengefasste 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 Messwerten wird keine Aggregation durchgeführt. Eine doppelte oder verschachtelte Aggregation ist daher nicht möglich. Messwerte von type: number oder type: yesno dienen als Platzhalter, sodass darin auf andere Messwerte oder Kombinationen von Messwerten verwiesen werden kann.

Messwerte von type: number werden beispielsweise verwendet, um Berechnungen zwischen Messwerten auszuführen. Dabei kann jeder gültige SQL-Ausdruck verwendet werden, der zu einer Zahl oder einem Ganzzahlwert führt.

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

Was ist aber, wenn für die Durchführung einer Analyse eine verschachtelte Aggregation erforderlich ist? Was ist beispielsweise, wenn Sie den durchschnittlichen Betrag wissen möchten, den Kunden während ihrer gesamten Kundenbeziehung ausgeben („durchschnittlicher Lifetime-Wert“). Dazu sind zwei Ebenen – eine Verdoppelung oder Verschachtelung – von Aggregationen erforderlich, darunter:

1. Eine Summe der Verkäufe, nach Kunden gruppiert

2. Der Durchschnitt dieser Summe

Mit LookML können Sie Folgendes tun:

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

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

Dies führt jedoch zu einem Fehler, da mit dem Messwert avg_customer_lifetime_value eine Aggregation des Messwerts total_revenue durchgeführt wird, 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

In Looker wird dazu eine abgeleitete Tabelle erstellt, um den Messwert total_lifetime_value in ein aggregierbares Feld zu „flechten“. In Looker wird dies als Dimensionierung eines Messwerts bezeichnet. In einer abgeleiteten Tabelle wird das total_lifetime_value-Maß zu einer Dimension. Anschließend können Sie ein Messwert-Element vom Typ type: average erstellen, das 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 Fehler auftreten.