logo标签列表

使用MySQL计算中位数

mysqlstatisticsmedian
7

我在计算一组数值的中位数时遇到了麻烦，而不是平均值。

我找到了这篇文章 用MySQL计算中位数的简单方法

它引用了下面的查询语句，但我并没有完全理解。

SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2

如果我有一个time列，想要计算中位数，那么x和y列是指什么？

请注意，您提到的解决方案在存在重复值时将无法找到中位数。（当中位数本身有重复项时，它会失败） - Mark Biesheuvel
3
MySQL已经存在数十年，被数百万人使用，但我不理解为什么它没有计算中位数的函数。还有其他数据中心系统没有实现通常在小学四年级教授的简单数学运算吗？请问有吗？ - Monica Heddneck
7个回答
12

我提议一种更快的方法。

获取行数：

SELECT CEIL(COUNT(*)/2) FROM data;

然后在排序子查询中取中间值：

SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x;

我使用一个5x10e6的随机数字数据集进行了测试，它可以在不到10秒的时间内找到中位数。

通过将COUNT(*)/2替换为COUNT(*)*n，其中n是百分位数（中位数为.5，第75个百分位数为.75等），这将找到任意百分位数。

4
好的解决办法，但如果项目数量是奇数，您应该取其中间两个点的平均值SELECT avg(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue, @numvalues) x;，其中@numvalues为(@middlevalue mod 2) +1。 - Ella Ryan
2
"val"是您的时间列，"x"和"y"是对数据表的两个引用（您可以编写"data AS x, data AS y"）。
编辑： 为了避免两次计算总和，您可以存储中间结果。
CREATE TEMPORARY TABLE average_user_total_time 
      (SELECT SUM(time) AS time_taken 
            FROM scores 
            WHERE created_at >= '2010-10-10' 
                    and created_at <= '2010-11-11' 
            GROUP BY user_id);

然后，您可以计算命名表中这些值的中位数。
编辑：在此处临时表 不起作用。您可以尝试使用具有“MEMORY”表类型的常规表。或者只需在查询中两次使用计算中位数值的子查询。除此之外，我没有看到其他解决方案。这并不意味着没有更好的方法，也许其他人会想出一个主意。
谢谢 @Krab！不知道你能否帮我解决以下问题：SELECT AVG(time_taken) FROM ( SELECT SUM(time) AS time_taken FROM scores WHERE created_at >= '2010-10-10' and created_at <= '2010-11-11' GROUP BY user_id) AS average_user_total_time" )用于计算用户分数的总平均值，但不确定如何将中位数公式应用于此查询。对于重新发布帖子超时的情况非常抱歉。 - Tim
当我尝试这样做时，我得到了“无法重新打开表x”的错误。以下是我的完整SQL代码。CREATE TEMPORARY TABLE average_user_total_time (SELECT SUM(time) AS time_taken FROM scores WHERE created_at >= '2010-10-10' and created_at <= '2010-11-11' GROUP BY user_id);SELECT x.time_taken from average_user_total_time as x, average_user_total_time as y GROUP BY x.time_taken HAVING SUM(SIGN(1-SIGN(y.time_taken-x.time_taken))) = (COUNT(*)+1)/2 - Tim
1
在mysql中使用group_concat查找中位数
查询：
SELECT
    IF(count%2=1,
       SUBSTRING_INDEX(substring_index(data_str,",",pos),",",-1),
       (SUBSTRING_INDEX(substring_index(data_str,",",pos),",",-1) 
         + SUBSTRING_INDEX(substring_index(data_str,",",pos+1),",",-1))/2) 
    as median 
FROM (SELECT group_concat(val order by val) data_str,
      CEILING(count(*)/2) pos,
      count(*) as count from data)temp;

解释：

排序是通过 group_concat 函数内的 order by 完成的。

确定位置（pos）和元素总数（count）。使用 CEILING 确定位置可以帮助我们在下面的步骤中使用 substring_index 函数。

根据 count，决定值的数量是偶数还是奇数。

  • 奇数值：直接选择属于 pos 的元素，使用 substring_index 函数。
  • 偶数值：找到属于 pos 和 pos+1 的元素，然后将它们相加并除以 2 来得到中位数。

最后计算中位数。

1
如果您有一个名为A的列的表格R，并且您想要获得A的中位数，则可以按照以下方式进行操作：
SELECT A FROM R R1
WHERE ( SELECT COUNT(A) FROM R R2 WHERE R2.A < R1.A ) = ( SELECT COUNT(A) FROM R R3 WHERE R3.A > R1.A )

注意：只有在A中没有重复值时才能正常工作。此外，不允许使用空值。
当列中有偶数行时，这个查询语句如何工作？因为传统逻辑是对于偶数，我们需要返回中间两个数字的平均值。但是上述查询语句没有处理这种情况。如果我错了，请纠正我。 - Amitrajit Bose
1
第一步先了解中位数是什么：它是在排序后的值列表中的中间值。
一旦你理解了这个概念，方法就可以分为两步：
1. 以任意顺序对值进行排序 2. 取中间值（如果值的数量为偶数，则取两个中间值的平均值）
例如：
Median of 0 1 3 7 9 10: 5 (because (7+3)/2=5)
Median of 0 1 3 7 9 10 11: 7 (because 7 is the middle value)

因此，要对日期进行排序，您需要一个数字值；您可以获取它们的时间戳（作为从纪元开始经过的秒数），并使用中位数的定义。

1
不同意你的第一个例子：中位数始终是集合中的实际成员。 - zanlok
5
任何“广为接受”的软件包都会计算我所展示的中位数（如果值的数量为偶数，则为平均值）。Matlab和R都会取平均值。你所说的是“中心点”，其中值始终是数据集的成员。 - Escualo
1

我和我的朋友找到的最简单方法......尽情享受！

SELECT count(*) INTO @c from station;
select ROUND((@c+1)/2) into @final; 
SELECT round(lat_n,4) from station a where @final-1=(select count(lat_n) from station b where b.lat_n > a.lat_n);
0

这里有一个易于理解的解决方案。只需根据您的要求替换 Your_Column 和 Your_Table 即可。

SET @r = 0;

SELECT AVG(Your_Column)
FROM (SELECT (@r := @r + 1) AS r, Your_Column FROM Your_Table ORDER BY Your_Column) Temp
WHERE
    r = (SELECT CEIL(COUNT(*) / 2) FROM Your_Table) OR
    r = (SELECT FLOOR((COUNT(*) / 2) + 1) FROM Your_Table)

最初采用自此线程。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接