postgresql rank() over, dense_rank(), row_number()用法區(qū)別
更新時間：2020年12月28日 16:55:59 作者：zdp072
這篇文章主要介紹了postgresql rank() over, dense_rank(), row_number()的用法區(qū)別，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
如下學(xué)生表student，學(xué)生表中有姓名、分?jǐn)?shù)、課程編號，需要按照課程對學(xué)生的成績進行排序
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 的結(jié)果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()很相似，可以把學(xué)生成績并列不間斷順序排名，如下course = 2 的結(jié)果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 可以把相同成績的連續(xù)排名，如下 course = 2 的結(jié)果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字段排在最前面，影響排序結(jié)果，可以如下：
rank over(partition by course order by score desc nulls last)
4、總結(jié)
partition by 用于結(jié)果集分組，如果沒有指定，會把整個結(jié)果集作為一個分組
rank 、dense_rank 、row_numer 都是不同方式的結(jié)果集組內(nèi)排序，一般都結(jié)合over 字句出現(xiàn)，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() 這樣的語句來進行對應(yīng)的字段排名，很是方便。MySQL卻沒有提供這樣的語法。
這次我提供的表結(jié)構(gòu)如下，
Table "ytt.t1"
Column | Type | Modifiers
--------+-----------------------+-----------
i_name | character varying(10) | not null
rank | integer | not null
我模擬了20條數(shù)據(jù)來做演示。
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下，我們來對這樣的排名函數(shù)進行三種不同的執(zhí)行方式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)
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
這篇文章主要介紹了PostgreSQL timestamp踩坑記錄與填坑指南，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
使用PostGIS完成兩點間的河流軌跡及流經(jīng)長度的計算(推薦)
這篇文章主要介紹了使用PostGIS完成兩點間的河流軌跡及流經(jīng)長度的計算,使用POSTGIS及其擴展pgrouting計算給定兩點間的河流流經(jīng)區(qū)域和河流長度，需要的朋友可以參考下2022-01-01
PostgreSQL數(shù)據(jù)庫中Sequence的使用方法詳解
在 PostgreSQL 數(shù)據(jù)庫中,Sequence 是一種特殊的表對象,主要用于生成按順序遞增或遞減的數(shù)字序列,通常用于需要唯一標(biāo)識符的場景,例如自增 ID,以下是如何在 PostgreSQL 中使用 Sequence 的詳細(xì)步驟,需要的朋友可以參考下2024-11-11
這篇文章主要介紹了FREEBSD安裝POSTGRESQL筆記,本文著重講解安裝中遇到到的一些問題和解決方法,需要的朋友可以參考下2015-02-02
這篇文章主要介紹了PostgreSQL教程(十八)：客戶端命令（2）,本文講解了pg_dump、pg_restore、psql、內(nèi)置命令應(yīng)用示例等內(nèi)容,需要的朋友可以參考下2015-05-05
如何使用Dockerfile創(chuàng)建PostgreSQL數(shù)據(jù)庫
這篇文章主要介紹了如何使用Dockerfile創(chuàng)建PostgreSQL數(shù)據(jù)庫,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友參考下吧2024-02-02
用一整天的時間安裝postgreSQL NTFS權(quán)限
看標(biāo)題貌似一天的收獲不小，但實際上是被一個問題搞的要死，啥問題？額，又是NTFS權(quán)限的問題。2009-08-08
PostgreSQL的SQL優(yōu)化技巧其實和大多數(shù)使用CBO優(yōu)化器的數(shù)據(jù)庫類似，因此一些常用的SQL優(yōu)化改寫技巧在PostgreSQL也是能夠使用的。當(dāng)然也會有一些不同的地方，今天我們來看看一些在PostgreSQL常用的SQL優(yōu)化改寫技巧2022-09-09
postgresql合并string_agg函數(shù)的實例
這篇文章主要介紹了postgresql合并string_agg函數(shù)的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
如何查看PostgreSQL數(shù)據(jù)庫中所有表
這篇文章主要介紹了如何查看PostgreSQL數(shù)據(jù)庫中所有表問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03