一道简单的sql语句题


全文共4371个字,22张图,预计阅读时间25分钟


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


问题引出


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


640?wx_fmt=png

用户表userinfo

640?wx_fmt=jpeg

订单表orderinfo


错误思路


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


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



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


640?wx_fmt=jpeg


这个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语句,就当我们以为大功告成之时,没想到结果只输出了一条,而不是所有的用户的每一条记录:


640?wx_fmt=jpeg


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


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


结果正确,输出如下:


640?wx_fmt=jpeg


随后我们加入group by 语句:


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


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


640?wx_fmt=jpeg


剩下的两不就不用解释了,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


结果输出为:

640?wx_fmt=jpeg


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


640?wx_fmt=jpeg


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


640?wx_fmt=jpeg


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


看似正确的思路



那么解决这个问题的正确姿势是什么呢?在融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)


结果输出如我们所预料:


640?wx_fmt=jpeg


可以看到,子查询返回的每个用户的当天最早的订单时间,然后外层查询用一个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)


结果输出如下:


640?wx_fmt=jpeg


咦,结果输出好像跟我们之前没有什么差别?这是为什么呢?细心的你可能发现了,我们用了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


结果正确输出:

640?wx_fmt=jpeg


再思考


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


640?wx_fmt=jpeg


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


640?wx_fmt=jpeg


真正的正确思路

三表链接


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


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


结果如下:

640?wx_fmt=jpeg

使用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排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)
比如下面的例子中,我们按照部门进行分组,然后按照薪水进行降序排序,最后一列表示排序后的组内排名。


640?wx_fmt=jpeg


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()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)


640?wx_fmt=jpeg


dence_rank()函数


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


640?wx_fmt=jpeg


好了,回到我们的题目,在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笔都可以计算出,功能十分强大。


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


输出结果正确如下:

640?wx_fmt=jpeg


上面的代码中用到了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


运行效果如下:

640?wx_fmt=jpeg

上面的效果得以实现,得益于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


输出结果为:


640?wx_fmt=png


有了上面的知识储备之后,我们之前实现的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


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

640?wx_fmt=jpeg


总结


学习是一个不断循环迭代的过程,这道题从最初的在滴滴面试中幼稚的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


原文链接:https://mp.weixin.qq.com/s?__biz=MzI1MzY0MzE4Mg==&mid=2247483773&idx=1&sn=d2578386819d66147f09209ecc834436&chksm=e9d011bcdea798aa21d65f935567eb1c66511d2fc9925b51ccea6a4985a21d85d95c01a513b3&scene=21#wechat_redirect


查阅更为简洁方便的分类文章以及最新的课程、产品信息,请移步至全新呈现的“LeadAI学院官网”:

www.leadai.org


请关注人工智能LeadAI公众号,查看更多专业文章

640?wx_fmt=jpeg

大家都在看

640.png?


LSTM模型在问答系统中的应用

基于TensorFlow的神经网络解决用户流失概览问题

最全常见算法工程师面试题目整理(一)

最全常见算法工程师面试题目整理(二)

TensorFlow从1到2 | 第三章 深度学习革命的开端:卷积神经网络

装饰器 | Python高级编程

