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)。例如，假设我们有一个网站_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() 是什么？

评估列表以找到非空值；即具有已知值的数据点。

SQL 命令 coalesce() 主要在数据清洗和聚合过程中使用，以填充空值并使数据集更易于商业用途和阅读。

如何使用 coalesce()

使用 coalesce() 通过设置2个或更多参数来查找或标准化非空信息。

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 团队