SQL使用技巧之窗口函数

什么是窗口函数

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

写法

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

partition by 可省略.

作用

• 组内排序 (分组后每组内排序  窗口函数为 :  rank() ,  dense_rank() , row_number())    
• 移动计算集合函数 (窗口函数有 : max() , sum() , avg() , min(), count())

组内排序

建立score 表 如下:

要求 :  每个班级内 按成绩排序 ,  SQL如下 : 

select stu_id 学号,
       room_id 班级,
       score 成绩,
       rank() over(partition by room_id order by score desc) as 排名1
  from score2;

结果 :  

rank() ,  dense_rank() , row_number() 三个函数区别? 

举例 SQL 如下:

select stu_id 学号,
       room_id 班级,
       score 成绩,
       rank() over( order by score desc) as 排名1,
       dense_rank() over( order by score desc) as 排名2,
       row_number() over( order by score desc) as 排名3
  from score2;

结果 : 

移动计算集合函数

SQL如下:

select 
       stu_id 学号,
       room_id 班级,
       score 成绩,
       sum(score) over(order by stu_id) as cur_sum,
       avg(score) over(order by stu_id) as cur_avg,
       count(score) over(order by stu_id) as cur_count,
       max(score) over(order by stu_id) as cur_max,
       min(score) over(order by stu_id) as cur_min
from score2; 

结果如下:

由此可知 :

聚合函数作为窗口函数, 是 以自身记录为准, 统计自身及自身以上的数据, 进行聚合统计.

比如 : 学号为 002 的, 统计的是 002 + 001 的数据

          学号为 003 的, 统计的是 003 + 002 + 001 的数据

          .......

移动聚合

聚合范围空指定

指定前三行数据进行聚合(自身行 + 上一行 + 上上行)

select 
       stu_id 学号,
       room_id 班级,
       score 成绩,
       sum(score) over(order by stu_id rows 2 preceding) as cur_sum
from score2;  

结果如下 : 

关键字 : 

         rows  : 取多少行

         preceding : 表示之前(以自身行为标准)

         following : 表示之后

rows between 1 preceding and 1 following  :  表示 前一行 + 自身 + 后一行