postgresql rank() over, dense_rank(), row_number()用法区别

如下学生表student,学生表中有姓名、分数、课程编号,需要按照课程对学生的成绩进行排序

select * from jinbo.student;
 id | name | score | course
----+-------+-------+--------
 5 | elic | 70 |  1
 4 | dock | 100 |  1
 3 | cark | 80 |  1
 2 | bob | 90 |  1
 1 | alice | 60 |  1
 10 | jacky | 80 |  2
 9 | iris | 80 |  2
 8 | hill | 60 |  1
 7 | grace | 50 |  2
 6 | frank | 70 |  2
 6 | test |  |  2
(11 rows)

1、rank over () 可以把成绩相同的两名是并列,如下course = 2 的结果rank值为:1 2 2 4 5

 select name,
  score,
  course,
  rank() over(partition by course order by score desc) as rank
 from jinbo.student;
 name | score | course | rank
-------+-------+--------+------
 dock | 100 |  1 | 1
 bob | 90 |  1 | 2
 cark | 80 |  1 | 3
 elic | 70 |  1 | 4
 hill | 60 |  1 | 5
 alice | 60 |  1 | 5
 test |  |  2 | 1
 iris | 80 |  2 | 2
 jacky | 80 |  2 | 2
 frank | 70 |  2 | 4
 grace | 50 |  2 | 5
(11 rows)

2、dense_rank()和rank over()很相似,可以把学生成绩并列不间断顺序排名,如下course = 2 的结果rank值为:1 2 2 3 4

select name,score,
  course,
  dense_rank() over(partition by course order by score desc) as rank
 from jinbo.student;
 name | score | course | rank
-------+-------+--------+------
 dock | 100 |  1 | 1
 bob | 90 |  1 | 2
 cark | 80 |  1 | 3
 elic | 70 |  1 | 4
 hill | 60 |  1 | 5
 alice | 60 |  1 | 5
 test |  |  2 | 1
 iris | 80 |  2 | 2
 jacky | 80 |  2 | 2
 frank | 70 |  2 | 3
 grace | 50 |  2 | 4
(11 rows)

3、row_number 可以把相同成绩的连续排名,如下 course = 2 的结果rank值为:1 2 3 4 5

select name,score,
  course,
  row_number() over(partition by course order by score desc) as rank
 from jinbo.student;
 name | score | course | rank
-------+-------+--------+------
 dock | 100 |  1 | 1
 bob | 90 |  1 | 2
 cark | 80 |  1 | 3
 elic | 70 |  1 | 4
 hill | 60 |  1 | 5
 alice | 60 |  1 | 6
 test |  |  2 | 1
 iris | 80 |  2 | 2
 jacky | 80 |  2 | 3
 frank | 70 |  2 | 4
 grace | 50 |  2 | 5
(11 rows)

使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果,可以如下:

rank over(partition by course order by score desc nulls last)

4、总结

partition by 用于结果集分组,如果没有指定,会把整个结果集作为一个分组

rank 、dense_rank 、row_numer 都是不同方式的结果集组内排序,一般都结合over 字句出现,over 字句里 会有 partition by、order by、last、first 的任意组合,如下:

rank() over(partition by a,b order by a, order by b desc);
rank() over(partition by a order by b nulls first)
rank() over(partition by a order by b nulls last)

补充:Oracle或者PostgreSQL的row_number over 排名语法

PostgreSQL 和Oracle 都提供了 row_number() over() 这样的语句来进行对应的字段排名,很是方便。MySQL却没有提供这样的语法。

这次我提供的表结构如下,

    Table "ytt.t1"
 Column |   Type   | Modifiers
--------+-----------------------+-----------
 i_name | character varying(10) | not null
 rank | integer    | not null 

我模拟了20条数据来做演示。

t_girl=# select * from t1 order by i_name;
 i_name | rank
