檢視的 SQL 概念

LookML 專案中的每個檢視檔案會在 Looker 中定義單一檢視畫面,指定要查詢的資料表,以及該資料表中的哪些欄位 (維度和指標) 會顯示在 Looker UI 中。檢視表會對應資料庫中的單一資料表或單一衍生資料表

本指南將說明下列主題:

如要進一步瞭解如何使用 SQL 定義並自訂 LookML 中的衍生資料表,請參閱衍生資料表的 SQL 概念

檢視畫面

以下是名為 users.view 的檢視檔案範例,其中包含要查詢的資料庫資料表定義,以及多個維度和評量:

view: users {
  sql_table_name: thelook.users ;;

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

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }
   measure: average_age {
    type: average
    sql: ${age} ;;  }

  dimension_group: created {
    type: time
    timeframes: [raw, time, date, week, month, quarter, year]
    sql: ${TABLE}.created_at ;;
  }

  measure: count {
    type: count
  }
}

檢視畫面定義的第一個元素是 sql_table_name 參數,可指定檢視畫面要查詢的資料庫表格。這個值是整個模型中唯一定義資料表名稱的位置,因為所有其他對檢視畫面的參照都會使用資料表別名 ${TABLE}。如果您想變更資料庫資料表名稱,只需在 sql_table_name 參數中變更即可。參照資料庫表格時,請考量以下事項

Looker 會使用 sql_table_name 值來編寫 SQL FROM 子句,接著是檢視表名稱,這會成為資料表別名。等效的 SQL 語法如下:

FROM `thelook`.`users` AS `users`

Looker 會使用檢視表定義的維度和資料表,產生 SQL SELECT 子句。每個維度都會定義維度的類型 (例如字串、數字或布林值),以及使用表格別名的 sql LookML 參數,用於在檢視畫面中參照維度。如需瞭解名為 age 的維度,請參閱以下範例:

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

當 Looker 建立要傳送至資料庫的 SQL 時,Looker 會將檢視表別名替換為 ${TABLE}。針對上一個範例的 age 維度,Looker 會產生類似以下的 SELECT 子句:

SELECT `users`.`age` AS `users.age`

指標通常是針對維度執行的彙整作業。您可以在評估指標的 sql 運算式中指定維度別名。舉例來說,計算 age 維度的平均值的評量指標,可能會包含含有別名 ${age}sql 運算式,如以下範例所示:

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

  measure: average_age {
    type: average
    sql: ${age} ;;
  }

如果您重新命名 age 維度,其新的別名會傳播至任何維度別名參照。

自訂檢視畫面檔案

您可以自訂檢視表檔案的 SQL 運算式,也可以使用 Looker 內建的 LookML 邏輯,模擬 SQL 運算式的邏輯。

使用 SQL 運算式

假設您想將年齡資料分為四個客層,將未滿 18 歲的使用者定義為「青少年」、18 到 35 歲的使用者定義為「年輕成人」、36 到 65 歲的使用者定義為「年長成人」、65 歲以上使用者定義為「銀髮族」。如要執行此分割作業,您必須使用可擷取這些同類群組的 sql 運算式,定義新的維度 (例如 dimension: age_cohort)。下列 LookML 維度定義使用適合 MySQL 資料庫連線的 CASE 陳述式:

dimension: age_cohort {
  type: string
  sql:
    CASE
      WHEN ${age} < 18 THEN 'Youth'
      WHEN ${age} < 35 THEN 'Young Adult'
      WHEN ${age} < 65 THEN 'Older Adult'
      ELSE 'Senior'
    END ;;
}

您已將年齡層區隔定義為維度,因此只要在探索查詢中加入年齡層區隔維度,即可重複使用 CASE 邏輯。

建立含有年齡同儕群組維度的探索查詢時,您可以使用探索的「SQL」分頁查看 Looker 產生的 SQL。使用年齡同儕群維度時,SQL 會像這樣:

SELECT
CASE
  WHEN users.age < 18 THEN 'Youth'
  WHEN users.age < 35 THEN 'Young Adult'
  WHEN users.age < 65 THEN 'Older Adult'
  ELSE 'Senior'
END  AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 500

使用 Looker 內建的例項邏輯

您可以使用不依賴資料庫的運算式,達到與 SQL CASE 陳述式相同的效果。您可以使用 LookML case 參數定義同類群值區,這些值區由 when 陳述式組成,而該陳述式會使用 sql 運算式擷取特定條件和字串,用於標示結果。

以下是使用 case LookML 參數編寫相同的新 age_cohort 維度範例:

  dimension: age_cohort {
    case: {
      when: {
        sql: ${age} < 18 ;;
        label: "Youth"
      }
      when: {
        sql: ${age} < 35 ;;
        label: "Young Adult"
      }
      when: {
        sql: ${age} < 65 ;;
        label: "Middle-aged Adult"
      }
      else: "Older Adult"
    }
  }

在執行階段,Looker 會為資料庫建立正確的 SQL CASE 語法。此外,Looker 會建立另一個運算式來處理分組排序作業,因此產生的標籤不會只以英數字母排序 (除非您定義排序順序為英數字母)。Looker 會建立類似以下的 SQL 查詢結果:

SELECT
CASE
  WHEN users.age < 18  THEN '0'
  WHEN users.age < 35  THEN '1'
  WHEN users.age < 65  THEN '2'
  ELSE '3'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age < 18  THEN 'Youth'
  WHEN users.age < 35  THEN 'Young Adult'
  WHEN users.age < 65  THEN 'Older Adult'
  ELSE 'Senior'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500

使用 Looker 內建的區塊或層級邏輯

另一種指定數值分組方式是使用 Looker 內建的 bintier 參數類型。type:binbins 參數搭配使用,type: tiertiers 參數搭配使用,可將數值維度分割為一組數字範圍。但您無法為每個區間定義標籤。

以下 LookML 範例會在維度中使用 bins 參數,定義每個集合的最小值:

  dimension: age_cohort {
    type: bin
    bins: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

您可以在維度中以完全相同的方式使用 tiers 參數。例如:

  dimension: age_cohort {
    type: tier
    tiers: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

Looker 隨後會產生類似以下的 SQL 陳述式:

SELECT
CASE
  WHEN users.age  < 18 THEN '0'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '1'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '2'
  WHEN users.age  >= 65 THEN '3'
  ELSE '4'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age  < 18 THEN 'Below 18'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '18 to 35'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '36 to 64'
  WHEN users.age  >= 65 THEN '65 or Above'
  ELSE 'Undefined'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500