分析函数语法
在Oracle官方文档中，oracle分析函数的标准语法如下：
Analytic_function(arg1,arg2,...) OVER(analytic_clause)
也可描述成如下：
FUNCTION_NAME(<argument>,<argument>...)
OVER
(<Partition-Clause><Order-by-Clause><Windowing Clause>)
例:
sum(sal) over (partition by deptno order by ename) new_alias
sum就是函数名
(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm)
over() 是开窗函数，这是开启分析函数的起点，对于既可作为聚集函数又可作为分析函数的函数，Oracle无法识别，必须用over来标识此函数为分析函数，此处不可省！
partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区
order by则是窗口规则
row |range BETWEEN...AND 是窗口范围(row就像通过自身位置的前后物理座位来找对应的位置，相对固定；而range是通过某种条件来找位置，需要计算方能确定相应位置
关于窗口范围的取数方式有多种，看似非常复杂，其实不难：
关键位置就3个：
1.分组第一行Unbounded preceding
2.当前行Current ROW
3.分组最后一行 Unbounded following
接下来，产生了最简单的三个取数范围：
分组第一行->当前行 rows [between] unbounded preceding [and current row]
分组第一行->分组最后一行 rows between unbounded preceding and unbounded following
当前行->分组最后一行 rows between current row and unbounded following
测试案例
SQL> drop table emp purge;
SQL> CREATE TABLE emp(
emp_id NUMBER(6),
ename VARCHAR2(45),
dept_id NUMBER(4),
hire_date DATE,
sal NUMBER(8,2));
插入emp数据
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (101, 'Tom', 20, TO_DATE('21-09-1989', 'DD-MM-YYYY'), 2000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (102, 'Mike', 20, TO_DATE('13-01-1993', 'DD-MM-YYYY'), 8000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (120, 'John', 50, TO_DATE('18-07-1996', 'DD-MM-YYYY'), 1000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (121, 'Joy', 50, TO_DATE('10-04-1997', 'DD-MM-YYYY'), 4000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (122, 'Rich', 50, TO_DATE('01-05-1995', 'DD-MM-YYYY'), 3000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (123, 'Kate', 50, TO_DATE('10-10-1997', 'DD-MM-YYYY'), 5000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (124, 'Jess', 50, TO_DATE('16-11-1999', 'DD-MM-YYYY'), 6000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (100, 'Stev', 10, TO_DATE('01-01-1990', 'DD-MM-YYYY'), 7000);
SQL> COMMIT;
SQL> set linesize 2000
SQL> set pagesize 2000
SQL> col emp_id format 999
SQL> col dept_id format 99
SQL> col ename format a5
SQL> SELECT
emp_id,ename,dept_id,hire_date,sal,
SUM(sal) OVER (PARTITION BY dept_id ORDER BY hire_date) sum_sal,
SUM(sal) OVER (PARTITION BY dept_id ) sum_sal2,
SUM(sal) OVER ( ) sum_sal3,
MAX(sal) OVER (PARTITION BY dept_id ORDER BY hire_date) max_sal,
MIN(sal) OVER (PARTITION BY dept_id ORDER BY hire_date) min_sal,
COUNT(sal) OVER (PARTITION BY dept_id ORDER BY hire_date) count_sal,
AVG(sal) OVER (PARTITION BY dept_id ORDER BY hire_date) avg_sal
FROM emp;
