‧
10分钟阅读
SQL 速查表：五个简单的 SQL 命令，助您入门数据分析
Metabase 团队
‧ 10分钟阅读
分享本文
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() 来清理和聚合数据集，并使其更具商业友好性。例如，识别空字段并用“none”等空标签替换它们。假设您有一个客户表，其中缺少电话号码，用“null”标记。
| CUSTOMER_ID | PHONE_NUMBER | … |
|-------------|--------------| … |
| abc000 | 1111111 | … |
| abc001 | null | … |
| abc002 | 2222222 | … |
使用 coalesce()，以下是使用“none”替换 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 团队
