Since you have not disclosed the RDBMS you intend to use (by the time of writing), I feel authorized to post a PostgreSQL-specific solution:
WITH rownums AS (
SELECT *, row_number() OVER (ORDER BY col1) AS rownum
averages AS (
SELECT avg(col2) OVER (ORDER BY rownum ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS average, rownum
WHERE rownum % 10 = 0
In the first CTE (called
rownums) I simply added row numbers according to ordering by the timestamp. This was necessary for being able to find every 10th row later. (If there were an additional unique key on this table, this yould be unnecessary.)
averages CTE I compute a moving average on every row. The window is set so that the current row and nine preceding give the numbers to be averaged.
Lastly, I just simply return every 10th row.
row_number andavg` are window functions here. MySQL lacks this functionality, and, as far as I know, H2 too. So porting the abov query to these will involve a lot more trickery, I think.
Try it at SQLFiddle.