zeitliches Sortierproblem

hightower1981 · 5 Mai 2015

Hallo,

ich habe folgende Tabelle

id, title, Wert, nr, timestamp
18129 Balkontuer 0 20 1430773021
18103 Balkontuer 0 20 1430772841
18077 Balkontuer 100 20 1430772661
18051 Balkontuer 100 20 1430772482
18025 Balkontuer 100 20 1430772301
17999 Balkontuer 100 20 1430772121
17973 Balkontuer 0 20 1430771941
17219 Balkontuer 0 20 1430766722
17193 Balkontuer 100 20 1430766541
17167 Balkontuer 0 20 1430766361

Sobald ich die Group By Funktion nutze erhalte ich die Werte von 17193 und 17167, nur leider benötige ich die werte von 17999 und 18103.

Erbitte Hilfe.

Distrilec · 5 Mai 2015

Mal davon abgesehen das dein Bild Passwort geschützt ist... Ich habe keine Ahnung was du eigentlich willst...

akretschmer · 5 Mai 2015

hightower1981 schrieb:
Sobald ich die Group By Funktion nutze erhalte ich die Werte von 17193 und 17167, nur leider benötige ich die werte von 17999 und 18103.

Kein Problem: 17999, 18103.

Mehr ist leider bei _DER_ Problembeschreibung nicht machbar.

hightower1981 · 6 Mai 2015

Ich versuche es nochmal zu umschreiben.
Folgende Werte lasse ich mir in eine Datenbank schreiben - MySQL bzw. kann das Auslesen testen mit PHPadmin.

id, title, Wert, nr, timestamp
18129, Balkontuer, 0, 20, 1430773021
18103, Balkontuer, 0, 20, 1430772841
18077, Balkontuer, 100, 20, 1430772661
18051, Balkontuer, 100, 20, 1430772482
18025, Balkontuer, 100, 20, 1430772301
17999, Balkontuer, 100, 20, 1430772121
17973, Balkontuer, 0, 20, 1430771941
17219, Balkontuer, 0, 20, 1430766722
17193, Balkontuer, 100, 20, 1430766541
17167, Balkontuer, 0, 20, 1430766361

Es dreht sich hier um einen Auszug von einer Hausautomatisierung, welche aufzeichnet.
- ID ist forlaufende in den Ereignissen
- Title = Beschreibung meines Sensors
- Wert = aktueller Zustand
- Nr = Sensor Nr.
- Timestamp, Zeit des Zustandes

Da das ganze auf ein Diagramm übertragen werden soll und ich die letzte L/H bzw. L/H flanke angeben möchte, so benötige ich darauf das Ereignis 18103 (Ende des Zustandes) und 17999 (Beginn des Zustandes). Wenn ich meine Werte mit Group By ordne oder Order, komme ich immer auf die Zeitlich vorherigen. Möchte aber gern das Zeitlich letzte (also das kürzer zurückliegende und nicht das das davor statt fand)

Ich hoffe ein wenig Licht in mein Vorhaben gebracht zu haben.

akretschmer · 6 Mai 2015

hightower1981 schrieb:
Da das ganze auf ein Diagramm übertragen werden soll und ich die letzte L/H bzw. L/H flanke angeben möchte, so benötige ich darauf das Ereignis 18103 (Ende des Zustandes) und 17999 (Beginn des Zustandes).

Dafür gibt es einen einfachen Weg in SQL: Du vergleichst den aktuellen Zustand mit dem Zustand im Datensatz vorher. Dazu gibt es die lag() - Funktion. Demo:

Du hast:

Code:

test=*# select * from sensor;
 id | status
----+--------
  1 |  0
  2 |  0
  3 |  0
  4 |  1
  5 |  1
  6 |  0
  7 |  0
  8 |  0
  9 |  1
 10 |  1
 11 |  0
 12 |  0
(12 rows)

Wo sind nun Flanken?

Code:

test=*# select *, case when status != lag(status) over (order by id) then 1 else 0 end as flanke from sensor;
 id | status | flanke
----+--------+--------
  1 |  0 |  0
  2 |  0 |  0
  3 |  0 |  0
  4 |  1 |  1
  5 |  1 |  0
  6 |  0 |  1
  7 |  0 |  0
  8 |  0 |  0
  9 |  1 |  1
 10 |  1 |  0
 11 |  0 |  1
 12 |  0 |  0
(12 rows)

Für Dich die schlechte Nachricht: MySQL gehört zu den wenigen DB-Systemen, die das nicht können. Vielleicht findest Du einen alternativen Weg, in MySQL lag() zu emulieren - oder nimmst gleich eine moderne DB.

Distrilec · 6 Mai 2015

Kann MySQL sowas?

Code:

With dat As
(
Select 1 As id, 0 As status From dual Union All
Select 2, 0 From dual Union All
Select 3, 0 From dual Union All
Select 4, 1 From dual Union All
Select 5, 1 From dual Union All
Select 6, 0 From dual Union All
Select 7, 0 From dual Union All
Select 8, 0 From dual Union All
Select 9, 1 From dual Union All
Select 10, 1 From dual Union All
Select 11, 0 From dual Union All
Select 12, 0 From dual
)
Select z.id
      ,z.status
      ,y.status As prior_status
      ,Case
          When z.status <> y.status Then
           1
          Else
           0
       End As flank
From   (Select t.id
              ,Max(t.status) As status
              ,Max(p.id) As prior_id
       
        From   dat t
       
        Left   Join dat p
        On     p.id < t.id
       
        Group  By t.id) z

