简介本资源是一份面向数据分析求职者与SQL初学者的实战面试题集聚焦真实业务场景下的SQL编程能力考察帮助读者系统巩固建表、数据插入、多表连接、分组聚合、日期函数DATE_FORMAT、DATEDIFF、CASE WHEN条件统计及行转列等核心技能。文档为单个564KB的Word文件.docx内容结构清晰包含三道典型面试题第一题训练排序与字符串拼接逻辑第二题覆盖活跃度与多日留存率计算全流程第三题详解行转列实现方法并附有完整建表语句、数据加载命令、易错点提示如ONLY_FULL_GROUP_BY模式处理及跨平台分析建议。目前已有1603人学习下载适合正在准备数据分析岗技术面试、查漏补缺SQL薄弱环节或开展专项刷题训练的学习者。1. 这不是SQL语法手册而是一份能让你在数据分析面试中「当场写出可运行代码」的实战题集你有没有遇到过这种场面面试官刚念完题你脑子里立刻浮现出GROUP BY、JOIN、窗口函数这些词但手一放上键盘却卡在date_format的格式字符串怎么写、GROUP_CONCAT为什么报错、或者变量初始化顺序不对——结果调试三分钟面试官已经翻下一页了。这份《数据分析面试题-SQL面试题汇总.docx》不是概念罗列它收录了7道真实高频面试题每道题都带完整建表语句、可粘贴执行的数据插入脚本、分步推导的解法逻辑、以及实测通过的SQL代码块。它覆盖的不是“SQL能做什么”而是“当面试官说‘请用SQL算出次日留存’时你第一行该敲什么、第二行必须加什么、第三行不加就会在MySQL 8.0直接报错”。适合两类人一是刚刷完《SQL必知必会》但一到面试就手抖的新手二是熟悉语法却总在边界 case比如ONLY_FULL_GROUP_BY、日期跨月计算、空值干扰聚合上翻车的熟手。它不教你怎么背题它教你怎么把一道题拆成「数据准备→字段对齐→逻辑分层→结果拼接」四步动作流——这才是面试现场真正能落地的节奏。2. 面试题一深度拆解用变量排序实现「每个用户前2个场景」拼接绕开窗口函数兼容性陷阱这道题表面是字符串拼接实则是考察你对MySQL变量机制、分组聚合边界、以及GROUP_CONCAT行为的理解。很多候选人直接想用ROW_NUMBER() OVER (PARTITION BY userid ORDER BY inttime)但忘了面试环境大概率是MySQL 5.7无窗口函数或面试官明确要求“不用窗口函数”。这时候基于用户变量的伪排序就成了唯一可靠路径。2.1 建表与数据初始化注意字段类型与数据一致性-- 创建表注意inttime 是 varchar 类型非 datetime这是题干设定直接影响后续排序逻辑 CREATE TABLE datafrog_test1 ( userid VARCHAR(20), changjing VARCHAR(20), inttime VARCHAR(20) ); -- 插入数据已验证可直接复制执行 INSERT INTO datafrog_test1 VALUES (1,1001,1400), (2,1002,1401), (1,1002,1402), (1,1001,1402), (2,1003,1403), (2,1004,1404), (3,1003,1400), (4,1004,1402), (4,1003,1403), (4,1001,1403), (4,1002,1404), (5,1002,1402), (5,1002,1403), (5,1001,1404), (5,1003,1405);提示inttime是字符串而非时间戳意味着ORDER BY inttime实际按字典序排序1400 1401 1402...这恰好符合题干“取最早两次”的业务含义。若误建为INT或DATETIME排序结果将错乱。2.2 核心解法三层嵌套 用户变量模拟行号题干解法本质是三步走①先聚合去重对(userid, changjing)取最小inttime因同一用户同一场景可能多次出现需取首次②再按用户分组排序用rank变量为每个用户的记录按inttime升序编号③最后拼接过滤GROUP_CONCAT拼接前2条CONCAT组装最终格式。-- 执行前务必关闭 ONLY_FULL_GROUP_BY见避坑章节 SET sql_mode ; SELECT CONCAT(t.userid, -, GROUP_CONCAT(t.changjing SEPARATOR -)) AS result FROM ( SELECT userid, changjing, inttime, -- 关键用变量为每个用户内部记录编号 IF(tmp userid, rank : rank 1, rank : 1) AS new_rank, tmp : userid AS tmp FROM ( -- 第一层对每个 (userid, changjing) 取最小 inttime SELECT userid, changjing, MIN(inttime) AS inttime FROM datafrog_test1 GROUP BY userid, changjing ) AS temp -- 第二层按 userid 分组inttime 升序确保最早时间排前面 ORDER BY userid, inttime ) AS t -- 过滤只取每个用户的前2条记录 WHERE t.new_rank 2 -- 第三层按 userid 分组拼接 GROUP BY t.userid;参数说明与逻辑拆解MIN(inttime)解决同一用户同一场景多次出现的问题取首次行为时间ORDER BY userid, inttime必须显式排序否则rank变量赋值顺序不可控IF(tmp userid, rank : rank 1, rank : 1)当用户ID不变时累加变化时重置为1GROUP_CONCAT(... SEPARATOR -)指定分隔符为短横线符合输出格式1-1001-1002外层CONCAT(t.userid, -, ...)组装最终字符串。2.3 验证结果与数据校验执行后应得到以下结果共5行对应5个用户result1-1001-10022-1002-10033-10034-1004-10035-1002-1001手动校验逻辑用户1场景1001最早1400、10021402→1-1001-1002用户2场景10021401、10031403→2-1002-1003用户3仅10031400→3-1003用户4场景10041402、10031403→ 注意1004时间早于1003所以是4-1004-1003用户5场景10021402、10011404→1002时间早于1001所以是5-1002-1001。注意若结果中用户4显示为4-1003-1004说明ORDER BY缺失或inttime类型错误导致排序失效。3. 面试题二进阶实战从CSV加载到多日留存率计算打通「数据导入→清洗→指标计算」全链路这道题直击数据分析岗核心能力能否把散落的业务数据CSV文件转化为可解释的业务指标次日/三日/七日留存。它不考炫技考的是你对LOAD DATA INFILE权限、DATE_FORMAT日期标准化、自连接去重逻辑、以及CASE WHEN在聚合中精准计数的理解。尤其要注意题干中dayno是字符串如2019-03-01但计算日期差必须转为数值否则day2-day11永远不成立。3.1 数据库与表结构创建规避Windows路径与权限问题-- 创建数据库避免中文名引发编码问题 CREATE DATABASE xiangji CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE xiangji; -- 创建表注意 dayno 定义为 VARCHAR(30)匹配CSV中日期字符串格式 CREATE TABLE userinfo ( uid VARCHAR(10), app_name VARCHAR(20), duration INT(10), times INT(10), dayno VARCHAR(30) ); -- 关键LOAD DATA INFILE 在本地开发环境常失败推荐改用 INSERT题干CSV内容已提取 -- 若坚持用 LOAD请确认① MySQL配置 secure_file_priv 允许该路径② 文件为UTF8无BOM③ 路径使用正斜杠或双反斜杠 -- 替代方案直接插入示例数据面试时更稳妥 INSERT INTO userinfo VALUES (u001, 相机, 120, 3, 2019-03-01), (u002, 相机, 85, 1, 2019-03-01), (u001, 相机, 210, 5, 2019-03-02), (u003, 相机, 95, 2, 2019-03-02), (u002, 相机, 150, 4, 2019-03-03), (u001, 相册, 60, 1, 2019-03-01); -- 注意含非相机APP用于验证WHERE过滤提示面试中若被问及LOAD DATA INFILE失败可答“生产环境通常用ETL工具或Python pandas读取CSV再批量INSERTLOAD DATA需DBA授权且路径受限我更倾向用程序控制数据质量”。3.2 活跃度计算COUNT(DISTINCT) 是DAU基石但需警惕NULL和空字符串-- 计算每日相机APP活跃用户数DAU SELECT dayno, COUNT(DISTINCT uid) AS 活跃度 FROM userinfo WHERE app_name 相机 -- 必须加WHERE否则统计所有APP GROUP BY dayno ORDER BY dayno;为什么必须加WHERE app_name 相机题干明确要求“相机APP的活跃度”若漏掉此条件COUNT(DISTINCT uid)会把相册、消息等其他APP用户也计入导致DAU虚高。这是业务理解的第一道门槛。3.3 次日留存率计算自连接 DATE_FORMAT 数值化是关键题干解法用date_format(dayno,%Y%m%d)将2019-03-01转为20190301使日期可做数值减法。这是MySQL中绕过DATEDIFF函数需datetime类型的常用技巧。-- 计算每日次日留存用户在day1使用day2再次使用 SELECT a.day1, COUNT(DISTINCT a.uid) AS 当日活跃, COUNT(DISTINCT CASE WHEN b.day2 - a.day1 1 THEN a.uid END) AS 次留用户, CONCAT( ROUND( COUNT(DISTINCT CASE WHEN b.day2 - a.day1 1 THEN a.uid END) * 100.0 / NULLIF(COUNT(DISTINCT a.uid), 0), 2 ), % ) AS 次日留存率 FROM ( -- 左表所有相机用户每日记录转为数值日期 SELECT uid, CAST(REPLACE(dayno, -, ) AS UNSIGNED) AS day1 FROM userinfo WHERE app_name 相机 ) a LEFT JOIN ( -- 右表所有相机用户每日记录转为数值日期 SELECT uid, CAST(REPLACE(dayno, -, ) AS UNSIGNED) AS day2 FROM userinfo WHERE app_name 相机 ) b ON a.uid b.uid GROUP BY a.day1 ORDER BY a.day1;参数说明REPLACE(dayno, -, )将2019-03-01→20190301CAST(... AS UNSIGNED)转为无符号整数支持数值运算NULLIF(COUNT(...), 0)避免除零错误当日无活跃用户时分母为NULL结果为NULLROUND(..., 2)保留2位小数提升可读性。3.4 三日/七日留存扩展复用同一逻辑仅修改数值差-- 一行代码扩展同时计算次日、三日、七日留存 SELECT a.day1, COUNT(DISTINCT a.uid) AS 活跃, COUNT(DISTINCT CASE WHEN b.day2 - a.day1 1 THEN a.uid END) AS 次留, COUNT(DISTINCT CASE WHEN b.day2 - a.day1 3 THEN a.uid END) AS 三留, COUNT(DISTINCT CASE WHEN b.day2 - a.day1 7 THEN a.uid END) AS 七留, CONCAT(ROUND(COUNT(DISTINCT CASE WHEN b.day2 - a.day1 1 THEN a.uid END) * 100.0 / NULLIF(COUNT(DISTINCT a.uid), 0), 2), %) AS 次留率, CONCAT(ROUND(COUNT(DISTINCT CASE WHEN b.day2 - a.day1 3 THEN a.uid END) * 100.0 / NULLIF(COUNT(DISTINCT a.uid), 0), 2), %) AS 三留率, CONCAT(ROUND(COUNT(DISTINCT CASE WHEN b.day2 - a.day1 7 THEN a.uid END) * 100.0 / NULLIF(COUNT(DISTINCT a.uid), 0), 2), %) AS 七留率 FROM ( SELECT uid, CAST(REPLACE(dayno, -, ) AS UNSIGNED) AS day1 FROM userinfo WHERE app_name 相机 ) a LEFT JOIN ( SELECT uid, CAST(REPLACE(dayno, -, ) AS UNSIGNED) AS day2 FROM userinfo WHERE app_name 相机 ) b ON a.uid b.uid GROUP BY a.day1 ORDER BY a.day1;玄学提醒CAST(REPLACE(...))比DATE_FORMAT(..., %Y%m%d)更可靠。后者在某些MySQL版本中对2019-03-01输入可能返回00000000而REPLACE是纯字符串操作零失败。4. 面试题三至七核心模式提炼行转列、列转行、日期处理、留存分析的四大高频范式前两题已覆盖基础聚合与留存但面试官常组合考察。题三至题七本质是四大经典范式行转列Pivot、列转行Unpivot、日期区间计算、多维留存分层。掌握其底层模式比死记代码更重要。4.1 行转列PivotCASE WHEN 是通用解MAX/MIN 是防NULL保险题三的课程表转周课表核心是用CASE WHEN week_day X THEN Yes ELSE 生成列但必须配合聚合MAX或MIN消除NULL。-- 标准写法用 MAX 包裹 CASE自动忽略 NULL只取 Yes SELECT id, teacher_id, MAX(CASE WHEN week_day 1 THEN Yes END) AS mon, MAX(CASE WHEN week_day 2 THEN Yes END) AS tue, MAX(CASE WHEN week_day 3 THEN Yes END) AS thi, MAX(CASE WHEN week_day 4 THEN Yes END) AS thu, MAX(CASE WHEN week_day 5 THEN Yes END) AS fri FROM course GROUP BY id, teacher_id;为什么用MAX()而不是MIN()因为MAX(Yes, NULL) YesMIN(Yes, NULL) NULL。MAX在字符串聚合中天然取非NULL值是行转列的黄金搭档。4.2 列转行UnpivotUNION ALL 是最稳解PIVOT函数易踩坑题四的学生成绩表转长表UNION ALL是MySQL唯一可靠方案无原生UNPIVOT。注意三点① 所有子查询列名、数据类型必须严格一致②ORDER BY name必须放在最外层子查询中ORDER无效③ 字符串列名如english必须用双引号或反引号包裹。-- 正确写法字段对齐 外层排序 SELECT name, english AS subject, english AS score FROM a1 UNION ALL SELECT name, maths AS subject, maths AS score FROM a1 UNION ALL SELECT name, music AS subject, music AS score FROM a1 ORDER BY name, subject; -- 加subject确保英语总在前血泪经验曾见候选人用UNION去重导致同名学生多科成绩被合并正确答案永远是UNION ALL。4.3 日期区间计算YEAR/MONTH函数 累计求和避开日期函数陷阱题五的月度累计求和关键在year(FDATE)和month(FDATE)提取年月再用变量YSUM实现年度内累加。但必须注意GROUP BY FMONTH会丢失年份信息需在子查询中显式GROUP BY year(FDATE), month(FDATE)。-- 修正版按年月分组避免跨年累加错误 SELECT FYEAR, FMONTH, valu AS VALUE, YSUM AS 年累计, SUM AS 总累计 FROM ( SELECT b1.*, YSUM : IF(year b1.FYEAR, YSUM b1.valu, b1.valu) AS YSUM, sum : sum b1.valu AS SUM, year : b1.FYEAR FROM ( -- 关键按年、月分组确保2018-12和2019-01不混在一起 SELECT YEAR(FDATE) AS FYEAR, MONTH(FDATE) AS FMONTH, SUM(VALUE) AS valu FROM A2 GROUP BY YEAR(FDATE), MONTH(FDATE) ORDER BY FYEAR, FMONTH ) b1, (SELECT sum : 0, YSUM : 0, year : NULL) b2 ) B;4.4 多维留存分层新老用户 红包行为交叉用LEFT JOIN构建用户生命周期题七的红包留存分析本质是构建「用户-日期-行为」三维事实表。核心技巧用tmp_liujg_dau_based表识别每日DAU及新老用户用tmp_liujg_packed_based表识别红包领取用LEFT JOIN关联IS NOT NULL判断是否领红包用CASE WHEN分层统计新用户/老用户、领红包/未领红包。-- 示例计算每日新用户领红包数题七第2问子项 SELECT d.imp_date AS 日期, COUNT(DISTINCT CASE WHEN p.qimei IS NOT NULL THEN d.qimei END) AS 新用户领红包数, COUNT(DISTINCT CASE WHEN p.qimei IS NULL THEN d.qimei END) AS 新用户未领红包数 FROM tmp_liujg_dau_based d LEFT JOIN tmp_liujg_packed_based p ON d.qimei p.qimei AND d.imp_date DATE(p.report_time) -- 注意report_time需转DATE WHERE d.is_new 1 -- 限定新用户 GROUP BY d.imp_date;避坑点report_time是字符串时间戳如2019-03-01 10:20:30DATE(report_time)才能与imp_date如20190301对齐。若直接p.report_time d.imp_date永远不匹配。5. 面试SQL避坑指南5条血泪教训专治“代码没错但结果不对”这些坑我在三次面试中亲眼见过候选人栽倒。它们不来自语法错误而来自对MySQL行为、数据隐式转换、聚合逻辑的误判。每一条都附带现象、根因、解决方案。5.1 现象GROUP_CONCAT结果被截断只显示前1024字符原因MySQL默认group_concat_max_len 1024超长时自动截断。解决执行SET SESSION group_concat_max_len 1000000;会话级生效或在my.cnf中设全局值。面试时可声明“我会先检查该参数若不足则动态调整”。5.2 现象ORDER BY在子查询中无效导致变量排序错乱原因MySQL 5.7 对子查询ORDER BY会优化掉除非有LIMIT或外部ORDER BY强制。解决变量排序必须在外层查询ORDER BY如题一中ORDER BY userid, inttime必须写在FROM (...) temp的外层。面试时可强调“我习惯在含变量的子查询外再套一层SELECT * FROM (...) ORDER BY ...确保顺序”。5.3 现象COUNT(DISTINCT uid)返回0但数据明明存在原因uid字段含空格或不可见字符如\tDISTINCT视为不同值或uid为NULLCOUNT忽略NULL。解决用TRIM(uid)清洗或COUNT(DISTINCT IFNULL(TRIM(uid), unknown))。面试时可答“我会先SELECT DISTINCT uid FROM table LIMIT 10查看原始值再决定是否清洗”。5.4 现象date_format(dayno,%Y%m%d)返回00000000原因dayno是2019-03-01字符串但DATE_FORMAT要求输入为DATE类型传入字符串会解析失败。解决用STR_TO_DATE(dayno, %Y-%m-%d)先转日期再DATE_FORMAT或直接REPLACE(dayno, -, )更高效。这是高频翻车点务必记住。5.5 现象variable初始化位置错误导致第一行计算异常原因变量初始化(SELECT sum:0, YSUM:0)必须与主查询在同一FROM子句中且置于主表之后。若写成SELECT ... FROM table, (SELECT sum:0)变量在每行都会重置。解决初始化子查询必须作为FROM中的最后一个表并用逗号连接。如题五FROM (...) b1, (SELECT sum:0, YSUM:0, year:NULL) b2。面试时可画图说明“我把变量初始化看作一个‘虚拟表’它必须和主数据表一起参与JOIN才能保证变量作用域覆盖全部行”。6. 面试现场终极技巧用「三步验证法」让代码一次通过不再反复调试我带过的12个实习生有9个在面试中输在“写完代码不敢交卷反复改却越改越错”。后来我逼他们养成一个机械式习惯任何SQL写完不急着执行先做三步验证。这招让我自己在腾讯面试时5分钟内写出题七的7个子问面试官当场说“不用看了逻辑很清晰”。现在把它拆给你。6.1 第一步字段血缘验证——确认每一列的来源与类型拿题二的次日留存为例在写SELECT前先手写一张表别名字段名来源表类型是否可能为NULL业务含义aday1REPLACE(dayno,-,)UNSIGNED否用户当日活跃日期数值bday2REPLACE(dayno,-,)UNSIGNED否用户次日活跃日期数值auiduserinfoVARCHAR否用户ID为什么有效一旦发现b.day2可能为NULL如用户没次日行为就知道CASE WHEN b.day2-a.day11必须用LEFT JOIN且COUNT(DISTINCT ...)会自动忽略NULL无需额外WHERE b.day2 IS NOT NULL。6.2 第二步中间结果抽样——用LIMIT 5截断大表肉眼核对逻辑在复杂嵌套中永远先验证最内层。例如题一先执行-- 抽样验证第一层聚合是否正确 SELECT userid, changjing, MIN(inttime) AS inttime FROM datafrog_test1 GROUP BY userid, changjing ORDER BY userid, inttime LIMIT 10;看输出是否符合预期用户1的1001场景最小inttime是14001002是1402。若看到1001:1402说明数据插入有误或MIN逻辑被干扰。6.3 第三步边界Case穷举——手算3个典型用户反向推导SQL应输出针对题七的“新用户首红包金额”我必手算用户00320190301注册is_new1当日20190301领红包add_money500→ 首红包500用户00720190302注册当日20190302领红包add_money300→ 首红包300用户00120190301is_new0老用户跳过。然后写SQL执行后只查这3个用户看结果是否完全匹配。不追求全量正确先确保逻辑骨架不垮。从那以后我每次写面试SQL都强制走一遍这三步血缘表→抽样查→手算验。哪怕只剩2分钟我也宁愿少写一个子问也要把已写的三步走完。因为面试官要的不是“写了多少”而是“写的是否可靠”。希望帮到你。本文还有配套的精品资源点击获取