Oracle MIN作为分析函数-ORDER BY的异常行为? [英] Oracle MIN as analytic function - odd behavior with ORDER BY?
问题描述
此特殊情况摘自一个示例，在该示例中，程序员假定对于两次进入油罐车的装运，首先要装载1号线.我更正了此问题，以允许以任何顺序执行加载-但是，我发现MIN() OVER (PARTITION BY)在Oracle中允许使用ORDER BY(在SQL Server中是不允许的)，此外，它还改变了该函数的行为，导致ORDER BY显然被添加到PARTITION BY.
This particular case was distilled from an example where the programmer assumed that for two shipments into a tank car, line #1 would be loaded first. I corrected this to allow for the loading to be performed in any order - however, I discovered that MIN() OVER (PARTITION BY) allows an ORDER BY in Oracle (this is not allowed in SQL Server), and additionally, it alters the behavior of the function, causing the ORDER BY to apparently be added to the 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示例(注释掉了不适用的代码):
A SQL Server example (with non-applicable code commented out):
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在做什么?为什么，对吗?
So question: What is Oracle doing and why and is it right?
推荐答案
如果将ORDER BY添加到MIN分析函数，则将其变成到目前为止"功能，而不是总的最小值.对于您要进行分区的最后一行，结果将是相同的.但是前几行的到目前为止"可能与总的最小值不同.
If you add an ORDER BY to the MIN analytic function, you turn it into a "min so far" function rather than an overall minimum. For the final row for whatever you're partitioning by, the results will be the same. But the prior rows may have a different "min so far" than the overall minimum.
以EMP表为例，您可以看到该部门到目前为止的最低工资最终收敛于该部门的总体最低工资.您会看到，任何给定部门的到目前为止"的最小值都会随着遇到较低的值而减小.
Using the EMP table as an example, you can see that the minimum salary so far for the department eventually converges on the overall minimum for the department. And you can see that the "min so far" value for any given department decreases as lower values are encountered.
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.
当然，当您尝试进行诸如计算个人最佳成绩之类的事情以供将来比较时，使用这种形式的分析函数会更有意义.如果您要跟踪某人的高尔夫得分，英里数或体重的下降，则显示个人最佳成绩可能是一种动机.
Of course, it would make more sense to use this form of the analytic function when you're trying to do something like compute a personal best that you can use as a comparison in future periods. If you're tracking an individual's decreasing golf scores, mile times, or weight, displaying personal bests can be a form of motivation.
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.
这篇关于Oracle MIN作为分析函数-ORDER BY的异常行为?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
