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