数据分析师训练营/ 第三篇 SQL/ 第十一章
第十一章 · SQL 篇

把问题翻译成一句 SQL

运营在群里发了一句:“给我昨天(2024-06-01)的日报,三个数——多少人来了、多少人下单、卖了多少。” 你手里有五张表、78,817 行。这三个数分别来自两张表,口径各不相同,但没有一个需要 JOIN,也没有一个需要 GROUP BY。

本章只做单表查询:SELECT 选列、WHERE 筛行、ORDER BY 排序、LIMIT 截断。 零件只有五个,但每一个都直接决定数字对不对。学完这一章,你要能独立写出这三条查询, 并且在别人问“这个数怎么来的”时,指着 WHERE 一行一行解释。

理论 : 实操 = 4 : 6 约 5 学时 前置 第 10 章

11.1五个零件

单表查询的骨架只有五段:SELECT、FROM、WHERE、ORDER BY、LIMIT。这一节不背语法, 只讲清楚每一段“能做什么、不能做什么”。

SELECT
要哪几列
→
FROM
从哪张表
→
WHERE
留哪些行
ORDER BY
怎么排
→
LIMIT
留几行
图 11.1.1 单表查询的五个零件(书写顺序):选列、选表、筛行、排序、截断

11.1.1SELECT 与列选择:为什么禁止 SELECT *

SELECT 决定结果里出现哪些列。普通 SELECT 不改变行数,只改变“你看到什么”。最小的一条查询长这样:

SELECT order_id, pay_amount
FROM orders
ORDER BY order_date
LIMIT 5;
order_idpay_amount
O230101165935,988.49
O230101037822,015.47
O230101169984,869.60
O2301010423022,966.56
O230101103893,215.88

初学者最爱写 SELECT *,因为省事。但在分析场景里,它有三个具体问题:

明确列名还有一个好处:SELECT 列表本身就是文档。看到 SELECT order_id, pay_amount,任何人都知道这份结果一行是一笔订单、金额是实付。

SELECT 也能直接算表达式,例如把优惠加回去还原原价:pay_amount + discount AS 原价。 但本章先只用原始列,把口径练稳;表达式和条件判断留给第 12 章。

1

只取你要的列,列名就是口径说明书

每一列都问一句“报表里要不要”。不要的就不写。需要改名字时用 AS, 例如 pay_amount AS 实付金额。

如果不这样会怎样:用 SELECT * 取 orders,再按位置去拿第 8 列, 结果有人调整了表结构,第 8 列变成了 city,你的 GMV 求和直接报错或算出天文数字。

11.1.2WHERE:比较、BETWEEN、IN、LIKE、IS NULL

WHERE 是行级过滤器:它逐行判断“这一行满不满足条件”,只把结果为 TRUE 的行留下。 它不知道 SELECT 要输出什么,也不做分组和聚合——这两件事分别属于 SELECT 和 GROUP BY。

五种最常用的写法,先看总表:

写法例子结果
比较pay_amount > 400007 笔订单
BETWEENpay_amount BETWEEN 1000 AND 20001,059 笔(含两端)
INstatus IN ('已取消', '已退款')6 月 1 日 11 笔,合计 142,262.33
LIKEuser_id LIKE 'U00000_'9 个用户(U000001–U000009)
IS NULLuser_id IS NULL565 笔游客订单

BETWEEN 是闭区间。BETWEEN 1000 AND 2000 等价于 >= 1000 AND <= 2000,两端都算。它适合金额、数量,不适合带时分秒的文本日期: BETWEEN '2024-06-01' AND '2024-06-02' 看似包含两天,实际只覆盖 6 月 1 日; 6 月 2 日的记录因为后面多了 00:00:00,字符串更大,被排除在外。

SELECT COUNT(*) FROM orders
WHERE pay_amount BETWEEN 1000 AND 2000;

IN 是一组等值的简写。status IN ('已取消','已退款') 比写两个 OR 更短、更不容易漏括号。加上日期条件,可以核出 6 月 1 日有 11 笔取消/退款订单:

