oracle分析函数-开窗函数

oracle分析函数

分析函数是什么？

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

分析函数和聚合函数的不同之处是什么？

普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

分析函数-开窗函数的写法

1.函数写法：
OVER(PARTITION BY ‘x’ ORDER BY ‘y’)——按照y排序进行累计，ORDER BY是个默认的开窗函数，按照x进行分区。
2.开窗的窗口范围：
  OVER(ORDER BY ‘x’ RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING)——窗口范围为当前行数据幅度减3加3后的范围内的，可以看作逻辑范围。
  OVER(ORDER BY ‘x’ ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)——窗口范围为当前行前后各移动3行，可以看作物理范围。

oracle开窗函数实例

查询表格数据sql:

SELECT C.XH "学号",C.XSXM "姓名",C.JXBMC "班级课程",C.FS "分数" 
FROM T_JW_XSCJ_FB C WHERE C.JXB = '10812762351216885761' AND C.KSPC = '1104146533416173568'

表格数据

一次查询高三一班数学平均分及排名；使用开窗函数示例sql:

SELECT C.XH "学号",C.XSXM "姓名",C.JXBMC "班级课程",C.FS "分数",ROUND(AVG(C.FS)OVER(PARTITION BY C.JXBMC),1) "平均分",
RANK() OVER(PARTITION BY C.JXBMC ORDER BY C.FS DESC) "排名" 
FROM T_JW_XSCJ_FB C WHERE C.JXB = '10812762351216885761' AND C.KSPC = '1104146533416173568'

查询结果：

下面还有很多用法和分组函数大致相同，就不一一列举了，简单介绍一下，和上面用法类似：
rank()over(partition by …order by …)：跳跃排序，有两个第二名时接下来就是第四名
dense_rank()over(partition by …order by …)：连续排序，有两个第二名时仍然跟着第三名
sum()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行数据。