SQL redundante werte nur einmal

[lordg2009]

Hi,

meine Tabelle enthält als primary key eine id, verschiedene andere Werte und einen hash.

Nun möchte ich alle Werte per SELECT abfragen, wobei jeder hash-wert dabei nur einmal vorkommen soll. Die Tabelle ist aufsteigend nach einer timestamp sortiert. Sollte ein hash-wert mehrfach enthalten sein, soll nur die Zeile ausgegeben werden, in der der hash-wert als erstes vorkommt, nach meiner Sortierung also am frühesten.

SELECT DISTINCT gibt ja nur die Spalte aus, die auf Redundanze getestet werden soll und ist daher wahrscheinlich nicht geeignet.

Hält das GROUP BY Statement die Reihenfolge ein?

Vielen Dank für eure Hilfe

 

[hroessler]

Hallo,
erst mal: Von welcher DB sprechen wier hier? Wieso haben verschiedene Zeilen den gleichen Hashwert? Hashes sollten ja eindeutig sein. Und ein ein "select distict * from..." selektiert sehr wohl die komplette Zeile ;-)

greetz
​hroessler

 

[RalphS]

DISTINCT gibt eindeutige Spalten. Ist der Hashwert doppelt und eine der anderen Einträge, zB der Timestamp, verschieden, dann gibt auch DISTINCT beide Hashwerte.

GROUP BY könnte funktionieren, erfordert dann aber eine vernünftige Aggregatfunktion auf den Hashwert.

Ansonsten per SELFJOIN mit SELECT DISTINCT. Muß man dann schauen, daß man sich sein Ergebnis so zusammenbaut, daß DISTINCT auch wirklich eindeutige Spalten kriegen kann UND man mit dem JOIN das drangebunden kriegt, was auch wirklich rangebunden werden soll.

Sonst blieben noch korrelierte Unterabfragen. Die wären aber... suboptimal.


Ansonsten kann man natürlich, dem Hinweis meines Vorredners folgend, einfach einen UNIQUE-Constraint (oder Index) auf die Hash-Spalte setzen (versuchen). Jedenfalls dann, wenn die Hashes tatsächlich eindeutig sein "müssen" und nicht gleich sein können (sollen/dürfen).

Doppelte Hashes findet man mit

SELECT hash, count(hash) FROM table GROUP BY hash HAVING COUNT(hash)>=2;

raus.

PS - Tabellen in SQL sind NICHT per definitionem geordnet. ORDER BY weglassen ist eine ganz schlechte Idee, wenn das Ergebnis geordnet sein SOLL. GROUP BY und ORDER BY schließen sich auch nicht aus.

 

[Drexel]

100%ig Kollisionssicher kann eine Hashfunktion nicht sein, da Du mehrere Millionen Bits auf 32 Bit z.B. Abbildest: https://de.m.wikipedia.org/wiki/Kollisionssicherheit

Neben Group By kannst Du doch noch ein Order by einfügen um Deine Reihenfolge zu garantieren.

Edit: Ah sorry stimmt nicht: https://stackoverflow.com/questions/10030787/using-order-by-and-group-by-together

 

[character]

Man müsste wirklich wissen, welches DBMS verwendet wird. Allgemein hört sich das aber nach einem Fall für ROW_NUMBER und PARTITION an.

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY hash ORDER BY date DESC) AS row_number
  FROM table
) AS rows
WHERE row_number = 1

 

[RalphS]

@Drexel: die von Dir verlinkte Abfrage in dem Post dort ist Käse. Was soll denn SELECT * FROM Tabelle GROUP BY spalte liefern?

Das kann nicht funktionieren, nicht mit und auch nicht ohne ORDER.

Der einzige Haken für GROUP BY ist die WHERE-Klausel, die ggf als HAVING umgebaut werden muß. Ansonsten kann und soll man aber GROUP BYs sortieren.


-- Oder anders gesagt, Du kannst Dein "stimmt nicht" gern zurückziehen, weil du einfach Recht hattest mit dem, was Du sagtest.

 

[Drexel]

Ich habs selber nicht ausprobiert und da wir auch nicht wissen um welches DBMS sich handelt ist es relativ spekulativ. Wenn aber erst gruppiert und dann sortiert wird, kannn Käse rauskommen...

 

[lordg2009]

Hi,

danke für eure Antworten. Folgendes habe ich mir jetzt überlegt:

Ich nutze zum Beispiel die Testdatenbank von w3schools
https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

Bedingungen:

1. Ich möchte, dass jede 'Country' nur einmal vorkommt.
2. Sollten mehrere Zeilen die gleiche 'Country' haben, soll die erste in der Reihenfolge kommen.
3. Dabei sortiere ich jeweils nach CustomerID, da es in der BeispielDB keine timestamp gibt