Left   Join dat y
On     y.id = z.prior_id

Order  By z.id Asc

BerndB · 6 Mai 2015

Hi,

hab noch noicht ganz verstanden was du möchtest. Hier aber mal ein anderer Ansatz:

Die TAbelle sieht so aus:

Code:

mysql> select * from dat;
+----+------+
| id | stat |
+----+------+
|  1 |    0 |
|  2 |    0 |
|  3 |    0 |
|  4 |    1 |
|  5 |    1 |
|  6 |    0 |
|  7 |    0 |
|  8 |    0 |
|  9 |    0 |
| 10 |    1 |
| 11 |    0 |
| 12 |    0 |
| 13 |    0 |
| 14 |    1 |
| 15 |    1 |
| 16 |    1 |
| 17 |    0 |
+----+------+
17 rows in set (0,00 sec)

In diesem Beispiel bekommst du alle EDGEs

Code:

mysql> SELECT d.*,
    ->   IF (stat <> @last ,1,0) edge,
    ->   IF (stat =1 AND @last=0 ,1,0) edge_L_TO_H,
    ->   IF (stat =0 AND @last=1 ,1,0) edge_H_TO_L,
    ->   @last:=stat tmp_last
    -> FROM dat d, (SELECT @last:=-1) tmp
    -> ORDER BY id DESC;
+----+------+------+-------------+-------------+----------+
| id | stat | edge | edge_L_TO_H | edge_H_TO_L | tmp_last |
+----+------+------+-------------+-------------+----------+
| 17 |    0 |    1 |           0 |           0 |        0 |
| 16 |    1 |    1 |           1 |           0 |        1 |
| 15 |    1 |    0 |           0 |           0 |        1 |
| 14 |    1 |    0 |           0 |           0 |        1 |
| 13 |    0 |    1 |           0 |           1 |        0 |
| 12 |    0 |    0 |           0 |           0 |        0 |
| 11 |    0 |    0 |           0 |           0 |        0 |
| 10 |    1 |    1 |           1 |           0 |        1 |
|  9 |    0 |    1 |           0 |           1 |        0 |
|  8 |    0 |    0 |           0 |           0 |        0 |
|  7 |    0 |    0 |           0 |           0 |        0 |
|  6 |    0 |    0 |           0 |           0 |        0 |
|  5 |    1 |    1 |           1 |           0 |        1 |
|  4 |    1 |    0 |           0 |           0 |        1 |
|  3 |    0 |    1 |           0 |           1 |        0 |
|  2 |    0 |    0 |           0 |           0 |        0 |
|  1 |    0 |    0 |           0 |           0 |        0 |
+----+------+------+-------------+-------------+----------+
17 rows in set (0,00 sec)

und so gefiltert - nur noch EDGEs.

Code:

mysql> SELECT d.*,
    ->   IF (stat <> @last ,1,0) edge,
    ->   IF (stat =1 AND @last=0 ,1,0) edge_L_TO_H,
    ->   IF (stat =0 AND @last=1 ,1,0) edge_H_TO_L,
    ->   @last:=stat tmp_last
    -> FROM dat d, (SELECT @last:=-1) tmp
    -> HAVING edge <> 0
    -> ORDER BY id DESC;
+----+------+------+-------------+-------------+----------+
| id | stat | edge | edge_L_TO_H | edge_H_TO_L | tmp_last |
+----+------+------+-------------+-------------+----------+
| 17 |    0 |    1 |           0 |           0 |        0 |
| 16 |    1 |    1 |           1 |           0 |        1 |
| 13 |    0 |    1 |           0 |           1 |        0 |
| 10 |    1 |    1 |           1 |           0 |        1 |
|  9 |    0 |    1 |           0 |           1 |        0 |
|  5 |    1 |    1 |           1 |           0 |        1 |
|  3 |    0 |    1 |           0 |           1 |        0 |
+----+------+------+-------------+-------------+----------+
7 rows in set (0,00 sec)

Poste mal dein Query.

Gruss

Bernd

hightower1981 · 7 Mai 2015

Hallo,

ich habe mal deinen Code probiert: (meine Tabelle heißt stats und ich habe derzeit nur Werte von 0 und 100 drin. -siehe oben-)

SELECT s.*,
IF (stat <> @last ,1,0) edge,
IF (stat =1 AND @last=0 ,1,0) edge_L_TO_H,
IF (stat =0 AND @last=1 ,1,0) edge_H_TO_L,
@last:=stat tmp_last
FROM stats s, (SELECT @last:=-1) tmp
HAVING edge <> 0
ORDER BY id DESC

nur leider erhalte ich da die Aussage:
MySQL meldet:

#1054 - Unknown column 'stat' in 'field list'

Ich teste direkt über phpAdmin um erst das Resultat zu sehen.

PS: Dein Ansatz ist gut, diesen würde ich so benötigen bzw. das könnte zu meiner gedachten Lösung kommen.

BerndB · 7 Mai 2015

In deiner Tabelle heisst das feld wohl "Wert". Also überall wo stat steht in Wert ändern.

Dein Image ist nicht zu sehen, da eine Authentifizierung erforderlich ist.

zeitliches Sortierproblem

hightower1981

Benutzer

Distrilec

Datenbank-Guru

akretschmer

Datenbank-Guru

hightower1981

Benutzer

akretschmer

Datenbank-Guru

Distrilec

Datenbank-Guru

BerndB

Datenbank-Guru

hightower1981

Benutzer

BerndB

Datenbank-Guru