Group timestamped records by consecutive repeating flag

user_4265785 0
Viewed 39

I have a dataset with the following columns:

DriverId  DateStamp        IsDriving  WasDriving DistanceSincePrev SecondsSincePrev
1         11/10/2018 08:00 0          0          0                 12
1         11/10/2018 08:01 1          0          10                60
1         11/10/2018 08:01 1          1          100               54
1         11/10/2018 08:02 1          1          14                32
1         11/10/2018 08:03 1          1          33                60
1         11/10/2018 08:04 0          1          10                59
1         11/10/2018 08:04 0          0          0                 60
1         11/10/2018 08:05 1          0          0                 60
1         11/10/2018 08:06 1          1          500               43
1         11/10/2018 08:06 0          1          300               32
1         11/10/2018 08:07 0          0          0                 60
1         11/10/2018 08:08 0          0          0                 12
1         11/10/2018 08:09 0          0          10                60
1         11/10/2018 08:10 0          0          100               54
1         11/10/2018 08:11 0          0          14                32
1         11/10/2018 08:12 0          0          33                60
1         11/10/2018 08:13 0          0          10                59
1         11/10/2018 08:14 0          0          0                 60
1         11/10/2018 08:15 1          0          0                 60
1         11/10/2018 08:16 1          1          500               43
1         11/10/2018 08:16 1          1          300               32
1         11/10/2018 08:17 1          1          0                 60
1         11/10/2018 08:18 1          1          500               43
1         11/10/2018 08:19 1          1          300               32
1         11/10/2018 08:19 1          1          0                 60
1         11/10/2018 08:20 1          1          500               43
1         11/10/2018 08:21 1          1          300               32
1         11/10/2018 08:22 1          1          0                 60
1         11/10/2018 08:23 1          1          500               43
1         11/10/2018 08:24 1          1          300               32
1         11/10/2018 08:24 0          1          0                 60
1         11/10/2018 08:25 0          0          0                 60

As you can see, those are timestamps of a person's driving. I'd like to group those timestamps into RIDES, and by that I mean sections where the person is driving without turning the engine off. In this dataset, I can use the 'IsDriving' and 'WasDriving' columns to do that. But I am having problems with writing the query.

I have 2 ideas on how the algorithm might work

1) More ideal, probably more difficult: the query would detect the records where IsDriving is 1 and WasDriving is 0 and count that as the beginning of the ride. Then it would detect the record where IsDriving is 0 and WasDriving is 1 and it would end the ride there.

2) Somewhat of a heuristic, but it would be gould enough: the query would simply aggregate records where both IsDriving and WasDriving is set to 1 consecutively and count that as a single ride.

Unfortunately, I can't get my head around putting that algotithm into SQL.

Ideally, my output would look like that:

DriverId StartOfRide       DistanceOfRide  LengthOfRide
1        11/10/2018 08:00  1400            221
1        11/10/2018 08:30  5900            329
1        11/10/2018 12:00  21400           3600
hive sql
2 Answers

You need to assign groups and then aggregate. In this case, you can define a group as the number of 0 values in IsDriving up to each record. Then aggregate:

select driverid, min(datestamp) as startofride,
       sum(distance) as distance,
       sum(seconds) as seconds
from (select t.*,
             sum(1 - isdriving) over (partition by driverid order by datestamp) as grp
      from t
     ) t
group by driverid, grp
user_11440350

Maybe this will do, remove/add columns you don't/do need:

create table #tmp (DriverId int , DateStamp datetime, IsDriving int , WasDriving int, DistanceSincePrev float, SecondsSincePrev float)

insert into #tmp values 
(1,        ' 11/10/2018 08:00', 0  ,        0     ,     0      ,           12),
(1,         '11/10/2018 08:01', 1 ,         0  ,        10  ,              60),
(1,         '11/10/2018 08:01' ,1 ,         1  ,        100 ,              54),
(1,         '11/10/2018 08:02' ,1 ,         1   ,       14  ,              32),
(1,         '11/10/2018 08:03' ,1 ,         1    ,      33,                60),
(1,         '11/10/2018 08:04' ,0 ,         1     ,     10  ,              59),
(1,         '11/10/2018 08:04' ,0 ,         0      ,    0   ,              60),
(1,         '11/10/2018 08:05' ,1 ,         0    ,      0   ,              60),
(1,         '11/10/2018 08:06' ,1 ,         1      ,    500  ,             43),
(1,         '11/10/2018 08:06' ,0 ,         1     ,     300  ,             32),
(1,         '11/10/2018 08:07' ,0 ,         0     ,     0    ,             60),
(1,         '11/10/2018 08:08' ,0 ,         0     ,     0   ,              12),
(1,         '11/10/2018 08:09' ,0 ,         0     ,     10  ,              60),
(1,         '11/10/2018 08:10' ,0 ,         0     ,     100,               54),
(1,         '11/10/2018 08:11' ,0 ,         0     ,     14 ,               32),
(1,        ' 11/10/2018 08:12' ,0 ,         0     ,     33  ,              60),
(1,         '11/10/2018 08:13' ,0 ,         0     ,     10  ,              59)





select * from 

(
select DateStamp as RideStart,DriverID, Grp,(SUM(DistanceSincePrev) over (partition by grp)) as DistanceofRide,
(SUM(SecondsSincePrev ) over (partition by grp)) as LengthofRide,
ROW_NUMBER() over (PARTITION by driverid,grp order by datestamp) r
from
(
 SELECT
    *,
    Grp = ROW_NUMBER() OVER (PARTITION BY driverID ORDER BY DateStamp) -
     ROW_NUMBER() OVER (PARTITION BY driverID,IsDriving ORDER BY DateStamp)
  FROM #tmp
) s
) x
where r = 1
user_46234890
Related