SQL 速查表：五個簡單的 SQL 指令，助您輕鬆入門資料分析

我們建立 Metabase 是為了讓您無需了解 SQL 也能探索和從資料中學習。但有時，當您在處理龐大而複雜的問題時，略懂一點 SQL 就能大有幫助。因此，我們收集了 5 個 SQL 指令和函式，方便您複製貼上使用。

如果您已熟悉 SQL，請隨時直接跳到下面的速查表，但如果您是新手，我們建議您查看我們的 SQL 最佳實務指南。

1. SQL 指令：count(distinct)

什麼是 SQL 指令 count(distinct)

SQL 指令 count(distinct) 用於傳回欄或運算式中不重複值的數量。

如何使用 count(distinct)

使用 count(distinct) 傳回完全不重複的資料點數量，例如員工、地點、客戶等的數量。

COUNT( DISTINCT <expression>)

例如，您可能想計算客戶居住的不同城市數量。若要在 Metabase 上跟著操作，您可以開啟SQL 編輯器，選取範例資料集並執行此查詢

SELECT count(distinct city) as cities
FROM people

您的傳回值看起來會像這樣：1966 (一個數字)。

更精確地說，您會得到一個包含單一值的欄

| CITIES |
|--------|
| 1966   |

真實世界範例：count(distinct)

分析師會在計算在網站上展現行為的不重複訪客人數時使用 count(distinct)。例如，假設我們有一個 website_intents 表格，將 Cookie 與網站中的某些行為對應起來

| COOKIE_ID | IS_VISIT_LANDING_PAGE | IS_VISIT_CHECKOUT | … |
|-----------|-----------------------|-------------------| … |
| abc000    | 1                     | 0                 | … |
| abc001    | 1                     | 1                 | … |
| abc002    | 1                     | 0                 | … |

以下是從到達結帳流程頂端的用戶取得不重複 Cookie 數量的查詢

SELECT count(distinct
                case
                    when is_visit_checkout = True then cookie_id
                    else null
                end) as visited_checkout
FROM website_intents

2. SQL 指令：date_trunc()

什麼是 SQL 指令 date_trunc()

將時間戳記截斷（縮短）為特定粒度，從微秒到千禧年。

SQL 指令 date_trunc() 用於根據小時、天、週或月「截斷」間隔，並提供可操作且更精確的間隔或時間戳記。

如何使用 date_trunc()

使用 date_trunc() 從時間戳記或時間間隔中移除不必要的資訊。

DATE_TRUNC(granularity, timestamp)

例如，您可能想要將時間戳記截斷到小時

SELECT date_trunc('hour', timestamp '2021-11-4 12:29:05')

您的傳回值看起來會像這樣：2021-11-4 12:00:00。

真實世界範例：date_trunc()

分析師使用 date_trunc() 來比較跨多個月、週或天的趨勢。使用 date_trunc()，您可以輕鬆查看特定時間段內的行為比率，例如查看上個月與前幾個月相比，有多少客戶建立了帳戶。例如，假設我們想要從您的 Orders 表格（來自範例資料集）中取得 2018 年建立的所有訂單。您的查詢可能看起來像這樣

SELECT count(distinct id) as total_order_2018
FROM ORDERS
WHERE DATE_TRUNC('year', created_at) = timestamp '2018-1-01 00:00:00'

您的傳回值會像這樣

| TOTAL_ORDER_2018 |
|------------------|
| 5834             |

想要更深入了解嗎？查看我們的其他資源，以探索此指令的更深入範例和使用案例：SQL 中的日期。

3. SQL 指令：coalesce()

什麼是 SQL 指令 coalesce()

評估清單以尋找非 Null 值；也就是說，具有已知值的資料點。

SQL 指令 coalesce() 主要用於資料清理和彙總過程中，以填補 Null 值，並使資料集更符合商業用途且更易於閱讀。

如何使用 coalesce()

使用 coalesce() 透過設定 2 個或更多參數來尋找或標準化非 Null 資訊。

COALESCE(<expression>, [<expression>, …])

範例

SELECT coalesce(null, value1, value2, value3, null)

您的傳回值看起來會像這樣：value1。

真實世界範例：coalesce()

分析師使用 coalesce() 來清理和彙總資料集，並使其更符合商業用途。例如，識別空白欄位並將其替換為空白標籤，例如「無」。假設您有一個客戶表格，其中缺少電話號碼，並以「Null」標記。

| CUSTOMER_ID | PHONE_NUMBER | … |
|-------------|--------------| … |
| abc000      | 1111111      | … |
| abc001      | null         | … |
| abc002      | 2222222      | … |

使用 coalesce()，以下是將 Null 值替換為「無」的查詢

SELECT customer_id,
      COALESCE(phone_number, 'none') AS phone_number
FROM client

這會產生

| CUSTOMER_ID | PHONE_NUMBER | … |
|-------------|--------------| … |
| abc000      | 1111111      | … |
| abc001      | none         | … |
| abc002      | 2222222      | … |

