| location_id | lat | long | speed |
------------- ------- -------- ---------
101241 0.12 1.1 0.0
------------- ------- -------- ---------
101242 0.12 1.1 0.0
------------- ------- -------- ---------
101243 0.12 1.1 0.0
------------- ------- -------- ---------
101244 1.25 0.74 7.4
------------- ------- -------- ---------
I want to select all locations where speed = 0 and lat && long are same
So from above example answer should be::
| location_id |
--------------
101241
--------------
101242
--------------
101243
--------------
Note:: Speed is constant 0 but lat and long depend on previous rows value
I actually read this as a gaps-and-islands problem, where you want adjacent rows that have the same latitude and longitude, and a speed of 0.
You could approach this with window functions: the difference between row numbers gives you the islands: you can then compute the lenght of each islands, and filter on those lenght is greater than 1 and whose speed is 0:
select *
from (
select t.*, count(*) over(partition by lat, long, speed, rn1 - rn2) cnt
from (
select t.*,
row_number() over(order by location_id) rn1,
row_number() over(partition by lat, long, speed order by location_id) rn2
from mytable t
) t
) t
where speed = 0 and cnt > 1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments