오류: Looker 집계(합계, 평균, 최소, 최대, 목록 유형)가 포함된 측정은 다른 측정을 참조하지 않을 수 있음

프로젝트에서 개발하는 동안 Explore 또는 LookML 검사기에 다음과 같은 오류가 표시될 수 있습니다. 

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

이 오류는 다음과 같이 LookML 정의에 있는 다른 집계 또는 다른 측정값을 참조하는 집계 측정으로 인해 발생합니다. 

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

이러한 SQL 문은 SQL에서 이중 또는 중첩 집계를 생성합니다. 대부분의 SQL 언어는 집계를 이중 집계하거나 중첩 집계할 수 없으므로 이러한 시도로 인해 오류가 트리거됩니다.

솔루션

다음과 같은 두 가지 문제 해결 방법이 있습니다.

  1. 비집계 측정을 사용하여 측정 간에 비집계 SQL 문을 수행합니다.
  2. 파생된 테이블을 사용하여 집계를 중첩하거나 이중 집계를 수행합니다.

비집계 측정 사용

type: yesno 및 type: number와 같은 비집계 측정은 다른 측정 또는 집계를 참조할 수 있는 유일한 측정값입니다. 비집계 측정은 집계를 수행하지 않으므로 이중 또는 중첩 집계를 수행하지 않습니다. type: number 또는 type: yesno 측정값은 자리표시자 역할을 하므로 다른 측정 또는 측정값 조합을 참조할 수 있습니다.

예를 들어 type: number 측정은 측정값을 계산하고 숫자 또는 정수를 생성하는 유효한 SQL 표현식을 수행하는 데 사용됩니다.

다음 예시에서는 type: number를 사용하여 취소된 모든 주문의 비율을 계산합니다. 

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

이중 또는 중첩 집계에 파생 테이블 사용

하지만 분석을 수행하는 데 중첩된 집계가 필요한 경우에는 어떻게 해야 할까요? 예를 들어 고객이 고객 평생 동안 지출한 평균 금액('평균 고객 평생 가치')을 알아야 하는 경우에는 어떻게 해야 하나요? 이를 위해서는 다음을 포함하여 두 가지 집계 수준(이중 또는 중첩)이 필요합니다.

  1. 고객별로 그룹화한 판매 합계

  2. 이 합계의 평균

이를 위해 LookML을 사용하려면 다음을 시도해 보세요. 

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

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

하지만 avg_customer_lifetime_value 측정이 이미 집계된 total_revenue 측정값에서 집계를 수행하므로 오류가 발생합니다. 앞에서 설명한 것처럼 대부분의 SQL 언어는 쿼리에 이중 또는 중첩 집계가 사용될 때 오류를 트리거합니다.

SQL에서 total_revenue 합계의 평균을 달성하기 위해서는 다음과 같은 서브 쿼리가 필요합니다. 

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

Looker에서 동일한 솔루션은 total_lifetime_value 측정값을 집계 가능한 필드로 '평면화'하는 파생된 테이블을 만드는 것입니다. Looker에서는 이를 측정값 측정기준화라고 합니다. 파생된 테이블을 사용하면 total_lifetime_value 측정값이 측정기준이 됩니다. 그런 다음 customer_lifetime_value 측정기준을 참조하는 type: average 측정값을 만들 수 있습니다. 

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

customer_facts 파생 테이블이 Explore에 조인되면 average_customer_lifetime_value 측정값을 사용하여 오류 없이 트리거에서 원하는 분석을 수행할 수 있습니다.