Recipe 13.1. Calculating Descriptive Statistics


Problem

You want to characterize a dataset by computing general descriptive or summary statistics.

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.

Discussion

Suppose that 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 and 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,     -> STDDEV_SAMP(score) AS 'std. dev.',     -> VAR_SAMP(score) AS 'variance'     -> FROM testscore; +----+------+---------+---------+--------+-----------+----------+ | n  | sum  | minimum | maximum | mean   | std. dev. | variance | +----+------+---------+---------+--------+-----------+----------+ | 20 |  146 |       4 |      10 | 7.3000 |    1.8382 |   3.3789 | +----+------+---------+---------+--------+-----------+----------+ 

The STDDEV_SAMP⁠(⁠ ⁠ ⁠) and VAR_SAMP⁠(⁠ ⁠ ⁠) functions produce sample measures rather than population measures. That is, for a set of n values, they produce a result that is based on n1 degrees of freedom. If you want the population measures, which are based on n degrees of freedom, use the STDDEV_POP⁠(⁠ ⁠ ⁠) and VAR_POP⁠(⁠ ⁠ ⁠) functions instead. STDDEV⁠(⁠ ⁠ ⁠) and VARIANCE⁠(⁠ ⁠ ⁠) are synonyms for STDDEV_POP⁠(⁠ ⁠ ⁠) and VAR_POP⁠(⁠ ⁠ ⁠).

Standard deviation can be used to identify outliersvalues 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 := STDDEV_SAMP(score) FROM testscore; SELECT score FROM testscore WHERE ABS(score-@mean) > @std * 3; 

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 frequency     -> FROM testscore GROUP BY score ORDER BY frequency DESC; +-------+-----------+ | score | frequency | +-------+-----------+ |     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:[*]

[*] Note that the definition of median given here isn't fully general; it doesn't address what to do if the middle values in the dataset are duplicated.

  • 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.

Based on that definition, use the following procedure to determine the median of a set of observations stored in the database:

  1. 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.

  2. Issue a query that includes an ORDER BY clause to sort the observations and a LIMIT clause to pull out the middle value or values.

  3. If there is a single middle value, it is the median. Otherwise, take the average of the middle values.

For example, if a table t contains a score column with 37 values (an odd number), you need to select a single value to get the median, using a statement like this:

SELECT score FROM t ORDER BY score LIMIT 18,1 

If the column contains 38 values (an even number), the statement becomes:

SELECT score FROM t ORDER BY score LIMIT 18,2 

Then you can select the values returned by the statement 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 database, table, and column that contain the set of observations. Then it generates the statement that retrieves the relevant values and returns their average:

sub median { my ($dbh, $db_name, $tbl_name, $col_name) = @_; my ($count, $limit);   $count = $dbh->selectrow_array ("SELECT COUNT($col_name)                                    FROM $db_name.$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 $db_name.$tbl_name                             ORDER BY $col_name $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 use 1-based array indexes, adjust the algorithm accordingly.




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net