SELECT COUNT(*), ROUND(SUM(pay_amount), 2)
FROM orders
WHERE order_date LIKE '2024-06-01%'
  AND status IN ('已取消', '已退款');
COUNT(*)SUM(pay_amount)
11142,262.33

LIKE 是两个通配符的游戏。% 代表任意长度(包括 0 个字符), _ 代表恰好一个字符。所以 '2024-06-01%' 匹配当天所有时间, 'U00000_' 只匹配 U000001 到 U000009 这 9 个 ID,不会匹配 U000010。

值模式匹配?原因
2024-06-01 00:19:28'2024-06-01%'是% 吸收后面的时分秒
2024-06-01'2024-06-01%'是% 可以匹配 0 个字符
2024-06-10 08:00:00'2024-06-01%'否第 9 位是 1 不是 0
U000009'U00000_'是_ 匹配最后一位 9
U000010'U00000_'否_ 只能一个字符,这里多了一位
图 11.1.2 % 匹配任意长度,_ 只匹配一个字符

IS NULL 是空值专用。第 10 章说过,= NULL 永远得不到 TRUE。 要数游客订单,只能写:

SELECT COUNT(*) FROM orders WHERE user_id IS NULL;
结果含义
565没有登录账号的游客订单

还有一个工程细节:LIKE '%关键词%' 前后都带 %,用不上索引, 两万行还能忍,两千万行会拖垮查询。前缀匹配 '2024-06-01%' 没有这个问题。 第 17 章讲索引时会再回到这条规则。

另外,!= 和 <> 在 SQLite 里等价,标准写法是 <>。 团队统一用一种,Code Review 时少吵架。

2

WHERE 只做行级过滤,不聚合、不分组、不认识别名

条件里的每一列都必须是表里真实存在的列。想过滤“聚合之后的结果”,要用 HAVING(第 12 章); 想按条件选列,用 CASE(第 12 章)。

如果不这样会怎样:把 SUM(pay_amount) > 1000000 写进 WHERE, SQLite 直接报 misuse of aggregate: SUM();把 amt 这样的别名写进 WHERE, MySQL 报 Unknown column。

11.1.3AND / OR 的优先级与括号

SQL 的优先级是 NOT > AND > OR。也就是说,A AND B OR C 会被理解成 (A AND B) OR C,而不是 A AND (B OR C)。这不是细节,它会直接改变数字。

运营想要“已支付,且城市是杭州或成都”的订单数。两种写法,差 525 笔:

-- 写法一:没有括号
SELECT COUNT(*) FROM orders
WHERE status = '已支付' AND city = '杭州' OR city = '成都';

-- 写法二:给 OR 加括号
SELECT COUNT(*) FROM orders
WHERE status = '已支付' AND (city = '杭州' OR city = '成都');
写法数据库的理解结果
无括号(已支付 且 杭州)或(所有成都)4,494
有括号已支付 且(杭州 或 成都)3,969

4,494 是怎么来的?已支付且杭州的有 2,050 笔,成都所有状态的有 2,444 笔,2,050 + 2,444 = 4,494。 其中 525 笔是成都的非已支付订单,被错误地算进了“已支付”。3,969 = 2,050 + 1,919(成都已支付)。

写法被算进来的行数量
无括号已支付 ∩ 杭州2,050
无括号成都(不分状态)2,444
有括号已支付 ∩ 杭州2,050
有括号已支付 ∩ 成都1,919
图 11.1.3 同一个需求,括号位置不同,结果差 525 笔

NOT 也遵循同样的逻辑:NOT (A OR B) 等价于 NOT A AND NOT B。 拿不准时,先把业务语言写成带括号的布尔表达式,再逐字翻译成 SQL,别凭感觉省括号。

3

只要句子里出现 OR,就给每个 OR 分支加括号

不要依赖记忆里的优先级。加括号不花钱,算错数要加班。混用 AND / OR / NOT 时, 括号是唯一可靠的写法。

