SQL排序函数基础详解 row_number()/rank()/dense_rank() over(partition by)

标签: SQL  排序函数  

为了方便学习和测试,所有的例子都是在Oracle自带用户Scott下建立的。
我用了mysql的环境,借助以下语句进行建表和插入数据,以备后续练习使用

create table EMP  --创建表EMP
(  
  empno    int(4) not null,  
  ename    VARCHAR(10),  
  job      VARCHAR(9),  
  mgr      int(4),  
  hiredate DATE,  
  sal      decimal(7,2),  
  comm     decimal(7,2),  
  deptno   int(2)  
)  

alter table EMP  add  primary key (EMPNO); --为表追加empno列为主键 

--插入数据
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
  values (7369, 'SMITH', 'CLERK', 7902, str_to_date('1980-12-17', '%Y-%m-%d'), 800, null, 20);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7499, 'ALLEN', 'SALESMAN', 7698, str_to_date('1981-02-20', '%Y-%m-%d'), 1600, 300, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7521, 'WARD', 'SALESMAN', 7698, str_to_date('1981-02-22', '%Y-%m-%d'), 1250, 500, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7566, 'JONES', 'MANAGER', 7839, str_to_date('1981-04-02', '%Y-%m-%d'), 2975, null, 20);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7654, 'MARTIN', 'SALESMAN', 7698, str_to_date('1981-09-28', '%Y-%m-%d'), 1250, 1400, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7698, 'BLAKE', 'MANAGER', 7839, str_to_date('1981-05-01', '%Y-%m-%d'), 2850, null, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7782, 'CLARK', 'MANAGER', 7839, str_to_date('1981-06-09', '%Y-%m-%d'), 2450, null, 10);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7788, 'SCOTT', 'ANALYST', 7566, str_to_date('1987-04-19', '%Y-%m-%d'), 3000, null, 20);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7839, 'KING', 'PRESIDENT', null, str_to_date('1981-11-17', '%Y-%m-%d'), 5000, null, 10);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7844, 'TURNER', 'SALESMAN', 7698, str_to_date('1981-09-08', '%Y-%m-%d'), 1500, 0, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7876, 'ADAMS', 'CLERK', 7788, str_to_date('1987-05-23', '%Y-%m-%d'), 1100, null, 20);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7900, 'JAMES', 'CLERK', 7698, str_to_date('1981-12-03', '%Y-%m-%d'), 950, null, 30);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7902, 'FORD', 'ANALYST', 7566, str_to_date('1981-12-03', '%Y-%m-%d'), 3000, null, 20);  
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)   
       values (7934, 'MILLER', 'CLERK', 7782, str_to_date('1982-01-23', '%Y-%m-%d'), 1300, null, 10);  
  • 1.假如现在有这样一个需求,查询每个部门工资最高的雇员的信息,一般面对”最大”,”最小”问题的时候我们最容易想到的是用max(),min()等聚合函数来解决

外链接:

-- 1.查询每个部门工资最高的雇员的信息

    select * from
    (SELECT ename,job,hiredate,sal,deptno from emp)t1
    right join
    (select deptno,max(sal) sal from emp group by deptno)t2
    on t1.deptno = t2.deptno and t1.sal = t2.sal
    order by t1.deptno;

思路:我们先看看查询t2表的语句干了什么,

select deptno,max(sal) sal from emp group by deptno

以deptno 部门分组,同事利用max()筛选出sal工资列的最高数值,查询的结果即为每个部门的最高工资及对应部门;

这里写图片描述

我们的需求是每个部门最高工资员工的信息,此时我们已经完成一半,既然需要相关信息那么我们势必需要将全部员工的相关信息先查询出来,再通过与刚才t2表(部门,最高工资)做连接的操作,即以t2表为基表,以deptno与sal值相等的作为限制条件,对两个表做笛卡尔积取结果集,得到结果如下:

这里写图片描述

或者我们以内连接方式查询


select * from (select ename, job, hiredate, sal, deptno  
          from emp)t1,  
               (select deptno, max(sal) sal from emp group by deptno)t2  
         where t1.deptno = t2.deptno  
           and t1.sal = t2.sal  
            order by t1.deptno;  

结果如下:
这里写图片描述

–以上是常用内连接简写,我们用标准内连接写法再写一次,注意写法上的区别

select * from         
            (select ename, job, hiredate, sal, deptno  
            from emp)t1
          inner join
            (select deptno, max(sal) sal from emp group by deptno)t2  
            on t1.deptno = t2.deptno and t1.sal = t2.sal
