minimaler Abstand zwischen zwei Werten einer Spalte

[Micha]

Hallo,

ich suche den kleinsten Wert zwischen zwei Werten einer Spalte mit Zeitstempel einer MySQL Tabelle. Das einzige was mir hierzu eingefallen ist, ist eine Schleife zu nutzen und die Zeitdifferenzen zu ermitteln.

Kann ich das auch direkt durch eine Abfrage lösen?

Mit freundlichem Gruß
Micha

[Frank (no reg)]

Hallo,

Kann ich das auch direkt durch eine Abfrage lösen?

Kommt zwar auch auf die Version deines MySql an, aber grundsätzlich: Ja. Und zwar mit einem SELF-JOIN.

Ciao, Frank

[Micha]

Hallo Frank (no reg),

Kommt zwar auch auf die Version deines MySql an, aber grundsätzlich: Ja.

Ist eine 5er Version; muss es genauer sein?

Und zwar mit einem SELF-JOIN.

Kannst Du das etwas ausführen? Mein SQL-Wissen ist gering.

Meine Tabelle sieht zB so aus:

  
epoche 	datum  
1	2009-05-14 10:52:55  
2	2009-05-14 11:05:55  
3	2009-05-14 11:19:26  
4	2009-05-14 11:34:24  
5	2009-05-14 11:52:50  
6	2009-05-14 12:07:49  
7	2009-05-14 12:22:52  
8	2009-05-14 12:35:13  
9	2009-05-14 12:52:54  
10	2009-05-14 13:04:26

Ich will nun wissen, wie groß der kleinste Zeitunterschied ist.

Mit freundlichem Gruß
Micha

[Vinzenz Mai]

Hallo Micha,

Und zwar mit einem SELF-JOIN.
Kannst Du das etwas ausführen? Mein SQL-Wissen ist gering.

hab' ich extra für Dich geschrieben ;-)

Freundliche Grüße

Vinzenz

[Vinzenz Mai]

Hallo Ingrid,

Und zwar mit einem SELF-JOIN.
Kannst Du das etwas ausführen? Mein SQL-Wissen ist gering.

hab' ich extra für Dich geschrieben ;-)

als Verknüpfungskriterium könntest Du zum Beispiel wählen,
dass die ID der "ersten" Tabelle größer sein soll als die der "zweiten" Tabelle, damit Du die Kombination eines Datensatzes mit sich selbst ausschliesst.

Um nun den gewünschten Datensatz zu bekommen:
noch eine berechnete Spalte für die Differenz hinzufügen, nach dieser aufsteigend sortieren und die Ergebnismenge auf einen Datensatz limitieren.

Dabei kannst Du Dir das Problem einhandeln, dass es mehrere Kombinationen geben könnte, die die gleiche minimale Differenz aufweisen. Wenn Du in diesem Fall alle Kombinationen benötigst, so ermittle diese Datensätze mit einem Subselect (ab MySQL 4.1, geht also bei Dir).

Ach ja: Beachte bitte den Abschnitt "Beachten Sie:" in meinem Artikel :-)

Freundliche Grüße

Vinzenz

[Micha]

Hallo Vinzenz Mai,

hab' ich extra für Dich geschrieben ;-)

Nein, der hat wohl eine andere Zielgruppe ;-) "Fortgeschritten" heißt hier, ich gehe über zu meinem ersten Plan - die Schleife. Mein Versuch

  
SELECT * FROM `tabelle` AS TB1 INNER JOIN `tabelle` AS TB2  
ON TB1.`id` != TB2.`id` ORDER BY (TB1.`datum` - TB2.`datum`)  

scheint nicht zu fruchten und ist sicher auch nicht ganz das, was Du beschrieben hast, oder?

Mit freundlichem Gruß
Micha

[Tom]

Hello,

SELECT * FROM tabelle AS TB1 INNER JOIN tabelle AS TB2
ON TB1.id != TB2.id ORDER BY (TB1.datum - TB2.datum)