4. SQL 指令：case

什麼是 SQL 指令 case

當較大資料集內的資料點符合特定條件時，傳回值。

SQL 指令 case 用於根據具體參數組織資料、產生類別或將資料點排序到類別中，或從各種資料產生可操作的資訊。

如何使用：case

使用 case 根據特定參數產生可操作的結果。

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END

例如，您可能想要使用簡短訊息將每個分數分類

case
   when score > 9 then 'awesome'
   when score < 5 then 'bad'
   else 'ok'
end as message

如果沒有符合任何條件，case when 將會產生 else。

您的傳回值看起來會像這樣

| SCORE | MESSAGE |
|-------|---------|
| 10    | awesome |
| 4     | bad     |
| 7     | ok      |

真實世界範例：case

SQL 指令 case 在分析流程時特別有幫助，尤其是在對應流程階段和根據客戶在流程中的位置組織客戶清單時。

例如，您還記得我們在「真實世界範例：count(distinct)」範例中展示的 website_intents 表格嗎？假設我們有一個 pageviews 表格，追蹤每個工作階段瀏覽過的網頁

| SESSION_ID | PAGE_URL_PATH | … |
|------------|---------------| … |
| abc000     | /landing-page | … |
| abc001     | /landing-page2| … |
| abc002     | /checkout     | … |

以下是取得該結果的查詢可能看起來像這樣

SELECT id,
    case
        when(
           page_url_path = '/landing-page.html'
           or page_url_path = '/landing-page2.html'
           or page_url_path = '/landing-page3.html'
        ) then 1 else 0
    end as is_visit_landing_page,
    case
        when(
           page_url_path like '/checkout%'
           or page_url_path like '/checkout-new%'
           or page_url_path like '/checkout-enterprise%'
        ) then 1 else 0
    end as is_visit_checkout,
    ...
FROM pageviews

這會產生

| SESSION_ID | PAGE_URL_PATH | IS_VISIT_LANDING_PAGE |  IS_VISIT_CHECKOUT | … |
|------------|---------------|-----------------------|--------------------| … |
| abc000     | /landing-page | 1                     | 0                  | … |
| abc001     | /landing-page2| 1                     | 0                  | … |
| abc002     | /checkout     | 0                     | 1                  | … |

5. SQL 指令：row_number()

什麼是 SQL 指令 row_number()

透過在序列中為每一列指派精確的位置，對分割區內的列進行排序。它從 1 開始，並根據視窗陳述式的 ORDER BY 部分為列編號。

SQL 指令 row_number() 用於根據您指定的參數快速且精確地組織資料集中的資訊。

請注意，並非每個資料庫都支援 row_number()。

如何使用：row_number()

使用 row_number() 變更清單的順序。

ROW_NUMBER() OVER (
    [PARTITION BY partition_column, ... ]
    ORDER BY sort_column [ASC | DESC], ...
)

例如，您可能想要依據帳戶建立時間重新排序帳戶清單

SELECT account_created_at,
       row_number() over(
           order by account_created_at
       ) as row
FROM accounts

您的傳回值看起來會像這樣

| ROW | ACCOUNT_CREATED_AT |
|-----|--------------------|
| 1   | 2021-01-14         |
| 2   | 2021-05-09         |
| 3   | 2021-08-22         |

真實世界範例：「row_number()」

分析師使用 row_number() 來組織清單中資訊的順序。例如，排序客戶資訊清單，以根據時間對訂單進行排名，以查看購買價值隨時間的變化。假設您有一個客戶資訊表格

| PLAN | ACCOUNT_CREATED_AT | … |
|------|--------------------| … |
| free | 2021-01-14         | … |
| pro  | 2021-02-20         | … |
| free | 2021-05-09         | … |
| pro  | 2021-07-24         | … |
| free | 2021-08-22         | … |

使用 row_number()，我們可以根據每個方案訂閱的建立時間來組織客戶資訊

SELECT plan,
       account_created_at,
       row_number() over(
           partition by plan
           order by account_created_at
       ) as row
FROM accounts

您的傳回值看起來會像這樣

| PLAN | ROW | ACCOUNT_CREATED_AT | … |
|------|-----|--------------------| … |
| free | 1   | 2021-01-14         | … |
| free | 2   | 2021-05-09         | … |
| free | 3   | 2021-08-22         | … |
| pro  | 1   | 2021-02-20         | … |
| pro  | 2   | 2021-07-24         | … |

最後想法：SQL 基礎入門

使用 Metabase，您不需要 SQL 也能探索資料，但如果您正在處理複雜的事情，一些簡單的指令可以將您的分析提升到新的境界。我們希望這份速查表能為您提供一些探索新方法的新想法。如果您想在您的技能庫中加入更多 SQL，請查看我們的 SQL 最佳實務指南。

乾杯，

Metabase 團隊