I have a SQL table similar to this:
+----+-------+--------+-------+-------+
| rd | ts_in | ts_out | sens | devid |
+----+-------+--------+-------+-------+
| 1 | 0 | 1577 | 2 | 2 |
| 1 | 229 | 1549 | 1 | 2 |
| 1 | 123 | 1322 | 0 | 2 |
+----+-------+--------+-------+-------+
Where a data logger ends up storing it's data.
This data logger is stored underground and it have two inductive devices that detect when a car walks on top of it, one on it's beginning, another on it's end. It also have a device between the two sensors which weights the car axis on top of it.
When an inductive device detects a car is on top of it, the datalogger takes a timestamp of it's internal clock (ts_in) and waits until the sensor is deasserted, by then it takes another snapshot (ts_out) and it inserts a line on the table containing the datalogger id (devid), the sensor which triggered (1 = front, 2 = rear) the event, and the related timestamps. It also sets the rd field to a per-sensor unique counter (but it's not a per-device, as you can notice in the example).
The weight device also have similar behavior but it stores sens=0 and other data not present in the example (but it's ts_in and ts_out works the same).
One can think of I would always get data like this: sens 1 -> sens 0 -> sens 2 if the vehicle goes in some direction and: sens 2 -> sens 0 -> sens 1 if the vehicle goes in the opposite direction, but there are caveats:
a) the sensors may fail. The weight doesn't, so sometimes I can get only a sens 0 event, other times it gets it and one of the sensors but not the other.
b) I need to match the events around a sens0 entry: if I have a sens0 entry with a ts_out > sens1_tsout && sens2_tsout, I know those sens1/sens2 entries can't belong to this one.
In order for this information to be useful to me, I need to do a self join on the table and extract the following information:
- I need do determine, for every sens0 entries, if there's a pair of matching sens1 & 2 entries for which it's ts_out < sens0_tsout, and I also need to get the ts_in of the first sensor that hits (something in mysql like LEAST(a.ts_in, a.ts_out) AND GREATEST(a.ts_out, b.ts_out).
What I expect in output is something like (the column order is unimportant):
+------| --------- | ------------+
|devid | direction | sens0_data |
+------| --------- | ------------+
| 2 | *text* | *weigth* |
+------| --------- | ------------+
Where text could be something like:
"forward" (i.e. tsin_sens11 > tsin_sens2 && tsout_sens2 > tsout_sens1);
"backward" (i.e.tsin_sens12 > tsin_sens1 && tsout_sens1 > tsout_sens2) "forward_rewind" (i.e. tsin_sens1 > tsin_sens2 && tsout_sens2 < tsout_sens1);
"backward_rewind" (i.e. tsin_sens2 > tsin_sens1 && tsout_sens1 < tsout_sens2);
NULL (i.e. there's no lines maching the above conditions, inclusive the case where there's no matching sens1/2 lines where ts_out < sens0 ts_out).
Basically, I need to prove, for every sens0 line, that I either:
- Have a nearby (in terms of ts_in & ts_out) pair of sens = 1 and sens = 2 lines;
-
Having those, that the car entered on one direction (which I must differentiate) and left on the other, or that it went backwards.
-
That, absent those conditions, I only have the sens = 0 data and NULL for the direction field.
Is that feasible in two or three inner joins or should I take a multiple query step?
