Autor Thema: Synonym-Doubletten: SQL-Lösung?  (Gelesen 2863 mal)

Martin Lemke

  • Administrator
  • *****
  • Beiträge: 14782
  • TK 2130 Lübeck, Schleswig-Holstein, Germany
    • Spinnenerfassung in SH
Synonym-Doubletten: SQL-Lösung?
« am: 2014-02-27 12:51:36 »
Viele Synonyme stehen doppelt und dreifach in der DB. Das führt bei einigen Abfragen zu Problemen. Ich vermute, dass irgend ein Skript dafür verantwortlich ist, dass diese Doubletten auftreten.

Nun meine Frage: Gibt es die Möglichkeit per SQL festzustellen, welche Einträge doppelt sind (und eventuell die Doubletten gleich entfernen)?

Für das erste Problem habe ich im Netz eine Lösung gefunden:
SELECT name, COUNT(*) FROM sf_gd_synonym GROUP BY name HAVING COUNT(*) > 1

Ergebnis: 2825 Taxa haben Mehrfacheinträge in der DB. Wie kriege ich die automatisiert gelöscht?

Dafür habe ich zwar auch Lösungen im Netz gefunden (a, b, c), aber da ich den Code nicht wirklich verstehe, scheue ich mich, das einzusetzen. Da wäre mir das Statement einer unserer Datenbank-Spezis lieber.

Ursache der Mehrfacheinträge ist die Seite der Taxonpflege: Spezial:TaxonBearbeiten1. Seit ich Trigger eingebaut habe, erscheinen Synonyme doppelt (muss ja so sein -- ich hatte es versäumt, das zu ändern). Ich werde die Seite neu bauen und bei der Gelegenheit etwas komfortabler gestalten.

Martin

1: Dieses Backend bitte momentan nicht benutzen.
« Letzte Änderung: 2014-03-02 11:53:04 von Eveline Merches »
Profil bei Researchgate.net

DAS waren noch Zeiten: Nowegen 2011.

Eveline Merches

  • Kerngruppe
  • ******
  • Beiträge: 3667
  • Altötting, Südostbayern, TK 7742-3
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #1 am: 2014-02-28 14:08:14 »
Hallo Martin,
bevor wir Daten löschen, sollten wir genau schauen, was die Tabelle beinhaltet und was drin sein soll.

Ich habe gesehen, dass es viele Einträge gibt, die keine Angabe zum Autor haben. Ist das richtig?

So kommt es dann auch vor dass ein Synonym allein deshalb zweimal auftaucht, weil es einmal mit und einmal ohne Autor in dieser Tabelle steht.

Wenn ich das richtig sehe, wird diese Tabelle nirgends referenziert, sodass es unkritisch sein sollte, wenn Datensätze gelöscht werden. Aber sicher bin ich mir noch nicht.
Auch weiß ich nicht, wo das Wiki die Tabelle nutzt, und welche felder sie verwendet. Wenn die Abfargen im Wiki nie auf die ID der Tabelle, sondern immer auf das Feld "Name" gehen, wird es auch leichter.
Generell ist es schwierig, alle bis auf einen Datensatz einer bestimmten Gruppe zu löschen.
Aber dazu lasse ich mir hier mal etwas einfallen.

liebe Grüße
Eveline
Ahme den Gang der Natur nach. Ihr Geheimnis ist Geduld.

Eveline Merches

  • Kerngruppe
  • ******
  • Beiträge: 3667
  • Altötting, Südostbayern, TK 7742-3
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #2 am: 2014-02-28 15:11:54 »
Wenn nur noch ein Datensatz je Gruppe übrig bleiben soll,
könnte man in mehreren Schritten, jeweils den ältesten (oder den jüngsten) Datensatz der Gruppe löschen.
Da in der Gruppe nur die erscheinen, die mindestens 2 Datensätze haben, sollte man nach max 3 Durchgängen nur noch einzelne haben.
Wenn die Angabe zum Autor wichtig ist, sollten zuerst nur Datensätze gelöscht werden, die keine Angabe im Feld Autor haben.
Da das recht viele (4260) in der Tabelle sind, müsste da noch mal jemand drüber schauen.

Synonymeinträge ohne Autor:
Zitat
select *
from sf_gd_synonym
where autor =''
order by name;

Liste der jüngsten Datensätze je Gruppe (Autor unberücksichtigt!)
Zitat
select min(id)as letzter_eintrag , id_art, name
from sf_gd_synonym
group by id_art, name
having count(*) >1;

Liste aller Gruppen mit gleicher id_art und gleichem namen
Zitat
select count(*)as anzahl , id_art, name
from sf_gd_synonym
group by id_art, name
having count(*) >1;

