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' 

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

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

SELECT DISTINCT 列名称 FROM 表名称

select DISTINCT fee_date from new_order

select DISTINCT fee_date,order_status from new_order

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必须放在开头

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 ] ) 

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('周润发','语文',89); 
 insert into student (name,course,score) values('周润发','数学',99); 
 insert into student (name,course,score) values('周润发','外语',67); 
 insert into student (name,course,score) values('周润发','物理',77); 
 insert into student (name,course,score) values('周润发','化学',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);

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

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

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 | 周润发 | 化学 | 87 | 1
 13 | 黎明 | 外语 | 95 | 1
 2 | 周润发 | 数学 | 99 | 1
 14 | 黎明 | 物理 | 90 | 1
 6 | 周星驰 | 语文 | 91 | 1
(5 rows)

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 | 周润发 | 外语 | 67 | 1
 12 | 黎明 | 数学 | 65 | 1
 9 | 周星驰 | 物理 | 68 | 1
 11 | 黎明 | 语文 | 85 | 1
(5 rows)