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

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 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 Kennzahlen, um nicht aggregierende SQL-Anweisungen zwischen Kennzahlen auszuführen.
2. 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 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.

Beispielsweise werden die Messwerte type: number verwendet, um Berechnungen zwischen Messwerten und jeden gültigen SQL-Ausdruck durchzuführen, der eine Zahl oder eine Ganzzahl ergibt.

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 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“)? Dies erfordert zwei Ebenen – eine Verdoppelung oder Verschachtelung – von Aggregationen, einschließlich:

1. Summe der Verkäufe, gruppiert nach Kunde

2. Der 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 einen Durchschnitt der Summe total_revenue in SQL zu ermitteln, 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 aus dem Messwert total_lifetime_value eine 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 Fehler auftreten.