>   
> scheint nicht zu fruchten und ist sicher auch nicht ganz das, was Du beschrieben hast, oder?  
  
Wie wäre es denn zur Abwechslung mal mit dem Create-Statement der Tabelle?  
Dann könnten wir sehen, welche Spaltentypen Du da vermurkst in `datum` :-)  
  
  
  
  
  
Liebe Grüße aus dem schönen Oberharz  
  
  
Tom vom Berg  
![](http://selfhtml.bitworks.de/Virencheck.gif)  
  

-- 
Nur selber lernen macht schlau  
<http://bergpost.annerschbarrich.de>  
  

[Micha]

Hallo Tom,

probiert hatte ich auch
WHERE MIN(TB1.datum - TB2.datum)
aber ... naja, erfolglos ;-)

Dann könnten wir sehen, welche Spaltentypen Du da vermurkst in datum :-)

Ich habe dort ein timestamp genommen. Das sieht so aus:

CREATE TABLE `tabelle` (  
  `id` bigint(255) unsigned NOT NULL auto_increment,  
  `datum` timestamp NOT NULL default CURRENT_TIMESTAMP,  
  PRIMARY KEY  (`epoche`),  
  UNIQUE KEY `datum` (`datum`)  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED AUTO_INCREMENT=1;

Es muss ja kein Datum sein, sondern kann auch eine Messreihe anderer Art sein. Ich möchte Daten glätten und will dabei gleichzeitig eine äquidistante Reihe erzeugen. Damit ich also in jedem Fall min. einen Messwert habe, benötige ich das kleinste Zeitfenster zwischen zwei Messungen. Es wären aber auch andere Daten denkbar:

Messreihe
2 4 6 8 9 11 13 15
Ich suche hier also (9-8)=1.

Liebe Grüße aus dem schönen Oberharz

Schöne Grüße aus Unterentfelden

Micha

[dedlfix]

Hi!

SELECT * FROM tabelle AS TB1 INNER JOIN tabelle AS TB2

ON TB1.id != TB2.id ORDER BY (TB1.datum - TB2.datum)

>   
> scheint nicht zu fruchten und ist sicher auch nicht ganz das, was Du beschrieben hast, oder?  
  
Das ist schon fast das was du willst. Aber der INNER JOIN verbindet jeden Datensatz mit jedem und du schließt nur die selben aus.  
  
  abc  
-+---  
a|-++  
b|+-+  
c|++-  
  
Du hast immer noch doppelte Kombinationen, wie ab und ba, ac und ca und so weiter. Du könntest als Bedingung TB1.id < TB2.id nehmen, dann hast du alle Kombinationen nur einmal.  
  
  abc  
-+---  
a|-++  
b|--+  
c|---  
  
Als nächstes bekommst du bei der Timestamp-Differenz negative Werte. Sortiert ergibt das als ersten den Datensatz mit der größten negativen Differenz. Der Absolutwert sollte dir helfen.  
  
  
Lo!

[Tom]

Hello moin,

das Create-Statement war so natürlich falsch  :-(

aber alles richtig, was Du da schreibst, dedlfix :-)

  
  
    SELECT * ,abs(TB1.`datum` - TB2.`datum`) as d  
    FROM `tabelle` AS TB1  
    INNER JOIN `tabelle` AS TB2  
    ON TB1.`id` < TB2.`id`  
    ORDER BY d;  

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Micha]

Hallo Tom und dedlfix,

vielen Dank für die Korrektur meines Codes - war ich ja wirklich gar nicht so weit weg. Leider dauert die Abfrage ewig. Vinzenz hatte da ja indirekt schon drauf hingewiesen.

Mit freundlichem Gruß
Micha

[dedlfix]

Hi!

vielen Dank für die Korrektur meines Codes - war ich ja wirklich gar nicht so weit weg. Leider dauert die Abfrage ewig. Vinzenz hatte da ja indirekt schon drauf hingewiesen.

