Mean Absolute Deviation

Nassim Taleb wrote a provocative article on Edge.Org calling for using the Mean Absolute Deviation in place of the more popular standard deviation as a measure of the variability of a collection of observations. His reasoning is persuasive to me, especially his claim that the standard deviation is widely misapplied and misunderstood.

MySQL (like many RDBMs) has an aggregate function STD() that computes the standard deviation of a collection of observations.  I’ve added a MySQL feature request for MAD(), the mean absolute deviation.

Adding this aggregate function to MySQL has the potential to improve science by making it easier to compute and present this measure of variation.

You can compute it with MySQL without the aggregate function, but it’s a little more complex than using STD(). Here’s an example using some weather data, computing the mean average deviation of of temperature month by month.

SELECT a.yearobs, a.station,
       a.average, a.stdev,
       AVG(ABS(b.temp - a.average)) meanavgdeviation
 FROM  (
        SELECT TRUNC_MONTH(obstime) AS yearobs, station,
               AVG(temp) AS average,
               STD(temp) AS stdev
          FROM obs
         GROUP BY station, TRUNC_MONTH(obstime)
       ) AS a
  JOIN obs AS b  ON a.yearobs = TRUNC_MONTH(b.obstime)
                AND a.station = b.station
 GROUP BY a.station, TRUNC_MONTH(b.obstime)
 ORDER BY a.station, TRUNC_MONTH(b.obstime)

It does happen to require two passes over the dataset to compute, where the STD() function doesn’t.

Microsoft Excel has this function: AVEDEV()

PostGreSQL doesn’t have it.

Leave a Comment