postgre函数001—row_number()

ROW_NUMBER（）定义

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

备注：ORDERBY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数：<partition_by_clause> ：将FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>：确定将 ROW_NUMBER 值分配给分区中行的顺序。
返回类型：bigint 。

ROW_NUMBER实现

row_number(): 组内排序，不间断,如1，2，3，4，5，6

对比函数：

rank() : 组内排序，会间断，如1，2，2，4，5，6
dense_rank(): 组内排序，但会重复，如1，2，2，3，4，5

操作表

DROP TABLE IF EXISTS "test"."empsalary";
CREATE TABLE "test"."empsalary" (
  "depname" varchar(255) COLLATE "pg_catalog"."default",
  "empno" int4,
  "salary" int4,
  "enroll_date" date
)
;

INSERT INTO "test"."empsalary" VALUES ('develop', 10, 5200, '2007-08-01');
INSERT INTO "test"."empsalary" VALUES ('sales', 1, 5000, '2006-10-01');
INSERT INTO "test"."empsalary" VALUES ('personnel', 5, 3500, '2007-12-10');
INSERT INTO "test"."empsalary" VALUES ('sales', 4, 4800, '2007-08-08');
INSERT INTO "test"."empsalary" VALUES ('sales', 6, 5500, '2007-01-02');
INSERT INTO "test"."empsalary" VALUES ('personnel', 2, 3900, '2006-12-23');
INSERT INTO "test"."empsalary" VALUES ('develop', 7, 4200, '2008-01-01');
INSERT INTO "test"."empsalary" VALUES ('develop', 9, 4500, '2008-01-01');
INSERT INTO "test"."empsalary" VALUES ('sales', 3, 4800, '2007-08-01');
INSERT INTO "test"."empsalary" VALUES ('develop', 8, 6000, '2006-10-01');
INSERT INTO "test"."empsalary" VALUES ('develop', 11, 5200, '2007-08-15');

执行语句

select depname,salary,row_number() over(partition by depname order by salary) sort_by_salary from empsalary; 
--根据depname 来对数据进行分组，然后根据salary来实现数据排序，由低到高

运行结果