Блог

я использую sql server 2012 для фильтрации требуемого результата, если скорость равна (> = 0 и <=5), то не учитывайте движение, а если скорость > 5, то она должна учитываться как движение ниже приведен запрос

 SELECT t.ObjectId, t.Number,t.VectorSpeed, t.GpsTime,t.X,t.Y

FROM (
SELECT  o.ObjectId, o.Number,o.Comment, m.VectorSpeed, m.X, m.Y, m.GpsTime,
     LEAD(m.VectorSpeed) OVER (PARTITION BY o.ObjectId, o.Number
                       ORDER BY m.GpsTime) AS NextSpeed,
     LAG(m.VectorSpeed) OVER (PARTITION BY o.ObjectId, o.Number
                       ORDER BY m.GpsTime) AS PrevSpeed         
FROM  tavl2.tavl.Object o INNER JOIN
                  tavl2.tavl.Message m ON o.ObjectId = m.ObjectId
where o.ClientId = 5 AND o.ObjectId = 1662 AND 
CONVERT(date,m.GpsTime)>=convert(date,'2020/8/25') and CONVERT(date,m.GpsTime)<=convert(date,'2020/8/25')
AND (m.Valid = 1) AND (m.Canal = 1)                               
                  
                  ) AS t
WHERE  t.VectorSpeed <> COALESCE(t.PrevSpeed, -1)
order by t.GpsTime
  

приведенный выше запрос возвращает приведенный ниже результат

 ID  Number  VectorSpeed GpsTime                X    Y
1   A-2         0      8/25/20 12:00 AM 68.5793472  25.3819936
1   A-2         2      8/25/20 7:46 AM  68.5793216  25.3825104
1   A-2         0      8/25/20 7:48 AM  68.5797952  25.3827024
1   A-2         3      8/25/20 7:49 AM  68.5798656  25.382696
1   A-2         5      8/25/20 7:50 AM  68.5806272  25.3826864
1   A-2         7      8/25/20 7:51 AM  68.5812672  25.3820704
1   A-2         8      8/25/20 7:52 AM  68.5821696  25.3824544
1   A-2        15      8/25/20 7:53 AM  68.582176   25.3828352
1   A-2        20      8/25/20 7:55 AM  68.5820672  25.3829328
1   A-2        25      8/25/20 7:56 AM  68.5819072  25.3828976
1   A-2        5       8/25/20 7:59 AM  68.5816192  25.382896
1   A-2        0       8/25/20 8:01 AM  68.5811392  25.382072
1   A-2        2       8/25/20 8:54 AM  68.5815296  25.3825056
1   A-2       10       8/25/20 8:55 AM  68.5815296  25.3825136
1   A-2       15       8/25/20 9:05 AM  68.5815296  25.3825648
1   A-2       20       8/25/20 9:06 AM  68.5815232  25.3825632
1   A-2       25       8/25/20 9:42 AM  68.5812992  25.3828352
1   A-2       5        8/25/20 12:00 PM 68.5778944  25.3954544
1   A-2       2        8/25/20 12:01 PM 68.5778944  25.397928
  

требуемый результат на основе векторной скорости

  ID Number    From              To                X          Y              decs
 1  A-2     8/25/20 12:00 AM    8/25/20 7:50 AM  68.5806272 25.3826864    stoppage 
 1  A-2     8/25/20 7:51 AM     8/25/20 7:56 AM  68.5819072 25.3828976    Movement
 1  A-2     8/25/20 7:59 AM     8/25/20 8:54 AM  68.5815296 25.3825056    stoppage      
 1  A-2     8/25/20 8:55 AM     8/25/20 9:42 AM  68.5812992 25.3828352    Movement
 1  A-2     8/25/20 12:00 PM    8/25/20 12:01 PM 68.5778944 25.397928     stoppage      
  

Работая с вашим текущим набором результатов (вы можете добавить его в общее табличное выражение cte ), используйте LAG() и CASE для определения различных разделов, в которых изменяется движение. Затем используйте OUTER APPLY для определения следующего раздела перемещения и последней записи текущего раздела перед этим.

Пример данных

 create table cteResults
(
  ID int,
  Number nvarchar(3),
  VectorSpeed int,
  GpsTime datetime,
  X decimal(9,7),
  Y decimal(9,7)
);

insert into cteResults (ID, Number, VectorSpeed, GpsTime, X, Y) values
(1, 'A-2',   0, '8/25/20 12:00 AM', 68.5793472 , 25.3819936 ),
(1, 'A-2',   2, '8/25/20 7:46 AM ',  68.5793216,  25.3825104),
(1, 'A-2',   0, '8/25/20 7:48 AM ',  68.5797952,  25.3827024),
(1, 'A-2',   3, '8/25/20 7:49 AM ',  68.5798656,  25.382696),
(1, 'A-2',   5, '8/25/20 7:50 AM ',  68.5806272,  25.3826864),
(1, 'A-2',   7, '8/25/20 7:51 AM ',  68.5812672,  25.3820704),
(1, 'A-2',   8, '8/25/20 7:52 AM ',  68.5821696,  25.3824544),
(1, 'A-2',  15, '8/25/20 7:53 AM ',  68.582176 ,  25.3828352),
(1, 'A-2',  20, '8/25/20 7:55 AM ',  68.5820672,  25.3829328),
(1, 'A-2',  25, '8/25/20 7:56 AM ',  68.5819072,  25.3828976),
(1, 'A-2',  5 , '8/25/20 7:59 AM ',  68.5816192,  25.382896),
(1, 'A-2',  0 , '8/25/20 8:01 AM ',  68.5811392,  25.382072),
(1, 'A-2',  2 , '8/25/20 8:54 AM ',  68.5815296,  25.3825056),
(1, 'A-2', 10 , '8/25/20 8:55 AM ',  68.5815296,  25.3825136),
(1, 'A-2', 15 , '8/25/20 9:05 AM ',  68.5815296,  25.3825648),
(1, 'A-2', 20 , '8/25/20 9:06 AM ',  68.5815232,  25.3825632),
(1, 'A-2', 25 , '8/25/20 9:42 AM ',  68.5812992,  25.3828352),
(1, 'A-2', 5  , '8/25/20 12:00 PM', 68.5778944 , 25.3954544),
(1, 'A-2', 2  , '8/25/20 12:01 PM', 68.5778944 , 25.397928);
  

Решение

Fiddle, который объясняет Section выражение внутри cte .

 with cte as
(
  --existing query goes here
  select cr.*,
         coalesce(case when (case when cr.VectorSpeed > 5 then 1 else 0 end) = lag(case when cr.VectorSpeed > 5 then 1 else 0 end) over(order by cr.GpsTime) then 0 else 1 end, 1) as Section
  from cteResults cr
)
select c1.ID,
       c1.Number,
       c1.GpsTime as [From],
       c3.GpsTime as [To],
       c3.X,
       c3.Y,
       case when c1.VectorSpeed > 5 then 'Moving' else 'Stopped' end as Description
from cte c1
-- next section
outer apply (  select top 1 c2.GpsTime
               from cte c2
               where c2.Section = 1
                 and c2.GpsTime > c1.GpsTime
               order by c2.GpsTime ) c2
-- last entry of current section (comes just before next section that was just retrieved)
outer apply (  select top 1 c3.GpsTime, c3.X, c3.Y
               from cte c3
               where c3.Section = 0
                 and c3.GpsTime > c1.GpsTime
                 and (c2.GpsTime is null or c3.GpsTime < c2.GpsTime)
               order by c3.GpsTime desc ) c3
where c1.Section = 1
order by c1.GpsTime;