✔ (Oracle 10.2) Wertes auf Basis eine min/max Wertes eines anderen Feldes

[Sabine_81]

Guten Tag,

ich hätte mal wieder ein Oracle-SQL-Problem und hoffe hier kann mir jemand einen hilfreichen Tipp dazu geben:

Also folgende Tabellenstruktur hab ich:

ID___ version___nummer
1____1________1542
1____2________ 8185
2____1________1547

Anforderung wäre nun folgens:

ID__älteste_versionsnummer___neueste versionsnummer
1___1542___________________ 8185
2___1547___________________ 1547

Also wenn die Version gleich der maximalen Version für diese ID ist, dann soll der Wert in die eine spalte, wenn die Version gleich der minimalen Version für diese ID ist, dann entsprechend in die zweite.
Verständlich? jemand ne idee wie man das machen könnte?

ich hab schon probiert mit

where version = min(version)

aber da bekomme ich den fehler das hier keie agregatfunktion erlaubt sei. mit

select id,
min(version) as a, 
max(version) as b 
from tab
group by id

bekomme ich die jeweils großte und kleinste version nebeneinander, aber wie kann ich da jetzt die nummer dazu bringen?

vlg

 

[benjava]

Hi,
zuallererst musst du darauf achten, dass jede Kombination aus ID und Version eindeutig ist. Dann kriegst du die passenden Werte zB so raus:

select x.id, minv.nummer as minnummer, maxv.nummer as maxnummer
from (select id, min(version) as a, max(version) as b
    from tab
    group by id ) as x,
  tab as minv,
  tab as maxv
where x.id = minv.id and x.a = minv.version
  and x.id = maxv.id and x.b = maxv.version

Afair ginge es auch mit Hilfe einiger OLAP-Funktionen, aber die hab ich grad nicht im Kopf.

 

[Sabine_81]

danke schonmal...
ich habe es jetzt damit probiert... bekomme aber einen fehler: "sql_befehl wurde nicht korekkt beendet" und das "as" nach der klammer wird als falsch angezzeigt. ich hab al testweise mal alle drei "as" weg gelassen, dann bekomme ich den fehler das maxv.version ein ungültiger bezeichner sei...

 

[benjava]

oh, stimmt, Oracle mag da kein "as". Aber wenn ich das weglasse, funktionierts bei mir:

SQL> create table tab( id integer not null, version integer not null, nummer integer not null, primary key( id, version ) );
SQL> insert into tab values( 1, 1, 1542);
SQL> insert into tab values( 1, 2, 8185 ); 
SQL> insert into tab values( 2, 1, 1547 );

SQL> select x.id, minv.nummer as minnr, maxv.nummer as maxnr
  2   from (
  3    select id, min(version) as a, max(version) as b from tab group by id
  4  ) x,
  5  tab minv, tab maxv
  6   where x.id = minv.id and x.a = minv.version
  7    and x.id = maxv.id and x.b = maxv.version;

        ID      MINNR      MAXNR
---------- ---------- ----------
         1       1542       8185
         2       1547       1547

 

[Sabine_81]

ohje... ich nehm alles zurück...
hab "tab" einfach stehen lassen und da nicht den namen meiner tabelle eingetragen... *schäm* sorry...

also es funktionierts ganz prima

ganz ganz ganz vielen lieben herzlichen Dank

 

[Exceptionfault]

Zu Vollständigkeit hier nochmal eine Lösung mit den Analytischen Funktionen und eine Performancebetrachtung der beiden Lösungen:

select x.id, minv.nummer as minnr, maxv.nummer as maxnr
  from (
   select id, min(version) as a, max(version) as b from tab group by id
 ) x,
 tab minv, tab maxv
  where x.id = minv.id and x.a = minv.version
   and x.id = maxv.id and x.b = maxv.version;

Für diese Lösung berechnet Oracle einen mehr oder weniger komplexen Ausführungsplan und kommt dadurch auf 118 Bytes "Aufwand" und muss 9 Datenblocks lesen.

-----------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     2 |   118 |     3|
|   1 |  NESTED LOOPS                 |              |     2 |   118 |     3|
|   2 |   NESTED LOOPS                |              |     2 |    98 |     2|
|   3 |    VIEW                       |              |     2 |    78 |     1|
|   4 |     HASH GROUP BY             |              |     2 |    12 |     1|
|   5 |      INDEX FULL SCAN          | SYS_C0063610 |     3 |    18 |     1|
|   6 |    TABLE ACCESS BY INDEX ROWID| TAB          |     1 |    10 |     1|
|*  7 |     INDEX UNIQUE SCAN         | SYS_C0063610 |     1 |       |     1|
|   8 |   TABLE ACCESS BY INDEX ROWID | TAB          |     1 |    10 |     1|
|*  9 |    INDEX UNIQUE SCAN          | SYS_C0063610 |     1 |       |     1|
-----------------------------------------------------------------------------

Statistics
-----------------------------
          9  consistent gets

Mit analytischen Funktionen würde die Lösung wie folgt aussehen:

select
	id,
	min(nummer) keep (dense_rank first order by version) as minnr,
	max(nummer) keep (dense_rank last order by version) as maxnr
from
	tab
group by id;

Der Ausführungsplan ist einfacher und die Performance besser, da nur 2 Blöcke gelesen werden und die "Kosten" nur bei 20 Bytes liegen:

----------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     2 |    20 |     1|
|   1 |  SORT GROUP BY NOSORT        |              |     2 |    20 |     1|
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB          |     3 |    30 |     1|
|   3 |    INDEX FULL SCAN           | SYS_C0063610 |     3 |       |     1|
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          2  consistent gets

 

[Sabine_81]

vielen Dank für den vergleich. ich habe die zweite Version auch mal ausprobiert, funktioniert auch sehr gut.
da es bei mir im moment nicht sehr viele datensätze gibt, bemerke ich perfomancemässig aber aktuell keinen unterschied. aber prima, dass ich diese version jetzt auch noch kenne :-D