Nächsten Zeitstempel finden, Abfrage dauert ewig.

Payoff · 7 April 2017

Hallo,
ich hab hier bisher schon als Gast einiges verfolgt, habe jetzt aber selber mal eine Frage.
Und zwar habe ich die zwei folgenden Tabellen CAN und GPS, welche über eine Tabelle log mittels der logid eine Beziehung eingehen. CAN <--> LOG <--> GPS

CAN:

Code:

id    canid   logid         data     timestamp
1  0x90FE6FFE   1    000000C533BB87FF   600
2  0x8CF004FE   1    007D7D9C6300F07D   610
3  0x90FE6FFE   1    000000C5E6C683FF  1640
4  0x8CF004FE   1    007D7DF03A00F07D  1500
5  0x90FE6FFE   1    000000C59AD283FF  1600

GPS:

Code:

id    lat      lng           logid                    timestamp
1  52.220961    9.416273        1                          550
2  52.220960    9.416273        1                          1550
3  52.220960    9.416273        1                          2551
4  52.220960    9.416273        1                          3546
5  52.220959    9.416274        1                          4548

Wie zusehen ist hat jeder GPS- und jeder CAN-Eintrag einen eigenen Zeitstempel.
In einer Abfrage möchte ich nun folgendes Ergebnis bekommen:

Code:

can_ID             lat            lng       gps.timestamp  diff_to_gps_TS  gps_ID
NULL           +52.220961     +9.416273       550             NULL           1
 1                NULL            NULL        NULL              50         NULL
 2                NULL            NULL        NULL              60         NULL
NULL           +52.220960      +9.416273      1550            NULL           2
 3                NULL            NULL        NULL              90         NULL
 4                NULL            NULL        NULL              50         NULL

Das heißt alle CAN-Daten sollen dem nächsten GPS-Signal welches zugeordnet werden.
Momentan arbeite ich an dem folgendem Query das zu erreichen.

Code:

SELECT c.id can_ID, g.lat, g.lng , g.timestamp gps_timestamp
FROM can c
JOIN gps g ON c.logid = g.logid
WHERE g.timeStamp = (SELECT gg.timestamp
                     FROM gps gg
                     ORDER BY abs(gg.timeStamp - c.timeStamp)
                     LIMIT 1
                    )
                    ORDER BY c.id LIMIT 1000

