专栏首页猴子聊数据分析滴滴2020年面试题:如何找出最小的N个数?
原创

滴滴2020年面试题:如何找出最小的N个数?

【题目】

“学生表”里记录了学生的学号、入学时间等信息。“成绩表”里是学生选课成绩的信息。两个表中的学号一一对应。(滴滴2020年面试题)

现在需要:

1. 筛选出2017年入学的“计算机”专业年龄最小的3位同学名单(姓名、年龄)

2. 统计每个班同学各科成绩平均分大于80分的人数和人数占比

【解题思路】

问题1:筛选出2017年入学的“计算机”专业年龄最小的3位同学名单(姓名、年龄)

一看是不是有点懵?

别着急,我们用逻辑树分析方法,把这个复杂问题拆解为一个一个可以解决的简单问题:

1)筛选条件:入学时间是2017,专业是计算机

2)最小的3位同学名单(姓名、年龄)

1.先找出符合要求的同学

筛选条件:入学时间是2017,专业是计算机。year(日期)函数用来获取日期的年份

select 姓名,年龄from 学生表where 专业='计算机' and year(入学时间)=2017;

2.最小的3位同学名单(姓名、年龄)

先使用order by对年龄排序(从小到大,也就是升序asc),然后使用limit输出前3行数据,就是年龄最小的3位。

select 姓名,年龄from 学生表where 专业='计算机' and year(入学时间)=2017order by 年龄 asclimit 3;

问题2:统计每个班同学各科成绩平均分大于80分的人数和人数占比

一看是不是有点懵?

别着急,我们用逻辑树分析方法,把这个复杂问题拆解为一个一个可以解决的简单问题:

(1)每位同学的平均成绩

(2)平均分大于80分的人数

(3)平均分大于80分的人数占比

(4)输出结果是班级,平均分大于80分的人数,平均分大于80分的人数占比

1. 每位同学的平均成绩

涉及到“每个”的时候,就要想到《猴子 从零学会sql》里的分组汇总了。按学号分组(group by),然后求平均成绩(avg函数),把所得结果看做临时表。

select 学号,avg(分数) as 平均成绩from 成绩表group by 学号;

2.平均成绩>80的人数

可以使用使用sum函数和case表达式来统计平均成绩大于80的人数

select sum(case when 平均成绩>80 then 1                      else 0 end) as 人数from 临时表;

下图是case和sum结合起来统计人数的sql过程:

3.平均成绩大于80分的人数占比

平均成绩>80的人数占比 =(平均成绩>80的人数)/ 总人数

总人数是表行数:count(学号)。所以平均成绩>80的人数占比就是:

select sum(case when 平均成绩>80 then 1                     else 0 end)/count(学号) as 人数占比from 临时表;

4. 输出结果是班级、人数、人数占比

班级在“学生表”中,这涉及到需要将“学生表”和“临时表”2张表,需要用到多表联结。联结两表的是“学号”,如下:

因为要保留“学生表”班级的全部数据,所以使用左联结。

select a.班级from 学生表 as a left join 临时表 as bon a.学号=b.学号group by 班级;

题目要求是输出班级、人数、人数占比,所以在上面sql中加入输出的列名:

select a.班级,人数,人数占比from 学生表 as a left join 临时表 as bon a.学号=b.学号group by 班级;

select子句中的人数、人数占比在前面第1步、第2步中已经得到,套入这个sql语句中就是:

最终sql如下:

select a.班级,sum(case when b.平均成绩>80 then 1else 0 end) as 人数,sum(case when b.平均成绩>80 then 1else 0 end)/count(a.学号) as 人数占比from 学生表 as a left join(select 学号,avg(分数) as 平均成绩from 成绩表group by 学号) as bon a.学号=b.学号group by 班级

【本题考点】

1.使用逻辑树分析方法将复杂问题变成简单问题的能力

2.当遇到“每个”问题的时候,要想到用分组汇总

3.查询最小n个数据的问题:先排序(order by),然后使用limit取出前n行数据

4.遇到有筛选条件的统计数量问题时,使用case表达式筛选出符合条件的行为1,否则为0。然后用汇总函数(sum)对case表达式输出列求和。

有筛选条件的统计数量问题的万能模板

select sum(case when <判断表达式> then 1       else 0end) as 数量from 信息表;

【举一反三】

1.查询最小/最大的N个数据的问题

某网站有购买记录表,找出消费最大的2名顾客,输出顾客ID和消费金额

select 顾客ID,消费金额from 购买记录表order by 消费金额 desclimit 2;

2. 分组汇总问题

某网站有顾客表和消费表,请统计每个城市的顾客平均消费在1000元以上的人数,输出城市,人数

select a.城市,sum(case when b.平均消费>1000 then 1else 0 end) as 人数from 顾客表 as a left join(select ID,avg(消费金额) as 平均消费from 消费表group by ID) as bon a.ID=b.IDgroup by 城市;

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 图解面试题:经典50题

    翻译成大白话:计算每个学号不及格分数个数,筛选出大于2个的学号并找出姓名,平均成绩,思路如图:

    猴子聊数据分析
  • 图解SQL联结:右联结

    右联结,会将右侧表中的数据全部取出来。下面图片中用文氏图画出了右联结,是红圈中的部分。

    猴子聊数据分析
  • 怎样才能写出高质量的SQL语句?

    我们公司的数据量非常大,需要的不仅仅是提取数据,要了解SQL方案优化的。一般在写SQL时需要注意哪些问题,可以提高查询的效率?

    猴子聊数据分析
  • PHP从数据库提取并显示数据的典型代码

    PHP从数据库提取并显示数据的典型代码如下: if ($res=mysql_query($sql)) { //数据库查询执行成功 ...

    似水的流年
  • PHP从数据库提取并显示数据的典型代码

    似水的流年
  • 2018.11月Go优质开源项目

    项目描述:Build cross-platform modern desktop apps in Go + HTML5

    李海彬
  • 百度调整架构从两年一次到一年三次,战时状态的必然

    百度今天宣布进行架构大调整,距离上次调整刚刚过去4个月,距离上上次调整刚好过去1年。如此频繁地进行架构调整在百度历史上十分罕见。在2015年之前的一次调整是20...

    罗超频道
  • jquery的基本选择器

    关于基本选择器包括 “*” ,“.class”,"element","#id","selector1 selementN" "*" 选择器,可以找到文档中的所...

    用户1197315
  • Lua性能分析

    Lua性能分析本来有一些现成的工具,比如LuaProfile,不幸的是这货不支持luajit,另外LuaStudio虽然挺好用但是是收费的。 比较不爽,刚好L...

    owent
  • jquery基本选择器

    关于基本选择器包括  “*” ,“.class”,"element","#id","selector1  selementN" "*" 选择器,可以找到文档中的...

    用户1197315

扫码关注云+社区

领取腾讯云代金券