---------+------
 Charlie | 12
 Charlie | 12
 Charlie | 13
 Charlie | 10
 Charlie | 11
 Lily  | 6
 Lily  | 7
 Lily  | 7
 Lily  | 6
 Lily  | 5
 Lily | 7
 Lily | 4
 Lucy | 1
 Lucy | 2
 Lucy | 2
 Ytt  | 14
 Ytt  | 15
 Ytt  | 14
 Ytt  | 14
 Ytt  | 15
(20 rows) 

在PostgreSQL下,我们来对这样的排名函数进行三种不同的执行方式1:

第一种:

完整的带有排名字段以及排序。

t_girl=# select i_name,rank, row_number() over(partition by i_name order by rank desc) as rank_number from t1;
 i_name | rank | rank_number
---------+------+-------------
 Charlie  | 13 |   1
 Charlie | 12 |   2
 Charlie | 12 |   3
 Charlie | 11 |   4
 Charlie | 10 |   5
 Lily  | 7 |   1
 Lily  | 7 |   2
 Lily  | 7 |   3
 Lily  | 6 |   4
 Lily  | 6 |   5
 Lily  | 5 |   6
 Lily  | 4 |   7
 Lucy | 2 |   1
 Lucy | 2 |   2
 Lucy | 1 |   3
 Ytt  | 15 |   1
 Ytt  | 15 |   2
 Ytt  | 14 |   3
 Ytt  | 14 |   4
 Ytt  | 14 |   5
(20 rows) 

第二种:

带有完整的排名字段但是没有排序。

t_girl=# select i_name,rank, row_number() over(partition by i_name ) as rank_number from t1;
 i_name | rank | rank_number
---------+------+-------------
 Charlie  | 12 |   1
 Charlie | 12 |   2
 Charlie | 13 |   3
 Charlie | 10 |   4
 Charlie | 11 |   5
 Lily  | 6 |   1
 Lily  | 7 |   2
 Lily  | 7 |   3
 Lily  | 6 |   4
 Lily  | 5 |   5
 Lily  | 7 |   6
 Lily  | 4 |   7
 Lucy | 1 |   1
 Lucy | 2 |   2
 Lucy | 2 |   3
 Ytt  | 14 |   1
 Ytt  | 15 |   2
 Ytt  | 14 |   3
 Ytt  | 14 |   4
 Ytt  | 15 |   5
(20 rows) 

第三种:

没有任何排名字段,也没有任何排序字段。

t_girl=# select i_name,rank, row_number() over() as rank_number from t1;
 i_name | rank | rank_number
---------+------+-------------
 Lily  | 7 |   1
 Lucy | 2 |   2
 Ytt  | 14 |   3
 Ytt  | 14 |   4
 Charlie | 12 |   5
 Charlie | 13 |   6
 Lily  | 7 |   7
 Lily  | 4 |   8
 Ytt  | 14 |   9
 Lily  | 6 |   10
 Lucy | 1 |   11
 Lily  | 7 |   12
 Ytt  | 15 |   13
 Lily  | 6 |   14
 Charlie | 11 |   15
 Charlie | 12 |   16
 Lucy | 2 |   17
 Charlie | 10 |   18
 Lily  | 5 |   19
 Ytt  | 15 |   20
(20 rows) 

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。如有错误或未考虑完全的地方,望不吝赐教。

(0)

