Keep learning with
LOKAD TV
/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) descNous 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.
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
Date
, et à l'agréger en un vecteur aligné avec la table Items
(articles). 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 descLes 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.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
.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 =
.by
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.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.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.group by
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) descL'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.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.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 SupplierMais, en le modifiant avec un agrégateur,
distinct
par exemple, alors le script devient valide.
show table "CORRECT!" with distinct(Name) group by SupplierLa 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
.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)
.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. 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
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.TwoAndMore = sum(O.1) if (O.Quantity >= 2)