Oracle-MIN als analytische Funktion - merkwürdiges Verhalten mit ORDER BY?

Diesem speziellen Fall wurde destilliert von einem Beispiel, wo der Programmierer davon ausgegangen, dass für zwei Sendungen in einen tank car, line #1 würde zuerst geladen werden. Ich korrigiert dies, dass für die Belastung in beliebiger Reihenfolge durchgeführt werden - allerdings habe ich entdeckt, dass MIN() OVER (PARTITION BY) ermöglicht eine ORDER BY in Oracle (dies ist nicht erlaubt im SQL-Server), und zusätzlich, es ändert das Verhalten der Funktion, wodurch die ORDER BY offenbar werden Hinzugefügt, um die 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

Einer SQL-Server-Beispiel (mit nicht zutreffenden code auskommentiert):

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

Frage also: Was ist Oracle tut und warum und ist es richtig?

  • ich denke, der Denali hat das jetzt
  • Smith ja, das Verhalten, die sollten identisch sein, und die ORDER BY sollte Auswirkungen auf das Verhalten. Ich verstehe nicht, warum Oracle nicht gegen die ORDER BY wie SQL Server ist, und dann, WARUM es wirkt sich auf das Verhalten.
  • Werfen Sie einen Blick auf diesen link geniiius.com/blog/t-sql-enhancements-over-clause Es erklärt das Verhalten, die Sie Fragen, die auf SQL Server Denali, und warum es Sinn macht
InformationsquelleAutor Cade Roux | 2011-09-12
  1. 12

    Wenn Sie hinzufügen, ein ORDER BY zu den MIN analytische Funktion, schalten Sie es in einen "min-soweit" - Funktion eher als eine Allgemeine minimum. Für die Letzte Zeile für das, was Sie sind-Partitionierung durch, die Ergebnisse werden die gleichen sein. Aber die vorherigen Zeilen haben vielleicht ein anderes "min-soweit" als die Gesamt-minimum.

    Mithilfe der EMP Tabelle als Beispiel können Sie sehen, dass das Mindestgehalt bisher für die Abteilung schließlich konvergiert auf die insgesamt mindestens für die Abteilung. Und Sie können sehen, dass die "min-soweit" - Wert für jede Abteilung nimmt als niedrigere Werte angetroffen werden.

    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.

    Natürlich würde es mehr Sinn machen, verwenden Sie diese form der Analyse-Funktion, wenn du versuchst, etwas zu machen, berechnen Sie eine persönliche Bestzeit, die Sie verwenden können, wie ein Vergleich in zukünftigen Perioden. Wenn Sie tracking eine individuelle Verringerung golf erzielt, Meile Zeiten oder Gewicht, Anzeige persönliche Bestleistungen kann eine form der 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.
    • Mein erstes Skript scheint nicht zu tun. Die anderen MIN und MAX nie gleichzeitig passend zum MIN und MAX.
    • Ich glaube, dein ersten script nicht tun. FIRST_TARE gibt Sie den minimalen Wert von TARE für jeden SHIPMENT_ID was Sie wollen. FIRST_TARE_INCORRECT_STILL gibt Sie den minimalen Wert von TARE für jeden SHIPMENT_ID wurde bisher begegnet. Die "min-soweit" der Linie 1 ist 2, weil das der einzige Wert, den es gesehen hat. Die "min-soweit" der Linie 2 ist 1. Und FIRST_TARE_INCORRECT_STILL2 gibt Sie die minimale TARE für jeden SHIPMENT_ID, LINE_NUMBER. Da ist vermutlich einzigartig, das wird nur wieder das TARE der Zeile.
    • Und die BESCHREIBUNG macht es nur noch verwirrender, denn es kippt und ist quasi die inverse? Ich denke es macht Sinn.
    • Recht. Umkehrung der Sortierreihenfolge macht das MAX Gegenzug die größte GROSS bisher für die SHIPMENT_ID beginnend mit dem größten LINE_NUMBER und arbeitet rückwärts. Das ist schwer, um Ihren Kopf herum, aber Oracle scheint zu tun, was Sie Fragen es.
    • Gut, ich werde nicht Fragen, es zu tun mehr!
    InformationsquelleAutor Justin Cave
Schreibe einen Kommentar