企业官网建设流程全解析

最近做模拟面试，几乎每天都有人栽在SQL上。不是不会写，而是把简单题写成了复杂题，把复杂题写成了错误题。现在数据分析岗位的面试，SQL基本是第一轮技术面的必选项，而且很多公司会把SQL题直接放在共享屏幕里让你现场敲。刷题的人不少，但能刷到点子上的人不多。

这篇文章不打算罗列一套题库，而是把数据分析SQL面试里真正高频的题型拆开讲：面试官在问什么、解题前要想清楚什么、哪些细节会直接让你从头再来。尤其是窗口函数、留存率、连续行为、Join数据质量这几类题，我把大厂真题（已脱敏）的常见考法、正确解法和常见翻车点全部过一遍。适合正在备战的求职者，也适合想系统梳理SQL取数思路的分析师。

1. 数据分析面试中SQL到底在考什么：题型地图与准备思路

1.1 比起语法，面试官更想看你的取数思维

先说一个很多人忽视的事实：数据分析岗位的SQL面试，和开发岗位的SQL面试，考核点完全不一样。开发岗位更关注索引、执行计划、事务、复杂查询性能；数据分析岗位更关注口径、去重、聚合、时间维度和业务指标。如果你只会背各种语法但不懂业务口径，写出来的SQL在面试官眼里就是一堆错误逻辑。

举个例子，一道简单题：“订单表中每个用户的人均消费金额是多少？”

很多候选人张嘴就问“订单表有哪些字段”，这是对的。但更关键的是，你要先想清楚“人均消费金额”的分母是什么——是有过支付行为的用户数，还是注册用户总数，还是所有用户数？分母不同，SQL完全不同。面试官大概率不会把所有口径都告诉你，而是在你写完之后追问一句“你这个分母对吗”。这一问问的就是你有没有业务意识，而不是会不会用avg。

所以准备SQL面试，我建议先建立一个认知：题目是壳，业务是核。语法只是工具，真正考察的是你在不确定信息下如何定义指标、如何取舍数据、如何验证结果。

1.2 常考题型地图与难度分层

根据我这些年看到的真实面试题，可以把数据分析SQL面试题分成五个层面：

大多数候选人能走到第三层，也就是窗口函数。但从第四层开始，差距就拉开了。头部公司的面试题基本集中在三、四、五层，而且会故意把口径设计得模糊，让候选人踩坑。

1.3 按这个顺序准备，效率最高

如果你还有一到两周的冲刺时间，建议不要按题型刷完整本题库，而是按下面这个顺序来：

1. 先把单表聚合、时间函数、子查询这些基础语法练到不用想就写对。
2. 再做窗口函数专项，尤其是row_number、rank、dense_rank三兄弟的区别，以及与group by的配合。
3. 然后练留存率、连续登录这类业务综合题，重点不是背答案，而是写完后能解释每一步为什么要这么做。
4. 最后看3到5个慢查询优化案例，理解Join执行顺序和索引的影响。

这个顺序的核心思路是：基础语法是速度，业务题型是深度，优化是区分度。面试只有不到一小时，你要靠准确性拿分，而不是靠奇技淫巧。

2. 送分也送命的聚合统计题：细节决定去留

2.1 一个订单表，能问出多少“送命题”

聚合统计题看上去是最简单的，但恰恰是淘汰率最高的一类。原因是候选人容易把题目想得太简单，上来就写，结果漏掉关键条件。

基本款题目通常长这样：

已知订单表有order_id, user_id, amount, pay_time, pay_status五个字段，求2024年每个用户的支付订单数、支付总金额、平均支付金额。

大多数人会这么写：

select user_id, count(*) as order_cnt, sum(amount) as total_amount, avg(amount) as avg_amount from orders where pay_time between '2024-01-01' and '2024-12-31' group by user_id;

这段SQL踩了两个坑。第一，count(*)会把所有订单都数一遍，如果表里存在pay_status为“已退款”或“未支付”的订单，金额就失真了。第二，order_id不一定是主键，如果底层数据有重复记录，count(*)会重复计数。

我在真实业务里处理过一份订单数据，因为上游同步多跑了一次任务，整张表出现了重复行。那周所有报表全部翻倍，排查了大半天才定位到。所以现在看到任何计数都会习惯性带上count(distinct ...)，线上用数前也会先看一眼有没有重复。

