rank() over
over 分析函数和聚合函数的区别: 聚合函数只返回一条数据, 而分析函数返回的是多条数据
1.rank() over(partiton by X order by Z desc)(分组排序序号)
2.sum(sal) over (partition by deptno) 按部门求薪酬总和
3.row_number() over (partition by col1 order by col2) 表示根据col1分组,在分组内部根据col2排序,而此函数partition by 可省略不写,这样就是全局排序
pgsql
# 根据a字段进行排名 (相同值并列)
SELECT a,b,c,rank() OVER (ORDER BY a) m FROM test;
# 根据b字段组内按a排名
SELECT a,b,c,rank() OVER (partition by b ORDER BY a) m FROM test;
# 根据b字段组内排名-连续排名
SELECT a,b,c,row_number() OVER (partition by b ORDER BY a) m FROM test;