相关推荐

  • PostgreSQL 实现将多行合并转为列

    需求将下列表格相同id的name拼接起来输出成一列 id Name 1 peter 1 lily 2 john 转化后效果: id Name 1 peter:lily 2 john: 实现方式使用 array_to_string 和 array_agg 函数,具体语句如下: string_agg(expression, delimiter) 把表达式变成一个数组 string_agg(expression, delimiter) 直接把一个表达式变成字符串 select id, array_to

  • Postgresql排序与limit组合场景性能极限优化详解

    1 构造测试数据 create table tbl(id int, num int, arr int[]); create index idx_tbl_arr on tbl using gin (arr); create or replace function gen_rand_arr() returns int[] as $$ select array(select (1000*random())::int from generate_series(1,64)); $$ language sq

  • postgresql 实现sql多行语句合并一行

    多行语句合并一行 三个表关联查询的结果集为: SELECT users.name,users.age,users.birthday,roles.name FROM users,users_roles,roles WHERE users_roles.userid = users.id and users_roles.roleid = roles.id name age birthday role 张三 23 1993-1-1 role1 张三 23 1993-1-1 role2 张三 23 199

  • PostgreSQL删除更新优化操作

    1. 先说删除吧,因为刚搞了. 删除缓慢的原因:主要是约束的问题.(数据库在有约束的时候,进行操作,会根据约束对相关表进行验证,可想而知,20W的数据验证要耗费多久的时间).其次就是sql的编写.(sql如果查询中包含子查询等的可以优化的where会影响匹配的速度<查询的话就不多逼逼了>).索引的问题 请看下面的 补充部分 具体解决方法: ALTER TABLE tableName DISABLE TRIGGER ALL; delete 目标语句 ALTER TABLE tableName E

  • postgreSQL 非count方法算记录数操作

    一般方法 select count(1) from table_name; 全量扫描一遍表,记录越多,查询速度越慢 新法 PostgreSQL 还真提供了一个这样的途径,那就是系统表 pg_class,这个系统表里头,存储着每个表的统计信息,其中 reltuples 就是对应的表的统计行,统计行的数据是pg有个独立进程,定期扫描不同的表,收集这些表的统计信息,保存在系统表里头. 方法如下: select reltuples::int as total from pg_class where re

  • postgresql通过索引优化查询速度操作

    当数据量比较大的时候,提升查询效率就是需要去考虑的事情了.一个百万级别的表格,如果不做任何优化的话,即使是最简单的查询语句执行起来也是慢的让人难以接受:当然"优化"本身是一个比较复杂的工程,从设计表.字段到查询语句的写法都有很多讲究,这里只考虑索引的方式,且是最普通的索引: 下面的操作中对应数据库表w008_execrise_info(8000数据量), w008_wf02_info(4000数据量) 1 任务表数据 SELECT w.* FROM w008_wf02_info w W

  • 在postgresql数据库中判断是否是数字和日期时间格式函数操作

    在编写GreenPlum函数的过程中,遇到要判断字符串是否是数字和日期格式的情况,基于GreenPlum和postgresql的亲缘关系,找到了下面两个函数. --1.判断字符串是否是数字 CREATE OR REPLACE FUNCTION isnumeric(txtStr VARCHAR) RETURNS BOOLEAN AS $$ BEGIN RETURN txtStr ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'; END; $$ LANGUAGE 'plpgsq

  • Postgresql自定义函数详解

    PostgreSQL函数也称为PostgreSQL存储过程. PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明,分配,循环,控制流程等). 语法: CREATE [OR REPLACE] FUNCTION function_name (arguments) RETURNS return_datatype AS $variable_name$ DECLARE declaration; [...] BEGIN < function_body

  • postgresql rank() over, dense_rank(), row_number()用法区别

    如下学生表student,学生表中有姓名.分数.课程编号,需要按照课程对学生的成绩进行排序 select * from jinbo.student; id | name | score | course ----+-------+-------+-------- 5 | elic | 70 | 1 4 | dock | 100 | 1 3 | cark | 80 | 1 2 | bob | 90 | 1 1 | alice | 60 | 1 10 | jacky | 80 | 2 9 | iri

  • SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法

    ROW_NUMBER() 说明:返回结果集分区内行的序列号,每个分区的第一行从 1 开始.语法:ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) .备注:ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序.参数:<partition_by_clause> :将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区.       <o

  • Oracle开发之分析函数(Rank, Dense_rank, row_number)

    一.使用rownum为记录排名: 在前面一篇<Oracle开发之分析函数简介Over>,我们认识了分析函数的基本应用,现在我们再来考虑下面几个问题: ①对所有客户按订单总额进行排名 ②按区域和客户订单总额进行排名 ③找出订单总额排名前13位的客户 ④找出订单总额最高.最低的客户 ⑤找出订单总额排名前25%的客户 按照前面第一篇文章的思路,我们只能做到对各个分组的数据进行统计,如果需要排名的话那么只需要简单地加上rownum不就行了吗?事实情况是否如此想象般简单,我们来实践一下. [1]测试环境

  • SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较

    排名函数是SQL Server2005新加的功能.在SQL Server2005中有如下四个排名函数: 1.row_number 2.rank 3.dense_rank 4.ntile 下面分别介绍一下这四个排名函数的功能及用法.在介绍之前假设有一个t_table表,表结构与表中的数据如图1所示: 图1 其中field1字段的类型是int,field2字段的类型是varchar 一.row_number row_number函数的用途是非常广泛,这个函数的功能是为查询出来的每一行记录生成一个序号

  • sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介绍

    1.ROW_NUMBER() 定义:ROW_NUMBER()函数作用就是将select查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,一般多用于分页查询,  比如查询前10个 查询10-100个学生. 实例: 1.1对学生成绩排序 这里number就是每个学生的序号 根据studentScore(分数)进行desc倒序 1.2获取第二个同学的成绩信息 这里用到的思想就是 分页查询的思想 在原sql外再套一层select  where  t.number>=1  and  

  • 数据库SQL中having和where的用法区别

    "Where" 是一个约束声明,使用Where来约束来之数据库的数据,Where是在结果返回之前起作用的,且Where中不能使用聚合函数. "Having"是一个过滤声明,是在查询返回结果集以后对查询结果进行的过滤操作,在Having中可以使用聚合函数. 聚合函数,SQL基本函数,聚合函数对一组值执行计算,并返回单个值.除了 COUNT 以外,聚合函数都会忽略空值. 聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用. Transact-SQL编程

  • PHP中exec与system用法区别分析

    本文实例讲述了PHP中exec与system用法区别,分享给大家供大家参考之用.具体方法如下: 一般来说,在PHP中调用外部命令,可以用exec及system来实现: system() 原型:string system (string command [, int return_var]) system()函数很其它语言中的差不多,它执行给定的命令,输出和返回结果.第二个参数是可选的,用来得到命令执行后的状态码. 返回结果: 成功返回0,   失败(命令不存在等原因)   返回   非0值 ex

  • 浅析php静态方法与非静态方法的用法区别

    在php编程中,static关键字声明一个属性或方法是和类相关的,而不是和类的某个特定的实例相关,因此,这类属性或方法也称为"类属性"或"类方法" 如果访问控制权限允许,可不必创建该类对象而直接使用类名加两个冒号"::"调用. static关键字可以用来修饰变量.方法. 不经过实例化,就可以直接访问类中static的属性和static的方法. static 的属性和方法,只能访问static的属性和方法,不能类访问非静态的属性和方法.因为静态属性

  • JavaScript常用截取字符串的三种方式用法区别实例解析

    stringObject.substring(start,stop) 用于提取字符串中介于两个指定下标之间的字符. start必需.一个非负的整数,规定要提取的子串的第一个字符在 stringObject 中的位置. stop可选.一个非负的整数,比要提取的子串的最后一个字符在 stringObject 中的位置多 1.如果省略该参数,那么返回的子串会一直到字符串的结尾. start从0开始 到stop(不包含stop)结束 不接受负的参数. stringObject.substr(start,

  • mysql中left join设置条件在on与where时的用法区别分析

    本文实例讲述了mysql中left join设置条件在on与where时的用法区别.分享给大家供大家参考,具体如下: 一.首先我们准备两张表来进行测试. CREATE TABLE `a` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(32) DEFAULT '' COMMENT '名称', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET

随机推荐