Wie ich grad sehe, hatte er schon größtenteils das geantwortet, was ich auch sagte, nur du hattest es nicht richtig umgesetzt. Was die Geschwindigkeit anbelangt wirst du nicht viel ausrichten können, denn die Differenz muss erst für alle Kombinationen berechnet werden, bevor die kleinste Differenz gesucht werden kann. Die Datenmenge einzuschränken wäre laufzeitverringernd, aber solange es keine anderen Ausschlusskriterien als die Zeitdifferenz gibt, hast du diesbezüglich schlechte Karten. Wenn sich die Datenmenge selten ändert, könntest du die Differenzen in einer zweiten Tabelle vorberechnen (Spalten: ID1, ID2, Differenz). Das ergibt … ähm, eine Menge Datensätze. Aber du kannst dann einen Index auf die Differenz legen. Nachteilig ist, dass du für jeden hinzukommenden Datensatz die Differenzen ausrechnen müsstest (da kann ein Trigger helfen).

Lo!

[Micha]

Hallo dedlfix,

Wie ich grad sehe, hatte er schon größtenteils das geantwortet, was ich auch sagte, nur du hattest es nicht richtig umgesetzt.

Ja, ich weiß. ;)

Was die Geschwindigkeit anbelangt wirst du nicht viel ausrichten können, denn die Differenz muss erst für alle Kombinationen berechnet werden, bevor die kleinste Differenz gesucht werden kann.

Das müsste er theoretisch nicht. Es reicht ja, wenn er sich immer seinen Vorgänger ansieht. In einer Schleife würde ich es ja auch so machen (Pseudocode):

  
dT = 1E16;  
for (i=1; i<array.length; i++) {  
   dT = min(dT, array[i] - array[i-1]);  
}

Voraussetzung ist, dass das array bereits sortiert ist nach den eingetragenen Zeiten. Eine Kombination mit allen anderen Zeitstempeln kann ich ja bereits im Vorfeld ausschließen, da bspw. der erste und der letzte Zeitstempel sowieso nicht in Frage kommen.

aber solange es keine anderen Ausschlusskriterien als die Zeitdifferenz gibt, hast du diesbezüglich schlechte Karten.

Habe ich ja, wie Du siehst. Nur, wie bringe ich das SQL bei?

Mit freundlichem Gruß
Micha

[dedlfix]

Hi!

Was die Geschwindigkeit anbelangt wirst du nicht viel ausrichten können, denn die Differenz muss erst für alle Kombinationen berechnet werden, bevor die kleinste Differenz gesucht werden kann.
Das müsste er theoretisch nicht. Es reicht ja, wenn er sich immer seinen Vorgänger ansieht.

Welchen Vorgänger? Die Datensätze sind per Definition eine unsortierte Menge. Wenn es ein eindeutiges Vorgängerkennzeichen gäbe, wäre das deine Verknüpfungsbedingung. Dann ergibt sich nicht jeder mit jedem (abzüglich Dopplungen und Selbstbezug) sondern nur noch x mit x - 1 (aber: x > min(x)). Wenn die ID lückenlos ist und bleibt ... aber bei "ID-Missbrauch" stellt sich nicht nur bei mir ein ungutes Bauchgefühl ein, auch wenn es auf den ersten Blick passabel erscheint. Wie auch immer, selbst wenn du auf dieses Pferd setzen wolltest, denke ich nicht, dass sich der Zeitverbrauch verringern wird, denn es muss immer noch jeder mit jedem verglichen werden, ob es der Vorgänger ist.

In einer Schleife würde ich es ja auch so machen (Pseudocode):

Dann mach diese Schleife, allerdings empfehle ich, es als Stored Procedure zu implementieren. Ansonsten müsstest du alle Datensätzt abholen und dann "schleifen". In der SP kannst du einen Cursor definieren (ORDER BY nicht vergessen), den du durchläufst und dabei Vergleiche mit dem Vorgänger anstellst. Wie sich das zeitlich verhält, müsstest du aber mal messen - vielleicht braucht die SP ja noch länger.

Lo!