Wenn die Tabelle bereinigt ist, sollte man vielleicht auch noch die "Verschreiber" löschen. Wo finde ich den eine korrekte Liste mit den Synonymen? Hat Platnick die?

liebe Grüße
Eveline
Ahme den Gang der Natur nach. Ihr Geheimnis ist Geduld.

Martin Lemke

  • Administrator
  • *****
  • Beiträge: 14782
  • TK 2130 Lübeck, Schleswig-Holstein, Germany
    • Spinnenerfassung in SH
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #3 am: 2014-03-01 14:33:47 »
Wie gesagt, viele der Einträge kommen durch ein Skript zustande, das ich nicht berichtigt hatte, nach dem ich Trigger eingebaut hatte.

Ich hatte angefangen, Doubletten von Hand zu löschen, aber dabei übersieht man leicht etwas und es ist eine heiden Arbeit, weil tausende von Datensätzen zu bearbeiten sind. Beim Löschen von Hand habe ich darauf geachtet, dass, wenn möglich, ein Datensatz mit Autor vorhanden bleibt. Eine Lösung per Skript ist besser.

Die Autoreneinräge werden allerdings nirgends verwendet. Es ist aber nicht auszuschließen, dass bei einigen Tieren beide beide Angaben zwingend notwendig sind, um die Taxa eindeutig zuordnen zu können. Darum finde ich es erstrebenswert, das Feld Auto auszufüllen.

Im Grund müsste ich das Skript, das sich beim Auftreten mehrerer Synonyme verschluckt (Fundlistengenerator), robuster machen.

Das Skript TaxonBearbeiten habe ich ja schon angefangen, besser zu konzeptionieren und will es demnächst besser umsetzen. Heute habe ich aber geholfen eine Heidefläche aufzuräumen und morgen habe ich Dienst. An diesem Wochenende mache ich also nicht mehr sehr viel. Aber vielleicht ein bisschen.

Martin
Profil bei Researchgate.net

DAS waren noch Zeiten: Nowegen 2011.

Katja Duske

  • Kerngruppe
  • ******
  • Beiträge: 4245
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #4 am: 2014-03-01 15:13:26 »
Wo finde ich denn eine korrekte Liste mit den Synonymen? Hat Platnick die?

Ja, die stehen in chronologischer Reihenfolge unter dem gerade gültigen Artnamen.

Katja

Eveline Merches

  • Kerngruppe
  • ******
  • Beiträge: 3667
  • Altötting, Südostbayern, TK 7742-3
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #5 am: 2014-03-01 17:07:45 »
Zitat
Wie gesagt, viele der Einträge kommen durch ein Skript zustande, das ich nicht berichtigt hatte, nach dem ich Trigger eingebaut hatte.

Ich hatte angefangen, Doubletten von Hand zu löschen, aber dabei übersieht man leicht etwas und es ist eine heiden Arbeit, weil tausende von Datensätzen zu bearbeiten sind. Beim Löschen von Hand habe ich darauf geachtet, dass, wenn möglich, ein Datensatz mit Autor vorhanden bleibt. Eine Lösung per Skript ist besser.

Solche Fehler gehören einfach dazu, wenn man soetwas baut. Die Tabelle hat 13 598 Einträge, da wird man blöd, wenn man das von Hand macht. In MySQL geht es zwar nicht sonderlich bequem, aber mit mehreren Anläufen klappt das ganz gut. Ich musste leider dazu temporäre Tabellen anlegen, weil MYSQL an der Stelle eingeschränkte Funktionalität hat. Um das Ganze zu testen habe ich die vorhandene Tabelle kopiert: sf_gd_synonym_dublette
An der habe ich rumprobiert und folgenden Weg gefunden:
Ich gruppiere auf id_art und name. Innerhalb jeder dieser Gruppe schaue ich auf den ältesten Datensatz (hat die kleinste ID) und prüfe, ob die einen Eintrag in Autor haben.
Wenn nicht, schreibe ich die in eine temporäre Tabelle: synonym_dublette_ohne_autor (92) --> anschließend lösche ich die in der sf_gd_synonym_dublette
Dann wiederhole ich den Vorgang mit kleinster ID und größter Id, finde aber keine doppelten Einträge mehr, die keinen Autor haben.
Also gruppiere ich wieder in der Haupttabelle (sf_gd_synonym_dublette) und lösche dann den jeweils ältesten Datensatz (Nachdem ich die in eine temporäre Tabelle geschrieben habe).
Dann gruppiere ich wieder , lösche wieder, gruppiere wieder usw. bis beim gruppieren keine doppelten Datensätze mehr gefunden werden. (6 Durchgänge)
Am Ende stehen nur noch 8.000 Datensätze in der Tabelle.

