衍生資料表可提供多種進階分析功能，但可能難以使用、實作及疑難排解。這份錦囊妙計包含 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 ;;
}
…
}
將新資料附加至大型資料表
永久累加型衍生資料表是一種永久衍生資料表，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 宣告。
如果資料庫方言支援視窗函式，您可以在原生衍生資料表中使用這些函式。建立 derived_column 參數，並使用包含視窗函式的 sql 參數。參照值時，請使用原生衍生資料表定義的資料欄名稱。
以下範例說明如何建立包含 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 參數的「探索」中不存在的資料欄。每個 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 測試不同的索引、分布和其他最佳化選項，不需要資料庫管理員或 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。接著執行測試查詢並比較結果。如果結果良好，可以請 DBA 或 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 中，點選專案檔案清單頂端的「+」，然後選取「建立檢視表」。或者，如要在資料夾中建立檔案，請點選資料夾選單，然後選取「建立檢視表」。
將檢視表名稱設為有意義的名稱。
視需要變更資料欄名稱、指定衍生資料欄，以及新增篩選器。
具備匯總感知功能的匯總表格
在 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 則會自動使用匯總表格回答查詢。如需更詳細的匯總感知功能逐步操作說明，請參閱匯總感知功能教學課程。