# 数据分析 · 第 02 篇｜数据准备与 SQL 取数

> 系列：project / 数据分析（共 12 篇）｜AI生成｜2026-10-27｜codebuddy

这一篇讲什么：上一篇说「先想清楚要什么数」，这一篇说「数到底怎么拿到、为什么拿到的是错的」。绝大多数分析事故不是方法用错，而是取数阶段就埋了雷：表连接让金额翻倍、分母悄悄换了一个、退款被算进 GMV、时区把月末两天挪到了下个月。这一篇用北辰优选的 users / orders / events 三张表，从「一行代表什么」讲起，走完 SELECT、JOIN、窗口函数、清洗、口径五关，最后用一个完整案例算出 3 月各渠道新客首单转化率。读完你应该能做到两件事：独立写出一段可复现的取数 SQL，以及在别人交给你一个数时，知道该问哪三个问题来验证它。

## 一、先把表读懂：一行代表什么

拿到一张表，不要先写 SELECT 星号。先回答三个问题：一行代表什么（粒度）、用什么唯一标识一行（主键）、哪些字段能分组、哪些字段能加总。

| 表 | 一行代表 | 主键 | 常用维度 | 常用度量 | 常见坑 |
| --- | --- | --- | --- | --- | --- |
| users | 一个注册用户 | user_id | city、channel、signup_date | 用户数（计数） | 一个用户可能有多条渠道记录（多渠道归因） |
| orders | 一次下单 | order_id | order_date、status、coupon、user_id | amount | 加了商品明细后，一行变成「一个订单行」 |
| events | 一次行为事件 | 无天然主键，用 user_id + event_time + event_name 近似 | event_name、page、event_time | 事件次数（PV）、去重用户数（UV） | 同一秒可能有多条，去重会误伤 |

**粒度是这一切的地基。** orders 一行是一个订单，那么 SUM(amount) 就是订单金额之和；一旦有人在 orders 后面 JOIN 了订单商品明细表（一个订单三件商品就是三行），再 SUM(amount)，金额会变成三倍。表面上看不出错——SQL 不报错，数字还挺好看——但你的 GMV 已经悄悄膨胀了。

**维度和度量的区别，决定了你写 GROUP BY 还是写 SUM。** 维度是「用来切分的标签」：城市、渠道、日期、状态。度量是「可以计算大小的数」：金额、次数、时长。把度量当维度（GROUP BY amount）几乎一定是错的；把维度当度量（SUM(city)）连语法都过不去。有一种特殊情况要小心：user_id 既是维度也是「计数对象」，COUNT(DISTINCT user_id) 才是用户数，COUNT(user_id) 在连接后会被放大。

**主键是否唯一，必须亲手验证。** 一句 SQL 就够：

```sql
SELECT COUNT(*) AS rows_cnt,
       COUNT(DISTINCT order_id) AS distinct_orders
FROM orders;
```

两个数不相等，说明这张表已经不是「一行一单」了，后面所有 SUM 都要打问号。这是我每个项目开始时都会跑的第一条语句。

## 二、SQL 取数的最小闭环

一段完整的取数 SQL，结构永远是六段：SELECT 取什么、WHERE 筛哪些行、GROUP BY 按什么分组、HAVING 筛哪些组、ORDER BY 怎么排、LIMIT 取多少。

需要记住的是**书写顺序和执行顺序不一样**：你先写 SELECT，数据库却先执行 FROM 和 WHERE，再 GROUP BY，再 HAVING，最后才轮到 SELECT 里的别名和 ORDER BY。这解释了两个常见困惑：WHERE 里不能直接用 SELECT 起的别名；聚合函数不能写在 WHERE 里，要写在 HAVING 里。

先算一个最基础的数：北辰优选 2026 年 3 月的支付 GMV。

```sql
SELECT
    SUM(o.amount)                                        AS gmv,
    COUNT(DISTINCT o.order_id)                           AS paid_orders,
    ROUND(SUM(o.amount) / COUNT(DISTINCT o.order_id), 2) AS aov
FROM orders o
WHERE o.status = 'paid'
  AND o.order_date >= '2026-03-01'
  AND o.order_date <  '2026-04-01';
```

