Error: Las medidas con agregaciones de Looker (suma, media, mínimo, máximo y tipos de lista) no pueden hacer referencia a otras medidas

Mientras desarrollas un proyecto, puede que veas un error como el siguiente en un 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:

    SELECT AVG((COUNT(*)) ) AS users.average_count FROM demo_db.users  AS users

Las instrucciones SQL como esta generan una agregación doble o anidada en SQL. La mayoría de los dialectos de SQL no pueden realizar agregaciones dobles ni anidar agregaciones, por lo que este tipo de intentos activan el error.

Soluciones

Hay dos soluciones posibles:

1. Usa medidas no agregadas para realizar instrucciones SQL no agregadas entre medidas.
2. Usa una tabla derivada para anidar agregaciones o para agregar dos veces.

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 realizan una agregación doble o anidada. Las medidas de type: number o type: yesno actúan como marcadores de posición para que se puedan hacer referencia a otras medidas o combinaciones de medidas dentro de ellas.

Por ejemplo, las medidas de type: number se usan para hacer cálculos entre medidas y admiten cualquier expresión SQL válida que dé como resultado un número o un entero.

En el siguiente ejemplo se usa type: number para calcular el porcentaje de todos los pedidos que se han cancelado:

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) ;;
}

Usar una tabla derivada para agregaciones dobles o anidadas

Pero ¿qué ocurre si se necesita una agregación anidada para realizar un análisis? Por ejemplo, ¿qué ocurre si necesitas saber la cantidad media que gastan los clientes durante su tiempo de vida ("valor medio del tiempo de vida del cliente")? Para ello, se necesitan dos niveles de agregación (doble o anidada), que incluyen lo siguiente:

1. Suma de las ventas, agrupadas por cliente

2. Una media de esa suma

Para conseguirlo 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, se producirá un error porque la métrica avg_customer_lifetime_value está agregando la métrica total_revenue, que ya es una agregación. Como hemos comentado anteriormente, la mayoría de los dialectos de SQL activarán un error cuando se utilicen agregaciones dobles o anidadas en una consulta.

Para obtener la media 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 medida total_lifetime_value en un campo que se pueda agregar. En Looker, esto se denomina convertir una medida en dimensión. Con una tabla derivada, la total_lifetime_value medida se convierte en una dimensión. A continuación, puede 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 customer_facts tabla derivada 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 que se produzca ningún error.