获取SQL中每X行的平均值 [英] Get Average value for each X rows in SQL
问题描述
我们说我有下表
+----+-------+
| 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行,如下所示:
I want to plot these values, but since my real table has thousands of values, I thought about getting and average for each X rows. Is there any way for me to do so for, ie, each 2 or 4 rows, like 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 |
+-----+------+
还可以根据表中的总行数来使此X值动态化吗?例如,如果我有1000行,则将基于每200行(1000/5)计算平均值,但是如果我有20行,则应基于每4行(20/5)计算平均值.
Also, is there any way to make this X value dynamic, based on the total number of rows in my table? Something like, if I have 1000 rows, the average will be calculated based on each 200 rows (1000/5), but if I have 20, calculate it based on each 4 rows (20/5).
我知道如何以编程方式执行此操作,但是有什么方法可以使用纯SQL做到这一点?
I know how to do that programmatically, but is there any way to do so using pure SQL?
我需要它在mysql上工作.
I need it to work on mysql.
推荐答案
根据您的DBMS,类似的方法将起作用:
Depending on your DBMS, something like this will work:
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个组或块.
This creates 5 groups or chunks no matter how many rows there are, as you requested.
如果您没有窗口功能,则可以伪造它:
If you have no windowing functions you can fake it:
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女士中.
I made some assumptions here such as Id
beginning with 1 and there being no gaps, and that you would want the last group too small instead of too big if things didn't divide evenly. I also assumed integer division would result as in Ms SQL Server.
这篇关于获取SQL中每X行的平均值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!