Doppelte Spalten filtern

[trench140]

[MySQL 4.0]Nur jeweils aktuellsten Timestamp selektieren

Mahlzeit,

ich habe eine Tabelle in der folgenden Form vorliegen:

IP - Richtung - Timestamp - Bytes

Als DB kommt MySQL 4.0.18 zum Einsatz.

Hier werden IPs mitsamt ein- bzw. ausgehendem Traffic sowie einem Timestamp reingepumpt.

Nun möchte ich gerne in einer Abfrage ein- und ausgehenden Traffic abfragen, aber jeweils nur vom AKTUELLSTEN Timestamp. Da verschiedenste IPs eingetragen werden, ist dieser auch nicht immer für alle IPs gleich, sondern kann sich um bis zu 3 Minuten unterscheiden.
Dazu kommt, dass diese IPs natürlich mehrfach auftauchen, da alle paar Minuten von jeder IP der Traffic eingetragen wird.

Was ich also am Ende gerne als Ergebnis haben möchte sieht so aus:

IP1 - In - AktuellsterTimestamp - GroßeZahl
IP1 - Out - AktuellsterTimestamp - GroßeZahl
IP2 - In - AktuellsterTimestamp - GroßeZahl
IP2 - Out - AktuellsterTimestamp - GroßeZahl
.
.
.
IPn - In - AktuellsterTimestamp - GroßeZahl
IPn - Out - AktuellsterTimestamp - GroßeZahl

Krieg ich aber nicht gebacken. Das beste Ergebnis, das ich bisher habe ist, dass ich einfach stur nach "NOW()-x" filtere, da sieht das Ergebnis ber total verkackt aus, etwa so:

IP1 - In - AktuellsterTimestamp - GroßeZahl
IP1 - Out - AktuellsterTimestamp - GroßeZahl
IP1 - Out - Timestamp - GroßeZahl
IP1 - In - Timestamp - GroßeZahl
IP2 - In - AktuellsterTimestamp - GroßeZahl
IP2 - In - Timestamp - GroßeZahl
IP2 - Out - AktuellsterTimestamp - GroßeZahl
.
.
.
IPn - Out - AktuellsterTimestamp - GroßeZahl
IPn - In - AktuellsterTimestamp - GroßeZahl
IPn - Out - Timestamp - GroßeZahl

Sprich, für mich unbrauchbar.

Ich könnte nun einfach mit meiner Abfrage leben, ein großzügiges x nehmen (z.B. minus 5 Minuten) und dann innerhalb meines Programm durchgehen, ob die aktuell bearbeitete IP den aktuellsten Timestamp hat und falls ja alle anderen Einträge mit gleicher IP und älterem Timestamp löschen, aber ich bin der festen Überzeugung, dass das, was ich will auch direkt mit SQL möglich ist.

Ich habe nur leider keine Ahnung wie, da ich bisher mit Datenbanken nicht viel am Hut hatte

Wenn also jemand eine Idee hat, vielen lieben Dank, ich lausche artig

Gruß,
Trench

 

[zeja]

Habe gerade keine Zeit das selber auszuprobieren aber das erste Beispiel in
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html
sieht doch schon ganz gut aus.

Dann brauchst du noch ein Order by IP, Richtung und dann sollte es eigentlich ganz gut aussehen.

 

[planb2000]

Hallo,

evtl. hab ichs nicht ganz verstanden, aber Richtung(IN) und Richtung (Out) können doch nie den gleichen Zeitstempel haben. Ich würde schon ein "Zeitfenster" anschauen z.B.:

SELECT IP
       , Richtung 
       , Max(Timestamp)
       , Bytes
FROM
        DeineTabelle
WHERE 
       Timestamp > NOW - 1/24 -- Schaut die letzte Stunde für IN und OUT an
GROUP BY IP, Richtung, Bytes
ORDER BY IP, Richtung

So solltest Du die aktuellsten Einträge für jede Richtung bekommen.
Wie ist das gemeint mit den Bytes bei OUT ? Evtl. mußt Du noch BytesOut - BytesIn berechnen um das Volumen herauszubekommen.

Tschüß

 

[trench140]

Hi,

tut mir leid, dass ich mich jetzt erst melde.

Also, idealerweise bräuchte ich eine Abfrage, die etwas Folgendes tut:

SELECT ip,clock
FROM traffic_table
AS a
WHERE UNIX_TIMESTAMP(clock)=(SELECT MAX(UNIX_TIMESTAMP(clock)) WHERE ip=a.ip)

Ich weiß nicht, ob ich mir das richtig zusammengefrickelt habe, daher geh ich es nochmal "verbal" durch, falls da oben nen Fehler drin ist:

Ich selektiere alle "ip"- und "clock"-Spalten unter dem Synonym "a". Anschließend gehe ich "a" durch und selektiere daraus all die "ip"- und "clock"-Spalteneinträge, deren "clock"-Eintrag maximal ist. D.h. im Ergebnis kommt keine IP doppelt vor, sondern nur einmal, dabei aber mit der jeweils aktuellsten Zeit.

Das Problem ist nur, dass MySQL ja erst ab Version 4.1 geschachtelte SQL-Abfragen kann, weswegen obige Abfrage fehl schlägt. Daher stellt sich mir die Frage, ob es dafür einen Workaround mit MySQL 4.0 gibt.

Gruß,
Trench

P.S.: Die "direction" habe ich jetzt erstmal aussen vor gelassen um das Beispiel auf das Wichtigste zu reduzieren.

 

[planb2000]

Hi,

also ohne geschachteltes SQL zu arbeiten ist wirklich eine challenge, allerdings brauchst du das hierbei auch nicht , trotzdem, ich würde updaten ;o)

Ich glaube das geht so:

SELECT a.ip
             ,a.max (clock) as MAX_TIMESTAMP
FROM traffic_table AS a
GROUP BY a.ip

Wobei ich mich nicht so gut mit MySQL auskenne und nicht genau weis warum du das hier machst -> MAX(UNIX_TIMESTAMP(clock), ist clock nicht ein normaler timestamp?

Jedenfalls: die GROUP BY clausel und die MAX() funktion sorgen dafür das nur einmal die IP angezeigt wird, also agregiert wird, hoffe es hilft tschö.

 

[trench140]

Hi,

ich habe es nun komplett ander gelöst, da jegliche Versuche spätestens daran scheiterten, dass es zu lange dauerte, die Werte auszulesen.

Ich habe nun einfach eine neue Tabelle angelegt, in die ich die aktuell ausgelesenen, neuen Werte reinschreibe, auslese, und anschließend wieder lösche. Dadurch befinden sich in dieser Tabelle immer nur die aktuellsten Einträge für jede IP und es gibt auch keine Probleme mehr mit der Laufzeit.
In der ursprünglichen Tabelle hätte ich jedes Mal ~2 Millionen Einträge durchgehen müssen. Ich hätte es zwar auch noch zeitlich einschränken können, aber ganz im ernst, in diesem Fall ging es nur darum, dass es überhaupt funktioniert, und da ich eben kein SQL-Guru bin und nicht wirklich weiterkam, hab ich es nun einfach Quick n' Dirty wie nen Anfänger gemacht

Trotzdem danke für eure Hilfe.

Gruß,
Trench