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

Durante o desenvolvimento de um projeto, talvez você encontre um erro como este em um Explore ou no Validador do LookML:

  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 fazer agregação dupla ou aninhar agregações. Portanto, 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 medidas.
2. Use uma tabela derivada para aninhar agregações ou agregação dupla.

Usar medidas não agregadas

As medições não agregadas, como type: yesno e type: number, são as únicas que podem fazer referência a outras medições ou agregações. As medidas não agregadas não realizam nenhuma agregação e, portanto, não realizam uma agregação dupla ou aninhada. As medidas de type: number ou type: yesno funcionam como marcadores de posição para que outras medidas ou combinações de medidas possam ser referenciadas nelas.

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

O exemplo a seguir 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 do cliente ("valor da vida útil do cliente médio")? Isso requer dois níveis de agregação, ou seja, uma duplicação ou aninhamento, incluindo:

1. Uma soma de vendas agrupadas por cliente

2. Uma média dessa soma

Para fazer isso com o LookML, tente:

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 medida total_revenue, que já é uma agregação. Como discutido anteriormente, a maioria dos dialetos SQL vai acionar um erro quando agregados aninhados ou duplos forem usados em uma consulta.

Para calcular a média da soma 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 "achatar" a métrica total_lifetime_value em um campo que possa ser agregado. No Looker, isso é chamado de dimensionalização de uma métrica. Com uma tabela derivada, a medida total_lifetime_value se torna uma dimensão. Em seguida, crie 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 customer_facts for combinada em uma Análise detalhada, a medida average_customer_lifetime_value poderá ser usada para realizar a análise desejada sem gerar erros.