稳妥写法是先明确字段含义，再把状态条件加进去：

select user_id, count(distinct order_id) as pay_order_cnt, sum(amount) as pay_amount, round(sum(amount) / count(distinct order_id), 2) as avg_amount from orders where pay_status = 'paid' and pay_time >= '2024-01-01' and pay_time < '2025-01-01' group by user_id;

时间条件我习惯用半开区间[start, end)，也就是>=和<，而不是between。因为between是闭区间，如果时间字段带时分秒，“2024-12-31 23:59:59”这条记录到底算不算2024年，不同数据库处理还不一样。用半开区间能彻底避免这种边界污染。

2.2 每月新增用户的计算：口径问题比语法更关键

另一类高频聚合题是“新增用户数”。题目一般长这样：

某产品有user_info(user_id, reg_time)表，也有pay_info(user_id, pay_time)表。求2024年每个月的支付用户中，有多少是在当月首次支付的。

很多人的第一反应是直接 group by 月份然后 count，但这会把老用户也数进来。正确思路是先找到每个用户的首次支付时间，再按月份聚合。

with first_pay as ( select user_id, min(pay_time) as first_pay_time from pay_info where pay_time >= '2024-01-01' and pay_time < '2025-01-01' group by user_id ) select date_format(first_pay_time, '%Y-%m') as pay_month, count(distinct user_id) as new_pay_users from first_pay group by date_format(first_pay_time, '%Y-%m') order by pay_month;

这里有几个细节值得注意。

如果题目问的是“2024年新增支付用户”，那first_pay_time必须在2024年内，所以 where 条件写在子查询里是对的。但如果你把 where 条件加在外层，比如在外面写where date_format(first_pay_time,'%Y-%m')是2024，意思也差不多，可执行效率会差很多——因为外层过滤没法利用min()的内部结构，甚至可能提前把2023年的老用户也拉出来算一遍。

另一个容易出错的地方是：date_format是类MySQL写法，Hive里要用substr(first_pay_time, 1, 7)或者trunc(first_pay_time, 'MM')，PostgreSQL里用to_char(first_pay_time, 'YYYY-MM')。面试时如果无法确定数据库类型，最好先跟面试官确认一下，不要沉默着用一种语法硬写。

2.3 我反复强调的审题习惯

面试时我会反复提醒自己三句话，这里也分享给你。

第一，先确认指标定义。不要怕问，面试官通常不会因为你问口径而扣分，反而会觉得你稳妥。第二，先做去重预算。凡是计数，心里都要过一遍“这个字段有没有可能有重复”，不确定就默认加distinct。第三，算完看一眼量级。如果你求的是月度新增用户，结果一个月掉了80%，那大概率不是业务波动，而是你的过滤条件飘了。

3. 窗口函数题：TopN、排名与并排去重

3.1 row_number、rank、dense_rank：选错函数就是0分

窗口函数题里，最基础也最高频的就是排名。面试官喜欢问这类题，因为一道题能同时考三个点：分区是否正确、排序方向是否正确、选哪个排名函数。

先把三兄弟的区别说清楚：

• row_number()：给每行一个连续编号，不管有没有并列，值从1到N，永不重复。
• rank()：存在并列时跳过排名。比如两个第1名，下一个就是第3名。
• dense_rank()：存在并列时也给出连续的排名，两个第1名之后，下一个是第2名。

业务含义完全不同。取“唯一的最新一条”用row_number()；取“并列的TopN并正常跳号”用rank()；取“所有跨过阈值的人都算”用dense_rank()。

最典型的题目是：员工表里求每个部门工资最高的前三名。

select dept_id, emp_name, salary, rank_no from ( select dept_id, emp_name, salary, rank() over(partition by dept_id order by salary desc) as rank_no from employee ) t where rank_no <= 3;

注意这里用的是rank()，因为如果部门里有两个人工资一样，他们应该并列第一，且下一个人的名次应该是第三。虽然题目说“前三名”，但并列逻辑必须看题面是否要求“每个部门最多返回3行”。如果明确要求最多3行，就要改用row_number()；如果要求“所有达到前三名水平的人”，就用rank()。一字之差，结果完全不同。

