【SQL高频练习带刷】day9：补充练习题

题目一：学生们参与各科测试的次数

题目要求：

查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

表结构：

 运行结果示例：

思路：

        在表连接操作中，有一种连接叫做笛卡尔积，但是大多数同学都知道，笛卡尔积连接会造成很大的时间和空间花费，因此很少使用，也很少有场景适用。但是本题就用到了笛卡尔积。

        在三张表中，我们需要统计考试表中不同学生id和科目的组合数量，并从学生表中获取到对应学生id的姓名。看似非常简单，只需要使用外连接+分组统计就可以，但是结果表需包含所有学生和所有科目（即便测试次数为0）。也就是我们还需要列举出所有的科目信息。但是科目表中只有一列数据，拿什么来进行关联呢？这时就用到了笛卡尔积。我们需要将学生表和科目表通过笛卡尔积获得不同学生和科目的组合，再使用左外连接，统计考试表中的数目，如果有考试表中不存在的学生、科目组合（即该学生未参加此科目的测试），也会由于左外连接的原因被保留下来，不会被忽略。最后我们再按照指定字段进行排序即可。

运行代码示例：

select s.student_id,s.student_name,sb.subject_name,count(e.subject_name) as attended_exams
from Students s
join Subjects sb -- join等价于inner join，不用关联条件的join等价于cross join
left join Examinations e 
on s.student_id = e.student_id and sb.subject_name = e.subject_name
group by student_id,subject_name
order by student_id,subject_name

题目二：至少有5名直接下属的经理

题目要求：

编写一个解决方案，找出至少有五个直接下属的经理。

以 任意顺序 返回结果表。

表结构：

 运行结果示例：

思路：

 本题只有一张数据表，难度也不大。题目要求寻找至少有5名直接下属的经理，观察表结构，可以发现我们需要寻找“id的数量大于等于5的managerId”。需要注意，我们的分组条件是managerId而不是id。同样，虽然样例中输出的结果只有一个，但是理论上我们找到的managerId结果集不仅仅是一个，因此我们要使用in子句来找到所有id = managerId的经理名称。

运行代码示例：

select name from Employee where id in (
    select managerId from Employee 
    group by managerId
    having count(id) >= 5
)

题目三：确认率

题目要求：

用户的 确认率 是 'confirmed' 消息的数量除以请求的确认消息的总数。没有请求任何确认消息的用户的确认率为 0 。确认率四舍五入到 小数点后两位 。

编写一个SQL查询来查找每个用户的 确认率 。

以 任意顺序 返回结果表。

表结构：

 运行结果示例：

思路：

        遇到看似复杂的题目不要慌，我们先将题目拆解一步步看。

        首先，我们主要要计算的是confirmed 消息的数量除以请求的确认消息的总数，那么我们只要将文字转换成SQL语句就可以了，状态为confirmed的消息我们可以用if语句或者case语句进行判断，统计它的数量，如果 action = 'confirmed'计为1，否则计为0（注意这里要用的函数是sum()而不是count（）），而求的确认消息的总数我们直接分组，用count（）函数统计每组的数量就可以。整理成SQL语句就是sum(case when action = 'confirmed' then 1 else 0 end ) / count( action)。

        第二步我们来完善一些细节。首先确认率要求四舍五入到小数点后两位 ，因此我们要把上一步中得到的结果放到round（）函数中进行处理，保留两位小数。其次对于没有请求任何确认消息的用户，我们要将用户表作为主表与Confirmations 表进行外连接，获取到所有的用户，并使用ifnull（）函数来处理那些为空的数据。

        以上我们对本题的分析就完成了，只需要整理SQL语句即可。

运行代码示例：

select user_id,ifnull(round(sum(case when action = 'confirmed' then 1 else 0 end ) / count( action) ,2),0) as confirmation_rate
from Signups
left join Confirmations
using(user_id)
group by user_id

题目四：餐饮营业额变化增长

题目要求：

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。

计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。

结果按 visited_on 升序排序。

表结构：

 运行结果示例：

思路：

这道题看似很难，实际上思路并不复杂，用窗口函数就可以解决。但需要注意的是我们要计算的是每7天的均值，如果使用窗口函数，它会自动计算范围内的所有数据：

因此我们需要使用where子句做一个判断，手动设置从第7天开始计算。

运行代码示例：

SELECT DISTINCT visited_on,
       sum_amount AS amount, 
       ROUND(sum_amount/7, 2) AS average_amount
FROM (
    SELECT visited_on, SUM(amount) OVER ( ORDER BY visited_on RANGE interval 6 day preceding  ) AS sum_amount 
    FROM Customer) t
WHERE DATEDIFF(visited_on, (SELECT MIN(visited_on) FROM Customer)) >= 6