Agréger les données

Accueil » Ressources » Ici

L'agrégation de données consiste à combiner plusieurs lignes issues d'une ou plusieurs tables, à travers des fonctions spéciales appelées « agrégateurs ». Dès qu'il s'agit de faire une somme, d'obtenir une moyenne, une valeur médiane ou de compter des lignes, les calculs sont effectués grâce à des agrégateurs. L'agrégation permet également de combiner des données issues de différentes tables. Envision offre une syntaxe riche qui permet de mettre en œuvre tous ces scénarios. Dans le présent article, nous présentons et illustrons le fonctionnement des agrégateurs dans Envision.

Un script d'illustration

Une fois encore, commençons avec l'échantillon de données qui devrait être accessible à l'adresse /sample dans votre compte Lokad. Le script ci-dessous est modérément complexe et illustre certaines possibilités d'agrégation disponibles dans Envision. L'agrégation de données peut avoir lieu à l'intérieur et à l'extérieur des vignettes. Nous vous recommandons de tout d'abord lire Faire des calculs avec Envision, qui devrait vous aider à comprendre la suite.

read "/sample/Lokad_Items.tsv" as Items
read "/sample/Lokad_Orders.tsv" as O
read "/sample/Lokad_PurchaseOrders.tsv" as PO
expect O.Quantity : number

show label "Aggregating data" a1f1 tomato

oend := max(O.Date)
obegin := oend - 365

totalPurchased := sum(PO.NetAmount)
totalSold := sum(O.NetAmount) when date >= obegin
show table "Total purchased" a2c2 with
totalPurchased unit:"$"
show table "Sold over 1 year" d2f2 with
totalSold unit:"$"

VolumeSold = sum(O.NetAmount)
UnitSold = median(O.Quantity)
show table "Top sellers" a3f4 tomato with
  Name
  VolumeSold as "Sold" unit:"$"
  UnitSold as "Median"
  order by VolumeSold desc

avgRet := avg(distinct(O.Date) by O.Client)
avgQty := avg(sum(O.Quantity) by [O.Client, O.Date])
show table "Average client returns" a5c5 with round(avgRet)
show table "Average backet quantity" d5f5 with avgQty

O.Brand = Brand
show table "Top Suppliers" a6f7 tomato with
  Supplier
  distinct(Category) as "Categories"
  sum(O.NetAmount) as "Sold" unit:"$"
  mode(O.Brand) if (O.Brand != "Fellowes") as "Most frequent brand sold"
  group by Supplier
  order by sum(O.NetAmount) desc

Nous recommandons de copier-coller le script ci-dessus dans votre compte Lokad et de l'exécuter une fois afin de visualiser le tableau de bord qui en résulte. Si tout fonctionne correctement, vous devriez voir les mêmes éléments que ci-dessous.

Des scalaires pour les agrégations d'une seule valeur

Une variable qui n'est pas rattachée à un article en particulier (c’est-à-dire à une ligne de la table Items) est appelé une variable scalaire. Par analogie avec Excel, les variables Envision sont semblables aux colonnes Excel par défaut : ce sont des vecteurs qui contiennent plusieurs valeurs (une valeur par article). Mais il est également possible que des variable se comportent comme des cellules uniques d'Excel et ne contiennent qu'une seule valeur : il s'agit des variables scalaires. Le script ci-dessus illustre comment deux variables scalaires peuvent être calculées, les lignes concernées sont recopiées ci-dessous.

totalPurchased := sum(PO.NetAmount)
totalSold := sum(O.NetAmount) when date >= obegin
show table "Total purchased" a2c2 with
totalPurchased unit:"$"
show table "Sold over 1 year" d2f2 with
totalSold unit:"$"

Plusieurs aspects de ces quelques lignes de code peuvent être soulignés. Tout d'abord, les deux affectations en ligne 1 et 2 sont effectuées à l'aide de :=, l'opérateur d'affectation scalaire. Lorsque cet opérateur est utilisé à la place de l'opérateur d'affectation =, l'agrégation ne doit fournir qu'une seule valeur résultat, indépendante des articles. Ensuite, le nom des variables totalPurchased et tableSold commence par une minuscule. Le nom des variables n'est pas sensible à la casse dans Envision mais la façon dont le nom de ces variables est orthographié n'est pas une erreur. C'est une bonne pratique de programmation que nous recommandons : faire commencer le nom des variables scalaires par une minuscule. Le nom des tables et des colonnes doivent quant à eux commencer par une majuscule. En suivant ces règles, vous rédigerez un script plus facile à lire et à corriger. Enfin, l'agrégation scalaire se fait par défaut dans l'instruction show table si la table ne contient qu'une colonne. Par exemple, les lignes 1 et 3 peuvent être combinées en une instruction show table comme suit.

show table "Total purchased" a2c2 with
sum(PO.NetAmount) unit:"$"

Les variables scalaires numériques peuvent également être utilisées dans des calculs arbitraires comme n'importe quelle variable numérique dans Envision. Par exemple — même si cela n'aurait pas beaucoup de sens du point de vue d'une entreprise — le calcul suivant pourrait être ajouté à la fin du script Envision, une fois que les variables totalPurchased et totalSold ont été définies :

