瞭解對稱式匯總函式

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

為何需要對稱式匯總函式

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

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

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

在本例中,假設 orders 資料表如下所示:

order_id user_id total order_date
1 100 $ 50.36 美元 2017-12-01
2 101 $ 24.12 美元 2017-12-02
3 137 $ 50.36 美元 2017-12-02

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

假設 order_items 資料表包含六列:

order_id item_id quantity unit_price
1 50 1 $ 23.00 美元
1 63 2 $ 13.68 美元
2 63 1 $ 13.68 美元
2 72 1 $ 5.08 美元
2 79 1 $ 5.36 美元
3 78 1 $ 50.36 美元

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

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

order_id user_id total order_date item_id quantity unit_price
1 100 $ 50.36 美元 2017-12-01 50 1 $ 23.00 美元
1 100 $ 50.36 美元 2017-12-01 63 2 $ 13.68 美元
2 101 $ 24.12 美元 2017-12-02 63 1 $ 13.68 美元
2 101 $ 24.12 美元 2017-12-02 72 1 $ 5.08 美元
2 101 $ 24.12 美元 2017-12-02 79 1 $ 5.36 美元
3 137 $ 50.36 美元 2017-12-02 78 1 $ 50.36 美元

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

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

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

對稱匯總的運作方式

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

在上一個範例中,對稱匯總函式會辨識 totalorders (而非 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 DISTINCTAVG DISTINCT 函式。

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

order_id total
1 $ 50.36 美元
1 $ 50.36 美元
2 $ 24.12 美元
2 $ 24.12 美元
2 $ 24.12 美元
3 $ 50.26 美元

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

big_unique_number total
802959190063912 $ 50.36 美元
802959190063912 $ 50.36 美元
917651724816292 $ 24.12 美元
917651724816292 $ 24.12 美元
917651724816292 $ 24.12 美元
110506994770727 $ 50.36 美元

接著,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,並盡可能提高效率,同時確保正確的答案。