oracle分析函数over的用法

insert into EMP (ENAME, DEPTNO, SAL)
values ('MILLER', '10', 1300);
insert into EMP (ENAME, DEPTNO, SAL)
values ('KING', '10', 5000);
insert into EMP (ENAME, DEPTNO, SAL)
ALLEN 30 1600 21225 1600 1
BLAKE 2850 24075 4450 2
JAMES 950 25025 5400 3
OVER (PARTITION BY deptno
ORDER BY ENAME) "Seq"
FROM emp
ORDER BY deptno, ename
/
Ename Deptno Sal Running Total Dept Total Seq
MARTIN 1250 26275 6650 4
TURNER 1500 27775 8150 5
WARD 1250 29025 9400 6
oracle分析函数over的用法
分析函数，最早是从ORACLE8.1.6开始出现的，它的设计目的是为了解决诸如“累计计算”，“找出分组内百分比”，“前-N条查询”，“移动平均数 计算”"等问题。其实大部分的问题都可以用PL/SQL解决，但是它的性能并不能达到你所期望的效果。分析函数是SQL言语的一种扩充，它并不是仅仅试代 码变得更简单而已，它的速度比纯粹的SQL或者PL/SQL更快。现在这些扩展已经被纳入了美国国家标准化组织SQL委员会的SQL规范说明书中。
SUM(sal)
OVER (ORDER BY deptno, ename) "Running Total",
SUM(SAL)
OVER (PARTITION BY deptno
ORDER BY ename) "Dept Total",
ROW_NUMBER()
9 rows selected.
该查询根据工资列以降序排列各个划分（或分组，属于该deptno），并在处理过程中为每行分配一个顺序号。然后使用WHERE语句得到各划分的 前三行。
Example 2例2：我需要工资为前三位的销售人员名字——即查找工资金额、排序、取最高的三项金额、给我领取这些工资的人员的名字。
values ('FORD', '20', 3000);
insert into EMP (ENAME, DEPTNO, SAL)
values ('ADAMS', '20', 1100);
insert into EMP (ENAME, DEPTNO, SAL)
values ('JONES', '20', 2975);
分析函数是在一个记录行分组的基础上计算它们的总值。与集合函数不同，他们返回各分组的多行记录。行的分组被称窗口，并通过分析语句定义。对于每记录行， 定义了一个“滑动”窗口。该窗口确定“当前行”计算的范围。窗口的大小可由各行的实际编号或由时间等逻辑间隔确定。
除了ORDER BY（按…排序）语句外，分析函数是一条查询被执行的操作。所有合并、WHERE、GROUP BY、HAVING语句都是分析函数处理之前完成的。因此，分析函数只出现在选择目录或ORDER BY（按…排序）语句中。
insert into EMP (ENAME, DEPTNO, SAL)
values ('TURNER', '30', 1500);
insert into EMP (ENAME, DEPTNO, SAL)
values ('MARTIN ', '30', 1250);
insert into EMP (ENAME, DEPTNO, SAL)
SELECT * FROM (
SELECT deptno, ename, sal,
DENSE_RANK()
OVER (
PARTITION BY deptno ORDER BY sal desc
) TopN FROM emp
)
WHERE TopN <= 3
CLARK 2450 2
MILLER 1300 3
20 SCOTT 3000 1
FORD 3000 2
values ('ALLEN', '30', 1600);
insert into EMP (ENAME, DEPTNO, SAL)
values ('BLAKE', '30', 2850);
insert into EMP (ENAME, DEPTNO, SAL)
values ('JAMES', '30', 950);
JONES 2975 3
30 BLAKE 2850 1
ALLEN 1600 2
TURNER 1500 3
窗口生成语句用以定义滑动或固定数据窗口，分析函数在分组内进行分析。该语句能够对分组中任意定义的滑动或固定窗口进行计算。
Example: Calculate a running Total例：累计计算：本例中对某部门的工资进行逐行计算，每行包括之前所有行中工资的合计。
SELECT ename "Ename", deptno "Deptno", sal "Sal",
) Top3 FROM emp
)
WHERE Top3 <= 3
/
DEPTNO ENAME SAL TOP3
---------- ---------- ---------- ----------
10 KING 5000 1
values ('WARD', '30', 1250);
commit;
The Syntax句法：
OVER (
<Query-Partition-Clause>
<Order-By-Clause>
<Windowing-Clause>
)
根据划分表达式设置的规则，PARTITION BY（按…划分）将一个结果逻辑分成N个分组划分表达式。在此“划分”和“分组”用作同义词。分析函数独立应用于各个分组，并在应用时重置（按…排序）语 句规定了每个分组（划分）的数据如何排序。这必然影响分析函数的结果。
前期数据准备：
create table EMP
(
ENAME VARCHAR2(10),
DEPTNO VARCHAR2(2),
SAL NUMBER(10)
)
insert into EMP (ENAME, DEPTNO, SAL)
values ('CLARK', '10', 2450);
ORDER BY deptno, sal DESC
/
DEPTNO ENAME SAL TOPN
---------- ---------- ---------- ----------
10 KING 5000 1
Top-N Queries前N条查询：如何通过部分字段得到前N条记说法存在问题。在 设计报告时，应留意这一点。我需要知道部门工资为前3名销售代表的谁。这句话的问题在于含混不清。因为存在重复的值，如果有四个人领着同样的工资，该怎么 处理？
JONES 2975 15825 7075 3
SCOTT 3000 18825 10075 4
SMITH 800 19625 10875 5
JONES 2975 2
ADAMS 1100 3
30 BLAKE 2850 1
insert into EMP (ENAME, DEPTNO, SAL)
values ('SCOTT ', '20', 3000);
insert into EMP (ENAME, DEPTNO, SAL)
values ('SMITH', '20', 800);
insert into EMP (ENAME, DEPTNO, SAL)
CLARK 2450 2
MILLER 1300 3
20 SCOTT 3000 1 <--- !
FORD 3000 1 <--- !
.本例指出了如何计算整条查询的“累计”。即使用排序后的整个结果集合，通过SUM(sal) OVER (ORDER BY deptno, ename)函数得到。可以进一步计算各个部门的累计值，该值在开始下一个部门计算时将被重置。由SUM(sal)中的PARTITION BY deptno实现。该条查询中指定划分语句将数据进行分组。根据排序规则（增加了“Seq”列以显示该状态)，ROW_NUMBER()函数将每组返回的 记录行进行顺序编号，执行计划显示，整条查询仅需3条一致get函数就可以很好的执行。这一点是标准SQL甚至PL/SQL不能都实现的。
------ ------ ------ ------------- ---------- ----
CLARK 10 2450 2450 2450 1
KING 5000 7450 7450 2