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:
- Use medidas não agregadas para executar instruções SQL não agregadas entre medidas.
- Usar uma tabela derivada para aninhar agregações ou agregação dupla.
Como usar medições 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 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) ;; }
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 um aninhamento, incluindo:
-
Uma soma das vendas agrupadas por cliente
-
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 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. 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.