[Erledigt] SELECT DISTINCT und JOIN - Redundanzen verringern

[Jürgen Thomas]

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.

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:

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:

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:

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

[mkinzler]

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

[Jürgen Thomas]

Zitat:

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

[mkinzler]

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

[Jürgen Thomas]

Zitat:

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

[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.

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:

SQL-Code:

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

[Jürgen Thomas]

Zitat:

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

[mkinzler]

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?

[Jürgen Thomas]

Bitte sehr:

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:

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.

[mkinzler]

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)?

[Jelly]

Zitat:

Zitat von Jürgen Thomas

die ID ist ja eindeutig, und die will ich behalten.

und

Zitat:

Zitat von Jürgen Thomas

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

widerspricht sich. Ich erkenne da keine eindeutige ID, sondern eher sowas, dass es für eine ID (z.B. 840) unterschiedliche Schreibweisen für die Strassennamen gibt. Es handelt sich aber wohl um ein und die gleiche Strasse.

Und wenn ich dein nun endlich richtig verstanden hab, willst du für jede ID (837-843) jeweils einen einzigen Datensatz in deine Zieltabelle schreiben. Welcher, scheint dir egal zu sein... Da wird dir also nix anderes übrig bleiben, als DS für DS zu durchlaufen, sortiert nach ID, und sobald du eine neue ID erhälst, schreibst du den DS in deine Zieltabelle. Anders wird das wohl nicht klappen.

[Jürgen Thomas]

Zitat:

Zitat von mkinzler

Vielleicht sollte man auch mal das Schema richtigstellen. Welche Bedeutung ahben die verchiedenen Namen?

Die Datenstruktur ist von der Post vorgegeben. Die Quelldatei ist eine Textdatei (feste Feld- und Satzlängen) mit 150 MB. Ich will sie in mehrere Tabellen in einer "richtigen" Datenbank übernehmen.

• Str_ID ist die bundesweit eindeutige laufende Nummer zu einem Straßennamen.
• Str_Nr ist die laufende Nummer für alle Straßen mit gleichem Namen.
• Name_Sort ist die eindeutige, sortierbare Bezeichnung.
• Name46 ist die "lange", normale Schreibweise mit max. 46 Zeichen.
• Name22 ist eine Verkürzung mit max. 22 Zeichen für den Fall, dass ein Adressenfeld nicht lang genug ist.
• Status = 'G' sind gültige Straßennamen. Andere Werte 'W', 'S', '1' usw. verweisen darauf, dass der Straßenname in diesem Ort korrigiert oder geändert wurde.

Zitat:

Was bezweckst du mit der Abfrage (zu was die 2. Tabelle)?

Redundanzen vermeiden, vor allem wegen der drei Schreibweisen: Die Königsberger Straße gibt es 172x, eine Friedrich-Ebert-Str. 188x und eine Gartenstraße 285x.

Da ich (anders als die Post-Textdatei) mit einer relationalen DB arbeite, brauche ich sowieso getrennte Nachschlagetabellen mit einem PrimaryKey. Da bietet sich eine Tabelle "Straßennamen" an. Jürgen

@Jelly
Das, was Du zitiert hast, ist die Quelltabelle. "... jeweils einen einzigen Datensatz in deine Zieltabelle schreiben. Welcher, scheint dir egal zu sein..." Nicht ganz egal (ich bevorzuge Status 'G'), aber fast.

[mkinzler]

Dann hol dir jeweils den 1. Datensatz mit der selben ID.

SQL-Code:

select STR_ID, MIN( name1), MIN(Name2), MIN(Name3) from Tabelle ORDER BY STR_ID;

[Jürgen Thomas]

Zitat:

Zitat von mkinzler

Dann hol dir jeweils den 1. Datensatz mit der selben ID.

SQL-Code:

select STR_ID, MIN( name1), MIN(Name2), MIN(Name3) from Tabelle ORDER BY STR_ID;

Das scheint das einfachste Verfahren zu sein.

Kann ich da noch den Status einbauen? Mir ist gerade die Idee gekommen, dass ich den Status 'G' durch '0' ersetzen könnte; dann wäre bei "Order By Str_ID, Status" der jeweils erste Treffer derjenige, den ich in die Zieltabelle nehmen möchte. Aber mir fehlt noch eine Idee, wie ich das mit "FIRST 1" kombinieren könnte. (Ich vermute, dass ein doppeltes SELECT mit SELF-JOIN zu meinen Problemen geführt hat.) Jürgen

[Jelly]

Oder eventuell so:

SQL-Code:

select StrId, Status, Name_Sort, Name46, Name22
from Tabelle
where StrNr = 1

[mkinzler]

Welchen Status willst du?

[Jürgen Thomas]

Zitat:

Zitat von mkinzler

Welchen Status willst du?

Siehe Quelltabelle in #9. Vorzugsweise 'G' (= gültig), aber den gibt es nicht bei allen Namen (in meinem Beispiel bei ID=837). Stattdessen kann ich, wie gesagt, auch '0' benutzen. Jürgen

[Jürgen Thomas]

Danke an Jelly und mkinzler,

aufbauend auf Euren Hinweisen habe ich es geschafft und dabei sogar den Status eingebaut. Die entscheidenden Ideen waren, MIN() zu verwenden mit passender Sortierung. Zusätzlich werden GROUP BY und ORDER BY sowie weiterhin DISTINCT benötigt. So sieht jetzt mein Code aus:

SQL-Code:

INSERT INTO STR_Namen
            ( ID, Aendg_per, 
              Name_Sort, Name_lang, Name_kurz )
       SELECT DISTINCT str_id,
              max(Geltung),
              min(Name_sort),
              SUBSTR(min(Status || Name46), 2,50),
              SUBSTR(min(Status || Name22), 2,50)
         FROM Quelle_Post_St
         GROUP BY Str_id, Name_sort
         ORDER BY Str_id, Name_sort

Alle Informationen werden so übernommen wie gewünscht; der bevorzugte Status 'G' musste vorher durch '0' (Null) ersetzt werden. Meine Idee dabei ist, dass der Status vor die (variablen) Namen gesetzt wird, dann der Minimalwert übernommen und vor dem Speichern wieder entfernt wird.

Das Ganze läuft auch in akzeptabler Geschwindigkeit von weniger als zwei Minuten (nicht gemessen, nur gefühlt - bei 512 MB RAM).

Danke für Eure Hilfe! Jürgen