Gain insights into using SQL for anomaly detection. Delve into applying mean, standard deviation, and z-score to identify and investigate data anomalies, enhancing your analytical skills.

ruby.tar.gz

Learn how to identify anomalies in your data using SQL. Understand how to apply mathematical concepts such as mean, standard deviation, and z-score to detect when an anomaly occurred in your data and how to investigate past data to improve and refine the model.

Simple Anomaly Detection using SQL

Using a z-score for detecting anomalies is an easy way to get started with anomaly detection and see results right away. But this method is not always the best choice, and if you do not get good alerts using this method, there are some improvements and other methods you can try using just SQL.

Our system uses a mean to determine a reasonable value and a lookback period to determine how far back to calculate the mean. In our case, we calculated the mean based on data from 1 hour ago.

Using this method of calculating mean gives the same weight to entries that happened 1 hour ago and to entries that just happened. Give more weight to recent entries at the expense of previous entries. The new weighted mean should become more sensitive to recent entries, and you may be able to identify anomalies quicker.
To give more weight to recent entries, you can use a weighted average:


Learn how to use weighted mean to improve the accuracy of anomaly detection.

Detecting Anomalies

Analyzing A Server Log

Backtesting

Improving Accuracy

Weighted Mean