集锦"/>
数据分析|SQL面试题集锦
.html
1.用一条SQL 语句 查询出每门课都大于80 分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)
B: select name from table group by name having min(fenshu)>80
写在前面
SQL可谓是数据分析必备技能之一了,面试中也是高频出现,初学SQL可以阅读《SQL必知必会》
全书通俗易懂,看完之后可以到牛客网和LeetCode刷一些SQL题巩固巩固。
数据库SQL实战_牛客网www.nowcoder.com
题库 - 力扣 (LeetCode)leetcode-cn.com
不过面试中的SQL大多都是结合部门业务,这里将我自己经历过的和之前看到的一些面试题收集整理过来,前期顺序可能比较乱,之后陆陆续续会归类分析。
目录(2020-05-18)
- 用户签到问题
- 销售情况问题
- sql去重操作
- 相邻间隔问题
- count(1)、count(*)和count(列名)的区别
切入正题
1. 用户签到问题
<题目>
给定一张用户签到表user_attendence,表中包含三个字段,分别是用户ID:【user_id】,日期:【date】,是否签到:【is_sign_in】,0否1是。
<问题1> 计算截至当前,每个用户已经连续签到的天数:
要求输出用户ID【user_id】和连续签到天数【recent_continuous_days】
<问题2> 计算有史以来用户最大连续签到天数:
要求输出用户ID【user_id】和最大连续签到天数【max_continuous_days】
<解题思路>
针对问题1,这里有一种非常巧妙的解法:只需要利用Max和datediff函数,也就是说只要找到用户最近一次没有签到的日期,计算和当前日期的差值即可。
针对问题2,可以将是否签到转换为一条0-1字符串序列,用0做split切割,计算切出来的1序列组中的最大长度。也可以运用窗口函数row_number,给所有签到记录为1的列排序号,这里也有一种很巧妙的思想,就用每行的签到日期减去序号,如果签到日期是连续的话,求得的值则是相同值,一旦日期不连续,将会求得一个新的相同值,这样的话,可以统计每个值的数量,进而判定最长签到天数。
举个栗子,下图为用户1在4月20日到4月27日的签到记录,其中4月23日没有签到,可以发现差值为19和20,其中20出现次数最多,有4次,说明该用户最大连续签到天数为4天。
不过有一点值得注意的是,求得的【值】必须唯一,上图中的数字显然不唯一。这里可以用日期去当作这个【差值】,毕竟日期具有唯一性,可以考虑运用MySQL中的DATE_SUB函数。
如果对窗口函数不了解的话,可以看我这个文章:
狗哥:数据分析|SQL窗口函数zhuanlan.zhihu.com
<答案>
# 求截止当前的连续签到天数
SELECT user_id, DATEDIFF('2020-04-27', max_date) AS recent_continuous_days
FROM (SELECT user_id, MAX(date) AS max_dateFROM user_attendenceWHERE is_sign_in = 0GROUP BY user_id) AS b# 求有史以来的最大连续签到天数(窗口函数)
SELECT b.user_id, MAX(b.continues_day) as max_continuous_days
FROM (SELECT a.user_id, DATE_SUB(a.date, INTERVAL a.rn DAY) AS difference, COUNT(*) AS continues_dayFROM (SELECT user_id, date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS rnFROM user_attendenceWHERE is_sign_in = 1) AS aGROUP BY a.user_id, difference) AS b
GROUP BY b.user_id
2. 销售计算问题
<题目及问题>
<解答>
这里用的办法比较复杂,主要涉及到了row_number、if和round函数,仅作参考
SELECT a.t_year, ROUND(SUM(IF(a.t_region = '华南', a.t_money, 0)), 2) as '华南总收入',ROUND(SUM(IF(a.t_region = '华北', a.t_money, 0)), 2) as '华北总收入',ROUND(SUM(IF(a.t_region = '华南', a.t_money, 0)) / SUM(cicy_hn), 2) as '华南平均收入',ROUND(SUM(IF(a.t_region = '华北', a.t_money, 0)) / SUM(cicy_hb), 2) as '华北平均收入',if(a.ranknumber = 1, a.t_city, 0) AS 销售第一FROM (SELECT t_year, t_region,t_city,t_money, if(t_region = '华南',COUNT(t_region), 0) AS cicy_hn,if(t_region = '华北',COUNT(t_region), 0) AS cicy_hb,ROW_NUMBER() OVER (PARTITION BY t_year ORDER BY t_money DESC) AS ranknumberFROM sql2GROUP BY t_year, t_city) AS aGROUP BY t_year
输出如下:
3. 去重问题
请移步
狗哥:数据分析|记一“道”难忘的SQL面试题...zhuanlan.zhihu.com
4. 相邻间隔问题
请移步
狗哥:数据分析|SQL面试题—相邻间隔问题zhuanlan.zhihu.com
5. count(1)、count(*)和count(列名)的区别
- 从执行结果来看
- count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL
- count(1)包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL
- count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计
- 从执行效率来看
- 如果列为主键,count(列名)效率优于count(1)
- 如果列不为主键,count(1)效率优于count(列名)
- 如果表中存在主键,count(主键列名)效率最优
- 如果表中只有一列,则count(*)效率最优
- 如果表有多列,且不存在主键,则count(1)效率优于count(*)
6. 某一时间点最大在线人数问题
<题目>
给定一张用户进入直播间事件表user_live,表中包含四个字段,分别是直播间id:【live_id】,用户ID:【user_id】,进入时间:【login_time】,退出时间:【logout_time】
<问题1> 计算每分钟最大在线人数
<解题思路>
假如我们用一个变量user_cnt记录当前在线人数,那么有用户进入则+1,有用户退出则-1。其实也就是只有在用户进入或者退出时,才会有人数变化。所以可以先对原数据做一些格式上的处理,将进入退出时间统称为事件时间,增加一个时间类型(1进入、-1退出),如下所示:
live_id | user_id | event_time | event_type |
---|---|---|---|
1001 | 0001 | 2020-11-25 12:00:00 | 1 |
1001 | 0001 | 2020-11-25 12:30:00 | -1 |
1001 | 0002 | 2020-11-25 12:30:30 | 1 |
这样的话,就可以在每条记录后运用sum() over()窗口函数记录此时的用户数,大概思路如下
selectsum(event_type) over(order by event_time) as sum_user --排序后第一行到本行的和from(selectorder_id,unix_timestamp(login_time) as event_time,1 as event_typefromuser_livewhere‘限定条件’union allselecorder_id,unix_timestamp(logout_time) as event_time,-1 as event_typefromuser_livewhere‘限定条件’)a
最后在根据需求对时间戳处理,求得某时间段的最大最小在线人数
更多推荐
数据分析|SQL面试题集锦
发布评论