Informazioni sugli aggregati simmetrici

Gli aggregati simmetrici in Looker sono una funzionalità molto potente. Tuttavia, poiché gli aggregati simmetrici possono sembrare un po' complicati e si verificano principalmente in background, può essere un po' confuso trovarli. Questa pagina fornisce le seguenti informazioni sugli aggregati simmetrici:

Perché sono necessari aggregati simmetrici

SQL, il linguaggio dell'analisi dei dati, è estremamente potente. Tuttavia, un grande potere comporta una grande 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 illustra come si può commettere un errore.

Immagina di avere due tabelle, orders e order_items. La tabella order_items registra una riga per ogni articolo 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 ogni articolo 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)) è pari 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.

Supponiamo ora di unire la tabella orders e la tabella order_items utilizzando la colonna condivisa order_id. Il risultato è 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 che due articoli sono stati ordinati il 1° dicembre (2017-12-01 nella colonna order_date) e quattro il 2 dicembre (2017-12-02). Alcuni dei calcoli precedenti, come quelli di SUM(quantity), sono ancora validi. Tuttavia, riscontrerai 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 utilizzano due piccole tabelle di esempio, la risoluzione di questo problema sarebbe molto più complicata 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 altro utilizzi Looker di calcolare erroneamente aggregati come somme, medie e conteggi. Gli aggregati simmetrici alleggeriscono il carico degli analisti, che possono fare affidamento sul fatto che gli utenti non continueranno a utilizzare dati errati. Gli aggregati simmetrici lo fanno assicurandosi 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 i calcoli sulla tabella unita, riconosce che, anche se ci sono due righe in cui il valore di order_id è 1, non deve conteggiare il totale due volte perché questo totale è già stato incluso nel calcolo e che deve 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 che ottieni sembrano errati, rivolgiti a un analista per assicurarti che tutto sia configurato 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, il codice SQL scritto da Looker potrebbe 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 delle aggregazioni simmetriche 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 di aggregazione simmetrica le conteggia una sola volta. A tal fine, utilizza le funzioni SUM DISTINCT e AVG DISTINCT poco conosciute 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, puoi ottenere in modo affidabile i totali aggregati correttamente, conteggiando 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 autonomamente per capire meglio il funzionamento degli aggregati simmetrici.

Il codice SQL necessario per farlo non è il più bello da vedere: con CAST(), md5(), SUM(DISTINCT) e STRTOL(), di certo non vorrai scrivere il codice SQL a mano. Fortunatamente, non è necessario: Looker può scrivere il codice SQL per te.

Quando un'aggregazione funziona correttamente senza la necessità di aggregati simmetrici, 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.