MySQL如何将平均值取到特定范围内 [英] MySQL how to get avg of values into specific range
问题描述
我有以下表格数据:
value
1
5
10.5
12
36
我想将这些值映射到:
range avg
0-21 (1 + 5 + 10.5 + 12) / 4
21.001-34 0
34.001-64 36
64 + 0
基本上将每个值映射到范围,并计算每个范围内所有值的平均值.
Basically map each value to ranges and calculate the avg of all values in each range.
我试图做:
select
case
when avggeneral between 0 and 21 then ' 0-21'
when avggeneral between 21.00001 and 34 then ' 21-34'
when avggeneral between 34.00001 and 64 then ' 34-64'
else '64+'
end as 'range',
AVG(*) as 'avg'
from table
但这不起作用...我不知道如何制作静态范围...
but this doesn't work... I don't know how to make the static ranges...
如何在MySQL中做到这一点?
How can I do that in MySQL?
方法应为: 1.将值映射到这些组中(0-21、21-34等.) 2.计算每组的AVG.
Methology should be: 1. Map values into these groups ( 0-21, 21-34 etc..) 2. calulate AVG in each group.
上面示例的期望输出是这样的:
Desired output for above example is this:
range avg
0-21 7.125
21.001-34 0
34.001-64 36
64 + 0
range
列是静态的.始终带有5行.
avg
列是动态的.值是value
列的实际AVG.
The range
column is static. Always with 5 rows.
The avg
column is dynamic.. the values there are the actual AVGs of value
column.
推荐答案
您可以使用UNION ALL
和LEFT JOIN
与其建立范围列表:
You could build a list of ranges using UNION ALL
and LEFT JOIN
with it:
SELECT CONCAT(IFNULL(ranges.min, '∞'), '-', IFNULL(ranges.max, '∞')) AS `range`, avg(value) AS avg
FROM (
SELECT 0 AS min, 21 AS max UNION ALL
SELECT 21, 34 UNION ALL
SELECT 34, 64 UNION ALL
SELECT 64, NULL
) AS ranges
LEFT JOIN t ON (ranges.min IS NULL OR value >= ranges.min) AND
(ranges.max IS NULL OR value < ranges.max)
GROUP BY ranges.min, ranges.max
请注意,上述查询会将20.9999
放在[0-21)
范围内,而21.0000
放在[21-34)
范围内.
Note that the above query will put 20.9999
inside [0-21)
and 21.0000
inside [21-34)
range.
这篇关于MySQL如何将平均值取到特定范围内的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!