Jetzt müsste einer da drauf schauen, ob das so passt, dann mache ich das auch für die Originaltabelle und lösche anschließend alle temporären Tabellen.

Zitat
select *
from  sf_gd_synonym_dublette
;

Es bleiben noch 26 Einträge die bei gleichem Namen eine unterschiedliche id_art haben:
Zitat
select count(*) as anz, name
from  sf_gd_synonym_dublette
group by name
having count(*) >1
;
Das sind wahrscheinlich keine Dubletten oder?

Die Schreibfehlereinträge muss man dann aber tatsächlich mal händisch durchgehen.
Zitat
select * from sf_gd_synonym(_dublette)
where id_art in
(select id_art
from  sf_gd_synonym(_dublette)
group by id_art
having count(*) >1 )
order by id_art, name;
Aber das kann man irgendwann mal machen. In der sortierten Anzeige, sieht man die ja sofort.

liebe Grüße
Eveline
Ahme den Gang der Natur nach. Ihr Geheimnis ist Geduld.

Martin Lemke

  • Administrator
  • *****
  • Beiträge: 14782
  • TK 2130 Lübeck, Schleswig-Holstein, Germany
    • Spinnenerfassung in SH
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #6 am: 2014-03-01 18:11:35 »
Es bleiben noch 26 Einträge die bei gleichem Namen eine unterschiedliche id_art haben:
select count(*) as anz, name
from  sf_gd_synonym_dublette
group by name
having count(*) >1
;

Das sind wahrscheinlich keine Dubletten oder?

Dieser Fall sollte eigentlich nicht auftreten! Hier sollte überprüft werden, ob dem jeweils genannten id_art in der sf_fgd_art Tabelle eine id zuzuorden ist. Wenn nicht, löschen!

Was dann noch übrig ist, muss von Hand überprüft werden, denn dann liegen Fehler vor.

Ich bin gerade vom Wochenendeinkauf zurück und noch immer von der Heideaufräumaktion (junge Birken und Kiefern wurden mit Motorsensen umgelegt und mussten von Hand aus der Heide entfernt werden) fix und fertig. Kann man glauben, dass man im Hintern Muskelkater hat?

Martin
Profil bei Researchgate.net

DAS waren noch Zeiten: Nowegen 2011.

Martin Lemke

  • Administrator
  • *****
  • Beiträge: 14782
  • TK 2130 Lübeck, Schleswig-Holstein, Germany
    • Spinnenerfassung in SH
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #7 am: 2014-03-01 18:47:46 »
Das sind ja nur 26, das prüfe ich mal schnell:

Misumenops tricuspidatus
Ozyptila blackwalli
Pardosa proxima
Pardosa wagleri

Peniza europaea
Philodromus corticinus
Poecilochroa albomaculata
Prosthesima vespertina
Rhaebothorax pallidus
Siro duricorius corfuanus
Siro minutus
Siro noctiphilus
Siro silhavyi
Siro teyrovskyi
Tegenaria campestris
Theridion instabile
Theridion simile
Trichoncus trifidus
Troglohyphantes excavatus
Troglohyphantes lakatnikensis
Typhochrestus pallidus
???
Typhocrestus tenuis ???
Zelotes balcanicus
Zelotes donnezanus
Zelotes errans
Zelotes silvicola
???

Mit den roten Fragezeichen markierte habe ich in sf_gd_art und sf_gd_synonym nicht gefunden. Durchgestrichene habe ich gelöscht (hoffentlich war das kein Fehler).

Bei den restlichen habe ich die überflüssigen Synonyme von Hand  in der Tabelle sf_gd_synonym gelöscht.

Martin
Profil bei Researchgate.net

DAS waren noch Zeiten: Nowegen 2011.

Eveline Merches

  • Kerngruppe
  • ******
  • Beiträge: 3667
  • Altötting, Südostbayern, TK 7742-3
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #8 am: 2014-03-01 19:22:32 »
Dann kann ich also die sf_gd_synonym auf die von mir beschriebene Art und Weise bereinigen?

Eveline
Ahme den Gang der Natur nach. Ihr Geheimnis ist Geduld.

Martin Lemke

  • Administrator
  • *****
  • Beiträge: 14782
  • TK 2130 Lübeck, Schleswig-Holstein, Germany
    • Spinnenerfassung in SH
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #9 am: 2014-03-01 19:27:39 »
Dann kann ich also die sf_gd_synonym auf die von mir beschriebene Art und Weise bereinigen?

