根据您的DBMS,这样的东西将起作用:
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开始并且没有间隙,并且如果事情没有均匀划分,你会希望最后一组太小而不是太大.我还假设整数除法将导致SQL Server中的问题.