Comprende las agregaciones simétricas

Los agregados simétricos en Looker son una función muy poderosa. Sin embargo, debido a que los agregados simétricos pueden parecer un poco intimidantes y, sobre todo, suceden detrás de escena, puede ser un poco confuso encontrarlos. En esta página, se proporciona la siguiente información sobre los agregados simétricos:

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

Por qué se necesitan las agregaciones simétricas

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

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

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

En esta tabla orders, 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 recuento de artículos pedidos es fácil. La suma de los valores de la columna quantity (SUM(quantity)) es 7.

Ahora, supongamos que unes la tabla orders y la tabla order_items con su columna compartida, order_id. Esto da como resultado la siguiente tabla:

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

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

Si bien es fácil evitar este tipo de error cuando trabajas con dos pequeñas tablas de ejemplo, 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 cálculo erróneo que alguien podría cometer sin siquiera darse cuenta. Este es el problema que resuelven los agregados simétricos.

Cómo funcionan los agregados simétricos

Los agregados simétricos evitan que los analistas (y cualquier otra persona que use Looker) calculen agregados de forma accidental, como sumas, promedios y recuentos, de forma errónea. Los agregados simétricos ayudan a aliviar una enorme carga de trabajo de los analistas hombros, porque los analistas pueden confiar en que los usuarios no se quedarán con datos incorrectos. Para esto, los agregados simétricos se aseguran de registrar cada hecho en el cálculo la cantidad correcta de veces y de llevar un registro de lo que se calcula.

En el ejemplo anterior, la función de agregados simétricos reconoce que total es una propiedad de orders (no de order_items), por lo que debe contar el total de cada pedido solo una vez para obtener la respuesta correcta. La función hace esto usando una clave primaria única que el analista definió en Looker. Eso significa que, cuando Looker realiza cálculos en la tabla unida, reconoce que, si bien hay dos filas en las que el valor de order_id es 1, no debería contar el total dos veces porque ese total ya se incluyó en el cálculo y que solo debería contar el total una vez para las tres filas en las que el valor de order_id es 2.

Vale la pena señalar que los agregados simétricos dependen de una clave primaria única y que se especifique la relación de unión correcta en el modelo. Por lo tanto, si los resultados que obtienes parecen incorrectos, habla con un analista para asegurarte de que todo esté configurado correctamente.

Por qué los agregados simétricos parecen complicados

La apariencia de los agregados simétricos puede ser un poco desconcertante. Sin agregaciones simétricas, Looker suele escribir SQL agradable y bien comportado, 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 los agregados simétricos, las escrituras de SQL de Looker podrían parecerse 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 toman los agregados simétricos depende del dialecto de SQL que escribe Looker, pero todos los formatos hacen lo mismo básico: si varias filas tienen la misma clave primaria, la función de las agregaciones simétricas solo las cuenta una vez. Para ello, usa las funciones poco conocidas SUM DISTINCT y AVG DISTINCT que forman parte del estándar de SQL.

Para ver cómo sucede esto, puedes tomar el cálculo que hiciste anteriormente y trabajarlo con agregados simétricos. De las siete columnas de las tablas unidas, solo necesitas dos: la que agregas (total) y la clave primaria única de los pedidos (order_id).

Los agregados simétricos toman la clave primaria (order_id, en este caso) y crean un número muy grande para cada una, que se garantiza que sea único y siempre proporcione el mismo resultado para la misma entrada. (Por lo general, lo hace con una función hash, cuyos detalles están fuera del alcance de esta página). Ese resultado se vería similar al siguiente:

Luego, para cada fila, Looker hace lo siguiente:

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

Esto te da de forma confiable los totales agregados correctamente, al contar cada total exactamente la cantidad correcta de veces. La función de agregación simétrica de Looker no se ve engañada por filas repetidas o varios pedidos que tienen el mismo total. Puedes intentar hacer los cálculos por tu cuenta para comprender mejor cómo funcionan los agregados simétricos.

El SQL necesario para hacer esto no es el más atractivo: con CAST(), md5(), SUM(DISTINCT) y STRTOL(), ciertamente no querrías escribir el SQL de forma manual. Pero, por suerte, no es necesario, ya que Looker puede escribir el código SQL por ti.

Cuando una agregación funcionará correctamente sin la necesidad de agregaciones simétricas, Looker lo detectará automáticamente y no usará la función. Debido a que los agregados simétricos imponen algunos costos de rendimiento, la capacidad de Looker para distinguir cuándo se debe usar y cuándo no, los agregados simétricos optimizan aún más el SQL que genera Looker y lo hacen lo más eficiente posible sin dejar de garantizar la respuesta correcta.