一个SQL,6种写法。
↑↑↑关注后"星标"简说Python
人人都可以简单入门Python、爬虫、数据分析 简说Python推荐 来源:小数志
作者:luanhz
题意理解不难,无非就是查找排名为N的记录,但常用SQL的都知道这里存在一个歧义,即排名是否存在相同和是否跳级的问题。经测试,这里的排名是"致密"排名(dense_rank),即同薪同名且不跳级那种。例如对于薪水3000/2000/2000/1000排名之后为1、2、2、3,若取N=3,则返回结果1000。另外,题目形式是一个自定义函数,但本质仍是一个SQL查询。
面对这样的一道题,你能迅速想到几种SQL写法呢?
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 SET N = N - 1;
4 RETURN (
5 SELECT
6 salary
7 FROM
8 employee
9 GROUP BY
10 salary
11 ORDER BY
12 salary DESC
13 LIMIT 1 OFFSET N
14 );
15END
由于只进行单表查询+单字段排序,对salary字段建立索引时查询效率会非常高。
既然是排名为N,那么就意味着大于等于目标薪水的记录数为N,更准确的说这里是去重后的记录数为N。基于此想法,很快可以写出相应SQL:
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT e.salary
6 FROM
7 employee e
8 WHERE
9 (SELECT count(DISTINCT salary) FROM employee WHERE salary>=e.salary) = N
10 );
11END
个人认为,SQL最强大也最有代表性的操作在于多表关联,这个问题自然也可以用连接查询。MySQL中主要支持join、left join和right join三种连接方式。具体到这一题,可以选用任何一种。例如,如果限定连接条件是薪水大于等于(含等于),则可直接用join实现两表自连接,然后对另一个计数即可;而如果限定连接条件是薪水大于(不含等于),则必须用left join,避免N取特殊值1时出现关联结果为空而查询失败的情况。具体来说:
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT e1.salary
6 FROM
7 employee e1 JOIN employee e2 ON e1.salary <= e2.salary
8 GROUP BY
9 e1.salary
10 HAVING
11 count(DISTINCT e2.salary) = N
12 );
13END
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT e1.salary
6 FROM
7 employee e1 LEFT JOIN employee e2 ON e1.salary < e2.salary
8 GROUP BY
9 e1.salary
10 HAVING
11 count(DISTINCT e2.salary) = N-1
12 );
13END
用join连接方式实现的SQL,都能用笛卡尔积实现,且一般来说笛卡尔效率要略低于连接查询,但很多情况下MySQL优化器会将笛卡尔积形式的查询优化成join形式,此时二者执行过程是一致的。可以很容易将解法3中的形式改成笛卡尔积形式的写法。
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT e1.salary
6 FROM
7 employee e1, employee e2
8 WHERE
9 e1.salary <= e2.salary
10 GROUP BY
11 e1.salary
12 HAVING
13 count(DISTINCT e2.salary) = N
14 );
15END
前面已经介绍了4种解法,对比来看:解法2-4中都存在两表关联的问题,而解法1因为仅涉及到单表排序,所以效率相比之下更高;另一方面,解法2-4功能更具扩展性:例如可以很容易实现分组查询排名第N高,而这是简单的limit+offset写法所不能实现的。那么,有没有既能拓展到分组查询、同时又具有单表查询的高效呢?答案是肯定的,例如下面的自定义变量写法,通过设定一个自变量,获取每个薪水的排名信息,然后筛选排名为N的薪水即可。
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT salary
6 FROM
7 (SELECT
8 salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:= salary
9 FROM
10 employee, (SELECT @r:=0, @p:=NULL)init
11 ORDER BY
12 salary DESC) tmp
13 WHERE rnk = N
14 );
15END
实际上,解法5中的自定义变量查询写法在MySQL8.0以后有相应的窗口函数可以实现。窗口函数在MySQL8.0版本首次引进,而其他很多SQL语言则早已内置。具体而言,对于本题获取"致密"排名的薪水,用到的窗口函数就是dense_rank()。
1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3 RETURN (
4 SELECT
5 DISTINCT salary
6 FROM
7 (SELECT
8 salary, dense_rank() over(ORDER BY salary DESC) AS rnk
9 FROM
10 employee) tmp
11 WHERE rnk = N
12 );
13END
以上用6种写法实现同一需求,实际上这应该也代表了绝大多数写SQL查询的一般性思路:
能用单表优先用单表,即便是需要用group by、order by、limit等,效率一般也比多表高
不能用单表时优先用连接,连接是SQL中非常强大的用法,小表驱动大表+建立合适索引+合理运用连接条件,基本上连接可以解决绝大部分问题。但join级数不宜过多,毕竟是一个接近指数级增长的关联效果
能不用子查询、笛卡尔积尽量不用,虽然很多情况下MySQL优化器会将其优化成连接方式的执行过程,但效率仍然难以保证
自定义变量在复杂SQL实现中会很有用,例如LeetCode中困难级别的数据库题目很多都需要借助自定义变量实现
如果MySQL版本允许,窗口函数是一个最优选择,除了经典的获取3种排名信息,还有聚合函数、向前向后取值、百分位等,具体可参考官方指南(本号回复关键字"教程"提供网盘下载)
MySQL8.0内置窗口函数
--END--
扫码即可加我微信
观看朋友圈,获取最新学习资源
学习更多: 整理了我开始分享学习笔记到现在超过250篇优质文章,涵盖数据分析、爬虫、机器学习等方面,别再说不知道该从哪开始,实战哪里找了 优秀的读者都知道,“点赞”传统美德不能丢