数据分析的核心能力不是SQL，而是“判断数据能不能用“

课程：B站大学
记录学习数仓开发，进阶数据分析和数据处理

---

# 从测试到数据分析：一堂"非典型SQL课"教会我的三件事

标签：数据分析SQL数据清洗指标体系CRISP-DM转行经验

写在前面

这节是课程第二周的收尾课，形式跟平时的语法课完全不一样——没有新语法，全程三位老师对着作业题讲思路 + 现场答疑。作业共三道题：

练习	场景	考察点
一	订单明细数据	数据探查、清洗、算 GMV/DAU/ARPU
二	手淘搜索业务	设计指标体系（北极星 + 拆解）
三	"一场秋雨一场凉"民谚	构思一份数据分析报告的思路（腾讯面试真题）

我的背景：学过 Excel 常用函数、数据透视表，SQL 刚过一遍SELECT / WHERE / ORDER BY / GROUP BY / HAVING，Python 只会最基础的读取。所以这节对我而言是个很好的**"查漏"场景**——看看一个真实需求来了，前面学的语法到底怎么串起来用。

老师开场一句话我印象很深：运营给你提需求时不会说得明明白白，最多就是"你给我做个看板，我要这个"。所以这节作业的真正目的，是模拟你作为数据分析师进公司接到的第一个需求——把下一步分析要用的数据先弄出来。

本文采用金字塔原理组织：先给结论，再分层展开，最后落到可复用的代码模板和踩坑记录。文中流程图由 Python(matplotlib) 绘制，框架图用 Mermaid 表达，可直接在 CSDN 渲染。

---

一、核心结论（金字塔尖）

数据分析师的核心竞争力，不是写 SQL，而是"判断数据能不能用"的能力。

这堂课用三道练习题，帮我建立了完整的工作链路认知：

一句话总结：拿到需求 → 摸清数据 → 定义口径 → 清洗计算 → 产出结论，这条链路比任何单个函数都重要。

---

二、练习一：订单明细数据的探查与清洗

2.1 完整工作链路

拿到一张表，正确的顺序是：找数据 → 摸清表结构 → 逐字段探查 → 处理脏数据 → 算指标 → 产出时间序列表。

图例说明：主链路 ①→④ 是线性推进，⑤⑥ 是最终产出；橙色框是串联在链路中的四个核心语法点，也是本文 2.3~2.6 节的重点。

2.2 第一步：先判断"有没有、能不能用"

如果业务成熟，数据已沉淀在标准库里，从明细表直接洗就能用；但刚接需求时，第一件事是先确认这份数据存不存在、来源靠不靠谱、质量行不行——这就是作业第一步为什么要"摸底"。

踩坑①：导入数据时一定勾选"第一行作为列名"，否则字段名变成C1/C2/C3/C4，后面写 SQL 会很痛苦。

摸清表结构的两件事：

• 看主键：第一列（无字段名）才是主键，不重复；而直觉上以为唯一的order_id其实不是主键，它到底有没有重复，需要后面用HAVING验证。
• 看字段类型：本例所有字段都是TEXT。文本类型不能直接参与SUM这类计算——这是 2.3 节CAST的直接原因。

2.3 核心语法一：CAST 类型转换

price是 TEXT，直接WHERE price = 0或SUM(price)会出问题，必须先转数值型：

-- 把 TEXT 类型的 price 转成浮点数再比较SELECT*FROMorder_detailWHERECAST(priceASDOUBLE)=0;

理解：CAST的作用是把字段在计算时转换成另一种数据类型。拿到一张表发现类型不匹配，就用它转。这是 SQL 清洗里出现频率极高的语法。

2.4 核心语法二：区分 NULL 与空字符串

很多人（包括我）容易把NULL和空字符串''当成一回事，实际是两个概念：

NULL	空字符串''
含义	字段里"没有东西"	字段里"有东西"，只是长度为 0
验证	LEN()返回 NULL	LEN()返回 0
过滤	IS NULL	= ''

空字符串是怎么来的？老师用一张 category 映射表（类似 Excel 的 VLOOKUP）解释：category_id唯一，category_code通过 id 去映射表查出来。若映射表里有这条 id 但没维护好，带过来的就是空字符串；若根本没有这条 id，带过来就是 NULL。

启示：category_code / brand缺失量大的背后，是映射表维护有问题——这种问题一定要反馈给运营，否则按类目/品牌拆分分析时，大量数据会错误归类。

