Informazioni sugli aggregati simmetrici

Gli aggregati simmetrici in Looker sono una funzionalità molto potente. Tuttavia, poiché gli aggregati simmetrici possono sembrare un po' intimidatori e avvengono per lo più dietro le quinte, può essere un po' confuso quando li incontri. Questa pagina fornisce le seguenti informazioni sugli aggregati simmetrici:

Perché sono necessari gli aggregati simmetrici

SQL, il linguaggio per l'analisi dei dati, è estremamente potente. Tuttavia, con un grande potere derivano grandi responsabilità e gli analisti hanno il compito di evitare di calcolare accidentalmente 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. L'esempio seguente spiega 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, quindi la relazione tra le tabelle è uno a molti. La relazione è uno a molti perché un ordine può contenere più elementi, ma ognuno può far parte di un solo ordine. Consulta la pagina Best practice per impostare correttamente il parametro della relazione per indicazioni su come determinare la relazione corretta per un'unione.

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 $

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

Ora, supponi di unire la tabella orders e la tabella order_items utilizzando la relativa colonna condivisa, order_id. Viene visualizzata 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 fatto che due articoli sono stati ordinati il 1° dicembre (2017-12-01 nella colonna order_date) e quattro articoli sono stati ordinati il 2 dicembre (2017-12-02). Alcuni dei calcoli precedenti, ad esempio SUM(quantity), sono ancora validi. Tuttavia, si verifica un problema se provi a 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 articoli diversi (con valori item_id di 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 articoli diversi. Di conseguenza, il risultato del calcolo SUM(total) per questa tabella è 223.44 anziché la risposta corretta, ovvero 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. Questo è esattamente il tipo di errore di calcolo che qualcuno potrebbe fare senza nemmeno rendersene conto. Questo è il problema risolto dagli aggregati simmetrici.

Come funzionano gli aggregati simmetrici

Gli aggregati simmetrici impediscono agli analisti e a chiunque utilizzi Looker di calcolare accidentalmente in modo errato gli aggregati come somme, medie e conteggi. Gli aggregati simmetrici aiutano gli analisti a prendere un enorme carico di lavoro perché gli analisti possono avere la certezza che gli utenti non pagheranno in anticipo con dati errati. I dati aggregati simmetrici lo fanno accertando di conteggiare ogni fatto nel calcolo il numero corretto di volte e tenendo traccia di ciò che stai calcolando.

Nell'esempio precedente, la funzione di aggregati simmetrici riconosce che total è una proprietà di orders (non di order_items), pertanto deve conteggiare il totale di ogni ordine una sola volta per ottenere la risposta corretta. La funzione esegue questa operazione utilizzando una chiave primaria univoca definita dall'analista in Looker. Ciò significa che quando Looker esegue calcoli sulla tabella unita, riconosce che, anche se sono presenti due righe in cui il valore di order_id è 1, non deve conteggiare due volte il totale perché quel totale è già stato incluso nel calcolo e che dovrebbe conteggiare una sola volta il totale 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 sono errati, chiedi a un analista di verificare che tutto sia impostato correttamente.

Perché gli aggregati simmetrici sembrano complicati

L'aspetto degli aggregati simmetrici può essere un po' enigmatico. Senza aggregati simmetrici, in genere Looker scrive SQL corretto e ben comportato, 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 gli aggregati simmetrici, le scritture di SQL Looker potrebbero avere il seguente aspetto: 

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

Il formato esatto adottato dagli aggregati simmetrici dipende dal dialetto SQL utilizzato da Looker, ma tutti i formati svolgono la stessa operazione di base: se più righe hanno la stessa chiave primaria, la funzione degli aggregati simmetrici le conteggia una sola volta. Per farlo, utilizza le funzioni SUM DISTINCT e AVG DISTINCT poco note che fanno parte dello standard SQL.

Per capire come funziona, puoi prendere il calcolo eseguito in precedenza ed esaminarlo con gli aggregati simmetrici. Delle sette colonne nelle tabelle unite, 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 (in questo caso order_id) e creano un numero molto grande per ogni chiave, che è garantito essere univoco e dare sempre lo stesso output per lo stesso input. In genere lo fa 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 $

Poi, per ogni riga, Looker esegue le seguenti operazioni: 

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

In questo modo ottieni i totali correttamente aggregati, contando ogni totale esattamente il numero di volte corretto. La funzione di aggregati simmetrici di Looker non viene ingannata da righe ripetute o da più ordini con lo stesso totale. Puoi provare a fare i calcoli per avere un'idea più chiara di come funzionano gli aggregati simmetrici.

L'SQL necessario per eseguire questa operazione non è il più bello da vedere: con CAST(), md5(), SUM(DISTINCT) e STRTOL(), non è sicuramente consigliabile scrivere l'SQL a mano. Fortunatamente, non è necessario: Looker può scrivere il codice SQL per te.

Quando un'aggregazione funziona correttamente senza la necessità 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 distinguere quando utilizzarli e quando no ottimizza ulteriormente il codice SQL generato da Looker e lo rende il più efficiente possibile, garantendo al contempo la risposta corretta.