正確設定關係參數

本頁面適用於嘗試使用 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_idcustomers 資料表的每個資料列中皆不重複,則關係為 many_to_many

您可以按照下列步驟,透過檢查主鍵,以程式輔助方式判斷正確的關聯值:

  1. 首先,請將 many_to_many 寫為關係。只要主鍵正確,Looker 就會一律觸發對稱匯總演算法並確保準確度,因此一律會產生正確的結果。不過,由於演算法會使查詢變得複雜,並增加執行時間,因此建議您嘗試將一或兩側的 many 變更為 one
  2. 請查看左側表格中 sql_on 子句中的欄位。如果欄位或多個欄位是左側資料表的主鍵,您可以將 relationship 參數的左側變更為 one。如果不是,通常必須保持 many。(如需特殊情況的相關資訊,請參閱本頁後續的「注意事項」一節)。
  3. 接著,請查看 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 會確認隱含的唯一性,但不會對其他欄位執行相同操作。它只是不會叫用對稱式匯總演算法。