SQL 面试经典 & 业务实战(二)

SQL 刷题&面试 专栏收录该内容
4 篇文章 0 订阅

更多商业数据分析案例(原理,完整代码,数据集)、统计学、SQL、网络爬虫详见公众号 " 数据分析与商业实践 "

引言

  非常常见的面试题,也是平时练手的经典题,把知识点串起来的同时也很好的联系了业务实际。直接将代码或思路背诵记忆同样可在相似场景中发挥作用。

业务背景

企业/单位 绩效检查类需求

  • 检索各项工作都达标的员工的信息(n 项工作都达到了基本指标,指标可以是分数,等级等)
  • 检索工作项项目 A 完成得比项目 B 好的员工的个人信息,即找到员工们各自的优势,若景常发现这一部分员工更擅长项目 A,而另一部分员工更擅长 B(完成 B 的质量比 A
    高),则可适当调整未来的工作计划与安排。
  • 检索每个项目都做得不错的员工,即每个项目如 A,B,C 都达到了目标分数 n 或目标等级x,可更好的准备年终奖。

准备 & 用到的知识

本次实战用到的数据虽然看起来比较 low,老生常谈的学生成绩查询,但只要将列名稍作更改,并发挥一定的想象力,就不难发现与业务背景所说的看似高大上的需求有异曲同工之妙。

  • 三张表
    在这里插入图片描述
  • SQL 编辑器(笔者 dbForge Studio)
  • SQL 基本知识(SELECT, GROUP BY, AS, 多表联结 join)
      本次实战将会疯狂的使用 join,所以整洁的代码格式对实现业务需求功不可没。

题目要求 & 效果前后

  • 用一条SQL语句查询每门课程都是大于80分的学生基本信息(id & name)
    在这里插入图片描述
  • 查询“语文”课程比“数学”课程成绩高的学生的信息以及课程分数
    在这里插入图片描述
  • 查询平均成绩大于65分的学生的id和姓名以及平均成绩
    在这里插入图片描述

流程分析 & 实现步骤

需求1:用一条SQL语句查询每门课程都是大于80分的学生基本信息(id & name)

  id,学生名,课程名,分别在不同的表格,首先连接起来是必须的,连接的方式有多种,这里选择 INNER JOIN,因为需要自带去重效果,没有说保留哪边舍弃哪边一说(LEFT/RIGHT JOIN),毕竟有可能出现脏数据(某学生只录入了 sid,而没有录入学生姓名),总的来说,INNER JOIN 是“严格 join”

  -- 查询每门课程都是大于 80 分的同学
SELECT
  s.Sid
  /* 这种格式写代码比较整洁
     1. 可以直接注释掉一行而不影响其他行
     2. 可在一行末尾添加注释而不影响其他行
     3. 可提醒自己不要漏掉括号*/
  , s.Sname
  , sc.score
FROM
  sc
INNER JOIN s
  ON s.Sid = sc.Sid
INNER JOIN c
  ON c.Cid = sc.Cid
;

在这里插入图片描述
  问题拆解:每门课都高于 80 分,关键在于转化表述方式,脑海翻译成 SQL 代码,如下展示多个 “翻译” 版本。

  • 每门课高于 80即平均分高于 80?尝试求这几门课的平均分 – 否决:可能有高分科目将 79 分的科目拉高
  • 每门课都高于 80,最差的一科也要高于 80?最差 – MIN – 可以值得一试
    下面将代码排版缩减(占空间少,但当然不够上一段代码那么清晰简洁)
SELECT s.Sid, s.Sname
FROM sc
  INNER JOIN s ON s.Sid = sc.Sid
  INNER JOIN c ON c.Cid = sc.Cid
GROUP BY s.Sname HAVING MIN(sc.score) > 80;

  这里需要 group by 一下,因为学生名字会有重复,最后我们显示的是不重复的学生名字,代码 MIN(sc.score) > 80 不难理解,但为什么是放在 group by 后面,接在 having 后呢?having 放在 group by 后,where 在 group by 前已经是不成文的规定了。解释:若代码 having 及后面改成在 group by 前,则无法运行

