Hallo,

ich bin dabei, eine nicht gut strukturierte Datendatei besser zu normalisieren, und habe Probleme, eine Liste eindeutiger Einträge zu erstellen. (Konkret: es handelt sich um die Straßendatei aus "Datafactory Postalcode" der Deutschen Post AG; ich möchte durch eine zusätzliche Tabelle mit eindeutigen Straßennamen die Wiederholungen reduzieren.)

Tabelle Quelle enthält etwa 260.000 Datensätze mit folgender Struktur (weitere vorhandene Felder können hier vernachlässigt werden):

• ID, Nr, Status, Name1, Name2, Name3

Tabelle Ziel mit etwa 115.000 Datensätzen enthält nur noch diese Felder:

• ID als PrimaryKey, Name1, Name2, Name3

ID und Name1 sind eindeutig. In der Tabelle Quelle stehen viele Datensätze mit diesen Werten, und auch die Kombination ID/Nr/Status ist nicht eindeutig. Ich möchte jeweils einen Eintrag aus "Quelle" nach "Ziel" übernehmen (vorzugsweise einen beliebigen mit Status='G') und finde keinen vernünftigen Weg.

Der folgende Versuch klappt nicht, weil Name2 und Name3 in mehreren Fassungen auftreten können.

markieren

SQL-Code:

INSERT INTO Ziel (ID, Name1, Name2, Name3)
SELECT DISTINCT ID, Name1, Name2, Name3 FROM Quelle

Mit dem folgenden Versuch erhalte ich zügig eine korrekte (und vollständige) Zieltabelle; aber ich finde keinen schnellen und einfachen Weg, die fehlenden Felder zu holen:

zusammenfalten · markieren

SQL-Code:

INSERT INTO Ziel (ID, Name1)
SELECT DISTINCT ID, Name1 FROM Quelle;

/* Teil einer StoredProcedure:
  danach zu jedem Eintrag die juengsten Angaben hinzufuegen  */
  FOR SELECT ID FROM Ziel INTO :current_id
  DO BEGIN
     FOR SELECT Name2, Name3
         FROM Quelle
        WHERE ID = :current_id
         INTO :current2, :current3
     DO BEGIN
        IF(passender Eintrag gemäß Status usw.)
        THEN BEGIN
          UPDATE STR_Namen
             SET Name2 = :current2,
                 Name3 = :current3
           WHERE ID = :current_id;
        END
     END
  END

Das sollte klappen; aber es dauert ewig, sodass IBExpert sich offensichtlich aufhängt.

Auch verschiedene Wege mit SELF-JOIN haben nur zum "Aufhängen" geführt, beispielsweise:

markieren

SQL-Code:

INSERT INTO Ziel
            ( ID, Name1, Name2, Name3 )
       SELECT DISTINCT st.ID, st.Name1,
              (SELECT FIRST 1 j.Name2
                                    FROM Quelle j
                                   WHERE st.ID = j.ID AND j.Status = 'G'),
              (SELECT FIRST 1 j.Name3
                                    FROM Quelle j
                                   WHERE st.ID = j.ID AND j.Status = 'G')
         FROM Quelle st

Natürlich sind zwei Sub-Selects hinderlich, aber die folgende Verkürzung ist syntaktisch nicht korrekt:

markieren

SQL-Code:

INSERT INTO Ziel
            ( ID, Name1, Name2, Name3 )
       SELECT DISTINCT st.ID, st.Name1,
              (SELECT FIRST 1 j.Name2, j.Name3
                                    FROM Quelle j
                                   WHERE st.ID = j.ID AND j.Status = 'G')
         FROM Quelle st

In der DP-Suche bin ich auf SQL-abfrage mit DISTINCT unterdruecken doppelter datensaetze gestoßen. Aber die dortigen Ideen konnte ich für ein SELF-JOIN nicht so umsetzen, dass genau ein zusätzlicher Datensatz geholt wird.

Hat einer von Euch eine schöne Idee für mich? Recht herzlichen Dank! Jürgen

Distinct zieht alle Felder der Abfrage mit ein, lass also ID mal weg.

Zitat von mkinzler:

Distinct zieht alle Felder der Abfrage mit ein, lass also ID mal weg.

Das mit DISTINCT ist mir klar.

Aber den Vorschlag verstehe ich nicht: die ID ist ja eindeutig, und die will ich behalten. Von den unterschiedlichen Werten bei Name2 und Name3 will ich genau einen übernehmen (vorzugsweise einen mit Status = 'G'). Jürgen

Ja, aber da ID eindeutig ist vewirkt das DISTINCT ja nichts.

Zitat von mkinzler:

