MySQL如何将平均值取到特定范围内

本文介绍了MySQL如何将平均值取到特定范围内的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下表格数据:

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.