Comprendre les agrégations symétriques

Les agrégations symétriques de Looker constituent une fonctionnalité très puissante. Cependant, comme les agrégations symétriques peuvent sembler un peu intimidantes et se produisent principalement en arrière-plan, il peut être un peu déroutant de les rencontrer. Cette page fournit les informations suivantes sur les agrégations symétriques:

Pourquoi les agrégations symétriques sont-elles nécessaires ?

SQL, le langage d’analyse de données, est extrêmement puissant. Mais un grand pouvoir implique de grandes responsabilités. Les analystes doivent donc éviter de calculer accidentellement des agrégats incorrects, tels que des sommes, des moyennes et des totaux.

Il est étonnamment facile d’effectuer ces calculs de manière incorrecte, et ces types de calculs incorrects peuvent être une source de grande frustration pour les analystes. L'exemple suivant montre comment vous pouvez vous tromper.

Imaginez que vous disposiez de 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 un à plusieurs. La relation est de type un à plusieurs, car une commande peut comporter de nombreux articles, mais chaque article ne peut faire partie que d'une seule commande. Consultez la page des bonnes pratiques Déterminer le bon 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

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

Supposons maintenant que vous joigniez les tables orders et order_items à l'aide de leur colonne partagée, order_id. Vous obtenez le tableau suivant :

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 que deux articles ont été commandés le 1er décembre (2017-12-01 dans la colonne order_date) et quatre articles le 2 décembre (2017-12-02). Certains des calculs précédents, tels que les calculs SUM(quantity), restent valides. Toutefois, vous rencontrerez un problème si vous essayez de calculer le montant total dépensé.

Si vous utilisez le calcul précédent, SUM(total), la valeur totale 50.36 du nouveau tableau pour les lignes où la valeur de order_id est 1 sera comptée deux fois, car la commande inclut 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 inclut trois articles différents. Par conséquent, le résultat du calcul SUM(total) pour ce tableau est 223.44 au lieu de la réponse correcte, qui est 124.84.

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

Fonctionnement des agrégations symétriques

Les agrégations symétriques empêchent les analystes (et toute autre personne qui utilise Looker) de calculer accidentellement des agrégations telles que des sommes, des moyennes et des totaux. Les agrégations symétriques permettent de soulager les analystes, car ils peuvent être sûrs que les utilisateurs ne vont pas avancer avec des données incorrectes. Pour ce faire, les agrégations symétriques s'assurent de compter chaque fait dans le calcul le bon nombre de fois et de suivre 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 définie par l'analyste 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 où la valeur de order_id est 1, il ne doit pas comptabiliser le total deux fois, car ce total a déjà été inclus dans le calcul. Il ne doit comptabiliser le total qu'une seule fois pour les trois lignes où 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 spécifiée dans le modèle. Donc, si les résultats vous semblent incorrects, parlez-en à un analyste pour vous assurer que tout est correctement configuré.

Pourquoi les agrégations symétriques semblent-elles complexes ?

L'apparence des agrégations symétriques peut être un peu mystérieuse. Sans agrégations symétriques, Looker écrit généralement du code SQL propre et respectueux, 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 que Looker écrit, mais tous les formats font la même chose 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 peu connues SUM DISTINCT et AVG DISTINCT, qui font partie du standard SQL.

Pour voir comment cela se produit, vous pouvez prendre le calcul que vous avez effectué précédemment et le corriger avec des agrégations symétriques. Parmi les sept colonnes des tables jointes, vous n'avez besoin que de deux: 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, dont l'unicité est garantie et donnera toujours la même sortie pour la même entrée. Pour ce faire, il s'agit généralement d'une fonction de hachage dont les détails dépassent le cadre de cette page. Le résultat devrait 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 effectue les opérations suivantes: 

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

Vous obtenez ainsi des totaux correctement agrégés, en comptant chaque total exactement le bon nombre de fois. La fonction d'agrégation symétrique Looker n'est pas induite en erreur 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 avoir une meilleure idée du fonctionnement des agrégations symétriques.

Le code SQL requis pour ce faire n'est pas le plus agréable à regarder : avec CAST(), md5(), SUM(DISTINCT) et STRTOL(), vous ne voudriez certainement pas écrire le code SQL à la main. Heureusement, ce n'est pas obligatoire : Looker peut écrire le code SQL à votre place.

Lorsque l'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 engendrent des coûts de performance, la capacité de Looker à discerner quand utiliser et quand ne pas les utiliser optimise davantage le code SQL généré par Looker et le rend aussi efficace que possible, tout en garantissant la bonne réponse.