概念

窗口的意思是将数据进行分组，每个分组即是一个窗口，这和使用聚合函数时的group by分组类似，通过 over 关键字和 partition by 关键字来定义分组，但与聚合函数不同的地方是：

  • 聚合函数（例如：sum/avg/min/max）会针对每个分组（窗口）聚合出一个结果（每一组返回一个结果）。
    窗口函数会对每一条数据进行计算，并不会使返回的数据变少（每一行返回一个结果）。
  • 窗口函数会逐行计算，其重点是计算当前行与窗口内其他成员之间的关系，例如：组内排序，累积分布等。
1
2
3
[你要的操作] OVER ( PARTITION BY  <用于分组的列名>
                    ORDER BY <按序叠加的列名> 
                    ROWS <窗口滑动的数据范围> )

<窗口滑动的数据范围> 用来限定[ 你要的操作] 所运用的数据的范围，具体有如下这些：

1
2
3
4
5
6
当前行 - current row
之前的行 - preceding
之后的行 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following

举例：

1
2
3
4
取当前行和前五行：ROWS between 5 preceding and current row --共6行
取当前行和后五行：ROWS between current row and 5 following --共6行
取前五行和后五行：ROWS between 5 preceding and 5 folowing --共11行

聚合窗口函数

sum

排序窗口函数

rank、dense_rank、row_number

1
2
3
4
5
6
select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num
from 班级;

结果：
func

rank:结果相同的会占用一名
dense_rank:结果相同的不占用一名
row:记录行

示例：TopN问题

1
2
3
4
5
6
7
select *
from (
   select *, 
          row_number() over (partition by 要分组的列名
                       order by 要排序的列名 desc) as 排名
   from 表名) as a
where 排名 <= N;

参考：

猴子数据分析