详细可以参考,介绍的很详细:/PostgreSQL’s 文档/
一、简介
-
窗口函数:
也称为OLAP(Online Analytical Processing)函数
对数据库数据就行实时分析处理<窗口函数> OVER ( [PARTITION BY <列清单>] ODER BY <排序用列清单> )
-
窗口函数分类:
窗口函数分为排名函数、分布函数、偏移函数等。
二、用法简介
- 排名函数:
ROW_NUMBER、NTILE、RANK和DENSE_RANK 。在标准中,前两者是一类,后两者是另一类。
-
ROW_NUMBER()
ROW_NUMBER函数根据指定的顺序,从1开始计算连续的行号。
。语法格式:
语法:ROW_NUMBER() OVER ( PARTITION BY COL1 ORDER BY COL2 ) 功能:表示根据COL1分组,在分组内部根据 COL2排序,而这个值就表示每组内部排序后的顺序编号(组内连续的唯一的) row_number() 返回的主要是“行”的信息,并没有排名
它会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。
在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。
1) 例1:按照SALARY排序
SELECT ID
,NAME
,AGE
,SALARY
,ROW_NUMBER()OVER(ORDER BY SALARY DESC) RN
FROM TEST_ROW_NUMBER_OVER t
结果:
2)例2:按照ID分组 、SALARY排序
SELECT ID
,NAME
,AGE
,SALARY
,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY SALARY DESC) RN
FROM TEST_ROW_NUMBER_OVER
结果:
3)每组第一
WHERE RN<2
-
RANK和DENSE_RANK
三个函数都是按照col1分组内从1开始排序 其中,ROW_NUMBER() 是没有重复值的排序(即使两条记录相同,序号也不重复的),不会有同名次。 DENSE_RANK() 是连续的排序,两个第二名仍然跟着第三名。 RANK() 是跳跃排序,两个第二名下来就是第四名。
对比例:
SELECT s.names
,s.salaries
,dept
, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salaries DESC) rank
FROM workers s;
SELECT s.names
, dept
, s.salaries
, DENSE_RANK() OVER(PARTITION BY dept ORDER BY salaries DESC) rank
FROM workers s;
结果如下,相同salaries时,RANK有重复,但连续。
SELECT s.names
, dept
,s.salaries
, RANK() OVER(PARTITION BY dept ORDER BY salaries DESC) rank
FROM workers s;
结果如下,相同salaries时,RANK有重复,同时不连续。
- 关于Parttion by:
Parttion by关键字是Oracle中分析性函数的一部分,用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而Group by是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。
TIPS:
使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果。
可以这样: rank over(partition by course order by score desc nulls last)
- 偏移函数
LAG,LEAD 分别是向前,向后;
语法:
LEAD ( scalar_expression ,[ offset ] , [ default ] ) OVER ( PARTITION BY COL1 ORDER BY COL2 )
lag 和lead 有三个参数,第一个参数是列名,第二个参数是偏移的offset(偏移多少行,如果不写,默认一行),第三个参数是 超出记录窗口时的默认值 (如果不写,默认为null)
例:
SELECT *,
LEAD(Value, 1, 666) OVER (ORDER BY Value) AS LEADVALUE, --提前1行,默认值666
LAG(Value, 2, 888) OVER (ORDER BY Value) AS LAGVALUE --滞后2行,默认值888
FROM #T;
结果:
不写偏移数和默认值,结果就是这样的:
SELECT *,
LEAD(Value) OVER (ORDER BY Value) AS LEADVALUE, --提前1行
LAG(Value) OVER (ORDER BY Value) AS LAGVALUE --滞后1行
FROM #T;
结果: