![](https://img-blog.csdnimg.cn/20201014180756927.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
SQL面试题
浊酒南街
纵使疾风起,人生不言弃。
展开
-
SQL面试题挑战15:sql实现分钟级的趋势图
在Hive或者ODPS中,怎么用sql实现分钟级的趋势图?比如从交易表中,如何统计0点到每分钟的交易趋势图?原表:trade_A(trade_id,pay_time(格式是2020-08-05 10:30:28),pay_gmv)。希望用sql实现分钟级的0点到当前分钟的GMV。结果表:result_A(minute_rn(分钟顺序),pay_gmv_td(每分钟的交易额,都是0点到当前分钟的累加值))。原创 2024-02-07 15:23:52 · 929 阅读 · 0 评论 -
SQL面试题挑战14:每年的在校人数
year表示学生入学年度,num表示对应年度录取学生人数,stu_len表示录取学生的学制;说明:例如录取年度2018学制是3年,表示该批学生在校年份为2018。由于需要计算每年的在校人数,所以先要造出连续的年份。然后与源表进行关联,关联条件保证年份在入学年份和结束年份之间即可。2020、2020-2021,在算每年的在校人数时,2018/2019/2020/2021年份都需要算上。根据以上示例计算出每年的在校人数。原创 2023-12-29 16:18:57 · 762 阅读 · 0 评论 -
SQL面试题挑战13:分组topN
下面是某个班级的成绩表,需要筛选出每个科目前2名的学生信息。如果分数一样,名次是并列的,后面的同学名次就不连续。比如有2个同学是第一名,那么下一个同学的名次就是第3名,呈现1,1,3的名次排列。非常典型的分组topN的问题,是面试经常被问到的。直接使用开窗函数排名即可,注意下row_number、rank、dense_rank三个开窗函数不同场景下的使用。原创 2023-12-29 16:01:35 · 783 阅读 · 0 评论 -
SQL面试题挑战12:计算部门的平均工资
现在要计算每个部门的平均工资(工资和/员工数),但是要去掉部门的最高工资和最低工资(如果一个部门最高或最低工资有并列的,去掉一个最高的和一个最低的)后,计算部门的平均工资。使用开窗函数找出该部门的最高工资和最低工资,并按最高到最低和最低到最高分别给排名,最后排除掉满足工资要求且排名在最前面的员工即可。原创 2023-12-29 10:30:29 · 537 阅读 · 0 评论 -
SQL面试题挑战11:访问会话切割
如下为某电商公司用户访问网站的数据,包括用户id和访问时间两个字段。现有如下规则:如果某个用户的连续的访问记录时间间隔小于60秒,则属于同一个会话,现在需要计算每个用户有多少个会话。比如A用户在第1秒,60秒,200秒,230秒有三次访问记录,则该用户有2个会话,其中第一个会话是第1秒和第60秒的记录,第二个会话是第200秒和230秒的记录。先按用户分组、时间排序后取每行数据的前一行的时间,然后判断当前行的时间与前一行时间的差值,看是否在给定的范围内,然后再做开窗累加就可以得到每个用户不同的会话编号了。原创 2023-12-28 19:50:52 · 1939 阅读 · 2 评论 -
SQL面试题挑战10:累计占比
现在有一张每个年份的每个部门的收入表。现在需要算每个部门的收入占同类型部门的收入的占比和当年整个公司的收入占比。要求一条SQL计算出来。比如研发部和产品部属于同类型的,都是产研;财务部和人事部都属于职能。考察sum() over 开窗函数的使用。原创 2023-12-28 11:17:26 · 942 阅读 · 0 评论 -
SQL面试题挑战09:有效值追溯
其中,expr 表示要获取最后一个值的列名或表达式,ignore_nulls 表示是否忽略 NULL 值,如果设置为 true,则 LAST_VALUE() 函数会忽略 NULL 值并返回最后一个非 NULL 值,否则返回 NULL。其中,expr 表示要获取第一个值的列名或表达式,PARTITION BY 子句用于指定分区字段,ORDER BY 子句用于指定排序规则,ROWS 子句用于指定窗口范围。在 Hive 中,LAST_VALUE() 是一种窗口函数,用于获取指定列的最后一个值。原创 2023-12-27 09:55:48 · 403 阅读 · 0 评论 -
SQL面试题挑战08:补全缺失日的月销售累计
如果是每个员工在每天都有销售记录,那么直接开窗就可以计算出来当月截至到每天的累计销售额。现在由于销售记录有缺失,他虽然在某天没有销售记录,但是他是有当月累计销售额的,且当月累计销售额与前一天的累计销售额一样,这种依然需要统计出来。所以我们首先考虑将每个人每天的销售记录补齐,当天没有销售记录的那么销售额给0,然后就可以开窗计算当月截至到每天的累计销售额了。在 Hive 中,REPEAT 函数用于将指定字符串重复多次,返回一个新的字符串。其中,str 表示要重复的字符串,n 表示要重复的次数。原创 2023-12-26 20:32:49 · 553 阅读 · 0 评论 -
SQL面试题挑战07:间隔连续问题(连续的升级版)
下面是某游戏公司记录的用户每日登录数据, 计算每个用户最大的连续登录天数,定义连续登录时可以间隔一天。举例:如果一个用户在 1,3,5,6,9 登录了游戏,则视为连续 6 天登录。这种解法是比较常见的,很多场景都可以这样使用。还有比如计算用户的会话数,当两次会话时间超过1分钟时就算做不同的会话,也可以这样做。这是个连续问题的升级版,当满足某种要求时我们也是算作连续的,所以不能使用传统的连续编号,然后做差值的解法了。补充:hive登录相关传送门。原创 2023-12-25 14:32:00 · 551 阅读 · 0 评论 -
SQL面试题挑战06:互相关注的人
解答思路二:找到互相关注的人的规律,当他们是互相关注时,那么将from_user和to_user其中一个顺序调换位置后,from_user和to_user就一定会出现两条数据(源表提前已经去重),所有出现两条数据的人就是有互相关注的。现在有一张relation表,里面只有两个字段:from_user和to_user,代表关注关系从from指向to,即from_user关注了to_user。解答思路一:使用自关联即可,这种方式简单也最易理解。适合数据量不是很大的情况,因为会导致数据膨胀。原创 2023-12-24 16:08:29 · 692 阅读 · 0 评论 -
SQL面试题挑战05:找出恶意购买用户
比如count(distinct stu_id) over(partition by class_id order by score range between 30 and current row)就表示按class_id分组,按照score升序,以当前行为锚点,在[score-30,score]的分数范围内计算stu_id的数量。与rows不同的是,这个每一行的开窗的范围是固定的,但行数是不固定的。针对这个例子,窗口大小就是半小时,然后按每条数据进行滑动,在窗口内判断该条数据对应的用户是否是恶意用户。原创 2023-12-22 19:37:29 · 468 阅读 · 0 评论 -
SQL面试题挑战04:找出使用相同ip的用户
现在有一张用户登陆日志表,该表包括user_id,ip,log_time三个字段,现在需要找出共同使用ip数量超过3个(含)的所有用户对。比如下面的示例数据,101和102用户共同使用的ip为4个,101和103用户共同使用的ip为3个,102和103用户共同使用的ip为3个。问题的关键点是使用自连接,先按用户和ip去重之后进行自关联。因为如果公共使用ip达到3个及以上的话,那么同一个用户对至少会出现3条数据,筛选一下就行。原创 2023-12-21 19:08:32 · 657 阅读 · 0 评论 -
SQL面试题挑战03:奖金瓜分问题(拼多多)
这里给出一种思考逻辑:假设奖金池的初始总奖金为n,那么第一名分到的奖金为n/2,第二名分到奖金n/4,第三名分到的奖金为n/8,依次类推第x名分到的奖金为n/2^x,然后计算即可。瓜分奖金的规则如下:按照score从高到低依次瓜分,每个人都能分走当前奖金池里面剩余奖金的一半,当奖金池里面剩余的奖金少于250时(不含),则停止瓜分奖金。在活动大促中,有玩游戏瓜分奖金环节。现有奖金池为10000元,代表奖金池中的初始额度。现在需要查询出所有分到奖金的user_id和其对应的奖金。原创 2023-12-20 20:06:25 · 730 阅读 · 0 评论 -
SQL面试题挑战02:同时最大在线人数问题
这是非常经典的一个面试题,不管大厂小厂都有问到过。解题思路也比较固定:就是用1代表开播(此时用开播时间),-1代表关播(此时用关播时间),可以理解1代表主播开播加入增1,-1代表主播关播离开减1,然后开窗可以计算出到每个时间点时有多少主播同时在线,最后求最大值即可。问题:如下为某直播平台各主播的开播及关播时间明细数据,现在需要计算出该平台最高峰期同时在线的主播人数。原创 2023-12-20 11:39:05 · 934 阅读 · 0 评论 -
SQL面试题挑战01:打折日期交叉问题
一般来说,rows 和 range 窗口帧都可以用于定义窗口函数的计算范围,但是它们有一些不同的特点:rows 窗口帧是基于行的,它使用一组相对于当前行的行号来定义窗口函数的计算范围。range 窗口帧是基于值的,它使用一组相对于当前行的数值范围来定义窗口函数的计算范围。例如,计算累计和、计算百分比等。需要注意的是,对于一些特殊的窗口函数,可能只能使用 rows 窗口帧,例如计算排名、计算移动平均等。因此,在使用 range 窗口帧时,需要根据具体的需求和窗口函数的特性选择合适的窗口帧类型。原创 2023-12-19 20:23:38 · 805 阅读 · 0 评论