- android - RelativeLayout 背景可绘制重叠内容
- android - 如何链接 cpufeatures lib 以获取 native android 库?
- java - OnItemClickListener 不起作用,但 OnLongItemClickListener 在自定义 ListView 中起作用
- java - Android 文件转字符串
我必须从表格中的一列中获取测试分数的中位数、众数和范围,但我不确定如何去做。当您使用 java 连接到数据库时,您通常会返回一个 ResultSet,您可以用它来制作表格或其他东西,但是您如何获得特定的数字或数字?是否有用于获取中值/众数/范围的 SQL 命令,或者我必须自己计算吗?如何从表中提取数字以便能够计算众数/中值/范围?
谢谢。
最佳答案
这必须在纯 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
testscore
containing 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-
NULL
observations. If you useNULL
to 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 (@ss
represents 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 BY
clause to sort the observations, and aLIMIT
clause 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,1
If the column contains 38 values (an even number), the query becomes:
SELECT score FROM t ORDER BY 1 LIMIT 18,2
Then 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.
关于java - 如何使用 Java 在 SQL 中获取列的中位数/众数/范围?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/2616168/
这个问题已经有答案了: Create a variable capturing the most frequent occurence by group (3 个回答) How to find the
我有一个类似这样的RDD color category green a green b red a orange a green b red d green c red d
我有一个数字数组。计算数据集的中位数、众数和标准差的最简单方法是什么? 最佳答案 Statistics::Basic::Mean Statistics::Basic::Median Statistic
我正在尝试获取数组中最常出现的数字,因此对于包含 1,2,10,5,1 的数组,结果应该是 1。我编写的代码返回的频率为每个数字,所以 1 出现两次,2 出现一次,10 出现一次等等。有什么建议可以解
我有一个基于概率的简单游戏,每天我们抛硬币,如果我们正面朝上,我们就赢,我们得到 20 美元,如果我们抛硬币,我们反面朝上,那么我们输掉 19 美元,最后每月(28 天)的一天,我们会看到我们损失了多
我正在尝试计算表中一系列 idsofInterest 的众数,每个 idsofInterest 都有一个附带的 valueOfInterest,例如: idsOfInterest | valueOfI
我是 jupyter notebook 的新手,想知道如何在函数内获取列的分位数: 数据框: num_likes | num_post | ... | 464.0 | 142.0 | .
是否有一种算法可以估计一组值的中值、众数、偏度和/或峰度,但不需要一次将所有值存储在内存中? 我想计算基本统计数据: 平均值:算术平均值 方差:与均值的平方偏差的平均值 标准差:方差的平方根 中位数:
我必须从表格中的一列中获取测试分数的中位数、众数和范围,但我不确定如何去做。当您使用 java 连接到数据库时,您通常会返回一个 ResultSet,您可以用它来制作表格或其他东西,但是您如何获得特定
我的任务是询问用户 x 的最小值和最大值。然后我使用极端值计算这些极端值之间的 20 个值。我必须在表格上显示 x 值及其 f(x) 值。然后我必须找到有关 f(x) 值的统计数据,例如最小值和最大值
我是一名优秀的程序员,十分优秀!