获取SQL中每个X行的平均值

Question

假设我有下表

+----+-------+
| 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 |
+----+-------+

我想要绘制这些值，但是由于我的真正的表有数千个值，所以我考虑了每一个X行的获取和平均值。我是否有办法这样做，即每2或4行，如下所示：

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 |
+-----+------+

另外，是否有任何方法根据我的表中的行总数使这个X值动态化？例如，如果我有1000行，则将根据每200行(1000/5)计算平均值，但如果我有20行，则根据每4行(20/5)计算它。

我知道如何以编程方式这样做，但是是否有任何方法可以使用纯SQL来做到这一点呢？

编辑:我需要它在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)
;

我在这里做了一些假设，比如从1开始的Id，没有空隙，如果事情分配不均匀的话，你会希望最后一组太小而不是太大。我还假设整数除法的结果与一样。

Answer 2

您可以使用modulos操作符对表的每一行进行操作。这个例子将得到每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;

当然，您需要根据表中的实际行数确定最佳间隔。

编辑:重新阅读你的帖子，我意识到这是一个平均每第N行，而不是每一个连续N行。我不确定这是否足够，或者你是否特别需要顺序平均值。

Answer 3

查看NTILE函数(如四分位数、五分位数、十分之一、百分位数)。您可以使用它将数据均匀地分成多个桶--在您的示例中，您似乎希望使用五个桶。

然后，您可以使用AVG来计算每个桶的平均值。

NTILE在SQL99中，所以大多数DBMSes都应该有它。