Los agregados simétricos en Looker son una función muy poderosa. Sin embargo, debido a que las agregaciones simétricas pueden parecer un poco intimidantes y en su mayoría ocurren detrás de escena, encontrarlas puede ser un poco confuso. En esta página, se proporciona la siguiente información sobre las agregaciones simétricas:
- Por qué se necesitan las agregaciones simétricas
- Cómo funcionan las agregaciones simétricas
- Por qué las agregaciones simétricas parecen complicadas
Por qué se necesitan las agregaciones simétricas
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 accidentalmente 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 muestra 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 uno de ellos 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.
orders se ve de la siguiente manera:
order_id |
user_id |
total |
order_date |
|---|---|---|---|
| 1 | 100 | $ 50.36 | 2017-12-01 |
| 2 | 101 | $ 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 | $ 13.68 |
| 2 | 63 | 1 | $ 13.68 |
| 2 | 72 | 1 | $ 5.08 |
| 2 | 79 | 1 | $ 5.36 |
| 3 | 78 | 1 | $ 50.36 |
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 mediante su columna compartida, order_id. Esto da como resultado 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 | $ 13.68 |
| 2 | 101 | $ 24.12 | 2017-12-02 | 63 | 1 | $ 13.68 |
| 2 | 101 | $ 24.12 | 2017-12-02 | 72 | 1 | $ 5.08 |
| 2 | 101 | $ 24.12 | 2017-12-02 | 79 | 1 | $ 5.36 |
| 3 | 137 | $ 50.36 | 2017-12-02 | 78 | 1 | $ 50.36 |
La tabla anterior proporciona información nueva, como la fecha en que se ordenaron dos elementos el 1 de diciembre (2017-12-01 en la columna order_date) y cuatro elementos 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, encontrará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 elementos diferentes (con item_id valores 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. En consecuencia, 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 se trabaja 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 error de cálculo que alguien podría cometer sin siquiera darse cuenta. Este es el problema que resuelven las agregaciones simétricas.
Cómo funcionan las agregaciones simétricas
Las agregaciones simétricas evitan que los analistas (y cualquier otra persona que use Looker) calculen erróneamente conjuntos, como sumas, promedios y recuentos. Las agregaciones simétricas ayudan a quitar una gran carga de los hombros de los analistas, porque estos pueden confiar en que los usuarios no cobrarán por delante con datos incorrectos. Las agregaciones simétricas hacen esto asegurándose de contar cada dato en el cálculo el número correcto de veces, así como al hacer un seguimiento de 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 debe contar el total de cada pedido solo una vez para obtener la respuesta correcta. La función lo hace 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 haya 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 solo debería hacerlo 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 de la relación de unión correcta que se especifica en el modelo. Por lo tanto, si los resultados no parecen correctos, 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 desconcertar un poco. Sin agregaciones simétricas, Looker suele escribir un SQL agradable y sencillo, 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 agregaciones simétricas, 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 las agregaciones simétricas 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 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, puedes tomar el cálculo que hiciste anteriormente y trabajar en él con agregaciones simétricas. De las siete columnas de las tablas unidas, solo necesitas dos: la que estás agregando (total) y la clave primaria única para los pedidos (order_id).
order_id |
total |
|---|---|
| 1 | $ 50.36 |
| 1 | $ 50.36 |
| 2 | $ 24.12 |
| 2 | $ 24.12 |
| 2 | $ 24.12 |
| 3 | $ 50.26 |
Las agregaciones simétricas toman la clave primaria (en este caso, order_id) y crean un número muy grande para cada una, que es única y siempre proporciona la misma salida para la misma entrada. (En general, lo hace con una función hash, cuyos detalles están fuera del alcance de esta página). El resultado se vería como el siguiente:
big_unique_number |
total |
|---|---|
| 802959190063912 | $ 50.36 |
| 802959190063912 | $ 50.36 |
| 917651724816292 | $ 24.12 |
| 917651724816292 | $ 24.12 |
| 917651724816292 | $ 24.12 |
| 110506994770727 | $ 50.36 |
Luego, para cada fila, Looker hará lo siguiente:
SUM(DISTINCT big_unique_number + total) - SUM(DISTINCT big_unique_number)
De este modo, obtienes los totales agregados correctamente y se cuenta cada total la cantidad correcta de veces. La función de agregación simétrica de Looker no se deja de engañar por las filas repetidas ni por múltiples pedidos que tienen el mismo total. Puedes intentar hacer los cálculos tú mismo para tener una mejor idea de cómo funcionan las agregaciones simétricas.
El SQL necesario para hacer esto no es el más atractivo a la vista: con CAST(), md5(), SUM(DISTINCT) y STRTOL(), ciertamente no querrás escribir el SQL a mano. Pero, por suerte, no tienes que hacerlo; Looker puede escribir el SQL por ti.
Cuando una agregación funciona correctamente sin la necesidad de agregar agregaciones simétricas, Looker detecta esto automáticamente y no usa la función. Debido a que las agregaciones simétricas imponen algunos costos de rendimiento, la capacidad de Looker para determinar cuándo usar y cuándo no, optimiza aún más el SQL que genera Looker y lo hace lo más eficiente posible, a la vez que garantiza la respuesta correcta.