在專案開發期間，您可能會在「探索」或 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 方言無法執行雙重匯總或巢狀匯總，因此這類嘗試會觸發錯誤。
解決方案
有兩種可能的解決方法：
使用非匯總指標
非匯總指標 (例如 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) ;;
}
使用衍生資料表進行雙重或巢狀匯總
但如果執行分析時需要巢狀匯總，該怎麼辦？舉例來說，如果您需要瞭解顧客在整個顧客生命週期內的平均支出金額 (即「平均顧客終身價值」)，這需要兩個層級的匯總 (雙重或巢狀)，包括：
-
按客戶分組的銷售額總和
-
該加總的平均值
如要使用 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 評估資料，在探索中執行所需分析，且不會觸發任何錯誤。