Error: Es posible que las medidas con agregaciones de Looker (suma, promedio, mínimo, máximo y tipos de lista) no hagan referencia a otras medidas.

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 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:

1. Usa medidas no agregadas para realizar instrucciones de SQL no agregadas entre medidas.
2. Usa una tabla derivada para anidar agregaciones o realizar una doble agregación.

Cómo usar 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 medidas y tomar cualquier expresión de SQL válida que genere 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 se necesita una agregación anidada 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:

1. Es la suma de las ventas, agrupadas por cliente.

2. 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 medida avg_customer_lifetime_value realiza una agregación en la medida 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 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 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 medición 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, la medida average_customer_lifetime_value se puede usar para realizar el análisis deseado en una exploración sin activar ningún error.