前阵子帮几位朋友做面试复盘我发现一个反复出现的现象简历上写着“熟练使用 SQL”但面试官真给一张订单表、一张用户表就当场卡壳。大多数人跟 SQL 之间隔着的并不是语法而是“用 SQL 解决问题的思路”。这篇文章不是语法手册而是从我做数据分析项目和处理面试题的真实经验出发梳理 SQL 数据分析实战里最高频的考点去重和空值处理、聚合与窗口函数、10 道大厂高频面试题逐题拆解以及慢 SQL 优化的排查路径。最后用一个销售分析项目把整个流程串起来。无论你是准备数据分析师岗位面试还是已经入行想补齐实战能力都可以直接参考里面的写法和思路。1. 为什么大厂面试官都爱拿 SQL 做第一道过滤器1.1 数据分析日常里七成时间都在跟 SQL 打交道数据分析师的日常并不是很多人以为的建模、算法、写 Python。真正落到工作里流程基本是接收业务方的取数需求去数据库里把数据捞出来清洗加工成能分析的宽表然后才进入指标计算和结论产出。在这个链路里SQL 是使用频率最高的工具没有之一。面试官考 SQL本质上是在做反向验证看候选人能不能完成日常工作中最频繁的动作——把模糊的业务问题翻译成结构清晰的查询逻辑。我见过不少候选人背了一堆函数名但遇到“统计最近 30 天活跃用户中消费金额超过 500 元的人数”这种需求就开始懵。这属于典型的问题拆分能力缺失。真正的 SQL 面试考的不是你会不会用某个函数而是你接到需求之后能不能先拆出过滤条件、再拆出聚合维度、最后选对时间窗口。表结构也是天天变的同一个问题换个表结构解法就完全不同。所以 SQL 在面试里其实是最难突击的部分它考察的是底层思维不是模板记忆。1.2 面试官从你的 SQL 语句里能读出三个信号第一是需求拆解能力。同样一句“我想看华东区上个月的销售情况”有人会先确认时间口径、地域口径、金额口径有人上来就SELECT *高下立判。第二是数据敏感度。会不会主动处理重复订单会不会考虑空值对聚合结果的影响这些细节最能看出候选人有没有真实处理过脏数据。第三是性能意识。表连接有没有控制数据量窗口函数会不会造成大表全排序这类问题往往在第二轮或更深层的追问中出现。举一个我在面试现场真实见到的例子。面试官问“找出每个城市每天的订单量还要求订单明细里有重复记录。”一个候选人写SELECT city, order_date, COUNT(*) FROM orders GROUP BY city, order_date另一个候选人先做了明细去重再聚合。两者的 SQL 都能跑但后者明显对“重复记录会把订单量算虚高”有意识。面试官要考察的就是这种条件反射式的判断力。SQL 题目答得好坏很多时候不是技巧问题而是平常有没有被业务数据毒打过。2. 数据清洗三板斧去重、空值、重复记录怎么处理才不翻车2.1 去重不是只有 DISTINCT 一种姿势“去重”在取数需求里出现频率极高但很多人第一反应就是SELECT DISTINCT。DISTINCT 当然有用它的局限也很明显当你需要根据业务键去重、同时保留其他字段的某一条记录时DISTINCT 就无能为力了。比如订单表里同一个order_id有多条更新记录我想保留每个订单最新修改的那条DISTINCT 根本排不上用场。正确的通用姿势是用窗口函数ROW_NUMBER()配合PARTITION BY指定去重键再用ORDER BY决定保留哪一条。以“每个订单只保留最新更新记录”为例WITH t AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY updated_at DESC ) AS rk FROM order_info ) SELECT * FROM t WHERE rk 1;这样写的好处是逻辑清晰PARTITION BY划定一个订单为一个组ORDER BY updated_at DESC让最新记录排第一rk 1就是最新那条。实际工作中我经常用这个写法应对“同主键多状态”的业务表效率比 GROUP BY 取 MAX 再回表 join 高得多而且可读性好。2.2 空值处理聚合函数里最容易翻车的细节面试里考空值最常见的是这么几个点第一COUNT(字段)不会统计 NULLCOUNT(*)统计所有行。同样一张表一个COUNT(amount)一个COUNT(*)出来的数字可能差一大截。第二AVG计算时直接把 NULL 忽略如果你的数据里有大量空值平均数会被“无形抬高”。第三GROUP BY会把 NULL 单独分成一组这在某些统计场景下会制造一个额外的“未知维度”。处理空值我一般分两步走。先想清楚空值的业务含义是“没有发生”还是“数据缺失”。如果是前者业务上通常用 0 填充如果是后者直接剔除或者标记。SQL 层面常用的有COALESCE(amount, 0)和IFNULL(amount, 0)两者的区别只在数据库方言逻辑一样。还有一个细节COUNT(DISTINCT CASE WHEN amount IS NOT NULL THEN user_id END)这类写法能实现“排除空值后的去重计数”在留存类指标里经常用到。提示清洗数据时SQL 和 Python 的边界要划清楚。亿级明细表先跑 SQL 做过滤、聚合、去重把数据量压下来再导进 Python 做复杂特征工程。SQL 能下推到数据库引擎执行别把几亿行全捞到本地再来处理那是自找麻烦。2.3 一组“脏数据”清洗的完整演示假设有一个用户订单流水表orders(order_id, user_id, amount, order_date)里面既有重复订单也有空金额。现在要统计每个用户的订单数和有效总金额WITH clean_orders AS ( SELECT order_id, user_id, COALESCE(amount, 0) AS amount, -- 空值按0处理 ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY order_date DESC ) AS rk FROM orders ) SELECT user_id, COUNT(DISTINCT order_id) AS order_cnt, SUM(amount) AS total_amount FROM clean_orders WHERE rk 1 GROUP BY user_id;这道题的巧妙之处在于同时处理了重复键和空值。如果不先ROW_NUMBER去重SUM(amount)会把重复订单的金额叠加进去如果不COALESCE部分订单金额缺失会导致总额偏低。面试官看的就是你有没有这两个意识。3. 聚合统计与窗口函数从分组汇总到对比排名的常见套路3.1 GROUP BY 与 HAVING 的边界感GROUP BY 配合聚合函数是基础中的基础但很多人会用错 WHERE 和 HAVING。我的记忆口诀很简单WHERE 过滤的是原始行HAVING 过滤的是聚合后的组。比如“统计订单量超过 10 单的用户”WHERE COUNT(*) 10是错的因为 WHERE 在分组之前执行这时候还没有 COUNT。正确写法是SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id HAVING COUNT(*) 10;这道题看着简单却是面试里出现频率很高的一道基础筛选因为很多人一紧张就把 HAVING 写成 WHERE。背后的逻辑顺序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY。把这个执行顺序背熟这类陷阱基本不会再踩。3.2 三个排序函数必须分清ROW_NUMBER、RANK、DENSE_RANK窗口函数在数据分析面试里基本是必考的其中最容易混淆的就是三个排序函数。我画过太多遍这张表了函数并列处理方式举例分数 90、90、80ROW_NUMBER()不管是否并列强行给连续编号1, 2, 3RANK()并列占用下一个位置1, 1, 3DENSE_RANK()并列不占用下一个位置1, 1, 2业务上怎么选如果要“取前 3 名且并列都算”用 RANK如果只要“物理意义上的前 3 条记录”用 ROW_NUMBER如果排行榜需要“1、1、2”这种紧凑名次用 DENSE_RANK。面试官特别喜欢追问“为什么这里用 DENSE_RANK 不用 RANK”这个问题的本质就是在确认你有没有理解并列场景。3.3 累计、环比与移动平均SUM OVER 和 LAG 的组合拳窗口函数另一个高频场景是“从分组汇总到逐行对比”。典型需求统计每个月的 GMV同时输出累计 GMV 和环比增速。这一道 SQL 就能覆盖三个考点SELECT month_str, gmv, LAG(gmv, 1) OVER (ORDER BY month_str) AS prev_gmv, ROUND( (gmv - LAG(gmv, 1) OVER (ORDER BY month_str)) / NULLIF(LAG(gmv, 1) OVER (ORDER BY month_str), 0) * 100, 2 ) AS mom_growth_rate, SUM(gmv) OVER (ORDER BY month_str) AS cum_gmv FROM sales ORDER BY month_str;这里面有两个容易被忽略的坑。第一LAG取上一行当上一行不存在时返回 NULL所以第一个月的prev_gmv一定是空的。第二环比增速算出来可能是无穷大因为分母为 0我用NULLIF(prev_gmv, 0)把 0 转成 NULL避免报错和除零脏数据。至于SUM(gmv) OVER (ORDER BY month_str)就是标准的累计求和执行原理是在每个行位置计算“当前行以及之前所有行的和”。4. 十道大厂高频 SQL 面试题逐题拆解这一章是全文的主菜。我挑的 10 道题基本覆盖了大厂数据分析岗面试的主要题型从连续登录到留存率、从分组 TopN 到中位数计算。每道题我会给出表结构假设、核心解法和考点分析。4.1 第一题连续登录 7 天的用户怎么筛表结构login_log(user_id, login_date)每个用户每天最多一条登录记录。要求筛选出连续登录 7 天及以上的用户。这类题的经典解法叫“日期减行号法”。先按用户分组并按日期排序生成行号然后用登录日期减去行号对应的天数。如果用户的登录日期是连续的减法之后会得到同一个基准日这个基准日就是连续段的“分组键”SELECT user_id, MIN(login_date) AS start_day, COUNT(*) AS cnt FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ) DAY) AS grp FROM login_log ) t GROUP BY user_id, grp HAVING COUNT(*) 7;思路拆解用户 A 在 1 号、2 号、3 号登录行号分别为 1、2、3减完后都是 0 号群组 grp 相同如果中间断了一天后面日期的减法结果就会变化形成新的分组。这个解法是连续性问题的最优套路比自连接不知道高到哪里去。注意如果登录表存在一天多条记录先SELECT DISTINCT user_id, login_date再套这个逻辑否则行号会错位。4.2 第二题每个部门工资排名前三的员工表结构employee(emp_id, emp_name, dept, salary)。求每个部门薪资前三的员工。这题是分组 TopN 的典型直接用 DENSE_RANK 还是 RANK 要看业务口径。如果“前三”要求并列都算用 RANK如果只是取前三条记录用 ROW_NUMBER。面试里我建议默认讲清楚两种口径的差异再让面试官选。以下是并列都算的口径WITH t AS ( SELECT *, DENSE_RANK() OVER ( PARTITION BY dept ORDER BY salary DESC ) AS rk FROM employee ) SELECT * FROM t WHERE rk 3;考这个题的时候面试官真正想知道的是你有没有理解“部门”这个分组维度落在了PARTITION BY上。有些人会写成GROUP BY dept再取 MAX那种写法只能拿到每个部门最高薪的人拿不到第三、第三名。窗口函数才是正解。4.3 第三题GMV 累计求和与环比增长表结构sales(month_str, gmv)month_str 为月份字符串。要求输出每月 GMV、累计 GMV 和环比增速。解法在上一个章节已经写过核心就是SUM(gmv) OVER (ORDER BY month_str)和LAG(gmv, 1) OVER (ORDER BY month_str)的组合。这里我再补充一个实战细节如果数据库是 Hive 或者 Spark SQL注意ORDER BY month_str排序时字符串月份可能不按时间排序2024-02和2024-10会被字典序排错所以月份字段最好存成标准yyyy-MM格式或者直接用DATE_TRUNC切出时间类型。这道题在面试里经常被用作“引子”后面会追问如果表里有多个店铺要分别输出每个店铺的累计 GMV 和环比该怎么办答案是把店铺加到 PARTITION BY 里PARTITION BY store_id ORDER BY month_str。窗口函数的威力就在于“分组后还能逐行比较”这是普通聚合做不到的。4.4 第四题次日留存率怎么计算表结构user_visit(uid, visit_date)。要求计算每天新用户次日留存率。先定义清楚新用户 当天第一次出现在user_visit里的用户次日留存 这批用户在第二天仍然有访问记录的比例。先把每个 uid 的首次访问日期取出来然后再去关联第二天的访问记录WITH first_day AS ( SELECT uid, MIN(visit_date) AS first_date FROM user_visit GROUP BY uid ) SELECT f.first_date, COUNT(DISTINCT f.uid) AS new_users, COUNT(DISTINCT v2.uid) AS retained_users, ROUND(COUNT(DISTINCT v2.uid) / COUNT(DISTINCT f.uid), 4) AS retention_rate FROM first_day f LEFT JOIN user_visit v2 ON f.uid v2.uid AND v2.visit_date DATE_ADD(f.first_date, INTERVAL 1 DAY) GROUP BY f.first_date ORDER BY f.first_date;这里必须用LEFT JOIN而不是INNER JOIN否则没留存下来的用户会被直接过滤掉分母就错了。另一个常见坑是关联条件要明确“第二天”直接在 ON 子句里写DATEDIFF(v2.visit_date, f.first_date) 1也可以但在大表上 DATE_ADD 写法更利于走索引。这类题目是留存分析的基本功指标口径搞不清楚的话后面做用户增长分析会全是坑。4.5 第五题重复订单记录怎么清洗表结构order_info(order_id, product_id, amount, updated_at)。同一个 order_id 存在多条变更记录要求清洗后每个订单只保留更新时间和金额最新的那条记录。这题我在第二章已经演示过 ROW_NUMBER 解法这里补充为什么不用 DISTINCT 和 GROUP BY。DISTINCT 只能去除完全相同的行同一个订单不同版本里 product_id 相同但 amount 不同DISTINCT 处理不了。GROUP BY order_id 配合 MAX(updated_at) 能找到每个订单的最新时间但在标准 SQL 里没法直接返回该时间对应的那一行 amount还得自连接回原表复杂度和维护成本都高。ROW_NUMBER 是面试官最希望看到的解法WITH ranked_orders AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY updated_at DESC ) AS rk FROM order_info ) SELECT * FROM ranked_orders WHERE rk 1;4.6 第六题每个品类销量最高的商品表结构product_info(category, product_name, sales_qty)。求每个品类中销量最高的商品。这题是 4.2 的简化版考的是 ROW_NUMBER 在分组排序中的应用WITH ranked_products AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY sales_qty DESC ) AS rk FROM product_info ) SELECT * FROM ranked_products WHERE rk 1;如果题目要求“并列最高都输出”把 ROW_NUMBER 换成 RANK 就行。这个细节面试官几乎必问。另一个变体是求“每个品类销量前三名的累计占比”此时需要把WHERE rk 3的结果继续做窗口求和或自关联本质是一样的套路。4.7 第七题找出消费金额高于平均值的订单表结构orders(order_id, amount)。找出金额高于整体平均值的所有订单。这是一道基础子查询题但隐藏着一个空值考点SELECT * FROM orders WHERE amount (SELECT AVG(amount) FROM orders);核心就是子查询的 AVG 结果作为标量比较。面试官追随时常出现在这里AVG(amount)会自动忽略 NULL如果表里大量 amount 为空这个平均值会比真实业务水平偏小导致误判。更严谨的写法是先把空值处理掉SELECT * FROM orders WHERE COALESCE(amount, 0) (SELECT AVG(COALESCE(amount, 0)) FROM orders);另外这个子查询是独立的和外部查询没有关联所以执行一次就能拿到常量值。如果把它改成相关子查询逐行重新计算平均值性能会非常差。这个对比也是面试官爱问的性能考点。4.8 第八题找出出现重复的手机号表结构users(uid, mobile)。找出所有在表中出现超过一次的手机号。典型的“分组 HAVING 过滤”SELECT mobile, COUNT(*) AS cnt FROM users GROUP BY mobile HAVING COUNT(*) 2;这题和 4.1 相比更基础但很能检验候选人有没有理解 HAVING 语义。网上有些错误答案会用WHERE COUNT(*) 2执行直接报错。同样如果要求“把 uid 也带出来看是哪些用户”按时间先后取最早注册的那个就得结合 ROW_NUMBERWITH ranked_users AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY mobile ORDER BY uid ) AS rk FROM users ) SELECT * FROM ranked_users WHERE mobile IN (SELECT mobile FROM users GROUP BY mobile HAVING COUNT(*) 2) AND rk 1;第一段筛重复手机号第二段取每个手机号对应的最早用户两层逻辑层层递进这套路在面试里非常受用。4.9 第九题用 SQL 求精确中位数表结构test_score(student_id, score)。求所有学生成绩的中位数。中位数在数据分析里是比平均值更稳的集中趋势指标但 SQL 没有内置 MEDIAN。常规思路是先排序编号再根据总行数的奇偶定位中间位置WITH ranked_scores AS ( SELECT score, ROW_NUMBER() OVER (ORDER BY score) AS rk, COUNT(*) OVER () AS total_cnt FROM test_score ) SELECT AVG(score) AS median FROM ranked_scores WHERE rk IN (FLOOR((total_cnt 1) / 2), CEIL((total_cnt 1) / 2));解释一下这个公式总行数是奇数时FLOOR((N1)/2)和CEIL((N1)/2)指向同一个位置总行数为偶数时两个位置正好是中间的两个数AVG(score)就把它们平均了。这个通用写法在 MySQL 8.0、Hive、PostgreSQL 里都能跑通。顺带一提如果面试官问“怎么算众数”思路就是COUNT(*) GROUP BY score ORDER BY cnt DESC LIMIT 1两个概念放在一起记更不容易混淆。4.10 第十题用户首单后 30 天内累计下单次数表结构orders(order_id, user_id, order_date)。要求计算每个用户从首单日期开始往后 30 天内含首单当天累计下单次数。这道题综合了自关联、日期计算和聚合是很好的压轴题WITH first_order AS ( SELECT user_id, MIN(order_date) AS first_date FROM orders GROUP BY user_id ) SELECT f.user_id, f.first_date, COUNT(DISTINCT o.order_id) AS orders_in_30d FROM first_order f LEFT JOIN orders o ON f.user_id o.user_id AND o.order_date BETWEEN f.first_date AND DATE_ADD(f.first_date, INTERVAL 30 DAY) GROUP BY f.user_id, f.first_date;这里用 LEFT JOIN 是为了让没有后续订单的用户也能输出count 为 0。关联条件把日期窗口放在了 ON 子句里防止 30 天窗口外的数据进入统计这是很多人容易写错的地方。整个 SQL 其实是在模拟一个业务概念新客户的“首月活跃度”后面接续分析复购率、客单价的时候就非常有价值。5. 慢 SQL 优化从定位问题到索引设计5.1 接到慢查询先跑 EXPLAIN很多数据分析师取数时遇到慢 SQL第一反应是“加索引”。这想法本身没错但顺序反了。正确的处理方式是先看执行计划再决定改哪。以 MySQL 为例在查询前面加 EXPLAINEXPLAIN SELECT order_id, user_id, amount FROM orders WHERE order_date 2024-01-01 AND user_id 12345;重点关注几个字段type列从好到差大概是const eq_ref ref range index ALL如果看到 ALL 就是全表扫描基本可以断定这条查询要走索引优化rows列是预估扫描行数数字越大越危险extra里出现Using temporary或Using filesort说明查询里可能有排序或分组操作在大数据量下容易拖慢速度。5.2 索引列上套函数是性能隐形杀手慢 SQL 优化里最常见的反模式之一是在索引字段上套函数。比如WHERE DATE(order_date) 2024-01-01这个写法对索引极不友好因为数据库无法直接使用 order_date 的索引树必须先全表扫描再逐行套 DATE 函数。更好的写法是写成范围条件WHERE order_date 2024-01-01 00:00:00 AND order_date 2024-01-02 00:00:00;另一个常见问题是 OR 条件。WHERE user_id 123 OR order_date 2024-01-01这类组合经常导致索引失效改成两个查询用 UNION ALL 合并或者重构条件为等值索引优先效果通常会好很多。5.3 组合索引的最左前缀原则组合索引是数据分析取数时最值得掌握的优化手段。假设业务里常见查询是按(store_id, order_date)过滤那么建INDEX idx_store_date(store_id, order_date)最合适。使用时要遵守最左前缀原则查询条件必须包含组合索引的第一列索引才会被用到。也就是说WHERE store_id 1 AND order_date 2024-01-01会走索引但只写WHERE order_date 2024-01-01这个索引就废了。注意索引不是越多越好。每个索引都要占据存储空间写入数据时要额外维护索引树。分析岗位日常面对的是宽表但也不要无脑给所有字段建索引。我一般的原则是高频过滤字段建索引低基数字段比如性别、城市几乎不建单列索引join 字段要确保两边类型一致否则隐式类型转换会让索引失效。6. 一个销售分析项目的完整 SQL 落地从取数到结论前面几章偏基础和面试最后回到一个实际业务场景帮大家把 SQL 能力串成一条线。假设公司有一个销售明细表order_detail(order_id, order_date, product_id, sales_amount, city)和商品维表product_dim(product_id, category, product_name)。业务方的需求是分析华东区“上月各品类销售额排行”并和上上月做环比找出增长最快的品类。第一步先把两张大表关联起来筛选出华东区上月数据WITH base AS ( SELECT d.order_id, d.order_date, d.sales_amount, p.category, d.city FROM order_detail d JOIN product_dim p ON d.product_id p.product_id WHERE d.city IN (上海, 南京, 杭州, 苏州, 无锡) AND d.order_date 2024-02-01 AND d.order_date 2024-03-01 )第二步按品类聚合统计本月销量和销售额SELECT base.category, COUNT(DISTINCT base.order_id) AS order_cnt, SUM(base.sales_amount) AS total_amount, ROUND(AVG(base.sales_amount), 2) AS avg_order_amount FROM base GROUP BY base.category ORDER BY total_amount DESC;第三步如果要跟上月对比把统计维度扩展到两个月再用 LAG 求环比WITH monthly AS ( SELECT p.category, DATE_FORMAT(d.order_date, %Y-%m) AS month_str, SUM(d.sales_amount) AS total_amount FROM order_detail d JOIN product_dim p ON d.product_id p.product_id WHERE d.city IN (上海, 南京, 杭州, 苏州, 无锡) AND d.order_date 2024-01-01 AND d.order_date 2024-03-01 GROUP BY p.category, DATE_FORMAT(d.order_date, %Y-%m) ) SELECT category, month_str, total_amount, LAG(total_amount, 1) OVER ( PARTITION BY category ORDER BY month_str ) AS prev_amount, ROUND( (total_amount - LAG(total_amount, 1) OVER ( PARTITION BY category ORDER BY month_str )) / NULLIF(LAG(total_amount, 1) OVER ( PARTITION BY category ORDER BY month_str ), 0) * 100, 2 ) AS growth_rate FROM monthly ORDER BY category, month_str;这段 SQL 把所有重点都覆盖了JOIN 关联维表、日期范围过滤、按维度聚合、窗口函数求环比。跑出结果后我把增长率为正的品类按增速降序排再结合单价、销量和平均订单金额综合判断最后落到业务结论某品类销量拉了 30%但平均金额在下降说明增长主要来自低价商品需要留意利润结构。这类项目复盘多了我的体会是SQL 能力的核心不只是背函数而是拿到任何一张表都能快速形成“先过滤、再聚合、后分析”的路径。面试前花时间刷题是有用的但刷完之后一定要落到真实业务上去验证一次。等你真正用 SQL 解决过几个业务问题再回头面对面试官那些窗口函数和去重技巧就会变得非常自然。
SQL数据分析实战：去重、空值与窗口函数高频考点全解析
前阵子帮几位朋友做面试复盘我发现一个反复出现的现象简历上写着“熟练使用 SQL”但面试官真给一张订单表、一张用户表就当场卡壳。大多数人跟 SQL 之间隔着的并不是语法而是“用 SQL 解决问题的思路”。这篇文章不是语法手册而是从我做数据分析项目和处理面试题的真实经验出发梳理 SQL 数据分析实战里最高频的考点去重和空值处理、聚合与窗口函数、10 道大厂高频面试题逐题拆解以及慢 SQL 优化的排查路径。最后用一个销售分析项目把整个流程串起来。无论你是准备数据分析师岗位面试还是已经入行想补齐实战能力都可以直接参考里面的写法和思路。1. 为什么大厂面试官都爱拿 SQL 做第一道过滤器1.1 数据分析日常里七成时间都在跟 SQL 打交道数据分析师的日常并不是很多人以为的建模、算法、写 Python。真正落到工作里流程基本是接收业务方的取数需求去数据库里把数据捞出来清洗加工成能分析的宽表然后才进入指标计算和结论产出。在这个链路里SQL 是使用频率最高的工具没有之一。面试官考 SQL本质上是在做反向验证看候选人能不能完成日常工作中最频繁的动作——把模糊的业务问题翻译成结构清晰的查询逻辑。我见过不少候选人背了一堆函数名但遇到“统计最近 30 天活跃用户中消费金额超过 500 元的人数”这种需求就开始懵。这属于典型的问题拆分能力缺失。真正的 SQL 面试考的不是你会不会用某个函数而是你接到需求之后能不能先拆出过滤条件、再拆出聚合维度、最后选对时间窗口。表结构也是天天变的同一个问题换个表结构解法就完全不同。所以 SQL 在面试里其实是最难突击的部分它考察的是底层思维不是模板记忆。1.2 面试官从你的 SQL 语句里能读出三个信号第一是需求拆解能力。同样一句“我想看华东区上个月的销售情况”有人会先确认时间口径、地域口径、金额口径有人上来就SELECT *高下立判。第二是数据敏感度。会不会主动处理重复订单会不会考虑空值对聚合结果的影响这些细节最能看出候选人有没有真实处理过脏数据。第三是性能意识。表连接有没有控制数据量窗口函数会不会造成大表全排序这类问题往往在第二轮或更深层的追问中出现。举一个我在面试现场真实见到的例子。面试官问“找出每个城市每天的订单量还要求订单明细里有重复记录。”一个候选人写SELECT city, order_date, COUNT(*) FROM orders GROUP BY city, order_date另一个候选人先做了明细去重再聚合。两者的 SQL 都能跑但后者明显对“重复记录会把订单量算虚高”有意识。面试官要考察的就是这种条件反射式的判断力。SQL 题目答得好坏很多时候不是技巧问题而是平常有没有被业务数据毒打过。2. 数据清洗三板斧去重、空值、重复记录怎么处理才不翻车2.1 去重不是只有 DISTINCT 一种姿势“去重”在取数需求里出现频率极高但很多人第一反应就是SELECT DISTINCT。DISTINCT 当然有用它的局限也很明显当你需要根据业务键去重、同时保留其他字段的某一条记录时DISTINCT 就无能为力了。比如订单表里同一个order_id有多条更新记录我想保留每个订单最新修改的那条DISTINCT 根本排不上用场。正确的通用姿势是用窗口函数ROW_NUMBER()配合PARTITION BY指定去重键再用ORDER BY决定保留哪一条。以“每个订单只保留最新更新记录”为例WITH t AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY updated_at DESC ) AS rk FROM order_info ) SELECT * FROM t WHERE rk 1;这样写的好处是逻辑清晰PARTITION BY划定一个订单为一个组ORDER BY updated_at DESC让最新记录排第一rk 1就是最新那条。实际工作中我经常用这个写法应对“同主键多状态”的业务表效率比 GROUP BY 取 MAX 再回表 join 高得多而且可读性好。2.2 空值处理聚合函数里最容易翻车的细节面试里考空值最常见的是这么几个点第一COUNT(字段)不会统计 NULLCOUNT(*)统计所有行。同样一张表一个COUNT(amount)一个COUNT(*)出来的数字可能差一大截。第二AVG计算时直接把 NULL 忽略如果你的数据里有大量空值平均数会被“无形抬高”。第三GROUP BY会把 NULL 单独分成一组这在某些统计场景下会制造一个额外的“未知维度”。处理空值我一般分两步走。先想清楚空值的业务含义是“没有发生”还是“数据缺失”。如果是前者业务上通常用 0 填充如果是后者直接剔除或者标记。SQL 层面常用的有COALESCE(amount, 0)和IFNULL(amount, 0)两者的区别只在数据库方言逻辑一样。还有一个细节COUNT(DISTINCT CASE WHEN amount IS NOT NULL THEN user_id END)这类写法能实现“排除空值后的去重计数”在留存类指标里经常用到。提示清洗数据时SQL 和 Python 的边界要划清楚。亿级明细表先跑 SQL 做过滤、聚合、去重把数据量压下来再导进 Python 做复杂特征工程。SQL 能下推到数据库引擎执行别把几亿行全捞到本地再来处理那是自找麻烦。2.3 一组“脏数据”清洗的完整演示假设有一个用户订单流水表orders(order_id, user_id, amount, order_date)里面既有重复订单也有空金额。现在要统计每个用户的订单数和有效总金额WITH clean_orders AS ( SELECT order_id, user_id, COALESCE(amount, 0) AS amount, -- 空值按0处理 ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY order_date DESC ) AS rk FROM orders ) SELECT user_id, COUNT(DISTINCT order_id) AS order_cnt, SUM(amount) AS total_amount FROM clean_orders WHERE rk 1 GROUP BY user_id;这道题的巧妙之处在于同时处理了重复键和空值。如果不先ROW_NUMBER去重SUM(amount)会把重复订单的金额叠加进去如果不COALESCE部分订单金额缺失会导致总额偏低。面试官看的就是你有没有这两个意识。3. 聚合统计与窗口函数从分组汇总到对比排名的常见套路3.1 GROUP BY 与 HAVING 的边界感GROUP BY 配合聚合函数是基础中的基础但很多人会用错 WHERE 和 HAVING。我的记忆口诀很简单WHERE 过滤的是原始行HAVING 过滤的是聚合后的组。比如“统计订单量超过 10 单的用户”WHERE COUNT(*) 10是错的因为 WHERE 在分组之前执行这时候还没有 COUNT。正确写法是SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id HAVING COUNT(*) 10;这道题看着简单却是面试里出现频率很高的一道基础筛选因为很多人一紧张就把 HAVING 写成 WHERE。背后的逻辑顺序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY。把这个执行顺序背熟这类陷阱基本不会再踩。3.2 三个排序函数必须分清ROW_NUMBER、RANK、DENSE_RANK窗口函数在数据分析面试里基本是必考的其中最容易混淆的就是三个排序函数。我画过太多遍这张表了函数并列处理方式举例分数 90、90、80ROW_NUMBER()不管是否并列强行给连续编号1, 2, 3RANK()并列占用下一个位置1, 1, 3DENSE_RANK()并列不占用下一个位置1, 1, 2业务上怎么选如果要“取前 3 名且并列都算”用 RANK如果只要“物理意义上的前 3 条记录”用 ROW_NUMBER如果排行榜需要“1、1、2”这种紧凑名次用 DENSE_RANK。面试官特别喜欢追问“为什么这里用 DENSE_RANK 不用 RANK”这个问题的本质就是在确认你有没有理解并列场景。3.3 累计、环比与移动平均SUM OVER 和 LAG 的组合拳窗口函数另一个高频场景是“从分组汇总到逐行对比”。典型需求统计每个月的 GMV同时输出累计 GMV 和环比增速。这一道 SQL 就能覆盖三个考点SELECT month_str, gmv, LAG(gmv, 1) OVER (ORDER BY month_str) AS prev_gmv, ROUND( (gmv - LAG(gmv, 1) OVER (ORDER BY month_str)) / NULLIF(LAG(gmv, 1) OVER (ORDER BY month_str), 0) * 100, 2 ) AS mom_growth_rate, SUM(gmv) OVER (ORDER BY month_str) AS cum_gmv FROM sales ORDER BY month_str;这里面有两个容易被忽略的坑。第一LAG取上一行当上一行不存在时返回 NULL所以第一个月的prev_gmv一定是空的。第二环比增速算出来可能是无穷大因为分母为 0我用NULLIF(prev_gmv, 0)把 0 转成 NULL避免报错和除零脏数据。至于SUM(gmv) OVER (ORDER BY month_str)就是标准的累计求和执行原理是在每个行位置计算“当前行以及之前所有行的和”。4. 十道大厂高频 SQL 面试题逐题拆解这一章是全文的主菜。我挑的 10 道题基本覆盖了大厂数据分析岗面试的主要题型从连续登录到留存率、从分组 TopN 到中位数计算。每道题我会给出表结构假设、核心解法和考点分析。4.1 第一题连续登录 7 天的用户怎么筛表结构login_log(user_id, login_date)每个用户每天最多一条登录记录。要求筛选出连续登录 7 天及以上的用户。这类题的经典解法叫“日期减行号法”。先按用户分组并按日期排序生成行号然后用登录日期减去行号对应的天数。如果用户的登录日期是连续的减法之后会得到同一个基准日这个基准日就是连续段的“分组键”SELECT user_id, MIN(login_date) AS start_day, COUNT(*) AS cnt FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY login_date ) DAY) AS grp FROM login_log ) t GROUP BY user_id, grp HAVING COUNT(*) 7;思路拆解用户 A 在 1 号、2 号、3 号登录行号分别为 1、2、3减完后都是 0 号群组 grp 相同如果中间断了一天后面日期的减法结果就会变化形成新的分组。这个解法是连续性问题的最优套路比自连接不知道高到哪里去。注意如果登录表存在一天多条记录先SELECT DISTINCT user_id, login_date再套这个逻辑否则行号会错位。4.2 第二题每个部门工资排名前三的员工表结构employee(emp_id, emp_name, dept, salary)。求每个部门薪资前三的员工。这题是分组 TopN 的典型直接用 DENSE_RANK 还是 RANK 要看业务口径。如果“前三”要求并列都算用 RANK如果只是取前三条记录用 ROW_NUMBER。面试里我建议默认讲清楚两种口径的差异再让面试官选。以下是并列都算的口径WITH t AS ( SELECT *, DENSE_RANK() OVER ( PARTITION BY dept ORDER BY salary DESC ) AS rk FROM employee ) SELECT * FROM t WHERE rk 3;考这个题的时候面试官真正想知道的是你有没有理解“部门”这个分组维度落在了PARTITION BY上。有些人会写成GROUP BY dept再取 MAX那种写法只能拿到每个部门最高薪的人拿不到第三、第三名。窗口函数才是正解。4.3 第三题GMV 累计求和与环比增长表结构sales(month_str, gmv)month_str 为月份字符串。要求输出每月 GMV、累计 GMV 和环比增速。解法在上一个章节已经写过核心就是SUM(gmv) OVER (ORDER BY month_str)和LAG(gmv, 1) OVER (ORDER BY month_str)的组合。这里我再补充一个实战细节如果数据库是 Hive 或者 Spark SQL注意ORDER BY month_str排序时字符串月份可能不按时间排序2024-02和2024-10会被字典序排错所以月份字段最好存成标准yyyy-MM格式或者直接用DATE_TRUNC切出时间类型。这道题在面试里经常被用作“引子”后面会追问如果表里有多个店铺要分别输出每个店铺的累计 GMV 和环比该怎么办答案是把店铺加到 PARTITION BY 里PARTITION BY store_id ORDER BY month_str。窗口函数的威力就在于“分组后还能逐行比较”这是普通聚合做不到的。4.4 第四题次日留存率怎么计算表结构user_visit(uid, visit_date)。要求计算每天新用户次日留存率。先定义清楚新用户 当天第一次出现在user_visit里的用户次日留存 这批用户在第二天仍然有访问记录的比例。先把每个 uid 的首次访问日期取出来然后再去关联第二天的访问记录WITH first_day AS ( SELECT uid, MIN(visit_date) AS first_date FROM user_visit GROUP BY uid ) SELECT f.first_date, COUNT(DISTINCT f.uid) AS new_users, COUNT(DISTINCT v2.uid) AS retained_users, ROUND(COUNT(DISTINCT v2.uid) / COUNT(DISTINCT f.uid), 4) AS retention_rate FROM first_day f LEFT JOIN user_visit v2 ON f.uid v2.uid AND v2.visit_date DATE_ADD(f.first_date, INTERVAL 1 DAY) GROUP BY f.first_date ORDER BY f.first_date;这里必须用LEFT JOIN而不是INNER JOIN否则没留存下来的用户会被直接过滤掉分母就错了。另一个常见坑是关联条件要明确“第二天”直接在 ON 子句里写DATEDIFF(v2.visit_date, f.first_date) 1也可以但在大表上 DATE_ADD 写法更利于走索引。这类题目是留存分析的基本功指标口径搞不清楚的话后面做用户增长分析会全是坑。4.5 第五题重复订单记录怎么清洗表结构order_info(order_id, product_id, amount, updated_at)。同一个 order_id 存在多条变更记录要求清洗后每个订单只保留更新时间和金额最新的那条记录。这题我在第二章已经演示过 ROW_NUMBER 解法这里补充为什么不用 DISTINCT 和 GROUP BY。DISTINCT 只能去除完全相同的行同一个订单不同版本里 product_id 相同但 amount 不同DISTINCT 处理不了。GROUP BY order_id 配合 MAX(updated_at) 能找到每个订单的最新时间但在标准 SQL 里没法直接返回该时间对应的那一行 amount还得自连接回原表复杂度和维护成本都高。ROW_NUMBER 是面试官最希望看到的解法WITH ranked_orders AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY updated_at DESC ) AS rk FROM order_info ) SELECT * FROM ranked_orders WHERE rk 1;4.6 第六题每个品类销量最高的商品表结构product_info(category, product_name, sales_qty)。求每个品类中销量最高的商品。这题是 4.2 的简化版考的是 ROW_NUMBER 在分组排序中的应用WITH ranked_products AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY sales_qty DESC ) AS rk FROM product_info ) SELECT * FROM ranked_products WHERE rk 1;如果题目要求“并列最高都输出”把 ROW_NUMBER 换成 RANK 就行。这个细节面试官几乎必问。另一个变体是求“每个品类销量前三名的累计占比”此时需要把WHERE rk 3的结果继续做窗口求和或自关联本质是一样的套路。4.7 第七题找出消费金额高于平均值的订单表结构orders(order_id, amount)。找出金额高于整体平均值的所有订单。这是一道基础子查询题但隐藏着一个空值考点SELECT * FROM orders WHERE amount (SELECT AVG(amount) FROM orders);核心就是子查询的 AVG 结果作为标量比较。面试官追随时常出现在这里AVG(amount)会自动忽略 NULL如果表里大量 amount 为空这个平均值会比真实业务水平偏小导致误判。更严谨的写法是先把空值处理掉SELECT * FROM orders WHERE COALESCE(amount, 0) (SELECT AVG(COALESCE(amount, 0)) FROM orders);另外这个子查询是独立的和外部查询没有关联所以执行一次就能拿到常量值。如果把它改成相关子查询逐行重新计算平均值性能会非常差。这个对比也是面试官爱问的性能考点。4.8 第八题找出出现重复的手机号表结构users(uid, mobile)。找出所有在表中出现超过一次的手机号。典型的“分组 HAVING 过滤”SELECT mobile, COUNT(*) AS cnt FROM users GROUP BY mobile HAVING COUNT(*) 2;这题和 4.1 相比更基础但很能检验候选人有没有理解 HAVING 语义。网上有些错误答案会用WHERE COUNT(*) 2执行直接报错。同样如果要求“把 uid 也带出来看是哪些用户”按时间先后取最早注册的那个就得结合 ROW_NUMBERWITH ranked_users AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY mobile ORDER BY uid ) AS rk FROM users ) SELECT * FROM ranked_users WHERE mobile IN (SELECT mobile FROM users GROUP BY mobile HAVING COUNT(*) 2) AND rk 1;第一段筛重复手机号第二段取每个手机号对应的最早用户两层逻辑层层递进这套路在面试里非常受用。4.9 第九题用 SQL 求精确中位数表结构test_score(student_id, score)。求所有学生成绩的中位数。中位数在数据分析里是比平均值更稳的集中趋势指标但 SQL 没有内置 MEDIAN。常规思路是先排序编号再根据总行数的奇偶定位中间位置WITH ranked_scores AS ( SELECT score, ROW_NUMBER() OVER (ORDER BY score) AS rk, COUNT(*) OVER () AS total_cnt FROM test_score ) SELECT AVG(score) AS median FROM ranked_scores WHERE rk IN (FLOOR((total_cnt 1) / 2), CEIL((total_cnt 1) / 2));解释一下这个公式总行数是奇数时FLOOR((N1)/2)和CEIL((N1)/2)指向同一个位置总行数为偶数时两个位置正好是中间的两个数AVG(score)就把它们平均了。这个通用写法在 MySQL 8.0、Hive、PostgreSQL 里都能跑通。顺带一提如果面试官问“怎么算众数”思路就是COUNT(*) GROUP BY score ORDER BY cnt DESC LIMIT 1两个概念放在一起记更不容易混淆。4.10 第十题用户首单后 30 天内累计下单次数表结构orders(order_id, user_id, order_date)。要求计算每个用户从首单日期开始往后 30 天内含首单当天累计下单次数。这道题综合了自关联、日期计算和聚合是很好的压轴题WITH first_order AS ( SELECT user_id, MIN(order_date) AS first_date FROM orders GROUP BY user_id ) SELECT f.user_id, f.first_date, COUNT(DISTINCT o.order_id) AS orders_in_30d FROM first_order f LEFT JOIN orders o ON f.user_id o.user_id AND o.order_date BETWEEN f.first_date AND DATE_ADD(f.first_date, INTERVAL 30 DAY) GROUP BY f.user_id, f.first_date;这里用 LEFT JOIN 是为了让没有后续订单的用户也能输出count 为 0。关联条件把日期窗口放在了 ON 子句里防止 30 天窗口外的数据进入统计这是很多人容易写错的地方。整个 SQL 其实是在模拟一个业务概念新客户的“首月活跃度”后面接续分析复购率、客单价的时候就非常有价值。5. 慢 SQL 优化从定位问题到索引设计5.1 接到慢查询先跑 EXPLAIN很多数据分析师取数时遇到慢 SQL第一反应是“加索引”。这想法本身没错但顺序反了。正确的处理方式是先看执行计划再决定改哪。以 MySQL 为例在查询前面加 EXPLAINEXPLAIN SELECT order_id, user_id, amount FROM orders WHERE order_date 2024-01-01 AND user_id 12345;重点关注几个字段type列从好到差大概是const eq_ref ref range index ALL如果看到 ALL 就是全表扫描基本可以断定这条查询要走索引优化rows列是预估扫描行数数字越大越危险extra里出现Using temporary或Using filesort说明查询里可能有排序或分组操作在大数据量下容易拖慢速度。5.2 索引列上套函数是性能隐形杀手慢 SQL 优化里最常见的反模式之一是在索引字段上套函数。比如WHERE DATE(order_date) 2024-01-01这个写法对索引极不友好因为数据库无法直接使用 order_date 的索引树必须先全表扫描再逐行套 DATE 函数。更好的写法是写成范围条件WHERE order_date 2024-01-01 00:00:00 AND order_date 2024-01-02 00:00:00;另一个常见问题是 OR 条件。WHERE user_id 123 OR order_date 2024-01-01这类组合经常导致索引失效改成两个查询用 UNION ALL 合并或者重构条件为等值索引优先效果通常会好很多。5.3 组合索引的最左前缀原则组合索引是数据分析取数时最值得掌握的优化手段。假设业务里常见查询是按(store_id, order_date)过滤那么建INDEX idx_store_date(store_id, order_date)最合适。使用时要遵守最左前缀原则查询条件必须包含组合索引的第一列索引才会被用到。也就是说WHERE store_id 1 AND order_date 2024-01-01会走索引但只写WHERE order_date 2024-01-01这个索引就废了。注意索引不是越多越好。每个索引都要占据存储空间写入数据时要额外维护索引树。分析岗位日常面对的是宽表但也不要无脑给所有字段建索引。我一般的原则是高频过滤字段建索引低基数字段比如性别、城市几乎不建单列索引join 字段要确保两边类型一致否则隐式类型转换会让索引失效。6. 一个销售分析项目的完整 SQL 落地从取数到结论前面几章偏基础和面试最后回到一个实际业务场景帮大家把 SQL 能力串成一条线。假设公司有一个销售明细表order_detail(order_id, order_date, product_id, sales_amount, city)和商品维表product_dim(product_id, category, product_name)。业务方的需求是分析华东区“上月各品类销售额排行”并和上上月做环比找出增长最快的品类。第一步先把两张大表关联起来筛选出华东区上月数据WITH base AS ( SELECT d.order_id, d.order_date, d.sales_amount, p.category, d.city FROM order_detail d JOIN product_dim p ON d.product_id p.product_id WHERE d.city IN (上海, 南京, 杭州, 苏州, 无锡) AND d.order_date 2024-02-01 AND d.order_date 2024-03-01 )第二步按品类聚合统计本月销量和销售额SELECT base.category, COUNT(DISTINCT base.order_id) AS order_cnt, SUM(base.sales_amount) AS total_amount, ROUND(AVG(base.sales_amount), 2) AS avg_order_amount FROM base GROUP BY base.category ORDER BY total_amount DESC;第三步如果要跟上月对比把统计维度扩展到两个月再用 LAG 求环比WITH monthly AS ( SELECT p.category, DATE_FORMAT(d.order_date, %Y-%m) AS month_str, SUM(d.sales_amount) AS total_amount FROM order_detail d JOIN product_dim p ON d.product_id p.product_id WHERE d.city IN (上海, 南京, 杭州, 苏州, 无锡) AND d.order_date 2024-01-01 AND d.order_date 2024-03-01 GROUP BY p.category, DATE_FORMAT(d.order_date, %Y-%m) ) SELECT category, month_str, total_amount, LAG(total_amount, 1) OVER ( PARTITION BY category ORDER BY month_str ) AS prev_amount, ROUND( (total_amount - LAG(total_amount, 1) OVER ( PARTITION BY category ORDER BY month_str )) / NULLIF(LAG(total_amount, 1) OVER ( PARTITION BY category ORDER BY month_str ), 0) * 100, 2 ) AS growth_rate FROM monthly ORDER BY category, month_str;这段 SQL 把所有重点都覆盖了JOIN 关联维表、日期范围过滤、按维度聚合、窗口函数求环比。跑出结果后我把增长率为正的品类按增速降序排再结合单价、销量和平均订单金额综合判断最后落到业务结论某品类销量拉了 30%但平均金额在下降说明增长主要来自低价商品需要留意利润结构。这类项目复盘多了我的体会是SQL 能力的核心不只是背函数而是拿到任何一张表都能快速形成“先过滤、再聚合、后分析”的路径。面试前花时间刷题是有用的但刷完之后一定要落到真实业务上去验证一次。等你真正用 SQL 解决过几个业务问题再回头面对面试官那些窗口函数和去重技巧就会变得非常自然。
