postgres row_number()分析

今天看唐成书看到一段语句不明白什么意思

select row_number() over (partition by id),ctid from t

以前依稀记得 见过  over( partiontion by  xxx) 类似的语法，但是工作中还未发现有什么sql会写不出来，以为自己用不到，居然没太关注。

不过书上别人这么写了，要是看不懂还是显得略微丢人了。

所以细细分析如下。

执行脚本

create table t1(
 c1 int4
);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
insert into t1 values(4);
insert into t1 values(5);
insert into t1 values(6);
insert into t1 values(7);
insert into t1 values(8);
insert into t1 values(9);
insert into t1 values(10);
insert into t1 values(1);
insert into t1 values(1);
insert into t1 values(1);

看到row_number()第一反应就是ORACLE中rownum，遂执行查询报错

那只能加上over执行

可见row_number()是一个分区后的排序，那么如何用到当成ORACLE的rownum使用呢？

可以这样？

select * from (select row_number() over (partition by 1) as rownum from t1 ) t1 where rownum =1

后来查询文档，发现一句说明 “在只有一个包含了所有行的分区情况下，也可以省略PARTITION BY。”

前半句我没看懂，但是他说可以省略PARTITION BY，原来不可以省略over但是可以省略PARTITION BY

select * from (select row_number() over () as rownum from t1 ) t1 where rownum =1

一样是没问题的，虽然写法不一样，但是对比了执行计划是一样的。

后面又测试了一些rank之类的函数。不一一分析了，测试语句贴一下好了。可以注意看执行计划；

select row_number() over (partition  by t1), ctid,c1 from t1 
select rank() over (),c1 from t1 
select rank() over (partition by t1),c1 from t1 
select row_number() over (partition by c1),rank() over (partition by 1 order by c1),c1 from t1 
select row_number() over (partition by c1),rank() over (order by c1),c1 from t1 order by c1 asc