3.2 大厂真题：取每人最近一笔已支付订单

排名题的第二类高频考法是“取最近一笔”或“取最早一笔”。这类题逻辑不难，但非常考验窗口函数的使用细节。

某电商平台面试题（已脱敏）：订单表orders(order_id, user_id, amount, pay_time, pay_status)，取每个用户最近一笔已支付订单的完整信息。

select order_id, user_id, amount, pay_time from ( select order_id, user_id, amount, pay_time, row_number() over( partition by user_id order by pay_time desc, order_id desc ) as rn from orders where pay_status = 'paid' ) t where rn = 1;

这里有两个容易扣分的地方。第一，partition by是明确“每个用户”的边界，漏掉它，整个结果就只剩全表一条记录。第二，order by后面只写pay_time desc还不够。如果同一秒有两笔订单，返回哪一条不确定，必须再加一个唯一排序键，比如order_id desc，确保结果稳定。面试官会追问“同一时间有多笔订单怎么处理”，就是看你有没有想到这一层。

3.3 窗口函数里最容易翻车的三个细节

窗口函数看起来不可怕，但实际现场写的时候有三个细节特别容易翻车。

第一个细节是：窗口函数不能直接在 where 里过滤。比如你写了where rn = 1，数据库会提示 rn 字段不存在。正确答案永远是把带窗口函数的查询包一层子查询。经常看到候选人在这一步卡住，然后开始手忙脚乱改结构，其实只需要平时多写两遍就记住了。

第二个细节是：窗口函数与 group by 同时使用时，逻辑顺序是先分组聚合，再对聚合后的结果开窗。比如“求每个部门平均工资最高的部门”，你可以先 group by 部门再套max(avg_salary)，也可以直接用窗口函数max(avg(salary)) over()，但后者的写法在不同数据库里支持程度不一样，心里要有数。

第三个细节是：over()里不加partition by，就是对全表开窗，也就是算一个全局值。有些题目的“占比”就是要全局窗口，但经常有人把它当成分组窗口来写，结果每个占比都变成100%。拿到题先画一遍分区范围，比直接写代码靠谱得多。

4. 留存率与连续行为：两个最吃口味的综合SQL题型

4.1 留存率：先定义再动手，不然写的SQL没有意义

留存率题几乎是头部公司数据分析面试的标配。看起来就是“次日留存、7日留存、30日留存”，但真正写起来错误率极高。核心原因不是SQL语法复杂，而是留存率的定义在一开始就没统一。

同样是“新增用户次日留存”，至少有三个口径差异：

面试时我一般会先问清楚这三件事，再写SQL。你写出的代码再漂亮，只要口径不对，面试官就没有兴趣听下去。

一个比较标准的次日及7日留存SQL模板大概长这样：

with active_user_daily as ( select user_id, dt from active_log where dt >= '2024-01-01' and dt < '2024-03-01' group by user_id, dt ), first_active as ( select user_id, min(dt) as first_dt from active_user_daily group by user_id ) select f.first_dt, count(distinct f.user_id) as new_users, count(distinct case when a1.dt = date_add(f.first_dt, 1) then a1.user_id end) as d1_users, count(distinct case when a7.dt = date_add(f.first_dt, 7) then a7.user_id end) as d7_users, round(count(distinct case when a1.dt = date_add(f.first_dt, 1) then a1.user_id end) / count(distinct f.user_id), 4) as d1_rate, round(count(distinct case when a7.dt = date_add(f.first_dt, 7) then a7.user_id end) / count(distinct f.user_id), 4) as d7_rate from first_active f left join active_user_daily a1 on f.user_id = a1.user_id and a1.dt = date_add(f.first_dt, 1) left join active_user_daily a7 on f.user_id = a7.user_id and a7.dt = date_add(f.first_dt, 7) group by f.first_dt;

这里最关键的一点是：活跃表必须先在用户+日期粒度去重。如果不去重，count(distinct ...)还能兜住一部分错误，但 Join 的数据量会大很多；如果你统计的是“用户当天活跃次数”，那不去重就直接错了。

另一个坑是：date_add的写法在不同引擎也不一样，Hive里还有date_add(f.first_dt, 1)，PostgreSQL里则要写f.first_dt + interval '1 day'。先确认环境再写，别硬套。

