Let´s suppose this situation:
CAR TIME A 1300 A 1301 A 1302 A 1315 A 1316 A 1317 A 1319 A 1320 B 1321 B 1322
I´d like to generate another column, enumerating each trip did by each car. We consider there´s a new trip every time we get a discontinuity on TIME.
CAR TIME TRIP A 1300 1 A 1301 1 A 1302 1 A 1315 2 A 1316 2 A 1317 2 A 1319 3 A 1320 3 B 1321 1 B 1322 1
Is there some SQL function to obtain this count ? Thanks in advance.
SELECT t.car, t.time, MIN(t3.time) FROM test t, test t3 WHERE NOT EXISTS (SELECT 1 FROM test t2 WHERE t2.car = t.car AND t2.time = t.time - 1) AND t3.car = t.car AND t3.time >= t.time AND NOT EXISTS (SELECT 1 FROM test t4 WHERE t4.car = t3.car AND t4.time = t3.time + 1) GROUP BY t.car, t.time ORDER BY 1, 2;
The first not-exists finds all the rows that don't have a row for the same car in the previous minute - that is to say, those rows who begin a period for a car.
The later not-exists gets a set of rows that do not have a following row for the same car - i.e. rows that end a period. The max function finds the least of these (that also are filtered to be greater or equal to the start of the period in question.