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 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:

1. Verwenden Sie nicht aggregierte Kennzahlen, um nicht aggregierende SQL-Anweisungen zwischen Kennzahlen auszuführen.
2. Verwenden Sie eine abgeleitete Tabelle, um Aggregationen oder doppelte Aggregationen zu verschachteln.

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. Maße von type: number oder type: yesno dienen als Platzhalter, sodass in ihnen 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 Tabelle 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. 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 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

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 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 verknüpft 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.