mysql - 在 MySQL 中使用 MEDIAN 以及 MAX、MIN 和 AVG 函数

Question

我有以下运行良好的 MySQL 查询:

select 
    count(*) as `# of Data points`, 
    name, 
    max((QNTY_Sell/QNTYDelivered)*1000) as `MAX Thousand Price`,
    min((QNTY_Sell/QNTYDelivered)*1000) as `MIN Thousand Price`,
    avg((QNTY_Sell/QNTYDelivered)*1000) as `MEAN Thousand Price` 
from 
    table_name 
where 
    year(date) >= 2012 and 
    name like "%the_name%" and 
    QNTYDelivered > 0 and 
    QNTY_Sell > 0 
group by name 
order by name;

现在我还想添加一个结果列，为我提供每行数据的中位数。在 SELECT 下，这在完美世界中看起来像这样:

median((QNTY_Sell/QNTYDelivered)*1000) as `MEDIAN Thousand Price`

在谷歌上搜索 MySQL 中值函数让我得到了这个答案，如果您对整个表的数据集的中值感兴趣，这似乎没问题:Simple way to calculate median with MySQL

此处的不同之处在于，我按 name 列对表中的数据进行分组，并希望获得按该列分组的每一行数据的中位数。

有谁知道允许我这样做的妙招吗？

谢谢!

Answer 1

即使没有内置的中值函数，您也可以在 MySQL 中使用 GROUP BY 计算中值。

考虑表格:

Acrington   200.00
Acrington   200.00
Acrington   300.00
Acrington   400.00
Bulingdon   200.00
Bulingdon   300.00
Bulingdon   400.00
Bulingdon   500.00
Cardington  100.00
Cardington  149.00
Cardington  151.00
Cardington  300.00
Cardington  300.00

对于每一行，您可以计算较少的相似项目的数量。您还可以计算有多少值小于或等于:

name        v       <   <=
Acrington   200.00  0   2
Acrington   200.00  0   2
Acrington   300.00  2   3
Acrington   400.00  3   4
Bulingdon   200.00  0   1
Bulingdon   300.00  1   2
Bulingdon   400.00  2   3
Bulingdon   500.00  3   4
Cardington  100.00  0   1
Cardington  149.00  1   2
Cardington  151.00  2   3
Cardington  300.00  3   5
Cardington  300.00  3   5

带查询

SELECT name,v, (SELECT COUNT(1) FROM sale WHERE v<o.v AND name=o.name) as ls
             , (SELECT COUNT(1) FROM sale WHERE v<=o.v AND name=o.name) as lse
  FROM sale o

当小于或等于项数的一半时，将出现中值

• Acrington 有 4 个项目。其中一半是 2，在 0..2 范围内(对应 200.00)，也在 2..3 范围内(对应 300.00)

• Bullingdon 也有 4 项。 2 在 1..2(值 300.00)和 2..3(值 400.00)范围内

• Cardington 有 5 个项目。值 2.5 介于 2 和 3 之间，对应 Cardington 151。

中值是返回的最小值和最大值的平均值:

SELECT cs.name,v
   FROM
   (SELECT name,v, (SELECT COUNT(1) FROM sale WHERE v<o.v AND name=o.name) as ls
                 , (SELECT COUNT(1) FROM sale WHERE v<=o.v AND name=o.name) as lse
      FROM sale o) cs JOIN
   (SELECT name,COUNT(1)*.5 as cn
      FROM sale
      GROUP BY name) cc ON cs.name=cc.name
 WHERE cn between ls and lse

给出:

Acrington   200.00
Acrington   200.00
Acrington   300.00
Bulingdon   300.00
Bulingdon   400.00
Cardington  151.00

最后我们可以得到中位数:

SELECT name,(MAX(v)+MIN(v))/2 FROM
(SELECT cs.name,v
   FROM
   (SELECT name,v, (SELECT COUNT(1) FROM sale WHERE v<o.v AND name=o.name) as ls
                 , (SELECT COUNT(1) FROM sale WHERE v<=o.v AND name=o.name) as lse
      FROM sale o) cs JOIN
   (SELECT name,COUNT(1)*.5 as cn
      FROM sale
     GROUP BY name) cc ON cs.name=cc.name
 WHERE cn between ls and lse
 ) AS medians
GROUP BY name

给予

Acrington   250.000000
Bulingdon   350.000000
Cardington  151.000000