数据分析笔试题实战复盘：从指标口径到SQL与Python交叉验证

简介这份数据分析笔试题资源包面向准备数据分析岗位笔试的求职者与在校学生覆盖从统计基础到机器学习、大数据处理的完整知识链条帮助读者在有限时间内系统梳理考点、熟悉真实题型。包内共41个文件以png图表、xlsx与xls数据表、docx文档为主辅以pptx演示、gif与mp4动态演示、exe工具及htm说明压缩包约54.78MB涵盖前海数据岗笔试题、SQL查询练习、BI练习题、测试题E套及多期制造与批发数据图表等素材。已有818人学习下载内容包含数据清洗、统计分布、可视化图表选择、建模算法与数据挖掘等常见笔试方向并配有分析报告与公司数量变化演示便于读者对照练习、复盘解题思路并查漏补缺适合需要系统刷题与实战演练的数据分析备考者。1. 数据分析笔试题从“刷完就忘”到“见题拆题”的实战复盘很多人拿到一份数据分析笔试题第一反应是打开 Excel 或者 Python 把数字算出来结果面试时被追问“为什么用中位数不用均值”“这个漏斗口径为什么这样定”当场卡壳。这份资源不是又一套“题库答案”的堆砌而是一份把笔试当项目来拆的实战笔记——它把每道题背后的业务场景、指标口径、计算逻辑和常见陷阱都摊开讲清楚。适合正在准备数据岗面试的应届生、想转行做数据分析的职场人以及带新人的团队负责人。如果你已经能写 SQL 和 pandas但总在“业务理解”和“指标定义”环节丢分这份东西值得花时间过一遍。2. 拆解笔试题的底层逻辑指标口径与业务场景2.1 为什么同一道题会有三个不同答案数据分析笔试题最坑的地方在于题目只给一段描述和一张表但不同的人会算出不同的结果而且每个人都能自圆其说。比如“计算某电商平台的复购率”有人用“重复购买用户数/总购买用户数”有人用“重复购买订单数/总订单数”还有人按“自然月内购买两次及以上”来算。三个答案都没错但面试官想看的不是数字而是你选择口径的理由。常见做法是先别急着写公式把题目里的时间范围、用户维度、业务目标圈出来。时间范围决定用日粒度还是月粒度用户维度决定是按注册用户还是按活跃用户业务目标决定复购是看“用户留存”还是“购买频次”。我一般会先在草稿纸上写三行这道题的业务方是谁、他要这个指标做什么决策、数据表里哪些字段能支撑这个口径。这三行写清楚后面代码就是翻译工作。提示笔试时如果题目没给明确口径不要自己假设一个就往下算。在答案开头用一句话说明“以下按 XX 口径计算若需其他口径可调整”面试官反而会觉得你严谨。2.2 从需求描述到计算字段的映射方法拿到题目后把自然语言翻译成字段映射表这一步决定了后面代码会不会返工。举个例子题目说“统计每个城市中下单金额超过该城市平均下单金额的用户占比”。这句话里有三个层次城市分组、城市内平均下单金额、超过平均值的用户。映射到字段就是 city、order_amount、user_id。我习惯用一张临时表来拆题目短语对应字段计算逻辑易错点每个城市citygroup by city城市字段有空值需过滤平均下单金额order_amountavg(order_amount) over(partition by city)是用户均值还是订单均值超过平均order_amountorder_amount city_avg是否包含等于用户占比user_idcount(distinct user_id)/count(distinct user_id)分母是全部用户还是下单用户这张表填完SQL 基本就出来了。很多新手翻车是因为跳过这一步直接写代码写到一半发现“平均”算的是订单均值但题目要的是用户均值只能推倒重来。2.3 用 SQL 实现一个典型指标计算下面用“每个城市下单金额超过城市平均下单金额的用户占比”来演示完整写法。假设表名为 orders字段有 user_id、city、order_amount、order_date。-- 第一步计算每个城市的下单用户平均金额按用户聚合不是按订单 WITH user_city_avg AS ( SELECT city, user_id, SUM(order_amount) AS user_total_amount -- 每个用户在该城市的总下单金额 FROM orders WHERE city IS NOT NULL -- 过滤城市空值 AND order_date 2024-01-01 -- 按题目时间范围过滤 GROUP BY city, user_id ), city_avg AS ( SELECT city, AVG(user_total_amount) AS city_avg_amount -- 城市内用户平均金额 FROM user_city_avg GROUP BY city ), user_flag AS ( SELECT u.city, u.user_id, CASE WHEN u.user_total_amount c.city_avg_amount THEN 1 ELSE 0 END AS is_above_avg FROM user_city_avg u JOIN city_avg c ON u.city c.city ) SELECT city, COUNT(DISTINCT CASE WHEN is_above_avg 1 THEN user_id END) * 1.0 / COUNT(DISTINCT user_id) AS above_avg_user_ratio FROM user_flag GROUP BY city;这段代码的关键点有三个。第一user_city_avg 里先按 user_id 聚合因为题目说的是“用户占比”不是“订单占比”如果直接对订单求平均高客单价用户会被重复计算。第二city_avg 基于用户聚合后的金额再求平均这才是“城市平均下单金额”的正确口径。第三最后用 COUNT(DISTINCT CASE WHEN ...) 算占比分母是全部下单用户不是全部用户因为题目限定在“下单金额”语境下。参数调整方面如果题目要求“超过或等于平均值”把 改成 如果时间范围是滚动 30 天把固定日期换成 DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)如果城市字段有拼写不一致先做一次 UPPER(TRIM(city)) 清洗。3. Python 侧的数据清洗与可视化笔试里最容易丢分的环节3.1 用 pandas 复现 SQL 逻辑并做交叉验证笔试如果允许用 Python很多人会直接上 pandas但写出来的代码和 SQL 结果对不上。常见原因是 groupby 的默认行为不同SQL 的 GROUP BY 会忽略空值分组pandas 的 groupby 默认把 NaN 也当成一组。另外SQL 的 COUNT(DISTINCT) 在 pandas 里要用 nunique()而 count() 是计非空行数两者混用会导致分母偏大。import pandas as pd import numpy as np # 读取数据假设是 CSV 格式 df pd.read_csv(orders.csv, parse_dates[order_date]) # 过滤条件与 SQL 保持一致 df df[df[city].notna() (df[order_date] 2024-01-01)] # 第一步按城市和用户聚合得到每个用户的总金额 user_city df.groupby([city, user_id], as_indexFalse)[order_amount].sum() user_city.rename(columns{order_amount: user_total_amount}, inplaceTrue) # 第二步计算每个城市的用户平均金额 city_avg user_city.groupby(city, as_indexFalse)[user_total_amount].mean() city_avg.rename(columns{user_total_amount: city_avg_amount}, inplaceTrue) # 第三步标记超过平均的用户 user_city user_city.merge(city_avg, oncity, howleft) user_city[is_above_avg] (user_city[user_total_amount] user_city[city_avg_amount]).astype(int) # 第四步计算占比 result user_city.groupby(city).apply( lambda x: x[x[is_above_avg] 1][user_id].nunique() / x[user_id].nunique() ).reset_index(nameabove_avg_user_ratio) print(result)这段代码和 SQL 的逻辑一一对应但有两个 pandas 特有的坑。第一groupby 之后如果不用 as_indexFalse分组字段会变成索引后面 merge 时容易出错。第二apply 里的 lambda 用了 nunique()如果换成 count()分母会把 NaN 也算进去。我一般会在写完 Python 后用 SQL 跑一遍同样的逻辑两个结果对不上就查数据分布十有八九是空值处理不一致。3.2 可视化不是画图是验证数据分布笔试里让“画图分析”的题很多人直接 bar chart 或 line chart 一贴就完事。但面试官看的是你有没有用图来验证假设。比如题目给了一份用户消费数据让你分析“消费金额分布是否正常”。这时候应该先画箱线图看离群点再画直方图看偏度最后画 QQ 图看是否接近正态分布。三张图下来你对数据的理解比单纯算个均值深刻得多。import matplotlib.pyplot as plt import seaborn as sns fig, axes plt.subplots(1, 3, figsize(15, 4)) # 箱线图看离群点和四分位距 sns.boxplot(xdf[order_amount], axaxes[0]) axes[0].set_title(Order Amount Boxplot) # 直方图看分布形态和偏度 sns.histplot(df[order_amount], bins50, kdeTrue, axaxes[1]) axes[1].set_title(Order Amount Distribution) # QQ 图看是否接近正态分布 from scipy import stats stats.probplot(df[order_amount], distnorm, plotaxes[2]) axes[2].set_title(Q-Q Plot) plt.tight_layout() plt.show()箱线图如果发现大量点超出上须说明数据右偏严重这时候用均值描述“平均消费”就会失真应该改用中位数。直方图如果呈现双峰说明用户群体可能分为两类需要进一步拆分。QQ 图如果点偏离对角线说明不能用 t 检验之类的参数方法。这些结论写进答案里比单纯说“平均消费是 XX 元”有价值得多。注意笔试时如果数据量很大不要直接画全量散点图会卡死。先 sample(10000) 再画并在答案里注明“抽样展示”。4. 避坑与排查笔试现场最容易翻车的五个点4.1 现象SQL 跑出来结果为空但数据表明明有数据原因最常见的是 WHERE 条件里的日期格式不匹配。比如 order_date 是字符串 2024/01/01但条件写的是 2024-01-01数据库不会报错只会返回空集。另一个原因是 JOIN 的关联字段类型不一致一边是 INT 一边是 STRING隐式转换失败。解决先用 SELECT * FROM orders LIMIT 10 看一眼原始数据的日期格式和字段类型。日期用 DATE() 或 STR_TO_DATE() 统一转换关联字段用 CAST() 显式转换。如果还不行把 WHERE 条件逐个注释掉看是哪一条过滤掉了数据。4.2 现象Python 的 groupby 结果和 SQL 对不上原因pandas 的 groupby 默认 dropnaTrue会把分组字段为空的行直接丢掉而 SQL 的 GROUP BY 会把空值单独作为一组。另外pandas 的 mean() 默认跳过 NaNSQL 的 AVG() 也跳过 NULL但如果数据里空值是空字符串而不是 NaNpandas 会把它当成有效值参与计算。解决在 groupby 里加 dropnaFalse保持和 SQL 一致。读取数据时用 na_values 参数把空字符串转成 NaN。如果还是对不上把两个结果分别导出用 merge 做 diff看具体是哪几行不一致。4.3 现象指标算出来是整数但题目要求百分比原因SQL 里整数除法会截断小数部分。比如 3/5 在有些数据库里返回 0 而不是 0.6。Python 2 里也有同样的问题Python 3 虽然改了但如果分子分母都是整数pandas 的 div 有时也会返回整数。解决SQL 里把分子或分母乘 1.0或者用 CAST(x AS FLOAT)。Python 里用 astype(float) 或直接写 1.0 * a / b。这个坑很小但笔试时因为格式丢分很冤。4.4 现象题目要求“按周统计”但跨年周算错了原因不同数据库对“周”的定义不同。MySQL 的 WEEK() 默认以周日为一周开始而有些业务场景以周一为开始。更麻烦的是跨年周比如 2024-12-30 属于 2024 年第 53 周还是 2025 年第 1 周不同函数返回不同结果。解决先确认题目有没有指定周起始日。如果没有在答案里注明“按 ISO 标准周周一为起始计算”。MySQL 用 YEARWEEK(date, 1)PostgreSQL 用 DATE_TRUNC(week, date)pandas 用 dt.to_period(W)。跨年周建议直接用日期截断到周一避免周数编号的歧义。4.5 现象面试官追问“如果数据量翻十倍你的代码还能跑吗”原因笔试时写的 SQL 或 Python 往往只考虑正确性没考虑性能。比如在 WHERE 里对字段做函数运算会导致索引失效或者用 SELECT * 把不需要的列也拉出来。解决提前准备一句“如果数据量增大我会把过滤条件下推到子查询里减少参与 JOIN 的数据量对常用查询字段建索引Python 侧改用 chunk 分块读取或换用 polars”。这句话不需要你真的跑过大数据但能体现你有工程意识。5. 从笔试到面试用“指标拆解模板”把答案讲成故事笔试过了只是第一步面试官拿着你的答案追问“为什么这么算”才是真正的考验。我后来养成一个习惯每道笔试题做完用一张 A4 纸把“业务背景 → 指标定义 → 计算逻辑 → 数据验证 → 结论建议”五段写下来。这张纸不交上去但面试前过一遍被问到任何细节都能接住。具体来说业务背景用一句话说清“谁用这个指标做什么决策”指标定义要给出分子分母和时间窗口计算逻辑写清楚用了哪些表、哪些字段、怎么过滤数据验证说一句“我用中位数和均值对比过发现右偏所以选了中位数”结论建议不要只给数字要给一句“建议对高客单价用户做分层运营”。这套模板练熟之后笔试就不再是算数题而是 mini 版的业务分析项目。还有一个技巧如果题目允许在答案最后加一个“口径敏感性分析”。比如“如果复购率按订单口径算结果是 12%按用户口径算结果是 8%。两者差异主要来自高频低客单价用户建议按用户口径向业务方汇报”。这一句话就能把你和只写一个数字的候选人区分开。从那以后我每次做数据分析笔试题都强制自己先写口径假设再动笔算算完再用另一种工具交叉验证一遍。希望帮到你。本文还有配套的精品资源点击获取