SQL 窗口函数

个人学习笔记

均值

, AVG(close) OVER (partition BY instrument ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS close_ma5

排序

ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY SCORE DESC) 
只取排序用ROW_NUMBER() OVER ()
rank() over (order by 成绩 desc) as ranking
dense_rank() over (order by 成绩 desc) as dese_rank,

聚合函数

sum(col2) over (partition by col1 order by col2) as current_sum,
avg(col2) over (partition by col1 order by col2) as current_avg,
count(col2) over (partition by col1 order by col2) as current_count,
max(col2) over (partition by col1 order by col2) as current_max,
min(col2) over (partition by col1 order bycol2) as current_min