2.5 核心语法三：用 HAVING 验证 order_id 是否重复

回到"疑似重复"的问题：前两行order_id一模一样。两种可能：一是表里存的就是"一行一商品"（买 3 件就 3 行），二是真的重复计数了。验证方式很巧妙——用 HAVING 比较"去重"和"不去重"的 product 数量：

SELECTorder_id,COUNT(product_id)AScnt_all,COUNT(DISTINCTproduct_id)AScnt_distinctFROMorder_detailGROUPBYorder_idHAVINGCOUNT(product_id)<>1-- 找出含多个商品的订单ANDCOUNT(product_id)=COUNT(DISTINCTproduct_id);-- 且去重后数量一致

结果解读：同一个order_id下有多个不同的product_id，且cnt_all = cnt_distinct，说明一个订单买了两件不同商品，表就是这样设计的，不是重复数据。

再反向验证order_id下有没有不同的user_id——结果为空，说明一个订单只对应一个用户，表没有问题。

方法论：这套"用 HAVING 交叉验证表设计"的思路，比直接下结论高级得多。

2.6 核心语法四：SUBSTRING 切字符串 + 日期格式统一

event_time是 TEXT 还带 UTC 字符，没法直接当日期用。处理两步：

1. SUBSTRING（类似 Excel 的 LEFT）把时间字符串"切一刀"，去掉末尾 UTC 标记；
2. 转成20200424这种年月日格式（去掉横杠），方便按天分组。

-- 示意：切出日期部分并转成 YYYYMMDDSELECTSUBSTRING(event_time,1,10)ASdate_strFROMorder_detail;

统一日期形态是个人/团队习惯，无所谓对错，但你要清楚自己用的数据形态是什么。

2.7 核心指标计算

探查完、清洗完，终于到算指标环节：

指标	算法	说明
GMV	SUM(price)	成交额
DAU	COUNT(DISTINCT user_id)	没有登录数据时，可自定义为"支付/下单即活跃"
订单量	COUNT(DISTINCT order_id)
ARPU	GMV / DAU	与 ARPPU（收入/支付用户数）不同

清洗要点：日期做大小比较时，字符串必须加引号（WHERE report_date > '2020-01-05'）；同时把 0 元价格、1970 年数据全部过滤掉。这一步是**“逻辑上的数据清洗”**——把有问题的字段干掉，再去做聚合。

SELECTSUBSTRING(event_time,1,10)ASorder_date,SUM(CAST(priceASDOUBLE))ASGMV,COUNT(DISTINCTuser_id)ASDAU,COUNT(DISTINCTorder_id)ASorder_cnt,SUM(CAST(priceASDOUBLE))/COUNT(DISTINCTuser_id)ASARPUFROMorder_detailWHERECAST(priceASDOUBLE)>0ANDevent_timeNOTLIKE'1970%'GROUPBYSUBSTRING(event_time,1,10);

2.8 进阶套路：CREATE TABLE AS + DROP TABLE IF EXISTS

真实工作里（尤其大厂 Hive 环境），不会把指标结果导到 Excel，而是直接建一张汇总表，供 PowerBI/Tableau 或后续 SQL 引用：

DROPTABLEIFEXISTSsales_order_summary;-- 保证可重复运行（幂等）CREATETABLEsales_order_summaryASSELECTSUBSTRING(event_time,1,10)ASorder_date,SUM(CAST(priceASDOUBLE))ASGMV,COUNT(DISTINCTuser_id)ASDAUFROMorder_detailWHERECAST(priceASDOUBLE)>0ANDevent_timeNOTLIKE'1970%'GROUPBYSUBSTRING(event_time,1,10);

DROP TABLE IF EXISTS的作用是保证可重复运行：表没建成也能跑通，建成了下次先删再建，避免"表已存在"报错。这是工程上非常常用的幂等写法。

2.9 CASE WHEN 处理空分类（给业务看的证据）

做可视化时，空字段直接展示对业务不友好。用CASE WHEN把空串分类归到"no_category"，再算 GMV：

SELECTCASEWHENcategory_code=''THEN'no_category'ELSEcategory_codeENDAScategory,SUM(CAST(priceASDOUBLE))ASgmvFROMorder_detailGROUPBYcategory;

结果里no_category的年度 GMV 相当可观——这就是给运营看的证据：分类映射不好好维护，这么大的体量都没法归类分析。数据分析师和业务沟通时，最有力的工具就是这种"数字摆出来"的方式。

