Fehler: Messungen mit Looker-Aggregationen (Summe, Durchschnitt, Min, Max, Listentypen) dürfen nicht auf andere Messwerte verweisen

Während der Entwicklung in einem Projekt kann in einem Explore oder im LookML-Validator ein Fehler wie der folgende angezeigt werden:

  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 Messung eines beliebigen Typs in der LookML-Definition verweist. Beispiele:

    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 verdoppeln oder verschachteln, sodass ein solcher Versuch den Fehler auslöst.

Lösungen

Es gibt zwei mögliche Lösungen:

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

Nicht zusammengefasste Messwerte verwenden

Nicht zusammengefasste 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 auch keine doppelte oder verschachtelte Aggregation. Messungen für type: number oder type: yesno fungieren als Platzhalter, sodass darin auf andere Messwerte oder Kombinationen von Messungen verwiesen werden kann.

Messwerte für type: number werden beispielsweise verwendet, um Berechnungen zwischen Messungen durchzuführen und jeden gültigen SQL-Ausdruck zu übernehmen, der zu einer Zahl oder einer Ganzzahl führt.

Im folgenden Beispiel wird mit 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 Tabelle für doppelte oder verschachtelte Aggregationen verwenden

Aber was ist, wenn eine verschachtelte Aggregation für die Durchführung einer Analyse erforderlich ist? Angenommen, Sie möchten den durchschnittlichen Betrag ermitteln, den Kunden während der Customer Lifetime im Durchschnitt ausgeben (durchschnittlicher Customer Lifetime Value). Dies erfordert zwei Ebenen – eine Verdoppelung oder Verschachtelung – von Aggregationen, darunter:

1. Summe der Verkäufe, gruppiert nach Kunden

2. Ein Durchschnitt dieser Summe

Um dies mit LookML zu erreichen, könnten Sie 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 zusammengefasst ist. Wie bereits erwähnt, lösen die meisten SQL-Dialekte einen Fehler aus, wenn doppelte oder verschachtelte Aggregatfunktionen in einer Abfrage verwendet werden.

Um einen Durchschnitt der total_revenue-Summe in SQL zu erreichen, 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 den Messwert total_lifetime_value in ein Feld zu „vereinfachen“, das aggregiert werden kann. In Looker wird dies als Dimensionisierung eines Messwerts bezeichnet. Bei abgeleiteten Tabellen wird der Messwert total_lifetime_value zu einer Dimension. Sie können dann eine Messung von type: average erstellen, die 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} ;;
    }
}

Nachdem die abgeleitete Tabelle customer_facts mit einem Explore verbunden wurde, kann der Messwert average_customer_lifetime_value verwendet werden, um die gewünschte Analyse in einem Explore durchzuführen, ohne einen Fehler auszulösen.