如果不这样会怎样:“已支付且杭州或成都”少了括号,525 笔成都未支付订单混进成交数, 日报 GMV 和订单数同时虚高,而且 SQL 不会报任何错。

11.1.4ORDER BY 多列排序与 LIMIT

ORDER BY 决定行的顺序,默认升序(ASC),加 DESC 变降序。它可以接多列: 先按第一列排,第一列相同再按第二列排。DESC 只作用于紧挨着它的那一列,所以下面这句是 status 升序、pay_amount 降序。

SELECT status, order_id, pay_amount
FROM orders
ORDER BY status, pay_amount DESC
LIMIT 6;
statusorder_idpay_amount
已取消O2306170005544,910.59
已取消O2406120552343,644.36
已取消O2302161520139,698.07
已取消O2303250352137,727.35
已取消O2301161403137,432.38
已取消O2309100317935,906.08

前六行全是“已取消”,因为 status 按字符排序后“已取消”排在前面。这也顺便证明了一件事: 全表金额最大的两笔都是取消单。所以做 Top N 之前,先用 WHERE 把口径定死:

SELECT order_id, pay_amount
FROM orders
WHERE order_date LIKE '2024-06-01%' AND status = '已支付'
ORDER BY pay_amount DESC
LIMIT 3;
order_idpay_amount
O2406010970026,283.04
O2406011601025,836.27
O2406011254423,859.87

LIMIT 只负责截断,不负责排序。没有 ORDER BY 的 LIMIT 3 是“随便给我三行”, 换一个列清单、换一个版本,拿到的三行都可能不同。LIMIT 5 OFFSET 10 表示跳过 10 行再取 5 行, 常用于分页;分页同样必须先排序,否则翻页会重复或漏行。

还有一个容易忽略的细节:SQLite 升序排序时 NULL 排在最前。按 user_id 升序取前三行, 拿到的全是游客单(user_id 为 NULL);降序时 NULL 又跑到最后。做用户维度排序前, 先用 WHERE user_id IS NOT NULL 把游客单排除,否则“用户排行榜”第一名会是一个空值。

4

ORDER BY 决定谁在前,LIMIT 只负责截断

Top N 的固定套路是三步:WHERE 定口径 → ORDER BY 定顺序 → LIMIT 取前 N。 三步的顺序不能换,也不能省掉中间一步。

如果不这样会怎样:直接 SELECT ... FROM orders LIMIT 3 当“金额最高的三笔”, 拿到的只是表里最前面的三行,和金额毫无关系;去掉 WHERE 再排序,榜首会变成三笔取消单。

11.2把日报拆成三条查询

现在回到运营那句话。一个“日报”听起来是一件事,实际上是三个指标,来自两张表,三种口径。 正确的做法不是写一条大 SQL,而是先拆。

指标业务问题表口径
日活 DAU多少人来了?events2024-06-01 有行为的去重用户数
下单人数多少人付了钱?orders2024-06-01 已支付订单的去重用户数
GMV卖了多少钱?orders2024-06-01 已支付订单的 pay_amount 合计

拆完之后,每条查询只需要回答三个问题:哪张表?哪一天?哪些状态? 这三个答案就是 WHERE 的全部内容。剩下的 SELECT 只负责选列和聚合。

日活
280
→
下单人数
37
→
GMV
395,466.51
图 11.2.1 6 月 1 日日报的三个数:来的人、付钱的人、付了多少钱

动手前先看一个会让新人翻车的细节:同样叫“6 月 1 日支付”,events 里只有 13 条支付事件、13 个人; orders 里却有 37 笔已支付订单、37 个人。差在哪?events 是前端埋点,它在本库里只覆盖了一部分行为 (全月 501 条支付事件,而 orders 有 15,615 笔已支付);orders 是交易系统,财务以它对账。 流量和漏斗用 events,成交和 GMV 用 orders。两个数都能自洽,但回答的不是同一个问题。

数据来源6 月 1 日支付人数适合回答
events(支付事件)13有多少人走到了支付这一步
orders(已支付)37有多少人真正完成了交易