三个细节值得停顿。

一是状态过滤放在 WHERE 里，不是放在 HAVING 里。它筛的是行，不是组。放错位置，SQL 要么报错，要么在分组后才过滤，语义完全不同。

二是日期用「左闭右开」：大于等于 3 月 1 日、小于 4 月 1 日。不要写成 BETWEEN 3 月 1 日 AND 3 月 31 日——如果 order_date 是带时分秒的时间戳，3 月 31 日 12:00 的单会被漏掉。这是月度报表最常见的少算原因，通常少 0.3%—3%，取决于下单的时间分布。

三是用 COUNT(DISTINCT order_id) 而不是 COUNT 星号。当前这条 SQL 没有连接，两者相等；但只要你后面加了一个 JOIN，COUNT 星号就会变成「连接后的行数」，而 COUNT(DISTINCT) 仍然正确地数订单。养成这个习惯，能省掉后面很多返工。

结果（示意值）：GMV 12,800,000 元，支付订单 37,100 单，客单价 345 元。这三个数要和财务口径、上游订单系统对得上，对不上就先别往下做。

想按月看趋势，把日期截断成月再分组：

```sql
SELECT
    DATE_TRUNC('month', o.order_date) AS mon,
    SUM(o.amount)                     AS gmv
FROM orders o
WHERE o.status = 'paid'
  AND o.order_date >= '2025-04-01'
  AND o.order_date <  '2026-04-01'
GROUP BY 1
ORDER BY 1;
```

GROUP BY 1 指按第一个选择列分组，是多数数据库都支持的简写。但要注意：换了数据库，日期截断函数名会变（有的用 DATE_FORMAT，有的用 strftime）。函数名可以查，口径不能猜。

## 三、JOIN：一对多是最常见的金额放大器

JOIN 有两种最常用的类型，区别只有一句话：INNER JOIN 只保留两边都匹配上的行，LEFT JOIN 保留左表全部行、右表没匹配上的补 NULL。

| 类型 | 保留什么 | 北辰场景 | 什么时候用 | 什么时候会坑你 |
| --- | --- | --- | --- | --- |
| INNER JOIN | 两边都匹配的行 | 只统计「有支付记录」的用户 | 你确定右表一定存在且必须存在 | 右表缺失的行被静默丢掉，总量偏小 |
| LEFT JOIN | 左表全部 + 右表匹配 | 全部新客，附上他们的首单信息 | 要找「没有发生某事」的人（未支付、未复购） | 右表一对多时，左表行被复制，度量翻倍 |

**LEFT JOIN 最危险的地方，不是它丢数据，是它复制数据。** 看一个真实感很强的例子。

北辰 3 月有 37,100 笔支付订单，其中 1,240 笔发生过退款，退款表里一笔订单可能有多条退款记录（部分退款、多次退款）。如果你直接连接：

```sql
-- 错误示范：直接连接，金额被退款记录放大
SELECT SUM(o.amount) AS gmv_wrong
FROM orders o
LEFT JOIN refunds r ON o.order_id = r.order_id
WHERE o.status = 'paid'
  AND o.order_date >= '2026-03-01'
  AND o.order_date <  '2026-04-01';
```

1,240 笔订单平均有 1.6 条退款记录，多出来的 744 行会重复计入金额，GMV 从 1,280 万变成约 1,341 万，虚增 4.8%。这个误差足以把「跌 8%」改写成「跌 3.5%」，让整个结论方向反转。

正确做法是**先在子查询里把右表聚合到与左表相同的粒度，再连接**：

```sql
WITH refund_agg AS (
    SELECT order_id,
           SUM(refund_amount) AS refund_amt,
           COUNT(*)           AS refund_cnt
    FROM refunds
    GROUP BY order_id               -- 先把粒度压回「一单一行」
)
SELECT
    SUM(o.amount)                                  AS gmv,
    SUM(COALESCE(r.refund_amt, 0))                 AS refund_amt,
    SUM(o.amount) - SUM(COALESCE(r.refund_amt, 0)) AS net_gmv
FROM orders o
LEFT JOIN refund_agg r ON o.order_id = r.order_id
WHERE o.status = 'paid'
  AND o.order_date >= '2026-03-01'
  AND o.order_date <  '2026-04-01';
```

