乘风原创程序

  • postgreSQL中的row_number() 与distinct用法说明
  • 2021/1/26 15:02:37
  • 我就废话不多说了,大家还是直接看代码吧~

    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执行所得到的数据是一样的!

    工具:postgresql

    1.我们要清楚,sql的执行顺序:

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

    2.row_number()分析函数

    说明:返回结果集分区内行的序列号,每个分区的第一行从 1 开始。

    语法:row_number () over ([ <partition_by_clause>]<order_by_clause> )

    备注:orderby 子句可确定在特定分区中为行分配唯一 row_number 的顺序。

    参数:<partition_by_clause> :将from 子句生成的结果集划入应用了 row_number 函数的分区。

    <order_by_clause>:确定将 row_number 值分配给分区中行的顺序。

    返回类型: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排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

    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('周润发','语文',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);

    1. 根据分数排序

    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)

    rn是给我们的一个排序。

    2. 根据科目分组,按分数排序

    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)

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

    只要在根据科目排序的时候按低到高顺序排列就好了。

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