4.2 连续登录最大天数：编号减日期的经典技巧

连续N天类题目，难度普遍在留存率之上。因为它考的已经不是“你会不会用某个函数”，而是“你能不能把连续区间问题转化为分组问题”。

题目常问：某APP活跃表user_id, login_date，每个用户一天可能有多条记录。求2024年每个用户最长连续登录天数。

第一步先去重，把每个用户每天是否登录变成一行：

with active_daily as ( select user_id, login_date from login_log where login_date >= '2024-01-01' and login_date < '2025-01-01' group by user_id, login_date ) select * from active_daily;

第二步是核心技巧：对每个用户按日期排序，生成行号。然后用登录日期减去行号。因为连续登录的日子里，日期每天加1，行号也每天加1，两者相减得到的“分组标识”完全一样；一旦出现断档，日期不连续，减出来的值就会跳变。

with numbered as ( select user_id, login_date, row_number() over(partition by user_id order by login_date) as rn from active_daily ), grouped as ( select user_id, login_date, date_sub(login_date, interval rn day) as group_flag from numbered ) select user_id, max(continue_days) as max_continue_days from ( select user_id, group_flag, count(*) as continue_days from grouped group by user_id, group_flag ) t group by user_id;

这个“日期减行号”的思路，建议背下来。它的本质是把一个连续的物理日期序列映射到一个常量分组键上。理解了原理，后面遇到“连续周”、“连续签到”、“每个月连续N天”都能举一反三。

4.3 变形题：任意连续N天、多维度连续行为

大厂面试很少满足于只问“最大连续天数”，更多是在这个基础上叠加条件。比如：

• 找出连续登录超过5天的用户。
• 找出2024年任意一个自然周内，活跃满5天的用户。
• 求直播间连续在线超过1小时的主播。

口诀还是那两句：先去重，再编号，最后差值分组。如果是“自然周内满5天”，就不需要看连续性，而是用date_format把日期转成周标号，再 group by 用户和周标号。

如果是“连续N天且中间不能有断档”，则在差值分组之后，还要再用having count(*) >= N过滤。注意having是在分组之后执行的，这正好符合我们的需求。

这里我还想提一个教训：做完连续登录题之后，一定要拿一个小样例手算一遍。比如用户A登录了1、2、3、5号，算出来最大连续应该是3天。很多人写完SQL后不验证，把“连续区间个数”和“最大连续天数”混为一谈，导致结果偏差。面试官让你“手写下结果”时，当场就能看出来你懂不懂。

5. Join与性能优化：基础关联题如何答出加分项

5.1 一个数据翻倍的隐患：多对一关联

如果说聚合和窗口是“能不能写出正确结果”，那Join和调优就是“在真实数据环境里能不能活下来”。

最容易翻车的一类题：订单明细表 join 商品信息表，金额翻倍。举个我处理过的场景：订单表一行是一个SKU（库存单位），商品表一行是一个SPU（商品编号），一个SPU下面挂了三个SKU。直接内连接后，同一个SPU的订单行会被复制成三行，金额跟着膨胀三倍。

如果你只记得left join能保留左表全部行，就以为不会出问题，那还远远不够。Join 的语义是“按照连接键匹配”，匹配上几条就复制成几条。左连接只保证左表行不消失，不保证左表行不膨胀。

处理这类问题有三种思路。第一种，先对维度表去重，确保连接键唯一，再Join。第二种，先聚合到业务键粒度，再Join。第三种，如果已经膨胀了，用distinct或者取行号为1的方式回退到业务主键粒度。面试时如果能说出这三种思路并讲清适用场景，基本就过关了。

5.2 从执行计划看Join优化

面试题里的性能优化，一般不会真让你调一个上亿数据的集群，而是给你一段能跑但明显很蠢的SQL，问你怎么优化。常见套路包括：

• 两张千万级大表直接Join，没有先过滤。
• where条件写在 on 后面的等价条件里，导致过滤顺序不对。
• 子查询里select *，反正不管有没有用的字段全部查出来。
• 对索引列做函数计算，比如where date(create_time) = '2024-01-01'，导致索引失效。

优化的基本原则：先缩小数据量，再关联；先下推过滤条件，再聚合；先投影需要的列，再参与计算。

以时间过滤为例：