[Tom]

Hello,

Welchen Vorgänger? Die Datensätze sind per Definition eine unsortierte Menge.

Das ist Quatsch. Er hat ein Ordnungskriterium, in diesem Fall den Zeitstempel. In der Selection haben die Datensätze also einen Vorgänger und einen Nachfolger, bis auf den ersten und den letzen.

Und mit Hilfe von Systemvariablen kann er auch einen Satzvergleich durchführen.
Ich hab jetzt leider nicht genug Zeit, das selber auszuprobieren, aber Nachbarn finden geht damit auf jeden Fall.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

Welchen Vorgänger? Die Datensätze sind per Definition eine unsortierte Menge.
Das ist Quatsch. Er hat ein Ordnungskriterium, in diesem Fall den Zeitstempel. In der Selection haben die Datensätze also einen Vorgänger und einen Nachfolger, bis auf den ersten und den letzen.

Ja, nach dem Sortieren kann man einen Vorgänger ermitteln. Allerdings bringt das nicht viel, weil man ihn nicht gezielt ansprechen kann - er lässt sich nicht auf einfache Weise berechnen. Man muss alle kleineren Werte nehmen, die sortieren und dann auf einen limitieren. Das für alle Datensätze zu machen bleibt aufwendig.

Und mit Hilfe von Systemvariablen kann er auch einen Satzvergleich durchführen.

Welche Systemvariablen meinst du?

Lo!

[Micha]

Hallo Ihr zwei,

noch mal zur Erinnerung, meine SQL-Kenntnisse sind begrenzt. Die Lösung mit der Schleife, wie beschrieben, war mein erster Gedanke. Dann dachte ich aber, gut, wenn ich schon mal eine DB habe, dann kann man diese vll auch (aus)nutzen.

Die IDs wollte ich nicht missbrauchen sondern, wie Tom schon schrieb, den Zeitstempel selbst als Kriterium einführen und nach diesem Sortieren. Ich muss dann eben nicht mehr jeden mit jeden sondern jeweils nur zwei Werte in Folge testen. Die Diskussion, auch wenn ich wenig aktiv dazu beitragen kann :-/ , zeigt mir aber irgendwie, dass ich mit meiner Schleife scheinbar schon eine schöne Lösung habe, da ich hier direkt den Vorgänger ermitteln kann.

Und mit Hilfe von Systemvariablen kann er auch einen Satzvergleich durchführen.

Welche Systemvariablen meinst du?

Wenn Ihr noch was habt, ich bin ganz Ohr ;-)

Mit freundlichem Gruß
Micha

[Tom]

Hello,

Vielleicht ein bisschen umständlich, aber so geht es ohne kartesisches Produkt.
Das dürfte auf jeden Fall schneller sein.

mysql> set @z1:=0, @diff:=9999999999, @id1:=0;
Query OK, 0 rows affected (0.02 sec)

mysql> select id,
    ->     zeitstempel,
    ->     (zeitstempel-@z1) as diff,
    ->     if(zeitstempel-@z1 < @diff, @id1:=id, @id) as id1,
    ->     if(zeitstempel-@z1 < @diff, @diff:= zeitstempel-@z1, @diff) as mindiff,
    ->     @z1:=zeitstempel as z1
    -> from zeit order by zeitstempel;
+----+-------------+---------+------+---------+---------+
| id | zeitstempel | diff    | id1  | mindiff | z1      |
+----+-------------+---------+------+---------+---------+
| 21 |         100 |     100 |   21 |     100 |     100 |
| 22 |         200 |     100 | NULL |     100 |     200 |
| 23 |         400 |     200 | NULL |     100 |     400 |
| 17 |        1000 |     600 | NULL |     100 |    1000 |
| 18 |       11000 |   10000 | NULL |     100 |   11000 |
| 19 |      111000 |  100000 | NULL |     100 |  111000 |
| 20 |     1511000 | 1400000 | NULL |     100 | 1511000 |
+----+-------------+---------+------+---------+---------+
7 rows in set (0.00 sec)

