錯誤：含有 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 評估資料，在探索中執行所需分析，且不會觸發任何錯誤。