计算按天分组的中位数 [英] Count median grouped by day

问题描述

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

I have a script which counts median value for all table data:

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.

Now I need to get median value not for all table values, but grouped by date. Is it possible? http://sqlfiddle.com/#!2/7cf27 - so as result I will get 2013-03-06 - 1.5 , 2013-03-05 - 3.5.

推荐答案

我希望我不要放松自己并使事情变得过于复杂，但这是我想出的:

I hope I didn't loose myself and overcomplicate things, but here's what I came up with:

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，因此我们可以将两个子查询结合起来.

What I did here, is mainly just to reset the rownumber to 1 when the date changes (it's important to order by created_at) and included the date so we can group by it. In the query which calculates total rows I also included created_at, so we can join the two subqueries.

这篇关于计算按天分组的中位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！