postgreSQL中的row_number() 與distinct用法說明

更新時間：2021年01月26日 10:48:05 作者：安善良民弱女子

這篇文章主要介紹了postgreSQL中的row_number() 與distinct用法說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

我就廢話不多說了，大家還是直接看代碼吧~

select count(s.*)
from ( 
 select *, row_number() over (partition by fee_date order by fee_date) as gr 
 from new_order where news_id='novel' and order_status='2' 
) s
where s.gr = 1 
SELECT count(DISTINCT fee_date) as dis from new_order where news_id='novel' and order_status='2'

這兩個SQL執(zhí)行所得到的數據是一樣的！

工具：postgreSQL

1.我們要清楚，sql的執(zhí)行順序：

from語句->where語句->group by語句->having語句->order by語句->select 語句

2.row_number()分析函數

說明：返回結果集分區(qū)內行的序列號，每個分區(qū)的第一行從 1 開始。

語法：ROW_NUMBER () OVER ([ <partition_by_clause>]<order_by_clause> )

備注：ORDERBY 子句可確定在特定分區(qū)中為行分配唯一 ROW_NUMBER 的順序。

參數：<partition_by_clause> ：將FROM 子句生成的結果集劃入應用了 ROW_NUMBER 函數的分區(qū)。

<order_by_clause>：確定將 ROW_NUMBER 值分配給分區(qū)中行的順序。

返回類型：bigint 。

row_number()從1開始，為每一條分組記錄返回一個數字

select *, row_number() over (order by fee_date) from new_order

先把 fee_date 升序排列，再為升序以后的每條記錄返回一個序號

select *, row_number() over (partition by fee_date order by fee_date) as gr from new_order

表示根據fee_date分組，在分組內部根據 fee_date排序，而此函數計算的值就表示每組內部排序后的順序編號（組內連續(xù)的唯一的)

2.distinct

語法：

SELECT DISTINCT 列名稱 FROM 表名稱

distinct這個關鍵字用來過濾掉多余的重復記錄只保留一條

select DISTINCT fee_date from new_order

select DISTINCT fee_date,order_status from new_order

從結果可以看出，是根據“fee_date+order_status”來去重復數據的，distinct同時作用在了fee_date和order_status上

SELECT count(DISTINCT fee_date) as dis from new_order where news_id='novel' and order_status='2'

select id,distinct fee_date from new_order ; –會提示錯誤，因為distinct必須放在開頭

distinct語句中select顯示的字段只能是distinct指定的字段，其他字段是不可能出現的

補充：PostgreSQL ROW_NUMBER() OVER()

我就廢話不多說了，大家還是直接看代碼吧~

SELECT
	*
FROM
	(
		SELECT
			tt.s_ci s_ci,
			sm.ci,
-- 			getdistance (
-- 				tt.longitude,
-- 				tt.latitude,
-- 				sm.longitude,
-- 				sm.latitude
-- 			) distance,
			ROW_NUMBER () OVER (
				PARTITION BY tt.s_ci
				ORDER BY
					getdistance (
						tt.longitude,
						tt.latitude,
						sm.longitude,
						sm.latitude
					)
			) rn
		FROM
			sm_cl_location sm
		INNER JOIN (
			SELECT
				s_ci,
				longitude,
				latitude,
				n3_pci,
				n3_earfcn
			FROM
				plan_ott_data
			WHERE
				1 = 1
			AND (
				s_ci = '460-00-1012286-2'
				OR s_ci = '460-00-25514-130'
			)
			AND rpt_time BETWEEN '2018-04-30'
			AND '2018-05-29'
		) tt ON sm.pci = tt.n3_pci
		AND sm.hannel_number = tt.n3_earfcn
	) T
WHERE
	T .rn BETWEEN 1 and 3

語法：

ROW_NUMBER() OVER( [ PRITITION BY col1] ORDER BY col2[ DESC ] )

解釋：

ROW_NUMBER()為返回的記錄定義個行編號， PARTITION BY col1 是根據col1分組，ORDER BY col2[ DESC ]是根據col2進行排序。

舉例：

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('周潤發(fā)','語文',89); 
 insert into student (name,course,score) values('周潤發(fā)','數學',99); 
 insert into student (name,course,score) values('周潤發(fā)','外語',67); 
 insert into student (name,course,score) values('周潤發(fā)','物理',77); 
 insert into student (name,course,score) values('周潤發(fā)','化學',87); 
 insert into student (name,course,score) values('周星馳','語文',91); 
 insert into student (name,course,score) values('周星馳','數學',81); 
 insert into student (name,course,score) values('周星馳','外語',88); 
 insert into student (name,course,score) values('周星馳','物理',68); 
 insert into student (name,course,score) values('周星馳','化學',83); 
 insert into student (name,course,score) values('黎明','語文',85); 
 insert into student (name,course,score) values('黎明','數學',65); 
 insert into student (name,course,score) values('黎明','外語',95); 
 insert into student (name,course,score) values('黎明','物理',90); 
 insert into student (name,course,score) values('黎明','化學',78);

