衍生表格可帶來進階分析的無限可能，但在採用、導入及排解相關問題時，可能會讓人難以應付。本食譜收錄了 Looker 中衍生資料表最常見的用途。
本頁面包含以下範例：
- 每天凌晨 3 點建立資料表
- 將新資料附加至大型資料表
- 使用 SQL 窗格函式
- 為計算值建立衍生欄
- 最佳化策略
- 使用 PDT 測試最佳化調整
UNION兩個資料表- 計算加總值 (將指標轉換為維度)
- 含有匯總認知度的匯總表格
衍生資料表資源
這些食譜假設您對 LookML 和衍生資料表有初步的瞭解。您應該熟悉如何建立檢視畫面和編輯模型檔案。如要複習這些主題，請參閱下列資源：
每天凌晨 3 點建立表格
本範例中的資料會在每天凌晨 2 點傳入。無論是凌晨 3 點或晚上 9 點執行查詢，結果都會相同。因此，建議您每天建構一次資料表，讓使用者從快取中提取結果。
在模型檔案中加入資料群組，即可在多個資料表和探索中重複使用該資料群組。這個資料群組包含 sql_trigger_value 參數，可告知資料群組何時觸發及重建衍生資料表。
如需更多觸發條件運算式的範例，請參閱 sql_trigger_value 說明文件。
## in the model file
datagroup: standard_data_load {
sql_trigger_value: SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24)) ;;
max_cache_age: "24 hours"
}
explore: orders {
…
將 datagroup_trigger 參數新增至檢視檔案中的 derived_table 定義，並指定要使用的資料群組名稱。在本例中，資料群組為 standard_data_load。
view: orders {
derived_table: {
indexes: ["id"]
datagroup_trigger: standard_data_load
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
…
}
將新資料附加至大型資料表
增量 PDT 是 Looker 透過將新資料附加至資料表，而非重新建構整個資料表，所建立的持續衍生資料表。
下一個範例會以 orders 資料表範例為基礎，說明如何逐步建立資料表。每天都會收到新的訂單資料，只要新增 increment_key 參數和 increment_offset 參數，即可將資料附加到現有資料表。
view: orders {
derived_table: {
indexes: ["id"]
increment_key: "created_at"
increment_offset: 3
datagroup_trigger: standard_data_load
distribution_style: all
sql:
SELECT
user_id,
id,
created_at,
status
FROM
demo_db.orders
GROUP BY
user_id ;;
}
dimension: id {
primary_key: yes
type: number
sql: ${TABLE}.id ;; }
…
}
increment_key 值設為 created_at，這是在本例中應查詢新資料並附加至 PDT 的時間增量。
increment_offset 值會設為 3，以指定要重新建構的先前時間週期數量 (以增量鍵的精細程度為準)，以便考量到延遲到達的資料。
使用 SQL 窗型函式
部分資料庫方言支援視窗函式，特別是用於建立序號、主鍵、動態和累積總和，以及其他實用的多列計算。執行主要查詢後，系統會在單獨的傳遞中執行任何 derived_column 宣告。
如果資料庫方言支援窗型函式，您可以在原生衍生資料表中使用這些函式。使用含有視窗函式的 sql 參數建立 derived_column 參數。參照值時，請使用原生衍生資料表中定義的資料欄名稱。
以下範例說明如何建立原生衍生資料表，其中包含 user_id、order_id 和 created_time 資料欄。接著，您可以使用衍生欄搭配 SQL ROW_NUMBER() 窗口函式，計算包含客戶訂單序號的資料欄。
view: user_order_sequences {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: order_id {
field: order_items.order_id
}
column: created_time {
field: order_items.created_time
}
derived_column: user_sequence {
sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
}
}
}
dimension: order_id {
hidden: yes
}
dimension: user_sequence {
type: number
}
}
為計算值建立衍生欄
您可以新增 derived_column 參數，指定 explore_source 參數的 Explore 中不存在的資料欄。每個 derived_column 參數都有一個 sql 參數，可指定建構值的方式。
sql 計算可使用您透過 column 參數指定的任何欄。衍生資料欄無法包含匯總函式，但可以包含可在資料表單一資料列上執行的計算。
這個範例會建立 average_customer_order 欄，該欄會根據原生衍生資料表中的 lifetime_customer_value 和 lifetime_number_of_orders 欄計算而得。
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: users.id
}
column: lifetime_number_of_orders {
field: order_items.count
}
column: lifetime_customer_value {
field: order_items.total_profit
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
最佳化策略
由於 PDT 會儲存在資料庫中，因此您應使用下列策略 (依方言支援的程度而定) 來改善 PDT：
舉例來說，如要新增持久性，您可以設定 PDT 在資料群組 orders_datagroup 觸發時重建，然後在 customer_id 和 first_order 上新增索引，如下所示：
view: customer_order_summary {
derived_table: {
explore_source: orders {
...
}
datagroup_trigger: orders_datagroup
indexes: ["customer_id", "first_order"]
}
}
如果您未新增索引 (或相應的方言)，Looker 會警告您應新增索引，以改善查詢效能。
使用 PDT 測試最佳化
您可以使用 PDT 測試不同的索引、分發作業和其他最佳化選項，而不需要 DBA 或 ETL 開發人員提供大量支援。
假設您有一個資料表，但想測試不同的索引。檢視畫面的初始 LookML 可能會如下所示：
view: customer {
sql_table_name: warehouse.customer ;;
}
如要測試最佳化策略，您可以使用 indexes 參數，在 LookML 中加入索引，如下所示：
view: customer {
# sql_table_name: warehouse.customer
derived_table: {
sql: SELECT * FROM warehouse.customer ;;
persist_for: "8 hours"
indexes: [customer_id, customer_name, salesperson_id]
}
}
查詢檢視一次，即可產生 PDT。然後執行測試查詢並比較結果。如果結果良好，您可以請資料庫管理員或 ETL 團隊在原始資料表中加入索引。
UNION 兩個資料表
如果 SQL 方言支援，您可以在兩個衍生資料表中執行 SQL UNION 或 UNION ALL 運算子。UNION 和 UNION ALL 運算子會合併兩個查詢的結果集。
以下範例說明以 SQL 為基礎的衍生資料表搭配 UNION 的樣貌：
view: first_and_second_quarter_sales {
derived_table: {
sql:
SELECT * AS sales_records
FROM sales_records_first_quarter
UNION
SELECT * AS sales_records
FROM sales_records_second_quarter ;;
}
}
sql 參數中的 UNION 陳述式會產生衍生資料表，合併兩個查詢的結果。
UNION 和 UNION ALL 的差異在於 UNION ALL 不會移除重複的資料列。使用 UNION 與 UNION ALL 時，請注意效能考量，因為資料庫伺服器必須額外執行作業來移除重複的資料列。
求和總和 (將評估資料維度化)
根據 SQL 和 Looker 的一般規則，您無法依匯總函式 (在 Looker 中以「指標」表示) 的結果分組查詢。您只能依未匯總的欄位 (在 Looker 中以「維度」表示) 進行分組。
如要依匯總資料進行分組 (例如匯總總和)，您必須將度量方式「轉為維度」。其中一種做法是使用衍生資料表，這可有效建立匯總的子查詢。
從探索開始，Looker 可以為所有或大部分的衍生資料表產生 LookML。只要建立探索，然後選取要納入衍生表格的所有欄位即可。接著，如要產生原生 (或以 SQL 為基礎) 的衍生資料表 LookML，請按照下列步驟操作：
按一下「探索」的齒輪選單，然後選取「取得 LookML」。
如要查看 LookML 以便為探索建立原生衍生資料表，請按一下「衍生資料表」分頁標籤。
複製 LookML。
複製產生的 LookML 後，請按照下列步驟將其貼到檢視檔案中：
在 Looker IDE 中，按一下專案檔案清單頂端的「+」圖示，然後選取「Create View」。或者，如要在資料夾中建立檔案，請按一下資料夾的選單，然後選取「建立檢視畫面」。
將檢視畫面名稱設為有意義的名稱。
視需要變更資料欄名稱、指定衍生欄，以及新增篩選器。
匯總表格含有匯總認知
在 Looker 中，您可能經常會遇到需要匯總資料表或匯總資料表的大型資料集或資料表，才能提高效能。
透過 Looker 的匯總資料意識，您可以預先建立匯總資料表，以便針對不同層級的精細度、維度和匯總進行匯總；您也可以告知 Looker 如何在現有探索中使用這些資料表。接著，查詢會在 Looker 認為適當的情況下使用這些匯總表，而無需任何使用者輸入。這麼做可以縮減查詢大小、縮短等待時間，並改善使用者體驗。
以下是 Looker 模型中非常簡單的實作方式，可說明輕量匯總感知功能。假設資料庫中有一個航班表格，其中每列資料代表透過 FAA 記錄的航班，您可以在 Looker 中使用該表格的檢視畫面和探索功能建立這個表格的模型。以下是匯總資料表的 LookML，您可以為探索定義這項資料表：
explore: flights {
aggregate_table: flights_by_week_and_carrier {
query: {
dimensions: [carrier, depart_week]
measures: [cancelled_count, count]
}
materialization: {
sql_trigger_value: SELECT CURRENT-DATE;;
}
}
}
有了這個匯總資料表，使用者就能查詢 flights 探索，Looker 也會自動使用匯總資料表來回答查詢。如要進一步瞭解匯總感知功能，請參閱匯總感知功能教學課程。