[Oracle:SQL] Subquery mit Bezug auf "Superquery" für View

[sirius]

Ich habe eine Tabelle bei der Messwerte untereinander gespeichert werden. Jeder Messwert ist durch zwei IDs und den Zeitstempel eindeutig definiert (die drei Spalten sind unique). Die Tabelle hat quasi folgende 4 Spalten:
ID1, ID2, Zeit, Messwert

Jetzt möchte ich ein View erstellen, welches für eine bestimmte ID1 Alle Messwerte über die Zeit auflistet, als Spalten soll dabei ID2 eingesetzt werden. Das sieht dann in etwa so aus:

Code:

Für eine bestimmte ID1 (z.B. 5000)
Zeit    ID2=3000 (Alias1)  ID2=3002 (Alias2)   ...
10:00   Messwert=2         Messwert=4000 
10:01   Messwert=1         Messwert=2000 
10:02   Messwert=1,4       Messwert=4000 
...

Das dies zumindest irgendwie möglich ist, habe ich in einer fremden Datenbankstrukutr in MS-SQL-Express gesehen. Ansonsten hätte ich dies erst gar nicht versucht.

In Oracle geht es mit diesem Ansatz zumindest nicht:

SQL-Code:

Select Zeit, 
       (Select Messwert from mytable
         where ID2=3001) as Alias1,
       (Select Messwert from mytable
         where ID2=3002) as Alias2 
      from mytable
      where ID1=5000

Die DB stellt fest, dass im Subquery mehrere Ergebnisse kommen. Is klar, denn zu einer ID" in mytable gibt es mehr als einen Messwert.
Jetzt kann ich natürlich noch die ID1 mit ins Subquery nehmen, bringt aber auch nichts, da ja zu einer Kombination von den beiden IDs es ja viele MEsswerte zu unterschiedlichen Zeiten gibt. Allerdings möchte ich ja pro Zeile nur den Messwert, welcher zum Zeitwert aus dem übergeordneten Query (Superquery) gehört.

Wie geht das in Oracle?

Vielen Dank im Vorraus
Sirius

[Codewalker]

Ich hoffe ich habe das soweit richtig verstanden.
Mein Ansatz wäre ein union über zwei getrennte Queries mit gleichnamigen Spalten. Das wäre dann ungefähr so:

SQL-Code:

select * from
(
select Zeit as "Zeit", Messwert as "Alias1", null as "Alias2" from mytable where ID2=3001 and ID = 5000
union
select Zeit as "Zeit", null as "Alias1", Messwert as "Alias2" from mytable where ID2=3002 and ID = 5000
)

Ich habe das mal verwendet, um Ereignisse (als Text) und Messwerte in eine zeitliche Abfolge in einer Query zu bekommen. Da die Spalten jeweils den gleichen Namen tragen werden sie von Oracle als identisch behandelt und entsprechend zusammengebaut. Damit der Union klappt, müssen dann jeweils Pseudo-Nullspalten hinzugepackt werden. Ich hoffe das passt oder du kannst es abändern, so wie du es brauchst.

Mfg

[Elvis]

Wie wird denn auf die Daten zugegriffen?
Denn du könntest dir ein Package schrieben, welches sich passend zu einer ID ein SQL mit Spalten für alle ID2s baut.

Ist jetzt als Trockenübung aus den Fingern gesaugt, also Nachsicht büddeshön!

SQL-Code:

create package CrosstabThingy authid current_user as
  function GetCrossTabFor(valueID in Integer) return sys_refcursor;
end;

create package body CrosstabThingy as
  function GetCrossTabFor(valueID in Integer) return sys_refcursor is
    result sys_refcursor;
    sqlStatement clob;
    valueIndex Integer := 0;
  begin
    sqlStatement := 'SELECT Zeit';

    for childID in (select ID2 from mytable where ID1 = valueID) loop
      valueIndex := valueIndex + 1;
      sqlStatement := sqlStatement || 
                      ',(SELECT Messwert from mytable where ID2 = '||
                      childID.ID2||') as Value'||to_Char(valueIndex, 'tm00');
    end loop;

    sqlStatement := sqlStatement || ' from myTable where ID1 = :id';
    open result for to_Char(sqlStatement)
      using valueID;
    return result;
  end;
end;

Per ODBC würde das so ausgeführt werden können:
{call CrosstabThingy.GetCrossTabFor(5000)} Je nachdem wie du drauf zugreifst ginge auch sowas:

SQL-Code:

select Zeit,
       cursor(select ID2, Messwert 
              from mytable 
              where ID1 = t.ID1) Values
from mytable t
where ...

Oracle hat auch eine Menge XML Funktionen parat, mussu nur sagen wofür das nachher benutzt werden soll.

[sirius]

Erstmal Danke Euch beiden.

Ich habs gefunden. Während ich versuchte Elvis' Package zum Laufen zu bringen, ist mir die Lösung aufgegangen:

SQL-Code:

