0

I have a data from GPS tracker. Let's say something like:

CREATE TABLE IF NOT EXISTS `gps_data` (
  `id` int(6) unsigned NOT NULL,
  `speed` int(3) unsigned NOT NULL,
  `time` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

and some sample data:

INSERT INTO `gps_data` (`id`, `speed`, `time`) VALUES
  ('1', '5', '07:00'),
  ('2', '10', '07:10'),
  ('3', '0', '07:20'),
  ('4', '0', '07:30'),
  ('5', '0', '07:40'),
  ('6', '0', '07:50'),
  ('7', '20', '08:00'),
  ('8', '40', '08:10'),
  ('9', '15', '08:15'),
  ('10', '0', '08:32'),
  ('11', '0', '08:40'),
  ('12', '0', '08:52'),
  ('13', '12', '09:10'),
  ('14', '0', '09:25');

The question is how to find a time of first and last position with speed = 0.

So in my example I would like to have something like:

[break_start, break_stop]
[07:20, 07:50]
[08:32, 08:52]
[09:25, NULL]

Here is the Fiddle to better understanding: http://sqlfiddle.com/#!9/d79228/4

What I have started to try is:

SELECT `time` AS break_start, `time` AS break_stop  FROM `gps_data` WHERE `speed`=0;
Tikky
  • 1,253
  • 2
  • 17
  • 36

1 Answers1

1

One method in MySQL is to assign a group to each row. This group can be the number of non-zero values before the row -- all rows with adjacent zero values are in the same group.

In MySQL 8+, you can use window functions for this:

select min(time), max(time)
from (select t.*,
             sum(speed <> 0) over (order by time) as grp
      from t
     ) t
where speed = 0
group by grp;

In earlier versions, one method is a correlated subquery:

select min(time), max(time)
from (select t.*,
             (select count(*)
              from t t2
              where t2.speed <> 0 and t2.time <= t.time
             ) as grp
      from t
     ) t
where speed = 0
group by grp;

Here is a SQL Fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you. Work as expected – Tikky Nov 24 '18 at 17:07
  • Gordon Linoff: thank you once again for help. I've tested this query in my production DB however works slower then expected. About 18s for this query. Is there something which could be improved in terms of speed? – Tikky Nov 25 '18 at 09:28
  • @Tikky . . . Can you ask another question regarding performance? – Gordon Linoff Nov 25 '18 at 21:49