Wenn das beseitigte, das einzige Hindernis war, ja.

Martin
Profil bei Researchgate.net

DAS waren noch Zeiten: Nowegen 2011.

Eveline Merches

  • Kerngruppe
  • ******
  • Beiträge: 3667
  • Altötting, Südostbayern, TK 7742-3
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #10 am: 2014-03-01 19:46:38 »
Ein Hindernis gibt es nicht, aber bevor ich über 5000 Datensätze lösche, sollte da noch jemand zweites drauf geschaut haben.
Deshalb habe ich es ja erstmal in einer Kopie der Tabelle gemacht.
Alternativ lege ich nocheinmal eine Kopie der Originaltabelle an und lösche dann wie beschrieben die Datensätze. Wenn es dann Probleme gibt, nehmen wir die alte wieder in Betrieb.

liebe Grüße
Eveline
Ahme den Gang der Natur nach. Ihr Geheimnis ist Geduld.

Martin Lemke

  • Administrator
  • *****
  • Beiträge: 14782
  • TK 2130 Lübeck, Schleswig-Holstein, Germany
    • Spinnenerfassung in SH
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #11 am: 2014-03-01 20:01:33 »
Wenn es dann Probleme gibt, nehmen wir die alte wieder in Betrieb.

Die klassische Backup-Lösung. Ja, das ist vernünftig.

Martin
Profil bei Researchgate.net

DAS waren noch Zeiten: Nowegen 2011.

Eveline Merches

  • Kerngruppe
  • ******
  • Beiträge: 3667
  • Altötting, Südostbayern, TK 7742-3
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #12 am: 2014-03-01 20:46:42 »
Das habe ich jetzt erledigt.
sf_gd_synonym hat keine Dubletten mehr.
Allerdings fehlen für 18 Arten in sf_gd_art noch Einträge in dieser Tabelle:
Zitat
select *
from sf_gd_art
where id not in (select id_art from sf_gd_synonym)
;

Die habe ich mit dem derzeitigen Artnamen in die Tabelle geschrieben:
Zitat
insert into sf_gd_synonym (id_art, name, autor) (select id,name, autor from sf_gd_art where id not in (select id_art from sf_gd_synonym));

Ist das in Ordnung?

liebe Grüße
Eveline
Ahme den Gang der Natur nach. Ihr Geheimnis ist Geduld.

Martin Lemke

  • Administrator
  • *****
  • Beiträge: 14782
  • TK 2130 Lübeck, Schleswig-Holstein, Germany
    • Spinnenerfassung in SH
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #13 am: 2014-03-01 21:09:48 »
Theoretisch perfekt! -- Ich habe es nicht nachgeprüft und fühle mich dazu auch im Moment nicht in der Lage. Ich bin total erledigt.

Martin
Profil bei Researchgate.net

DAS waren noch Zeiten: Nowegen 2011.

Eveline Merches

  • Kerngruppe
  • ******
  • Beiträge: 3667
  • Altötting, Südostbayern, TK 7742-3
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #14 am: 2014-03-02 09:31:02 »
Zitat
Theoretisch perfekt! -- Ich habe es nicht nachgeprüft und fühle mich dazu auch im Moment nicht in der Lage. Ich bin total erledigt.
Ist ja auch kein Problem, da es alle Daten noch in der Archiv-Tabelle gibt.

Bei Gelegenheit solltest Du aber schauen, ob die Funktionen, die die Tabelle nutzen nun (noch) fehlerfrei laufen.

liebe Grüße
Eveline
Ahme den Gang der Natur nach. Ihr Geheimnis ist Geduld.

Martin Lemke

  • Administrator
  • *****
  • Beiträge: 14782
  • TK 2130 Lübeck, Schleswig-Holstein, Germany
    • Spinnenerfassung in SH
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #15 am: 2014-03-02 11:09:51 »
Nicht mit den Doubletten zurecht kam der Fundlisten-Generator: http://liste.eu-arachnida.de/eingabe.php

Der funktioniert nun wieder. Habe aber nur 3 Arten getestet.

Martin
Profil bei Researchgate.net

DAS waren noch Zeiten: Nowegen 2011.

Eveline Merches

  • Kerngruppe
  • ******
  • Beiträge: 3667
  • Altötting, Südostbayern, TK 7742-3
Re: Synonym-Doubletten: SQL-Lösung?
« Antwort #16 am: 2014-03-02 11:52:44 »
Danke!
Dann setze ich dieses Thema mal auf erledigt.

liebe Grüße und einen schönen Sonntag!
Eveline
Ahme den Gang der Natur nach. Ihr Geheimnis ist Geduld.