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

Ao desenvolver em um projeto, talvez você veja um erro como o seguinte em 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 é capaz de duplicar ou aninhar agregações, e essa tentativa aciona o erro.

Soluções

Há duas soluções possíveis:

1. Usar medidas não agregadas para executar instruções SQL não agregadas entre medidas.
2. Usar uma tabela derivada para aninhar agregações ou agregados duplos.

Como usar medições não agregadas

As medidas não agregadas, como type: yesno e type: number, são as únicas que podem fazer referência a outras medidas ou agregações. As medidas não agregadas não executam agregação e, portanto, não executam 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 dentro delas.

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

O exemplo a seguir usa um type: number para calcular a porcentagem de todos os pedidos que foram 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) ;;
}

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 no ciclo de vida do cliente ("valor médio de vida útil do cliente")? Isso requer dois níveis de agregações, a duplicação ou o aninhamento, incluindo:

1. Uma soma das vendas agrupadas por cliente

2. Uma média dessa soma

Para conseguir 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 acionará o erro porque a medida avg_customer_lifetime_value está realizando uma agregação na medida total_revenue, que já é uma agregação. Conforme discutido anteriormente, a maioria dos dialetos SQL acionarão um erro quando agregados duplos ou aninhados forem 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 dimensionalização de uma medição. Com uma tabela derivada, a medida total_lifetime_value se torna uma dimensão. Você pode criar uma medição 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 é mesclada a uma Análise, a medida average_customer_lifetime_value pode ser usada para realizar a análise desejada em uma Análise sem acionar qualquer erro.