【Oracle笔记】OVER (PARTITION BY)函数的用法及实例解析

  开窗函数，Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。   开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

1、over函数的写法

  over（partition by class order by sroce） 按照sroce排序进行累计，order by是个默认的开窗函数，按照class分区。

2、开窗的窗口范围

  over（order by sroce range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的。   over（order by sroce rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。

3、与over()函数结合的函数的介绍

（1）查询每个班的第一名的成绩   rank()和dense_rank()可以将所有的都查找出来，rank可以将并列第一名的都查找出来；rank()和dense_rank()区别：rank()是跳跃排序，有两个第二名时接下来就是第四名。

SELECT *
  FROM (SELECT t.name,
               t.class,
               t.score,
               RANK () OVER (PARTITION BY t.class ORDER BY t.score DESC) mm
          FROM st_score t)
 WHERE mm = 1;

输出：

张三	1	100	1
赵七	2	99	1
王二	2	99	1
丽丽	3	96	1

（2）在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果。

SELECT *
  FROM (SELECT t.name,
               t.class,
               t.score,
               row_number () OVER (PARTITION BY t.class ORDER BY t.score DESC) mm
          FROM st_score t)
 WHERE mm = 1;

输出：

张三	1	100	1
赵七	2	99	1
丽丽	3	96	1

（3）其他用法

• sum() over(partition by … order by …)：求分组后的总和。
• first_value() over(partition by … order by …)：求分组后的第一个。
• last_value() over(partition by … order by …)：求分组后的最后一个。
• count() over(partition by … order by …)：求分组后的总数。
• max() over(partition by … order by …)：求分组后的最大值。
• min() over(partition by … order by …)：求分组后的最小值。
• avg() over(partition by … order by …)：求分组后的平均值。
• lag() over(partition by … order by …)：取出前n行数据。
• lead() over(partition by … order by …)：取出后n行数据。