mySum := (totalSold - totalPurchased) / 2
show table "My Sum" with mySum

Agrégation à plusieurs valeurs

Il également possible d'effectuer des agrégations à plusieurs valeurs dans Envision, généralement en agrégeant les données d'une table spécifique et en projetant le résultat sur les lignes d'une autre table. Ainsi, l'agrégation est la méthode la plus utilisée pour combiner des données issues de différentes tables. L'un des cas d'utilisation les plus fréquents consiste à prendre une table qui contient des données historiques, c’est-à-dire une table également indexée sur sa colonne Date, et à l'agréger en un vecteur aligné avec la table Items (articles).

Le script présenté ci-dessus illustre ce fonctionnement avec une table qui affiche les produits les mieux vendus, c’est-à-dire les articles classés par ordre décroissant en fonction de leur volume de vente. Les lignes concernées sont copiées ci-dessous.

VolumeSold = sum(O.NetAmount)
UnitSold = median(O.Quantity)
show table "Top sellers" a3f4 tomato with
  Name
  VolumeSold as "Sold" unit:"$"
  UnitSold as "Median"
  order by VolumeSold desc

Les lignes 1 et 2 effectuent des agrégations de façon similaire aux agrégations scalaires que vous avons passées en revue précédemment. Cependant, l'affectation est effectuée avec le signe égal = et non l'opérateur := des affectations scalaires. Par conséquent, VolumeSold et UnitSold sont des vecteurs qui font partie de la table Items. Donc, ces variables sont similaires à des colonnes Excel et contiennent une valeur par article.

sum et median sont des fonctions spéciales appelées agrégateur dans Envision. Il y a d'autres agrégateurs dans Envision comme avg, min, max, first, last, etc. Pour plus de concision, nous ne détaillons pas tous ces agrégateurs dans le présent article mais, pour plus d'informations, vous pouvez consulter la liste complète des agrégateurs.

Le résultat de ces deux agrégations est affiché dans la table définie par les lignes 3 à 7. Les deux vecteurs VolumeSold et UnitSold sont listés en tant qu'arguments après le mot-clé with et leurs valeurs sont affichées dans la table. Enfin, à la ligne 7, l'instruction order by indique que la table doit être classée par ordre décroissant des valeurs VolumeSold.

Les lecteurs qui connaissent la syntaxe GROUP BY en SQL se demandent peut-être comment Envision sait quel groupement utiliser dans le calcul sum à la ligne 1. Par défaut, Envision effectue un regroupement en utilisant les colonnes qui jouent le rôle de clés à droite de l'instruction d'affectation. Dans le cas d'une variable qui appartient à la table Items, celle dont le nom est implicite, la colonne qui joue le rôle de clé primaire est la colonne Id. Ceci explique pourquoi une agrégation par article résulte de l'utilisation du signe =.

Groupes d'agrégation explicites avec by

Jusqu'ici les agrégations que nous avons effectuées reposaient sur l'agrégation implicite dans Envision. Mais le fonctionnement de tous les agrégateurs peut être modifié avec le mot-clé optionnel by qui est utilisé pour spécifier explicitement le groupement à utiliser. Voyons comment de mot-clé by est utilisé :

VolumeSold = sum(O.NetAmount)
SameVolumeSold = sum(O.NetAmount) by Id // Idem !

À la ligne 2, un second vecteur nommé SameVolumeSold est créé mais il contient les mêmes valeurs que celles du vecteur VolumeSold en ligne 1. L'option by Id est également utilisée en ligne 1 mais implicitement. De façon intuitive, lorsque l'option by est utilisée, c'est comme si des groupes étaient créés tout d'abord selon une cible de groupement puis comme si l'agrégateur était calculé pour chaque groupe. L'option by offre la possibilité de composer des agrégations relativement complexes, comme l'illustre le script présenté en début d'article. Passons en revue les deux lignes dans lesquelles des agrégations sont effectuées avec l'option by.

avgRet := avg(distinct(O.Date) by O.Client)
avgQty := avg(sum(O.Quantity) by [O.Client, O.Date])

L'agrégateur distinct compte le nombre de valeurs distinctes trouvées dans chaque groupe. À la ligne 1, les lignes de la table O (pour Orders) sont tout d'abord regroupées selon leurs valeurs Client respectives, puis, pour chaque client, le nombre de dates de commande distinctes est compté. Intuitivement, cette agrégation peut être interprétée comme la comptabilisation du nombre de fois qu'un client est revenu. Puis, ce résultat est ré-agrégé en une seule valeur scalaire avec l'agrégateur avg qui contient l'agrégation distinct en son sein.

La valeur scalaire avgQty peut être interprétée comme le nombre d'unités achetées par panier. Le calcul commence avec une instruction sum() by et, après l'option by, il n'y a pas une mais deux variables séparées par des virgules et listées entre crochets : [O.Client, O.Date]. Cette syntaxe doit être comprise comme suit : créer un groupe pour chaque paire Client - Date. D'un point de vue métier, nous considérons tous les articles achetés le même jour comme faisant partie du même panier, ce qui est une approximation raisonnable dans la plupart des cas. Enfin, l'appel externe à avg fournit la moyenne finale de toutes les sommes calculées pour chaque paire.