日报还常带第四个数:客单价 = GMV ÷ 已支付订单数 = 395,466.51 ÷ 37 = 10,688.28。 它不需要新查询,用前两个数就能算出来,是最便宜的交叉验证。

为什么不写成一条 SQL?因为日活来自 events,下单人数和 GMV 来自 orders,粒度和口径都不同。 硬拼在一起要么 JOIN 出错,要么用子查询绕晕自己。第 12 章会用 GROUP BY 做“按天汇总”, 但那是“同一天、同一张表”的汇总;跨表指标,先分开算、再拼报表,永远是最稳的。

口径三问,背下来:这个数从哪张表来?算哪一天(时间边界是什么)?算哪些状态(要不要排除取消和退款)? 三个答案写进注释,别人问起时你就不用回忆。

11.3实验:日活、下单人数、GMV

八步,从确认数据范围到写出三个数。每一步都能独立运行,结果必须和正文一致。

已完成 0 / 8 预计 35 分钟
01

确认数据范围

3 分钟 · 对应规则 2
做什么
先确认 events 和 orders 各自覆盖哪段时间。
为什么
如果 6 月 1 日根本不在 events 的范围里,后面所有日活都是 0。先确认“有数据”,再谈“是多少”。
怎么做
SELECT MIN(event_time), MAX(event_time) FROM events;
SELECT MIN(order_date), MAX(order_date) FROM orders;
看到什么
events:2024-06-01 ~ 2024-06-30;orders:2023-01-01 ~ 2024-06-30。
卡住了 / 出错了

events 只有 6 月一个月,这正是它可以做日活、不能做全年分析的原因。别拿它算年度留存。如果 MIN/MAX 返回 NULL,说明表是空的,先别往下算。

02

日活第一版:先数事件条数

4 分钟 · 对应规则 2
做什么
数 6 月 1 日有多少条行为记录。
为什么
先跑一个“看起来对”的错答案,你才会真正理解 COUNT(*) 和 COUNT(DISTINCT) 的差别。
怎么做
SELECT COUNT(*) FROM events
WHERE event_time LIKE '2024-06-01%';
看到什么
536 条。这不是 536 个人,是 536 次行为。
为什么不是日活

一个用户可以浏览、加购、下单、支付,产生 4 条记录。把行为条数当人数,日活会被行为频次放大。

03

日活正确版:COUNT(DISTINCT user_id)

4 分钟 · 对应规则 2
做什么
把 536 条行为压成“有多少个不同的人”。
为什么
“活跃用户”的单位是人,不是次。DISTINCT 去重是日活的唯一正确算法。
怎么做
SELECT COUNT(DISTINCT user_id) FROM events
WHERE event_time LIKE '2024-06-01%';
看到什么
280。536 条行为来自 280 个用户,人均 1.9 次。
卡住了 / 出错了

如果得到 281 或别的数,检查日期条件是不是写成了 >= '2024-06-01' 却没写上界,把后面 29 天也算进去了。

04

下单人数:别忘了 status

5 分钟 · 对应规则 5
做什么
数 6 月 1 日真正付了钱的去重用户。
为什么
orders 里有已取消、已退款、待支付。不筛状态,你把“点了下单的人”当成了“付钱的人”。
怎么做
SELECT COUNT(DISTINCT user_id)
FROM orders
WHERE order_date LIKE '2024-06-01%'
  AND status = '已支付';
看到什么
37 人。不加 status 是 47 人——多出的 10 人只有取消或退款记录。
为什么不是 37 笔

这一天恰好是 37 笔已支付、37 个用户,一笔一人。换成别的日期就可能一人两单,所以人数必须用 COUNT(DISTINCT user_id),不能数订单行数。

05

GMV:用 pay_amount,不用 amount

5 分钟 · 对应规则 5
做什么
把 6 月 1 日已支付订单的实付金额加起来。
为什么
GMV 的口径是“实际收到的钱”。amount 是原价,pay_amount 才是原价减优惠。
怎么做
SELECT ROUND(SUM(pay_amount), 2) AS GMV
FROM orders
WHERE order_date LIKE '2024-06-01%'
  AND status = '已支付';
