我需要从表格的一列中获取测试成绩的中位数、众数和极差，但我不确定如何操作。当您使用Java连接数据库时，通常会返回一个ResultSet，您可以将其制作成表格或其他形式，但是如何获取特定的数字？是否有SQL命令可获取中位数/众数/极差，还是必须自行计算？如何从表格中提取数字以便能够计算众数/中位数/极差？
谢谢。
谢谢。
3个回答
3
这必须可以在纯SQL中完成。使用 MIN() 和 MAX() 函数很容易实现范围。至于剩下的部分，我不是统计学专家，所以无法凭空判断，但我找到了一个链接可能会有用：http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-chp-13-sect-2.html
13.2 Calculating Descriptive Statistics
13.2.1 Problem
You want to characterize a dataset by computing general descriptive or summary statistics.
13.2.2 Solution
Many common descriptive statistics, such as mean and standard deviation, can be obtained by applying aggregate functions to your data. Others, such as median or mode, can be calculated based on counting queries.
13.2.3 Discussion
Suppose you have a table
testscorecontaining observations representing subject ID, age, sex, and test score:mysql> SELECT subject, age, sex, score FROM testscore ORDER BY subject; +---------+-----+-----+-------+ | subject | age | sex | score | +---------+-----+-----+-------+ | 1 | 5 | M | 5 | | 2 | 5 | M | 4 | | 3 | 5 | F | 6 | | 4 | 5 | F | 7 | | 5 | 6 | M | 8 | | 6 | 6 | M | 9 | | 7 | 6 | F | 4 | | 8 | 6 | F | 6 | | 9 | 7 | M | 8 | | 10 | 7 | M | 6 | | 11 | 7 | F | 9 | | 12 | 7 | F | 7 | | 13 | 8 | M | 9 | | 14 | 8 | M | 6 | | 15 | 8 | F | 7 | | 16 | 8 | F | 10 | | 17 | 9 | M | 9 | | 18 | 9 | M | 7 | | 19 | 9 | F | 10 | | 20 | 9 | F | 9 | +---------+-----+-----+-------+A good first step in analyzing a set of observations is to generate some descriptive statistics that summarize their general characteristics as a whole. Common statistical values of this kind include:
- The number of observations, their sum, and their range (minimum and maximum)
- Measures of central tendency, such as mean, median, and mode
- Measures of variation, such as standard deviation or variance
Aside from the median and mode, all of these can be calculated easily by invoking aggregate functions:
mysql> SELECT COUNT(score) AS n, -> SUM(score) AS sum, -> MIN(score) AS minimum, -> MAX(score) AS maximum, -> AVG(score) AS mean, -> STD(score) AS 'std. dev.' -> FROM testscore; +----+------+---------+---------+--------+-----------+ | n | sum | minimum | maximum | mean | std. dev. | +----+------+---------+---------+--------+-----------+ | 20 | 146 | 4 | 10 | 7.3000 | 1.7916 | +----+------+---------+---------+--------+-----------+The aggregate functions as used in this query count only non-
NULLobservations. If you useNULLto represent missing values,you may want to perform an additional characterization to assess the extent to which values are missing. (See Recipe 13.5.)Variance is not shown in the query, and MySQL has no function for calculating it. However, variance is just the square of the standard deviation, so it's easily computed like this:
STD(score) * STD(score)
STDDEV( )is a synonym forSTD( ).Standard deviation can be used to identify outliers—values that are uncharacteristically far from the mean. For example, to select values that lie more than three standard deviations from the mean, you can do something like this:
SELECT @mean := AVG(score), @std := STD(score) FROM testscore; SELECT score FROM testscore WHERE ABS(score-@mean) > @std * 3;For a set of n values, the standard deviation produced by
STD( )is based on n degrees of freedom. This is equivalent to computing the standard deviation "by hand" as follows (@ssrepresents the sum of squares):mysql> SELECT -> @n := COUNT(score), -> @sum := SUM(score), -> @ss := SUM(score*score) -> FROM testscore; mysql> SELECT @var := ((@n * @ss) - (@sum * @sum)) / (@n * @n); mysql> SELECT SQRT(@var); +------------+ | SQRT(@var) | +------------+ | 1.791647 | +------------+To calculate a standard deviation based on n-1 degrees of freedom instead, do it like this:
mysql> SELECT -> @n := COUNT(score), -> @sum := SUM(score), -> @ss := SUM(score*score) -> FROM testscore; mysql> SELECT @var := ((@n * @ss) - (@sum * @sum)) / (@n * (@n - 1)); mysql> SELECT SQRT(@var); +------------+ | SQRT(@var) | +------------+ | 1.838191 | +------------+Or, more simply, like this:
mysql> SELECT @n := COUNT(score) FROM testscore; mysql> SELECT STD(score)*SQRT(@n/(@n-1)) FROM testscore; +----------------------------+ | STD(score)*SQRT(@n/(@n-1)) | +----------------------------+ | 1.838191 | +----------------------------+MySQL has no built-in function for computing the mode or median of a set of values, but you can compute them yourself. The mode is the value that occurs most frequently. To determine what it is, count each value and see which one is most common:
mysql> SELECT score, COUNT(score) AS count -> FROM testscore GROUP BY score ORDER BY count DESC; +-------+-------+ | score | count | +-------+-------+ | 9 | 5 | | 6 | 4 | | 7 | 4 | | 4 | 2 | | 8 | 2 | | 10 | 2 | | 5 | 1 | +-------+-------+In this case, 9 is the modal score value.
The median of a set of ordered values can be calculated like this:
- If the number of values is odd, the median is the middle value.
- If the number of values is even, the median is the average of the two middle values.
Note that the definition of median given here isn't fully general; it doesn't address what to do if there's duplication of the middle values in the dataset.
Based on that definition, use the following procedure to determine the median of a set of observations stored in the database:
- Issue a query to count the number of observations. From the count, you can determine whether the median calculation requires one or two values, and what their indexes are within the ordered set of observations.
- Issue a query that includes an
ORDER BYclause to sort the observations, and aLIMITclause to pull out the middle value or values.- Take the average of the selected value or values.
For example, if a table t contains a score column with 37 values (an odd number), you need to select a single value, using a query like this:
SELECT score FROM t ORDER BY 1 LIMIT 18,1If the column contains 38 values (an even number), the query becomes:
SELECT score FROM t ORDER BY 1 LIMIT 18,2Then you can select the value or values returned by the query and compute the median from their average.
The following Perl function implements a median calculation. It takes a database handle and the names of the table and column that contain the set of observations, then generates the query that retrieves the relevant values, and returns their average:
sub median { my ($dbh, $tbl_name, $col_name) = @_; my ($count, $limit); $count = $dbh->selectrow_array ("SELECT COUNT($col_name) FROM $tbl_name"); return undef unless $count > 0; if ($count % 2 == 1) # odd number of values; select middle value { $limit = sprintf ("LIMIT %d,1", ($count-1)/2); } else # even number of values; select middle two values { $limit = sprintf ("LIMIT %d,2", $count/2 - 1); } my $sth = $dbh->prepare ( "SELECT $col_name FROM $tbl_name ORDER BY 1 $limit"); $sth->execute ( ); my ($n, $sum) = (0, 0); while (my $ref = $sth->fetchrow_arrayref ( )) { ++$n; $sum += $ref->[0]; } return ($sum / $n); }The preceding technique works for a set of values stored in the database. If you happen to have already fetched an ordered set of values into an array @val, you can compute the median like this instead:
if (@val == 0) # if array is empty, median is undefined { $median = undef; } elsif (@val % 2 == 1) # if array size is odd, median is middle number { $median = $val[(@val-1)/2]; } else # array size is even; median is average { # of two middle numbers $median = ($val[@val/2 - 1] + $val[@val/2]) / 2; }The code works for arrays that have an initial subscript of 0; for languages that used 1-based array indexes, adjust the algorithm accordingly.
- BalusC
2
请在此处放置答案的内容。 - Jonathan
1@JonathanLeaders：完成。 - BalusC
0
我在 SQL Server 中计算修剪平均数/截断平均数的方法如下：
with tempResultSet as (
select Score,
ntile(20) over(order by Score) as n,
count(*) over() as x
from @TestScores
)
select avg(cast(Score as float))
from tempResultSet
where (x >= 20 and n between 2 and 19)
or (x < 20 and n between 2 and x - 1)
这将删除您的数据集中上下10％，并获取“分数”的平均值
- Filip Cornelissen
0
你没有告诉我们你使用的是哪个数据库，所以我假设你想要一个能在标准 SQL 数据库中工作的解决方案。
你可以使用 SQL 来计算范围（使用聚合函数 MIN/MAX）和平均数（使用 AVG）的简单聚合。
标准 SQL 不支持中位数，所以你需要让 SQL 对输出进行排序并浏览条目，直到找到“中间”的那个。
众数不被支持，但请注意，您可以使用 COUNT() 和 GROUP BY 使 SQL 创建频率列表（每个数据点出现的次数），具有最高计数的条目是众数。
- Elemental
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接