这个「先聚合、后连接」的模式，能解决八成 JOIN 膨胀问题。它的本质是：**连接前，确保两边的粒度一致。**

还有一种更隐蔽的膨胀：orders JOIN events。有人想算「下单用户在下单前看了几个商品」，就写 orders 和 events 按 user_id 连接。问题是 events 是用户级、订单级完全不对齐——一个用户有 200 条行为，他那 3 笔订单每笔都会被复制 200 次，SUM(amount) 变成 600 倍。正确做法是先把行为按 user_id 聚合成「下单前 24 小时浏览商品数」，再按 user_id 连接。

**验证 JOIN 有没有出问题，有三个动作，建议每次都做：**

1. 连接前后各跑一次行数和主键去重数，看行数是否被放大。
2. 连接前后各跑一次 SUM(amount)，看度量是否变化。没变化不一定是安全的（可能刚好没重复），变化一定有问题。
3. 单独查一下右表的主键唯一性：按 order_id 分组计数、筛计数大于 1，有空结果才说明是一对一。

## 四、窗口函数：在不改变行数的前提下做组内计算

窗口函数是 SQL 里性价比最高的一个工具。它和 GROUP BY 的区别是：GROUP BY 会把多行压成一行，窗口函数保留每一行，只是给每行附加一个「组内算出来的值」。

第一个常用场景：取每个用户的首单。

```sql
WITH ranked AS (
    SELECT
        o.order_id,
        o.user_id,
        o.order_date,
        o.amount,
        ROW_NUMBER() OVER (
            PARTITION BY o.user_id
            ORDER BY o.order_date, o.order_id
        ) AS rn
    FROM orders o
    WHERE o.status = 'paid'
)
SELECT user_id, order_id, order_date, amount
FROM ranked
WHERE rn = 1;
```

PARTITION BY user_id 是把数据按用户分组，ORDER BY order_date 是组内排序，ROW_NUMBER 给每行一个 1、2、3 的序号。rn 等于 1 就是首单。

这里有个细节必须处理：**同一天下两单怎么办？** 只按 order_date 排序，会在同日期时产生不确定的顺序，同一条 SQL 跑两次可能给出不同的「首单」。解决办法是加一个稳定的第二排序键，比如 order_id，让结果可复现。这个习惯在审计场景里很重要——别人复现不出你的数，你的结论就没有说服力。

顺带区分三个常被混用的函数：ROW_NUMBER 永远不重复（1、2、3），RANK 遇并列会跳号（1、1、3），DENSE_RANK 遇并列不跳号（1、1、2）。取首单用 ROW_NUMBER，做排行榜用 RANK。

第二个场景：算累计 GMV。

```sql
WITH daily AS (
    SELECT order_date,
           SUM(amount) AS gmv
    FROM orders
    WHERE status = 'paid'
      AND order_date >= '2026-03-01'
      AND order_date <  '2026-04-01'
    GROUP BY order_date
)
SELECT
    order_date,
    gmv,
    SUM(gmv) OVER (ORDER BY order_date
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_gmv
FROM daily
ORDER BY order_date;
```

显式写 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，意思是「从第一行累加到当前行」。不写这一句，多数数据库默认也是这个范围，但显式写出来更安全，尤其是在有并列日期或多层分组时。

累计曲线在实战里很有用：它能一眼看出「这个月的目标是靠月初冲的，还是均匀完成的」。北辰 3 月的累计 GMV 曲线在前 5 天就走完了全月的 24%，明显高于正常月份的 16%，说明月初有一波促销，这也解释了后面转化率为什么会掉——需求被提前透支了。

## 五、数据清洗：六类脏数据和处理顺序

