SQL Get max value of n next rows

user_17658249 0
Viewed 74

Say I have a table with two columns: the time and the value. I want to be able to get a table with : for each time get the max values of every next n seconds.

If I want the max value of every next 3 seconds, the following table:

time value
1 6
2 1
3 4
4 2
5 5
6 1
7 1
8 3
9 7

Should return:

time value max
1 6 6
2 1 4
3 4 5
4 2 5
5 5 5
6 1 3
7 1 7
8 3 NULL
9 7 NULL

Is there a way to do this directly with an sql query?

tsql sql
2 Answers

You can use the max window function:

select *,
case 
when row_number() over(order by time desc) > 2 then
max(value) over(order by time rows between current row and 2 following)
end as max
from table_name;

Fiddle

The case expression checks that there are more than 2 rows after the current row to calculate the max, otherwise null is returned (for the last 2 rows ordered by time).

user_176450990

Similar Version to Zakaria, but this solution uses about 40% less CPU resources (scaled to 3M rows for benchmark) as the window functions both use the same exact OVER clause so SQL can better optimize the query.

Optimized Max Value of Rolling Window of 3 Rows

SELECT *,
    MaxValueIn3SecondWindow =   CASE 
                                    /*Check 3 rows exists to compare. If 3 rows exists, then calculate max value*/
                                    WHEN 3 =   COUNT(*) OVER (ORDER BY [Time] ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
                                    /*Returns max [Value] between the current row and the next 2 rows*/
                                    THEN MAX(A.[Value]) OVER (ORDER BY [Time] ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)
                                END
FROM #YourTable AS A
user_46419280
Related