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()，您可以轻松查看特定时间段内的行为率，例如查看上个月创建账户的客户数量与前几个月的比较。例如，假设我们想从您的订单表（来自示例数据集）中获取 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 值替换为“none”的查询

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