PostgreSQL ROW_NUMBER() OVER()的用法說(shuō)明
更新時(shí)間：2021年02月02日 08:46:45 作者：賤一白
這篇文章主要介紹了PostgreSQL ROW_NUMBER() OVER()的用法說(shuō)明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧
語(yǔ)法：
ROW_NUMBER() OVER( [ PRITITION BY col1] ORDER BY col2[ DESC ] )
解釋：
ROW_NUMBER()為返回的記錄定義個(gè)行編號(hào)， PARTITION BY col1 是根據(jù)col1分組，ORDER BY col2[ DESC ]是根據(jù)col2進(jìn)行排序。
舉例：
postgres=# create table student(id serial,name character varying,course character varying,score integer);
CREATE TABLE
postgres=#
postgres=# \d student
Table "public.student"
Column | Type | Modifiers
--------+-------------------+----------------------------------------------
id | integer | not null default nextval('student_id_seq'::regclass)
name | character varying |
course | character varying |
score | integer |
insert into student (name,course,score) values('周潤(rùn)發(fā)','語(yǔ)文',89);
insert into student (name,course,score) values('周潤(rùn)發(fā)','數(shù)學(xué)',99);
insert into student (name,course,score) values('周潤(rùn)發(fā)','外語(yǔ)',67);
insert into student (name,course,score) values('周潤(rùn)發(fā)','物理',77);
insert into student (name,course,score) values('周潤(rùn)發(fā)','化學(xué)',87);
insert into student (name,course,score) values('周星馳','語(yǔ)文',91);
insert into student (name,course,score) values('周星馳','數(shù)學(xué)',81);
insert into student (name,course,score) values('周星馳','外語(yǔ)',88);
insert into student (name,course,score) values('周星馳','物理',68);
insert into student (name,course,score) values('周星馳','化學(xué)',83);
insert into student (name,course,score) values('黎明','語(yǔ)文',85);
insert into student (name,course,score) values('黎明','數(shù)學(xué)',65);
insert into student (name,course,score) values('黎明','外語(yǔ)',95);
insert into student (name,course,score) values('黎明','物理',90);
insert into student (name,course,score) values('黎明','化學(xué)',78);
1. 根據(jù)分?jǐn)?shù)排序
postgres=# select *,row_number() over(order by score desc)rn from student; id | name | course | score | rn ----+--------+--------+-------+---- 2 | 周潤(rùn)發(fā) | 數(shù)學(xué) | 99 | 1 13 | 黎明 | 外語(yǔ) | 95 | 2 6 | 周星馳 | 語(yǔ)文 | 91 | 3 14 | 黎明 | 物理 | 90 | 4 1 | 周潤(rùn)發(fā) | 語(yǔ)文 | 89 | 5 8 | 周星馳 | 外語(yǔ) | 88 | 6 5 | 周潤(rùn)發(fā) | 化學(xué) | 87 | 7 11 | 黎明 | 語(yǔ)文 | 85 | 8 10 | 周星馳 | 化學(xué) | 83 | 9 7 | 周星馳 | 數(shù)學(xué) | 81 | 10 15 | 黎明 | 化學(xué) | 78 | 11 4 | 周潤(rùn)發(fā) | 物理 | 77 | 12 9 | 周星馳 | 物理 | 68 | 13 3 | 周潤(rùn)發(fā) | 外語(yǔ) | 67 | 14 12 | 黎明 | 數(shù)學(xué) | 65 | 15 (15 rows)
rn是給我們的一個(gè)排序。
2. 根據(jù)科目分組，按分?jǐn)?shù)排序
postgres=# select *,row_number() over(partition by course order by score desc)rn from student; id | name | course | score | rn ----+--------+--------+-------+---- 5 | 周潤(rùn)發(fā) | 化學(xué) | 87 | 1 10 | 周星馳 | 化學(xué) | 83 | 2 15 | 黎明 | 化學(xué) | 78 | 3 13 | 黎明 | 外語(yǔ) | 95 | 1 8 | 周星馳 | 外語(yǔ) | 88 | 2 3 | 周潤(rùn)發(fā) | 外語(yǔ) | 67 | 3 2 | 周潤(rùn)發(fā) | 數(shù)學(xué) | 99 | 1 7 | 周星馳 | 數(shù)學(xué) | 81 | 2 12 | 黎明 | 數(shù)學(xué) | 65 | 3 14 | 黎明 | 物理 | 90 | 1 4 | 周潤(rùn)發(fā) | 物理 | 77 | 2 9 | 周星馳 | 物理 | 68 | 3 6 | 周星馳 | 語(yǔ)文 | 91 | 1 1 | 周潤(rùn)發(fā) | 語(yǔ)文 | 89 | 2 11 | 黎明 | 語(yǔ)文 | 85 | 3 (15 rows)
3. 獲取每個(gè)科目的最高分
postgres=# select * from(select *,row_number() over(partition by course order by score desc)rn from student)t where rn=1; id | name | course | score | rn ----+--------+--------+-------+---- 5 | 周潤(rùn)發(fā) | 化學(xué) | 87 | 1 13 | 黎明 | 外語(yǔ) | 95 | 1 2 | 周潤(rùn)發(fā) | 數(shù)學(xué) | 99 | 1 14 | 黎明 | 物理 | 90 | 1 6 | 周星馳 | 語(yǔ)文 | 91 | 1 (5 rows)
4. 每個(gè)科目的最低分也是一樣的
postgres=# select * from(select *,row_number() over(partition by course order by score)rn from student)t where rn=1; id | name | course | score | rn ----+--------+--------+-------+---- 15 | 黎明 | 化學(xué) | 78 | 1 3 | 周潤(rùn)發(fā) | 外語(yǔ) | 67 | 1 12 | 黎明 | 數(shù)學(xué) | 65 | 1 9 | 周星馳 | 物理 | 68 | 1 11 | 黎明 | 語(yǔ)文 | 85 | 1 (5 rows)
只要在根據(jù)科目排序的時(shí)候按低到高順序排列就好了。
補(bǔ)充：SQL：postgresql中為查詢結(jié)果增加一個(gè)自增序列之ROW_NUMBER () OVER ()的使用
舉例說(shuō)明:
SELECT ROW_NUMBER () OVER ( ORDER BY starttime DESC ) "id", starttime AS "text", starttime FROM warning_products WHERE pid_model = '結(jié)果' AND starttime IS NOT NULL GROUP BY starttime
在這一段代碼中:
查詢語(yǔ)句就不說(shuō)了, select …from…where
GROUP BY的作用:
這一段代碼執(zhí)行的結(jié)果是:
如果將GROUP BY刪除,那么執(zhí)行結(jié)果為:
可以看到查詢出了兩個(gè)相同starttime數(shù)據(jù).
由此得出:
GROUP BY的作用是分類匯總.也就是說(shuō),查詢結(jié)果中,starttime每一種查詢結(jié)果只有一個(gè)
GROUP BY的作用:
如果將DESC換成
() OVER ( ORDER BY starttime ASC ) "id",
則運(yùn)行結(jié)果為:
相比可以發(fā)現(xiàn),ORDER BY的作用為進(jìn)行排序.
按照某種要求進(jìn)行固定的排序
ROW_NUMBER () OVER() “id”
先來(lái)看一下,如果把這一段刪掉,運(yùn)行結(jié)果:
加上呢?
明顯的對(duì)比,我們?yōu)樽罱K的查詢結(jié)果增加了一列自增的id序列(這里id可以改名,"id"改為其他的即可)
由此得到結(jié)論,在執(zhí)行帶有row_number() over() "xx"的SQL語(yǔ)句時(shí)候,代碼會(huì)先執(zhí)行查詢語(yǔ)句,然后執(zhí)行over中的命令,最后為結(jié)果增加一列自增的序列.
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。
相關(guān)文章
PostgreSQL怎么創(chuàng)建分區(qū)表詳解
數(shù)據(jù)庫(kù)表分區(qū)把一個(gè)大的物理表分成若干個(gè)小的物理表,并使得這些小物理表在邏輯上可以被當(dāng)成一張表來(lái)使用,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL怎么創(chuàng)建分區(qū)表的相關(guān)資料,需要的朋友可以參考下2022-06-06
PostgreSQL定時(shí)清理舊數(shù)據(jù)的實(shí)現(xiàn)方法
最近覺(jué)得數(shù)據(jù)庫(kù)中每日數(shù)據(jù)不需要都保持,只需要保留30天的,所以這篇文章給大家介紹了PostgreSQL定時(shí)清理舊數(shù)據(jù)的實(shí)現(xiàn)方法,文中通過(guò)代碼示例和圖文給大家介紹的非常詳細(xì),具有一定的參考價(jià)值,需要的朋友可以參考下2024-03-03
這篇文章主要介紹了PostgreSQL 中字段類型varchar的用法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2020-12-12
解決postgresql無(wú)法遠(yuǎn)程訪問(wèn)的情況
這篇文章主要介紹了解決postgresql無(wú)法遠(yuǎn)程訪問(wèn)的情況，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
PostgreSQL 數(shù)據(jù)同步到ES 搭建操作
這篇文章主要介紹了PostgreSQL 數(shù)據(jù)同步到ES 搭建操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
深入解讀PostgreSQL中的序列及其相關(guān)函數(shù)的用法
這篇文章主要介紹了PostgreSQL中的序列及其相關(guān)函數(shù)的用法,包括序列的更新和刪除等重要知識(shí),需要的朋友可以參考下2016-01-01
PostgreSQL 使用raise函數(shù)打印字符串
這篇文章主要介紹了PostgreSQL 使用raise函數(shù)打印字符串，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-01-01
postgresql行轉(zhuǎn)列與列轉(zhuǎn)行圖文教程
PostgreSQL是一種開(kāi)源的關(guān)系型數(shù)據(jù)庫(kù),它提供了多種管理工具來(lái)操作數(shù)據(jù)庫(kù),下面這篇文章主要給大家介紹了關(guān)于postgresql行轉(zhuǎn)列與列轉(zhuǎn)行的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06
PostgreSQL 查看數(shù)據(jù)庫(kù)，索引，表，表空間大小的示例代碼
PostgreSQL 提供了多個(gè)系統(tǒng)管理函數(shù)來(lái)查看表，索引，表空間及數(shù)據(jù)庫(kù)的大小，下面詳細(xì)介紹一下2013-08-08
postgresql處理空值NULL與替換的問(wèn)題解決辦法
由于在不同的語(yǔ)言中對(duì)空值的處理方式不同,因此常常會(huì)對(duì)空值產(chǎn)生一些混淆,下面這篇文章主要給大家介紹了關(guān)于postgresql處理空值NULL與替換的問(wèn)題解決辦法,需要的朋友可以參考下2024-02-02