-- 不推荐：先全表关联，再在外部过滤时间 select ... from orders o join users u on o.user_id = u.user_id where date(o.pay_time) = '2024-01-01'; -- 推荐：内部子查询先把时间范围过滤掉 select ... from (select * from orders where pay_time >= '2024-01-01' and pay_time < '2024-01-02') o join users u on o.user_id = u.user_id;

第二种写法看着多包了一层，没准结果更慢？不一定。关键在于优化器能不能识别你的意图。在大多数数据库里，直接写pay_time >= '2024-01-01' and pay_time < '2024-01-02'也可以走索引，但一旦写成date(pay_time)，索引就废了。所以在面试中，与其讨论子查询怎么写，不如先回答“如何保证可以使用索引”和“如何缩小Join前的数据量”。

5.3 做过分区裁剪和字段裁剪的人，面试官会高看一眼

大厂数据分析场景里，表通常是分区表，最常见的时间分区。面试官问“这张订单表有几十亿行，你怎么做分析”时，不是让你写select * from orders，而是看你有没有分区意识和字段裁剪意识。

一个标准回答是：先通过where锁死时间分区，缩小扫描范围；再只select业务需要字段，避免大字段拖慢查询；最后能用聚合的尽量在子查询里先聚合，减少Join的数据量。如果还是慢，再看Join类型，是不是连接键的类型不一致导致隐式转换，比如字符串类型的user_id和数值类型的user_id直接等值连接。

这个思路我在真实业务里反复用过。有一次一个报表任务从40分钟降到2分钟，就是加了一个分区过滤条件，再排除一个从不需要的备注字段。面试考优化，不是为了让你背概念，而是看你在百万级、亿级数据的压力下会不会想到这些最基础的物理约束。

6. 面试实战的Process与话术：从“会写”到“讲清楚”

6.1 拿到SQL题先做三件事，不要急着输出

很多候选人能力强，但输在面试节奏上。看到题，鼠标已经停在编辑器里，手比脑子快。结果写一半发现少了个过滤条件，又开始整段重写，非常影响印象分。

我建议拿到SQL题先做三件事：

1. 用半分钟复述题目：“所以我理解是，2024年，每个用户的所有已支付订单里，取金额最高的一单，这个对吧？”让面试官确认口径，也给自己思考时间。
2. 在草稿或注释里写出主查询骨架：从哪张表取数、按什么分组、要哪几个字段。
3. 默写过滤条件：时间范围、状态过滤、去重键。写之前关闭编辑器，先把这三点说一遍。

这三件事做完，真正动手写的时候基本一气呵成。面试官不会觉得你反应慢，反而会看到你有一个规范的取数流程。

6.2 面试官最想听到的分析框架

SQL题的追问环节，往往比写题环节更致命。你写出了正确SQL，面试官会接着问三个问题：

• 这个SQL在数据量很大的时候会慢吗？怎么优化？
• 如果一张表有脏数据，你的结果会怎么变？
• 如果指标口径变了，比如从“支付用户”变成“注册用户”，你的SQL要改哪里？

这三个问题的本质是：你会不会只写代码，而不思考数据的生命周期。我的建议是准备一套自己的分析框架，每次回答都按“口径确认、数据验证、性能预估”三条线来讲。即使你现场想不到完美答案，至少让面试官看到你有系统思考的习惯。

6.3 复盘：写对SQL只是第一步

面试结束后，很多人会陷入“我题都写对了，为什么挂了”的困惑。大概率不是因为SQL写错，而是因为没有把解题过程讲清楚。写SQL时嘴里要同步说：我先看到需要去重、我准备用窗口函数取TopN、这里注意时间边界。先做什么后做什么，优先说什么话，直接决定面试官对你的判断。

最后再分享一个我用了很久的小技巧：每天抽15分钟，找一个谁都看得懂的小表，用至少三种不同写法实现同一个取数需求，然后对比它们的差异。比如求每个用户最大连续登录天数，先写两遍窗口函数法，再写一遍日期差法，再试试能不能用变量法实现。这个过程不只是在练SQL，更是在练你对数据结构的敏感度。面试时真正拉开差距的，往往不是那几百道题刷得多熟，而是你能不能对一个看似简单的需求讲出背后的数据逻辑。