mysql> select @id1;
+------+
| @id1 |
+------+
|   21 |
+------+
1 row in set (0.00 sec)

Gegenprobe:

mysql> update zeit set zeitstempel=420 where id=17;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> set @z1:=0, @diff:=9999999999, @id1:=0;
Query OK, 0 rows affected (0.02 sec)

mysql> select id,
    ->     zeitstempel,
    ->     (zeitstempel-@z1) as diff,
    ->     if(zeitstempel-@z1 < @diff, @id1:=id, @id) as id1,
    ->     if(zeitstempel-@z1 < @diff, @diff:= zeitstempel-@z1, @diff) as mindiff,
    ->     @z1:=zeitstempel as z1
    -> from zeit order by zeitstempel;
+----+-------------+---------+------+---------+---------+
| id | zeitstempel | diff    | id1  | mindiff | z1      |
+----+-------------+---------+------+---------+---------+
| 21 |         100 |     100 |   21 |     100 |     100 |
| 22 |         200 |     100 | NULL |     100 |     200 |
| 23 |         400 |     200 | NULL |     100 |     400 |
| 17 |         420 |      20 |   17 |      20 |     420 |
| 18 |       11000 |   10580 | NULL |      20 |   11000 |
| 19 |      111000 |  100000 | NULL |      20 |  111000 |
| 20 |     1511000 | 1400000 | NULL |      20 | 1511000 |
+----+-------------+---------+------+---------+---------+
7 rows in set (0.00 sec)

mysql> select @id1;
+------+
| @id1 |
+------+
|   17 |
+------+
1 row in set (0.00 sec)

@id1 enthält dann die ID des Datensatzes, der zu seinem Vorgänger (order by zeitstempel) die geringste Differenz hat.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[dedlfix]

Hi!

noch mal zur Erinnerung, meine SQL-Kenntnisse sind begrenzt.

Das heißt ja nicht, dass sie nicht weiter ausbaufähig sind, oder?

Die Lösung mit der Schleife, wie beschrieben, war mein erster Gedanke. Dann dachte ich aber, gut, wenn ich schon mal eine DB habe, dann kann man diese vll auch (aus)nutzen.

Ich denke, Toms Idee mit der Uservariable ist die einfachste und vermutlich auch nicht die uneffektivste.

Lo!

[Micha]

Hallo,

Das heißt ja nicht, dass sie nicht weiter ausbaufähig sind, oder?

Nein, das heisst es nicht aber wenn es schlagartig ansteigt, so wie im Moment, ist man eher erschlagen ;-)

Ich werd das ganze mal austesten. Danke Euch beiden!

Mit freundlichem Gruß
Micha

[Tom]

Hello,

Ich werd das ganze mal austesten. Danke Euch beiden!

Das ist ausgetestet!

Ist nur noch ein Schreibfehler im Statement gewesen.
Das sollte immer @id1 heißen und nicht einmal @id.

set @z1:=0, @diff:=9999999999, @id1:=0;

select id,
      zeitstempel,
      (zeitstempel-@z1) as diff,
      if(zeitstempel-@z1 < @diff, @id1:=id, @id1) as id1,
      if(zeitstempel-@z1 < @diff, @diff:= zeitstempel-@z1, @diff) as mindiff,
      @z1:=zeitstempel as z1
  from zeit order by zeitstempel limit 1;

So würdest Du nun mit zwei Statements auskommen.
In der berechnten Spalte id1 würde dann zum Schluss die ermittelte ID für den Treffer stehen.
Der Ergebnisdatensatz als solches hat keine Relevanz, nur die Spalten id1 und mindiff.
mindiff gibt dann die ermittelte kleinste Differenz an.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Tom]

Hello,

Welche Systemvariablen meinst du?

Tschuldigung, ich meinte User-Variablen. :-)

