如果ORDER BY在MIN分析函数中添加，则将其变为“到目前为止的最小”函数，而不是总的最小值。对于您要进行分区的最后一行，结果将是相同的。但是，前面的行可能有一个不同于整体最小值的“到目前为止”。
以该EMP表为例，您可以看到到目前为止该部门的最低工资最终收敛于该部门的整体最低工资。您会看到，任何给定部门的“到目前为止”的最小值都会随着遇到更低的值而减小。
sql> ed
Wrote file afiedt.buf
1 select ename,
2 deptno,
3 sal,
4 min(sal) over (partition by deptno order by ename) min_so_far,
5 min(sal) over (partition by deptno) min_overall
6 from emp
7* order by deptno, ename
sql> /
ENAME DEPTNO SAL MIN_SO_FAR MIN_OVERALL
---------- ---------- ---------- ---------- -----------
CLARK 10 2450 2450 1300
KING 10 5000 2450 1300
MILLER 10 1300 1300 1300
ADAMS 20 1110 1110 800
FORD 20 3000 1110 800
JONES 20 2975 1110 800
SCOTT 20 3000 1110 800
smith 20 800 800 800
ALLEN 30 1600 1600 950
BLAKE 30 2850 1600 950
MARTIN 30 1250 1250 950
SM0 30 950 950 950
TURNER 30 1500 950 950
WARD 30 1250 950 950
BAR
PAV
16 rows selected.
当然，当您尝试进行诸如计算个人最佳成绩之类的事情以供将来比较时，使用这种形式的分析函数会更有意义。如果您要跟踪某人的高尔夫得分，英里数或体重的下降，则显示个人最佳成绩可能是一种激励。
sql> ed
Wrote file afiedt.buf
1 with golf_scores as
2 ( select 1 golfer_id, 80 score, sysdate dt from dual union all
3 select 1, 82, sysdate+1 dt from dual union all
4 select 1, 72, sysdate+2 dt from dual union all
5 select 1, 75, sysdate+3 dt from dual union all
6 select 1, 71, sysdate+4 dt from dual union all
7 select 2, 74, sysdate from dual )
8 select golfer_id,
9 score,
10 dt,
11 (case when score=personal_best
12 then 'New personal best'
13 else null
14 end) msg
15 from (
16 select golfer_id,
17 score,
18 dt,
19 min(score) over (partition by golfer_id
20 order by dt) personal_best
21 from golf_scores
22* )
sql> /
GOLFER_ID score DT MSG
---------- ---------- --------- -----------------
1 80 12-SEP-11 New personal best
1 82 13-SEP-11
1 72 14-SEP-11 New personal best
1 75 15-SEP-11
1 71 16-SEP-11 New personal best
2 74 12-SEP-11 New personal best
6 rows selected.
602392714
清零编程群