Форум пользователей MySQL

klow · 25.03.2016 10:27:55

Добрый день! Прошу помочь.
Есть таблица с координатами трассы, скорость и время в точке.

CREATE TABLE route (

  ID bigint(20) NOT NULL,

  Lon varchar(12) NOT NULL,

  Lat varchar(12) NOT NULL,

  Speed int(11) NOT NULL,

  DateStan datetime NOT NULL,

  PRIMARY KEY (ID)

)...

Нужно сгруппировать одинаковые значения точек, когда машина стоит на месте

Group by Lon, Lat, Speed;

Но так чтобы повторные точки, когда машина возвращается на прежнее место, не попадали в группировку.
Например, есть значения (условные): Lon, Lat, Speed, DateStan
1. Lon1, Lat1, Speed1, DateStan1 -- Стоит
2. Lon1, Lat1, Speed1, DateStan2 -- Стоит
3. Lon2, Lat2, Speed2, DateStan3 -- Уехала
4. Lon1, Lat1, Speed1, DateStan4 -- Возвратилась в исходную точку

Даты всегда разные и DateStan1<DateStan2<DateStan3<DateStan4

В результате должно быть
1. Lon1, Lat1, Speed1
2. Lon2, Lat2, Speed2
3. Lon1, Lat1, Speed1

То есть, записи 1 и 2 сгруппировались, но запись 4 осталась отдельно, хотя у нее те-же значения, что и у записей 1 и 2, но другое время.

vasya · 26.03.2016 10:03:20

klow написал:
То есть, записи 1 и 2 сгруппировались, но запись 4 осталась отдельно, хотя у нее те-же значения, что и у записей 1 и 2, но другое время.

Время у записей 1 и 2 тоже разное. Важен факт, что в промежутке машина уезжала. Можно на лету с помощью пользовательских переменных генерировать доп поле, в котором объединять интервалы стоянки в один этап и уже потом делать группировку с учетом этого поля.(но пипец производительности)
Будет
Lon, Lat, Speed, DateStan, Dop
1. Lon1, Lat1, Speed1, DateStan1, 1 -- Стоит
2. Lon1, Lat1, Speed1, DateStan2, 1 -- Стоит
3. Lon2, Lat2, Speed2, DateStan3, 2 -- Уехала
4. Lon1, Lat1, Speed1, DateStan4, 3 -- Возвратилась в исходную точку

Group by Dop, Lon, Lat, Speed;

klow · 26.03.2016 18:49:31

Вариант, производительность не существенна, так как выборка будет на уже на предварительно отобранных записях, примерно, 1-3 тыс.
Спасибо!

vasya · 26.03.2016 19:57:56

Группировка то вам и не нужна, нужно из нескольких подряд идущих строк где машина стоит (speed=0) оставить первую.
Т.е. проходим всю таблицу по возрастанию времени и для строк speed=0 смотрим скорость в предыдущей строке и если тоже 0, то отбрасываем эту строку

MariaDB [test]> select * from route;

+----+------+------+-------+-----------+

| ID | Lon  | Lat  | Speed | DateStan  |

+----+------+------+-------+-----------+

|  1 | Lon1 | Lat1 |     0 | DateStan1 |

|  2 | Lon1 | Lat1 |     0 | DateStan2 |

|  3 | Lon2 | Lat2 |     4 | DateStan3 |

|  4 | Lon1 | Lat1 |     0 | DateStan4 |

+----+------+------+-------+-----------+

4 rows in set (0.00 sec)

MariaDB [test]> select id, lon, lat, speed, datestan from 

(select if(speed=0,if(@s=speed,0,1),1) y, r.*, @s:=speed from route r, (select @s:=-1) x order by datestan) t

where y;

+----+------+------+-------+-----------+

| id | lon  | lat  | speed | datestan  |

+----+------+------+-------+-----------+

|  1 | Lon1 | Lat1 |     0 | DateStan1 |

|  3 | Lon2 | Lat2 |     4 | DateStan3 |

|  4 | Lon1 | Lat1 |     0 | DateStan4 |

+----+------+------+-------+-----------+

3 rows in set (0.00 sec)

или без переменных

MariaDB [test]> select * from route r where speed<>0 or 

(speed=0 and speed<>ifnull((select speed from route where datestan < r.datestan order by datestan desc limit 1),5));

+----+------+------+-------+-----------+

| ID | Lon  | Lat  | Speed | DateStan  |

+----+------+------+-------+-----------+

|  1 | Lon1 | Lat1 |     0 | DateStan1 |

|  3 | Lon2 | Lat2 |     4 | DateStan3 |

|  4 | Lon1 | Lat1 |     0 | DateStan4 |

+----+------+------+-------+-----------+

3 rows in set (0.00 sec)

klow · 26.03.2016 21:43:22

Извини, немного протупил и не до конца рассказал задачу. Думал не важно.
Мне нужно, кроме того, получить начальное и конечное время стоянки.
Но в любом случае спасибо!
Думаю, что гораздо проще будет эту задачу переложить на приложение. Там это решается просто, но есть и свои недостатки.

Форум пользователей MySQL

#1 25.03.2016 10:27:55

Группировка координат трассы

#2 26.03.2016 10:03:20

Re: Группировка координат трассы

klow написал:

#3 26.03.2016 18:49:31

Re: Группировка координат трассы

#4 26.03.2016 19:57:56

Re: Группировка координат трассы

#5 26.03.2016 21:43:22

Re: Группировка координат трассы

Board footer