Erreur : Les mesures avec des agrégations Looker (somme, moyenne, min, max, types de listes) ne peuvent pas faire référence à d'autres mesures

Lors du développement dans un projet, une erreur semblable à la suivante peut s'afficher dans une exploration ou dans l'outil de validation LookML:

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

Cette erreur est causée par une mesure agrégée qui fait référence à une autre agrégation ou une autre mesure de n'importe quel type dans sa définition LookML, par exemple:

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

Les instructions SQL de ce type génèrent une agrégation double ou imbriquée en SQL. La plupart des dialectes SQL ne sont pas en mesure de double agréger ou d'imbriquer des agrégations, donc une telle tentative déclenche l'erreur.

Solutions

Il existe deux solutions :

1. Utilisez des mesures non agrégées pour effectuer des instructions SQL non agrégées entre les mesures.
2. Utilisez une table dérivée pour imbriquer des agrégations ou effectuer une double agrégation.

Utiliser des mesures non agrégées

Les mesures non agrégées (comme type: yesno et type: number) sont les seules mesures pouvant faire référence à d'autres mesures ou agrégations. Les mesures non agrégées n'effectuent aucune agrégation et ne peuvent donc pas effectuer d'agrégation double ou imbriquée. Les mesures de type: number ou type: yesno servent de repères afin que d'autres mesures ou combinaisons de mesures puissent y être référencées.

Par exemple, les mesures de type: number permettent d'effectuer des calculs entre les mesures et de prendre n'importe quelle expression SQL valide qui donne un nombre ou un entier.

L'exemple suivant utilise un type: number pour calculer le pourcentage de toutes les commandes annulées :

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

Utiliser une table dérivée pour les agrégations doubles ou imbriquées

Mais que se passe-t-il si une agrégation imbriquée est nécessaire pour effectuer une analyse ? Par exemple, que se passe-t-il si vous avez besoin de connaître le montant moyen dépensé par les clients au cours de leur durée de vie (valeur vie client moyenne) ? Pour ce faire, vous devez utiliser deux niveaux d'agrégation (doublement ou imbrication), y compris :

1. Somme des ventes, regroupées par client

2. La moyenne de cette somme

Pour y parvenir avec LookML, vous pouvez essayer:

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

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

Toutefois, cela déclenche l'erreur, car la mesure avg_customer_lifetime_value effectue une agrégation sur la mesure total_revenue, qui est déjà une agrégation. Comme mentionné précédemment, la plupart des dialectes SQL déclenchent une erreur lorsque des agrégations doubles ou imbriquées sont utilisées dans une requête.

Pour obtenir une moyenne de la somme total_revenue en SQL, une sous-requête comme celle-ci est nécessaire:

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

La solution équivalente dans Looker consiste à créer une table dérivée pour "aplatir" la mesure total_lifetime_value dans un champ pouvant être agrégé. Dans Looker, on parle de dimensionnalisation d'une mesure. Avec une table dérivée, la mesure total_lifetime_value devient une dimension. Vous pouvez ensuite créer une mesure de type: average qui fait référence à la dimension 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} ;;
    }
}

Une fois la table dérivée customer_facts jointe à une exploration, la mesure average_customer_lifetime_value peut être utilisée pour effectuer l'analyse souhaitée dans une exploration sans déclencher d'erreur.