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() 是什么？

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

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

如何使用 coalesce()

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

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()，以下是将空值替换为“无”的查询

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