Noções básicas sobre agregações simétricas

Os agregados simétricos no Looker são um recurso muito eficiente. No entanto, como agregações simétricas podem parecer um pouco intimidantes e geralmente acontecem em segundo plano, pode ser um pouco confuso encontrá-las. Nesta página, você encontra as seguintes informações sobre agregações simétricas:

Por que agregados simétricos são necessários

O SQL, a linguagem da análise de dados, é extremamente poderoso. Porém, com grandes poderes vêm grandes responsabilidades, e os analistas têm a responsabilidade de evitar o cálculo acidental de agregações incorretas, 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 o que pode acontecer.

Imagine que você tem duas tabelas, orders e order_items. A tabela order_items registra uma linha para cada item em um pedido, então a relação entre as tabelas é de um para muitos. A relação é 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 em Como definir o parâmetro de relacionamento corretamente para orientações sobre como determinar a relação correta para 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

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

Agora, suponha que você mescle a tabela orders e a tabela order_items usando a coluna compartilhada order_id. Isso resulta na seguinte tabela:

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 apresenta novas informações, como dois itens pedidos em 1o de dezembro (2017-12-01 na coluna order_date) e quatro itens pedidos em 2 de dezembro (2017-12-02). Alguns dos cálculos anteriores, como SUM(quantity), ainda são válidos. No entanto, você terá problemas se tentar calcular o gasto total.

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 for 1 será contado duas vezes, já que o pedido inclui dois itens diferentes (com valores item_id de 50 e 63). O total de 24.12 para linhas em que 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 esta tabela é 223.44, em vez da resposta correta, que é 124.84.

Embora seja fácil evitar esse tipo de erro quando se 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 pode cometer sem perceber. Esse é o problema resolvido com agregações simétricas.

Como funcionam os agregados simétricos

Os agregados simétricos impedem que os analistas (e qualquer outra pessoa que use o Looker) calculem acidentalmente os agregados, como somas, médias e contagens. Agregados simétricos ajudam a aliviar um grande peso dos ombros dos analistas, porque os analistas podem confiar que os usuários não vão cobrar dados incorretos à frente. 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). Por isso, ela precisa contar o total de cada pedido apenas uma vez para encontrar 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 estiver fazendo cálculos na tabela unida, ele reconhece que, embora haja duas linhas em que o valor de order_id seja 1, ele não precisa contar o total duas vezes, porque esse total já foi incluído no cálculo e 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 da especificação de uma chave primária exclusiva e da relação de mesclagem correta especificada no modelo. Portanto, se os resultados que você está obtendo parecerem errados, fale com um analista para se certificar de que tudo esteja configurado corretamente.

Por que agregados simétricos parecem complicados

A aparência de agregados simétricos pode ser um pouco misteriosa. Sem agregados simétricos, o Looker geralmente escreve SQL bom e bem comportado, como no exemplo a seguir: 

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 agregações simétricas, as gravações do SQL Looker podem ficar parecidas 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á gravando, mas todos os formatos fazem a mesma coisa básica: se várias linhas tiverem a mesma chave primária, a função de agregações simétricas só as conta uma vez. Isso é feito usando as funções pouco conhecidas SUM DISTINCT e AVG DISTINCT que fazem parte do padrão SQL.

Para ver como isso acontece, use o cálculo feito anteriormente com agregações simétricas. Das sete colunas nas tabelas unidas, você só precisa de duas: a que você está agregando (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 pegam a chave primária (order_id, neste caso) e criam um número muito grande para cada uma, o que é exclusivo e sempre gera a mesma saída para a mesma entrada. Geralmente, isso é feito com uma função de hash, com detalhes além do escopo desta página. O resultado deve 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 proporciona 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 é enganada por linhas repetidas ou por vários pedidos com o mesmo total. Você pode tentar fazer os cálculos por conta própria para ter uma ideia melhor de como os agregados simétricos funcionam.

O SQL necessário para fazer isso não é o mais bonito de se observar: com CAST(), md5(), SUM(DISTINCT) e STRTOL(), certamente não seria interessante escrever o SQL manualmente. Felizmente, isso não é necessário. O Looker pode escrever 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 performance, a capacidade do Looker de saber quando usar e quando não usar, as agregações simétricas otimizam ainda mais o SQL que o Looker gera e o torna o mais eficiente possível, sem deixar de garantir a resposta certa.