衍生資料表的 SQL 概念

雖然 LookML 檢視表通常會根據資料庫中的現有資料表建立，但您也可以建立以 SQL SELECT 陳述式為基礎的檢視表。在 Looker 中，這類檢視表稱為衍生資料表。衍生表是指查詢結果會視為資料庫中實際表格的查詢。

本指南將說明下列主題：

• Looker 如何為衍生資料表產生 SQL
• 以 SQL 為基礎的衍生資料表
• 原生衍生資料表
• 永久衍生資料表 (PDT)

Looker 如何為衍生資料表產生 SQL

在 LookML 中查詢衍生資料表時，Looker 會將查詢轉譯為 SQL 陳述式，並視方言而定，轉譯為通用表格運算式 (CTE) 或內嵌檢視表。換句話說，產生的 SQL 查詢可能類似下列範例之一。

以下範例說明 Looker 如何產生使用 CTE 查詢衍生資料表的 SQL 查詢：

WITH (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users
SELECT ...
FROM users AS u
INNER JOIN top_100_users ON u.id == top_100_users.id
WHERE ...

以下範例說明 Looker 如何產生 SQL 查詢，使用內嵌資料檢視查詢衍生資料表：

SELECT ...
FROM users AS u
INNER JOIN (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users ON u.id == top_100_users.id
WHERE ...

以 SQL 為基礎的衍生資料表

如要建立以 SQL 為基礎的衍生資料表，請使用檢視畫面 derived_table 參數中的 sql 參數，直接在 LookML 中定義 SQL 查詢。這樣一來，您就能使用 SQL 定義衍生資料表的資料欄。

舉例來說，以下 LookML 範例會定義名為 top_100_users 的衍生資料表，用於找出所有訂單中總支出最高的前 100 名使用者：

view: top_100_users {
  derived_table: {
    sql: SELECT o.user_id as id
         FROM orders AS o
         INNER JOIN order_items AS oi ON o.id == oi.order_id
         ORDER BY SUM(oi.total_sale_price) DESC
         GROUP BY o.customer_id
         LIMIT 100 ;;
  }

  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
  }
}

當探索查詢參照 top_100_users 檢視時，Looker 會根據方言，在產生的 SQL 中使用此 SQL SELECT 陳述式做為 CTE 或內嵌檢視。

使用 SQL 定義衍生資料表時，可能會受到一些限制。舉例來說，在「以 SQL 為基礎的衍生資料表」一節的範例中，請考量以下事項：

• LookML 模型中可能已定義 orders 和 order_items 資料表之間的關係。如果資料庫中的基礎資料表名稱有所變更，則必須在多個位置更新資料表名稱，包括以 SQL 為基礎的衍生資料表定義。
• 衍生資料表定義中的 SQL 必須使用基礎資料庫的正確方言。如果資料已移至其他資料庫，則需要變更以 SQL 為基礎的衍生資料表定義。

基於這些原因，原生衍生資料表通常是較佳的選擇。

原生衍生資料表

在 Looker 中，原生衍生資料表是使用 LookML 定義。與使用以 SQL 為基礎的衍生資料表相比，定義原生衍生資料表時，您會以以下方式運用 LookML 模型：

• 如果資料庫中的資料表名稱有所變更，您只需在 LookML 模型中更新一次。由於原生衍生資料表會指向定義資料庫資料表的現有 LookML 物件，因此原生衍生資料表會自動參照適當的資料表。
• 同樣地，如果您改用其他方言，原生衍生資料表的 LookML 仍會有效，因為 Looker 會產生適合資料庫連線的 SQL。

您可以手動定義原生衍生資料表的 LookML。不過，建立原生衍生資料表最簡單的方式，就是讓 Looker 從探索查詢建立衍生資料表。在探索中選取要用於衍生資料表的欄位，然後在探索齒輪選單中依序選取「取得 LookML」 >「衍生資料表」選項，即可取得 LookML。Looker 會產生您用於從探索查詢建立衍生資料表所需的 LookML，包括 LookML 模型中的相關欄位定義，這些定義是建立衍生資料表的資料欄所需的資訊。您可以將 LookML 複製到專案中的檢視畫面檔案，以建立衍生表格。

以下範例顯示原生衍生表格，可找出在所有訂單中總支出最高的前 100 名使用者：

view: top_100_users {
  derived_table: {
    explore_source:  orders {
      column: id {
        field: orders.customer_id
      }
      sorts: [order_items.sum_total_sale_price desc]
      limit: 100
    }
  }
}

在探索中查詢原生衍生資料表時，系統會自動產生原生衍生資料表的 SQL 查詢詳細資料。當 LookML 檢視表含有原生衍生資料表時，系統會根據基礎探索的定義推斷資料欄定義，因此不必重複任何定義。

永久衍生資料表 (PDT)

在 Looker 中，您可以建立臨時衍生資料表和永久衍生資料表。建立以 SQL 為基礎的衍生資料表或原生衍生資料表後，您可以新增持久性，讓 Looker 將資料表寫入資料庫上的暫存結構定義，並依照您指定的時間表重新產生資料表。詳情請參閱「Looker 中的衍生資料表」。

相關資源

• Looker 中的衍生資料表
• derived_table
• 建立原生衍生資料表
• Looker 產生 SQL 的方式