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

Ao desenvolver um projeto, você pode encontrar um erro como este em uma ferramenta Explore ou no LookML Validator:

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

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

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

Instruções SQL como essa geram uma agregação dupla ou aninhada no SQL. A maioria dos dialetos SQL não consegue duplicar a agregação ou aninhar agregações, então essa tentativa aciona o erro.

Soluções

Há duas soluções possíveis:

1. Use medidas não agregadas para executar instruções SQL não agregadas entre as medidas.
2. Use uma tabela derivada para aninhar agregações ou duplas.

Como 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 nenhuma agregação e, portanto, não realizarão 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.

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

O exemplo abaixo usa um type: number para calcular a porcentagem de todos os pedidos cancelados:

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

Como usar uma tabela derivada para agregações duplas ou aninhadas

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

1. Uma soma das vendas agrupadas por cliente

2. Uma média dessa soma

Para fazer isso com o LookML, você pode tentar:

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

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

No entanto, isso vai acionar o erro porque a medida avg_customer_lifetime_value está realizando uma agregação na total_revenue, que já é uma agregação. Como discutido anteriormente, a maioria dos dialetos SQL dispara um erro quando agregados duplos ou aninhados são usados em uma consulta.

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

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

A solução equivalente no Looker é criar uma tabela derivada para nivelar a medida total_lifetime_value em um campo que pode ser agregado. No Looker, isso é chamado de dimensionar uma medida. Com uma tabela derivada, a medida total_lifetime_value se torna uma dimensão. Você 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 que a tabela derivada de customer_facts é mesclada a uma Análise, a medida average_customer_lifetime_value pode ser usada para realizar a análise desejada sem acionar nenhum erro.