| vehicle_id | GPS_time | speed |
+------------+---------------------+-------+
| 2031 | 2010-01-07 22:37:10 | 0 |
| 2031 | 2010-01-07 22:40:25 | 0 |
| 2031 | 2010-01-07 22:43:37 | 0 |
| 2031 | 2010-01-07 22:46:50 | 1|
| 2031 | 2010-01-07 22:50:04 | 1 |
| 2031 | 2010-01-07 22:53:19 | 1 |
| 2031 | 2010-01-07 22:56:34 | 1|
| 2031 | 2010-01-07 22:59:49 | 1 |
| 2031 | 2010-01-07 23:03:03 | 0 |
| 2031 | 2010-01-07 23:06:18 | 0 |
| 2031 | 2010-01-07 23:09:33 | 0 |
| 2031 | 2010-01-07 23:12:46 | 0 |
| 2031 | 2010-01-07 23:16:01 | 0 |
| 2031 | 2010-01-07 23:19:14 | 0 |
| 2031 | 2010-01-07 23:22:26 | 0 |
| 2031 | 2010-01-07 23:25:40 | 0 |
| 2031 | 2010-01-07 23:28:54 | 0 |
| 2031 | 2010-01-07 23:32:09 | 0 |
| 2031 | 2010-01-07 23:35:23 | 0 |
| 2031 | 2010-01-07 23:38:39 | 0 |
| 2031 | 2010-01-07 23:41:56 | 0 |
| 2031 | 2010-01-07 23:45:13 | 1 |
| 2031 | 2010-01-07 23:48:27 | 1 |
| 2031 | 2010-01-07 23:51:45 | 1 |
| 2031 | 2010-01-07 23:55:00 | 1 |
| 2031 | 2010-01-07 23:58:17 | 1 |
| 2031 | 2010-01-08 00:01:30 | 0 |
| 2031 | 2010-01-08 00:04:45 | 0 |
| 2031 | 2010-01-08 00:08:03 | 0 |
| 2031 | 2010-01-08 00:11:15 | 0 |
| 2031 | 2010-01-08 00:14:30 | 0 |
| 2031 | 2010-01-08 00:17:46 | 0 |
| 2031 | 2010-01-08 00:21:00 | 0 |
| 2031 | 2010-01-08 00:24:13 | 0 |
| 2031 | 2010-01-08 00:27:28 | 0 |
| 2031 | 2010-01-08 00:30:43 | 0 |
| 2031 | 2010-01-08 00:33:58 | 4 |
| 2031 | 2010-01-08 00:37:16 | 1 |
| 2031 | 2010-01-08 00:40:31 | 1 |
| 2031 | 2010-01-08 00:43:46 | 0 |
| 2031 | 2010-01-08 00:47:00 | 0 |
| 2031 | 2010-01-08 00:50:15 | 0 如上表,是已经查询ID的结果。想要实现当speed 相邻为0的项合并,并用这些相邻的最大时间减去最小时间,得到时间差。
比如上表应该得到共四项,因为有四处连续为0的地方。
ID stoptime speed
2031 13.5小时 0
2031 14.6小时 0
SQL可以实现么?
+------------+---------------------+-------+
| 2031 | 2010-01-07 22:37:10 | 0 |
| 2031 | 2010-01-07 22:40:25 | 0 |
| 2031 | 2010-01-07 22:43:37 | 0 |
| 2031 | 2010-01-07 22:46:50 | 1|
| 2031 | 2010-01-07 22:50:04 | 1 |
| 2031 | 2010-01-07 22:53:19 | 1 |
| 2031 | 2010-01-07 22:56:34 | 1|
| 2031 | 2010-01-07 22:59:49 | 1 |
| 2031 | 2010-01-07 23:03:03 | 0 |
| 2031 | 2010-01-07 23:06:18 | 0 |
| 2031 | 2010-01-07 23:09:33 | 0 |
| 2031 | 2010-01-07 23:12:46 | 0 |
| 2031 | 2010-01-07 23:16:01 | 0 |
| 2031 | 2010-01-07 23:19:14 | 0 |
| 2031 | 2010-01-07 23:22:26 | 0 |
| 2031 | 2010-01-07 23:25:40 | 0 |
| 2031 | 2010-01-07 23:28:54 | 0 |
| 2031 | 2010-01-07 23:32:09 | 0 |
| 2031 | 2010-01-07 23:35:23 | 0 |
| 2031 | 2010-01-07 23:38:39 | 0 |
| 2031 | 2010-01-07 23:41:56 | 0 |
| 2031 | 2010-01-07 23:45:13 | 1 |
| 2031 | 2010-01-07 23:48:27 | 1 |
| 2031 | 2010-01-07 23:51:45 | 1 |
| 2031 | 2010-01-07 23:55:00 | 1 |
| 2031 | 2010-01-07 23:58:17 | 1 |
| 2031 | 2010-01-08 00:01:30 | 0 |
| 2031 | 2010-01-08 00:04:45 | 0 |
| 2031 | 2010-01-08 00:08:03 | 0 |
| 2031 | 2010-01-08 00:11:15 | 0 |
| 2031 | 2010-01-08 00:14:30 | 0 |
| 2031 | 2010-01-08 00:17:46 | 0 |
| 2031 | 2010-01-08 00:21:00 | 0 |
| 2031 | 2010-01-08 00:24:13 | 0 |
| 2031 | 2010-01-08 00:27:28 | 0 |
| 2031 | 2010-01-08 00:30:43 | 0 |
| 2031 | 2010-01-08 00:33:58 | 4 |
| 2031 | 2010-01-08 00:37:16 | 1 |
| 2031 | 2010-01-08 00:40:31 | 1 |
| 2031 | 2010-01-08 00:43:46 | 0 |
| 2031 | 2010-01-08 00:47:00 | 0 |
| 2031 | 2010-01-08 00:50:15 | 0 如上表,是已经查询ID的结果。想要实现当speed 相邻为0的项合并,并用这些相邻的最大时间减去最小时间,得到时间差。
比如上表应该得到共四项,因为有四处连续为0的地方。
ID stoptime speed
2031 13.5小时 0
2031 14.6小时 0
SQL可以实现么?
| vehicle_id | GPS_time | speed |
+------------+---------------------+-------+
| 2031 | 2010-01-07 22:37:10 | 0 |
| 2031 | 2010-01-07 22:40:25 | 0 |
| 2031 | 2010-01-07 22:43:37 | 0 |
| 2031 | 2010-01-07 22:46:50 | 1|
| vehicle_id | GPS_time | speed |
+------------+---------------------+-------+
| 2031 | 2010-01-07 22:37:10 | 0 |
| 2031 | 2010-01-07 22:40:25 | 0 |
| 2031 | 2010-01-07 22:43:37 | 0 |
这样的表,第一项是要得到2010-01-07 22:43:37 减去2010-01-07 22:37:10 的时间差。