Mientras desarrollas un proyecto, es posible que veas un error como el siguiente en Explorar o en el validador de LookML:
Measures with Looker aggregations (sum, average, min, max, list types) may not reference other measures.
Este error se debe a una medida agregada que hace referencia a otra agregación o medida de cualquier tipo en su definición de LookML, como las siguientes:
SELECT AVG((COUNT(*)) ) AS users.average_count FROM demo_db.users AS users
Las instrucciones de SQL como esta generan una agregación doble o anidada en SQL; la mayoría de los dialectos de SQL no pueden agregar dos veces o anidar agregaciones, por lo que este intento activa el error.
Soluciones
Existen dos soluciones posibles:
- Usar medidas no agregadas para realizar instrucciones de SQL no agregables entre las mediciones
- Usa una tabla derivada para anidar agregaciones o agregar doble.
Cómo usar medidas no agregadas
Las medidas no agregadas, como type: yesno
y type: number
, son las únicas mediciones que pueden hacer referencia a otras medidas o agregaciones. Las mediciones no agregadas no realizan ninguna agregación y, por lo tanto, no realizarán una agregación doble ni anidada. Las mediciones de type: number
o type: yesno
actúan como marcadores de posición para que se pueda hacer referencia a otras mediciones o combinaciones de mediciones dentro de ellas.
Por ejemplo, las medidas de type: number
se usan para realizar cálculos entre mediciones y tomar cualquier expresión SQL válida que dé como resultado un número o un número entero.
En el siguiente ejemplo, se usa un type: number
para calcular el porcentaje de todos los pedidos que se cancelan:
measure: order_count { # Base measure #1 type: count sql: ${order_id} ;; } measure: cancelled_orders { # Base measure #2 type: count filters: [status: "Cancelled"] } measure: percent_cancelled_orders { # New measure type: number sql: (1.0*${cancelled_orders})/ISNULL(${order_count},0) ;; }
Usa una tabla derivada para agregaciones dobles o anidadas
Pero, ¿qué sucede si se necesita una agregación anidada para realizar un análisis? Por ejemplo, ¿qué sucede si necesitas saber el importe promedio que invierten los clientes durante su ciclo de vida ("valor promedio del ciclo de vida del cliente")? Esto requiere dos niveles (la duplicación o anidación) de agregaciones, que incluye lo siguiente:
-
Una suma de ventas agrupadas por cliente
-
Un promedio de esa suma
Para lograrlo con LookML, puedes probar lo siguiente:
measure: total_revenue { type: sum sql: ${sale_price} ;; } measure: avg_customer_lifetime_value { type: average sql: ${total_revenue} ;; }
Sin embargo, esto activará el error porque la medición avg_customer_lifetime_value
está realizando una agregación en la medición total_revenue
, que ya es una agregación. Como se analizó anteriormente, la mayoría de los dialectos de SQL activarán un error cuando se usen los agregados dobles o anidados en una consulta.
Para obtener un promedio de la suma de total_revenue
en SQL, se necesita una subconsulta como la siguiente:
SELECT AVG(s.sum) FROM (SELECT SUM(x) as sum FROM ...) AS s
La solución equivalente en Looker es crear una tabla derivada que se “compacte” la medida total_lifetime_value
en un campo que se puede agregar. En Looker, esto se llama dimensionalización de una medida. Con una tabla derivada, la medida total_lifetime_value
se convierte en una dimensión. Luego, puedes crear una medida de type: average
que haga referencia a la dimensión customer_lifetime_value
:
view: customer_facts { derived_table: { sql: SELECT user_id, COALESCE(SUM(sale_price), 0) AS customer_lifetime_value FROM orders GROUP BY user_id;; } dimension: customer_lifetime_value { type: number sql: ${TABLE}."customer_lifetime_value" ;; } measure: average_customer_lifetime_value { type: average sql: ${customer_lifetime_value} ;; } }
Una vez que la tabla derivada customer_facts
se une a una exploración, se puede usar la medida average_customer_lifetime_value
para realizar el análisis deseado en una exploración sin activar ningún error.