按天分组计算中位数

Question

按天分组计算中位数

6

我有一个脚本，可以计算表格数据的中位数：

SELECT avg(t1.price) as median_val FROM (
SELECT @rownum:=@rownum+1 as `row_number`, d.price
  FROM mediana d,  (SELECT @rownum:=0) r
  WHERE 1
  ORDER BY d.price
) as t1, 
(
  SELECT count(*) as total_rows
  FROM mediana d
  WHERE 1
) as t2
AND t1.row_number>=total_rows/2 and t1.row_number<=total_rows/2+1;

现在我需要按日期分组获取中位数，而不是所有表值的中位数。这可能吗？http://sqlfiddle.com/#!2/7cf27 - 所以结果我会得到 2013-03-06 - 1.5 ，2013-03-05 - 3.5。

- Alex

2个回答

1

这里有另一种中位数的方法，灵感来自于这个帖子，使用了SUBSTRING_INDEX和GROUP_CONCAT。相对于使用行号的@fancyPants所描述的方法，在大表上的性能我不太确定，但在较小的表（约20K行）上，它运行非常快。

SET SESSION group_concat_max_len = 1000000;
SELECT
    created_at,
    (
    CAST(
        SUBSTRING_INDEX(
        SUBSTRING_INDEX(
        GROUP_CONCAT(
            price ORDER BY price SEPARATOR ','),
            ',', FLOOR((COUNT(*)+1)/2) ), ',', -1) AS DECIMAL) +
    CAST(
        SUBSTRING_INDEX(
        SUBSTRING_INDEX(
        GROUP_CONCAT(
            price ORDER BY price SEPARATOR ','),
            ',', FLOOR((COUNT(*)+2)/2) ), ',', -1) AS DECIMAL)
    ) / 2.0 AS median_price
FROM
    mediana
GROUP BY
    created_at
;

这是问题中提供的sqlfiddle的输出结果（该网站似乎出现了故障，但我在MySQL中运行了此表格）：

+------------+--------------+
| created_at | median_price |
+------------+--------------+
| 2012-03-05 |       3.5000 |
| 2012-03-06 |       1.5000 |
+------------+--------------+

GROUP_CONCAT本质上创建了一个字符串表示每个created_at日期的价格数组。然后，两个SUBSTRING_INDEX命令查找中间值（即中位数）。有必要调用两次GROUP_CONCAT并对它们取平均值，以处理单个created_at日期的price元素数量为偶数的情况。

更新：

值得一提的是，GROUP_CONCAT函数默认长度为1024字节，请参见这里。这可能会导致非常长的结果被截断，从而导致计算错误。如果您担心结果过大，可以使用命令SET SESSION group_concat_max_len = N;来设置更大的默认值，其中N是其他更大的值。我已将该设置添加到上面的代码片段中。我选择了1000000，但您也可以使用其他值。

你还可以使用COUNT(*)和OFFSET来检查你的结果，其中包括一个GROUP BY值。例如，

首先获取特定GROUP BY值的行数计数，

SELECT COUNT(*) FROM mediana WHERE created_at = '2012-03-06';

令X为从步骤1中获得的行数。将X除以2得到其一半值Y。
使用值Y作为偏移量来查找中位数。

a. 如果Y是一个整数，则执行以下两个操作：

SELECT price FROM mediana WHERE created_at = '2012-03-06' ORDER BY price LIMIT 1 OFFSET (Y-1);

和

SELECT price FROM mediana WHERE created_at = '2012-03-06' ORDER BY price LIMIT 1 OFFSET Y;

并对这两个结果求平均值以获取中位数。

b. 如果Y是一个小数，则将Y向下舍入到最接近的整数（称其为W），并将其用作单个偏移量，

SELECT price FROM mediana WHERE created_at = '2012-03-06' ORDER BY price LIMIT 1 OFFSET W;

这将是您的中位数值。

- wingr

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- fancyPants · Accepted Answer

我希望自己没有迷失并过度复杂化事情，但这是我想出来的：

SELECT sq.created_at, avg(sq.price) as median_val FROM (
SELECT t1.row_number, t1.price, t1.created_at FROM(
SELECT IF(@prev!=d.created_at, @rownum:=1, @rownum:=@rownum+1) as `row_number`, d.price, @prev:=d.created_at AS created_at
FROM mediana d, (SELECT @rownum:=0, @prev:=NULL) r
ORDER BY created_at, price
) as t1 INNER JOIN  
(
  SELECT count(*) as total_rows, created_at 
  FROM mediana d
  GROUP BY created_at
) as t2
ON t1.created_at = t2.created_at
WHERE 1=1
AND t1.row_number>=t2.total_rows/2 and t1.row_number<=t2.total_rows/2+1
)sq
group by sq.created_at

我在这里做的主要是在日期变化时重置行号为1（按created_at排序很重要），并包括日期，以便我们可以按日期分组。在计算总行数的查询中，我还包括了created_at，这样我们可以连接两个子查询。