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 d'un projet, une erreur semblable à celle-ci 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 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 double agrégation ou une agrégation imbriquée en SQL. La plupart des dialectes SQL ne sont pas en mesure d'effectuer une double agrégation ni d'imbriquer des agrégations. Une telle tentative déclenche donc l'erreur.

Solutions

Il existe deux solutions :

1. Utilisez des mesures non agrégées pour exécuter 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 permettent donc pas d'effectuer une double agrégation ni une agrégation 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 sont utilisées pour effectuer des calculs entre les mesures et 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 faire si vous devez connaître le montant moyen que les clients dépensent 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 ce faire 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 indiqué 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 la moyenne de la somme total_revenue en SQL, vous avez besoin d'une sous-requête telle que la suivante:

  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 associée à 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.