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

Os agregados simétricos no Looker são um recurso muito poderoso. No entanto, como os agregados simétricos 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 agregados simétricos são necessários
• Como os agregados simétricos funcionam
• Por que os agregados simétricos parecem complicados

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

SQL, a linguagem da análise de dados, é extremamente poderosa. Mas, com grande poder vem grande responsabilidade, 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 como você pode dar errado.

Imagine que você tenha duas tabelas, orders e order_items. A tabela order_items registra uma linha para cada item em um pedido, de modo que 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.

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:

É fácil saber a quantidade de itens encomendados. 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. Isso resulta na seguinte tabela:

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 a ordem inclui dois itens diferentes (com valores item_id 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 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 impedem que os analistas e qualquer outra pessoa que use o Looker façam cálculos incorretos de agregação, como somas, médias e contagens. Os agregados simétricos ajudam a aliviar a carga dos analistas, porque eles podem confiar que os usuários não vão cobrar com dados incorretos. Os agregados simétricos fazem isso contando cada fato no cálculo o número correto de vezes e mantendo o controle do 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. Para isso, a função usa uma chave primária exclusiva que o analista definiu no Looker. Isso significa que, quando o Looker está fazendo cálculos na tabela mesclada, ele reconhece que, embora haja duas linhas em que o valor de order_id é 1, ele não deve contar o total duas vezes, porque esse total já foi incluído no cálculo e que ele conta o total apenas 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 única e da relação de mesclagem correta especificada 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 agregados simétricos parecem complicados

A aparência de agregados simétricos pode ser um pouco misteriosa. Sem agregados simétricos, o Looker normalmente escreve SQL agradável e com comportamento adequado, como neste exemplo:

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 assim:

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á escrevendo, mas todos os formatos fazem a mesma coisa básica: se várias linhas têm a mesma chave primária, a função de agregados simétricos as conta apenas uma vez. Para isso, ele usa as funções SUM DISTINCT e AVG DISTINCT pouco conhecidas, 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 você está agregando (total) e a chave primária exclusiva para pedidos (order_id).

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:

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ção simétrica do Looker não é enganada por linhas repetidas ou por vários pedidos com o mesmo total. Você pode tentar fazer os cálculos para ter uma ideia 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, para nossa sorte, 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 discernir quando usar e quando não usar, os agregados simétricos otimizam ainda mais o SQL gerado pelo Looker e o torna o mais eficiente possível, sem deixar de garantir a resposta certa.