Dieser ist allerdings unglaublich langsam, was vermutlich an der Datenmenge der CAN-Daten liegt (~40000), daher hab ich den Query erstmal auf 1000 Elemente beschränkt, was jedoch auch schon ~15s Rechenzeit beansprucht.
Das nächste Problem an diesem Query ist allerdings, das scheinbar nicht alle Daten berücksichtigt werden, weshalb ich aus dem Join ein FULL OUTER JOIN machen wollte. Dies bricht allerdings immer mit dem folgenden Fehler ab:

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'FULL OUTER JOIN gps g ON c.logid = g.logid
WHERE g.timeStamp = (SELECT gg.times' in Zeile 3

Wäre über jede Hilfe dankbar!

akretschmer · 8 April 2017

ich versteh den Zusammenhang zwischen can und gps nicht, liegt vielleicht an der unbekannten Tabelle log. Davon abgesehen suchst Du offenbar Window-Funktionen, die MySQL natürlich nicht kann.

Payoff · 9 April 2017

Hallo,
die Tabelle log benötige ich für die Abfrage nicht. Trotzdem hier zur Info:
id | logdate | logtime | vehicleID
1 2017-03-22 10:56:54 1
2 2017-03-22 06:57:22 1

Ich bin mittlerweile soweit:

Code:

SELECT can.id can_ID, gps.id gps_ID, can.logid logid, can.canid canid, can.pgn pgn, can.data data, gps.lat lat, gps.lng lng, gps.speed speed, can.timestamp can_TS, gps.timestamp gps_TS
from can
JOIN gps on gps.logid=can.logid
WHERE can.pgn IN ('0xF004', '0xFEE6', '0xFE6F') AND gps.logid IN (1,2,3,4,5,6,7) AND abs(gps.timeStamp - can.timeStamp)
GROUP BY  can.id
ORDER BY `can_ID` ASC

can_ID gps_ID logid canid pgn data lat lng speed can_Timestamp gps_Timestamp
1 1 1 0x90FE6FFE 0xFE6F 000000C533BB87FF 52.221002 9.416257 0 599 1823
2 1 1 0x8CF004FE 0xF004 007D7D9C6300F07D 52.221002 9.416257 0 704 1823
3 1 1 0x90FE6FFE 0xFE6F 000000C5E6C683FF 52.221002 9.416257 0 1004 1823
4 1 1 0x8CF004FE 0xF004 007D7DF03A00F07D 52.221002 9.416257 0 2300 1823
....

Also sieht das auf den ersten Blick schonmal genau so aus, wie ich es haben möchte.
Jedoch bleibt die GPS_ID immer die selbe, sobald der Zeitstempel can_Timestamp eigentlich dem nächsten gps_Timestamp zugeschrieben werden müsste ändert sich nichts.
Irgendwelche Ideen?

Der Nächste GPS-Eintrag hat nämlich den Zeitstempel
gps_ID | lat | lng | speed | vehicleID | timestamp
2 52.221000 9.416259 0 1 2368
Demnach sollte can_ID 4 bereits der gps_ID 2 zugeschrieben werden, da der Unterschied zwischen 2368 und 2300 geringer ist als zu 1823.

ukulele · 12 April 2017

Ich habs mal anhand des Beispiels versucht und zwar so das es auch auf MySQL gehen müsste, wie schnell kann ich nicht einschätzen.

Code:

WITH can(id,canid,logid,data,[timestamp]) AS (
SELECT   1,0x90FE6FFE,1,'000000C533BB87FF',600
UNION ALL
SELECT   2,0x8CF004FE,1,'007D7D9C6300F07D',610
UNION ALL
SELECT   3,0x90FE6FFE,1,'000000C5E6C683FF',1640
UNION ALL
SELECT   4,0x8CF004FE,1,'007D7DF03A00F07D',1500
UNION ALL
SELECT   5,0x90FE6FFE,1,'000000C59AD283FF',1600
     ), gps(id,lat,lng,logid,[timestamp]) AS (
SELECT   1,52.220961,9.416273,1,550
UNION ALL
SELECT   2,52.220960,9.416273,1,1550
UNION ALL
SELECT   3,52.220960,9.416273,1,2551
UNION ALL
SELECT   4,52.220960,9.416273,1,3546
UNION ALL
SELECT   5,52.220959,9.416274,1,4548
     )

Das emuliert nur deine Beispieltabellen bei mir, das muss MySQL nicht können.

Code:

SELECT   t2.logid,
     t2.can_ID,
     t2.lat,
     t2.lng,
     t2.gps_timestamp,
     t2.diff_to_gps_TS,
     t2.gps_ID
FROM   (

SELECT   g.logid,
     NULL AS can_ID,
     g.lat,
     g.lng,
     g.[timestamp] AS gps_timestamp,
     0 AS diff_to_gps_TS,
     g.id AS gps_ID
FROM   gps g
UNION ALL
SELECT   c1.logid,
     c1.id AS can_ID,
     g1.lat,
     g1.lng,
     g1.[timestamp] AS gps_timestamp,
     t1.diff_to_gps_TS,
     g1.id AS gps_ID
FROM   can c1
INNER JOIN gps g1
ON     c1.logid = g1.logid
INNER JOIN (

SELECT   c2.logid,
     c2.id AS can_ID,
     min(abs(c2.[timestamp] - g2.[timestamp])) AS diff_to_gps_TS
FROM   can c2
INNER JOIN gps g2
ON     c2.logid = g2.logid
GROUP BY c2.logid,c2.id

     ) t1
ON     t1.logid = g1.logid
AND     t1.can_ID = c1.id
AND     t1.diff_to_gps_TS = abs(c1.[timestamp] - g1.[timestamp])

     ) t2
ORDER BY t2.logid,t2.gps_ID,t2.can_ID

logid can_ID lat lng gps_timestamp diff_to_gps_TS gps_ID
----------- ----------- --------------------------------------- --------------------------------------- ------------- -------------- -----------
1 NULL 52.220961 9.416273 550 0 1
1 1 52.220961 9.416273 550 50 1
1 2 52.220961 9.416273 550 60 1
1 NULL 52.220960 9.416273 1550 0 2
1 3 52.220960 9.416273 1550 90 2
1 4 52.220960 9.416273 1550 50 2
1 5 52.220960 9.416273 1550 50 2
1 NULL 52.220960 9.416273 2551 0 3
1 NULL 52.220960 9.416273 3546 0 4
1 NULL 52.220959 9.416274 4548 0 5

(10 Zeile(n) betroffen)[/QOUTE]

Nächsten Zeitstempel finden, Abfrage dauert ewig.

Payoff

Neuer Benutzer

akretschmer

Datenbank-Guru

Payoff

Neuer Benutzer

ukulele

Datenbank-Guru

Ähnliche Themen