今天不如来复习下Python基础



  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL是高级的非过程化编程语言,是沟通数据库服务器和客户端的重要工具,允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以,具有完全不同底层结构的不同数据库系统,可以使用相同的SQL语言作为数据输入与管理的SQL接口。 它以记录集合作为操作对象,所有SQL语句接受集合作为输入,返回集合作为输出,这种集合特性允许一条SQL语句的输出作为另一条SQL语句的输入,所以SQL语句可以嵌套,这使它具有极大的灵活性和强大的功能,在多数情况下,在其他语言中需要一大段程序实现的功能只需要一个SQL语句就可以达到目的,这也意味着用SQL语言可以出非常复杂的语句。    结构化查询语言(Structured Query Language)最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言,它的前身是SQUARE语言。SQL语言结构简洁,功能强大,简单易学,所以自从IBM公司1981年推出以来,SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、DB2、Informix、SQL Server这些大型的数据库管理系统,还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统,都支持SQL语言作为查询语言。    美国国家标准局(ANSI)与国际标准化组织(ISO)已经制定了SQL标准。ANSI是一个美国工业和商业集团组织,负责开发美国的商务和通讯标准。ANSI同时也是ISO和International Electrotechnical Commission(IEC)的成员之一。ANSI 发布与国际标准组织相应的美国标准。1992年,ISO和IEC发布了SQL国际标准,称为SQL-92。ANSI随之发布的相应标准是ANSI SQL-92。ANSI SQL-92有时被称为ANSI SQL。尽管不同的关系数据库使用的SQL版本有一些差异,但大多数都遵循 ANSI SQL 标准。SQL Server使用ANSI SQL-92的扩展集,称为T-SQL,其遵循ANSI制定的 SQL-92标准。    SQL语言包含4个部分:    数据定义语言(DDL),例如:CREATE、DROP、ALTER等语句。    数据操作语言(DML),例如:INSERT(插入)、UPDATE(修改)、DELETE(删除)语句。    数据查询语言(DQL),例如:SELECT语句。    数据控制语言(DCL),例如:GRANT、REVOKE、COMMIT、ROLLBACK等语句。    SQL语言包括三种主要程序设计语言类别的语句:数据定义语言(DDL),数据操作语言(DML)及数据控制语言(DCL)。
第二天 1、查询客户表,统计每个机构2000年之前开户数、2000~2005开户数(含头不含尾)、2005~2010开户数(含头不含尾)、2010之后开户数 展示字段:机构号、2000年之前开户数、2000~2005年开户数、2005~2010年开户数、2010年之后开户数 2、查询客户表,按年份统计,每年、每个机构开户数占全年开户数的占比 展示字段:年份、机构号、开户数、开户占比百分比(百分比) 3、统计所有客户的客户号、存款账户数、2011.12.31日的存款余额、2011.12存款月日均、贷款账户数、2011.12.31日的贷款余额、2011.12贷款月日均 备注:null置为0 第三天 1、统计所有客户的2011.12.31日的存款余额、存款比上日余额、存款比上月余额、、存款比上年余额 备注:存款比上日余额 = 2011.12.31日的存款余额-2011.12.30日的存款余额 存款比上月余额 = 2011.12.31日的存款余额-2011.11.30日的存款余额 存款比上年余额 = 2011.12.31日的存款余额-2010.12.31日的存款余额 只有2011.12.31这个日期可以死,其他日期要通过2011.12.31这个日期来生成。 2、统计所有2011年存款年日均大于100的客户号、客户名称、存款账户数、2011年年日均 第四天 1、统计所有2011年存款年日均和2011年贷款年日均都大于100的客户号、存款账户数、2011年存款年日均、贷款账户数、2011年贷款年日均 2、统计所有客户的客户号、 存款标志(有存款账户的客户置为1、没存款账户的客户置为0)、 贷款标志(有贷款借据的客户置为1、没贷款借据的客户置为0)、 存款质量分类(2011年存款年日均>=10000置为优质、2011年存款年日均>=1000<10000 置为良好、2011年存款年日均=10000置为优质、2011年贷款年日均>=1000<10000 置为良好、2011年贷款年日均<1000置为普通)、 2011年贷款年日均 第五天 1、根据《事件表.xlsx》来建表,然后将excel中的数据导入到目标表中 2、根据客户表、存款信息表、事件表,统计每个客户2017年的客户号、 交易账户数(客户下有多少个账户有交易就是多少)、 当年有交易的天数(如果2017年有5天有过交易,则有交易天数为5)、 当年有交易总月数(如果2017的1、3、5月有交易,则有交易总月数为3)、 最大的月交易总金额(按月统计交易金额,存放最大的月交易金额)、 最大月交易金额的月份(按月统计交易金额,存放交易金额最大的月份)、 年总交易金额、 年交易金额排名(按客户排名,如果总交易金额为0,则不参与排名,排名置为9999)、 年总手续费、 年总手续费排名(按客户排名,如果总手续费为0,则不参与排名,排名置为9999)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值