mysql 部分行 平均值_MySQL如何获取平均值的特定范围内的值

I have the following table data:

value

1

5

10.5

12

36

I want to map these values into:

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.

I tried to do:

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

How can I do that in MySQL?

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

The range column is static. Always with 5 rows.

The avg column is dynamic.. the values there are the actual AVGs of value column.

解决方案

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

Note that the above query will put 20.9999 inside [0-21) and 21.0000 inside [21-34) range.