Metabase｜在 Metabase 使用 SQL 的筆記 2018-Dec-5th

SQL 使用筆記

工作與生活使用 SQL 的筆記。

SQL 語法範例

SELECT
  Table_A.cid,
  Table_B.title,
  Table_A.uid,
  COUNT(*) AS "Number",
  CASE
    WHEN Table_A.collect_type = "R" THEN "Type R"
    WHEN Table_A.collect_type = "D" THEN "Type D"
  END AS "Type",
  Table_C.email
FROM
  Table_A
LEFT JOIN
  Table_B
ON
  Table_A.cid = Table_B.id
LEFT JOIN
  Table_C
ON
  Table_A.uid = Table_C.id
WHERE
  Table_A.created >= {{start}}
  AND Table_A.created <= {{end}}
  AND Table_A.deleted IS NULL
  AND
  CASE
    WHEN LOWER({{type}}) = "all" THEN Table_A.collect_type LIKE "%%"
    WHEN LOWER({{type}}) = "r" THEN Table_A.collect_type = LOWER({{type}})
    WHEN LOWER({{type}}) = "d" THEN Table_A.collect_type = LOWER({{type}})
    ELSE Table_A.collect_type LIKE "%%"
  END
  AND
  CASE
    WHEN LOWER({{uid}}) = "all" THEN Table_A.uid LIKE "%%"
    ELSE Table_A.uid = {{uid}}
  END
  AND
  CASE
    WHEN LOWER({{cid}}) = "all" THEN Table_A.cid LIKE "%%"
    ELSE Table_A.cid = {{cid}}
  END
GROUP BY
  Table_A.cid,
  Table_A.uid
ORDER BY
  Table_A.cid DESC,
  COUNT(*) DESC

常用關鍵字說明

語法 Statement

SQL 的查詢用語，像是：

SELECT
  uid, name
FROM
  u
WHERE
  uid > 100

就是一個完整的 SQL 查詢語句。

查詢 Query

使用 SQL 查詢語句對資料庫要求資料。

資料表 Table、欄位 Column、橫列 Row 與紀錄 Record

一個資料庫由多個資料表（table）所組成，每一個資料表的名稱在資料庫中都是獨一無二的。

資料表中的每一直行稱為欄位（column），每一個欄位都有各自的資料型態（data type）。

由不同欄位組成的橫列（row），稱之為紀錄（record）。

資料表是由無數個紀錄所組成。

Uid	Name	City
10	foo	Taipei City
20	bar	Kaohsiung City

以上述表格來說：

• 整個表格就是一個資料表（table）
• Uid、Name 與 City 是欄位（column）
• 由 10、foo、Taipei City 組成的橫列（row）藍色資料是紀錄（record）

SQL 語法說明

CASE、WHEN、ELSE 與 END

在上述範例中，SELECT statement 使用 CASE 的目的是為了轉換 query 顯示的文字內容。

舉例來說，當我們希望 query 列出的 Table_A.collect_type 的資料是 R 時，顯示為 Type R，就可以使用 CASE … WHEN … END 來做到。

CASE 與 END 之間的 statement，表示這是一個 CASE 的區塊。

WHEN 則是代表條件，以及符合 WHEN 的條件所要進行的動作，一個 CASE 可以包含多個 WHEN。

ELSE 則是代表所有 WHEN 都不符合時要做的動作。

LEFT JOIN 與 ON

當需要 SELECT 跨 table 的資料時，可以使用 LEFT JOIN 與 ON。

在上述範例中，主要 query 的 table 是 Table_A。除了 Table_A 的資料之外，同時也希望能列出 Table_B.title 的資料。Table_A 與 Table_B 的共通 column 是 cid 與 id。

在語法的使用上，LEFT JOIN 後面接的是希望 query 的額外 table，也就是 Table_B。而 ON 後面接的內容則是從 Table_B 撈資料的條件：當 Table_A.cid 與 Table_B.id 相同時，將資料從 Table_B 撈出。

因為 Table_B.title 是我們希望列出的資料，因此在 SELECT 的 statement 中可以看到指定列出 Table_B.title 這個 column 的資料。

GROUP BY

從資料庫取得資料後，將資料依照指定的 columns 進行分組，指定的 columns 的值相同的資料會被視做同一個群組。

GROUP BY 後面可以指定一個以上的 columns。

在上述範例中，Table_A.cid 與 Table_A.uid，被指定為 GROUP BY 的 columns。當資料從資料庫取得後，GROUP BY statement 會將取得的所有資料分組， Table_A.cid 與 Table_A.uid 相同的任兩筆資料會被分到同一組。

GROUP BY 通常會搭配聚合函數（aggregation function）使用，像是 AVG()、COUNT()、MAX()、MIN()、SUM() 等內建函數。

上述範例即是使用 COUNT() 計算每一個分組的資料總數，並且使用 AS 將資料總數的欄位設定為 “Number”。

ORDER BY

將 SELECT 取回的資料，依照指定的 columns 進行排序，排序時可以選擇遞增 ASC 或是遞減 DESC，沒有指定時預設為 ASC。

以上述範例來說，ORDER BY 的順序是先依照 Table_A.cid 的資料由大到小排序，接著再依照 COUNT(*) 的數值由大到小排序。

Metabase 專用符號

Metabase 提供讓使用者自行輸入變數（variable）的功能。

在 Metabase SQL statement 中，用兩個大括號 {{variable}} 包起來的，即是可讓使用者輸入的變數（variable）。

提供變數給使用者自行輸入的常用情況有：

• 查詢不同時間區間的資料
• 對特定 column 查詢指定關鍵字的資料，例如針對 user 這個 column 查詢叫做 foo 的使用者資料

References

SQL 語法 Syntax

GROUP BY 敘述句（SQL GROUP BY Statement）