衍生資料表的 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