错误：使用 Looker 汇总（求和、平均值、最小值、最大值、列表类型）的测量值不得引用其他测量值

在项目中进行开发时，您可能会在探索或 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 指标执行了汇总，而 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 派生表联接到“探索”后，您就可以使用 average_customer_lifetime_value 测量参数在“探索”中执行所需的分析，而不会触发任何错误。