清洗不是「把数据弄干净」，是「知道什么地方脏、脏了多少、脏了会怎样」。顺序上建议：先去重和排除测试账号，再处理缺失，再看异常，最后核对时间边界和状态。

| 问题类型 | 识别方法 | 处理建议 | 北辰示例 |
| --- | --- | --- | --- |
| 缺失值 | 总行数减非空行数，算缺失率 | 关键字段（user_id、amount、status）缺失直接剔除；维度字段缺失归为「未知」 | 3 月 0.4% 订单 user_id 为空，多为游客下单，单独归组不做用户分析 |
| 重复值 | 主键行数对比去重行数 | 按主键取最新一条（用 ROW_NUMBER），并记录重复量 | 订单表主键唯一，但 events 有同一秒重复上报，需按用户+时间+事件名去重 |
| 异常值 | 分位数、箱线图、业务上下限 | 先判断是录入错误还是真实极端值，不要一律删除 | 出现金额 999,999 的测试单 3 笔，占 GMV 0.02%，剔除 |
| 时区 / 日期边界 | 对比事件时间与下单日期的日期分布 | 统一到同一时区；区间用左闭右开 | 服务器存 UTC，需加 8 小时才是北京时间，否则 3 月 31 日 23:00 的单会算到 4 月 |
| 退款与取消 | 按状态分组看分布 | paid / refunded / cancelled 分开算，GMV 默认只算 paid | 3 月退款 1,240 单，金额 61 万，占支付 GMV 4.8% |
| 测试账号 | 用户 ID 前缀、内部员工名单、金额 0.01 | 建黑名单表，每次取数都 LEFT JOIN 排除 | 内部账号 12 个，若混入会让 3 月新客数虚增 0.09% |

三条经验值得单独说。

**异常值不要急着删。** 先问一句：它是录入错误，还是真实的大客户？北辰有一笔 8 万元的订单，看着像异常，查下来是企业团购，删掉会低估 B 端占比。判断方法是用业务规则兜底，而不是用统计规则一刀切。

**时区问题在日报里看不出来，在月报里会咬人。** 如果服务器存的是 UTC，北京时间 3 月 31 日 23:30 的订单存成 3 月 31 日 15:30 UTC，看起来还在 3 月；但北京时间 4 月 1 日 02:00 的订单会存成 3 月 31 日 18:00 UTC，被错误地算进 3 月。一个月错两天，误差通常在 0.5% 以内，但足以让环比和同比对不上。

**测试账号是沉默的污染源。** 它们金额小、频次高、行为规律，混在里面会让转化率和留存率虚高。最省事的做法是维护一张内部账号黑名单，在数据层就排除，而不是每个分析师各自在 SQL 里写排除条件——总有人会忘。

## 六、指标口径：同一个词，不同的数

这一节是第 02 篇的核心。口径不是文档细节，它决定结论。

| 指标 | 常见定义 A | 常见定义 B | 差异有多大 | 什么时候用哪个 |
| --- | --- | --- | --- | --- |
| GMV | 支付订单金额之和（paid） | 下单金额之和（含未支付、含取消） | 示例中相差 11% | 对外披露、考核用支付口径；看需求热度可用下单口径 |
| 支付订单数 | 订单数（order_id 去重） | 订单行数（含商品明细） | 一单多品时相差 1.8 倍 | 一律用订单数，除非分析商品结构 |
| 客单价 | GMV ÷ 支付订单数 | GMV ÷ 支付用户数 | 示例中 345 元 vs 402 元 | 前者看「每单买多少」，后者看「每人花多少」 |
| 转化率 | 支付用户数 ÷ 访问 UV | 支付订单数 ÷ 访问 PV | 示例中 3.3% vs 4.9% | 增长分析用 UV 口径，页面优化用 PV 口径但要注明 |
| 留存率 | 第 30 天当天有访问 ÷ 当期注册 | 30 天内任意一天有访问 ÷ 当期注册 | 示例中 18% vs 41% | 严格留存用前者，活跃度用后者，不能混用 |

