Mientras desarrollas en un proyecto, es posible que veas un error como el siguiente en una exploración 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.
Uso de 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 una agregación anidada es necesaria 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 (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 lograr 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.