如何使用1个MySQL查询返回所有结果的数字箱线图数据? [英] How can I return the numerical boxplot data of all results using 1 mySQL query?

问题描述

[tbl_votes]
- id <!-- unique id of the vote) -->
- item_id <!-- vote belongs to item <id> -->
- vote <!-- number 1-10 -->

我们当然可以通过以下方法解决此问题:

Of course we can fix this by getting:

• smallest observation(如此)
• lower quartile(lq)
• median(我)
• upper quartile(uq)
• 和largest observation(lo)

• the smallest observation (so)
• the lower quartile (lq)
• the median (me)
• the upper quartile (uq)
• and the largest observation (lo)

..一对一地使用多个查询，但我想知道是否可以通过单个查询来完成.

..one-by-one using multiple queries but I am wondering if it can be done with a single query.

在Oracle中，我可以使用COUNT OVER和RATIO_TO_REPORT，但这在mySQL中不受支持.

In Oracle I can use COUNT OVER and RATIO_TO_REPORT, but this is not supported in mySQL.

对于那些不知道箱线图是什么的人: http://en.wikipedia.org /wiki/Box_plot

For those who don't know what a boxplot is: http://en.wikipedia.org/wiki/Box_plot

任何帮助将不胜感激.

Any help would be appreciated.

推荐答案

以下是计算e32组中e256值范围的四分位数的示例，在这种情况下，(e32，e256)上的索引为必须的:

Here is an example of calculation of the quartiles for e256 value ranges within e32 groups, an index on (e32, e256) in this case is a must:

SELECT
  @group:=IF(e32=@group, e32, GREATEST(@index:=-1, e32)) as e32_,
  MIN(e256) as so,
  MAX(IF(lq_i=(@index:=@index+1), e256, NULL)) as lq,
  MAX(IF(me_i=@index, e256, NULL)) as me,
  MAX(IF(uq_i=@index, e256, NULL)) as uq,
  MAX(e256) as lo
FROM (SELECT @index:=NULL, @group:=NULL) as init, test t
JOIN (
  SELECT e32,
    COUNT(*) as cnt,
    (COUNT(*) div 4) as lq_i,    -- lq value index within the group
    (COUNT(*) div 2) as me_i,    -- me value index within the group
    (COUNT(*) * 3 div 4) as uq_i -- uq value index within the group
  FROM test
  GROUP BY e32
) as cnts
USING (e32)
GROUP BY e32;

如果不需要分组，则查询会稍微简单一些.

If there is no need in groupings, the query will be slightly simplier.

P.S. test是我的随机值操场表，其中e32是Python的int(random.expovariate(1.0) * 32)等的结果.

P.S. test is my playground table of random values where e32 is the result of Python's int(random.expovariate(1.0) * 32), etc.

这篇关于如何使用1个MySQL查询返回所有结果的数字箱线图数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！