De façon plus générale, il est possible d'utiliser un nombre arbitraire de variables avec l'option by, à travers la syntaxe sum(foo) by [arg1, arg2, …, argN]. En pratique, pourtant, il est plutôt rare de rencontrer des situations dans lesquelles il est utile de regrouper autour de plus de 4 variables à la fois. De même, l'ordre des arguments n'a aucun impact sur les groupes formés et utilisés pour les calculs.

Agrégation explicite au sein d'une table avec group by

Parfois, à travers l'agrégation, il est possible de créer une nouvelle table qui est plus pertinente pour un tableau de bord que la table originale non-agrégée. Ainsi, Envision peut prendre en charge l'agrégation de données directement dans l'instruction de déclaration d'une vignette. Le moyen le plus direct de visualiser cette capacité Envision est d'agréger les données à afficher dans une table, ce qui est fait dans le script au début du présent article. Jetons un œil aux lignes concernées, copiées ci-dessous.

O.Brand = Brand
show table "Top Suppliers" a6f7 tomato with
  Supplier
  distinct(Category) as "Categories"
  sum(O.NetAmount) as "Sold" unit:"$"
  mode(O.Brand) if (O.Brand != "Fellowes") as "Most frequent brand sold"
  group by Supplier
  order by sum(O.NetAmount) desc

L'instruction show table va de ligne 2 à la ligne 8 et, plus spécifiquement, l'agrégation est spécifiée à la ligne 7 avec l'instruction group by, avec exactement la même sémantique que l'option by que nous avons expliquée plus haut. Vous vous demandez peut-être pourquoi Envision n'utilise pas le mot-clé by au lieu de group by si la sémantique est la même. Le if de la ligne 6 est un filtre d'agrégateur, présenté dans la section suivante.

La réponse est simple : il est possible d'utiliser l'option by dans la liste d'expressions utilisées après with pour une vignette donnée (cette situation n'est pas illustrée dans le présent document). Donc group by permet de différencier une instruction by qui fait partie d'une expression passée à la vignette d'une instruction group by qui s'applique à la vignette entière. En d'autres termes, group by s'applique à toutes les expressions listées après le mot-clé with, contrairement à l'instruction by qui n'a qu'un impact local.

Lorsque group by est utilisé, toutes les expressions passées à la vignette après le mot-clé with doivent pouvoir être agrégées. Par exemple, le script d'une ligne ci-dessous est incorrect parce que Name ne peut être agrégé alors que group by Supplier est spécifié.

show table "WRONG!" with Name group by Supplier

Mais, en le modifiant avec un agrégateur, distinct par exemple, alors le script devient valide.

show table "CORRECT!" with distinct(Name) group by Supplier

La seule exception à cette règle est l'agrégation de la cible elle-même. Dans le script présenté au début de cette section, il y a une instruction group by Supplier à la ligne 7. À la ligne 3, la variable Supplier est listée sans agrégateur, pourtant le script reste valide, parce que le regroupement est effectué selon la variable Supplier.

La nécessité d'utiliser un agrégateur s'applique à l'instruction order by à la ligne 8. En effet, la table est tout d'abord agrégée par fournisseur (supplier) puis classée selon la colonne Id — sauf si un autre ordre de classement est spécifié. Par conséquent, Envision doit calculer une valeur par groupe afin de classer tous ces groupes, c'est exactement ce qui se passe avec l'instruction group by sum(O.NetAmount).

Nous avons illustré group by avec la vignette table, mais cette syntaxe n'est pas spécifique à la vignette table et peut être utilisée avec la plupart des autres vignettes. Par exemple, il est possible d'étendre le premier script du présent article avec une vignette barchart agrégée par Brand :

show barchart "Sales by brand" with sum(O.NetAmount) group by Brand

group by permet également de faire des groupements multiples : regrouper par plusieurs vecteurs ou plusieurs expressions, avec une syntaxe similaire à celle de l'option by détaillée plus haut.

Filtres d'agrégation

Les agrégateurs Envision sont également compatibles avec des « filtres » via le mot clé if. Le script illustre l'utilisation d'un tel filtre à la ligne 6 :

O.Brand = Brand
show table "Top Suppliers" a6f7 tomato with
Supplier
distinct(Category) as "Categories"
sum(O.NetAmount) as "Sold" unit:"$"
mode(O.Brand) if (O.Brand != "Fellowes") as "Most frequent brand sold"
group by Supplier
order by sum(O.NetAmount) desc

Ici la marque Fellowes est explicitement exclue du rapport via le filtre if. Les filtres sont utiles au sein des instructions de vignette car ils offrent la possibilité de filtrer séparément chaque colonne. Au contraire, un filtre where en dehors des instructions de vignette porte sur toutes les lignes sélectionnées de la table O, qu'elle que soit la colonne.

Les filtres peuvent être utilisés en ligne :

TwoAndMore = sum(O.1) if (O.Quantity >= 2)