Aggregare i dati con Envision - Software di ottimizzazione delle scorte

Aggregare i dati con Envision












Home » Risorse » Qui

Aggregare i dati significa combinare le righe di più tabelle attraverso speciali funzioni chiamate aggregatori, che eseguono calcoli di somme, medie, mediane e numero di righe. L'aggregazione consente anche di combinare dati provenienti da diverse tabelle. E la ricca sintassi di Envision ti permette di gestire tutte queste situazioni. Vediamo come, nel tutorial in questa pagina.

Lo script: un esempio

Ripartiamo dal nostro set di dati campione, accessibile dal percorso /sample nel tuo account Lokad. Lo script qui sotto è mediamente complesso e illustra alcuni degli schemi di aggregazione dati disponibili in Envision. L'aggregazione dei dati può essere eseguita sia all'interno che all'esterno delle caselle. Prima di continuare col nostro tutorial, ti consigliamo di leggere la pagina Eseguire calcoli con Envision, indispensabile per capire il contenuto di questa pagina.
read "/sample/Lokad_Items.tsv" as Items
read "/sample/Lokad_Orders.tsv" as Orders
read "/sample/Lokad_PurchaseOrders.tsv" as PO
expect Orders.Quantity : number

show label "Filtering data with Envision" a1f1 tomato

oend := max(Orders.Date)
currency := mode(Orders.Currency)

totalPurchased := sum(PO.NetAmount)
totalSold := sum(Orders.NetAmount) when date >= oend - 365
show table "Total purchased{$}" a2c2 with totalPurchased
show table "Sold over 1 year{$}" d2f2 with totalSold

VolumeSold = sum(Orders.NetAmount)
UnitSold = median(Orders.Quantity)
show table "Top sellers" a3f4 tomato with 
  Name, 
  VolumeSold as "Sold{$}", 
  UnitSold as "Median" 
  order by VolumeSold desc

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

Orders.Brand = Brand
show table "Top Suppliers" a6f7 tomato with 
  Supplier, 
  distinct(Category) as "Categories", 
  sum(Orders.NetAmount) as "Sold{$}", 
  mode(Orders.Brand) if (Orders.Brand != "Fellowes") as "Most frequent brand sold" 
  group by Supplier 
  order by sum(Orders.NetAmount) desc
Copia e incolla lo script qui sopra nel tuo account Lokad, poi esegui lo script. Dovresti visualizzare un pannello di controllo come quello qui sotto.

Image

Scalari per le aggregazioni a un solo valore

