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:

enter image description here

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?

CodePudding user response：

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

CodePudding user response：

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