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

工具: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)

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

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

(0)

相关推荐

  • postgresql 计算两点距离的2种方法小结

    postgresql计算两点距离 下面两种方法: select ST_Distance( ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography, ST_SetSRID(ST_MakePoint(106.00231199774656,29.719258550486572),4326)::geography ), ST_Length( ST_MakeLine( ST_MakePoint(115.

  • PostgreSQL 更新视图脚本的注意事项说明

    项目最早是基于Oracle的,移植到PostgreSQL后,本着尽量少修改的原则,创建/更新视图的脚本也沿用了Oracle风格的CREATE OR REPLACE VIEW形式.但是每当要更新视图定义时,常常报"cannot change name of view column xxx to yyy"的错误,通常是在视图修改某字段名.中间增加字段.删除字段时发生. 究其原因,是PostgreSQL虽然支持CREATE OR REPLACE VIEW语义,却有着容易让人忽略的重要限制(O

  • PostgreSQL中enable、disable和validate外键约束的实例

    我就废话不多说了,大家还是直接看实例吧~ postgres=# create table t1(a int primary key,b text,c date); CREATE TABLE postgres=# create table t2(a int primary key,b int references t1(a),c text); CREATE TABLE postgres=# insert into t1 (a,b,c) values(1,'aa',now()); INSERT 0

  • PostgreSQL 修改视图的操作

    最近发现PostgreSQL(create or replace) 修改视图有很多的限制,不像SQL Server和Oracle那样可以随意修改. 错误提示 修改视图原有字段 ERROR: cannot change name of view column "user_id" to "?column?" 删除视图原有字段 ERROR: cannot drop columns from view 找到如下原因,内容原文 究其原因,是PostgreSQL虽然支持CREA

  • postgresql 计算距离的实例(单位直接生成米)

    之前用的是ST_Distance 函数,但是貌似需要进行一次单位的转换,而且网上有说那种转换不是特别准确,现在暂时将该算法记录在此: select st_distance(ST_GeomFromText('POINT(120.451737 36.520975)',900913),ST_GeomFromText('POINT(120.455636 36.520885)',900913))*60*1.852; 这里的计算方式倒是可以换坐标系,但是,测试了两个坐标系都没有起作用.而且该种方式转换过单位

  • Postgresql 动态统计某一列的某一值出现的次数实例

    实例解析: select to_char(log.date, 'yyyy-MM-dd HH24') as hour, log.exten, sum(case log.grade when '1' then 1 else 0 end) as "1", sum(case log.grade when '2' then 1 else 0 end) as "2", sum(case log.grade when '3' then 1 else 0 end) as "

  • PostgreSQL 查看表的主外键等约束关系详解

    我就废话不多说了,大家还是直接看代码吧~ SELECT tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name, tc.is_deferrable,tc.initially_deferred FROM information_schema.table_constraints AS tc JOIN

  • 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_ord

  • PostgreSQL 中字段类型varchar的用法

    PostgreSql数据库中varchar类型与sql server中字段用法有差别,PostgreSql中如果字段设置为varchar类型长度为10,则无论存字母.数字或其它符号,长度最大为10个,也就是字母和汉字占的位置是一样的. Sql server中如设置字段类型为nvarchar类型长度为10,则存汉字最大为5个,字母为10个,字母加汉字混合时,字母和汉字占的长度一样 补充:Postgresql 数据库 varchar()字符占用多少字节 如下所示: create table tmp1

  • 详细讲解PostgreSQL中的全文搜索的用法

    开发Web应用时,你经常要加上搜索功能.甚至还不知能要搜什么,就在草图上画了一个放大镜. 搜索是项非常重要的功能,所以像elasticsearch和SOLR这样的基于lucene的工具变得很流行.它们都很棒.但使用这些大规模"杀伤性"的搜索武器前,你可能需要来点轻量级的,但又足够好的搜索工具. 所谓"足够好",我是指一个搜索引擎拥有下列的功能: 词根(Stemming) 排名/提升(Ranking / Boost) 支持多种语言 对拼写错误模糊搜索 方言的支持 幸运

  • postgreSQL中的case用法说明

    工具:postgreSQL Navicat Premium 又一次在看代码的时候,发现了不懂的东西! 这次就是case when then SQL CASE表达式是一种通用的条件表达式,类似于其它语言中的if/else语句. CASE WHEN condition THEN result [WHEN ...] [ELSE result] END 解释: condition是一个返回boolean的表达式. 如果为真,那么CASE表达式的结果就是符合条件的result. 如果结果为假,那么以相同方

  • PostgreSQL ROW_NUMBER() OVER()的用法说明

    语法: 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 vary

  • Oracle中ROW_NUMBER()OVER()函数用法实例讲解

    目录 1. 说明: 2. 原理: 3.语法: 4.示例一: 5. 示例二 总结 Oracle中ROW_NUMBER() OVER()函数用法 1. 说明: ROW_NUMBER() OVER() 函数的作用:分组排序 2. 原理: row_number() over() 函数,over() 里的分组以及排序的执行晚于 where.group by.order by 的执行. 3.语法: row_number() over( partition by 分组列 order by 排序列 desc )

  • postgresql中wal_level的三个参数用法说明

    wal_level中有三个主要的参数:minimal.archive和hot_standby 1.minimal是默认的值,它仅写入崩溃或者突发关机时所需要的信息(不建议使用). 2.archive是增加wal归档所需的日志(最常用). 3.hot_standby是在备用服务器上增加了运行只读查询所需的信息,一般实在流复制的时候使用到. 补充:postgresql WAL相关参数 配置文件 # - Settings - wal_level = minimal # minimal, replica

  • 浅谈PostgreSQL中的孤儿文件用法(orphaned data files)

    创建一个测试表 postgres=# create table t1(a int); CREATE TABLE postgres=# select pg_relation_filepath('t1'); pg_relation_filepath ---------------------- base/75062/75297 (1 row) postgres=# 在操作系统上已经可以看到该文件. $ ls -la $PGDATA/base/75062/75297 -rw------- 1 post

  • postgresql 中round函数的用法说明

    round函数是去小数点,我查过好多方法并不好用,于是有了 round(cast(a/b as numeric), 2) 这个就可以用,要加个cast才可以的 补充:postgresql中to_char和round的混合使用 在postgresql中大家都用过round吧,当遇到小数的时候该如何显示呢?看例子 SELECT to_char(round(127 * 0.1 / 67543,6)*10000,'90.99') 1.88 SELECT to_char(round(127 * 0.1 /

  • PostgreSQL中的VACUUM命令用法说明

    每当PostgreSQL数据库中的表中的行被更新或删除时,死亡行会被遗留下来.VACUUM则会把它们除去来使空间能被重新利用.如果一个表没有被清空,它会变得臃肿,浪费磁盘空间而且会降低顺序表扫描的速度,而且在较小范围内也会降低索引扫描的速度. VACUUM命令只可以移除这些不再被需要的行版本(也被称为元组).如果被删除事务的事务ID(存储在xmax系统列中)比仍然活跃在PostgreSQL数据库(或者共享表的整个集群)中最老的事务(xmin界限)更老,那么这个元组将不再被需要. 注意以下三种情况

随机推荐