Looker 的對稱匯總功能非常強大,不過,由於對稱匯總資料看起來有點嚇人,而且大多是在幕後發生,因此遇到這類資料時可能會感到困惑。本頁面提供對稱集合運算的下列資訊:
為何需要對稱式匯總函式
SQL 是資料分析的語言,功能非常強大。但權力越大,責任越重,分析師有責任避免意外計算錯誤的匯總值,例如加總、平均值和計數。
這類計算很容易出錯,而這類錯誤計算可能會讓分析師感到非常挫折。以下範例說明可能發生的錯誤。
假設您有兩個資料表:orders
和 order_items
。order_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
值為 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 |
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,並盡可能提高效率,同時確保正確的答案。