SQL——窗口函数汇总

本文环境：Redshift库，使用pgsql

官网链接：Amazon Redshift——窗口函数

1. 什么是窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

标准窗口函数语法：
function (expression) OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list [ frame_clause ] ] )

在窗口函数的基本语法中，最重要的是partition by，partition by划分的范围被称为窗口，这也是窗口函数的由来。

2. 窗口函数应用

• 分组排序
• Top N
• 累计指标计算

3. 窗口函数实例

( 一 ) 排名函数：rank()、dense_rank()、row_number()、ntile()

select studentno,score,
rank() over(order by score desc) as rank_rank,
dense_rank() over(order by score desc) as rank_dense_rank,
row_number() over(order by score desc) as rank_row_number,
ntile(3) over(order by score desc) as rank_ntile --排序并分组
from 
(
select '001' as studentno,98 as score 
union all
select '002' as studentno,94 as score 
union all 
select '003' as studentno,96 as score 
union all 
select '004' as studentno,88 as score 
union all 
select '005' as studentno,88 as score 
union all 
select '006' as studentno,75 as score 
union all 
select '007' as studentno,89 as score 
union all 
select '008' as studentno,88 as score 
)

查询结果：

( 二 ) 聚合函数：max()、min()、sum()、avg()、count()

select studentno,score,
max