Información sobre las agregaciones simétricas

Las agrupaciones simétricas en Looker son una función muy potente. 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

SQL, el lenguaje del análisis de datos, es extremadamente poderoso. 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 estos tipos de cálculos incorrectos pueden 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 para 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 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 elegir el parámetro de relación correcto para obtener orientación sobre cómo determinar la relación correcta para una unión.

En este ejemplo, supongamos que la tabla orders se ve de la siguiente manera:

order_id user_id total order_date
1 100 $ 50.36 2017-12-01
2 101 USD 24.12 2017-12-02
3 137 $ 50.36 2017-12-02

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:

order_id item_id quantity unit_price
1 50 1 $ 23.00
1 63 2 USD 13.68
2 63 1 USD 13.68
2 72 1 USD 5.08
2 79 1 $ 5.36
3 78 1 $ 50.36

Obtener la cantidad 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 genera la siguiente tabla:

order_id user_id total order_date item_id quantity unit_price
1 100 $ 50.36 2017-12-01 50 1 $ 23.00
1 100 $ 50.36 2017-12-01 63 2 USD 13.68
2 101 USD 24.12 2017-12-02 63 1 USD 13.68
2 101 USD 24.12 2017-12-02 72 1 USD 5.08
2 101 USD 24.12 2017-12-02 79 1 $ 5.36
3 137 $ 50.36 2017-12-02 78 1 USD 50.36

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 cálculos de SUM(quantity), siguen siendo válidos. Sin embargo, tendrás un problema si intentas calcular el total invertido.

Si usas el cálculo anterior, SUM(total), el valor total 50.36 en 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 artículos 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 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 los agregados simétricos

Las agregaciones simétricas evitan que los analistas y cualquier otra persona que use Looker calculen por error agregaciones como sumas, promedios y recuentos. 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 ello, se aseguran de contar cada hecho en el cálculo la cantidad correcta de veces y de hacer un seguimiento de lo que se está calculando.

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, aunque hay dos filas en las que el valor de order_id es 1, no debe contar el total dos veces porque ese total ya se incluyó en el cálculo y que solo debe 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é las agregaciones simétricas parecen complicadas

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 las agregaciones simétricas, las operaciones de escritura de SQL de Looker podrían verse como en el 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 SUM DISTINCT y AVG DISTINCT poco conocidas 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 estás agregando (total) y la clave primaria única para pedidos (order_id).

order_id total
1 $ 50.36
1 USD 50.36
2 $ 24.12
2 $ 24.12
2 USD 24.12
3 $ 50.26

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:

big_unique_number total
802959190063912 USD 50.36
802959190063912 $ 50.36
917651724816292 $ 24.12
917651724816292 $ 24.12
917651724816292 $ 24.12
110506994770727 $ 50.36

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 la sentencia SQL por ti.

Cuando una agregación funcionará correctamente sin necesidad de agregaciones simétricas, Looker lo detectará automáticamente y no usará la función. Debido a que las agregaciones simétricas imponen algunos costos de rendimiento, la capacidad de Looker para discernir cuándo usarlas y cuándo no, optimiza aún más el SQL que genera Looker y lo hace lo más eficiente posible sin dejar de garantizar la respuesta correcta.