错误：使用 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 测量执行聚合，而该测量已属于聚合。如前所述，在查询中使用双精度或嵌套聚合时，大多数 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 测量在“探索”中执行所需的分析，而不会触发任何错误。