CREATE TABLE zeit (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  zeitstempel bigint(20) DEFAULT NULL,
  bemerkung varchar(20) DEFAULT NULL,
   PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 |

set @z1:=0;

select zeitstempel, if(zeitstempel-@z1 > @z1, @z1:=zeitstempel-@z1,@z1) as diff from zeit;
+-------------+---------+
| zeitstempel | diff    |
+-------------+---------+
|        1000 |    1000 |
|       11000 |   10000 |
|      111000 |  101000 |
|     1511000 | 1410000 |
|         100 | 1410000 |
|         200 | 1410000 |
|         400 | 1410000 |
+-------------+---------+
7 rows in set (0.01 sec)

mysql> select zeitstempel, (zeitstempel-@z1) as diff, @z1:=zeitstempel as z1 from zeit order by zeitstempel;
+-------------+----------+---------+
| zeitstempel | diff     | z1      |
+-------------+----------+---------+
|         100 | -1510900 |     100 |
|         200 |      100 |     200 |
|         400 |      200 |     400 |
|        1000 |      600 |    1000 |
|       11000 |    10000 |   11000 |
|      111000 |   100000 |  111000 |
|     1511000 |  1400000 | 1511000 |
+-------------+----------+---------+
7 rows in set (0.00 sec)

eventuell in Temp-Table schreiben und dann das Minimum abfragen

Ginge das auch als Subselect?

Die Sortierung muss ja über `zeitstempel´ laufen, weil sonst nicht die Differenz zwischen zwei Folgeelementen gebildet würde...

Das Sortieren über diff ist im ersten Schritt also nicht sinnvoll.

Die ID sollte man natürlich auch mit abfragen ;-)
Hab ich aber hier nicht gemacht.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Tom]

Hello,

vielen Dank für die Korrektur meines Codes - war ich ja wirklich gar nicht so weit weg. Leider dauert die Abfrage ewig. Vinzenz hatte da ja indirekt schon drauf hingewiesen.

Du kannst eventuell etwas über die Konfiguration des MySQL-Servers bewegen, wenn Du die ändern darfst. Die Speicherausnutzung kann man ändern.

Es gibt da Beispiele
my-small.cnf
my-medium-cnf
my-large.cnf
my-huge.cnf

Vielleicht hilft Dir diese Seite weiter:
http://dev.mysql.com/doc/refman/5.1/en/option-files.html

Die Frage kam schon öfter in diesem Forum und nachdem die Leute dann entsprechend die Speichernutzung geändert hatten, waren sie meistens zufrieden...

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Micha]

Hallo Tom,

Du kannst eventuell etwas über die Konfiguration des MySQL-Servers bewegen, wenn Du die ändern darfst.

Ja, könnte ich. Es handelt sich um eine lokale WAMPP Installation, die nicht über eine Webapplikation sondern mit einem JAVA-Programm arbeitet.

Es gibt da Beispiele
my-small.cnf
my-medium-cnf
my-large.cnf
my-huge.cnf

Hmm, ich habe nur die \mysql\bin\my.cnf gefunden. Fehlen diese Dateien bei der XAMPP Umgebung?!

Mit freundlichem Gruß
Micha

[Tom]

Hello,

Du kannst eventuell etwas über die Konfiguration des MySQL-Servers bewegen, wenn Du die ändern darfst.
Ja, könnte ich. Es handelt sich um eine lokale WAMPP Installation, die nicht über eine Webapplikation sondern mit einem JAVA-Programm arbeitet.

Es gibt da Beispiele
my-small.cnf
my-medium-cnf
my-large.cnf
my-huge.cnf
Hmm, ich habe nur die \mysql\bin\my.cnf gefunden. Fehlen diese Dateien bei der XAMPP Umgebung?!

Das kann sein, dass bei Wampp die Musterdateien nicht dabei sind. Im Web springen sie dich allerdings gleich an, wenn Du Metager oder so benutzt.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Tom]

Hello,

hab' ich extra für Dich geschrieben ;-)

Darf ich Dich in Zukunft Nostradamus nennen?

Jedes Forum braucht schließlich einen eigenen Hellseher :~)

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg