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 misura di qualsiasi tipo nella definizione di LookML, ad esempio:

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

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 nidificare le aggregazioni, pertanto questo tentativo attiva l'errore.

Soluzioni

Esistono due possibili soluzioni:

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

Utilizzo di misure non aggregate

Le metriche non aggregate, come type: yesno e type: number, sono le uniche che possono fare riferimento ad altre metriche 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, pertanto al loro interno è possibile fare riferimento ad altre misure o combinazioni di misure.

Ad esempio, le misure di type: number vengono utilizzate per eseguire calcoli tra le misure e accettare qualsiasi espressione SQL valida che generi 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 di aggregazioni, ovvero un raddoppio o un nidificazione, tra cui:

1. Una somma delle vendite, raggruppata per cliente

2. Una media di questa somma

Per ottenere questo risultato con LookML, puoi 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 attiva 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 consiste nel creare una tabella derivata per "appiattire" la misura total_lifetime_value in un campo che può essere aggregato. In Looker, questa operazione è chiamata definizione delle dimensioni 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} ;;
    }
}

Una volta unita la tabella derivata customer_facts a un'esplorazione, la misura average_customer_lifetime_value può essere utilizzata per eseguire l'analisi desiderata in un'esplorazione senza attivare alcun errore.