这个特殊情况是从一个示例中提炼出来的，在该示例中，程序员假定两次装罐车，首先要装载1号线。我更正了此问题，以允许以任何顺序执行加载- 但是，我发现在Oracle中MIN() OVER (PARTITION BY)允许使用a ORDER BY（在SQL Server中不允许这样做），此外，它更改了函数的行为，导致ORDER BY显然是已添加到中PARTITION BY。
MIN() OVER (PARTITION BY)
ORDER BY
PARTITION BY
WITH data AS ( SELECT 1 AS SHIPMENT_ID, 1 AS LINE_NUMBER, 2 AS TARE, 3 AS GROSS FROM DUAL UNION ALL SELECT 1 AS SHIPMENT_ID, 2 AS LINE_NUMBER, 1 AS TARE, 2 AS GROSS FROM DUAL ) SELECT MIN(tare) OVER (PARTITION BY shipment_id) first_tare ,MAX(gross) OVER (PARTITION BY shipment_id) last_gross ,FIRST_VALUE(tare) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER) first_tare_incorrect ,FIRST_VALUE(gross) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER DESC) last_gross_incorrect ,MIN(tare) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER) first_tare_incorrect_still ,MAX(gross) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER DESC) last_gross_incorrect_still ,MIN(tare) OVER (PARTITION BY shipment_id, LINE_NUMBER) first_tare_incorrect_still2 ,MAX(gross) OVER (PARTITION BY shipment_id, LINE_NUMBER) last_gross_incorrect_still2 FROM data
一个SQL Server示例（注释掉了不适用的代码）：
WITH data AS ( SELECT 1 AS SHIPMENT_ID, 1 AS LINE_NUMBER, 2 AS TARE, 3 AS GROSS -- FROM DUAL UNION ALL SELECT 1 AS SHIPMENT_ID, 2 AS LINE_NUMBER, 1 AS TARE, 2 AS GROSS -- FROM DUAL ) SELECT MIN(tare) OVER (PARTITION BY shipment_id) first_tare ,MAX(gross) OVER (PARTITION BY shipment_id) last_gross -- ,FIRST_VALUE(tare) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER) first_tare_incorrect -- ,FIRST_VALUE(gross) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER DESC) last_gross_incorrect -- ,MIN(tare) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER) first_tare_incorrect_still -- ,MAX(gross) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER DESC) last_gross_incorrect_still ,MIN(tare) OVER (PARTITION BY shipment_id, LINE_NUMBER) first_tare_incorrect_still2 ,MAX(gross) OVER (PARTITION BY shipment_id, LINE_NUMBER) last_gross_incorrect_still2 FROM data
那么问题来了：Oracle在做什么？为什么，对吗？
如果ORDER BY在MIN分析函数中添加，则将其变为“到目前为止的最小”函数，而不是总的最小值。对于您要进行分区的最后一行，结果将是相同的。但是，前面的行可能有一个不同于整体最小值的“到目前为止”。
MIN
以该EMP表为例，您可以看到到目前为止该部门的最低工资最终收敛于该部门的整体最低工资。您会看到，任何给定部门的“到目前为止”的最小值都会随着遇到更低的值而减小。
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.
