本頁面適用於嘗試使用 LookML 在 Looker 中建立「探索」的使用者。如果您精通 SQL,尤其是瞭解內部和外部聯結的差異,就更容易理解本頁內容。如要簡要瞭解內部和外部彙整的差異,請參閱 w3schools 的這篇文章,瞭解 SQL 彙整。
Looker 可為貴公司提供強大的 SQL 引擎。LookML 中的抽象建模功能可讓資料和 IT 團隊建立一律為真的一般規則,讓業務分析師可以自由建立查詢,即使資料團隊從未預期需要這些查詢,也能確保查詢結果正確無誤。這項功能的核心驅動力是對稱的匯總演算法,可解決業界普遍的 SQL 彙整問題。不過,您必須正確執行兩項操作,才能充分運用演算法:在包含指標的每個檢視畫面中 (通常是所有檢視畫面),主鍵都必須正確無誤;在每個彙整作業中,relationship
參數也必須正確無誤。
主要金鑰
在許多情況下,瞭解資料表的主鍵,就等同於瞭解資料表的用途和可能的用途。唯一需要符合的條件是,您選擇做為主鍵的資料欄 (或一組連接的資料欄) 不得有重複的值。
relationship
參數
主鍵已完成驗證,您可以決定彙整作業的 relationship
參數的正確值。relationship
參數的用途是告訴 Looker,在將彙整寫入 SQL 查詢時,是否要叫用對稱的彙整。這裡可行的做法是告訴 Looker 一律要叫用這些函式,這樣就能一律產生準確的結果。不過,這會導致效能成本,因此建議您謹慎使用對稱集合。
內部和外部彙整的正確值判斷程序略有不同。
內部彙整
舉例來說,假設您有一個訂單資料表,主鍵為 order_id
:
order_id | amount | customer_id |
---|---|---|
1 | $25.00 美元 | 1 |
2 | $50.00 | 1 |
3 | $75.00 | 2 |
4 | $35.00 | 3 |
假設您也有一個客戶資料表,主鍵為 customer_id
:
customer_id | first_name | last_name | 造訪 |
---|---|---|---|
1 | Amelia | Earhart | 2 |
2 | Bessie | Coleman | 2 |
3 | Wilbur | Wright | 4 |
您可以使用 customer_id
欄位彙整這兩個資料表,因為這兩個資料表都含有這個欄位。這項彙整作業在 LookML 中的表示方式如下:
explore: orders { join: customers { type: inner sql_on: ${orders.customer_id} = ${customers.customer_id} ;; relationship: many_to_one } }
這個 LookML 彙整結果可表示為單一彙整資料表,如下所示:
order_id | amount | customer_id | customer_id | first_name | last_name | 造訪 |
---|---|---|---|---|---|---|
1 | $25.00 美元 | 1 | 1 | Amelia | Earhart | 2 |
2 | $50.00 | 1 | 1 | Amelia | Earhart | 2 |
3 | $75.00 | 2 | 2 | Bessie | Coleman | 2 |
4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
這裡的 many_to_one
關係是指在每個資料表中,一個值的聯結欄位 (customer_id
) 出現的次數。在 orders
資料表 (左側資料表) 中,單一客戶 ID 會多次顯示 (在本例中,這位於多個資料列中的客戶 ID 為 1
)。
在 customers
資料表 (右側資料表) 中,每個客戶 ID 只會出現一次,因為 customer_id
是該資料表的主鍵。因此,orders
資料表中的記錄可能會與 customers
資料表中的單一值相符。如果 customer_id
在 customers
資料表的每個資料列中皆不重複,則關係為 many_to_many
。
您可以按照下列步驟,透過檢查主鍵,以程式輔助方式判斷正確的關聯值:
- 首先,請將
many_to_many
寫為關係。只要主鍵正確,Looker 就會一律觸發對稱匯總演算法並確保準確度,因此一律會產生正確的結果。不過,由於演算法會使查詢變得複雜,並增加執行時間,因此建議您嘗試將一或兩側的many
變更為one
。 - 請查看左側表格中
sql_on
子句中的欄位。如果欄位或多個欄位是左側資料表的主鍵,您可以將relationship
參數的左側變更為one
。如果不是,通常必須保持many
。(如需特殊情況的相關資訊,請參閱本頁後續的「注意事項」一節)。 - 接著,請查看
sql_on
子句中代表右側資料表的欄位。如果欄位或欄位組成右側資料表的主鍵,您可以將右側變更為one
。
最佳做法是從左邊表格開始撰寫 sql_on
片語,這個表格位於等號左側,右邊表格則位於右側。sql_on
參數中的條件順序不重要,除非順序與資料庫的 SQL 方言相關。雖然 sql_on
參數不要求您以這種方式排序欄位,但安排 sql_on
條件,讓等號左側和右側與 relationship
參數從左至右的讀取方式相符,有助於您判斷關係。以這種方式排序欄位,有助於您一眼就能分辨要與新資料表彙整的探索中現有資料表。
外部彙整
對於外部彙整,您也必須考量到在彙整期間新增空值記錄時,可能會發生分支的情況。這點尤其重要,因為 Looker 的預設值是左外部聯結。雖然空值記錄不會影響總和或平均值,但會影響 Looker 執行 type: count
評估方式。如果做法不正確,系統就會計算空值記錄 (這是不希望發生的情況)。
在完整外部彙整中,如果彙整鍵缺少另一個資料表中的值,則可將空值記錄新增至任一資料表。以下範例說明瞭這一點,其中涉及 orders
資料表:
order_id | amount | customer_id |
---|---|---|
1 | $25.00 美元 | 1 |
2 | $50.00 | 1 |
3 | $75.00 | 2 |
4 | $35.00 | 3 |
舉例來說,假設您也有下列 customers
資料表:
customer_id | first_name | last_name | 造訪 |
---|---|---|---|
1 | Amelia | Earhart | 2 |
2 | Bessie | Coleman | 2 |
3 | Wilbur | Wright | 4 |
4 | Charles | Yeager | 3 |
彙整這些資料表後,彙整資料表可表示為以下格式:
order_id | amount | customer_id | customer_id | first_name | last_name | 造訪 |
---|---|---|---|---|---|---|
1 | $25.00 美元 | 1 | 1 | Amelia | Earhart | 2 |
2 | $50.00 | 1 | 1 | Amelia | Earhart | 2 |
3 | $75.00 | 2 | 2 | Bessie | Coleman | 2 |
4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
null | 空值 | null | 4 | Charles | Yeager | 3 |
就像內部彙整一樣,資料表主鍵之間的關係是 many_to_one
。不過,新增的空值記錄會強制要求左側資料表也要使用對稱的匯總。因此,您必須將 relationship
參數變更為 many_to_many
,因為執行這項彙整會中斷左側資料表的計數。
如果這個範例是左外連接,就不會新增空值資料列,且會捨棄額外的客戶記錄。在這種情況下,關係仍會是 many_to_one
。這是 Looker 的預設值,因為系統會假設基礎表格定義了分析作業。在這種情況下,您要分析的是訂單,而非客戶。如果客戶資料表位於左側,情況就會有所不同。
多層級彙整
在某些探索中,基礎資料表會彙整至一或多個檢視畫面,而這些檢視畫面又需要彙整至一或多個額外的檢視畫面。在本例中,這表示資料表會與客戶資料表彙整。在這種情況下,建議您在評估 relationship
參數時,只查看寫入的個別彙整。即使受影響的檢視畫面不在實際建立分支的彙整中,Looker 仍可瞭解下游分支會影響查詢。
Looker 有什麼好處?
Looker 提供機制,可確保關係值正確無誤。其中一個是檢查主鍵的唯一性。每當需要使用分支和對稱匯總來計算評估指標時,Looker 就會檢查所使用的主鍵是否唯一。如果不具唯一性,系統會在查詢執行期間顯示錯誤 (不過,這不會導致 LookML 驗證工具錯誤)。
此外,如果 Looker 無法處理分支 (通常是因為未指定主鍵),則該檢視畫面的「探索」中不會顯示任何指標。如要修正這個問題,只要將欄位指定為主鍵,即可讓指標進入「探索」頁面。
注意事項
對稱式匯總函式的方言支援
Looker 可連線至不支援對稱匯總的某些方言。您可以在 symmetric_aggregates
說明文件頁面中,查看方言清單及其對稱匯總的支援情形。
特殊情況
本頁稍早的「 內部聯結」一節指出,如要判斷正確的關聯值,請查看左側資料表 sql_on
子句中的欄位:「如果欄位是左側資料表的主鍵,您可以將 relationship
參數的左側變更為 one
。如果不是,則通常必須保留為 many
。」除非資料表包含多個不含重複記錄的資料欄,否則這項說法正確。在這種情況下,您可以將任何這類欄視為主鍵,即使不是指定 primary_key: yes
的欄也是如此。
建議您建立某種軟體規則,確保前段落中的陳述式在您指定的資料欄中一律為真。如果是的話,請繼續將其視為此類資料,並在 View 檔案中記下其特殊屬性,方便其他人日後參考 (並附上 SQL Runner 連結以證明)。不過請注意,當欄位指定為主鍵時,Looker 會確認隱含的唯一性,但不會對其他欄位執行相同操作。它只是不會叫用對稱式匯總演算法。