Informazioni sugli aggregati simmetrici

Gli aggregati simmetrici di Looker sono una funzionalità molto potente. Tuttavia, poiché gli aggregati simmetrici possono sembrare un po' intimidatori e accade per lo più dietro le quinte, incontrarli può creare confusione. Questa pagina fornisce le seguenti informazioni sugli aggregati simmetrici:

Perché sono necessari i dati aggregati simmetrici

SQL, il linguaggio di analisi dei dati, è estremamente potente. Ma un grande potere comporta una grande responsabilità e gli analisti hanno la responsabilità di evitare di calcolare accidentalmente dati aggregati errati, come somme, medie e conteggi.

È sorprendentemente facile eseguire questi calcoli in modo errato e questi tipi di calcoli errati possono essere fonte di grande frustrazione per gli analisti. Il seguente esempio illustra come puoi sbagliare.

Immagina di avere due tabelle, orders e order_items. La tabella order_items registra una riga per ogni elemento di un ordine, pertanto la relazione tra le tabelle è one-to-many. La relazione è one-to-many perché un ordine può avere molti articoli, ma ciascuno di essi può far parte di un solo ordine. Per istruzioni su come determinare la relazione corretta per un join, consulta la pagina di best practice Come interpretare il parametro di relazione.

In questo esempio, supponiamo che la tabella orders abbia il seguente aspetto:

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

In questa tabella orders, la somma dei valori nella colonna total (SUM(total)) è uguale a 124.84.

Supponiamo che la tabella order_items contenga sei righe:

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 $

Ottenere il conteggio degli articoli ordinati è facile. La somma dei valori nella colonna quantity (SUM(quantity)) è 7.

Ora, supponi di unire la tabella orders e la tabella order_items utilizzando la colonna condivisa order_id. Questo genera la seguente tabella:

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 $

La tabella precedente fornisce nuove informazioni, ad esempio il 1° dicembre (2017-12-01 nella colonna order_date) e l'ordinazione di quattro articoli il 2 dicembre (2017-12-02). Alcuni dei calcoli precedenti, come i calcoli SUM(quantity), sono ancora validi. Tuttavia, si verificherà un problema se tenti di calcolare la spesa totale.

Se utilizzi il calcolo precedente, SUM(total), il valore totale 50.36 nella nuova tabella per le righe in cui il valore di order_id è 1 verrà conteggiato due volte, poiché l'ordine include due elementi diversi (con valori item_id pari a 50 e 63). Il totale di 24.12 per le righe in cui order_id è 2 verrà conteggiato tre volte, poiché questo ordine include tre elementi diversi. Di conseguenza, il risultato del calcolo SUM(total) per questa tabella è 223.44 anziché la risposta corretta, che è 124.84.

Sebbene sia facile evitare questo tipo di errore quando si lavora con due piccole tabelle di esempio, risolvere questo problema sarebbe molto più complicato nella vita reale, con molte tabelle e molti dati. Si tratta esattamente del tipo di errore di calcolo che si potrebbe fare senza neanche rendersi conto. Questo è il problema risolto con gli aggregati simmetrici.

Come funzionano gli aggregati simmetrici

Gli aggregati simmetrici impediscono agli analisti, e a chiunque utilizzi Looker, di calcolare accidentalmente in modo errato i dati aggregati come somme, medie e conteggi. Gli aggregati simmetrici contribuiscono a sollevare un enorme carico dalle spalle degli analisti, perché questi ultimi possono avere la certezza che gli utenti non pagheranno dati errati. A tale scopo, i dati aggregati simmetrici devono accertarsi di conteggiare ogni fatto nel calcolo il numero corretto di volte, nonché tenere traccia di quanto viene calcolato.

Nell'esempio precedente, la funzione di aggregazione simmetrica riconosce che total è una proprietà di orders (non di order_items), pertanto deve conteggiare il totale di ogni ordine solo una volta per ottenere la risposta corretta. A questo scopo, la funzione utilizza una chiave primaria univoca definita dall'analista in Looker. Ciò significa che, quando Looker esegue calcoli nella tabella unita, riconosce che, anche se ci sono due righe il cui valore di order_id è 1, non dovrebbe conteggiare due volte il totale perché quel totale è già stato incluso nel calcolo e dovrebbe conteggiare il totale una sola volta per le tre righe in cui il valore di order_id è 2.

Vale la pena notare che gli aggregati simmetrici dipendono da una chiave primaria univoca e dalla relazione di join corretta specificata nel modello. Pertanto, se i risultati ti sembrano errati, consulta un analista per assicurarti che la configurazione sia corretta.

Perché gli aggregati simmetrici sembrano complicati

L'aspetto dei dati aggregati simmetrici può essere un po' mistico. Senza i dati aggregati simmetrici, Looker in genere scrive codice SQL corretto e ben strutturato, come nell'esempio seguente: 

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

Con i dati aggregati simmetrici, le scritture di Looker SQL potrebbero avere un aspetto simile al seguente esempio: 

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

L'esatto formato degli aggregati simmetrici dipende dal dialetto di SQL scritto da Looker, ma tutti i formati hanno lo stesso principio: se più righe hanno la stessa chiave primaria, la funzione di aggregazione simmetrica le conteggia solo una volta. A questo scopo, utilizza le poco note funzioni SUM DISTINCT e AVG DISTINCT che fanno parte dello standard SQL.

Per capire come questo accade, puoi utilizzare il calcolo eseguito in precedenza e analizzarlo con i dati aggregati simmetrici. Delle sette colonne nelle tabelle unite, te ne occorrono solo due: quella che stai aggregando (total) e la chiave primaria univoca per gli ordini (order_id).

order_id total
1 50,36 $
1 50,36 $
2 24,12 $
2 24,12 $
2 24,12 $
3 50,26 $

Gli aggregati simmetrici prendono la chiave primaria (order_id, in questo caso) e creano un numero molto grande per ciascuna, il che garantisce essere univoco e fornisce sempre lo stesso output per lo stesso input. In genere questo avviene con una funzione di hashing, i cui dettagli non rientrano nell'ambito di questa pagina. Il risultato sarà simile al seguente:

big_unique_number total
802959190063912 50,36 $
802959190063912 50,36 $
917651724816292 24,12 $
917651724816292 24,12 $
917651724816292 24,12 $
110506994770727 50,36 $

Quindi, per ogni riga, Looker esegue questa operazione: 

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

Questo ti fornisce in modo affidabile i totali aggregati correttamente, contando ogni totale esattamente il numero corretto di volte. La funzione di aggregazione simmetrica di Looker non viene ingannata da righe ripetute o da più ordini che hanno lo stesso totale. Puoi provare a fare i calcoli autonomamente per avere un'idea più chiara di come funzionano gli aggregati simmetrici.

L'SQL necessario per eseguire questa operazione non è il più piacevole da esaminare: con CAST(), md5(), SUM(DISTINCT) e STRTOL(), non vorrai certo scrivere l'SQL a mano. Ma, per fortuna, non devi farlo: Looker può scrivere l'SQL al posto tuo.

Quando un'aggregazione funziona correttamente senza bisogno di aggregazioni simmetriche, Looker lo rileva automaticamente e non utilizza la funzione. Poiché gli aggregati simmetrici comportano alcuni costi in termini di prestazioni, la capacità di Looker di capire quando utilizzare e quando non utilizzare gli aggregati simmetrici ottimizza ulteriormente l'SQL generato da Looker e lo rende il più efficiente possibile, garantendo comunque la risposta giusta.