1. 根據分數排序

postgres=# select *,row_number() over(order by score desc)rn from student;
 id | name | course | score | rn 
----+--------+--------+-------+----
 2 | 周潤發(fā) | 數學 | 99 | 1
 13 | 黎明 | 外語 | 95 | 2
 6 | 周星馳 | 語文 | 91 | 3
 14 | 黎明 | 物理 | 90 | 4
 1 | 周潤發(fā) | 語文 | 89 | 5
 8 | 周星馳 | 外語 | 88 | 6
 5 | 周潤發(fā) | 化學 | 87 | 7
 11 | 黎明 | 語文 | 85 | 8
 10 | 周星馳 | 化學 | 83 | 9
 7 | 周星馳 | 數學 | 81 | 10
 15 | 黎明 | 化學 | 78 | 11
 4 | 周潤發(fā) | 物理 | 77 | 12
 9 | 周星馳 | 物理 | 68 | 13
 3 | 周潤發(fā) | 外語 | 67 | 14
 12 | 黎明 | 數學 | 65 | 15
(15 rows)

rn是給我們的一個排序。

2. 根據科目分組，按分數排序

postgres=# select *,row_number() over(partition by course order by score desc)rn from student;
 id | name | course | score | rn 
----+--------+--------+-------+----
 5 | 周潤發(fā) | 化學 | 87 | 1
 10 | 周星馳 | 化學 | 83 | 2
 15 | 黎明 | 化學 | 78 | 3
 13 | 黎明 | 外語 | 95 | 1
 8 | 周星馳 | 外語 | 88 | 2
 3 | 周潤發(fā) | 外語 | 67 | 3
 2 | 周潤發(fā) | 數學 | 99 | 1
 7 | 周星馳 | 數學 | 81 | 2
 12 | 黎明 | 數學 | 65 | 3
 14 | 黎明 | 物理 | 90 | 1
 4 | 周潤發(fā) | 物理 | 77 | 2
 9 | 周星馳 | 物理 | 68 | 3
 6 | 周星馳 | 語文 | 91 | 1
 1 | 周潤發(fā) | 語文 | 89 | 2
 11 | 黎明 | 語文 | 85 | 3
(15 rows)

3. 獲取每個科目的最高分

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 | 周潤發(fā) | 化學 | 87 | 1
 13 | 黎明 | 外語 | 95 | 1
 2 | 周潤發(fā) | 數學 | 99 | 1
 14 | 黎明 | 物理 | 90 | 1
 6 | 周星馳 | 語文 | 91 | 1
(5 rows)

4. 每個科目的最低分也是一樣的

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 | 黎明 | 化學 | 78 | 1
 3 | 周潤發(fā) | 外語 | 67 | 1
 12 | 黎明 | 數學 | 65 | 1
 9 | 周星馳 | 物理 | 68 | 1
 11 | 黎明 | 語文 | 85 | 1
(5 rows)

只要在根據科目排序的時候按低到高順序排列就好了。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章:

postgresql查詢鎖表以及解除鎖表操作
這篇文章主要介紹了postgresql查詢鎖表以及解除鎖表操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2020-12-12
PostgreSQL報錯解決操作符不存在的問題
這篇文章主要介紹了PostgreSQL報錯解決操作符不存在的問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2021-01-01
PostgreSQL工具pgAdmin的介紹及使用
本文主要介紹了PostgreSQL工具pgAdmin的介紹及使用，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2022-07-07
PostgreSQL 禁用全表掃描的實現
這篇文章主要介紹了PostgreSQL 禁用全表掃描的實現操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2021-01-01
Postgresql限制用戶登錄錯誤次數的實例代碼
這篇文章主要介紹了Postgresql限制用戶登錄錯誤次數的實例代碼,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2021-03-03
postgresql合并string_agg函數的實例
這篇文章主要介紹了postgresql合并string_agg函數的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2021-01-01
PostgreSQL的日期時間差DATEDIFF實例詳解
PostgreSQL是一款簡介而又性能強大的數據庫應用程序,其在日期時間數據方面所支持的功能也都非常給力,下面這篇文章主要給大家介紹了關于PostgreSQL的日期時間差DATEDIFF的相關資料,需要的朋友可以參考下
2023-04-04
Postgresql 數據庫權限功能的使用總結
這篇文章主要介紹了Postgresql 數據庫權限功能的使用總結，具有很好的參考價值，對大家有所幫助。一起跟隨小編過來看看吧
2021-02-02
Windows下Postgresql數據庫的下載與配置方法
這篇文章主要介紹了Windows下Postgresql數據庫的下載與配置方法 ,需要的朋友可以參考下
2014-06-06
postgresql實現對已有數據表分區(qū)處理的操作詳解
這篇文章主要為大家詳細介紹了postgresql實現對已有數據表分區(qū)處理的操作的相關知識,文中的示例代碼講解詳細,感興趣的小伙伴可以跟隨小編一起學習一下
2023-12-12