order by t1.deptno;  


得到的结果当然也是一样的

这里写图片描述


上面很啰嗦,因为最近从工作中发现自己在连接这块还是很欠缺的,所以针对这最简单的例子进行一个回顾,有益无害吧,大佬们直接略过就好

接下来才是这篇博客我想总结的点

  • 在满足第一个需求的同时,我们应该习惯性的思考一下是否还有别的方法。答案是肯定的,
    –就是标题中 rank() over(partition by)dense_rank() over(partition by)row_number() over(partition by)的排序函数

SQL分别如下:

  • rank() over(partition by)

 select t1.empno, t1.ename, t1.job, t1.hiredate, t1.sal, t1.deptno  
        from (select empno, ename, job, hiredate, sal, deptno,
        rank() over (partition by deptno order by sal desc ) Ranklist
        from  emp)t1
        where Ranklist =1;

先看结果:

这里写图片描述

可见,得到的结果相同

那为什么会得出跟上面的语句一样的结果呢?
这里我们着重分析一下rank()/dense_rank() over(partition by e.deptno order by e.sal desc)这句的语法。

  • over —— 在什么条件之上。
  • partition by e.deptno —— 按部门编号划分(分区)。
  • order by e.sal desc —— 按工资从高到低排序(使用rank()/dense_rank() 时,必须要带order by否则为非法(都说要排序了,再不指定排序的字段是怕是不合适吧?))
  • rank()/dense_rank()/row_number() —— 排序

那结合起来,整个语句的意思就是:
在按部门划分的基础上按工资从高到低对雇员进行排序,“排序”由从小到大(desc降序)的数字表示(最小值一定为1)。

以下两个排序函数的结果

  • dense_rank() over(partition by)
select t1.empno, t1.ename, t1.job, t1.hiredate, t1.sal, t1.deptno  
        from (select empno,ename,job,hiredate,sal,deptno,
        dense_rank() over (partition by deptno order by sal desc ) Ranklist
        from emp)t1
        where Ranklist = 1;

结果:
这里写图片描述

  • row_number() over(partition by)
select t1.empno, t1.ename, t1.job, t1.hiredate, t1.sal, t1.deptno  
        from (select empno,ename,job,hiredate,sal,deptno,
        row_number() over (partition by deptno order by sal desc ) Ranklist
        from emp)t1
        where Ranklist = 1;

结果如下:
这里写图片描述

我们可以看到,rank()、dense_rank()结果相同,而row_number()仅出线了以为sal为3000的员工scott

这时候我们会想是什么导致了以上的差别?


那我们此时关注的问题便转化为:
- row_number()、rank()与dense_rank()与之间的区别?

1.row_number()
  • row_number():暂且称为去重排序,在每个分组内,为查询出来的每一行记录生成一个序号,依次排序且不会重复;
  • 其基本原理是先使用over子句中的排序语句对记录进行排序,然后按照这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系,这两处的order by 可以完全不同
  1. 在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where group by order by 的执行。

  2. partition by 用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组,它和聚合函数不同的地方在于它能够返回一个分组中的多条记录,而聚合函数一般只有一个反映统计值的记录。

  3. 注意:在使用row_number**实现分页**时需要特别注意一点,over子句中的order by要与Sql排序记录中的order by 保持一致,否则得到的序号可能不是连续的。

结果:
这里写图片描述

2. rank()
  • rank(): 跳跃排序,在每个分组内,如果有两个第一位时,接下来就是第三位。

     他会对查询出来的记录进行排名,与row_number函数不同的是,
     rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号;
    

通过刚才例子中的子句运行结果可清楚得知:

rank()
这里写图片描述

3. dense_rank()
  • dense_rank(): 连续排序,在每个分组内,如果有两个第一级时,接下来仍然是第二级。

     dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连
     续。
     dense_rank函数**出现相同排名时,将不跳过相同排名号**,rank值**紧接**上一次的rank值。在各个分组内,
     rank()是跳  跃排序,有两个第一名时接下来就是第四名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。
    

结果如下:

这里写图片描述

版权声明:本文为Ego_Bai原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/Ego_Bai/article/details/81567496

智能推荐

spring cloud netflix (07) 服务的消费者(feign)

前言 完整知识点:spring cloud netflix 系列技术栈 Feign (同步通信 HTTP通信) feign是基于接口完成服务与服务之间的通信的 搭建Feign服务 项目结构 项目搭建 pom.xml application类 application.yml 使用feign完成服务与服务之间的通信 feign是基于接口完成服务与服务之间的通信的...

