【SQL每日一练】day7：leetcode高频习题练习（完结篇）_开窗函数体育馆人流量的问题-CSDN博客

本文链接：https://blog.csdn.net/Liu_y_xin/article/details/141336030

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

题目要求：

查询出每个学生参加每一门科目测试的次数，结果按 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

题目二：确认率

题目要求：

用户的 确认率 是 '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

题目三：部门工资前三高的所有员工

题目要求：

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的不同工资中 排名前三 。

编写解决方案，找出每个部门中 收入高的员工 。

以 任意顺序 返回结果表。

表结构：

运行结果示例：

思路：

本题的难点在于如何“按照部门分类”找到每个部门前三高的工资。其实很多人都会钻牛角尖，想要找到部门id和对应的前三工资数额，再使用in来匹配结果（没错说的就是我）。但是换一个思路，我们可以找“工资比这些员工高的不到3个”,。这种情况下，处理起来就简单多了，我们只需要使用子查询即可。考虑到效率问题，我们可以用自连接改进一下，我们找到同一个部门中，表1比表2更高的工资数据，再连接表Department，获取对应部门id的部门名称，最后选出count(distinct e2.salary)的结果小于等于3的数据即可。

运行代码示例：

select 
    d.name as department, e1.name as employee, e1.salary as salary
from department d 
join employee e1 on d.id = e1.departmentid
join employee e2 on e1.departmentid = e2.departmentid and e1.salary<=e2.salary
group by 
    d.name, e1.name
having 
    count(distinct e2.salary)<=3

题目四：第N高的薪水

题目要求：

查询 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询结果应该为 null 。

表结构：

运行结果示例：

思路：

这道题本身并不难，因为只需要查询整体中排名第N的数据，并不需要根据分组查询每组中前N的数据，因此只要按照salary字段进行排序，再结合limit子句即可。

本题的坑点在于当遇到相同值的salary时，我们需要去除重复值，这个坑很容易跳出来，查询的时候使用distinct关键字就可以了。

另一个坑点在于limit子句的写法，我们知道limit子句中的两个参数分别是start起始值、 count数量。其中start的显示值是从start+1开始的。但limit子句中不能加入表达式（如N - 1）作为参数，因此我们需要额外定义一个参数，使其等于N - 1。

补充：MySQL中的函数：
CREATE FUNCTION function_name (parameter1 datatype, parameter2 datatype, ...)
RETURNS return_datatype
BEGIN
    -- 声明局部变量（可选）
    DECLARE var_name datatype;
    
    -- 函数体
    -- 执行一些操作
    -- 返回结果
    RETURN some_value;
END;
例如计算两个数字的和：
CREATE FUNCTION add_numbers (a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
    -- 返回两个数的和
    RETURN a + b;
END;

运行代码示例：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
            distinct salary
      FROM 
            employee
      ORDER BY 
            salary DESC
      LIMIT N, 1
  );
END

题目五：行程和用户

题目要求：

取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

编写解决方案找出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。

返回结果表中的数据 无顺序要求 。

表结构：

运行结果示例：

思路：

觉得本题有难度的可以再仔细看一下题目二：确认率这道题。其实这两道题是一样的，只不过本题的限定条件更多了一些。

我们先来看题目中的主体部分：计算取消率。在题目中已经给出计算方法的情况下，我们所需要做的就仅仅是翻译的工作。首先我们需要按照日期进行分组，被司机或乘客取消的非禁止用户生成的订单数量我们可以用sum（）+ if判断或者case语句来完成，判断订单状态是否为已完成，参考语句
case when status = 'completed' then 0 else 1 end
非禁止用户生成的订单总数则非常简单了，我们直接使用count（）函数即可。最后使用round（）函数保留两位小数。

接下来处理细节问题，题目要求限定订单时间为"2013-10-01" 至 "2013-10-03"，我们直接将其添加到where子句中即可，时间的判定方式有很多，这里不再一一列举。其次题目还限定了乘客和司机都是banned 为 No 的用户，这里我们之间使用一个子查询，限定乘客和司机的id不在子查询查询到的范围列表中即可。

运行代码示例：

select request_at as day, round(sum(case when status = 'completed' then 0 else 1 end) / count(*) ,2) as 'Cancellation Rate'
from Trips
where client_id not in (select users_id from Users where banned = 'Yes')
and driver_id not in (select users_id from Users where banned = 'Yes')
and request_at between '2013-10-01' and '2013-10-03'
group by request_at

题目六：体育馆的人流量

题目要求：

编写解决方案找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列 的结果表。

表结构：

运行结果示例：

思路：

这道题也是比较经典的一个题型，先来上一个比较简单容易理解的解法。

我们对表进行自连接，使得查询出的数据满足三表之间的id依次递增，且三表中对应数据的人数大于100的条件，参考代码如下：
SELECT distinct a.*
FROM stadium as a,stadium as b,stadium as c
where ((a.id = b.id-1 and b.id+1 = c.id) or
       (a.id-1 = b.id and a.id+1 = c.id) or
       (a.id-1 = c.id and c.id-1 = b.id))
  and (a.people>=100 and b.people>=100 and c.people>=100)
order by a.id;
接下来我们介绍一个更为通用的解法，我们首先筛选出所有满足人流量大于100的数据行，并使用id - 行号作为一个新列，再使用开窗函数统计每组d - 行号相同的列的数量，如果数量大于等于3，就说明有连续的3列及以上记录。

例如，我们首先筛选出所有满足人流量大于100的数据行，并使用id - 行号作为一个新列（step），得到结果如下：

可以看到，id连续的行，其step值也是相同的。参考代码如下：
select s2.id -row_number () over ( ORDER BY id ) step,s2.id,s2.visit_date，s2.people 
FROM Stadium s2 
WHERE s2.people >= 100 
ORDER BY s2.id 
接下来我们需要做的工作就是统计出step相同的行的数量，也就是根据step进行分组统计，这里我们需要用到开窗函数，得到如下的结果，不难看出，此时count大于等于3的数据就是我们想要获取到的数据：

简化版代码如下：
SELECT count(*) over ( PARTITION BY t1.step ) count, t1.id,t1.step
FROM (
	select s2.id -row_number () over ( ORDER BY id ) step,s2.id
    FROM Stadium s2 
    WHERE s2.people >= 100 
    ORDER BY s2.id 
) t1 
最后我们在主查询里筛选出count>= 3的数据即可。

运行代码示例：

SELECT t2.id,t2.visit_date,t2.people 
from (
	SELECT
		count(*) over ( PARTITION BY t1.step ) count,
		t1.id,
		t1.visit_date,
		t1.people 
	FROM
		(
		SELECT
			s2.id -row_number () over ( ORDER BY s2.id ) step,
			s2.id,
			s2.visit_date,
			s2.people 
		FROM
			Stadium s2 
		WHERE
			s2.people >= 100 
		ORDER BY
			s2.id 
		) t1 
	) t2 
WHERE t2.count >= 3
order by t2.id

练习汇总

【SQL每日一练】day7：leetcode高频习题练习（完结篇）

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

题目二：确认率

题目三：部门工资前三高的所有员工

题目四：第N高的薪水

题目五：行程和用户

题目六：体育馆的人流量

练习汇总

（一）理论学习&基础巩固阶段

（二）理论实践阶段（含牛客网和鱼皮大佬SQL进阶之路习题）

（三）力扣高频SQL50题带练

（四）力扣简单难度题目带练

（五）力扣中等困难难度题目带练

（六）大厂笔面真题