Errore: le misure con aggregazioni di Looker (somma, media, min, max, tipi di elenco) non possono fare riferimento ad altre misure

Durante lo sviluppo in un progetto, potresti visualizzare un errore come il seguente in un'esplorazione o nello strumento di convalida LookML:

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

Questo errore è causato da una misura aggregata che fa riferimento a un'altra aggregazione o da una misura di qualsiasi tipo nella sua definizione LookML, ad esempio:

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

Le istruzioni SQL come questa generano un'aggregazione doppia o nidificata in SQL: la maggior parte dei dialetti SQL non è in grado di eseguire l'aggregazione doppia o di nidificare le aggregazioni, quindi un simile tentativo attiva l'errore.

Soluzioni

Esistono due possibili soluzioni:

1. Utilizza misure non aggregate per eseguire istruzioni SQL non aggregate tra le misure.
2. Utilizza una tabella derivata per nidificare le aggregazioni o eseguire l'aggregazione doppia.

Utilizzo di misure non aggregate

Le misure non aggregate, ad esempio type: yesno e type: number, sono le uniche misure che possono fare riferimento ad altre misure o aggregazioni. Le misure non aggregate non eseguono alcuna aggregazione e, di conseguenza, non eseguono un'aggregazione doppia o nidificata. Le misure di type: number o type: yesno fungono da segnaposto in modo che sia possibile fare riferimento ad altre misure o combinazioni di misure al loro interno.

Ad esempio, le misure di type: number vengono utilizzate per eseguire calcoli tra le misure e utilizzano qualsiasi espressione SQL valida che restituisca un numero o un numero intero.

L'esempio seguente utilizza un type: number per calcolare la percentuale di tutti gli ordini annullati:

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) ;;
}

Utilizzo di una tabella derivata per aggregazioni doppie o nidificate

Ma cosa succede se per eseguire un'analisi è necessaria un'aggregazione nidificata? Ad esempio, cosa succede se devi conoscere l'importo medio speso dai clienti nel corso del loro lifetime ("lifetime value cliente medio")? Ciò richiede due livelli (raddoppio o nidificazione) di aggregazioni, tra cui:

1. Una somma delle vendite, raggruppate per cliente

2. Una media di questa somma

Per ottenere questo risultato con LookML, potresti provare a:

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

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

Tuttavia, si verificherà l'errore perché la misura avg_customer_lifetime_value esegue un'aggregazione sulla misura total_revenue, che è già un'aggregazione. Come discusso in precedenza, la maggior parte dei dialetti SQL attiverà un errore quando in una query vengono utilizzati aggregati doppi o nidificati.

Per ottenere una media della somma di total_revenue in SQL, è necessaria una sottoquery come la seguente:

  SELECT AVG(s.sum) FROM (SELECT SUM(x) as sum FROM ...) AS s

La soluzione equivalente in Looker è creare una tabella derivata da "appiattire" la misura total_lifetime_value in un campo che può essere aggregato. In Looker, questa operazione è chiamata dimensionamento di una misura. Con una tabella derivata, la misura total_lifetime_value diventa una dimensione. Puoi quindi creare una misura di type: average che fa riferimento alla dimensione customer_lifetime_value:

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} ;;
    }
}

Dopo aver unione la tabella derivata customer_facts in un'esplorazione, la misura average_customer_lifetime_value può essere utilizzata per eseguire l'analisi desiderata in un'esplorazione senza attivare alcun errore.