MySQL 公里计算

Question

示例表如下。 Km 列显示汽车的公里计数器值。这是一个不断增加的值(value)。我每 5 分钟自动记录一次数据，以公里为单位。

ID	TIMESTAMP	            KM
1	    3.11.2017 10:00	    3778733
2	    3.11.2017 10:05	    3778774
3	    3.11.2017 10:10	    3778816
4	    3.11.2017 10:15	    3778857
5	    3.11.2017 10:20	    3778897
6	    3.11.2017 10:25	    3778937
7	    3.11.2017 10:30	    3778976
8	    3.11.2017 10:35	    3779015
9	    3.11.2017 10:40	    3779054
10	  3.11.2017 10:45	    3779094
11	  3.11.2017 10:50	    3779138
12	  3.11.2017 10:55	    3779178
13	  3.11.2017 11:00	    3779217
14	  3.11.2017 11:05	    3779260
15	  3.11.2017 11:10	    3779302
16	  3.11.2017 11:15	    3779343
17	  3.11.2017 11:20	    3779384
18	  3.11.2017 11:25	    3779426
19	  3.11.2017 11:30	    3779468
20	  3.11.2017 11:35	    3779511
21	  3.11.2017 11:40	    3779558
22	  3.11.2017 11:45	    3779600
23	  3.11.2017 11:50	    3779641
24	  3.11.2017 11:55	    3779681
25	  3.11.2017 12:00	    3779723
26	  3.11.2017 12:05	    3779764
27	  3.11.2017 12:10	    3779807
28	  3.11.2017 12:15	    3779849
29	  3.11.2017 12:20	    3779890
30	  3.11.2017 12:25	    3779932
31	  3.11.2017 12:30	    3779981
32	  3.11.2017 12:35	    3780024
33	  3.11.2017 12:40	    3780066
34	  3.11.2017 12:45	    3780110
35	  3.11.2017 12:50	    3780152
36	  3.11.2017 12:55	    3780195
37	  3.11.2017 13:00	    3780200

我想计算汽车每小时行驶的距离(使用 View 表)。

示例输出表；

....
.....
......
10:00 - 11:00 => 484
11:00 - 12:00 => 506
12:00 - 13:00 => 477
......
.....
....

如何编写sql代码？你能帮我吗？

Answer 1

在分组之前使用并集为 0 分钟行创建虚拟记录。请注意，只有在不跨越日期界限的情况下，这才有效 - 如果跨越日期界限，则编写一个程序来执行此操作。

drop table if exists t;

create table t
(ID int,    tv varchar(20), ts datetime  ,  dt date, hr int,         KM int);
insert into t (id,tv,km) values
(1    ,  '3.11.2017 10:00'    ,  3778733),
(2    ,  '3.11.2017 10:05'    ,  3778774),
(3    ,  '3.11.2017 10:10'    ,  3778816),
(4    ,  '3.11.2017 10:15'    ,  3778857),
(5    ,  '3.11.2017 10:20'    ,  3778897),
(6    ,  '3.11.2017 10:25'    ,  3778937),
(7    ,  '3.11.2017 10:30'    ,  3778976),
(8    ,  '3.11.2017 10:35'    ,  3779015),
(9    ,  '3.11.2017 10:40'    ,  3779054),
(10,      '3.11.2017 10:45'   ,  3779094),
(11,      '3.11.2017 10:50'   ,  3779138),
(12,      '3.11.2017 10:55'   ,  3779178),
(13 , '3.11.2017 11:00',        3779217),
(14 ,  '3.11.2017 11:05',       3779260),
(15 ,  '3.11.2017 11:10',       3779302),
(16 ,  '3.11.2017 11:15',       3779343),
(17 ,  '3.11.2017 11:20',       3779384),
(18 ,  '3.11.2017 11:25',       3779426),
(19 ,  '3.11.2017 11:30',       3779468),
(20 ,  '3.11.2017 11:35',       3779511),
(21 ,  '3.11.2017 11:40',       3779558),
(22 ,  '3.11.2017 11:45',       3779600),
(23 ,  '3.11.2017 11:50',       3779641),
(24 ,  '3.11.2017 11:55',       3779681),
(25 ,  '3.11.2017 12:00',       3779723),
(26 ,  '3.11.2017 12:05',       3779764),
(27 ,  '3.11.2017 12:10',       3779807),
(28 ,  '3.11.2017 12:15',       3779849),
(29  , '3.11.2017 12:20',       3779890),
(30 ,  '3.11.2017 12:25',       3779932),
(31 ,  '3.11.2017 12:30',       3779981),
(32 ,  '3.11.2017 12:35',       3780024),
(33 ,  '3.11.2017 12:40',       3780066),
(34 ,  '3.11.2017 12:45',       3780110),
(35 ,  '3.11.2017 12:50',       3780152),
(36 ,  '3.11.2017 12:55',       3780195),
(37 ,  '3.11.2017 13:00',    3780200);

select id,str_to_date(tv,'%d.%m.%Y %H:%i') from t;

update t 
    set ts = str_to_date(tv,'%d.%m.%Y %H:%i');
update t 
    set dt = date(ts), hr = hour(ts);

select dt,hr, min(km), max(km) ,max(km) - min(km)
from (select dt,hr,km from t
union all
select dt,hr - 1 ,km from t where minute(ts) = 0) s
group by dt,hr