数据分析人员需要掌握 SQL 到什么程度？3 个常考题目刷一刷

在数据类岗位招聘过程中，经常会考察求职者的 SQL 能力，这里整理了 3 个常考的 SQL 数据分析题，按照由简单到复杂排序，一起来测试一下你掌握了么？

PS：以下 SQL 代码在 MySQL8.0 及其以上版本运行。

题目 1：找出每个部门工资第二高的员工

现有一张公司员工信息表 employee，表中包含如下 4 个字段。

• employee_id（员工 ID）：VARCHAR。

• employee_name（员工姓名）：VARCHAR。

• employee_salary（员工薪资）：INT。

• department（员工所属部门 ID）：VARCHAR。

employee 表的数据如下表所示。

还有一张部门信息表 department，表中包含如下两个字段。

• department_id（部门 ID）：VARCHAR。

• department_name（部门名称）：VARCHAR。

department 表的数据如下表所示。

问题：查询每个部门薪资第二高的员工信息。

输出内容包括：

• employee_id（员工 ID）

• employee_name（员工姓名）

• employee_salary（员工薪资）

• department_id（员工所属部门名称）

结果样例如下图所示。

可供参考的解题思路：使用窗口函数根据部门 ID 分组，在组内按照员工薪资降序排列并记为 ranking，然后将该处理后的表和部门信息表进行内连接，从而把部门名称关联进来，最后在连接后的表上使用 ranking=2 作为薪资第二高的条件进行 WHERE 筛选，选择需要的列，即可得到结果。

涉及知识点：窗口函数、子查询、多表连接。

SELECT  a.employee_id       ,a.employee_name       ,a.employee_salary       ,b.department_idFROM (    SELECT  *           ,RANK() OVER (PARTITION BY department ORDER BY employee_salary DESC) AS ranking    FROM employee ) AS aINNER JOIN department AS bON a.department = b.department_idWHERE a.ranking = 2;

题目 2：网站登录时间间隔统计

现有一张网站登录情况表 login_info，该表记录了所有用户的网站登录信息，包含如下两个字段。

• user_id（用户 ID）：VARCHAR。

• login_time（用户登录日期）：DATE。

login_info 表的数据如下表所示。

问题：计算每个用户登录日期间隔小于 5 天的次数。

输出内容包括：

• user_id（用户 ID）

• num（用户登录日期间隔小于 5 天的次数）

结果样例如下图所示。

可供参考的解题思路：本题考查 LEAD()函数在处理时间间隔问题上的使用方法，观察内层的查询部分，使用 LEAD()函数在原有的 login_time 字段的基础上创造一列新的时间字段（即该用户下一次登录日期），内层查询代码如下：

SELECT  user_id        ,login_time        ,LEAD(login_time,1) OVER (PARTITION BY user_id ORDER BY login_time) AS next_login_timeFROM login_info;

查询结果如下图所示。

在上图中可以发现，经过 LEAD()函数处理后，数据会根据 user_id 字段分组后按照 login_time 字段排序。经过内层的处理后，只需在外层筛选出 next_login_time 与 login_time 字段的日期差小于 5 天的数据，即最终统计的目标数据，这里使用了 TIMESTAMPDIFF(DAY, login_time, next_login_time)计算日期差，最后分组聚合统计不同 user_id 的记录个数，即每个用户登录日期间隔小于 5 天的次数。

涉及知识点：窗口函数、子查询、分组聚合、时间函数。

问题：查询每天仅使用手机端的用户、仅使用网页端的用户和同时使用网页端和手机端（both）的不同用户人数和总购物金额，并且即使某天某渠道没有用户的购买信息，也需要展示。

输出内容包括：

purchase_date（日期）

channel（购买渠道）

sum_amount（总购买金额）

total_users（不同用户人数）

结果样例如下图所示。

可供参考的解题思路：根据用户 ID 和日期进行分组，通过统计用户在各购买渠道购物的记录个数来判断某用户在某日期购物时采用的访问方式（web、app 和 both）。其中，web 和 app 可以通过一个 SELECT 语句查询，both 则可以通过另一个 SELECT 语句查询。将两部分使用 UNION 连接在一起，并将以上部分作为子查询内部，在子查询外部统计不同购买日期、购买渠道的总购买金额和总购买用户。

本部分输出结果如下图所示。

上述部分似乎已经完成了本题要求，但仔细观察就会发现，题目要求即使某天某渠道没有用户的购买信息，也需要展示。而想要展示更全的信息，则考虑使用最全的信息（所有日期和 3 个渠道的笛卡尔积）与刚查询出的结果数据表进行 LEFT JOIN 连接，即可得到两张表根据日期和渠道进行连接的结果。

涉及知识点：UNION、分组聚合、数据去重。

这些题目你做出了么？

本文题目内容摘自最新出版的《SQL 数据分析：从基础破冰到面试题解》，该书包含大量练习题（共 48 个），并附带数据导入和参考解析代码，更多题目见下图：

常见疑问和解答

1. 没有基础可以学吗？

可以，本书从环境搭建开始，从基础入门到进阶，然后通过题目实战提升 SQL 能力，是一本关于 SQL 数据分析的实战手册。

2. 本书对比市面上大部分 SQL 书籍的特色是什么？

将数据分析挖掘中所需的 SQL 能力抽出来专门写，不会涉及很多不常用的功能语法，由浅入深，并配套大量练习题（可作为求职笔试面试的练习题），每个练习题都配有数据导入、解题思路和参考答案。练习题会结合当前数据分析很多场景需求来编制，例如“活跃用户分析”、“连续登录用户分析”、“社区团购行为分析”、“商品销量同环比”。

3. 本书适合数据分析相关岗位求职备考准备么？

很适合，本书展现了数据分析工作的日常内容，给出了数据分析岗位的工作技能要求，然后讲述了数据分析笔试与面试中对 SQL 的考查知识点。通过 3 种难度的题目练习，能提升求职能力并达到初级数据分析挖掘岗位对 SQL 的能力要求。

4. 本书的适合什么样的读者？

• 数据分析与数据开发求职者和从业者

• 计算机科学与技术、统计学、数学、大数据、人工智能、数据科学相关专业的师生

• 对数据分析和 SQL 感兴趣人群

• 转行做数据分析与数据开发的人员

福利

大家如果经常买书都会知道，新书刚出版上市是没有什么折扣的。

本次给大家申请到了全网最低价福利，原价 89 元，现在只要 49 元包邮。

大家可以扫描下方二维码或者点击阅读原文直接购买。