Noções básicas sobre agregados simétricos

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

Por que os conjuntos simétricos são necessários

SQL, a linguagem da análise de dados, é extremamente poderosa. Mas com grandes poderes vêm grandes responsabilidades, e os analistas têm a responsabilidade de evitar o cálculo acidental de agregados incorretos, como somas, médias e contagens.

É surpreendentemente fácil realizar esses cálculos incorretamente, e esses tipos de cálculos incorretos podem ser uma fonte de grande frustração para os analistas. O exemplo a seguir ilustra como você pode errar.

Imagine que você tenha duas tabelas, orders e order_items. A tabela order_items registra uma linha para cada item em um pedido. Portanto, a relação entre as tabelas é de um para muitos. O relacionamento é de um para muitos porque um pedido pode ter muitos itens, mas cada item só pode fazer parte de um pedido. Consulte a página de práticas recomendadas Como configurar corretamente o parâmetro de relação para saber como determinar a relação correta de uma mesclagem.

Neste exemplo, suponha que a tabela orders tenha esta aparência:

order_id user_id total order_date
1 100 US$ 50,36 2017-12-01
2 101 US$ 24,12 2017-12-02
3 137 US$ 50,36 2017-12-02

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

Suponha que a tabela order_items contenha seis linhas:

order_id item_id quantity unit_price
1 50 1 US$ 23,00
1 63 2 US$ 13,68
2 63 1 US$ 13,68
2 72 1 US$ 5,08
2 79 1 US$ 5,36
3 78 1 US$ 50,36

Saber a contagem dos itens pedidos é fácil. A soma dos valores na coluna quantity (SUM(quantity)) é 7.

Agora, suponha que você mescle as tabelas orders e order_items usando a coluna compartilhada order_id. O resultado é a tabela a seguir:

order_id user_id total order_date item_id quantity unit_price
1 100 US$ 50,36 2017-12-01 50 1 US$ 23,00
1 100 US$ 50,36 2017-12-01 63 2 US$ 13,68
2 101 US$ 24,12 2017-12-02 63 1 US$ 13,68
2 101 US$ 24,12 2017-12-02 72 1 US$ 5,08
2 101 US$ 24,12 2017-12-02 79 1 US$ 5,36
3 137 US$ 50,36 2017-12-02 78 1 US$ 50,36

A tabela anterior fornece novas informações, como o fato de dois itens terem sido encomendados em 1º de dezembro (2017-12-01 na coluna order_date) e quatro itens em 2 de dezembro (2017-12-02). Alguns dos cálculos anteriores, como os de SUM(quantity), ainda são válidos. No entanto, você vai encontrar um problema se tentar calcular o total gasto.

Se você 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 será contado duas vezes, já que o pedido inclui dois itens diferentes (com valores de item_id de 50 e 63). O total de 24.12 para linhas em que o order_id é 2 será contado três vezes, já que esse pedido inclui três itens diferentes. Como resultado, o resultado do cálculo SUM(total) para essa tabela é 223.44 em vez da resposta correta, que é 124.84.

Embora seja fácil evitar esse tipo de erro quando você trabalha com duas pequenas tabelas de exemplo, resolver esse problema seria muito mais complicado na vida real, com muitas tabelas e muitos dados. Esse é exatamente o tipo de erro de cálculo que alguém poderia cometer sem nem perceber. Esse é o problema que os agregados simétricos resolvem.

Como funcionam os agregados simétricos

Os agregados simétricos evitam que analistas e qualquer outra pessoa que use o Looker calculem acidentalmente agregados, como somas, médias e contagens. Agregados simétricos ajudam a eliminar um enorme fardo dos analistas os ombros, porque os analistas podem confiar que os usuários não vão avançar com dados incorretos. Os agregados simétricos fazem isso contando cada fato no cálculo o número correto de vezes, bem como acompanhando o que você está calculando.

No exemplo anterior, a função de agregação simétrica reconhece que total é uma propriedade de orders (não order_items). Portanto, ela precisa contar o total de cada pedido apenas uma vez para obter a resposta correta. A função faz isso usando uma chave primária exclusiva definida pelo analista no Looker. Isso significa que, quando o Looker faz cálculos na tabela combinada, ele reconhece que, embora haja duas linhas em que o valor de order_id é 1, não é necessário contar o total duas vezes, porque esse total já foi incluído no cálculo. Além disso, ele só precisa contar o total uma vez para as três linhas em que o valor de order_id é 2.

Os agregados simétricos dependem de uma chave primária exclusiva e do relacionamento de mesclagem correto especificado no modelo. Portanto, se os resultados que você está obtendo estiverem errados, fale com um analista para ter certeza de que tudo está configurado corretamente.

Por que os conjuntos simétricos parecem complicados

A aparência de agregados simétricos pode ser um pouco misteriosa. Sem agregações simétricas, o Looker geralmente grava SQLs boas e bem comportadas, como no exemplo abaixo: 

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, as gravações do SQL do Looker podem se parecer com este 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 dos agregados simétricos depende do dialeto do SQL que o Looker está programando, mas todos os formatos fazem a mesma coisa: se várias linhas têm a mesma chave primária, a função de agregados simétricos só as conta uma vez. Ele faz isso usando as funções pouco conhecidas SUM DISTINCT e AVG DISTINCT que fazem parte do padrão SQL.

Para ver como isso acontece, é possível usar o cálculo feito anteriormente com agregações simétricas. Das sete colunas nas tabelas mescladas, você só precisa de duas: a que está sendo agregada (total) e a chave primária exclusiva para pedidos (order_id).

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

Os agregados simétricos usam a chave primária (order_id, neste caso) e criam um número muito grande para cada uma delas, que tem a garantia de ser exclusivo e sempre gerar a mesma saída para a mesma entrada. Isso geralmente é feito com uma função de hash, cujos detalhes estão fora do escopo desta página. Esse resultado será parecido com este:

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

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

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

Isso fornece os totais agregados corretamente, contando cada total exatamente o número certo de vezes. A função de agregações simétricas do Looker não é enganosa por linhas repetidas ou por vários pedidos que têm o mesmo total. Você pode tentar fazer as contas para ter uma ideia melhor de como os agregados simétricos funcionam.

O SQL necessário para fazer isso não é o mais bonito: com CAST(), md5(), SUM(DISTINCT) e STRTOL(), você certamente não vai querer escrever o SQL manualmente. Mas, felizmente, você não precisa fazer isso. O Looker pode gravar o SQL para você.

Quando uma agregação funciona corretamente sem a necessidade de agregações simétricas, o Looker detecta isso automaticamente e não usa a função. Como os agregados simétricos impõem alguns custos de desempenho, a capacidade do Looker de discernir quando usar e quando não usar otimiza ainda mais o SQL gerado pelo Looker e o torna o mais eficiente possível, garantindo a resposta certa.