Mientras desarrollas en un proyecto, es posible que veas un error como el siguiente en un explorador o en el Validador de Looker:
Measures with Looker aggregations (sum, average, min, max, list types) may not reference other measures.
Este error se produce por una medida agregada que hace referencia a otra agregación o medida de cualquier tipo en su definición de LookML, como la siguiente:
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 doblemente ni anidar agregaciones, por lo que un intento de este tipo activa el error.
Soluciones
Hay dos soluciones posibles:
- Usa medidas no agregadas para realizar instrucciones de SQL no agregadas entre mediciones.
- Usar una tabla derivada para anidar agregaciones o agregar dobles
Usa medidas no agregadas
Las medidas no agregadas, como type: yesno y type: number, son las únicas medidas 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 o 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 medidas dentro de ellas.
Por ejemplo, las medidas de type: number se usan para realizar cálculos entre medidas y tomar cualquier expresión de 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é pasa si es necesaria una agregación anidada para realizar un análisis? Por ejemplo, ¿qué sucede si necesitas conocer el importe promedio que invierten los clientes a lo largo del ciclo de vida del cliente ("valor promedio del ciclo de vida del cliente")? Esto requiere dos niveles de agregaciones, de duplicación o anidación, que incluyen lo siguiente:
-
Una suma de las ventas agrupadas por cliente
-
Un promedio de esa suma
Para lograrlo con LookML, puedes intentar 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 medida avg_customer_lifetime_value realiza una agregación en la medición total_revenue, que ya es una agregación. Como se explicó anteriormente, la mayoría de los dialectos de SQL activarán un error cuando se utilicen agregados dobles o anidados en una consulta.
Para lograr un promedio de la suma 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 "compactar" la medición total_lifetime_value en un campo que se pueda agregar. En Looker, esto se denomina dimensionamiento de una medida. Con una tabla derivada, la medida total_lifetime_value se convierte en una dimensión. Luego, puedes crear una medición 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 medición average_customer_lifetime_value para realizar el análisis deseado en una exploración sin activar ningún error.