Comprendre les agrégations symétriques

Les agrégations symétriques de Looker constituent une fonctionnalité très puissante. Cependant, les agrégations symétriques peuvent sembler un peu intimidantes et se déroulent le plus souvent en arrière-plan. Leur présence peut donc être un peu déroutante. Cette page fournit les informations suivantes sur les agrégations symétriques:

Pourquoi les agrégations symétriques sont nécessaires

SQL, le langage d’analyse de données, est extrêmement puissant. Mais avec un grand pouvoir s'accompagne de grandes responsabilités, et les analystes ont la responsabilité d'éviter de calculer accidentellement des agrégats incorrects, tels que des sommes, des moyennes et des décomptes.

Il est étonnamment facile d'effectuer ces calculs de manière incorrecte, et ces types de calculs incorrects peuvent être une grande frustration pour les analystes. L'exemple suivant montre comment résoudre les problèmes.

Imaginons que vous ayez deux tables : orders et order_items. La table order_items enregistre une ligne pour chaque élément d'une commande. La relation entre les tables est donc de type un à plusieurs. La relation est de type un à plusieurs, car une commande peut comporter plusieurs articles, mais chaque article ne peut faire partie que d'une seule commande. Consultez la page Optimiser le paramètre de relation pour savoir comment déterminer la relation appropriée pour une jointure.

Dans cet exemple, supposons que la table orders se présente comme suit:

order_id user_id total order_date
1 100 50,36 $ 2017-12-01
2 101 24,12 $ 2017-12-02
3 137 50,36 $ 2017-12-02

Dans cette table orders, la somme des valeurs de la colonne total (SUM(total)) est égale à 124.84.

Supposons que la table order_items contienne six lignes:

order_id item_id quantity unit_price
1 50 1 23,00 €
1 63 2 13,68 $
2 63 1 13,68 $
2 72 1 5,08 €
2 79 1 5,36 €
3 78 1 50,36 $

Obtenir le nombre d'articles commandés est facile. La somme des valeurs de la colonne quantity (SUM(quantity)) est 7.

Supposons maintenant que vous rejoigniez les tables orders et order_items à l'aide de la colonne partagée order_id. Le tableau suivant s'affiche:

order_id user_id total order_date item_id quantity unit_price
1 100 50,36 $ 2017-12-01 50 1 23,00 €
1 100 50,36 $ 2017-12-01 63 2 13,68 $
2 101 24,12 $ 2017-12-02 63 1 13,68 $
2 101 24,12 $ 2017-12-02 72 1 5,08 €
2 101 24,12 $ 2017-12-02 79 1 5,36 €
3 137 50,36 $ 2017-12-02 78 1 50,36 $

Le tableau précédent fournit de nouvelles informations. Par exemple, deux articles ont été commandés le 1er décembre (2017-12-01 dans la colonne order_date) et quatre articles ont été commandés le 2 décembre (2017-12-02). Certains des calculs précédents, comme les calculs de SUM(quantity), sont toujours valides. Cependant, vous rencontrerez un problème si vous essayez de calculer le total dépensé.

Si vous utilisez le calcul précédent, SUM(total), la valeur totale 50.36 dans le nouveau tableau pour les lignes dont la valeur de order_id est 1 sera comptée deux fois, car la commande comprend deux articles différents (avec des valeurs item_id de 50 et 63). Le total de 24.12 pour les lignes où order_id est 2 sera compté trois fois, car cette commande comprend trois articles différents. Par conséquent, le résultat du calcul SUM(total) pour cette table est 223.44 au lieu de la bonne réponse, qui est 124.84.

Bien qu'il soit facile d'éviter ce type d'erreur lorsque vous travaillez avec deux petits exemples de tableaux, la résolution de ce problème serait beaucoup plus compliquée dans la vie réelle, avec beaucoup de tableaux et de données. C'est exactement le genre d'erreur que quelqu'un pourrait faire sans même s'en rendre compte. C'est ce problème que les agrégations symétriques permettent de résoudre.

Fonctionnement des agrégations symétriques

Les agrégations symétriques empêchent les analystes, et toute autre personne utilisant Looker, de calculer accidentellement des agrégations telles que des sommes, des moyennes et des nombres. Les agrégations symétriques aident à alléger la charge pesant sur les épaules des analystes, car les analystes peuvent croire que les utilisateurs ne vont pas avancer avec des données incorrectes. Pour ce faire, les agrégations symétriques veillent à compter chaque fait dans le calcul le nombre correct de fois et assurent le suivi de ce que vous calculez.

