minimaler Abstand zwischen zwei Werten einer Spalte

[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