分析函数汇总

count,avg,sum,max,min的聚合函数分别会有相同的情况：

over() 总数

 over(order by eage) 递加

over(partition by esex) 分组

over(partition by esex order by eage) 分组递加

1、count() over()  ：统计分区中各组的行数，partition by 可选，order by 可选

select ename,esex,eage,count(*) over() from emp; --总计数

select ename,esex,eage,count(*) over(order by eage) from emp; --递加计数

select ename,esex,eage,count(*) over(partition by esex) from emp; --分组计数

select ename,esex,eage,count(*) over(partition by esex order by eage) from emp;--分组递加计数

2、sum() over()  ：统计分区中记录的总和，partition by 可选，order by 可选

select ename,esex,eage,sum(salary) over() from emp; --总累计求和

select ename,esex,eage,sum(salary) over(order by eage) from emp; --递加累计求和

select ename,esex,eage,sum(salary) over(partition by esex) from emp; --分组累计求和

select ename,esex,eage,sum(salary) over(partition by esex order by eage) from emp; --分组递加累计求和

3、avg() over()  ：统计分区中记录的平均值，partition by 可选，order by 可选

select ename,esex,eage,avg(salary) over() from emp; --总平均值

select ename,esex,eage,avg(salary) over(order by eage) from emp; --递加求平均值

select ename,esex,eage,avg(salary) over(partition by esex) from emp; --分组求平均值

select ename,esex,eage,avg(salary) over(partition by esex order by eage) from emp; --分组递加求平均值

4、min() over() ：统计分区中记录的最小值，partition by 可选，order by 可选

     max() over() ：统计分区中记录的最大值，partition by 可选，order by 可选

select ename,esex,eage,salary,min(salary) over() from emp; --求总最小值

select ename,esex,eage,salary,min(salary) over(order by eage) from emp; --递加求最小值

select ename,esex,eage,salary,min(salary) over(partition by esex) from emp; --分组求最小值

select ename,esex,eage,salary,min(salary) over(partition by esex order by eage) from emp; --分组递加求最小值

select ename,esex,eage,salary,max(salary) over() from emp; --求总最大值

select ename,esex,eage,salary,max(salary) over(order by eage) from emp; --递加求最大值

select ename,esex,eage,salary,max(salary) over(partition by esex) from emp; --分组求最大值

select ename,esex,eage,salary,max(salary) over(partition by esex order by eage) from emp; --分组递加求最大值