Erro: as medidas com agregações do Looker (soma, média, mínimo, máximo, tipos de listas) podem não fazer referência a outras medidas

Durante o desenvolvimento num projeto, pode ver um erro como o seguinte numa exploração ou no validador de LookML:

  Measures with Looker aggregations (sum, average, min, max, list types) may not reference other measures.

Este erro é causado por uma medida agregada que faz referência a outra agregação ou medida de qualquer tipo na respetiva definição do LookML, como:

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

As declarações SQL, como esta, geram uma agregação dupla ou aninhada em SQL. A maioria dos dialetos SQL não consegue fazer uma agregação dupla nem aninhar agregações, pelo que uma tentativa deste tipo aciona o erro.

Soluções

Existem duas soluções possíveis:

1. Use medidas não agregadas para executar declarações SQL não agregadoras entre medidas.
2. Use uma tabela derivada para aninhar agregações ou agregar duas vezes.

Usar medidas não agregadas

As medidas não agregadas, como type: yesno e type: number, são as únicas medidas que podem fazer referência a outras medidas ou agregações. As medidas não agregadas não realizam qualquer agregação e, por isso, não realizam uma agregação dupla ou aninhada. As medidas de type: number ou type: yesno atuam como marcadores de posição para que outras medidas ou combinações de medidas possam ser referenciadas nas mesmas.

Por exemplo, as medidas de type: number são usadas para fazer cálculos entre medidas e aceitar qualquer expressão SQL válida que resulte num número ou num inteiro.

O exemplo seguinte usa um type: number para calcular a percentagem de todas as encomendas que são canceladas:

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 uma tabela derivada para agregações duplas ou aninhadas

Mas e se for necessária uma agregação aninhada para realizar uma análise? Por exemplo, e se precisar de saber o valor médio que os clientes gastam durante o seu ciclo de vida ("valor do cliente médio")? Isto requer dois níveis, ou seja, uma duplicação ou aninhamento, de agregações, incluindo:

1. Uma soma das vendas, agrupadas por cliente

2. Uma média dessa soma

Para alcançar este objetivo com o LookML, pode experimentar:

measure: total_revenue {
    type: sum
    sql: ${sale_price} ;;
}

measure: avg_customer_lifetime_value {
    type: average
    sql: ${total_revenue} ;;
}

No entanto, isto aciona o erro porque a medida avg_customer_lifetime_value está a fazer uma agregação na medida total_revenue, que já é uma agregação. Conforme abordado anteriormente, a maioria dos dialetos SQL aciona um erro quando são usados agregados duplos ou aninhados numa consulta.

Para alcançar uma média da soma no SQL, é necessária uma subconsulta, como a seguinte:total_revenue

  SELECT AVG(s.sum) FROM (SELECT SUM(x) as sum FROM ...) AS s

A solução equivalente no Looker é criar uma tabela derivada para "aplanar" a medida total_lifetime_value num campo que possa ser agregado. No Looker, isto chama-se tornar uma medida dimensional. Com uma tabela derivada, a medida total_lifetime_value torna-se uma dimensão. Em seguida, pode criar uma medida de type: average que faça referência à dimensão 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} ;;
    }
}

Depois de a tabela derivada customer_facts ser associada a um elemento Explorar, a medida average_customer_lifetime_value pode ser usada para realizar a análise pretendida num elemento Explorar sem acionar nenhum erro.