Collegare le tabelle con il costrutto left by di Envision

Collegare le tabelle con il costrutto By-At












Home » Risorse » Qui

Envision offre un meccanismo implicito che consente di collegare le tabelle quando queste contengono un campo Id (indice articoli), il che avviene di frequente nel caso dello storico delle vendite o degli acquisti. Questo meccanismo, che chiamiamo di collegamento naturale, non è però adatto a situazioni più complesse. Per questo abbiamo deciso di dotare Envision di un meccanismo più generico, il by-at, una particolare aggregazione che consente di collegare le tabelle su basi meno restrittive.

Un esempio di script

Su questa pagina, facciamo riferimento al campione di dati Envision, disponibile nel percorso /sample di ogni account Lokad. Per chi non l'avesse ancora fatto, suggeriamo per prima cosa di leggere la nostra guida a come leggere i file con Envision, in particolare la sezione dedicata ai tipi di file che il sistema si aspetta, che sarà fondamentale per capire lo script con cui stiamo per lavorare.

read "/sample/Lokad_Items.tsv"
read "/sample/Lokad_Orders.tsv" as O
read "/sample/Lokad_Suppliers.tsv" as S[*]

Moq = same(S.Moq) by S.Supplier at Supplier
show table "Item list" a1d4 tomato with 
Id 
Name 
Supplier 
Moq

Nelle prime tre righe ritroviamo il nostro solito assegnamento per la lettura dei file. Alla riga 3, il costrutto S[*] specifica che la tabella fornitori non è soggetta ad alcuna aspettativa sul tipo di tabella, poiché è una tabella indipendente senza chiavi primarie né chiavi esterne. In effetti, il file Lokad_Suppliers.tsv, a differenza degli altri file .tsv contenuti nella cartella, non ha una colonna Id, ed Envision non può quindi ricollegarlo implicitamente alla tabella Articoli: la tabella dei fornitori è infatti ordinata per fornitore, con un fornitore per riga, mentre tutti gli altri file contengono cartelle ordinate per articolo.

Alla riga 5, sfruttiamo l'aggregazione by-at (così chiamata perché utilizza le parole chiave by e at) come anello di congiunzione tra le due tabelle Items (Articoli) e S (Fornitori). Possiamo anche scrivere:
Items.Moq = same(S.Moq) by S.Supplier at Items.Supplier
Tuttavia, le convenzioni di Envision ci impongono di omettere il nome della tabella Items.

In questo caso specifico, abbiamo popolato un vettore Items.Moq con i dati ottenuti dalla tabella S e abbiamo stabilito un collegamento tra Items.Supplier e S.Supplier.

Se una voce fornitore nella tabella Items non ha un equivalente nella tabella S, verrà usato un valore zero; oppure, come specifica la parola chiave or, secondo l'uso solito degli aggregatori in Envision, verranno usati altri valori di default. Ad esempio, avremmo potuto scrivere:
Items.Moq = same(S.Moq) by S.Supplier at Items.Supplier or 0

Alle righe 6-10, viene mostrata la nuova tabella Items per illustrare il calcolo eseguito dall'aggregazione by-at nella riga precedente. Come previsto, ogni articolo è associato al valore MOQ del rispettivo fornitore.

Sintassi generale per by-at

Vediamo la sintassi generale per by-at:

T1.A = agg(T2.B) by [T2.X, T2.Y, T2.Z] if E at [T1.X, T1.Y, T1.Z] or T1.C

In maniera simile agli altri aggregatori di Envision, l'aggregazione by-at offre la possibilità di generare una corrispondenza di tuple, ossia di combinare 'n' campi alla volta. A questo proposito, possiamo utilizzare tutti gli aggregatori di Envision: sum, ma anche min, median, same, etc. Il blocco or può essere omesso, proprio come nell'aggregazione normale.

La semantica di by-at è questa:

  • forma gruppi distinti di tuple per by [T2.X, T2.Y, T2.Z], filtrando con la condizione E, se presente. Per ognuno di questi gruppi, se esiste una corrispondenza con almeno uno dei gruppi di sinistra, calcola l'aggregato agg(T2.B);
  • forma gruppi distinti di tuple per at [T1.X, T1.Y, T1.Z]. Per ognuno di questi, assegna l'aggregato corrispondente di destra;
  • per i gruppi di at che non hanno un gruppo corrispondente a destra, assegna il valore predefinito T1.C.

Proprio come gli altri aggregatori, l'assegnamento or è opzionale e fornisce il valore da usare come risultato dell'aggregazione se il gruppo è vuoto.

By-at: consigli per l'uso

Il by-at è un costrutto dalle enormi potenzialità, che può essere utilizzato nelle situazioni più diverse, non solo per collegare le tabelle nella prospettiva SQL solita.

Una tabella può essere collegata a se stessa: ad esempio,
O.DaySum = sum(O.Quantity) by O.Date at O.Date
illustra come calcolare i totali giornalieri con la tabella O (Ordini) senza fare riferimento alla tabella Day (Giorno).

Possono essere collegate le tabelle calendario Giorno e Settimana: ad esempio,
Day.Shift = sum(O.Quantity) by [O.Id, O.Date - 1] at [Day.Id, Day.Date]
illustra come spostare le quantità da un giorno attraverso il by-at.

Tradurre il by-at in SQL

Per chi ha familiarità con SQL, l'espressione in Envision:
Moq = same(S.Moq) by S.Supplier at Supplier or defaultMoq

equivale all'espressione in SQL, che usa un collegamento esterno a sinistra:

UPDATE Items LEFT OUTER JOIN S ON Items.Supplier = S.Supplier SET Moq = COALESCE(S.Moq, defaultMoq)

La sintassi di Envision pone l'accento, più che sull'algebra relazionale in sé e per sé, su calcoli simili a quelli di Excel.