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() 清洗和聚合数据集，使其更具业务友好性。例如，识别空字段并将其替换为“none”之类的空标签。假设您有一个客户表，其中包含标记为“null”的缺失电话号码。

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

使用 coalesce()，以下是将空值替换为“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 团队