口径要写清三件事：**分子是什么、分母是什么、时间怎么归属。** 少一件，这个数就不可复现。

举个北辰的例子。3 月「新客首单转化率」至少有三种算法：

1. 分母是 3 月所有访问 UV，分子是其中完成首单的人——这是「全站转化率」，老客也被算进分母。
2. 分母是 3 月注册的新客，分子是这些人在 3 月内完成首单的人——这是「新客当月转化率」。
3. 分母是 3 月注册且注册当天有访问的新客，分子是这些人在注册后 7 天内完成首单的人——这是「新客 7 日激活率」。

三种算法算出来可能是 3.3%、5.1%、12.4%，都叫「新客转化率」。开会时两个人各拿一个数，争半小时，最后发现口径不同——这是分析团队最常发生的内耗。

解决办法很笨但有效：**每个交付物第一屏写三行口径说明，且全篇不改。** 北辰这次用的是第 3 种，写清楚「注册后 7 日内、以支付时间为准、退款单不回溯剔除」。

## 七、案例：3 月各渠道新客首单转化率

需求来自增长团队：3 月新客首单转化率从 4.2% 掉到 3.3%，想知道是哪个渠道拖的。

先定口径：分母是 3 月注册、且注册当天有 view 行为的用户；分子是其中在注册后 7 天内完成首笔 paid 订单的用户；渠道取 users.channel。

```sql
WITH new_users AS (
    SELECT user_id, channel, signup_date
    FROM users
    WHERE signup_date >= '2026-03-01'
      AND signup_date <  '2026-04-01'
),
activated AS (                       -- 注册当天有访问的人，构成分母
    SELECT DISTINCT u.user_id, u.channel
    FROM new_users u
    JOIN events e
      ON e.user_id = u.user_id
     AND e.event_name = 'view'
     AND CAST(e.event_time AS DATE) = u.signup_date
),
first_pay AS (                       -- 注册后 7 天内首笔支付，构成分子
    SELECT DISTINCT u.user_id, u.channel
    FROM new_users u
    JOIN orders o
      ON o.user_id = u.user_id
     AND o.status = 'paid'
     AND o.order_date >= u.signup_date
     AND o.order_date <  u.signup_date + INTERVAL '7' DAY
)
SELECT
    a.channel,
    COUNT(DISTINCT a.user_id)             AS denom_users,
    COUNT(DISTINCT p.user_id)             AS numer_users,
    ROUND(COUNT(DISTINCT p.user_id) * 1.0
          / COUNT(DISTINCT a.user_id), 4) AS cvr
FROM activated a
LEFT JOIN first_pay p
       ON p.user_id = a.user_id
GROUP BY a.channel
ORDER BY cvr DESC;
```

这段 SQL 有三个设计点。

第一，分母用注册当天有访问的人，而不是全部注册用户。因为注册了但当天没打开的人，根本没进入转化流程，把他们放进分母会把转化率整体压低，且各渠道压低程度不同（信息流渠道有大量「误点注册」，会显得特别差）。

第二，分子用 LEFT JOIN 而不是 INNER JOIN。用 INNER 也能算出转化率，但会丢掉「零转化」的渠道——如果一个渠道一个人都没转化，它会从结果里消失，而不是显示 0%。这类「消失的组」在对比时非常危险。

第三，日期条件用「大于等于注册日、小于注册日加 7 天」，而不是两头都闭的 BETWEEN。后者会把第 8 天也算进去，分子虚高约 6%。

结果（示意值）：

| 渠道 | 分母：注册且有访问 | 分子：7 日内首单 | 首单转化率 |
| --- | --- | --- | --- |
| 老客推荐 | 25,000 | 1,650 | 6.60% |
| 搜索 | 65,000 | 3,250 | 5.00% |
| 信息流 | 370,000 | 10,360 | 2.80% |
| 合计 | 460,000 | 15,260 | 3.32% |

对照 2 月的 4.2%：信息流从 4.1% 掉到 2.8%，搜索从 5.2% 到 5.0%，老客推荐从 6.8% 到 6.6%。搜索和推荐基本稳定，信息流单独掉了 1.3 个百分点。