Una variabile che non ha affinità con nessuno degli articoli è definita in Envision “variabile scalare” (a indicare che la variabile non è associata ad alcuna riga della tabella Items). Tenendo a mente l'analogia con Excel, le variabili Envision sono assimilabili alle colonne di Excel: le variabili sono, infatti, vettori che riuniscono più valori insieme (in realtà, un valore per articolo). Tuttavia, possiamo anche avere variabili che si comportano come una singola cella di Excel, con un solo valore: queste sono le variabili scalari. Lo script qui sopra mostra come calcolare due variabili scalari. Per maggiore chiarezza, riproponiamo qui sotto le righe di script corrispondenti.
totalPurchased := sum(PO.NetAmount)
totalSold := sum(Orders.NetAmount) when date >= oend - 365
show table "Total purchased{$}" a2c2 with totalPurchased
show table "Sold over 1 year{$}" d2f2 with totalSold
A proposito di queste righe possiamo fare diverse osservazioni. In primo luogo, i due assegnamenti alle righe 1 e 2 ricorrono a :=, l'operatore di assegnamento scalare. Se usiamo l'operatore := al posto dell'operatore di assegnamento =, l'aggregazione dà come risultato un singolo valore indipendente dagli articoli. In secondo luogo, i nomi delle variabili totalPurchased (totale Acquistati) e tableSold (tabella Venduti) iniziano con una lettera minuscola. Per i nomi delle variabili, Envision non distingue le lettere maiuscole dalle minuscole; in questo caso, però, la scelta della minuscola non è casuale. La prassi che seguiamo, infatti, è di mantenere l'iniziale minuscola per le variabili che contengono valori scalari e di assegnare una lettera maiuscola ai nomi di tabelle e colonne (ti consigliamo di seguire queste regole per rendere lo script più leggibile e facile da sistemare in caso di problemi). In terzo luogo, l'aggregazione scalare avviene di default con l'istruzione show table (mostra tabella) se la tabella ha una sola colonna. Ad esempio, le righe 1 e 3 possono essere combinate in una sola riga, come nell'esempio qui sotto.
show table "Total purchased{$}" a2c2 with sum(PurchaseOrders.NetAmount)
Le variabili scalari numeriche possono anche essere usate per eseguire calcoli arbitrari, come una qualsiasi variabile numerica in Envision. Ad esempio (anche se non ha molto senso per un'attività commerciale), dopo aver definito le variabili totalPurchased e totalSold possiamo aggiungere questo calcolo alla fine del nostro script Envision:
mySum := (totalSold - totalPurchased) / 2
show table "My Sum" with mySum

Aggregazioni a più valori

Envision supporta anche aggregazioni a più valori, aggregando i dati di una specifica tabella e proiettando i risultati sulle righe di un'altra tabella. Questo tipo di aggregazione è il sistema più comune per combinare dati di diverse tabelle. Più precisamente, l'approccio più frequente consiste nel partire da una tabella storica (che contiene cioè una colonna Date, data) e aggregare la tabella in un vettore allineato con la tabella degli articoli, Items.

Il primo script di questa pagina illustra il procedimento con una tabella di top seller, ossia di articoli ordinati secondo un volume di vendite decrescente. Per maggiore chiarezza, riproponiamo qui sotto le righe che ci interessano.
VolumeSold = sum(Orders.NetAmount)
UnitSold = median(Orders.Quantity)
show table "Top sellers" a3f4 tomato with 
  Name, 
  VolumeSold as "Sold{$}", 
  UnitSold as "Median" 
  order by VolumeSold desc
Le righe 1 e 2 eseguono le aggregazioni in un modo simile a quello che abbiamo appena visto per le aggregazioni scalari. Avrai notato, però, che gli assegnamenti hanno il segno uguale = invece che l'operatore scalare :=. Sia VolumeSold (volume venduto) che UnitSold (unità vendute) sono vettori che fanno parte della tabella Items. Di conseguenza, queste variabili risultano simili alle colonne di Excel e contengono un valore per articolo.

Sia sum (somma) che median (mediana) sono speciali funzioni di Envision, chiamate aggregatori. Envision supporta anche altri aggregatori, tra cui avg (media), min (valore minimo), max (valore massimo), first (primo valore), last (ultimo valore), etc. Puoi trovare una lista dettagliata degli aggregatori alla pagina dedicata.

Il risultato delle due aggregazioni è mostrato nella tabella definita nelle righe dalla 3 alla 7. I vettori VolumeSold e UnitSold sono elencati come argomento dopo la parola chiave with e i valori corrispondenti sono inseriti nella tabella. Alla riga 7, l'istruzione order by indica che la tabella deve essere ordinata a partire dagli articoli con il più alto valore di VolumeSold.

Se hai familiarità con SQL e con la sintassi GROUP BY, probabilmente ti starai chiedendo come fa Envision a sapere quale tipo di raggruppamento utilizzare nel calcolo della somma (sum) alla riga 1. Per impostazione predefinita, Envision effettua il raggruppamento usando le colonne che fungono da “chiave” a destra dell'assegnamento. Nel caso di una variabile che appartiene alla tabella Items (il cui nome, lo ricordiamo, resta implicito), la colonna che agisce da chiave (principale) è la colonna Id. Ecco perché usando il segno = si ottiene un'aggregazione per articolo.

Gruppi espliciti di aggregazione con by

Le aggregazioni che abbiamo visto finora sono tutte basate sugli schemi di aggregazione implicita di Envision. Possiamo, però, modificare il comportamento di un qualsiasi aggregatore con la parola chiave by, per specificare esplicitamente il raggruppamento applicabile. Vediamo come, con l'aiuto dell'esempio qui sotto:
VolumeSold = sum(Orders.NetAmount)
SameVolumeSold = sum(Orders.NetAmount) by Id // Same!
La riga 2 è assegnata a un secondo vettore, SameVolumeSold (stesso volume venduto), ma i valori di questo vettore sono praticamente identici a quelli del vettore VolumeSold alla riga 1. Infatti, alla riga 1 abbiamo usato, sebbene implicitamente, l'opzione by Id. Intuitivamente, quando è usata l'opzione by, è come se i gruppi fossero creati secondo il raggruppamento prescelto e se l'aggregatore venisse calcolato separatamente per ogni gruppo. L'opzione by offre la possibilità di creare aggregazioni piuttosto complesse, come quella nello script all'inizio della pagina. Rivediamo le righe di script con l'opzione by.
avgRet := avg(distinct(Orders.Date) by Orders.Client)
avgQty := avg(sum(Orders.Quantity) by [Orders.Client, Orders.Date])
L'aggregatore distinct conta il numero di valori distinti osservati all'interno di ogni gruppo. Alla riga 1, le righe della tabella Orders sono raggruppate secondo il valore Client (cliente) corrispondente; poi, per ogni cliente, viene contato il numero di date di ordine. Intuitivamente, questo tipo di aggregazione può essere visto come un sistema per contare il numero di volte che ogni cliente è tornato a servirsi da noi. Il risultato del conteggio è riaggregato in un valore scalare con l'aggregatore avg, che contiene l'aggregazione distinct al suo interno.

Lo scalare avgQty può essere considerato come il numero di unità acquistate per cestino. Il calcolo inizia con l'istruzione sum() by; dopo l'opzione by, però, non abbiamo una, bensì due variabili separate da una virgola e tra parentesi: [Orders.Client, Orders.Date ] (ordini cliente, ordini data). La sintassi crea quindi un gruppo per ogni coppia di Client e Date. Dal punto di vista commerciale, consideriamo tutte le unità acquistate lo stesso giorno come parte di uno stesso cestino, approssimazione ragionevole per gran parte delle situazioni. Infine, la chiamata esterna a avg produce la media finale di tutte le somme calcolate per tutte le coppie.

Più in generale, l'opzione by supporta un numero arbitrario di variabili che seguono la sintassi sum(foo) by [arg1, arg2, …, argN]. Nella pratica, le situazioni in cui ha senso raggruppare più di 4 variabili alla volta sono piuttosto rare. Peraltro, l'ordine degli argomenti non incide affatto sulla creazione e sul calcolo degli aggregati.

Aggregazione esplicita all'interno di una tabella con group by

A volte, è possibile utilizzare l'aggregazione per produrre una nuova tabella, molto più pratica da usare nel pannello di controllo rispetto alla tabella originale non aggregata. Envision offre quindi la possibilità di aggregare i dati direttamente dall'istruzione di dichiarazione di una casella. Il modo più diretto per farlo è aggregare i dati che dovranno essere mostrati in una tabella, come abbiamo fatto nello script all'inizio di questa pagina. Rivediamo le righe di script che ci interessano.
Orders.Brand = Brand
show table "Top Suppliers" a6f7 tomato with 
  Supplier, 
  distinct(Category) as "Categories", 
  sum(Orders.NetAmount) as "Sold{$}", 
  mode(Orders.Brand) if (Orders.Brand != "Fellowes") as "Most frequent brand sold" 
  group by Supplier 
  order by sum(Orders.NetAmount) desc
L'istruzione show table va dalla riga 2 alla riga 8. Più in particolare, l'aggregazione viene specificata alla riga 7 con l'istruzione group by, che ha la stessa semantica dell'opzione by che abbiamo appena visto. A questo punto, sorge spontanea la domanda: perché non usare by invece di group by se la semantica è la stessa? (if alla riga 6 è un filtro per l'aggregazione che vedremo nella prossima sezione in dettaglio).

La risposta è semplice: è possibile usare l'opzione by all'interno della lista di espressioni usate dopo with per una data casella (non è però la situazione che mostra lo script). group by consente di distinguere da un lato un'istruzione by parte di un'espressione passata alla casella, dall'altro un'istruzione group by applicabile all'intera casella. In altre parole, group by è applicabile a tutte le espressioni elencate dopo with, mentre by ha un campo d'azione più ristretto.

Quando usiamo group by, possiamo aggregare tutte le espressioni passate alla casella dopo la parola chiave with. Ad esempio, lo script qui sotto non è corretto, poiché abbiamo specificato group by Supplier (raggruppa per fornitore), ma Name (nome) non può essere aggregato.
show table "WRONG!" with Name group by Supplier
Se modifichiamo lo script introducendo un aggregatore, come distinct, lo script Envision diventa valido.
show table "CORRECT!" with distinct(Name) group by Supplier
L'unica eccezione a questa regola è l'aggregazione del nostro obiettivo. Nello script all'inizio di questa sezione, abbiamo un'istruzione group by Supplier alla riga 7. Alla riga 3, la variabile Supplier (fornitore) è elencata senza alcun aggregatore, ma lo script è comunque valido, proprio perché il raggruppamento viene fatto in base alla variabile Supplier.

È necessario introdurre un aggregatore anche alla riga 8, per l'istruzione order by. La tabella, infatti, è aggregata per fornitore e solo successivamente ordinata secondo la colonna Id (a meno che non sia specificato un altro tipo di ordine). Per ordinare tutti i gruppi, Envision deve quindi calcolare un valore per gruppo. Questo è esattamente ciò che succede con l'istruzione group by sum(Orders.NetAmount).

Nel nostro caso specifico, abbiamo visto l'istruzione group by applicata a una casella di tipo table, ma possiamo usare lo stesso schema anche con altri tipi di casella. Ad esempio, potremmo estendere lo script all'inizio di questa pagina con una casella barchart (diagramma a barre) aggregata per Brand:
show barchart "Sales by brand" with sum(Orders.NetAmount) group by Brand
group by supporta anche raggruppamenti per vettori multipli o espressioni multiple, con una sintassi identica a quella che abbiamo visto più in alto con l'opzione by.

Filtri di aggregazione

Gli aggregatori di Envision supportano anche una funzione filtro, grazie alla parola chiave if. Lo script in alto illustra l'uso del filtro alla riga 6:
Orders.Brand = Brand
show table "Top Suppliers" a6f7 tomato with 
Supplier, 
distinct(Category) as "Categories", 
sum(Orders.NetAmount) as "Sold{$}", 
mode(Orders.Brand) if (Orders.Brand != "Fellowes") as "Most frequent brand sold" 
group by Supplier 
order by sum(Orders.NetAmount) desc

In questo caso, il marchio Fellowes è escluso esplicitamente dal rapporto attraverso il filtro if. I filtri sono utili all'interno dell'assegnamento della casella, perché offrono la possibilità di filtrare ogni colonna separatamente, mentre un filtro where posizionato all'esterno dell'assegnamento filtrerebbe tutte le righe selezionate della tabella Orders senza distinguere tra le varie colonne.

I filtri possono anche essere usati inline:
TwoAndMore = sum(Orders.1) if (Orders.Quantity >= 2)