oracle over()函数使用--与聚合函数连用？

over()开窗函数，可在同一行返回查询列和汇总值，常与sum、count、avg、max、min等聚合函数和row_number、rank()、dense_RANK排序函数连用，用于返回，汇总值、累计汇总值、组内汇总值，组内累计值等，功能十分强大。

一、建表及数据准备

create table test15 (team int,name varchar2(20),sal number);
 insert into test15 values('1','张三','1000');
 insert into test15 values('2','李四','2000');
 insert into test15 values('3','王五','3000');
 insert into test15 values('4','赵六','3000');
 insert into test15 values('5','周七','6000');
 commit;

二、聚合函数
1、与sum连用
    1.1 sum()over(),最后一列，返回汇总值

select t.*,sum(sal) over() from test15 t;

    1.2 sum()over(partition by),最后一列，按partition by 后的字段分组，返回组内总值

select t.*,sum(sal) over(partition by team) from test15 t;

    1.3 sum()over(order by),最后一列，按order by 后的字段排序，返回累计值

select t.*,sum(sal) over(order by team) from test15 t;

    1.4 sum()over(partition by order by),最后一列，按partition by 后的字段分组，order by 后的字段排序，返回组内累计值

2、与count连用
    2.1 count()over(),最后一列，返回总数

  select t.*,count(name) over() from test15 t;

    2.2 count()over(partition by),最后一列，按partition by 后的字段分组，返回组内汇总个数

select t.*,count(name) over(partition by team) from test15 t;

    2.3 count()over(order by),最后一列，按order by 后的字段排序，返回累计个数

select t.*,count(name) over(order by team) from test15 t;

    2.4 count()over(partition by order by),最后一列，按partition by 后的字段分组，order by 后的字段排序，返回组内累计个数

select t.*,count(name) over(partition by team order by team) from test15 t;

3、与avg连用
    3.1 avg()over(),最后一列，返回总平均值

select t.*,avg(sal) over() from test15 t;

    3.2 avg()over(partition by),最后一列，按partition by 后的字段分组，返回组内平均值

select t.*,avg(sal) over(partition by team) from test15 t;

    3.3 avg()over(order by),最后一列，按order by 后的字段排序，返回累计平均

 select t.*,avg(sal) over(order by team) from test15 t;

    3.4 avg()over(partition by order by),最后一列，按partition by 后的字段分组，order by 后的字段排序，返回组内累计平均
 

select t.*,avg(sal) over(partition by team order by team) from test15 t;

4、与max连用
    4.1 max()over(),最后一列，返回所有数据中的最大值

select t.*,max(sal) over() from test15 t;

    4.2 max()over(partition by),最后一列，按partition by 后的字段分组，返回组内最大值

select t.*,max(sal) over(partition by team) from test15 t;

    4.3 max()over(partition by order by),最后一列，按partition by 后的字段分组，order by 后的字段排序，返回组内累计平均

 select t.*,max(sal) over(partition by team order by team) from test15 t;

5、与min连用，与上一个类似。

ps:排序函数在下一章讲解