6

Context

I am tasked with trying to detect outliers in time series data in an Amazon Redshift (PostgreSQL) system. Also known as a public holiday detector around the office. The method I have been using takes a windowed average of the previous N data points, I have also a windowed Standard Deviation and then take those stats about the N prior points and apply the following to the current datapoint:

(x0 - avg(x1:xN)) / stddev(x1:xN) > threshold

So window length and threshold have been sufficient to play with but this method is not a robust method since we have had significant growth after an advertising campaign then the series is straying hugely away from the running average and standard deviation threshold and everything is an outlier.

Reducing the window length allows it to adapt to these changes faster but it doesn't have as good of a long term model.

Increasing the threshold for this sort of growth would mean previous outliers we detected will no longer get detected.

These related questions provide suggestions in R but the answer quite often mention Median Absolute Deviation as a robust method:

https://en.wikipedia.org/wiki/Median_absolute_deviation

tl;dr

How do I implement Median Absolute Deviation on a times series data set in Amazon Redshift?

I'm not sure if I'm missing something fundamental about the method but I'd like it to only work on a window and not have it work on the entire data set. Although the Median Window functions do not allow a frame clause.

If not this method, then point me in the right direction of more sophisticated outlier detection queries in Amazon Redshift would be appreciated.

1
  • I think your base problem is a very complicated one, and a field of many (unsuccessful ;) efforts. As for the implementation, if Redshift has the necessary functions, it must be relatively easy to reimplement (or 'translate') an existing R implementation. So, to me, the question is if these functions are there - and that is possibly explained by the documentation. Commented Jun 30, 2016 at 12:35

1 Answer 1

4

I believe this is very doable with a CTE or subquery!

MAD is can be calculated by composing several Redshift functions:

  • the median of a list of values
  • absolute value of the difference between each value and that median
  • the median of those values

I wrote this in the form of:

WITH
medians AS (
  SELECT
    t.values,
    MEDIAN(t.values) OVER () as median_value,
    ABS(t.values-MEDIAN(t.values) OVER ()) AS absolute_deviation
  FROM table AS t
  GROUP BY t.values
)
SELECT
  MEDIAN(absolute_deviation) OVER () AS median_absolute_deviation
FROM medians

Hope this helps

1
  • This technically answers the question and nicely so. Unfortunately the MAD method doesn't suit my situation. Thanks for providing this, it helped me model it enough to show I'm needing something more sophisticated.
    – Josh Peak
    Commented Jul 6, 2016 at 5:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.