Ja, aber da ID eindeutig ist vewirkt das DISTINCT ja nichts.

Aber innerhalb der Quelle ist die ID nicht eindeutig, sondern soll erst im Ziel eindeutig (und PrimaryKey) sein.

Außerdem: beziehst Du Dich auf einen speziellen meiner Versuche? Die verschiedenen Werte von Name2 und Name3, über die ich im Ergebnis hinwegsehen will, bringen FB beim DISTINCT wegen des PrimaryKey durcheinander. Jürgen

Du musst erst mal definieren, was für dich ein identischer Datensatz ist. Und über diese Spalten musst du ein "select distinct" ausführen.

Willst du für diese Datensätze dann noch eine von eventuell mehreren IDs übernehmen, musst du ein Kriterium finden, welche ID genommen werden soll (z.B. jeweils die Kleinste). Das Kriterium muss über eine Agregatfunktion definierbar sein, wie z.B. Min

Also könnte deine Abfrage in etwa so lauten:

markieren

SQL-Code:

select min(ID), Name2, Name3
from Tabelle
group by Name2, Name3

Zitat von Jelly:

Du musst erst mal definieren, was für dich ein identischer Datensatz ist. Und über diese Spalten musst du ein "select distinct" ausführen.

Das ist klar: ID oder Name1; jeder Wert für sich ist eindeutig, die Kombination ist also die Basis der Zieltabelle.

Zitat:

Willst du für diese Datensätze dann noch eine von eventuell mehreren IDs übernehmen, musst du ein Kriterium finden, welche ID genommen werden soll (z.B. jeweils die Kleinste). Das Kriterium muss über eine Agregatfunktion definierbar sein, wie z.B. Min

Nein, ich will je einen von teilweise mehreren verschiedenen Werten für Name2 und Name3 übernehmen. Welcher das ist, ist mir ziemlich egal (deshalb auch FIRST 1 bei einem meiner Versuche). Am liebsten hätte ich etwas mit Status 'G' in der Quelle (aber das gibt es nicht immer...). Aber nichts davon passt zu einer Aggregatfunktion, oder?

Jürgen

Beim DISTINCT wird überprüft ob alle Felder pübereinstimmen.

1, Peter, Achim, Tina
2, Peter, Achim, Tina

sind dann verschieden!

Wie sieht dein Schema aus?

Bitte sehr:

markieren

Code:

  STR_ID STR_NR  STATUS NAME_SORT       NAME46             NAME22
---------------------------------------------------------------------
     837     1     S      AGASTR          Agastr.           Agastr.
     838     1     G      AGATHAGASSE     Agathagasse       Agathagasse
     839     1     G      AGATHAPLATZ     Agathaplatz       Agathaplatz
     840     1     S      AGATHARIEDERSTR Agatharider Str.  Agatharider Str.
     840     1     G      AGATHARIEDERSTR Agatharieder Str. Agatharieder Str.
     841     1     G      AGATHASTR       Agathastr.        Agathastr.
     841     2     G      AGATHASTR       Agathastr.        Agathastr.
     841     3     G      AGATHASTR       Agathastr.        Agathastr.
     841     4     G      AGATHASTR       Agathastr.        Agathastr.
     841     5     G      AGATHASTR       Agathastr.        Agathastr.
     841     6     G      AGATHASTR       Agathastr.        Agathastr.
     842     1     G      AGATHAWEG       Agathaweg         Agathaweg
     843     1     G      AGATHELASCHWEG  Agathe-Lasch-Weg  Agathe-Lasch-Weg

Zieltabelle soll enthalten:

markieren

Code:

  STR_ID NAME_SORT       NAME46             NAME22
---------------------------------------------------------------------
     837  AGASTR          Agastr.           Agastr.            //  Status 'G' fehlt
     838  AGATHAGASSE     Agathagasse       Agathagasse
     839  AGATHAPLATZ     Agathaplatz       Agathaplatz
     840  AGATHARIEDERSTR Agatharieder Str. Agatharieder Str.  //  Status 'G' vorziehen
     841  AGATHASTR       Agathastr.        Agathastr.         //  Str_Nr 1 übernehmen
     842  AGATHAWEG       Agathaweg         Agathaweg
     843  AGATHELASCHWEG  Agathe-Lasch-Weg  Agathe-Lasch-Weg

Jürgen

Nachtrag: Order by Status hilft mir nicht, weil es auch '1', '2' usw. als Status geben kann.

Vielleicht sollte man auch mal das Schema richtigstellen. Welche Bedeutung ahben die verchiedenen Namen? Was bezweckst du mit der Abfrage (zu was die 2. Tabelle)?