最近身边有不少小伙伴在考虑跳槽的事情,免不了的会遇到一些SQL的面试题。
根据过往的经历和大伙儿的反馈,我们整理归纳了一些问题出来,以供大家参考。
这个问题有两个变形:(1)某APP用户活跃记录表active,有uid(用户id)、dt(活跃日期)字段,求出连续出勤3天及以上的用户数 (2)某APP用户活跃记录表active,有uid(用户id)、dt(活跃日期)字段,求每个用户的最大连续活跃天数 以上两个问题要求输出的数据不同,但都是要考察对于窗口函数lead、lag的掌握能力。第二个是在第一个问题基础上的延伸,也可以作为此类问题的通解。我们按照顺序分别来进行解答。首先,我们构建一个表以供测试说明,形如下图:
考虑到权限问题,这里就不单独新建hive表了,直接在with子句中union出来一个临时表:
(1)要知道一个用户连续活跃,那么肯定是要对他的所有活跃记录进行排序的。对于active这个表,每天每个活跃用户都会有一条记录,我们将每个用户的活跃记录按日期顺序排列,如果上一条记录的日期与本条记录的日期刚好相差1天,那么这两条记录就是连续的。反过来也就是说,连续出勤的记录之间,日期差值为1,推而广之,如果用户连续出勤了N天,那么这N条记录之中任意相邻的两条都是差1天,而这段记录开始的日期到结束的日期之间的差值则是N-1。如果某用户连续出勤了4天,那么从他第3天出勤的记录往前数第6条记录就刚好是他连续出勤的开始日期,所以,我们从每条记录往前数第6条的日期与本条记录的日期差值刚好是2天的话,就表示这期间没有间断。 我们利用lead获取每一条记录其往前第2条记录的日期,与本条记录日期求差值,如果这个差值等于2,就表明该用户连续出勤了3天及以上。具体计算sql如下所示,可以求得共有3个用户连续活跃了3天及以上。
这是从后往前数,同样的利用lag函数可以获取每条记录往后数第2条记录的日期,如果有差值等于2的记录,那么也可以表明用户连续出勤了3天及以上。(2)有了第一个问题作为铺垫,再来看第二个问题,相信大家应该更容易有思路了。在这个问题下,没有明确给出要计算连续多少天活跃,而是要求每个用户的最大连续活跃天数。如此一来,我们就不能直接错位相减了,而是需要清晰地找到每一段连续活跃的起止时间点。所以我们首先要判断每条记录与其相邻的记录之间是否连续,如果不连续则说明这条记录是某一段连续活跃的起点或者终点。
给所有记录标记上是否是断点之后,我们就可以为每条记录去匹配距离他最近的一次起点:
根据上图的子查询,我们就能知道每条活跃记录是从哪天开始连续活跃的了,然后求每个用户UID下所有活跃记录中与其起始日期最大的差值即可:
如此,就求得了所有用户的最大连续活跃天数了:
无论是第一种还是第二种,主要都是考察窗口函数的应用。熟练掌握窗口函数,并灵活运用,此类问题就不在话下了。
常见的SQL笔试选择题含答案 常见的SQL笔试选择题含答案 1.下列属于关系型数据库的是()(选择两项) 2.储蓄所有多个储户,储户在多个储户所存取款,储蓄所与储户之间是() 3.视图是一个“虚表”,视图的构造基于() 4.设有关系R(A,B,C,D)及其上的函数相关性集合F={B→A,BC→D},那么关系R最高是() 分析: 根据数据库三大范式的依赖性要求,从B,BC函数确定A和D这一点上,明显看出B,BC都有可能是主码. 若B是主码的话,仔细看会发现,F中竟然没有谁去函数确定C,这显然是说不通的,(因为C至少会被B这个主码函数确定); 若BC是主码,那么F中存在非主属性对候选码的部分依赖,不满足第二范式的要求,故为第一范式. 5.关于HQL与SQL,以下哪些说法正确?() B.HQL面向对象,而SQL操纵关系数据库 |
1、查询,查询学生"张三"的全部基本信息
2、查询,查询学生"张三"和”李四”的基本信息
3、查询,查询姓"张"学生的基本信息
4、查询,查询姓名中含有"四"字的学生的基本信息
5、查询,查询姓名长度为三个字,姓“李”,且最后一个字是“强”的全部学生信息。
6、查询,查询姓"张"或者姓”李”的学生的基本信息。
7、查询,查询姓"张"并且"所属省份"是"北京"的学生信息
9、查询,查询姓"张",但是"所属省份"不是"北京"的学生信息
10、查询,查询全部学生信息,并按照“性别”排序,性别相同的情况下按照“所属省份”排序,所属省份相同的情况下再按照“班级”排序
(多个排序条件,用逗号以此分开,先排第一个、再排第二个。。。。)
11、查询,查询现有学生都来自于哪些不同的省份
12、查询,查询没有填写成绩的学生的学号、课程号和成绩
13、查询,查询全部填写了成绩的学生的选修信息,并按照“成绩”从高到低进行排序
1、统计,统计共有多少个学生
2、统计,统计年龄大于20岁的学生有多少个
3、统计,统计入学时间在1998年至2000年的学生人数
4、统计,统计学号为"S001"的学生的平均成绩
5、统计,统计学号为"S001"的学生的总成绩