Counting blocks of continuous sequences in SQL

  • A+
Category:Languages

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.

 


Another approach:

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.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: