一道简单的sql语句题

这是很早之前面的,第一次面数据分析的面试,当时还傻乎乎的以为数据分析和数据挖掘是一回事呢。结果才发现,数据分析岗位大多注重的是数据库的能力,比如sql语句的考察,hive的考察,以及一些运营思维的考察,所以第一次面试就很悲剧啦,不过题目还是很有代表性的。其他的不写了,这里只分享一个关于sql的题目。

1、问题引出

现在有两个数据表,一个数据表记录司机的信息,比如司机id,司机姓名,司机注册时间等等,一个数据表记录一天的订单情况,比如订单ID,订单司机id,订单时间。写sql语句,返回每个司机今天最早的一笔订单。两个数据表如下图所示:

用户表userinfo

订单表orderinfo

2、错误思路

好了,模拟的数据我们准备完毕了,接下来我们就要开动脑筋解决这个问题了,想了半天,脑子里蹦出这么一个想法,这不很简单么,我们先把两个表链接起来,然后按照用户进行一个分组,然后对数据排序,最后输出第一个记录不就好了,所以,我们写了如下的sql语句:

select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid order by orderinfo.ordertime limit 1

信心满满地运行,发现 报错了!

这个only_full_group_by是什么鬼哟。百度了一下,这好像是mysql5.7版本的新特性,按照网上的方法,执行如下的sql语句就可以取消这个模式:

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

再次执行我们上面的sql语句,就当我们以为大功告成之时,没想到结果只输出了一条,而不是所有的用户的每一条记录:

我们来探究一下原因,是什么出现了问题,我们一步步分析,首先我们执行表链接语句:

select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid 

结果正确,输出如下:

随后我们加入group by 语句:

select name,sex,register,ordertime from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid

在groupby语句的时候,已经是返回每个用户的一条记录了:

剩下的两不就不用解释了,order by将上面返回的三条记录进行一个排序,最后limit 1返回了一条结果。所以我们找到了问题所在,就是这个group by的问题,它只能返回每一组的一行。

你可能会想,既然groupby只能返回一行,我们返回min(ordertime)不就好了:

select name,sex,register,min(ordertime) from userinfo,orderinfo where userinfo.id = orderinfo.userid group by orderinfo.userid

结果输出为:

哇,结果是对的哎。结果真是对的么?如果我们在orderinfo里面加入了新的一列,乘客姓名,将orderinfo表变为如下的形式:

实在没有多余的脑细胞去想复杂的名字了,不过这已经足以让我们来解决问题了。继续运行上面的代码,结果如下:

结果并不对,张三的第一个用户应该是二号,细心的你可能已经发现问题了,还是group by的问题,它返回的是链接之后分组的第一条记录,min(ordertime)相当于是不在表中的一个新加入的字段,它的值通过min函数计算而来,所以会出现上面的结果。

3、看似正确的思路

那么解决这个问题的正确姿势是什么呢?在融360面试的时候,我被问到了类似的问题,吸取在滴滴面试的教训,我们用一个子查询来解决这个问题:

select name,sex,register,ordertime,orderuser from userinfo,orderinfo where userinfo.id = orderinfo.userid and orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid)

结果输出如我们所预料:

可以看到,子查询返回的每个用户的当天最早的订单时间,然后外层查询用一个in,返回订单时间在最早订单时间列表里的记录。

当然,你可能会说,有的司机可能没有订单,但我们也想要返回这个司机的信息,比如我在userinfo表里添加一条龙六的信息,这时候,用如上的语句就不行了,因为上面相当于内链接,我们这时候要考虑左外链接,语句变为:

select name,sex,register,ordertime,orderuser from userinfo left outer join orderinfo on userinfo.id = orderinfo.userid where orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid)

结果输出如下:

咦,结果输出好像跟我们之前没有什么差别?这是为什么呢?细心的你可能发现了,我们用了where子句限定了ordertime的取值范围,所以不会出现那些没有订单信息的用户,所以我们还要对语句作如下修改,让ordertime可以为Null值:

select name,sex,register,ordertime,orderuser from userinfo left outer join orderinfo on userinfo.id = orderinfo.userid where orderinfo.ordertime in (select min(ordertime) from orderinfo group by userid) or orderinfo.ordertime is Null

结果正确输出:

4、再思考

就当我信心满满以为这样就结束并把帖子发出去的时候,群里的专家提出了疑问,如果有不同司机在同一时间接了订单怎么办?记录可能如下:

这时候我们就会发现,运行上面的代码,某些用户会返回两条数据,因为两个用户在同一时间接到了订单,对于其中一个用户来说,是当天的第一笔,对于另一个用户来说,不是当天的第一笔,这样就会出现问题了:

5、真正的正确思路

5.1 三表链接

大佬提供了两种解决方案,一种是把子查询的结果作为一个新表,然后利用三表链接:

select name,sex,register,ordertime,orderuser from userinfo,orderinfo,(select userid,min(ordertime) as ordertime2 from orderinfo group by userid) as orderinfo2 where userinfo.id = orderinfo.userid and orderinfo.ordertime = orderinfo2.ordertime2 and orderinfo.userid = orderinfo2.userid

结果如下:

5.2 使用over函数(mysql不支持)

上面的结果是正确的,不过太麻烦了吧,于是我们还有第二种解决方案,使用row_number()/rank()/dense_rank() over(partition by),这个在mysql中并没有实现,在oracle或者sql server中是有实现的。

不过我们还是要来看一下这一语法的基本用法:

over()函数:

over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。 其参数:over(partition by columnname1 order by columnname2) 含义:按columname1指定的字段进行分组,并按照 例如:employees表中,有两个部门的记录:department_id =10和20 select department_id,rank() over(partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名,在部门20中进行薪水排名。

row_number()函数

row_number()函数用于返回根据over函数分组排序结果的编号。例如row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的) 比如下面的例子中,我们按照部门进行分组,然后按照薪水进行降序排序,最后一列表示排序后的组内排名。

row_number()在我们这道题目的背景下是适用的,不过在其他的场景,比如按照每个部分进行分组,再按照工人的薪资进行降序排序,如果有两个人的薪资相同,这两个人的row_number值不会相同,这种情况下row_number()函数就不再适用,我们可以考虑rank()或者dense_rank()函数与over函数结合使用。

不过,这也引出了row_number()函数另一个比较有趣的作用,根据某几列进行去重:假设表TAB中有a,b,c三列,可以使用下列语句删除a,b,c都相同的重复行。

DELETE FROM (select year,QUARTER,RESULTS,row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO FROM SALE )   WHERE ROW_NO>1  

rank()函数

rank() over()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)

dense_rank()函数

dense_rank() over()是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 .

好了,回到我们的题目,在oracle等其他数据库中,我们可以用下面的语法进行解决,但很遗憾,mysql不行(下面的语句没有真正测试过,因为在mysql环境中无法执行,如果有错误,欢迎大家指正!)

select name,sex,register,ordertime,orderuser from userinfo,(select *,row_number() over(PARTITION by userid order by ordertime) as tn from orderinfo) as t1 where userinfo.id = t1.userid and t1.tn = 1

上面语句中的row_number()完全可以换做rank()或者dense_rank()。同时,使用上面这种语法,不仅仅是最早的一笔订单,最早的5笔,10笔都可以计算出,功能十分强大。

5.3 mysql模拟实现rank_over

mysql没有row_number()/rank()/dense_rank() over(partition by)这样高级的sql语法,不过我们可以通过编程的方式来模拟实现类似的功能,下面给出了具体的代码:

select u.name,u.sex,u.register,o.ordertime,o.orderuser from userinfo as u,(select orderinfo.*,if(@userid = orderinfo.userid,@rank:=@rank+1,@rank:=1) as rank,@userid:=orderinfo.userid from orderinfo,(select @rank:=0,@userid:=NULL) as b order by orderinfo.userid,orderinfo.ordertime) as o where u.id = o.userid and o.rank = 1

输出结果正确如下:

上面的代码中用到了mysql变量的知识,首先,我们大体讲一下mysql中变量的相关知识。

mysql变量

mysql中变量不用事前申明,在用的时候直接用“@变量名”使用就可以了。 第一种用法:set @num=1; 或set @num:=1; //这里要使用变量来保存数据,直接使用@num变量 第二种用法:select @num:=1; 或 select @num:=字段名 from 表名 where …… 注意上面两种赋值符号,使用set时可以用“=”或“:=”,但是使用select时必须用“:=赋值”

使用变量添加行号

我们可以设置一个初始行号,接下来在 select语句中不断改变行号的值即可:

set @i = 0;
select   (@i:=@i+1)  as   i,userinfo.*   from   userinfo

如果使用一句话,我们可以将设置初始值的过程放在from后面:

select   (@i:=@i+1)  as   i,userinfo.*   from   userinfo,(select   @i:=0)   as  it 

运行效果如下:

上面的效果得以实现,得益于mysql中变量在select被循环赋值的特性,即每取出一行,i的值都会变化一次,而在sql server中,i不会被循环赋值,所有列的值都是最后一次的i值。