再补一步结构性检查：信息流的 UV 占比从 2 月的 78% 升到 3 月的 80%，而它的转化率最低。这意味着即使各渠道转化率都不变，总体转化率也会因为结构变化而下降约 0.1 个百分点。这就是辛普森悖论的雏形——总体在跌，不一定每个部分都在跌。北辰这次是「部分真的跌了 + 结构略微恶化」两个因素叠加，前者占九成。

到这一步，取数任务完成：结论可复现、口径可追溯、结构因素已排除。至于「为什么信息流转化率掉了」，那是第 04 篇的事。

## 八、错误清单：六种最常见的取数事故

| 症状 | 根因 | 修法 |
| --- | --- | --- |
| GMV 比财务多 3%—5% | JOIN 退款表或商品明细表导致一对多膨胀 | 先按主键聚合右表，再连接；连接前后比对 SUM |
| 转化率忽高忽低，重跑就变 | 分子分母时间窗不一致，或用 PV 代替 UV | 分子分母写在同一段 SQL 里，统一时间条件 |
| 月度数据对不上，差两天 | 时区未统一，或用了闭区间 BETWEEN | 统一时区；日期一律左闭右开 |
| GMV 包含退款，实际收入被高估 | 只过滤了下单，没过滤状态 | 明确 paid / refunded / cancelled，GMV 只取 paid |
| 用户数偏大，人均指标偏低 | 用 COUNT(user_id) 而非去重计数 | 所有去重口径显式写 DISTINCT |
| 新渠道「没有数据」而不是「转化率 0」 | INNER JOIN 丢掉了无匹配的组 | 用 LEFT JOIN，零值显式呈现 |

这六条覆盖了我在实际项目里见过的绝大多数取数事故。它们的共同点是：**SQL 不会报错，数字看起来合理，只有交叉验证才能发现。** 所以最后给一个习惯——任何要发出去的数字，至少用两种方式算一遍：SQL 算一次，从明细导出在 Excel 里透视一次；两个数对不上，先怀疑自己，不要怀疑数据。

## 本篇要点回顾

1. 一张表先问「一行代表什么」，粒度错了后面全错；orders 一旦被商品明细或退款表连接，SUM(amount) 就不再是 GMV——示例中一次错误 JOIN 让 1,280 万变成 1,341 万，虚增 4.8%。
2. 取数 SQL 的执行顺序是 FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY，所以聚合条件不能写在 WHERE，日期一律左闭右开，避免漏掉月末最后一秒。
3. JOIN 膨胀的通用解法是「先聚合到同一粒度、再连接」；连接前后必须比对行数和 SUM，两个都看，缺一不可。
4. 窗口函数的价值是「保留每一行、附加组内计算」：ROW_NUMBER 取首单要加稳定的第二排序键，SUM OVER 算累计要显式写窗口范围。
5. 口径写三件事——分子、分母、时间归属；北辰的「新客首单转化率」有三种算法，分别得 3.3%、5.1%、12.4%，开会吵架多半是这一行没写清。

## 延伸阅读

- **第 01 篇《数据分析是什么》**：本篇处理的是七步工作流里的第 3、4 步（口径与取数），如果还不清楚「为什么要先有可分析的问题」，先回看第 01 篇第四、五节。
- **第 03 篇《描述性分析与指标体系》**：本篇算出的转化率、留存率，在第 03 篇会被组织成指标树和漏斗，回答「这些数该放在哪一层看」。
- **第 04 篇《诊断性分析与归因》**：本篇案例停在「信息流转化率掉了 1.3 个点」，第 04 篇接着往下钻，判断这是改版、流量结构还是埋点的问题。
- **第 08 篇《工具使用：Excel、SQL、Python、BI》**：如果本篇的窗口函数和 CTE 还不够熟，第 08 篇给了可套用的模板和四种工具的边界。
- **第 09 篇《行业实战：电商与 SaaS》**：本篇的三张表在电商篇会被扩展成完整的增长分析链路，包含复购和退款率。