Search code examples
sqloraclewindow-functionsgaps-and-islands

How to sumarize rows in Oracle?

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;
Solution

  • 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.