分析函数格式(三部分组成)
函数名(参数) over(partition by 子句 order by 子句 rows/range子句)三部分组成
1: 函数名
比如:sum,avg,max,min等聚合函数或lead,lag行比较函数
2: over 关键字,说明是分析函数
3: 分析子句
over括号内的内容,包括三部分
(1): partition by 分组子句
(2): order by 排序子句 组内排序
(3): rows/range 窗口子句 必须和order by 子句同时使用,操作行集合
一:order by
(1): 没有order by
结果集的一行到最后一行
(2): 有order by
默认从第一行到当前分组最后一行
select e.*,sum(sal) over() sum_sal,
sum(sal) over(order by deptno) sum_ord_sal
from emp e;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SUM_SAL SUM_ORD_SAL
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- -----------
7782 CLARK MANAGER 7839 09-6月 -81 2450 10 30025 8750
7934 MILLER CLERK 7782 23-1月 -82 1300 10 30025 8750
7839 KING PRESIDENT 17-11月-81 5000 10 30025 8750
7566 JONES MANAGER 7839 02-4月 -81 2975 20 30025 19625
7369 SMITH CLERK 7902 17-12月-80 800 20 30025 19625
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 30025 19625
7902 FORD ANALYST 7566 03-12月-81 3000 20 30025 19625
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 30025 19625
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 30025 29025
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 30025 29025
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 30025 29025
7900 JAMES CLERK 7698 03-12月-81 950 30 30025 29025
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 30025 29025
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 30025 29025
1 aaa sales 7902 03-3月 -81 1000 30025 30025
二:排列
rank: 有多个并列名次时,下一名次会跳跃:如两个2,下一个是4
dense_rank 有多个并列名次时,正常排序。如两个2,下一个是3
row_number 当有两个并列时,名次不一样(有先后)
三: 窗口函数
1: row窗口
特点:可以order by多列,可以是任何类型
(1): rows 3 preceding
包括当前行和当前行的前3行记录在内,如果有partition by 就是在当前分组内
2: range 窗口
特点:只能是number,date类型,只支持order by一列
(1): range unbounded preceding
当前行和之前所有行包含进来
select deptno,ename,sal,
sum(sal) over(order by deptno range unbounded preceding) sumsal
from emp;
DEPTNO ENAME SAL SUMSAL
---------- ---------- ---------- ----------
10 CLARK 2450 8750
10 MILLER 1300 8750
10 KING 5000 8750
20 JONES 2975 19625
20 SMITH 800 19625
20 SCOTT 3000 19625
20 FORD 3000 19625
20 ADAMS 1100 19625
30 WARD 1250 29025
30 TURNER 1500 29025
30 ALLEN 1600 29025
30 JAMES 950 29025
30 MARTIN 1250 29025
30 BLAKE 2850 29025
aaa 1000 30025
#order by 默认是range between unbounded preceding and current row
select deptno,ename,sal,
sum(sal) over (order by deptno) sumsal
from emp;
DEPTNO ENAME SAL SUMSAL
---------- ---------- ---------- ----------
10 CLARK 2450 8750
10 MILLER 1300 8750
10 KING 5000 8750
20 JONES 2975 19625
20 SMITH 800 19625
20 SCOTT 3000 19625
20 FORD 3000 19625
20 ADAMS 1100 19625
30 WARD 1250 29025
30 TURNER 1500 29025
30 ALLEN 1600 29025
30 JAMES 950 29025
30 MARTIN 1250 29025
30 BLAKE 2850 29025
aaa 1000 30025
(3): range 100 preceding
假如over()里asc排列,意思是[number-100,number]这样一个闭区间是它的窗口。
假如over()里desc排列,意思是[number,number+100]这样一个闭区间是它的窗口
(4): 窗口总结
1、unbounded preceding:从当前分区的第一行开始,到当前行结束。
2、current row:从当前行开始,也结束于当前行。
3、[numeric eXPression] preceding:
对于rows来说从当前行之前的第[numeric expression]行开始,到当前行结束。
对range来说从小于数值表达式的值开始,到当前行结束。
4、[numeric expression] following:与[numeric expression] preceding相反。