How to select MAX/MIN in MySQL with dynamic between clause-CodePudding

Assume a table with two columns t (a string with TimeStamps) and v (decimal). For each t I want to query the MAXIMUM of the value v in a certain range defined by the current t.

How can i transfer below statement to proper SQL?

select t, max(v for t between t-2MIN and t 2min) from table_name;

Example:

Assume below table.

t	v
1	3
2	2
3	5
4	4
5	8
6	1

I need an SQL-statement which gives me (for e.g. a width 2: max(v for t between t-2 and t 2)) the following result

t	v
1	5
2	5
3	8
4	8
5	8
6	8

CodePudding user response：

Join the table with itself using the range as the joining condition.

SELECT t1.t, MAX(t2.v) AS max_v
FROM table_name AS t1
JOIN table_name AS t2 ON t2.t BETWEEN t1.t - 2 AND t1.t   2
GROUP BY t1.t

If you use MySQL 8.x I think you should be able to do it using window functions, but I don't know the proper syntax for this.

CodePudding user response：

In MySql 8 you can use a MAX OVER with rows between a range.

select t
, max(v) over (order by t rows 
               between 2 preceding and 2 following) v
from table_name