SELECT s.Sid, s.Sname
FROM sc
  INNER JOIN s ON s.Sid = sc.Sid
  INNER JOIN c ON c.Cid = sc.Cid
  WHERE MIN(sc.score) > 80 
  -- 如果上一行是最原始的 WHERE sc.score > 80, 
      -- 那当然不会报错,添加了聚合函数,就意味着你希望的筛选动作是在分组后执行
GROUP BY s.Sname; -- 哪怕这一行去掉,也还是会报错

  则含义为筛选最低分科目是大于 80 分的(首先中文语义就已经不通顺了)。再者,SQL 语法中 group by 存在时,若想使用带聚合函数的筛选条件,应在 group by 后使用 HAVING ,而不是在 group by 前就着急的使用 WHERE + 聚合函数。原因:WHERE 是对 初始表 的行进行筛选,而我们现在是需要对 分组之后 的数据进行筛选(使用了聚合函数),就已经不是最原始的行了,所以再使用 WHERE 就自然会报错。如果对这句话不理解的朋友,可以在脑海中想一下 这段 SQL 代码的阅读顺序,从(from) 某表中读入数据 --> 拼接(join)其他表后 --> 按照某个要求分组(group by) --> 最后以 select 的形式呈现出来。我们希望的 MIN(sc.score) > 80 这个筛选条件是对每一组的同学都使用一下。

SELECT s.Sid, s.Sname
FROM sc
  INNER JOIN s ON s.Sid = sc.Sid
  INNER JOIN c ON c.Cid = sc.Cid
GROUP BY s.Sname HAVING MIN(sc.score) > 80;

效果图
在这里插入图片描述
  如果觉得上面的一段话理解起来有难度,则可以参考一下下面三种不同的解释方式

  • 聚合函数不能放在 where 后面!where sc.score > 80 表示普通的过滤,添加了聚合函数变成 where MIN(sc.score > 80 后,便表示筛选,“ 筛选 ” 与 “ 过滤 ” 这两个词的意思并不一样)
  • 以下段代码为例(从代码阅读顺序不同来切入),where 后不添加聚合函数时
SELECT s.Sid, s.Sname
FROM sc
  INNER JOIN s ON s.Sid = sc.Sid
  INNER JOIN c ON c.Cid = sc.Cid
  WHERE sc.score > 80 
GROUP BY s.Sname; 

阅读顺序为 从(from) sc 表中读入数据,根据要求 on 内连接(inner join) s,c 两表,对连接后的表(相对于 group by 前来说还是 “ 原来的表 ” 进行 where 条件筛选,对筛选完后的表进行分组 group by) – 可以运行

  • 以下段代码为例,where 后添加聚合函数时
SELECT s.Sid, s.Sname
FROM sc
  INNER JOIN s ON s.Sid = sc.Sid
  INNER JOIN c ON c.Cid = sc.Cid
  WHERE MIN(sc.score) > 80 
GROUP BY s.Sname; 

  聚集函数的别称为列函数,是基于整列数据进行计算的,而 where 子句则是对数据行进行过滤(过滤 和 筛选 两个词的意义不同)的,在筛选过程中依赖 “ 基于已经筛选完毕的数据得出的计算结果 ” 是一种悖论,这是行不通的。更简单地说,因为聚集函数要对全列数据时行计算,因而使用它的前提是:结果集已经确定!而where子句还处于“确定”结果集的过程中,因而不能使用聚集函数。

需求2:-- 查询“语文”课程比“数学”课程成绩高的学生的信息以及课程分数

  对于该需求,我们不必催毛求疵一步完美实现,毕竟需求的实现并不是一蹴而就的。

问题分析
  • 会不会有的同学并没有参加语文和数学这两门考试,或者缺考了其中一门
  • 打印数据,这里使用 inner join 去掉了那些可能存在名字或者学号录入失误的学生信息
SELECT 
    s.Sid
    , s.Sname
    , c.Cname
    , sc.score
FROM sc
  INNER JOIN s 
      ON s.Sid = sc.Sid
  INNER JOIN c 
      ON c.Cid = sc.Cid
 ;

在这里插入图片描述
  发现的确有同学存在缺考这两门的情况。那接下来就好办了,先简单的分批打印一下数据,语文成绩表一个,数学成绩表一个,先看看概览。
在这里插入图片描述
  惊喜的发现似乎只有 吴兰 和 郑竹 这两位同学比较突出,如果能将他们去掉就好了,当我们将两个表的信息都打印出来以后,自然就能想到如果能在任意一个表上多添加一列另一科目的成绩列,再去掉一下突出者(inner join 的天然作用),最后在过滤一下数据(where),就完美了。这时我们不妨在思路中这样构思
在这里插入图片描述
  关键是这两个表的 “ 建表代码 ” 又已经确定,所以只需要中间来个 inner join 就行了,如果按照整洁的老司机写法,代码长度看起来会比较大,如下:

-- 查询“语文”课程比“数学”课程成绩高的学生的信息以及课程分数
SELECT
  chinese_table.Sid
  , chinese_table.Sname
  , chinese_table.chinese
  , math_table.math
FROM
  -- 仅含语文成绩列的表
  (SELECT
    s.Sid
    , s.Sname
    , c.Cname
    , sc.score AS chinese
  FROM
    sc
    INNER JOIN s
      ON s.Sid = sc.Sid
    INNER JOIN c
      ON c.Cid = sc.Cid
    WHERE c.Cname = '语文') AS chinese_table

INNER JOIN  -- 大“INNER JOIN”
  
  -- 仅含数学成绩列的表
  (SELECT
    s.Sid
    , s.Sname -- select 的内容可以适当减小,这里都是按照最详细的来列
    , c.Cname
    , sc.score AS math
  FROM
    sc
    INNER JOIN s
      ON s.Sid = sc.Sid
    INNER JOIN c
      ON c.Cid = sc.Cid
    WHERE c.Cname = '数学') AS math_table

  -- 两个小表内连接,以学生名为连接标准
ON chinese_table.Sname = math_table.Sname
  -- 呼应需求,语文成绩 比 数学成绩高
WHERE chinese_table.chinese > math_table.math
;

大段代码的分框阅读技巧跟思路图一样
瘦版代码

-- 查询“语文”课程比“数学”课程成绩高的学生的信息以及课程分数
SELECT chinese_table.Sid, chinese_table.Sname
, chinese_table.chinese, math_table.math
FROM
  (SELECT s.Sid, s.Sname, c.Cname, sc.score AS chinese
  FROM sc
    INNER JOIN s ON s.Sid = sc.Sid
    INNER JOIN c ON c.Cid = sc.Cid
    WHERE c.Cname = '语文') AS chinese_table
INNER JOIN
  (SELECT
    s.Sid, s.Sname, c.Cname, sc.score AS math
  FROM sc
    INNER JOIN s ON s.Sid = sc.Sid
    INNER JOIN c ON c.Cid = sc.Cid
    WHERE c.Cname = '数学') AS math_table
ON chinese_table.Sname = math_table.Sname
WHERE chinese_table.chinese > math_table.math
;

在这里插入图片描述

需求3:查询平均成绩大于65分的学生的id和姓名以及平均成绩

  懂得了 having 与 where 的区别,这个需求就是分分钟的事情了。

SELECT
  s.Sid
  , s.Sname
  , AVG(sc.score) AS avg_score
FROM
  sc
INNER JOIN s
  ON s.Sid = sc.Sid
INNER JOIN c
  ON c.Cid = sc.Cid
GROUP BY s.Sid, s.Sname
 HAVING AVG(sc.score) > 65
;

在这里插入图片描述
难易结合的三个需求


模拟面试

  • 现场写代码(重点)
  • group by 与 having 的区别,使用 where 时有什么注意点(由浅入深的讲解,注意细节)

后记

  SQL 作为数据分析师的必备技能,有许多非常精彩的技巧和用法等着我们去掌握(后续会出),另外,拿到高手的代码时,按照简洁版或文中的老司机写法将代码拆解,虽然看起来会增加代码长度,但整洁性会给你带来意想不到的守护哦。然后在其源代码上添加一些框框(截图工具 Snipaste),也可以自己添加一些括号,别名。这样看到大段代码也不会慌了,加油!

  • 1
    点赞
  • 1
    评论
  • 4
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值