Co相关子组的Sql查询。 [英] Sql Query for Co-related Subgroups.

问题描述

我有车辆跟踪数据，我想创建车辆停止报告。帮我写那个sql查询。实际数据和结果数据分别如表1和表2所示。



表1

I've vehicle tracking data, I want to create Vehicle stoppage report. Help me to write sql query for that. Actual Data and resultant data are like Table1 and Table2 respectively.

TABLE 1

VehicleId   UnixTimeStamp           Speed
1           17-01-2015 12:00:05       12
1           17-01-2015 12:00:06       0
1           17-01-2015 12:00:07       0
1           17-01-2015 12:00:08       0
1           17-01-2015 12:00:09       5
1           17-01-2015 12:00:10       8
1           17-01-2015 12:00:11       0
1           17-01-2015 12:00:12       0
1           17-01-2015 12:00:13       0
1           17-01-2015 12:00:14       7
2           17-01-2015 12:00:05       10
2           17-01-2015 12:00:06       0
2           17-01-2015 12:00:07       0
2           17-01-2015 12:00:08       0
2           17-01-2015 12:00:09       12
2           17-01-2015 12:00:10       0
2           17-01-2015 12:00:11       0
2           17-01-2015 12:00:12       10

表2

TABLE 2

Vehicield	Stopped From	                 Stopped To	        Duration (Sec)
1	        17-01-2015 12:00:06	         17-01-2015 12:00:08	         3
1	        17-01-2015 12:00:11	         17-01-2015 12:00:13	         3
2	        17-01-2015 12:00:06	         17-01-2015 12:00:08	         3
2	        17-01-2015 12:00:10	         17-01-2015 12:00:11	         2

推荐答案

实际上没有对此进行测试，但可能类似于这个：

Haven't actually tested this but perhaps something like this:

select start.unixtimestamp,
       stop.unixtimestamp
from table1 start,
     table1 stop
where start.speed        = 0
and   stop.vehicleid     = start.vehicleid
and   stop.unixtimestamp > start.unixtimestamp
and   stop.speed         = 0
and   exists (select 1 
              from  table1 prev
              where prev.vehicleid     = start.vehicleid
              and   prev.unixtimestamp = (select max(p2.unixtimestamp)
                                          from   table1 p2
                                          where  p2.vehicleid     = start.vehicleid
                                          and    p2.unixtimestamp < start.unixtimestamp)
              and   prev.speed > 0) 
and stop.unixtimestamp = (select max(p4.unixtimestamp)
                          from table1 p4
						  where p4.unixtimestamp < (select min(p3.unixtimestamp)
                                 from   table1 p3
                                 where  p3.vehicleid     = stop.vehicleid
                                 and    p3.unixtimestamp > start.unixtimestamp
                                 and    p3.speed         != 0))

编辑：纠正了一些错误

corrected few mistakes

我试过这个...

I tried this...

declare @t as table (vehicleid INT, unixtimestamp DATETIME, speed int)

INSERT INTO @t VALUES(1, '2015-01-17 12:00:05', 12)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:06', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:07', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:08', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:09', 5)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:10', 8)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:11', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:12', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:13', 0)
INSERT INTO @t VALUES(1, '2015-01-17 12:00:14', 7)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:05', 10)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:06', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:07', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:08', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:09', 12)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:10', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:11', 0)
INSERT INTO @t VALUES(2, '2015-01-17 12:00:12', 10)


select start.unixtimestamp,
       stop.unixtimestamp
from @t start,
     @t stop
where start.speed        = 0
and   stop.vehicleid     = start.vehicleid
and   stop.unixtimestamp > start.unixtimestamp
and   stop.speed         = 0
and   exists (select 1 
              from  @t prev
              where prev.vehicleid     = start.vehicleid
              and   prev.unixtimestamp = (select max(p2.unixtimestamp)
                                          from   @t p2
                                          where  p2.vehicleid = start.vehicleid
                                          and    p2.unixtimestamp < start.unixtimestamp)
              and   prev.speed > 0) 
and not exists (select 1
                from   @t p3
                where  p3.vehicleid     = start.vehicleid
                and    p3.unixtimestamp > start.unixtimestamp
                and    p3.unixtimestamp < stop.unixtimestamp
                and    p3.speed         > 0)

但结果不正确......两列如下：

but results are not correct...two columns like this:

unixtimestamp           unixtimestamp
2015-01-17 12:00:06.000 2015-01-17 12:00:07.000
2015-01-17 12:00:06.000 2015-01-17 12:00:08.000
2015-01-17 12:00:11.000 2015-01-17 12:00:12.000
2015-01-17 12:00:11.000 2015-01-17 12:00:13.000
2015-01-17 12:00:06.000 2015-01-17 12:00:07.000
2015-01-17 12:00:06.000 2015-01-17 12:00:08.000
2015-01-17 12:00:10.000 2015-01-17 12:00:11.000

这篇关于Co相关子组的Sql查询。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！