Ich habe beobachtet, dass der Befehl

SELECT * FROM Customers GROUP BY Country ORDER BY CustomerID

jede 'Country' nur einmal zulässt. Die Zeile, die dabei angezeigt wird, ist die zuletzt vorgekommene. Man könnte denken, dass der Befehl GROUP BY Country die Tabelle von Anfang bis Ende durchläuft und bei erneuten Vorkommen des gleichen Wertes für 'Country' die bereits gefundene Zeile überschreibt (reine Spekulation).
Die genannte Abfrage spuckt eine Tabelle mit den jeweils letzten CustomerIDs aus, die eine bestimmte 'Country' beinhalten.

Da man sich auf die Reihenfolge ohne Sortierung nicht verlassen kann und ich die ersten CustomerIDs angezeigt haben wollte, verwendete ich ein subStatement, dass ich absteigend sortierte und kam zum gewünschten Ergebnis:

SELECT * FROM (SELECT * FROM Customers ORDER BY CustomerID DESC) subStatement GROUP BY Country ORDER BY CustomerID ASC;

Jetzt die Frage:
Ist dieses Verhalten von GROUP BY definiert? Kann man sich darauf verlassen, dass GROUP BY immer die Spalte mit dem zuletzt vorgekommenen Wert ausgibt?

 

[RalphS]

MySQL... sieht es jedenfalls danach aus.

Besorg Dir ein ordentliches DBMS, wie zB Postgres.

Warum? Weil ein solches Verhalten Käse ist und von DBMS nicht zugelassen werden sollte.

GROUP BY aggregiert. Heißt: Datensätze werden zusammengefaßt. Dafür gibt es Aggregatfunktionen, wie zB MIN(), MAX(), AVG() für Minimum, Maximum und Durchschnitt (beispielsweise).

SELECT * GROUP BY ist IMMER Käse. Das darfst Du getrost vergessen.

Stattdessen hier als Beispiel:

SELECT PLZ, ORT, count(*) AS AnzahlBewohner FROM Adressen GROUP BY PLZ, ORT

Da wird eine Adressdatenbank angeschaut. Die Abfrage gruppiert nun nach PLZ und ORT aufgetrennt, heißt: alle Einträge, wo PLZ und Ort gleich sind, werden in eine einzelne Zeile eingestampft. Die restlichen Daten können nur noch berücksichtigt werden, wenn sie aggregiert werden. Hier mit der COUNT-Funktion, welche dafür sorgt, daß ich am Ende erfahre, wieviele Adressen es in jedem Ort X mit der PLZ Y gibt. Ich könnt die PLZ auch weglassen oder den ORT: dann erfahre ich, daß es zB 10 Adressen gibt in Berlin und fünf in Brandenburg oder halt 20 im PLZ-Bereich 12679. Nehm ich beides, wird es genauer und ich erfahre, daß sich die 10 Berliner Adressen auf fünf PLZ-Bereiche aufteilen (und wieviele dort jeweils stecken) - analog für PLZ, die zu mehreren Orten gehören.


Ohne Aggregatfunktion auf die nicht-gruppierten Spalten weiß das DBMS nicht, was es machen soll. Es soll zB "Hausnummer" anzeigen. Nur: Welche Hausnummer? Eine geht nur pro Gruppe. Normal müßte ich sagen "die kleinste" oder "die größte" oder sonst "gib mir genau DIESE Hausnummer". Alles andere ist unbestimmt und damit ein Fehler.

PARTITION ist, denk ich, schon der beste Ansatz. Ansonsten mit GROUP BY über den Hash, aber dann müßtest Du für jede weitere Spalte schauen, daß Du eine passende Aggregatfunktion findest für die zusammengefaßten Zeilen.

 

[ayngush]

Abgesehen davon ist "SELECT *" ganz pauschal ausgedrückt ein schlechter, weil einzig auf eigener Schreibfaulheit basierender, "Stil", der auch nicht vernünftig vom Datenbankserver optimiert werden kann (Nutzung der Indizes), oftmals einfach zu viele Daten "durch das Netz in die Systeme schaufelt" (Performance, Ressourcennutzung) und manchmal, bei doppelt vergebenen Bezeichnern, auch einfach das Binding hart "verkackt" und man so unerwartete Ergebnisse zurück bekommt.

Man kann recht pauschal behaupten: An jedem Quellcode, in dem "SELECT * FROM" öfter verwendet wird, hat kein Experte mitgewirkt. Da lohnt sich das genaue "hinschauen" immer doppelt.

 

