Verstädnissfrage zu GROUP BY

cl90

Hallo.

Ich habe eine simple kleine Tabelle welche eine ID, einer userID, eine IP und einen Zeitstempel enthällt.
(SQL Fiddle Link) [url]http://sqlfiddle.com/#!2/e0a96[/url]

Diese Query:

SELECT userID, ip FROM userips ORDER BY ID DESC

Gibt mir immer die neusten einträge.

Jetzt würde ich nur gerne zu jeder userID ausschließlich den letzten Eintrag haben. Dafür wollte ich Group By nutzen.

SELECT userID, ip FROM userips GROUP BY userID ORDER BY ID DESC

Ich habe nur keine ahnung wieso bei ip jetzt die älteste IP steht und nicht (wie vorher) die neuste.
Offensichtlich wird Group By zuerst ausgeführt. Aber wie kann die Gruppierung hinter die Sortierung setzen? Ich möchte ja nicht einfach die erste ip, sondern die aktuellste.

Hallo cl90,

ich finde deinen Enthusiasmus sehr gut und auch, dass du dein Freizeitprojekt trotz kleiner Problemchen immer weiter treibst!

Die paar Queries, die du brauchst, kannst du dir wahrscheinlich meist irgendwie zusammenbauen oder findest etwas im Internet... Aber zum richtigen Verständnis ist es umbedingt nötig ein Buch über SQL kaufen und das durchzuarbeiten.

Zu deiner Frage: group by hat andere Ziele als wofür du es benutzen willst.

Was du machen willst, kannst du so machen:
SELECT ui1.userID, ui1.ip FROM userips ui1 where not exists (select * from userips ui2 where ui1.ID<ui2.ID and ui1.userID=ui2.userID);
Idee hier: Wir wollen die Tupel holen, für die es keine Tupeln mit einer größeren ID gibt -> Wenn es für ein Tupel kein Tupel mit einer größeren ID gibt, dann hat es ja die größte! (Alles natürlich pro userID)

oder so (dann hast du dein group by):
select userID,ip from userips where ID in (SELECT max(ID) FROM userips GROUP BY userID);
Idee: Finde für jede userID die größte ID und benutze dann die ID als Selektionskriterium.

Es gibt noch viele andere Lösungen... und diese beiden Lösungen musst du wahrscheinlich auch noch anpassen.

tntnet

Dafür musst Du ja im Prinzip jeden Eintrag mit einen anderen Vergleichen. Dafür ist HAVING in Verbindung mit GROUP BY da:

SELECT a.userID, a.ip
   FROM userips a
   JOIN userips b
     ON a.userID = b.userID
  GROUP BY a.userID, a.ip
 HAVING a.ID = max(b.ID)

hustbaer

cl90 schrieb:

Dafür wollte ich Group By nutzen.
SELECT userID, ip FROM userips GROUP BY userID ORDER BY ID DESC

Also MSSQL verweigert eine Abfrage dieser Art, und ich bin mir auch ziemlich sicher dass Standard-SQL das auch verbietet.
Du kannst nix selektieren was nicht entweder in der GROUP BY Liste vorkommt oder eine Aggregatfunktion ( min/max/avg/count ) verwendet.

@tntnet:
Dein (für mich sehr schwer zu entzifferndes) Konstrukt entspricht logisch genau der von Duplikateneliminator vorgeschlagenen Lösung mit "where ID in".
Und genau so (die "where ID in" Variante) würde ich es auch selbst schreiben.

hustbaer

Achso, in T-SQL kann man auch schön Ranking-Functions und Common-Table-Expressions kombinieren um das selbe zu machen:

; WITH latest_userips AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY userID ORDER BY ID DESC) AS age FROM userips
)
SELECT * FROM latest_userips WHERE age = 1

tntnet

hustbaer schrieb:

@tntnet:
Dein (für mich sehr schwer zu entzifferndes) Konstrukt entspricht logisch genau der von Duplikateneliminator vorgeschlagenen Lösung mit "where ID in".
Und genau so (die "where ID in" Variante) würde ich es auch selbst schreiben.

Mag sein, dass das schwer zu entziffern ist aber es ist erst mal Standard SQL und außerdem wahrscheinlich eher das, was erwartet wird.

Ich gehe erst mal davon aus, dass die IDs pro user vergeben werden, so dass die IDs doppelt vor kommen können. Der Primärschlüssel wäre (userid, id) Also so was wie:

userid id ip
------ -- -------
hugo   1  1.2.3.4
willi  1  3.4.5.6
hugo   2  5.5.6.3
uwe    1  6.3.5.6

Damit fehlt bei der GROUP-BY-Lösung von Duplikateneliminator im Subselect die Referenz auf die äußere Tabelle. Ich bekomme in diesem Fall also alle Datensätze, da das Subselect (1, 2) liefert und das äussere alle Datensätze, wo diese id gesetzt ist und damit auch den hugo mit der id 1.

Ob das dann noch wirklich leichter zu entziffern ist, wage ich zu bezweifeln.

Das Selfjoin mit HAVING ist in der Regel schneller. Optimierer können besser damit um gehen. Zumindest ist das meine Erfahrung und sicher stark von den jeweiligen Umständen abhängig, also Datenmengen und natürlich die verwendete Datenbank.

tntnet

Da das ein interessantes Problem ist und mich der Ehrgeiz gepackt hat, habe ich das nochmal ausprobiert. Ich habe hier eine Datenbank mit einer Tabelle mit 18239 Sätzen in einer Postgresql-9.3.5-Datenbank unter Fedora 20. Also nicht wirklich groß aber mehr als ein paar Sätze. Der Aufbau der Tabelle ist ein wenig komplexer aber nah genug, dass ich die 2 Lösungen (ich habe die 2. fehlerhafte von Duplikateneliminator weg gelassen) miteinander vergleichen kann.

