Let's say I have a table containing some GPS points with their speed attribute (let's ignore position here):
ID TIME SPEED
---------------------------------
1 2024-01-01 09:00:02 0
3 2024-01-01 09:00:03 0
6 2024-01-01 09:00:04 2
8 2024-01-01 09:00:09 11
14 2024-01-01 09:00:10 15
11 2024-01-01 09:00:22 22
12 2024-01-01 09:00:28 4
10 2024-01-01 09:00:32 0
15 2024-01-01 09:00:33 2
16 2024-01-01 09:00:34 8
17 2024-01-01 09:00:35 12
18 2024-01-01 09:00:38 3
I need to uniquely identify the ranges of records for which the speed was < 5, keeping the TIME sorting.
Something like this (I included 2 valid alternatives: RANGE_NO and ALT_RANGE_NO):
ID TIME SPEED RANGE_NO ALT_RANGE_NO
---------------------------------------------------------------
1 2024-01-01 09:00:02 0 1 1
3 2024-01-01 09:00:03 0 1 1
6 2024-01-01 09:00:04 2 1 1
8 2024-01-01 09:00:09 11 (null or 0) 2
14 2024-01-01 09:00:10 15 (null or 0) 2
11 2024-01-01 09:00:22 22 (null or 0) 2
12 2024-01-01 09:00:28 4 2 3
10 2024-01-01 09:00:32 0 2 3
15 2024-01-01 09:00:33 2 2 3
16 2024-01-01 09:00:34 8 (null or 0) 4
17 2024-01-01 09:00:35 12 (null or 0) 4
18 2024-01-01 09:00:38 3 3 5
I'm trying with analytical functions, but I couldn't manage to RANK on such a condition keeping the order by TIME (or a numeric equivalent obtained by RANK() over(order by TIME)).
Sure I can identify the "edge" points using LEAD() and LAG() functions to get when the next or previous record exceeded the speed threshold.
But I can not figure out a suitable partition by condition.
I always find that if such a condition existed it would be the same "RANGE_NO" that I'm looking for.
Can I obtain this classification in a single query or with (a few) subqueries or with clause?
You have a kind of gaps and islands problem, you can create your groupings by counting when the speed changes to more than 5 using window function LAG(), like so:
SELECT ID, TIME, SPEED,
1+ SUM(CASE WHEN minSpeed = prevSpeed THEN 0 ELSE 1 END)
OVER (ORDER BY TIME) ALT_RANGE_NO
FROM (
SELECT ID, TIME, SPEED, minSpeed, Lag(minSpeed, 1, minSpeed) over(ORDER BY TIME) prevSpeed
FROM (
SELECT ID, TIME, SPEED, CASE WHEN SPEED < 5 THEN 1 ELSE 0 END as minSpeed
FROM mytable
)
ORDER BY TIME
)
Results :
ID TIME SPEED ALT_RANGE_NO
1 01-JAN-24 09.00.02.000000 0 1
3 01-JAN-24 09.00.03.000000 0 1
6 01-JAN-24 09.00.04.000000 2 1
8 01-JAN-24 09.00.09.000000 11 2
14 01-JAN-24 09.00.10.000000 15 2
11 01-JAN-24 09.00.22.000000 22 2
12 01-JAN-24 09.00.28.000000 4 3
10 01-JAN-24 09.00.32.000000 0 3
15 01-JAN-24 09.00.33.000000 2 3
16 01-JAN-24 09.00.34.000000 8 4
17 01-JAN-24 09.00.35.000000 12 4
18 01-JAN-24 09.00.38.000000 3 5