Información sobre los agregados simétricos

Los agregados simétricos de Looker son una función muy potente. Sin embargo, como los agregados simétricos pueden parecer un poco intimidantes y se producen principalmente en segundo plano, puede resultar un poco confuso encontrarlos. En esta página se proporciona la siguiente información sobre los agregados simétricos:

• Por qué se necesitan agregados simétricos
• Cómo funcionan las agregaciones simétricas
• Por qué los agregados simétricos parecen complicados

Por qué se necesitan agregados simétricos

SQL, el lenguaje del análisis de datos, es extremadamente potente. Sin embargo, un gran poder conlleva una gran responsabilidad, y los analistas tienen la responsabilidad de evitar calcular agregaciones incorrectas por error, como sumas, medias y recuentos.

Es sorprendentemente fácil realizar estos cálculos de forma incorrecta, y estos tipos de cálculos incorrectos pueden ser una fuente de gran frustración para los analistas. En el siguiente ejemplo se muestra cómo puedes equivocarte.

Imagina que tienes dos tablas, orders y order_items. La tabla order_items registra una fila por cada artículo de un pedido, por lo que la relación entre las tablas es de uno a varios. La relación es de uno a muchos porque un pedido puede tener muchos artículos, pero cada artículo solo puede formar parte de un pedido. Consulta la página de prácticas recomendadas Cómo usar correctamente el parámetro de relación para obtener información sobre cómo determinar la relación correcta de una unión.

En esta orders tabla, la suma de los valores de la columna total (SUM(total)) es igual a 124.84.

Supongamos que la tabla order_items contiene seis filas:

Obtener el número de artículos pedidos es fácil. La suma de los valores de la columna quantity (SUM(quantity)) es 7.

Ahora, supongamos que une las tablas orders y order_items mediante la columna compartida order_id. El resultado es la siguiente tabla:

La tabla anterior proporciona información nueva, como que se pidieron dos artículos el 1 de diciembre (2017-12-01 en la columna order_date) y cuatro artículos el 2 de diciembre (2017-12-02). Algunos de los cálculos anteriores, como los de SUM(quantity), siguen siendo válidos. Sin embargo, tendrás un problema si intentas calcular el total gastado.

Si usa el cálculo anterior, SUM(total), el valor total 50.36 de la nueva tabla de las filas en las que el valor de order_id es 1 se contabilizará dos veces, ya que el pedido incluye dos artículos diferentes (con valores de item_id de 50 y 63). El total de 24.12 de las filas en las que order_id es 2 se contabilizará tres veces, ya que este pedido incluye tres artículos diferentes. Por lo tanto, el resultado del cálculo SUM(total) de esta tabla es 223.44 en lugar de la respuesta correcta, que es 124.84.

Aunque es fácil evitar este tipo de errores cuando trabajas con dos tablas de ejemplo pequeñas, resolver este problema sería mucho más complicado en la vida real, con muchas tablas y muchos datos. Este es exactamente el tipo de error de cálculo que alguien podría cometer sin darse cuenta. Este es el problema que resuelven los agregados simétricos.

Cómo funcionan las agregaciones simétricas

Las agregaciones simétricas evitan que los analistas (y cualquier otra persona que use Looker) calculen mal por error agregaciones como sumas, medias y recuentos. Las agregaciones simétricas alivian en gran medida la carga de los analistas, ya que pueden confiar en que los usuarios no seguirán adelante con datos incorrectos. Los agregados simétricos lo hacen contando cada hecho en el cálculo el número correcto de veces, así como registrando lo que estás calculando.

En el ejemplo anterior, la función de agregación simétrica reconoce que total es una propiedad de orders (no de order_items), por lo que solo tiene que contar el total de cada pedido una vez para obtener la respuesta correcta. Para ello, la función usa una clave principal única que el analista ha definido en Looker. Esto significa que, cuando Looker hace cálculos en la tabla combinada, reconoce que, aunque haya dos filas en las que el valor de order_id sea 1, no debe contar el total dos veces porque ya se ha incluido en el cálculo, y que solo debe contar el total una vez en las tres filas en las que el valor de order_id sea 2.

Es importante tener en cuenta que los agregados simétricos dependen de una clave principal única y de que se especifique la relación de unión correcta en el modelo. Por lo tanto, si los resultados que obtienes no son correctos, habla con un analista para asegurarte de que todo esté configurado correctamente.

Por qué los agregados simétricos parecen complicados

La aparición de agregaciones simétricas puede ser un poco misteriosa. Sin agregaciones simétricas, Looker suele escribir código SQL correcto, como en el siguiente ejemplo:

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 las agregaciones simétricas, el SQL que escribe Looker podría ser similar al siguiente ejemplo:

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

El formato exacto que adoptan los agregados simétricos depende del dialecto de SQL que esté escribiendo Looker, pero todos los formatos hacen lo mismo: si varias filas tienen la misma clave principal, la función de agregados simétricos solo las cuenta una vez. Para ello, usa las funciones SUM DISTINCT y AVG DISTINCT, que son poco conocidas y forman parte del estándar SQL.

Para ver cómo se produce, puedes hacer el cálculo que has hecho antes con agregaciones simétricas. De las siete columnas de las tablas combinadas, solo necesitas dos: la que estás agregando (total) y la clave principal única de los pedidos (order_id).

Las agregaciones simétricas toman la clave principal (order_id en este caso) y crean un número muy grande para cada una, que es único y siempre da el mismo resultado para la misma entrada. (Por lo general, lo hace con una función de hash, cuyos detalles no se explican en esta página). El resultado sería similar al siguiente:

A continuación, Looker hace lo siguiente en cada fila:

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

De esta forma, obtendrá los totales agregados correctos, ya que cada total se contabilizará el número de veces exacto. La función de agregaciones simétricas de Looker no se ve afectada por las filas repetidas ni por varios pedidos que tengan el mismo total. Puedes hacer los cálculos tú mismo para entender mejor cómo funcionan las agregaciones simétricas.

El código SQL necesario para hacerlo no es muy atractivo: con CAST(), md5(), SUM(DISTINCT) y STRTOL(), no te interesaría escribir el código SQL a mano. Sin embargo, por suerte, no tienes que hacerlo, ya que Looker puede escribir el código SQL por ti.

Cuando una agregación funciona correctamente sin necesidad de agregaciones simétricas, Looker lo detecta automáticamente y no usa la función. Como los agregados simétricos implican ciertos costes de rendimiento, la capacidad de Looker para discernir cuándo usar y cuándo no usar agregados simétricos optimiza aún más el SQL que genera Looker y lo hace lo más eficiente posible, al tiempo que garantiza la respuesta correcta.