Der Zugriff mit dem Subquery dauert so im Schnitt 50ms. Mit meiner HAVING-Lösung ca. 39ms. Das bestätigt also meine Vermutung, dass die HAVING-Lösung schneller ist. Ich könnte jetzt natürlich noch die Zugriffspfade (explain) vergleichen, aber das erspare ich mir jetzt. Übrigens habe ich die Zeiten direkt mit psql gemessen. Da kann man das mit "\timing on" einschalten. Ach, ich liebe postgresql .

Also so ein Self-Join ist schon nützlich. Und wenn man das Prinzip des Self-Joins verstanden hat, dann ist das nicht wirklich schwerer zu entziffern als so ein correlated Subquery.

[quote="tntnet"]

hustbaer schrieb:

Ich gehe erst mal davon aus, dass die IDs pro user vergeben werden, so dass die IDs doppelt vor kommen können. Der Primärschlüssel wäre (userid, id)

Deine Annahme ist nicht korrekt -> siehe SQL Fiddle. Wobei da natürlich auch noch genug Interpretationsspielraum bleibt... ID scheint pk zu sein. Deshalb ist die zweite Lösung nicht falsch.

hustbaer

@tntnet
Ich sag' nicht dass ein self-join grundsätzlich schwer zu verstehen ist.
Es ist nur für mich schwer zu lesen wenn man es alles in eine Abfrage schreibt.
Genau so wie ich z.B. auch Abfragen schwer lesen kann die bei Joins kein "ON" verwenden, sondern alles im "WHERE" machen.

Deine Abfrage lässt sich z.B. auch so schreiben, und so finde ich sie viel einfacher zu verstehen:

SELECT a.userID, a.ip
FROM userips a 
     INNER JOIN (SELECT x.userID, max(x.ID) max_ID FROM userips x GROUP BY x.userID) AS b
         ON a.userID = b.userID AND a.ID = b.max_ID

Bzw. mit CTEs auch so

; WITH max_ids AS (
    SELECT userID, max(ID) max_ID FROM userips GROUP BY userID
)
SELECT a.userID, a.ip
FROM userips a
     INNER JOIN max_ids b ON a.userID = b.userID AND a.ID = b.max_ID

tntnet schrieb:

Das Selfjoin mit HAVING ist in der Regel schneller. Optimierer können besser damit um gehen.

Schneller als was?
Schneller als die where not exists von Duplikateneliminator? Ziemlich sicher ja.
Schneller als die where ID in Variante (falls sie verwendbar ist, also falls ID für sich genommen unique ist): ziemlich sicher nein.
Zumindest ist es meine Erfahrung dass WHERE ... IN vom MSSQL Optimizer genau gleich behandelt wird wie ein JOIN. (Mit anderen DBMS fehlt mir die Erfahrung.)

tntnet

hustbaer schrieb:

Schneller als was?
Schneller als die where not exists von Duplikateneliminator? Ziemlich sicher ja.
Schneller als die where ID in Variante (falls sie verwendbar ist, also falls ID für sich genommen unique ist): ziemlich sicher nein.
Zumindest ist es meine Erfahrung dass WHERE ... IN vom MSSQL Optimizer genau gleich behandelt wird wie ein JOIN. (Mit anderen DBMS fehlt mir die Erfahrung.)

Ich habe doch meine Annahme erläutert. Und das WHERE ... IN ist unter diesen Annahmen (die offensichtlich nicht korrekt waren) nicht anwendbar. Also ist mein self-join unter den Annahmen, die ich erläutert habe schneller als die EXISTS -Lösung, wie Du mir ja bestätigst. Kommen ich mit einer nicht correlated Subquery aus, dann ist das natürlich schneller.

Und ja - Deine erste Lösung finde ich auch prima. Danke für den Vorschlag. Deine zweite Lösung ist kein Standard-SQL. Und wenn ich Standard-SQL verwenden kann, dann tue ich das.

hustbaer

OK, war mir nicht sicher welche Variante du als langsamer eingestuft hast.

tntnet schrieb:

Und ja - Deine erste Lösung finde ich auch prima. Danke für den Vorschlag.

Gerne

tntnet schrieb:

Deine zweite Lösung ist kein Standard-SQL. Und wenn ich Standard-SQL verwenden kann, dann tue ich das.

Huch? Wieso nicht? Was genau ist daran non-Standard?

Common table expression sind soweit ich weiss schon Standard SQL.
Siehe
http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression
und
http://en.wikipedia.org/wiki/SQL:1999#Common_table_expressions_and_recursive_queries
(Bloss der olle MySQL kann sie halt (noch?) nicht.)

Und mMn. eines der collsten SQL Features die ich lange lange Zeit übersehen habe.
Abgesehen von rekursiven CTEs kann man damit zwar nicht mehr machen als mit normalen Subqueries, aber man kann es damit viel, viel aufgeräumter/übersichtlicher machen.

Bei diesem einfachen Beispiel zeigt sich das noch nicht so, aber wenn man mehrere, aufeinander aufbauende CTEs hat ... wenn man dann versucht das ganze ohne CTEs hinzuschreiben, sieht man schnell, wie furchtbar das würde

tntnet

Ich sehe schon - bin bei SQL92 stehen geblieben. SQL99 bringt ja schon ein paar interessante Neuerungen mit .