[Andreas_]

Abgesehen davon ist "SELECT *" ganz pauschal ausgedrückt ein schlechter, weil einzig auf eigener Schreibfaulheit basierender, "Stil", der auch nicht vernünftig vom Datenbankserver optimiert werden kann (Nutzung der Indizes),

Auch bei "SELECT *" kann der Datenbankserver optimieren und Indizes benutzen - an Hand der in where/having oder join benutzten Spalten.

Allerdings kann ich mich ehrlich gesagt nicht erinnern, jemals in einem Projekt mit "SELECT *" gearbeitet zu haben ...

 

[ayngush]

Ich sagte ja auch "kann", und nicht "wird es nicht".
Man schreibt es auch schon aus rein ästhetischen Gründen aus.
Wer fremden Code mit "SELECT *" mal warten musste der flucht wahrscheinlich immer noch ohne Ende, weil man nicht weiß, WAS da vom Backend "kommt" und wie es benannt wurde ohne zur (dann auch meistens nicht vorhandenen) Doku zu greifen... Das nervt härter als nicht optimal genutzte Indizes.

So oder so: Es ist einfach schlecht.
Zumal es ja eine Alternative gibt: Ordentlich hinschreiben, was man machen möchte.

 

[The Ripper]

weil man nicht weiß, WAS da vom Backend "kommt"

​doch weiß man?

 

[pizza4ever]

Es wird wohl auf eine Kombination von MIN und Group By hinauslaufen aber ohne Beispieldaten ist das schwierig

 

[ayngush]

​doch weiß man?

Muss man sich erst wieder ergründen. Im Quelltext ablesen kann man es nicht. Unnötig umständlich.

 

[RalphS]

Schlimmer noch, irgendwann baut einer rum am Backend und plötzlich bekommt man was ganz anderes.

Oder anders gesagt, nein man weiß es wirklich nicht. Man kann es zwar für "jetzt" rauskriegen, aber wenn nicht grad ein View dahintersteht, von dem man weiß, daß er nicht weiter verändert wird...

Protip: Datenbankabfragen möglichst genau spezifizieren. Das schließt Aliases mit ein. Und wenn man damit direkt arbeiten sollte: Schemas auch (gilt nicht, wenn eh nur auf eines zugegriffen wird).

Dann sieht meine DB-Abfrage so aus:

SELECT a.spalte_a, a.spalte_b, a.spalte_c 
FROM Tabelle A
 ...

und wenn man jetzt etwas ändern will oder muß, dann geht das ganz einfach, einschließlich JOINs: Einfach einen neuen Alias vergeben.

Es gibt paar DBMS, die maulen bei Konflikten von JOINs. SELECT * funktioniert dort nicht und SELECT a.*, b.* oder dergleichen ebenfalls nicht: haben beide Tabellen eine Spalte mit demselben Namen, dann kollidiert das in der Ausgabe und es gibt einen Fehler.

Völlig analog: habe ich zwei Tabellen mit JOIN und da ist das NICHT so, dann ist das zwar schön, aber den Moment, wo man selber (oder jemand anderes!) eine Spalte hinzufügt und buchstäblich rein zufällig heißt die genauso wie eine der Spalten in der anderen Tabelle, dann ist mein JOIN schon kaputt und die Abfrage liefert kein Ergebnis, sondern eine Fehlermeldung.

Wieder andere DBMS werfen hier keinen Fehler. Oracle 11g zum Beispiel handhabt das so, daß SELECT * über mehrere Tabellen dann gleichnamigen Spalten einen Unterstrich und einen Index anhängt (und man kriegt zB "Spalte" und "Spalte_1").

Schön, wenn dann dasselbe in den beiden Spalten stand. War das nicht der Fall, und wie oben ändert jemand das Datenbankschema, dann krieg ich keine Fehlermeldung, sondern ein falsches Ergebnis.

 

[lordg2009]

Es wird wohl auf eine Kombination von MIN und Group By hinauslaufen aber ohne Beispieldaten ist das schwierig

Beispieldaten findest im Post #8, sogar mit Editor, kannst du den SQL Befehl mit MIN und GROUP BY ausformulieren?

 

[Andreas_]

Beispieldaten findest im Post #8, sogar mit Editor, kannst du den SQL Befehl mit MIN und GROUP BY ausformulieren?

Ich sehe da zwar nicht wirklich Sinn drin, aber hier ist eine Möglichkeit ...

SELECT CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country FROM Customers
WHERE CustomerID IN (SELECT MIN(CustomerID) FROM Customers GROUP BY Country)
ORDER BY CustomerID ASC