---

三、练习二：搜索业务指标体系设计

这道题没有标准答案，老师带着从业务理解开始推。

3.1 先理解业务：搜索在手淘首页的流量位逻辑

手淘首页结构：搜索框（顶部巨大流量位）→金刚位（各 BU 抢着放 icon）→推荐流（千人千面）。整个界面没有商品类目导航，就是要你"搜和逛"。用户旅程简化为三个词：搜 → 看 → 买。

3.2 北极星指标：搜索贡献的 GMV

老师的结论：北极星定为**“搜索贡献的 GMV”**，且有两种口径：

• 窄口径：本次搜索链路末尾直接下单的 GMV 才算搜索贡献；
• 宽口径：只要搜索过某类目、最终买了该类目商品，就算搜索贡献（哪怕第二次从收藏/历史足迹进来下单）。

宽口径更符合用户真实习惯（搜完去比价、收藏、回头再买），考核更温和；窄口径会逼着算法在链路内激进促成。

现场质疑：搜完收藏但没下单算不算贡献？GMV 会不会跟着大盘走、体现不出团队差异？
老师回答：指标设置是"艺术"，不是纯科学——要考虑 KPI 导向会不会导致动作变形；同时北极星要简单直白、能自上而下传达战略，太生僻的指标层层传递下来损耗很大。

3.3 指标体系拆解：搜得到 → 搜得快 → 有转化

从用户视角出发，围绕"好的搜索 = 效率 + 质量"展开：

维度	指标	可控性
有效性（搜得到）	搜索结果点击率、搜索无结果率、报错率	强
效率（搜得快）	平均耗时、TOP5 点击率、平均点击位置	强
转化（买不买）	加购率、收藏率、购买转化率	弱

关键洞察：搜索团队的可为点在"搜得到、搜得快"（可控性强），“看了买不买"可控性弱——用户买不买取决于价格和商品功能，搜索能做的只有"如何呈现”。让搜索背 GMV，是为了让它即使可控性弱也得去努力、去跟商品团队配合发现问题。

3.4 用 UGM 和 AARRR 补全视角

• UGM（用户旅程地图）：把搜索入口 → 搜索主页 → 结果页 → 商品详情页 → 收藏 → 下单支付完整路径画出来，每个环节定义曝光/点击/浏览/访问深度指标。抓手不在 GMV 本身，而在旅程的每个环节。
• AARRR（海盗法则）：从增长视角补充入口曝光、搜索 DAU/WAU/MAU、渗透率（大盘 DAU 中用过搜索的比例）、搜索留存等。

老师总结：指标体系没有唯一正确答案，关键是"你怎么把课程里学到的框架真正用进一个具体业务"。比记住海盗法则、OSM 更重要的是——面对真实业务时，能顺着用户逻辑把指标一层层拆出来。

---

四、练习三：秋雨民谚与 CRISP-DM 分析思路

这题考察的不是跑数能力，而是思维框架。核心工具是 CRISP-DM 模型。

4.1 CRISP-DM 六阶段

4.2 应用到"一场秋雨一场凉"

针对"五场秋雨不穿单，十场秋雨要穿棉"写分析报告构思：

阶段	关键动作	落地示例
业务理解	明确为什么做	验证性（这句话对不对）+ 实用性（天气 APP 推关怀、电商据此备货）
数据理解	先把口径定义清楚（最容易忽略！）	降到几度算"不穿单"？下多大算"一场雨"？界定哪个地域？
数据准备	获取立秋后各地降水+温度，处理空值异常	气象数据，整理成时序/面板数据
建模	求降水场次与温度下降的相关性	华北/中原 3~5 场后降到 10℃ 以下
评估+发布	修正模型，回馈业务目标	"五场"说法过于严苛，实际 3~5 场即可

最容易忽略的"数据理解"三口径：

• 温度口径：降到多少度算"不穿单"（如 10℃ 以下）？多少度算"要穿棉"（如 5℃ 以下）？
• 雨的口径：下多大算"一场雨"？一天降水 10mm 以上才算？
• 地域口径：南方 9 月后天天阴雨、北方一场秋雨就入冬、西北一年下不了几场雨——必须界定地域。

全课最值得记的一句话：大家做数据分析最容易忽略的恰恰是"业务理解"和"数据理解"这两步，常常一上来就搜集数据、跑数，这是不对的。第一步先搞清楚为什么做、范畴是什么；第二步统一口径。

---

五、重点实操清单（可直接照做）