看到什么
GMV = 395,466.51。全部状态是 537,728.84;用 amount 则是 396,587.74。
三个数差在哪

537,728.84 − 395,466.51 = 142,262.33,这是 11 笔取消/退款订单的金额。

396,587.74 − 395,466.51 = 1,121.23,这是 37 笔已支付订单的优惠合计。

06

顺手算客单价,做交叉验证

3 分钟 · 对应规则 5
做什么
用 GMV ÷ 已支付订单数,验证前两步没有互相矛盾。
为什么
单看三个数看不出错,但“金额 ÷ 单数 = 客单价”必须自洽。这是一个廉价的对账手段。
怎么做
SELECT ROUND(SUM(pay_amount) / COUNT(*), 2) AS 客单价
FROM orders
WHERE order_date LIKE '2024-06-01%'
  AND status = '已支付';
看到什么
客单价 10,688.28;395,466.51 ÷ 37 = 10,688.28,对得上。
卡住了 / 出错了

除数是 COUNT(*) 不是 COUNT(DISTINCT user_id)。客单价的分母是订单数;一人两单要算两次。

07

换一种日期写法,结果必须一样

4 分钟 · 对应规则 2
做什么
把 LIKE 换成半开区间,再跑一次三个指标。
为什么
LIKE 依赖字符串前缀,半开区间是日期查询的标准写法。两种写法结果一致,才说明你理解的是边界,不是巧合。
怎么做
SELECT COUNT(DISTINCT user_id) AS 日活
FROM events
WHERE event_time >= '2024-06-01' AND event_time < '2024-06-02';

SELECT COUNT(*) AS 订单数,
       COUNT(DISTINCT user_id) AS 下单人数,
       ROUND(SUM(pay_amount), 2) AS GMV
FROM orders
WHERE order_date >= '2024-06-01' AND order_date < '2024-06-02'
  AND status = '已支付';
看到什么
日活 280;订单数 37,下单人数 37,GMV 395,466.51。与 LIKE 写法完全一致。
08

写进日报,附上口径

5 分钟 · 对应规则 5
做什么
保存三条 SQL,并在日报里写清每个数的表、日期、状态。
为什么
数字本身没有意义,口径才有。下周别人接手,看到“GMV 395,466.51”却不知道排除了退款,就会重复踩坑。
怎么做
  1. 新建 daily_report.sql,把三条查询贴进去,每条上面写一行注释
  2. 注释格式:-- 指标 / 表 / 日期 / 状态 / 金额字段
  3. 把三个结果填进日报表格
看到什么
日报三行:日活 280;下单人数 37;GMV 395,466.51。
卡住了 / 出错了

如果三个数里有一个和前几步不同,先检查是不是把 LIKE 和半开区间混用、漏了 status,或者把 amount 当成了 pay_amount。

11.4结果验证

先填三个数,再做五道选择题。数字对是底线,能解释为什么才是目标。

11.4.1三个数字

events 表,COUNT(DISTINCT user_id)
orders 表,status = '已支付'
SUM(pay_amount),不是 amount

11.4.2五道选择题

Q1计算日活时,为什么不能用 COUNT(*)?
解析:events 一行是一次行为。6 月 1 日 536 条行为只来自 280 个人,日活必须用 COUNT(DISTINCT user_id)。
Q2计算 GMV 时,为什么要加 status = '已支付'?
解析:不筛状态,6 月 1 日会从 395,466.51 变成 537,728.84,多出 142,262.33 的取消/退款金额。SQL 不会报错,数字会错。
Q3WHERE order_date = '2024-06-01' 为什么返回 0 行?
解析:值是 2024-06-01 00:19:28,不是 2024-06-01。用 LIKE '2024-06-01%' 或半开区间。实际有 48 笔订单。
Q4WHERE status = '已支付' AND city = '杭州' OR city = '成都' 等价于哪种写法?
解析:AND 先于 OR,所以先算“已支付且杭州”,再和“成都所有状态”取并集,得到 4,494。想要 3,969,必须给 OR 加括号。
Q5要取“金额最高的 3 笔已支付订单”,正确的写法是?
解析:Top N 必须三步:WHERE 定口径、ORDER BY 定顺序、LIMIT 截断。没有 ORDER BY 的 LIMIT 只是随便取三行;C 的语法顺序也错。
得分:0 / 5

