瞭解對稱式匯總函式

Looker 的對稱匯總功能非常強大，不過，由於對稱匯總資料看起來有點嚇人，而且大多是在幕後發生，因此遇到這類資料時可能會感到困惑。本頁面提供對稱集合運算的下列資訊：

• 為何需要對稱式匯總函式
• 對稱匯總的運作方式
• 為什麼對稱式匯總函式看起來很複雜

為何需要對稱式匯總函式

SQL 是資料分析的語言，功能非常強大。但權力越大，責任越重，分析師有責任避免意外計算錯誤的匯總值，例如加總、平均值和計數。

這類計算很容易出錯，而這類錯誤計算可能會讓分析師感到非常挫折。以下範例說明可能發生的錯誤。

假設您有兩個資料表：orders 和 order_items。order_items 資料表會為訂單中的每項商品記錄一列，因此資料表之間的關係是一對多。這類關係是一對多，因為一個訂單可以包含多項商品，但每項商品只能屬於一筆訂單。如要瞭解如何判斷正確的彙整關聯，請參閱「正確設定關聯參數」最佳做法頁面。

在這個 orders 表格中，total 欄 (SUM(total)) 中的值總和等於 124.84。

假設 order_items 資料表包含六列：

取得訂購商品數量的方法很簡單。quantity 欄 (SUM(quantity)) 中值的總和為 7。

假設您要使用共用資料欄 order_id 彙整 orders 資料表和 order_items 資料表。這會產生下列表格：

上表提供新資訊，例如 12 月 1 日訂購了兩項商品 (order_date 欄中的 2017-12-01)，12 月 2 日訂購了四項商品 (2017-12-02)。部分先前的計算 (例如 SUM(quantity) 計算) 仍有效。不過，如果您嘗試計算總支出金額，就會遇到問題。

如果使用先前的計算方式 SUM(total)，新資料表中 order_id 值為 1 的資料列總值 50.36 會被計算兩次，因為訂單包含兩個不同的項目 (item_id 值為 50 和 63)。order_id 為 2 的資料列 24.12 總數會被計算三次，因為這筆訂單包含三個不同的項目。因此，這個資料表的計算 SUM(total) 結果為 223.44，而非正確答案 124.84。

雖然使用兩個小型範例資料表時，很容易避免這種錯誤，但在實際情況中，由於有許多資料表和大量資料，因此解決這類問題會更加複雜。這正是某人可能會犯的錯誤，而且自己都沒察覺。這就是對稱匯總解決的問題。

對稱匯總的運作方式

對稱匯總可避免分析師和其他使用者不小心誤算匯總值，例如加總、平均值和計數。對稱匯總資料可減輕分析師的負擔，因為分析師可以放心，使用者不會以錯誤的資料繼續進行。對稱匯總會確保計算中每個事實的次數正確無誤，並追蹤您要計算的項目。

在上一個範例中，對稱匯總函式會辨識 total 是 orders (而非 order_items) 的屬性，因此只需計算每個訂單的總數一次，即可取得正確答案。這個函式會使用分析師在 Looker 中定義的專屬主鍵來執行這項操作。也就是說，當 Looker 在已彙整的資料表上執行計算時，系統會認知，即使有兩個資料列的 order_id 值為 1，也不應將總計計算兩次，因為總計已納入計算，且對於 order_id 值為 2 的三個資料列，系統只應將總計計算一次。

值得注意的是，對稱匯總資料取決於唯一 主鍵，以及模型中指定的正確彙整關係。因此，如果您獲得的結果似乎有誤，請與分析師聯絡，確認所有設定都正確無誤。

為什麼對稱式匯總函式看起來複雜

對稱匯總的出現方式可能有點神秘。在沒有對稱匯總的情況下，Looker 通常會寫入良好的 SQL，例如以下範例：

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price"
FROM order_items AS order_items

GROUP BY 1,2
ORDER BY 1
LIMIT 500

使用對稱匯總時，SQL Looker 的寫入內容可能會類似以下範例：

SELECT
  order_items.order_id AS "order_items.order_id",
  order_items.sale_price AS "order_items.sale_price",
  (COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(users.age ,0)
  *(1000000*1.0)) AS DECIMAL(38,0))) +
  CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0)) )
  - SUM(DISTINCT CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))
  * 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))) ) AS DOUBLE PRECISION)
  / CAST((1000000*1.0) AS DOUBLE PRECISION), 0)
  / NULLIF(COUNT(DISTINCT CASE WHEN users.age IS NOT NULL THEN users.id
  ELSE NULL END), 0)) AS "users.average_age
FROM order_items AS order_items
LEFT JOIN users AS users ON order_items.user_id = users.id

GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 500

對稱匯總函式採用的確切格式取決於 Looker 所寫的 SQL 方言，但所有格式都會執行相同的基本操作：如果有多個資料列具有相同的主鍵，對稱匯總函式只會計算一次。這項功能會使用 SQL 標準中鮮為人知的 SUM DISTINCT 和 AVG DISTINCT 函式。

如要瞭解發生這種情況的原因，您可以使用對稱匯總函式來執行先前的計算。在已彙整的資料表中，您只需要兩個資料欄：要匯總的資料欄 (total) 和訂單的不重複主鍵 (order_id)。

對稱式匯總會採用主鍵 (在本例中為 order_id)，並為每個主鍵建立非常大的數字，保證這些數字皆為不重複的值，且在相同輸入內容下，一律會產生相同的輸出內容。(通常會透過雜湊函式執行這項操作，詳細資訊超出本頁範圍)。結果如下所示：

接著，Looker 會針對每個資料列執行以下操作：

SUM(DISTINCT big_unique_number + total) - SUM(DISTINCT big_unique_number)

這樣一來，您就能確實獲得正確的匯總總數，每個總數都會被計算正確次數。Looker 對稱匯總函式不會受到重複資料列或總計相同的多個訂單影響。您可以自行試算，進一步瞭解對稱集合運算的運作方式。

這項操作所需的 SQL 語法並不好看：使用 CAST()、md5()、SUM(DISTINCT) 和 STRTOL() 時，您肯定不會想手動編寫 SQL。不過，您不必親自撰寫 SQL 查詢，Looker 可以代勞。

如果不需要對稱匯總，匯總功能仍可正常運作，Looker 會自動偵測這類情況，並停止使用該函式。對稱匯總會造成一些效能成本，因此 Looker 能夠判斷何時使用、何時不使用對稱匯總，進一步最佳化 Looker 產生的 SQL，並盡可能提高效率，同時確保正確的答案。