#	操作要点
1	导入数据勾选"第一行作为列名"，避免字段变 C1/C2/C3/C4
2	拿到表先LIMIT 5+ 看结构，确认主键是谁、字段类型是什么（TEXT 不能直接计算）
3	用COUNT(*)vsCOUNT(DISTINCT 字段)做字段总览，差异过大的字段重点怀疑
4	异常值处理三步走：跑明细看是什么 → 统计异常行数判断占比 → 记录并找数仓/业务确认，别自己默默删
5	文本型数值用CAST(x AS DOUBLE)转换后再计算/比较
6	区分 NULL 和空字符串：用LEN()验证，= ''能筛出的是空字符串
7	验证order_id是否重复：GROUP BY + HAVING COUNT() = COUNT(DISTINCT)交叉验证表设计意图
8	日期清洗：SUBSTRING切多余字符，转统一形态；字符串比较要加引号
9	指标套路：GMV=SUM(price)/DAU=COUNT(DISTINCT user_id)/订单量=COUNT(DISTINCT order_id)/ARPU=GMV/DAU
10	建汇总表用DROP TABLE IF EXISTS + CREATE TABLE AS，保证可重复运行
11	空分类用CASE WHEN归到"no_category"，再算 GMV 给运营看证据
12	设计指标体系：北极星（商业+用户价值交叉）→ 用户旅程拆环节 → 各环节定义度量 → AARRR 补增长视角
13	写分析报告：先业务理解、再数据理解（定口径），最后才谈数据和模型——别跳步

---

六、踩坑记录（真实工作高频坑）

坑	后果	解决方案
Excel 处理长数字丢精度（user_id）	转科学计数法，末位变 0，颗粒度丢失，结论直接错	user_id 类长字符串一律用 TEXT 存储
NULL 和空字符串混为一谈	清洗逻辑错误	用LEN()区分，口径先统一
不同数据库 SQL 方言差异	一套 SQL 跑不通（本课 MySQL 语法、演示 SQLite）	掌握大框架 +善用 AI 查细节再自验
口径不统一	开会各拿各的数字互相不认	先问清对方口径，会前声明"本次采用 XX 口径"
0 元价格 / 1970 时间戳直接删	可能误删抽奖单、优惠券单、迁移默认值	先确认再处理，记录异常
数据量大硬用 Excel 洗	卡顿 + 科学计数法误导	上 SQL / Python，别让工具限制结论
迷信"完美的北极星"	陷入无休止争论	跟商业目标对齐，接受它要在讨论中反复修改

补充坑点：ARPU与ARPPU的区别也在这里——ARPU = 收入 / DAU，ARPPU（客单价）= 收入 / 支付用户数，日常不总是等价（本案例无未支付订单故等价，但要注意）。

---

七、个人总结：从测试到数据分析的思维转变

7.1 四个关键认知升级

第一，数据清洗的前置动作是"探查"。以前我以为清洗就是去空值、去重、改格式。这节课让我看到清洗之前要先搞清楚：主键是谁、字段类型是什么、order_id是不是唯一、0 元价格占多少、1970 时间戳有多少。这一整套"摸底"，才决定了后面所有分析建立在什么地基上。用老师的话说：拿了一个错误的数据源，可能把业务导到错误的方向。

第二，指标设计要"先定义口径，再谈数字"。练习二、练习三在反复强化同一个道理——无论是指标体系的北极星，还是 CRISP-DM 里的数据理解，第一步都是把定义和边界敲清楚。以前我觉得这是废话，听完才明白：口径不清是真实职场里最耗时间的坑，大家各拿各的数字互怼，本质就是口径不一致。

第三，"先完成再完美"不是鸡汤，是工作节奏。老师大意是：先能 60 分交出去，跟着业务跑起来，再一边做一边打磨；不是等技能打磨完美了再上岗。对转行的测试工程师来说，这其实挺解压——先独立把"接需求→探查→清洗→算指标→出结论"跑通，比纠结某个函数写得漂不漂亮重要得多。

第四，善用 AI 工具解决环境差异。老师演示 SQLite 语法时直接说"这个我没用过，我去搜 AI 问了一下"，然后照搬回来验证。对初学者很实用的启发：不用背全所有方言的语法，掌握分析框架，语法细节交给 AI 辅助 + 自己验证。

---

如果这篇博客对你有帮助，欢迎点赞 / 收藏 / 关注。我们一起在数据分析的路上进阶！🚀