1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen

(Oracle) Subselect für Update soll pk zurück liefern und bringt mich um den Verstand

Dieses Thema im Forum "Relationale Datenbanksysteme" wurde erstellt von Atlantiz, 15. Dezember 2016.

  1. Atlantiz

    Atlantiz Grünschnabel

    Hallo Forum

    Beim Suchen nach einer Lösung bin ich auf dieses Forum gestossen.
    Habe eigentlich ein lächerliches Thema, das mich aber schon viel Zeit gekostet hat.

    Folgende Tabelle:
    T_TRAD_UPE_SB mit folgenden Spalten:
    • UPE_PK (PK zum Unverbindlichen VK Preis)
    • Z_TLN (Teilenummern)
    • GUELTIG_AB (Preisgültigkeit)
    • UPE_QUELLE (Datenquelle aus welcher der Preis stammt 1 oder 2)
    • UPE (Unverbindlicher VK Preis)
    • MAX (Kenzzeichnungsflag der Datensätze, die innerhalb der Gruppe
      ZTL; GUELTIG_AB & UPE_QUELLE den Max Preis haben
    ZIEL:
    Per Update soll das Feld max = 1 gesetzt werden, bei den Datensätzen (es können mehrere DS sein), die in der Gruppe(GROUP BY Z_TLN, UPE_QUELLE, GUELTIG_AB) den maximalen UPE haben.

    Ich schaffe es nicht (roter Bereich), den Subselect so zu formulieren, dass ich die ID´s der betroffenen Datensätze (UPE_PK) mit dem höchsten UPE der Gruppe bekomme.

    also so ungefähr:
    Code (SQL):
    1. UPDATE T_TRAD_UPE_SB dst
    2. SET
    3. dst.MAX = 1
    4. WHERE
    5. dst."UPE_PK" = src."UPE_PK"
    6. AND
    7. dst."UPE" IN (SELECT MAX(src.UPE)
    8. FROM T_TRAD_UPE_SB src
    9. GROUP BY
    10. src."Z_TLN", src."UPE_QUELLE", src."GUELTIG_AB") ;
    Ein Subselect
    SELECT Max(src.VAL) FROM ATLANTIS src GROUP BY src."G1", src."G2", src."G3" funktioniert.
    Wie bekomme ich jetzt dazu von genau diesen Datensätzen mit MAX Werten in der Gruppe die jeweiligen PK`s zurück?

    Vielleicht läßt es sich auch mit Analysefunktionen erschlagen, da bin ich aber noch total feucht hinter den Ohren.

    Vielen Dank für einen kurzen Tip... oder gerne auch 2-3 gute Suchbegriffe für Google.
    Ich finde aktuell immer nur Lösungen über die max(ID) also neuesten Datensatz oder Lösungenansätze,
    wenn es nur einen Datensatz mit MAX Wert gibt usw... was aber nicht weiter hilft.

    Habe schon in einem anderen Forum angefragt, jedoch nur Lösungen erhalten, die auf Oracle leider nicht laufen...

    Euch bereits jetzt vielen Dank!

    Atlantis
     
    Zuletzt von einem Moderator bearbeitet: 15. Dezember 2016
  2. Yaslaw

    Yaslaw n/a Moderator

    - Irgendwie sehe ich bei deinem SQL-Versuch nicht durch. Im WHERE vergleichst du die Daten von dst und src. src ist aber im SQL nirgends drin.
    - max ist ein schlechter Name für ein Feld. Es ist auch ein SQL-Befehl. Beschreibe beim Feldnamen von was es das Max ist. zB max_val.
    - Eine Tabelle ist beschrieben. Die Andere nicht. ebensowenig ihre Beziehnung zu einander
    - Was hat es mit G1 bis G3 auf sich?
     
  3. Atlantiz

    Atlantiz Grünschnabel

    Hallo Y
    Danke Dir für Deine rasche Antwort


    Das war ein dummer Fehler. Satt des richtigen SQL Statements habe ich versehentlich das meiner Testtabelle (ATLANTIS) eingefügt. Damit habe ich jetzt grosse Verwirrung angerichtet.
    "SELECT Max(src.VAL) FROM ATLANTIS src GROUP BY src."G1", src."G2", src."G3" funktioniert" muss heissen:
    SELECT Max(src.UPE) FROM T_TRAD_UPE_SB src GROUP BY src."Z_TLN", src."UPE_QUELLE", src."GUELTIG_AB" funktioniert.


    Kann ich das oben in der Originalanfrage noch irgendwie editieren, um niemanden mehr zu verwirren?

    Ich hoffe somit ist dann klar, was ich überhapt haben möchte...

    Gruss

    Atlantis
     
  4. Yaslaw

    Yaslaw n/a Moderator

    Das Reicht mit der Antwort.
    Im Gruppierungsquery die Gruppierungsfelder mitfiltern

    Wie ich das verstehe, geht es in diese Richtung: http://wiki.yaslaw.info/dokuwiki/doku.php/sql/tutorials/selectrecordwithmaxdatepergroup
    Einfach noch den SELECT durch ein UPDATE ersetzen
    Code (SQL):
    1. UPDATE tbl t1
    2. SET mx_val = (
    3.         SELECT MAX(t2.val)
    4.        FROM tbl t2
    5.        WHERE t1.feld1 = t2.feld1 AND t1.feld2 = t2.feld2
    6.        GROUP BY t2.feld1, t2.feld2
    7.     )
     
  5. Atlantiz

    Atlantiz Grünschnabel

    Hallo Yaslav

    Tolle Seite auf die Du verlinkt hast. Habe ich gleich als Lesezeichen abgelegt...
    Ich bin mir nicht sicher, ob ich damit schon ans Ziel komme:
    Wenn man bei Deiner Tabelle eine Spalte Maxkurs und die grünen Werte hinzufügt

    [kurs]
    w1 | w2 | kurs | datum | Maxkurs
    CHF | EUR | 0.779438988 | 2011-03-21
    CHF | USD | 1.10045 | 2011-03-20
    CHF | USD | 1.10314 | 2011-03-21
    CHF | EUR | 0.872367653 | 2011-03-20
    CHF | USD | 1.10112 | 2011-03-19
    CHF | EUR | 0.79 | 2011-03-21
    CHF | EUR | 0.79 | 2011-03-21
    CHF | USD | 1.12 | 2011-03-19


    Jetzt möchte ich die Spalte Maxkurs per Update auf 1 setzen und zwar da, wo die Gruppe w1,w2,datum den Maxwert beim Kurs hat.
    (Wunschergebnis in Blau)
    [kurs]
    w1 | w2 | kurs | datum | Maxkurs
    CHF | EUR | 0.779438988 | 2011-03-21
    CHF | USD | 1.10045 | 2011-03-20 1
    CHF | USD | 1.10314 | 2011-03-21 1
    CHF | EUR | 0.872367653 | 2011-03-20
    CHF | USD | 1.10112 | 2011-03-19
    CHF | EUR | 0.79 | 2011-03-21 1
    CHF | EUR | 0.79 | 2011-03-21 1
    CHF | USD | 1.12 | 2011-03-19 1


    Wie kann ich jetzt die DS aus dieser Unterabfrage:
    SELECT w1,
    w2,
    MAX(datum) AS max_datum
    FROM kurs
    GROUP BY w1,
    w2
    ;
    in mein Update maxkurs so integrieren, dass es auch mehrere DS (dunkelgrün) geben darf, die dann gekennzeichnet werden?
    Ich hatte gehofft eine Subquerry zu bekommen, dass mir die PK´s der jeweiligen DS liefert, die die 1 bekommen...

    Sorry, dass ich noch nicht am Ziel bin.... und danke Dir vielmals für die Geduld...
     
  6. Yaslaw

    Yaslaw n/a Moderator

    OK, Ich glaube ich verstehe jetzt was du willst.

    Das könnte so aussehen
    Code (SQL):
    1. UPDATE kurs k
    2. SET k.maxkurs = 1
    3. WHERE
    4.   EXISTS (
    5.       SELECT km.w1, km.w2, MAX(km.datum) AS max_datum
    6.       FROM kurs km
    7.       GROUP BY km.w1, km.w2
    8.       HAVING k.w1 = km.w1 AND k.w2 = km.w2 AND k.datum = MAX(km.datum)
    9.   )
    Code (Text):
    1. W1  W2        KURS DATUM                MAXKURS
    2. --- --- ---------- ----------------- ----------
    3. CHF EUR .779438988 21.03.11 00:00:00          1
    4. CHF USD    1.10045 20.03.11 00:00:00          1
    5. CHF EUR .872367653 20.03.11 00:00:00          
    6. CHF EUR .872367653 20.03.11 00:00:00          
    7. CHF USD    1.10112 19.03.11 00:00:00          
    8. CHF USD    1.10112 19.03.11 00:00:00          
    9. CHF EUR        .79 21.03.11 00:00:00          1
    10. CHF EUR        .79 21.03.11 00:00:00          1
    11. CHF USD       1.12 19.03.11 00:00:00          
     
  7. Atlantiz

    Atlantiz Grünschnabel

    Hallo Yaslaw

    Du bist ja wie ein Orakel, das alles weis...
    Ich zücke den Hut! Damit hast DU mir sehr geholfen!

    Der Ansatz von Dir war zwar nicht wie von mir gewünscht, aber damit habe ich mir jetzt die Lösung selbst basteln können...

    UPDATE kurs k
    SET k.maxkurs = 1
    WHERE
    EXISTS (
    SELECT km.w1, km.w2, MAX(km.KURS) AS max_kurs
    FROM kurs km
    GROUP BY km.w1, km.w2, km.datum
    HAVING k.w1 = km.w1 AND k.w2 = km.w2 AND k.datum = km.datum AND MAX(km.kurs)
    )

    Jetzt noch Sahnehäubchen Frage:
    Wenn ich alle anderen Datensätze auf 0 Setzen möchte, gibt es da einen kürzeren/ eleganteren und vor allem zeitlich schnellleren Weg als:

    UPDATE kurs k
    SET k.maxkurs = 1
    WHERE
    EXISTS (
    SELECT km.w1, km.w2, MAX(km.KURS) AS max_kurs
    FROM kurs km
    GROUP BY km.w1, km.w2, km.datum
    HAVING k.w1 = km.w1 AND k.w2 = km.w2 AND k.datum = km.datum AND MAX(km.kurs)

    )

    UPDATE kurs k
    SET k.maxkurs = 0
    WHERE
    NOT EXISTS (
    SELECT km.w1, km.w2, MAX(km.KURS) AS max_kurs
    FROM kurs km
    GROUP BY km.w1, km.w2, km.datum
    HAVING k.w1 = km.w1 AND k.w2 = km.w2 AND k.datum = km.datum AND MAX(km.kurs)
    )
    So etwas wie die ELSE Funktion...

    Und Danke noch einmal vielmals!

    Atlantis
     
  8. Yaslaw

    Yaslaw n/a Moderator

    Bitte in Zukunft dein Code in Code-Tags schreiben. Macht ihn lesbarer.

    Zu deiner Frage. Die Logik aus dem WHERE herausnehmen und in den Set einfügen. Mit CASE WHEN auswerten und 0 oder 1 zurückgeben.

    Code (SQL):
    1. UPDATE kurs k
    2. SET k.maxkurs = CASE WHEN EXISTS (
    3.               SELECT km.w1
    4.               FROM kurs km
    5.               GROUP BY km.w1, km.w2, km.datum
    6.               HAVING k.w1 = km.w1 AND k.w2 = km.w2 AND k.datum = km.datum AND MAX(km.kurs) = k.kurs
    7.             )
    8.         THEN 1
    9.         ELSE 0
    10.       END;
     
  9. Atlantiz

    Atlantiz Grünschnabel

    Vielen Dank noch mal für die geniale Unterstützung.
     
  10. Atlantiz

    Atlantiz Grünschnabel

    Hallo Yaslav

    Kann es sein, dass die Abfrage von heute Morgen 8:55 Uhr (im SQL Developer gestartet) jetzt noch läuft bei 1.844.328 Datensätzen?
    Eher nicht, oder? Fehlermeldung gab es zumindest keine...

    Das war der Code:
    Code (SQL):
    1. UPDATE T_TRAD_UPE_SB dst
    2. SET dst.MAXWERT = CASE WHEN
    3. EXISTS (
    4.            SELECT src.Z_TLN, src.GUELTIG_AB, MAX(src.UPE) AS MAX_VAL
    5.            FROM T_TRAD_UPE_SB src
    6.            GROUP BY src.Z_TLN, src.GUELTIG_AB, src.UPE_QUELLE
    7.            HAVING dst.Z_TLN = src.Z_TLN AND dst.GUELTIG_AB = src.GUELTIG_AB AND dst.UPE = MAX(src.UPE)
    8.            )
    9. THEN 1
    10. ELSE 0
    11. END;
    [/QUOTE]
     
  11. Yaslaw

    Yaslaw n/a Moderator

    Schwer zu sagen. Kommt auf Indexe etc. an
     
  12. Atlantiz

    Atlantiz Grünschnabel

    Ohne weitere Indexe (Nur den STD primary Key UPE_PK)
    Das Thema Indexe ist eine meiner nä. Baustellen...

    Bisher war ich noch nicht in der Verlegenheit sonderlich zeitkritisch zu sein.
    Erst seit ich vorhandenes Datenmaterial eingespielt habe, merkt man die ersten Wartezeiten...

    lG.
    Atlantis
     
  13. Yaslaw

    Yaslaw n/a Moderator

    Das mekrt man meistens erst mit wachsenden Daten.

    Da hast du aber noch etwas gefährliches im Code.
    Im GROUP BY hast du ein Feld, dass du nachher nicht vergleichst. UPE_QUELLE

    Die folgende Lösung könnte schneller sein, da zuerst gefiltert wird und dann erst Gruppiert
    Code (SQL):
    1. UPDATE t_trad_upe_sb dst
    2. SET dst.maxwert = (
    3.         SELECT CASE WHEN dst.upe = MAX(src.upe) THEN 1 ELSE 0 END
    4.         FROM t_trad_upe_sb src
    5.         WHERE dst.z_tln = src.z_tln AND dst.gueltig_ab = src.gueltig_ab AND dst.upe_quelle = src.upe_quelle
    6.         GROUP BY src.z_tln, src.gueltig_ab, src.upe_quelle
    7.     )
     
  14. Atlantiz

    Atlantiz Grünschnabel

    Hallo Yaslav

    Habe das von Dir optimierte SQL Statement gerade nach 71.830 Sekunden abgebrochen.

    Jetzt ist wohl die Zeit gekommen sich dem Thema Index zu widmen.
    Ich werde mal die Tage nach dem Thema Index & Tutorial suchen...

    Danke Dir noch einmal für den super Service.
    Eine schöne Weihnachtszeit

    Atlantis
     
  15. Yaslaw

    Yaslaw n/a Moderator

    Bei fast 2 Millionen Einträgen sind Indexe überfällig.
    Ist wie ein Buch ohne Inhalts- und Stichwortverzeichniss mit 2 Mio Seiten.
     
  16. Biber3

    Biber3 Mitglied

    Moin Atlantis,

    habe gerade eben erst diesen Thread entdeckt.
    Falls es noch nicht erledigt ist, versuche es mal so:

    Code (SQL):
    1. MERGE INTO T_TRAD_UPE_SB dst
    2.       USING (SELECT src.Z_TLN
    3.            , src.GUELTIG_AB
    4.            , MAX(src.UPE) AS MAX_VAL
    5.     FROM T_TRAD_UPE_SB src
    6.     GROUP BY src.Z_TLN
    7.              , src.GUELTIG_AB
    8.              , src.UPE_QUELLE
    9.     ) src ON (dst.Z_TLN = src.Z_TLN
    10.      AND dst.GUELTIG_AB = src.GUELTIG_AB
    11.      AND dst.UPE = src.UPE)
    12.      
    13.    WHEN MATCHED
    14.         UPDATE SET dst.MAXWERT = 1
    15.    WHEN NOT MATCHED
    16.         UPDATE SET dst.MAXWERT = 0
    Grüße
    Biber
     
  17. Yaslaw

    Yaslaw n/a Moderator

    @Biber3
    Ist der Merge schneller?
     
  18. Biber3

    Biber3 Mitglied

    Moin Yaslaw,

    ich sach ma' so...
    Hier in diesem Fall ist es eine vollkommen hypothetische Frage.
    Der TE will in einer Tabelle mit 2 Mio Datensätzen jeden, aber auch jeden Datensatz updaten.
    Ich hätte - da es ja eine Initialbefüllung zu sein scheint, ohnehin erst alle Datensätze auf MAXWERT=0 gesetzt oder wahrscheinlicher noch den Default für dieses Feld auf "NOT NULLABLE WITH Default =0" geändert.
    Damit wären 1, 9 Mio Datensätze korrekt auf "nicht die aktuellen" gesetzt und die verbleibenden 100000 hätte ich mit dem MERGE-Update auf 1 gesetzt. Also Statement oben ohne "WHEN NOT MATCHED".

    Aber zurück zu deiner Frage:
    - ja, MERGE ist nach meiner Wahrnehmung relativ schnell
    - aber auch Merge kann natürlich nur Indexe nutzen, die vorhanden sind (also wird er ohne Indizes auch nicht schneller sein als deine Varianten oben)

    Hauptgrund, weshalb ich den MERGE INTO hin und wieder verwende ist, dass Oracle die Verwendung von CTEs (also "WITH TableX as (select...)" bei SELECT, INSERT, DELETE unterstützt, nicht aber bei UPDATE-Operationen.

    Dort wäre das Pendant zu einer WITH-Syntax eben das verwendete "USING (...)", in dem common table expressions verwendet werden können.

    und ein weiteres Goodie ist die Möglichkeit, bei MERGE INTO nicht nur UPDATEs machen zu können, sondern z.B. im Fall "WHEN NOT MATCHED" auch ein INSERT eines neuen Satzes machen zu können.

    Unterm Strich: ja, ich halte MERGE INTO für schnell und empfehle mal das Ausprobieren.

    Grüße
    Biber
     
    Yaslaw gefällt das.
  19. Yaslaw

    Yaslaw n/a Moderator

    Merci für die Erläuterungen.
    ich kenne Merge. Mir wäre es aber nicht in den Sinn gekommen ihn für einen reinen Update anzuwenden. Aber deine Erklärung ist gut. Ich merke diese Idee mal.
     
Die Seite wird geladen...