Dans l'exemple précédent, la fonction d'agrégation symétrique reconnaît que total est une propriété de orders (et non de order_items). Elle ne doit donc compter le total de chaque commande qu'une seule fois pour obtenir la bonne réponse. Pour ce faire, la fonction utilise une clé primaire unique que l'analyste a définie dans Looker. Cela signifie que lorsque Looker effectue des calculs sur la table jointe, il reconnaît que même s'il existe deux lignes pour lesquelles la valeur de order_id est 1, il ne doit pas compter le total deux fois, car ce total a déjà été inclus dans le calcul, et qu'il ne doit compter le total qu'une seule fois pour les trois lignes pour lesquelles la valeur de order_id est 2.

Notez que les agrégations symétriques dépendent d'une clé primaire unique et de la relation de jointure correcte dans le modèle. Donc, si les résultats vous semblent erronés, parlez-en à un analyste pour vous assurer que tout est correctement configuré.

Pourquoi les agrégations symétriques semblent compliquées

L'apparence des agrégations symétriques peut être un peu mystérieuse. Sans agrégations symétriques, Looker écrit généralement un langage SQL bien fait, comme dans l'exemple suivant: 

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price"
FROM order_items AS order_items

GROUP BY 1,2
ORDER BY 1
LIMIT 500

Avec les agrégations symétriques, les écritures SQL Looker peuvent ressembler à l'exemple suivant: 

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price",
  (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(users.age ,0)
  *(1000000*1.0)) AS DECIMAL(38,0))) +
  CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0)) )
  - SUM(DISTINCT CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))) ) AS DOUBLE PRECISION)
  / CAST((1000000*1.0) AS DOUBLE PRECISION), 0)
  / NULLIF(COUNT(DISTINCT CASE WHEN users.age IS NOT NULL THEN users.id
  ELSE NULL END), 0)) AS "users.average_age
FROM order_items AS order_items
LEFT JOIN users AS users ON order_items.user_id = users.id

GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

Le format exact des agrégations symétriques dépend du dialecte SQL écrit par Looker, mais tous les formats ont le même principe de base: si plusieurs lignes ont la même clé primaire, la fonction d'agrégation symétrique ne les comptabilise qu'une seule fois. Pour ce faire, il utilise les fonctions SUM DISTINCT et AVG DISTINCT peu connues qui font partie de la norme SQL.

Pour voir comment cela se produit, vous pouvez prendre le calcul que vous avez effectué précédemment et le travailler avec des agrégations symétriques. Parmi les sept colonnes des tables jointes, vous n'avez besoin que de deux colonnes: celle que vous agrégez (total) et la clé primaire unique pour les commandes (order_id).

order_id total
1 50,36 $
1 50,36 $
2 24,12 $
2 24,12 $
2 24,12 $
3 50,26 $

Les agrégations symétriques prennent la clé primaire (order_id, dans ce cas) et créent un très grand nombre pour chacune d'elles. Ce nombre est alors unique, et la même sortie est garantie pour la même entrée. Pour ce faire, elle utilise généralement une fonction de hachage, dont les détails n'entrent pas dans le cadre de cette page. Le résultat doit ressembler à ceci:

big_unique_number total
802959190063912 50,36 $
802959190063912 50,36 $
917651724816292 24,12 $
917651724816292 24,12 $
917651724816292 24,12 $
110506994770727 50,36 $

Ensuite, pour chaque ligne, Looker procède comme suit: 

SUM(DISTINCT big_unique_number + total) - SUM(DISTINCT big_unique_number)

Vous obtenez ainsi de manière fiable les totaux correctement agrégés, en comptant chaque total exactement le bon nombre de fois. La fonction d'agrégation symétrique de Looker n'est pas trompée par des lignes répétées ou par plusieurs commandes ayant le même total. Vous pouvez essayer de faire le calcul vous-même pour vous faire une meilleure idée du fonctionnement des agrégations symétriques.

Le code SQL requis pour effectuer cette opération n'est pas le plus beau à regarder: avec CAST(), md5(), SUM(DISTINCT) et STRTOL(), vous ne voudriez certainement pas écrire le code SQL manuellement. Mais heureusement, ce n'est pas nécessaire : Looker peut écrire le code SQL pour vous.

Lorsqu'une agrégation fonctionne correctement sans avoir besoin d'agrégations symétriques, Looker le détecte automatiquement et n'utilise pas la fonction. Étant donné que les agrégations symétriques entraînent certains coûts de performance, la capacité de Looker à déterminer quand utiliser ou non les agrégations symétriques optimise encore davantage le code SQL généré par Looker et le rend aussi efficace que possible tout en garantissant la bonne réponse.