Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Outlier Detection in SQL

periscopedata.com

Post reply on HN
Print view View original

21–30 of 36 posts

Re: Outlier Detection in SQL

#21

Post by rodionos »

numlocked wrote: post #17

These are some cool SQL tricks! I like it. The big caveat with the standard deviation technique is that it assumes a normal distribution. Many datasets are not actually distributed normally (power-law, Poisson, beta, etc, etc) and so the technique won't work. It's a much harder problem to 'generically' detect outliers without knowledge of the underlying distribution. I don't have any idea how to do it (though a forme…

Other than normal distribution assumption, there is another assumption that doesn't hold true for most time series related to human activity, nature, or scheduling. If heteroscedasticity is present, you cannot use the same standard deviation for the entire series. A more practical approach is to compute variance for each calendar period separately.

Here's an example - expected variance for the number of SWIFT payments processed during non-banking hours is 0. Transaction counter greater than 0 is an outlier.

Re: Outlier Detection in SQL

#23

Post by huac »

et2o wrote: post #18

I would like to know about more sophisticated techniques for outlier detection. These are stat 101 level. Z scores? You can get into a lot of trouble assuming a normal distribution. What are credit card companies doing? What's the best way to combine multiple variables that are predictive of an event for outlier detection? Is there a simple framework to automate reporting of these events in real-time? One way to mode…

Anomaly detection is very different across different domains. For CC fraud / risk, you have discrete transactions so the problem is one of classification, and generally approached with supervised learning.

I don't know what you mean by combining multiple variables. Do you mean analysis methods that work with multiple variables (instead of 1-dimensional z-scores) or do you mean methods that combine multiple variables into 1, to reduce input dimensions (i.e. principal component analysis)

Because data and data reporting platforms are so different across companies, there's no 'simple framework' to do reporting. You probably want something like https://github.com/etsy/skyline.

You also describe an ensemble method for outlier detection, which is what Skyline uses. I want to note that there is no reason to consider ensembles "not" outlier detection.

Re: Outlier Detection in SQL

#24

Post by thom »

numlocked wrote: post #17

These are some cool SQL tricks! I like it. The big caveat with the standard deviation technique is that it assumes a normal distribution. Many datasets are not actually distributed normally (power-law, Poisson, beta, etc, etc) and so the technique won't work. It's a much harder problem to 'generically' detect outliers without knowledge of the underlying distribution. I don't have any idea how to do it (though a forme…

Your colleague is describing something similar to kernel density estimation, which would be your first port of call google-wise.

Re: Outlier Detection in SQL

#25

Post by Artemis2 »

stingraycharles wrote: post #13
frik wrote: post #12

Earlier quoted context omitted.

Is it just me or is Periscope allergic to showing the "Pricing" page? (SaaS anti-pattern) Or is it free? The blog content is good though

You can get a free trial, but it's most definitely not free (on the contrary, the price is a lot higher than i would've expected).

It's not extremely expensive, but it's clearly aimed at enterprise-level companies and not at small businesses.

Re: Outlier Detection in SQL

#26

Post by thom »

I've recently been doing a bunch of stuff with sports stats, which involves lots of GIS data. This sort of thing comes up a lot - trying to find a player's 'territory' based on coordinates of their actions in a game, without including outlying events that cause you to overestimate the area.

There's a concept in animal behaviour called a 'home range' which is more or less the same thing - GPS attached to tigers in the wild etc. Some of the algorithms there are quite interesting, from simply drawing a bounding box around the the data points, to working out the probability density, to things like LoCoH, which sort of recursively build up convex hulls from nearest neighbours.

All of these things are pretty much possible in SQL to one degree of performance or another. But ultimately I'm fascinated by things like SQL Server's R support - you can get far simpler, more natural implementations of these things in R (or indeed in custom aggregates or functions in other languages). I think in the long term, database engines that offer this sort of extensibility are going to thrive for analytics work, be they SQL based or otherwise.

Re: Outlier Detection in SQL

#27

Post by et2o »

huac wrote: post #23
et2o wrote: post #18

I would like to know about more sophisticated techniques for outlier detection. These are stat 101 level. Z scores? You can get into a lot of trouble assuming a normal distribution. What are credit card companies doing? What's the best way to combine multiple variables that are predictive of an event for outlier detection? Is there a simple framework to automate reporting of these events in real-time? One way to mode…

Anomaly detection is very different across different domains. For CC fraud / risk, you have discrete transactions so the problem is one of classification, and generally approached with supervised learning. I don't know what you mean by combining multiple variables. Do you mean analysis methods that work with multiple variables (instead of 1-dimensional z-scores) or do you mean methods that combine multiple variables…

I meant using multiple variables to categorize outlier events. What was shown here are also techniques to categorize discrete events ("Does this day cross some threshold?"). I guessed supervised learning methods.

Re: Outlier Detection in SQL

#28

Post by mistermann »

hglaser wrote: post #14
cwyers wrote: post #3

Is it just me or is Periscope allergic to saying what databases it supports? I can find nothing on the website, and I am really hesitant to sign up for more info once companies started actually calling my phone after I did that. Also, none of those queries will work on MySQL.

Periscope Data cofounder here. I wouldn't say we're "allergic" necessarily. ;) Like a lot of startups, we're very young and haven't gotten around to building out the website as much as we'd like to. `jplitz and `ajones are right. As of now we support MySQL, Postgres, Amazon Redshift, Vertica, SQL Server, Oracle, MemSQL, Sybase, Exasol and Google BigQuery. We add more all the time.

Shouldn't be too much work to copy/paste that onto the website, should we expect to see it there soon? :)

Re: Outlier Detection in SQL

#29

Post by rubyfan »

hglaser wrote: post #14
cwyers wrote: post #3

Is it just me or is Periscope allergic to saying what databases it supports? I can find nothing on the website, and I am really hesitant to sign up for more info once companies started actually calling my phone after I did that. Also, none of those queries will work on MySQL.

Periscope Data cofounder here. I wouldn't say we're "allergic" necessarily. ;) Like a lot of startups, we're very young and haven't gotten around to building out the website as much as we'd like to. `jplitz and `ajones are right. As of now we support MySQL, Postgres, Amazon Redshift, Vertica, SQL Server, Oracle, MemSQL, Sybase, Exasol and Google BigQuery. We add more all the time.

The examples worked well in Hive also.

I've never used Periscope but have followed and read the blog for a while. Always great content.

Re: Outlier Detection in SQL

#30

Post by darkxanthos »

Standard deviations assume normal distributions... but ultimately when you look for some two standard deviations from the mean, you're just look for an event that only occurs 95% of the time. A technique that works regardless of distribution is percentiles. Postgesql supports these now as well.
Post reply on HN