Select Zeit, 
       (Select Messwert from mytable 
         where ID2=3001 and Zeit=t.Zeit) as Alias1, 
       (Select Messwert from mytable 
         where ID2=3002 and Zeit=t.Zeit) as Alias2 
      from mytable t
      where ID1=5000

@CodeWalker
Dein Code brachte eher folgendes Ergebnis:

Code:

Für eine bestimmte ID1 (z.B. 5000) 
Zeit    ID2=3000 (Alias1)  ID2=3002 (Alias2)   ... 
10:00   Messwert=2         null
10:00   null               Messwert=4000 
10:01   Messwert=1         null 
10:01   null               Messwert=2000 
...

Diese null-Werte waren zu viel.


@Elvis:
Dein Package sieht ja sehr interessant aus. Das muss ich mir mal zu Gemüte führen.
Was ich mit dieser Anweisung mache, ist eine View erstellen (wie im Titel angekündigt). Und zwar exakt in der dynamischen Form, welche du in deinem Package auch verwendet hast. Der GRund ist, dass ich die Tabellstruktur ändern will (die vorher so aussah, wie das Ergebnis der View), aber der lesende Zugriff von außen soll gleich bleiben und damit über die View gehen. Performancetechnisch sicherlich nicht besonders toll, aber ich will den Weg mal testen.

[p80286]

Hallo Sirius,

wie wäre es hiermit:

SQL-Code:

Select t.Zeit,a.messwert,b.messwert
from mytable t,
     mytable a,
     mytable b
where a.zeit=t.zeit
  and b.zeit=t.zeit
  and a.id2=3001
  and b.id2=3002
  and t.id1=5000

natürlich könnte man t und a auch in einer Tabelle abhandeln, aber das Prinzip kommt wohl rüber.

Gruß
K-H

[sirius]

@p80286: Danke füpr dein Bemühen, aber ich habe die Lösung schon gefunden (siehe Post über Dir). Es war deutlich einfacher als ich dachte.

@Elvis: Deine Procedure habe ich auch noch zum Laufen gebracht. Ich erhoffte mir damit Performanceverbesserungen gegenüber der View (auch wenn ich mir nicht vorstellen konnte, wo die herkommen sollten; aber bei Datenbanken weis ich ja nie). Trat dann aber auch nicht ein.

[Elvis]

Zitat von sirius:

@Elvis: Deine Procedure habe ich auch noch zum Laufen gebracht. Ich erhoffte mir damit Performanceverbesserungen gegenüber der View (auch wenn ich mir nicht vorstellen konnte, wo die herkommen sollten; aber bei Datenbanken weis ich ja nie). Trat dann aber auch nicht ein.

Kann auch nicht schneller sein, weil ich dich missverstanden hatte.
Ich dachte du hättest n ID2-Werte. Deshalb die variable Spaltenzahl in dem dynamic SQL.
Aber wenn du immer 2 hast, ist ein View schon OK.
Sorge aber dafür, dass du einen kombinierten Index über ID2 und Zeit hast. Dadurch sollten dich die Subqueries fast nix kosten.

Wenn du eine ältere Oracle Version einsetzt möchtest du vielleicht die Abfrage lösen wie es mein Vorredner mit dem kryptischen Namen vorschlug. Neuere Versionen haben einen so guten Optimizer, dass es keinen Unterschied macht, der weiß einfach genau was du vorhast...

[sirius]

Hallo Elvis,

Ich habe das bei der View auch dynamisch gelöst. Ich hatte zum Testen nur zwei IDs, im Original sind es aber mehr. Du meinst, dass es evtl. Vorteile bei mehr IDs geben wird. Mal sehen. Da muss ich erstmal meine Testtabelle füllen.
Dann teste ich auch noch die Variante von p80286.

Welche Oracle-Version im Original eingesetzt wird, weiß ich grad nicht. Ich vermute ja mal eine aktuellere als meine 9. Ich werde aber auch demnächst auf die neueste Version updaten.

[Elvis]

Zitat von sirius:

Hallo Elvis,

Ich habe das bei der View auch dynamisch gelöst. Ich hatte zum Testen nur zwei IDs, im Original sind es aber mehr. Du meinst, dass es evtl. Vorteile bei mehr IDs geben wird. Mal sehen. Da muss ich erstmal meine Testtabelle füllen.

Der Weg über das Package macht nur sinn, wenn du nicht weißt wieviele "Alias*" Spalten du haben kannst.

[sirius]

Zitat von Elvis:

Zitat von sirius:

Hallo Elvis,

Ich habe das bei der View auch dynamisch gelöst. Ich hatte zum Testen nur zwei IDs, im Original sind es aber mehr. Du meinst, dass es evtl. Vorteile bei mehr IDs geben wird. Mal sehen. Da muss ich erstmal meine Testtabelle füllen.

Der Weg über das Package macht nur sinn, wenn du nicht weißt wieviele "Alias*" Spalten du haben kannst.

Die Anzahl ändert sich im laufenden Betrieb eher selten, und wenn doch, dann würde ich die View erneuern.