Oracle 中 over() 函数

over() 函数功能：

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

 

分析函数和聚合函数的区别：

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

 

over() 函数语法：

over() 分析函数经常与 分组、 排序、 窗口三种子句结合使用。同时，over() 分析函数还经常与 sum()、rank()、min() 等函数结合使用。

注意：分组、排序、窗口三种子句可 自由结合。

1.分组

分组语句格式为 over(partition by partition_col)

通过分组子句，将可以基于每条记录中对应的分区字段，对数据进行分组。随后基于分组，结合  sum()、rank()、min() 等 函数进行运算。

例如：查找对每个员工的工资，并显示其所在部门的最高工资。

SQL>  SELECT userid, salary, deptid, sum(salary) over(partition by deptid)

FROM salary;

2.排序

分组语句格式为  over(order by order_col)

通过排序子句，将可以基于每条记录中对应的排序字段，做以排序字段值为边界的排序（每条记录的排序可能不同）。随后基于排序，结合   sum()、rank()、min() 等 函数进行运算。

例如：查找对每个员工的工资，并显示不高于其工资的总工资和。

SQL> SELECT userid, salary, deptid, sum(salary) over(order by salary asc)

FROM salary;

例如：查找对每个员工的工资，并显示不低于其工资的总工资和。

SQL> SELECT userid, salary, deptid, sum(salary) over(order by salary desc)

FROM salary;

3.窗口

分组语句格式为 over(order by order_col [rows | range] between xxx preceding and yyy following)

首先通过排序子句，将可以基于每条记录中对应的排序字段，做以排序字段值为中心， xxx 值为向前范围， yyy 为向后范围的排序（每条记录的排序可能不同）。随后基于排序，结合 sum()、rank()、min() 等函数进行运算。

注意：建设排序字段值为 a，则窗口的大小为 ( a - xxx ) <= range <= ( a + yyy )。

例如：查找对每个员工的工资，并显示按工资排序，工资比他低的前两位员工与其自身的工资和。

SQL>  SELECT userid, salary, deptid, sum(salary) over(partition by deptid order by salary rows BETWEEN 2 preceding AND 0 following)

FROM salary;

 

例如：查找对每个员工的工资，并显示比他工资少 800 元以内（含 800 元），且不高于他工资的工资和。

SQL>  SELECT userid, salary, deptid, sum(salary) over(partition by deptid order by salary range BETWEEN 800 preceding AND 0 following)

FROM salary;

 

 

 

 

转载于:https://www.cnblogs.com/autopenguin/p/6736571.html