1

I have a question that is more like a way to find a idea to how to solve my problem.

So the problem:

Every 2 minutes I receive a new line at my table with various information. Are they:

ID_VEIC - Vehicle ID
DT_POSI - Position date
LAT - latitude
LONGI - longitude
SPEED - car speed when positioning
GMT - Vehicle gmt.

Several of these positions come with speed equal to zero, indicating, in my case, that the vehicle is stopped. If there are several positions of the vehicle stopped (speed = 0) I have to bring in the select only the last position of the stop between two movement positions.

Explaining with images:

Table img

In the image, two lines are selected where the speed is 0. I need to somehow show only the last line of that period, in the case of line 11 and still bring all the other lines where the speed is > 0.

In more detail here:

Current resultset:

ROWNUM  ID_VEIC    DAT_POSI              LAT            LONGI   SPEED        
1   1211678   06/08/2020 06:08  -254.454.135       -544.047.225   15    
2   1211678   06/08/2020 06:38  -25.445.364        -544.047.383   20    
3   1211678   06/08/2020 07:08  -25.445.401        -54.404.762     0    
4   1211678   06/08/2020 07:38  -254.454.135       -544.046.878    0    
5   1211678   06/08/2020 08:08  -254.454.255       -544.046.828   10    
6   1211678   06/08/2020 08:38  -254.453.996       -54.404.707    25    
7   1211678   06/08/2020 09:08  -25.445.428        -544.047.445   45    
8   1211678   06/08/2020 09:38  -254.454.583       -544.048.415    0

Desired resultset

 ROWNUM ID_VEIC    DAT_POSI              LAT            LONGI   SPEED        
    1   1211678   06/08/2020 06:08  -254.454.135       -544.047.225   15    
    2   1211678   06/08/2020 06:38  -25.445.364        -544.047.383   20    
    3   1211678   06/08/2020 07:38  -254.454.135       -544.046.878    0    
    4   1211678   06/08/2020 08:08  -254.454.255       -544.046.828   10    
    5   1211678   06/08/2020 08:38  -254.453.996       -54.404.707    25    
    6   1211678   06/08/2020 09:08  -25.445.428        -544.047.445   45    
    7   1211678   06/08/2020 09:38  -254.454.583       -544.048.415    0

Line 3 has been removed and only the line that was previously line 4 is shown.

Any idea how to do this?

Below the select used:

SELECT  ID_VEIC,
        DAT_POSI,
        LAT,
        LONGI, 
        SPEED,
        GMT
  FROM LITERAL_VIEW   
 WHERE  ID_VEIC= 1211678
   AND DAT_POSI BETWEEN SYSDATE - INTERVAL '2' HOUR AND SYSDATE
   AND ROWNUM <= 999
 ORDER BY ID_VEIC, 
          DAT_POSI;
Ricardo Costardi
  • 19
  • 5
  • 2
    Please edit your question to show your raw data and expected results as formatted text (not images). Sounds like a gaps-and-islands problem but not sure. Why are lat/long changing while speed is zero, and does that matter? Also, filtering by `rownum` *before* ordering gives you an indeterminate set of rows back. – Alex Poole Aug 05 '20 at 17:28
  • Agreed. Use `rownum` very carefully when you have open `order by` in your query. – Sujitmohanty30 Aug 05 '20 at 17:36
  • The lat / long is changing, despite the speed being zero, as I receive a positioning of the vehicle every 2 minutes. In that time the car may have moved a little and stopped again, setting zero speed in a different position, in that interval. Thanks for the rownum information, and also the formatting. – Ricardo Costardi Aug 06 '20 at 13:22

1 Answers1

2

You can use window functions to filter out rows where speed is 0 and whose "next" row has a 0 speed too:

select id_veic, dat_posi, lat, longi, speed, gmt
from (
    select l.*, lead(speed) over(partition by id_veic order by date_posi) lead_speed
    from literal_view l
) t
where not (speed = 0 and lead_speed = 0)

You can easily modify the query to filter on a given date range and/or vehicle, by adding a where clause to the subquery.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    add default value of more than 0 for `lead_speed` when there is no more next row, like so `lead(speed,1,1)` so that if the last rows are 0 speed the filter will still work for them. – Junjie Aug 06 '20 at 07:19
  • Thanks for the answer, it's working! Just a small detail, if all positions have zero speed, when I run this query, no position appears to me. There is some way to show the last position? I put the 'lead (speed, 1,1)' but still nothing comes. – Ricardo Costardi Aug 06 '20 at 14:24
  • 1
    If all positions have 0 speed, `lead(speed,1,1) over(partition by id_veic order by dat_posi) lead_speed` worked for me and only the last row is displayed. – Junjie Aug 07 '20 at 00:42