在MySQL中将MEDIAN与MAX，MIN和AVG一起使用 [英] Using MEDIAN along side MAX, MIN, and AVG functions in MySQL

问题描述

我有以下运行正常的MySQL查询:

I have the following MySQL query which is working perfectly:

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下，在理想世界中看起来像这样:

Now I wish to also add a result column that gives me the MEDIAN of the data for each line. Under SELECT this would look like this in a perfect world:

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

在Google上搜索MySQL中位数函数使我得到了这个答案，如果您对整个表的数据集的中位数感兴趣，这似乎没问题:

Searching Google for a MySQL median function brought me to this answer, which seems ok if you are interested in the median of a data set for an entire table: Simple way to calculate median with MySQL

这里的区别是我要按name列对表中的数据进行分组，并希望获得按此列分组的数据的每一行的中位数.

The difference here is that I am grouping the data in my table by the name column, and want to get the median for each line of the data grouped by this column.

有人知道让我这样做的妙招吗?

Does anyone know a nifty trick to allow me to do this?

谢谢！

推荐答案

我发现做到这一点的唯一方法是通过字符串操作:
使用GROUP_CONCAT创建所有值的列表，然后使用缩进的SUBSTRING_INDEX获取中间值

The only way I found to do this is through string manipulation:
with GROUP_CONCAT a list of all value is created then with indented SUBSTRING_INDEX the median value is taken

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`
  , CASE (count(*) % 2)
    WHEN 1 THEN SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        group_concat((QNTY_Sell/QNTYDelivered)*1000 
                      ORDER BY (QNTY_Sell/QNTYDelivered)*1000 SEPARATOR ',')
      , ',', (count(*) + 1) / 2)
    , ',', -1)
    ELSE (SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        group_concat((QNTY_Sell/QNTYDelivered)*1000 
                      ORDER BY (QNTY_Sell/QNTYDelivered)*1000 SEPARATOR ',')
      , ',', count(*) / 2)
    , ',', -1)
  + SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        group_concat((QNTY_Sell/QNTYDelivered)*1000 
                      ORDER BY (QNTY_Sell/QNTYDelivered)*1000 SEPARATOR ',')
      , ',', (count(*) + 1) / 2)
    , ',', -1)) / 2
    END median
FROM
    sales
WHERE
    year(date) >= 2012 AND
    name LIKE "%art.%" AND
    QNTYDelivered > 0 AND
    QNTY_Sell > 0
GROUP BY name
ORDER BY name;  

需要用CASE来检查我们是否有一个中位数，具有奇数个值，或者两个中位数，具有偶数个值，在第二种情况下，中位数是所建立的两个值的平均值

The CASE is needed to check if we have a single median value, with an odd number of values, or two median values, with an even number of values, in the second case the median is the mean of the two values founded.

SQLFiddle

这篇关于在MySQL中将MEDIAN与MAX，MIN和AVG一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！