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 produce cuando una medida agregada hace referencia a otra agregación o medida de cualquier tipo en su definición de LookML, como en los siguientes casos:
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 realizar una agregación doble ni 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 realizar una doble agregación.
Uso de medidas no agregadas
Las medidas no agregadas, como type: yesno y type: number, son las únicas que pueden hacer referencia a otras medidas o agregaciones. Las medidas no agregadas no realizan ninguna agregación y, por lo tanto, no realizarán una agregación doble ni anidada. Las medidas de type: number o type: yesno actúan como marcadores de posición para que se pueda hacer referencia a otras medidas o combinaciones de medidas 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 las 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 conocer el importe promedio que los clientes gastan durante su ciclo de vida ("valor del ciclo de vida promedio del cliente")? Esto requiere dos niveles (una duplicación o anidación) de agregaciones, incluidos los siguientes:
-
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 realiza una agregación en la medición total_revenue, que ya es una agregación. Como se mencionó anteriormente, la mayoría de los dialectos de SQL activarán un error cuando se usen agregaciones dobles o anidadas 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 para “aplanar” la medición total_lifetime_value en un campo que se pueda agregar. En Looker, esto se denomina dimensionalizar una medición. 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.