Compreender as acumulações simétricas

Os agregados simétricos no Looker são uma funcionalidade muito poderosa. No entanto, como os dados agregados simétricos podem parecer um pouco intimidantes e ocorrem principalmente nos bastidores, pode ser um pouco confuso encontrá-los. Esta página fornece as seguintes informações sobre dados agregados simétricos:

Por que motivo são necessárias acumulações simétricas

O SQL, a linguagem de análise de dados, é extremamente poderoso. No entanto, com um grande poder vem uma grande responsabilidade, e os analistas têm a responsabilidade de evitar calcular acidentalmente agregações incorretas, como somas, médias e contagens.

É surpreendentemente fácil realizar estes cálculos de forma incorreta, e estes tipos de cálculos incorretos podem ser uma fonte de grande frustração para os analistas. O exemplo seguinte ilustra como pode cometer erros.

Imagine que tem duas tabelas, orders e order_items. A tabela order_items regista uma linha para cada artigo numa encomenda, pelo que a relação entre as tabelas é de um para muitos. A relação é de um para muitos porque uma encomenda pode ter muitos artigos, mas cada artigo só pode fazer parte de uma encomenda. Consulte a página de práticas recomendadas Como acertar no parâmetro de relação para obter orientações sobre como determinar a relação correta para uma junção.

Neste exemplo, suponhamos que a tabela orders tem o seguinte aspeto:

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

Nesta tabela orders, a soma dos valores na coluna total (SUM(total)) é igual a 124.84.

Suponhamos que a tabela order_items contém seis linhas:

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 $

É fácil saber a quantidade de artigos encomendados. A soma dos valores na coluna quantity (SUM(quantity)) é 7.

Agora, suponha que junta a tabela orders e a tabela order_items através da respetiva coluna partilhada, order_id. Isto resulta na seguinte tabela:

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 $

A tabela anterior fornece novas informações, como o facto de terem sido encomendados dois artigos a 1 de dezembro (2017-12-01 na coluna order_date) e quatro artigos a 2 de dezembro (2017-12-02). Alguns dos cálculos anteriores, como os cálculos SUM(quantity), continuam a ser válidos. No entanto, vai ter um problema se tentar calcular o total gasto.

Se usar o cálculo anterior, SUM(total), o valor total 50.36 na nova tabela para linhas em que o valor de order_id é 1 é contabilizado duas vezes, uma vez que a encomenda inclui dois artigos diferentes (com valores de item_id de 50 e 63). O total de 24.12 para linhas em que o order_id é 2 é contabilizado três vezes, uma vez que esta encomenda inclui três artigos diferentes. Como resultado, o resultado do cálculo SUM(total) para esta tabela é 223.44, em vez da resposta correta, que é 124.84.

Embora seja fácil evitar este tipo de erro quando trabalha com duas pequenas tabelas de exemplo, resolver este problema seria muito mais complicado na vida real, com muitas tabelas e muitos dados. Este é exatamente o tipo de erro de cálculo que alguém pode cometer sem sequer se aperceber. Este é o problema que os agregados simétricos resolvem.

Como funcionam os dados agregados simétricos

Os agregados simétricos impedem que os analistas, e qualquer outra pessoa que use o Looker, calculem incorretamente agregados, como somas, médias e contagens. As agregações simétricas ajudam a aliviar a carga de trabalho dos analistas, uma vez que estes podem confiar que os utilizadores não vão avançar com dados incorretos. Os agregados simétricos fazem isto garantindo que contam cada facto no cálculo o número correto de vezes, bem como monitorizando o que está a calcular.

No exemplo anterior, a função de agregação simétrica reconhece que total é uma propriedade de orders (e não de order_items), pelo que tem de contabilizar o total de cada encomenda apenas uma vez para obter a resposta correta. A função faz isto através de uma chave primária única que o analista definiu no Looker. Isto significa que, quando o Looker faz cálculos na tabela unida, reconhece que, embora existam duas linhas em que o valor de order_id é 1, não deve contar o total duas vezes porque esse total já foi incluído no cálculo e que só deve contar o total uma vez para as três linhas em que o valor de order_id é 2.

É importante ter em atenção que os agregados simétricos dependem de uma chave principal única e da relação de junção correta especificada no modelo. Por isso, se os resultados que está a receber parecerem incorretos, fale com um analista para se certificar de que tudo está configurado corretamente.

Por que motivo as acumulações simétricas parecem complicadas

O aspeto dos agregados simétricos pode ser um pouco misterioso. Sem os agregados simétricos, o Looker escreve normalmente um SQL bem estruturado, como no exemplo seguinte: 

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

Com agregados simétricos, o SQL que o Looker escreve pode ter um aspeto semelhante ao seguinte exemplo: 

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

O formato exato que os agregados simétricos assumem depende do dialeto de SQL que o Looker está a escrever, mas todos os formatos fazem a mesma coisa básica: se várias linhas tiverem a mesma chave primária, a função de agregados simétricos só as conta uma vez. Para tal, usa as funções pouco conhecidas SUM DISTINCT e AVG DISTINCT que fazem parte da norma SQL.

Para ver como isto acontece, pode usar o cálculo que fez anteriormente e trabalhar com agregações simétricas. Das sete colunas nas tabelas unidas, só precisa de duas: a que está a agregar (total) e a chave principal única para encomendas (order_id).

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

Os agregados simétricos usam a chave primária (order_id, neste caso) e criam um número muito grande para cada um, que tem a garantia de ser exclusivo e dar sempre o mesmo resultado para a mesma entrada. (Geralmente, isto é feito com uma função de hash, cujos detalhes estão fora do âmbito desta página.) Esse resultado teria um aspeto semelhante ao seguinte:

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

Em seguida, para cada linha, o Looker faz o seguinte: 

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

Isto dá-lhe de forma fiável os totais agregados corretos, contabilizando cada total exatamente o número certo de vezes. A função de agregações simétricas do Looker não é enganada por linhas repetidas nem por várias encomendas com o mesmo total. Pode tentar fazer os cálculos manualmente para compreender melhor como funcionam os dados agregados simétricos.

O SQL necessário para o fazer não é o mais bonito de se ver: com CAST(), md5(), SUM(DISTINCT) e STRTOL(), certamente que não iria querer escrever o SQL manualmente. No entanto, felizmente, não tem de o fazer. O Looker pode escrever o SQL por si.

Quando uma agregação funciona corretamente sem a necessidade de agregações simétricas, o Looker deteta-o automaticamente e não usa a função. Uma vez que os agregados simétricos impõem alguns custos de desempenho, a capacidade do Looker de discernir quando usar e quando não usar agregados simétricos otimiza ainda mais o SQL que o Looker gera e torna-o o mais eficiente possível, garantindo ao mesmo tempo a resposta certa.