专栏首页小数志一句SQL,我有6种写法

一句SQL,我有6种写法

导读

最近在刷LeetCode中数据库题目时,有一道排名题目,用了6种写法分别代表6种SQL思维来实现,想想也算是有趣。

题目描述:

题意理解不难,无非就是查找排名为N的记录,但常用SQL的都知道这里存在一个歧义,即排名是否存在相同和是否跳级的问题。经测试,这里的排名是"致密"排名(dense_rank),即同薪同名且不跳级那种。例如对于薪水3000/2000/2000/1000排名之后为1、2、2、3,若取N=3,则返回结果1000。另外,题目形式是一个自定义函数,但本质仍是一个SQL查询。

面对这样的一道题,你能迅速想到几种SQL写法呢?


解法1 limit+offset

由于这里题目需求很简单,仅仅是返回全局的第N高薪水,而不存在分组排名或其他需求,所以最简单的办法就是用limit+offset关键字直接获取。

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字段建立索引时查询效率会非常高。


解法2 子查询

既然是排名为N,那么就意味着大于等于目标薪水的记录数为N,更准确的说这里是去重后的记录数为N。基于此想法,很快可以写出相应SQL:

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

执行效率:

这个子查询效率要低不少,因为每条记录都要执行一条子查询判断聚合次数是否等于N。


解法3 连接查询

个人认为,SQL最强大也最有代表性的操作在于多表关联,这个问题自然也可以用连接查询。MySQL中主要支持join、left join和right join三种连接方式。具体到这一题,可以选用任何一种。例如,如果限定连接条件是薪水大于等于(含等于),则可直接用join实现两表自连接,然后对另一个计数即可;而如果限定连接条件是薪水大于(不含等于),则必须用left join,避免N取特殊值1时出现关联结果为空而查询失败的情况。具体来说:

应用join的SQL语句:

 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

执行效率:

应用left join的SQL语句:

 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

另外,right join本质上和left join是一致的,简单交换两表顺序可以很容实现right join写法。

执行效率:

可见,无论是用内连接还是外连接,效率都不是太高,与子查询效率相当。


解法4 笛卡尔积

用join连接方式实现的SQL,都能用笛卡尔积实现,且一般来说笛卡尔效率要略低于连接查询,但很多情况下MySQL优化器会将笛卡尔积形式的查询优化成join形式,此时二者执行过程是一致的。可以很容易将解法3中的形式改成笛卡尔积形式的写法。

SQL语句:

 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

执行效率:

这个查询的效率相比连接查询和子查询又要略低一些。


解法5 自定义变量

前面已经介绍了4种解法,对比来看:解法2-4中都存在两表关联的问题,而解法1因为仅涉及到单表排序,所以效率相比之下更高;另一方面,解法2-4功能更具扩展性:例如可以很容易实现分组查询排名第N高,而这是简单的limit+offset写法所不能实现的。那么,有没有既能拓展到分组查询、同时又具有单表查询的高效呢?答案是肯定的,例如下面的自定义变量写法,通过设定一个自变量,获取每个薪水的排名信息,然后筛选排名为N的薪水即可。

SQL语句:

 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

执行效率:

因为仅涉及到单表查询,所以效率更高,与直接用limit+offset效率相当。


解法6 窗口函数

实际上,解法5中的自定义变量查询写法在MySQL8.0以后有相应的窗口函数可以实现。窗口函数在MySQL8.0版本首次引进,而其他很多SQL语言则早已内置。具体而言,对于本题获取"致密"排名的薪水,用到的窗口函数就是dense_rank()。

SQL语句:

 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

实际执行过程和解法5是一样的,只是调用内置函数写法更加简洁,效率也与解法5相当并略高于后者。因为当前OJ系统应用MySQL5.6版本,所以无法测试效率。


对比总结

以上用6种写法实现同一需求,实际上这应该也代表了绝大多数写SQL查询的一般性思路:

  • 能用单表优先用单表,即便是需要用group by、order by、limit等,效率一般也比多表高
  • 不能用单表时优先用连接,连接是SQL中非常强大的用法,小表驱动大表+建立合适索引+合理运用连接条件,基本上连接可以解决绝大部分问题。但join级数不宜过多,毕竟是一个接近指数级增长的关联效果
  • 能不用子查询、笛卡尔积尽量不用,虽然很多情况下MySQL优化器会将其优化成连接方式的执行过程,但效率仍然难以保证
  • 自定义变量在复杂SQL实现中会很有用,例如LeetCode中困难级别的数据库题目很多都需要借助自定义变量实现
  • 如果MySQL版本允许,窗口函数是一个最优选择,除了经典的获取3种排名信息,还有聚合函数、向前向后取值、百分位等,具体可参考官方指南(本号回复关键字"教程"提供网盘下载)

MySQL8.0内置窗口函数

相关阅读:

本文分享自微信公众号 - 小数志(Datazhi),作者:luanhz

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 数据科学系列:sklearn库主要模块功能简介

    作为一名数据分析师,当我初次接触数据分析三剑客(numpy、pandas、matplotlib)时,感觉每个库的功能都很多很杂,所以在差不多理清了各模块功能后便...

    luanhz
  • python排序算法(一)

    luanhz
  • python排序算法(二)

    luanhz
  • 一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法

    这篇外传之前有这么几篇文章: 《一个执行计划异常变更的案例 - 前传》 《一个执行计划异常变更的案例 - 外传之绑定变量窥探》

    bisal
  • leetcode435. Non-overlapping Intervals

    使用二维数组表示区间组,每一个子数组的第一个值表示区间的开始坐标,第二个值表示区间的结束坐标。计算最少进行多少次删除操作,可以确保剩下的区间不会产生任何重叠。

    眯眯眼的猫头鹰
  • three.js 制作逻辑转体游戏(下)

    上一篇已经对绕非定轴转动有所了解,这篇郭先生继续说一说逻辑转体游戏的制作,这部分我们同样会遇到一些小问题,首先是根据数据渲染陷阱和目标区域,然后是对可以转动的判...

    郭先生的博客
  • 收缩Oracle数据文件

          最近有网友提到收缩Oracle数据文件的问题,这是DBA经常碰到的一个常见问题。通常我们需要收缩相应的数据文件以减少来自磁盘空间的压力以及提高数据库...

    Leshami
  • Linux终端下 dstat 监控工具

    关于dstat dstat 是一个可以取代vmstat,iostat,netstat和ifstat这些命令的多功能产品。dstat克服了这些命令的局限并增加了...

    小小科
  • Objective-C属性(property)的特性(attribute)

    以下:「attribute(s)」,「特性」是指同一事物(都指@property后面括号内的单词)。

    iOS Development
  • 德国、美国和中国之间的工业4.0大决战

    当“工业4.0”上升成为德国的民族战略、由总理默克尔亲自代言,并把这一概念迅速风靡全球时,中国工信部正在狠狠推动“中国制造2025”,总李理也担任起了“中国制造...

    机器人网

扫码关注云+社区

领取腾讯云代金券