if语句

mysql中if语句的语法如下:

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。 比如下面的例子,我们根据司机的注册时间划分司机类型:

select name,if(register > '2017-08-05','A','B') as type from userinfo

输出结果为:

有了上面的知识储备之后,我们之前实现的mysql语句也就不难理解了,我们首先在内部生成了一个新表o,新表o对司机进行了分组,并按照接单时间先后进行了排序:

select orderinfo.*,if(@userid = orderinfo.userid,@rank:=@rank+1,@rank:=1) as rank,@userid:=orderinfo.userid from orderinfo,(select @rank:=0,@userid:=NULL) as b

可以看到,内部查询的输出如下,接下来就是简单的两表链接和筛选了:

6、总结

学习是一个不断循环迭代的过程,这道题从最初的在滴滴面试中幼稚的group by 想法,到融360面试时自认为正确的解法,再到被大佬质疑从而继续思考,最终到基本get到解题的正确姿势,经历了一系列迭代的过程。

希望我们在以后的学习过程中,能够不断的举一反三,将知识一步步的掌握扎实。

参考文章:

ROW_NUMBER() OVER()函数用法:http://www.cnblogs.com/alsf/p/6344197.html mysql实现oracle分析函数功能 over:http://blog.csdn.net/mengtianyalll/article/details/45767603 MySql 申明变量以及赋值: http://www.cnblogs.com/qixuejia/archive/2010/12/21/1913203.html mysql数据库 实现类似标记序号的伪列: http://blog.csdn.net/ystyaoshengting/article/details/6904627 MySQL的if,case语句使用总结: http://outofmemory.cn/code-snippet/1149/MySQL-if-case-statement-usage-summary

本文分享自微信公众号 - 小小挖掘机(wAIsjwj)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-08-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据结构与算法

二项式定理

其实二项式定理也就一句话:$(x + y)^n = \sum_{i = 0}^n C_{n}^i x^{n - i} y^{i}$

17010
来自专栏iKcamp

翻译连载 |《你不知道的JS》姊妹篇 |《JavaScript 轻量级函数式编程》- 第 5 章:减少副作用

原文地址:Functional-Light-JS 原文作者:Kyle Simpson-《You-Dont-Know-JS》作者 第 5 章:减少副作用 在第 2...

29070
来自专栏程序员的诗和远方

20181013_ARTS_week16

这题没好好审题,题目中说不能增加其它空间,以及要在原数组中改,没注意最后只要前 n 位是无重复的就可以了。

11130
来自专栏哈雷彗星撞地球

设计模式简介

设计模式(Design pattern)是一套被反复使用的、多数人知晓的、经过分类编目的、代码设计经验的总结。 设计模式代表了最佳的实践,通常被有经验的面向对...

10340
来自专栏陈纪庚

一道面试题引起的思考

今天在认真干(划)活(水)的时候,看到群里有人发了一道头条的面试题,就顺便看了一下,发现挺有意思的,就决定分享给大家,并且给出我的解决方案和思考过程。

10230
来自专栏阿杜的世界

《重构》阅读笔记-代码的坏味道

开发者必须通过实践培养自己的经验和直觉,培养出自己的判断力:学会判断一个类内有多少个实例变量算是太大、学会判断一个函数内有多少行代码才算太长。

8220
来自专栏hanlp学习笔记

HanLP自然语言处理包初步安装与使用

        HanLP是由一系列模型与算法组成的Java工具包,目标是促进自然语言处理在生产环境中的应用。HanLP具备功能完善、性能高效、架构清晰、语料时...

11810
来自专栏Java帮帮-微信公众号-技术文章全总结

Java基础-day05-超市收银系统案例题

Java基础-day05-超市收银系统案例题 案例描述 将超市购物小票案例中,键盘录入部分封装为方法。 将switch语句完成的三个分支代码逻辑封装为3个方法 ...

71540
来自专栏写代码的海盗

SEO是件贼有意思的事情 golang入坑系列

这两天迷上了SEO。真心看不起百度的竞价排名,但作为一个商业网站,赚钱是一件无可厚非的事情。只做活雷锋,没有大金主是做不长的。做完功课后,发现百度和google...

34350
来自专栏牛客网

深信服一面C++

Linux中创建共享内存的方式?共享内存中起始地址是不是按照页的大小对齐?创建共享内存的时候物理页一定分配吗?惰性空间分配的实现方式?

17620

扫码关注云+社区

领取腾讯云代金券