AtCoder Beginner Contest 174 E.Logs

AtCoder Beginner Contest 174 E.Logs 题目链接 到最后才发现是二分,菜菜的我/(ㄒoㄒ)/~~ 我们直接二分 [1,max{a[i]}][1,max\lbrace a[i]\rbrace][1,max{a[i]}] 即可,对每一个 midmidmid,每个数 a[i]a[i]a[i] 只需要切 a[i]−1mid\frac{a[i]-1}{mid}mi...

小程序基础与实战案例

小程序开发工具与基础 小程序开发准备: 申请小程序账号( appid ) 下载并安装微信开发者工具 具体步骤如下: 先进入 微信公众平台 ,下拉页面,把鼠标悬浮在小程序图标上 然后点击 小程序开发文档 照着里面给的步骤,就可以申请到小程序账号了。 然后就可以下载 开发者工具 了 下载完打开后的界面就是这个样子 下面让我们来新建一个小程序开发项目: 在AppID输入自己刚刚注册的AppID就可以,或...

VMware centOS7 下通过minikube部署Kubernetes

1、环境准备: VMware CentOS-7-x86_64 CPU:2*2core 内存:8G 宿主机和虚拟机需网络互通,虚拟机外网访问正常 Centos发行版版本查看:cat /etc/centos-release root用户操作 2、禁用swap分区 Kubernetes 1.8开始要求关闭系统的Swap,可暂时关闭或永久禁用, 使用 $ free -m 确认swap是否为开启状态 $ s...

逻辑回归与scikit-learn

欢迎关注本人的微信公众号AI_Engine LogisticRegression 算法原理 一句话概括:逻辑回归假设数据服从伯努利分布,通过极大化似然函数(损失函数)的方法,运用梯度下降或其他优化算法来求解参数,来达到将数据二分类的目的。 定义:逻辑回归(Logistic Regression)是一种用于解决二分类(0 or 1)问题的机器学习方法,用于估计某种事物的可能性(不是概率)。比如某用户...

猜你喜欢

指针OR数组?用他们来表达字符串又有何不同?

cocowy的编程之旅 在学习C语言的过程中我们经常可以看到或者听到这样一句话:数组其实等价于指针,例如: 在这里可以轻松的看出输出后他们的值相等,其实在计算机内存里面,p为本地变量,有着他自己的作用域。而指针变量q保存着这个数组的首地址,通过*号指向这个地址保存的变量值。 然而我们再看一个例子: 这个时候计算机报错,这是为什么呢? 其实原因很简单,指针说指向的这个字符串的地址是位于计算机代码段地...

广度搜索

广度搜索的基本使用方法 广度搜索不同于深度搜索,是一种一步一步进行的过程,每一个点只记录一遍。需要用到队列记录每一步可以走到的位置,找到目标位置输出步数即可。 用到的知识:结构体、队列 如图 首先我们需要定义一个结构体来存储每个遍历到的点和步数 广搜不会用到递归,所以可以直接在主函数里写,这里需要定义一个结构体队列 初始化队列并将起始点入列 遍历 完整代码...

NIO Socket 编程实现tcp通信入门(二)

1、NIO简介 NIO面向通道和缓冲区进行工作,数据总是从通道读取到缓冲区中,或者从缓冲区写入到通道中。可以双向传输数据,是同步非阻塞式IO。NIO还引入了选择器机制,从而实现了一个选择器监听多个底层通道,减少了线程并发数。用NIO实现socket的Tcp通信需要掌握下面三个知识点: Buffer 缓冲区 Channel 通道 Selector 选择器   2、java.nio.Buff...

[字节码系列]ObjectWeb ASM构建Method Monitor

      在前面的篇章中,我们看到Java Instrutment的强大能力,本篇,我们将介绍如何使用ObjectWeb ASM的字节码增强能力构建Method Monitor       1.什么是ObjectWeb ASM      ObjectWeb ...

Core Location 电子围栏:入门

原文:Geofencing with Core Location: Getting Started 作者:Andy Pereira 译者:kmyhy 更新说明:Andy Pereira 将本教程升级至 Xcode 9.3 和 Swift 4.1。 Geofencing 会在设备进入/离开指定的电子围栏时通知应用程序。它可以让你写出一些很酷的应用程序,当你从家里出来时触发通知,或者在附近出现最爱的商...