11.5常见问题排查

日报的三个数最容易错在这几处。按“现象 → 原因 → 解决”对照。

现象原因解决
日活算成 536 用了 COUNT(*),数的是行为条数 改成 COUNT(DISTINCT user_id),得到 280
下单人数是 47 没加 status = '已支付' 补上状态条件,得到 37;差的 10 人只有取消/退款记录
GMV 是 537,728.84 没筛状态,把取消和退款也算进来了 加 AND status = '已支付',得到 395,466.51
GMV 比财务多 1,121.23 用了 amount(原价),没减优惠 改用 pay_amount
用 events 算支付人数只有 13 events 是前端埋点,不是交易系统 成交相关一律用 orders 的已支付订单,得到 37
日期条件返回 0 行 用了等值比较,或月份没补零 用 LIKE '2024-06-01%' 或半开区间
加了 OR 之后数字变大 AND 优先级高于 OR,条件被拆错 给 OR 分支加括号,再核对总数
LIMIT 的结果每次都不同 没有 ORDER BY,顺序不保证 先写 ORDER BY,再 LIMIT

11.6本章小结与练习

  1. 单表查询只有五个零件:SELECT 选列、FROM 选表、WHERE 筛行、ORDER BY 排序、LIMIT 截断。
  2. 列名就是口径。禁止 SELECT *,每一列都要能说出为什么出现在报表里。
  3. WHERE 只做行级过滤。比较、BETWEEN、IN、LIKE、IS NULL 五种写法,覆盖日常 90% 的条件。
  4. AND 先于 OR。句子里出现 OR,就给每个分支加括号;6 月 1 日的例子里,括号差 525 笔。
  5. Top N 三步走:WHERE 定口径 → ORDER BY 定顺序 → LIMIT 取前 N,一步都不能省。
  6. 日报三问:哪张表?哪一天?哪些状态?三个答案写进注释,数字才可解释。
  7. 同一指标可能有两个来源。events 的支付事件 13 人,orders 的已支付 37 人;流量看 events,成交看 orders。

11.6.1练习

  1. 统计 2024-06-02 的日活,以及当天的事件条数。两个数为什么不同?
  2. 统计 6 月 1 日“浏览”事件的行数和去重人数。这次为什么恰好相同?
  3. 统计 6 月 1 日已支付订单中,pay_amount 在 5,000 到 20,000 之间的订单数(含两端)。
  4. 下面这句返回 15,774 行,写出它的等价括号写法,并解释为什么差这么多:
    WHERE status = '已支付' OR status = '待支付' AND city = '杭州'
参考答案

1. 事件 573 条,日活 310 人。一个人当天可以产生多条行为,所以条数大于人数。

2. 行数 280,人数 280。当天每个活跃用户恰好只有一条“浏览”记录,所以两个数相同;换一天或换事件类型就可能不同,不能因此就用 COUNT(*) 算人数。

3. 23 笔。条件:order_date LIKE '2024-06-01%' AND status='已支付' AND pay_amount BETWEEN 5000 AND 20000。

4. 原句被理解为“已支付(全部)”或“待支付且杭州”,共 15,615 + 159 = 15,774。括号写法是 WHERE (status='已支付' OR status='待支付') AND city='杭州',结果 2,050 + 159 = 2,209。差 13,565 行,全是其他城市的已支付订单。

下一章预告:三条查询只能给出三个孤立的数。运营下一句一定是“按渠道拆一下”“按城市拆一下”。 第 12 章用 GROUP BY 把一张表压成很多组,一次算出所有维度的日报——但前提仍然是本章的 WHERE 口径。
数据分析师训练营 · 第十一章
SQLite 3 · 所有查询均在 qingning.db 上跑通