Get average value for each row X in SQL

Question

Let's say I have the following table

+----+-------+
| Id | Value |
+----+-------+
|  1 |   2.0 |
|  2 |   8.0 |
|  3 |   3.0 |
|  4 |   9.0 |
|  5 |   1.0 |
|  6 |   4.0 |
|  7 |   2.5 |
|  8 |   6.5 |
+----+-------+

I want to build these values, but since my real table has thousands of values, I was thinking about getting and the average for each row of X. Is there any way for me to do this, i.e. Every 2 or 4 lines as shown below:

2
+-----+------+
| 1-2 |  5.0 |
| 3-4 |  6.0 |
| 5-6 |  2.5 |
| 7-8 |  4.5 |
+-----+------+

4
+-----+------+
| 1-4 |  5.5 |
| 5-8 |  3.5 |
+-----+------+

Also, is there a way to make this X value dynamic based on the total number of rows in my table? Something like if I have 1000 lines, the average will be calculated based on every 200 lines (1000/5), but if I have 20, calculate it based on every 4 lines (20/5).

I know how to do this programmatically, but is there a way to do this using pure SQL?

EDIT: I need it to work with mysql.

Answer 1

, - :

SELECT
   ChunkStart = Min(Id),
   ChunkEnd = Max(Id),
   Value = Avg(Value)
FROM
   (
      SELECT
         Chunk = NTILE(5) OVER (ORDER BY Id),
         *
      FROM
         YourTable
   ) AS T
GROUP BY
   Chunk
ORDER BY 
   ChunkStart;

5 , .

, :

SELECT
   ChunkStart = Min(Id),
   ChunkEnd = Max(Id),
   Value = Avg(Value)
FROM
   YourTable
GROUP BY
   (Id - 1) / (((SELECT Count(*) FROM YourTable) + 4) / 5)
;

, Id, 1, , , , , . , Ms SQL Server.

Answer 2

modulos N- . 10- :

select avg(Value) from some_table where id % 10 = 0;

, :

select avg(Value) from some_table where id % (select round(count(*)/1000) from some_table) = 0;

, , , .

EDIT: , , N- , N . , , .

Answer 3

NTILE ( , , , ). - , .

You can then use AVG to calculate the average for each bucket.

NTILE is in SQL-99, so most DBMSs should have one.