<!DOCTYPE html>
<html lang="en" class="nojs" xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
<head>
<title>
Simple Anomaly Detection Using Plain SQL | Haki Benita
</title>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="referrer" content="origin-when-cross-origin" />
<meta http-equiv="Content-Security-Policy" content="default-src 'self'; object-src 'none'; script-src 'self' https://goat.hakibenita.com 'unsafe-inline' https://www.google-analytics.com; style-src 'self' 'unsafe-inline'; font-src 'self'; img-src 'self' https://www.google-analytics.com *.googleusercontent.com stats.g.doubleclick.net https://goat.hakibenita.com; worker-src 'none'; connect-src 'self' https://www.google-analytics.com; frame-src 'none';" />
<meta name="description" content="Many developers think that having a critical bug in their code is the worse thing that can happen. Well, there is something much worst than that: Having a critical bug in your code and not knowing about it! Using some high school level statistics and a fair knowledge of SQL, I implemented a very simple anomaly detection system." />
<meta name="copyright" content="Haki Benita" />
<link rel="shortcut icon" type="image/png" href="/images/favicon-32x32.png" />
<link href="https://hakibenita.com/feeds/all.atom.xml" type="application/atom+xml" rel="alternate" title="Haki Benita Full Atom Feed" />
<script async="async" src="https://www.google-analytics.com/analytics.js"></script>
<script>
<![CDATA[
window.ga=window.ga||function(){(ga.q=ga.q||[]).push(arguments);};ga.l=+(new Date());
ga('create', 'UA-131229086-1', 'auto');
ga('send', 'pageview');
]]>
</script>
<script>
<![CDATA[
window.goatcounter = {
path: location.pathname || '/',
referrer: function() {
return goatcounter.get_query('utm_source') || document.referrer;
},
};
document.addEventListener('DOMContentLoaded', () => {
const baseUrl = `${location.protocol}//${location.host}`
Array.from(document.querySelectorAll('a[href]'))
.filter(a => !a.href.startsWith(baseUrl))
.forEach(a => a.addEventListener('click', () => window.goatcounter.count({
path: p => `outgoing:${p}`,
title: a.textContent,
event: true,
})));
});
]]>
</script>
<script data-goatcounter="https://goat.hakibenita.com:/count" async="async" src="https://goat.hakibenita.com/count.js"></script>
<script>
<![CDATA[
(function(window, document) {
// JS
document.documentElement.classList.remove('nojs');
// Theme
const prefersDarkColorScheme = window.matchMedia && window.matchMedia("(prefers-color-scheme: dark)").matches;
const selectedTheme = window.localStorage.getItem('theme');
if (selectedTheme === 'dark' || (!selectedTheme && prefersDarkColorScheme)) {
document.documentElement.classList.add('dark');
ga('set', 'dimension1', 'dark');
} else {
ga('set', 'dimension1', 'light');
}
document.addEventListener('DOMContentLoaded', () => {
const toggleButton = document.querySelector('.toggle-theme');
toggleButton && toggleButton.addEventListener('click', () => {
const wasLight = document.documentElement.classList.toggle('dark');
const theme = wasLight ? 'dark' : 'light';
ga('set', 'dimension1', theme);
ga('send', 'event', 'theme', 'switched theme', theme, 1);
window.localStorage.setItem('theme', theme);
});
});
})(window, document);
]]>
</script>
<link rel="stylesheet" type="text/css" href="https://hakibenita.com/theme/css/style.css?v=301d17d" />
<link rel="stylesheet" type="text/css" href="https://hakibenita.com/theme/css/pygment.css?v=301d17d" />
<link rel="canonical" href="https://hakibenita.com/sql-anomaly-detection" />
<meta name="twitter:card" content="summary_large_image" />
<meta name="twitter:site" content="@be_haki" />
<meta name="twitter:creator" content="@be_haki" />
<meta property="og:type" content="article" />
<meta property="og:title" content="Simple Anomaly Detection Using Plain SQL" />
<meta property="og:description" content="Identify Problems Before They Become Disasters" />
<meta property="og:url" content="https://hakibenita.com/sql-anomaly-detection" />
<meta property="og:image" content="https://hakibenita.com/images/00-sql-anomaly-detection-scatter-plot.png" />
<meta name="subtitle" content="Identify Problems Before They Become Disasters" />
<meta name="author" content="Haki Benita" />
<meta name="date" content="2020-09-21" />
<meta name="tags" content="SQL" />
<meta name="monetization" content="$ilp.uphold.com/iEY62FwdNGHy" />
<style>
<![CDATA[
.subscribe {
display: flex;
justify-content: center;
align-items: center;
}
.subscribe form {
max-width: 100%;
min-width: 0;
}
.subscribe__image {
margin-right: 1em;
}
.subscribe h5 {
line-height: var(--line-height-tight);
margin-bottom: 0.5em;
}
.subscribe__input {
display: flex;
border: 1px solid var(--brand-color);
box-shadow: 4px 4px 0px rgba(0, 0, 0, 0.15);
}
.subscribe__input input {
appearance: none;
-moz-appearance: none;
-webkit-appearance: none;
border: 0;
border-radius: 0;
background-color: inherit;
padding: 0 1em;
line-height: 2.5;
min-width: 0;
max-width: 100%;
flex-grow: 1;
}
.subscribe__input input[type="email"] {
font-family: inherit;
}
.subscribe__input input[type="submit"] {
background-color: var(--brand-color);
color: var(--bg-color);
font-family: monospace;
font-weight: bold;
}
.subscribe__input input[type="submit"]:disabled {
opacity: 0.5;
}
]]>
</style>
</head>
<body class="">
<nav>
<a class="logo" href="https://hakibenita.com/">Haki Benita</a>
<ul>
<li>
<a href="https://hakibenita.com/pages/about">About</a>
</li>
<li>
<a href="https://hakibenita.com/subscribe">Subscribe</a>
</li>
</ul><button title="Switch Theme" aria-label="Switch Theme" class="toggle-theme"><svg width="1em" height="1em" viewbox="0 0 24 24" fill="currentColor" stroke="none" class="icon-moon">
<path d="M21 12.79A9 9 0 1 1 11.21 3 7 7 0 0 0 21 12.79z"></path></svg> <svg width="1em" height="1em" viewbox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" class="icon-sun">
<circle cx="12" cy="12" r="5" fill="currentColor"></circle>
<line x1="12" y1="1" x2="12" y2="3"></line>
<line x1="12" y1="21" x2="12" y2="23"></line>
<line x1="4.22" y1="4.22" x2="5.64" y2="5.64"></line>
<line x1="18.36" y1="18.36" x2="19.78" y2="19.78"></line>
<line x1="1" y1="12" x2="3" y2="12"></line>
<line x1="21" y1="12" x2="23" y2="12"></line>
<line x1="4.22" y1="19.78" x2="5.64" y2="18.36"></line>
<line x1="18.36" y1="5.64" x2="19.78" y2="4.22"></line></svg></button>
</nav>
<main>
<div class="article">
<header class="article__header">
<ul class="article__info">
<li>
<time class="published" datetime="2020-09-21">21 September 2020</time>
</li>
<li>
<a class="tag" href="https://hakibenita.com/tag/sql">SQL</a>
</li>
</ul>
<h1>
Simple Anomaly Detection Using Plain SQL
</h1>
<h2>
Identify Problems Before They Become Disasters
</h2>
</header>
<article class="article__content" data-progress-indicator="">
<hr />
<p>
Many developers think that having a critical bug in their code is the worst thing that can happen. Well, there is something much worse than that: Having a critical bug in your code and <strong>not knowing about it!</strong>
</p>
<p>
To make sure I get notified about critical bugs as soon as possible, I started looking for ways to find anomalies in my data. I quickly found that information about these subjects tend to get very complicated, and involve a lot of ad-hoc tools and dependencies.
</p>
<p>
I'm not a statistician and not a data scientist, I'm just a developer. Before I introduce dependencies into my system I make sure I really can't do without them. So, <strong>using some high school level statistics and a fair knowledge of SQL, I implemented a simple anomaly detection system <em>that works</em>.</strong>
</p>
<figure>
<img alt="Can you spot the anomaly?<br><small>Photo by <a href="https://unsplash.com/photos/KmKZV8pso-s">Ricardo Gomez Angel</a></small>" src="https://hakibenita.com/images/00-sql-anomaly-detection.png" />
<figcaption>
Can you spot the anomaly?<br />
<small>Photo by <a href="https://unsplash.com/photos/KmKZV8pso-s">Ricardo Gomez Angel</a></small>
</figcaption>
</figure>
<details class="toc-container" open="">
<summary>
Table of Contents
</summary>
<div class="toc">
<ul>
<li>
<a href="#detecting-anomalies">Detecting Anomalies</a>
<ul>
<li>
<a href="#understanding-z-score">Understanding Z-Score</a>
</li>
<li>
<a href="#optimizing-z-score">Optimizing Z-Score</a>
</li>
</ul>
</li>
<li>
<a href="#analyzing-a-server-log">Analyzing a Server Log</a>
<ul>
<li>
<a href="#preparing-the-data">Preparing the Data</a>
</li>
<li>
<a href="#getting-a-sense-of-the-data">Getting a Sense of the Data</a>
</li>
<li>
<a href="#identifying-anomalies">Identifying Anomalies</a>
</li>
</ul>
</li>
<li>
<a href="#backtesting">Backtesting</a>
<ul>
<li>
<a href="#finding-past-anomalies">Finding Past Anomalies</a>
</li>
<li>
<a href="#adding-thresholds">Adding Thresholds</a>
</li>
<li>
<a href="#eliminating-repeating-alerts">Eliminating Repeating Alerts</a>
</li>
<li>
<a href="#experiment-with-different-values">Experiment With Different Values</a>
</li>
</ul>
</li>
<li>
<a href="#improving-accuracy">Improving Accuracy</a>
<ul>
<li>
<a href="#use-weighted-mean">Use Weighted Mean</a>
</li>
<li>
<a href="#use-median">Use Median</a>
</li>
<li>
<a href="#use-mad">Use MAD</a>
</li>
<li>
<a href="#use-different-measures">Use Different Measures</a>
</li>
</ul>
</li>
<li>
<a href="#conclusion">Conclusion</a>
</li>
</ul>
</div>
</details>
<hr />
<div class="admonition tip">
<p class="admonition-title">
Interactive Editor
</p>
<div style="display:flex;align-items:center;">
<svg xmlns="http://www.w3.org/2000/svg" style="height:6em;min-height:2em" viewbox="0 0 110.7 144.7">
<g stroke-opacity=".6" fill-opacity=".6" fill-rule="evenodd">
<path d="M10 130l25-41 10-5 6 1 14 2 8 8 17 17 6 12 4 1-22 7-66 1" fill="#f41d92"></path>
<path d="M15 134c4-8 14-41 22-49 9-8 22-6 31 2 10 7 37 37 28 44-10 8-72 1-86 1m3 0c5-7 21-37 30-44s15-5 24 2c9 6 38 30 29 37s-68 5-82 6" stroke="transparent" fill="none"></path>
</g>
<path d="M41 48c2 10 2 16 3 38m-2-37c1 11 0 23 2 35m-2 1l-30 39m30-40c-9 13-17 24-31 40m1 1c34-5 68-5 88-3m-88 2c32-3 65-2 87-3m2-2L65 87m35 33c-11-8-20-19-36-35m2 0l-3-39m2 40l-1-38m3-3l-25 6m22-5l-22 3" stroke="#000" stroke-width="4" fill="none"></path>
<path d="M42 30l4 2 1 3v4l-3 2h-4l-3-2v-4l2-3 3-2c1 0 0 0 0 0m0 0l4 2 1 3v4l-3 2h-4l-3-2v-4l2-3 3-2c1 0 0 0 0 0" fill="#ced4da"></path>
<path d="M42 30l4 2 1 3v4l-3 2h-4l-3-2v-4l2-3 3-2c1 0 0 0 0 0m0 0l4 2 1 3v4l-3 2h-4l-3-2v-4l2-3 3-2c1 0 0 0 0 0" stroke="transparent" fill="none"></path>
<path d="M61 23l3-2 4 1 1 4c0 2-2 3-2 3l-2 3-5-1-2-2 1-5 3-2h1m2 1l4 1-1 2-2 4c-1 1-2 3-4 3l-2-3-2-3 2-5 3-1c1-1 3 1 3 1l-1-2" fill="#ced4da"></path>
<path d="M62 22h3l4 2v4l-1 2-4 2-5-2-1-3v-4l4-1 1-1m0 0l2 1 3 4 1 1-2 5c-1 1-2 2-3 1-2 0-3-3-4-4l-3-2 2-4h3l1-2" stroke="transparent" fill="none"></path>
<g>
<path d="M49 10l3 3v5c0 2-1 3-2 3l-3 2-5-2-2-4 2-4 3-3 5 1h1m-6-1l2-1 4 2-1 7-1 2-2 2-4 1-3-4 1-4 3-5h-1" fill="#ced4da"></path>
<path d="M47 9l4 3v7l-4 3-4-1-1-2v-5l1-2 4-1c1 0 0 0 0 0m1-1l4 3-2 2c0 1 2 4 1 6l-5 2-3-1-2-4-1-3 2-4h5l2-1" stroke="transparent" fill="none"></path>
</g></svg>
<div style="flex-grow:1; margin-left: 1em;" markdown="1">
To follow along with the article and experiment with actual data online check out the <strong><a href="https://popsql.com/queries/-MECQV6GiKr04WdCWM0K/simple-anomaly-detection-with-sql?access_token=2d2c0729f9a1cfa7b6a2dbb5b0adb45c" rel="noopener">interactive editor on PopSQL ≫</a></strong>
</div>
</div>
</div>
<hr />
<h2 id="detecting-anomalies">
<a class="toclink" href="#detecting-anomalies">Detecting Anomalies</a>
</h2>
<p>
Anomaly in a data series is a significant deviation from some reasonable value. Looking at this series of numbers for example, which number stands out?
</p>
<div class="highlight">
<pre>2, 3, 5, 2, 3, 12, 5, 3, 4
</pre>
</div>
<p>
The number that stands out in this series is 12.
</p>
<figure>
<img alt="Scatter plot" src="https://hakibenita.com/images/00-sql-anomaly-detection-scatter-plot.png" />
<figcaption>
Scatter plot
</figcaption>
</figure>
<p>
This is intuitive to a human, but computer programs don't have intuition...
</p>
<p>
To find the anomaly in the series we first need to define what a reasonable value is, and then define how far away from this value we consider a significant deviation. A good place to start looking for a reasonable value is the mean:
</p>
<div class="highlight">
<pre><span class="k">SELECT</span> <span class="n">avg</span><span class="p">(</span><span class="n">n</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">unnest</span><span class="p">(</span><span class="k">array</span><span class="p">[</span><span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">12</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">4</span><span class="p">])</span> <span class="k">AS</span> <span class="n">n</span><span class="p">;</span>
<span class="go"> avg</span>
<span class="go">────────────────────</span>
<span class="go">4.3333333333333333</span>
</pre>
</div>
<p>
The mean is ~4.33.
</p>
<p>
Next, we need to define the deviation. Let's use <a href="https://en.wikipedia.org/wiki/Standard_deviation" rel="noopener">Standard Deviation</a>:
</p>
<div class="highlight">
<pre><span class="k">SELECT</span> <span class="n">stddev</span><span class="p">(</span><span class="n">n</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">unnest</span><span class="p">(</span><span class="k">array</span><span class="p">[</span><span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">12</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">4</span><span class="p">])</span> <span class="k">AS</span> <span class="n">n</span><span class="p">;</span>
<span class="go"> stddev</span>
<span class="go">────────────────────</span>
<span class="go">3.0822070014844882</span>
</pre>
</div>
<p>
Standard deviation is the square root of the <a href="https://en.wikipedia.org/wiki/Variance" rel="noopener">variance</a>, which is the average squared distance from the mean. In this case it's 3.08.
</p>
<p>
Now that we've defined a "reasonable" value and a deviation, we can define a <em>range</em> of acceptable values:
</p>
<div class="highlight">
<pre><span class="k">SELECT</span>
<span class="n">avg</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="o">-</span> <span class="n">stddev</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="k">AS</span> <span class="n">lower_bound</span><span class="p">,</span>
<span class="n">avg</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="o">+</span> <span class="n">stddev</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="k">AS</span> <span class="n">upper_bound</span>
<span class="k">FROM</span>
<span class="n">unnest</span><span class="p">(</span><span class="k">array</span><span class="p">[</span><span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">12</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">4</span><span class="p">])</span> <span class="k">AS</span> <span class="n">n</span><span class="p">;</span>
<span class="go"> lower_bound │ upper_bound</span>
<span class="go">───────────────────┼────────────────────</span>
<span class="go">1.2511263318488451 │ 7.4155403348178215</span>
</pre>
</div>
<p>
The range we defined is one standard deviation from the mean. Any value outside this range is considered an anomaly:
</p>
<div class="highlight">
<pre><span class="k">WITH</span> <span class="n">series</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">unnest</span><span class="p">(</span><span class="k">array</span><span class="p">[</span><span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">12</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">4</span><span class="p">])</span> <span class="k">AS</span> <span class="n">n</span>
<span class="p">),</span>
<span class="n">bounds</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">avg</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="o">-</span> <span class="n">stddev</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="k">AS</span> <span class="n">lower_bound</span><span class="p">,</span>
<span class="n">avg</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="o">+</span> <span class="n">stddev</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="k">AS</span> <span class="n">upper_bound</span>
<span class="k">FROM</span>
<span class="n">series</span>
<span class="p">)</span>
<span class="k">SELECT</span>
<span class="n">n</span><span class="p">,</span>
<span class="n">n</span> <span class="k">NOT</span> <span class="k">BETWEEN</span> <span class="n">lower_bound</span> <span class="k">AND</span> <span class="n">upper_bound</span> <span class="k">AS</span> <span class="n">is_anomaly</span>
<span class="k">FROM</span>
<span class="n">series</span><span class="p">,</span>
<span class="n">bounds</span><span class="p">;</span>
<span class="go">n │ is_anomaly</span>
<span class="go">───┼────────────</span>
<span class="go"> 2 │ f</span>
<span class="go"> 3 │ f</span>
<span class="go"> 5 │ f</span>
<span class="go"> 2 │ f</span>
<span class="go"> 3 │ f</span>
<span class="hll"><span class="go">12 │ t</span>
</span><span class="go"> 5 │ f</span>
<span class="go"> 3 │ f</span>
<span class="go"> 4 │ f</span>
</pre>
</div>
<p>
Using the query we found that the value 12 is outside the range of acceptable values, and identified it as an anomaly.
</p>
<h3 id="understanding-z-score">
<a class="toclink" href="#understanding-z-score">Understanding Z-Score</a>
</h3>
<p>
Another way to represent a range of acceptable values is using a z-score. <a href="https://en.wikipedia.org/wiki/Standard_score" rel="noopener">z-score, or Standard Score</a>, is the number of standard deviations from the mean. In the previous section, our acceptable range was one standard deviation from the mean, or in other words, a z-score in the range ±1:
</p>
<div class="highlight">
<pre><span class="k">WITH</span> <span class="n">series</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">unnest</span><span class="p">(</span><span class="k">array</span><span class="p">[</span><span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">12</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">4</span><span class="p">])</span> <span class="k">AS</span> <span class="n">n</span>
<span class="p">),</span>
<span class="n">stats</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">avg</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="n">series_mean</span><span class="p">,</span>
<span class="n">stddev</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="k">as</span> <span class="n">series_stddev</span>
<span class="k">FROM</span>
<span class="n">series</span>
<span class="p">)</span>
<span class="k">SELECT</span>
<span class="n">n</span><span class="p">,</span>
<span class="hll"> <span class="p">(</span><span class="n">n</span> <span class="o">-</span> <span class="n">series_mean</span><span class="p">)</span> <span class="o">/</span> <span class="n">series_stddev</span> <span class="k">as</span> <span class="n">zscore</span>
</span><span class="k">FROM</span>
<span class="n">series</span><span class="p">,</span>
<span class="n">stats</span><span class="p">;</span>
<span class="go">n │ zscore</span>
<span class="go">───┼─────────────────────────</span>
<span class="go"> 2 │ -0.75703329861022517346</span>
<span class="go"> 3 │ -0.43259045634870009448</span>
<span class="go"> 5 │ 0.21629522817435006346</span>
<span class="go"> 2 │ -0.75703329861022517346</span>
<span class="go"> 3 │ -0.43259045634870009448</span>
<span class="go">12 │ 2.4873951240050256</span>
<span class="go"> 5 │ 0.21629522817435006346</span>
<span class="go"> 3 │ -0.43259045634870009448</span>
<span class="go"> 4 │ -0.10814761408717501551</span>
</pre>
</div>
<p>
Like before, we can detect anomalies by searching for values which are outside the acceptable range using the z-score:
</p>
<div class="highlight">
<pre><span class="k">WITH</span> <span class="n">series</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">unnest</span><span class="p">(</span><span class="k">array</span><span class="p">[</span><span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">12</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">4</span><span class="p">])</span> <span class="k">AS</span> <span class="n">n</span>
<span class="p">),</span>
<span class="n">stats</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">avg</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="n">series_avg</span><span class="p">,</span>
<span class="n">stddev</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="k">as</span> <span class="n">series_stddev</span>
<span class="k">FROM</span>
<span class="n">series</span>
<span class="p">),</span>
<span class="n">zscores</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">n</span><span class="p">,</span>
<span class="p">(</span><span class="n">n</span> <span class="o">-</span> <span class="n">series_avg</span><span class="p">)</span> <span class="o">/</span> <span class="n">series_stddev</span> <span class="k">AS</span> <span class="n">zscore</span>
<span class="k">FROM</span>
<span class="n">series</span><span class="p">,</span>
<span class="n">stats</span>
<span class="p">)</span>
<span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="n">zscore</span> <span class="k">NOT</span> <span class="k">BETWEEN</span> <span class="o">-</span><span class="mf">1</span> <span class="k">AND</span> <span class="mf">1</span> <span class="k">AS</span> <span class="n">is_anomaly</span>
<span class="k">FROM</span>
<span class="n">zscores</span><span class="p">;</span>
<span class="go">n │ zscore │ is_anomaly</span>
<span class="go">───┼─────────────────────────┼────────────</span>
<span class="go"> 2 │ -0.75703329861022517346 │ f</span>
<span class="go"> 3 │ -0.43259045634870009448 │ f</span>
<span class="go"> 5 │ 0.21629522817435006346 │ f</span>
<span class="go"> 2 │ -0.75703329861022517346 │ f</span>
<span class="go"> 3 │ -0.43259045634870009448 │ f</span>
<span class="hll"><span class="go">12 │ 2.4873951240050256 │ t</span>
</span><span class="go"> 5 │ 0.21629522817435006346 │ f</span>
<span class="go"> 3 │ -0.43259045634870009448 │ f</span>
<span class="go"> 4 │ -0.10814761408717501551 │ f</span>
</pre>
</div>
<p>
Using z-score, we also identified 12 as an anomaly in this series.
</p>
<h3 id="optimizing-z-score">
<a class="toclink" href="#optimizing-z-score">Optimizing Z-Score</a>
</h3>
<p>
So far we used one standard deviation from the mean, or a z-score of ±1 to identify anomalies. Changing the z-score threshold can affect our results. For example, let's see what anomalies we identify when the z-score is greater than 0.5 and when it's greater than 3:
</p>
<div class="highlight">
<pre><span class="k">WITH</span> <span class="n">series</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">unnest</span><span class="p">(</span><span class="k">array</span><span class="p">[</span><span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">12</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">4</span><span class="p">])</span> <span class="k">AS</span> <span class="n">n</span>
<span class="p">),</span>
<span class="n">stats</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">avg</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="n">series_avg</span><span class="p">,</span>
<span class="n">stddev</span><span class="p">(</span><span class="n">n</span><span class="p">)</span> <span class="k">as</span> <span class="n">series_stddev</span>
<span class="k">FROM</span>
<span class="n">series</span>
<span class="p">),</span>
<span class="n">zscores</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">n</span><span class="p">,</span>
<span class="p">(</span><span class="n">n</span> <span class="o">-</span> <span class="n">series_avg</span><span class="p">)</span> <span class="o">/</span> <span class="n">series_stddev</span> <span class="k">AS</span> <span class="n">zscore</span>
<span class="k">FROM</span>
<span class="n">series</span><span class="p">,</span>
<span class="n">stats</span>
<span class="p">)</span>
<span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="hll"> <span class="n">zscore</span> <span class="k">NOT</span> <span class="k">BETWEEN</span> <span class="o">-</span><span class="mf">0.5</span> <span class="k">AND</span> <span class="mf">0.5</span> <span class="k">AS</span> <span class="n">is_anomaly_0_5</span><span class="p">,</span>
</span><span class="hll"> <span class="n">zscore</span> <span class="k">NOT</span> <span class="k">BETWEEN</span> <span class="o">-</span><span class="mf">1</span> <span class="k">AND</span> <span class="mf">1</span> <span class="k">AS</span> <span class="n">is_anomaly_1</span><span class="p">,</span>
</span><span class="hll"> <span class="n">zscore</span> <span class="k">NOT</span> <span class="k">BETWEEN</span> <span class="o">-</span><span class="mf">3</span> <span class="k">AND</span> <span class="mf">3</span> <span class="k">AS</span> <span class="n">is_anomaly_3</span>
</span><span class="k">FROM</span>
<span class="n">zscores</span><span class="p">;</span>
<span class="go">n │ zscore │ is_anomaly_0_5 │ is_anomaly_1 │ is_anomaly_3</span>
<span class="go">───┼─────────────────────────┼────────────────┼──────────────┼──────────────</span>
<span class="go"> 2 │ -0.75703329861022517346 │ t │ f │ f</span>
<span class="go"> 3 │ -0.43259045634870009448 │ f │ f │ f</span>
<span class="go"> 5 │ 0.21629522817435006346 │ f │ f │ f</span>
<span class="go"> 2 │ -0.75703329861022517346 │ t │ f │ f</span>
<span class="go"> 3 │ -0.43259045634870009448 │ f │ f │ f</span>
<span class="go">12 │ 2.4873951240050256 │ t │ t │ f</span>
<span class="go"> 5 │ 0.21629522817435006346 │ f │ f │ f</span>
<span class="go"> 3 │ -0.43259045634870009448 │ f │ f │ f</span>
<span class="go"> 4 │ -0.10814761408717501551 │ f │ f │ f</span>
</pre>
</div>
<p>
Let's see what we got:
</p>
<ul>
<li>When we decreased the z-score threshold to 0.5, we identified the value 2 as an anomaly in addition to the value 12.
</li>
<li>When we increased the z-score threshold to 3 we did not identify any anomaly.
</li>
</ul>
<p>
The quality of our results are directly related to the parameters we set for the query. Later we'll see how using backtesting can help us identify ideal values.
</p>
<hr />
<h2 id="analyzing-a-server-log">
<a class="toclink" href="#analyzing-a-server-log">Analyzing a Server Log</a>
</h2>
<p>
Application servers such as nginx, Apache and IIS write a lot of useful information to access logs. The data in these logs can be extremely useful in identifying anomalies.
</p>
<p>
We are going to analyze logs of a web application, so the data we are most interested in is the timestamp and the status code of every response from the server. To illustrate the type of insight we can draw from just this data:
</p>
<ul>
<li>
<strong>A sudden increase in 500 status code</strong>: You may have a problem in the server. Did you just push a new version? Is there an external service you're using that started failing in unexpected ways?
</li>
<li>
<strong>A sudden increase in 400 status code</strong>: You may have a problem in the client. Did you change some validation logic and forgot to update the client? Did you make a change and forgot to handle backward compatibility?
</li>
<li>
<strong>A sudden increase in 404 status code</strong>: You may have an SEO problem. Did you move some pages and forgot to set up redirects? Is there some script kiddy running a scan on your site?
</li>
<li>
<strong>A sudden increase in 200 status code</strong>: You either have some significant legit traffic coming in, or you are under a DOS attack. Either way, you probably want to check where it's coming from.
</li>
</ul>
<h3 id="preparing-the-data">
<a class="toclink" href="#preparing-the-data">Preparing the Data</a>
</h3>
<p>
Parsing and processing logs is outside the scope of this article, so let's assume we did that and we have a table that looks like this:
</p>
<div class="highlight">
<pre><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">server_log_summary</span> <span class="k">AS</span> <span class="p">(</span>
<span class="n">period</span> <span class="n">timestamptz</span><span class="p">,</span>
<span class="n">status_code</span> <span class="nb">int</span><span class="p">,</span>
<span class="n">entries</span> <span class="nb">int</span>
<span class="p">);</span>
</pre>
</div>
<p>
The table stores the number of entries for each status code at a given period. For example, our table stores how many responses returned each status code every minute:
</p>
<div class="highlight">
<pre><span class="gp">db=#</span> <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">server_log_summary</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">period</span> <span class="k">DESC</span> <span class="k">LIMIT</span> <span class="mf">10</span><span class="p">;</span>
<span class="go"> period │ status_code │ entries</span>
<span class="go">───────────────────────┼─────────────┼─────────</span>
<span class="go">2020-08-01 18:00:00+00 │ 200 │ 4084</span>
<span class="go">2020-08-01 18:00:00+00 │ 404 │ 0</span>
<span class="go">2020-08-01 18:00:00+00 │ 400 │ 24</span>
<span class="go">2020-08-01 18:00:00+00 │ 500 │ 0</span>
<span class="go">2020-08-01 17:59:00+00 │ 400 │ 12</span>
<span class="go">2020-08-01 17:59:00+00 │ 200 │ 3927</span>
<span class="go">2020-08-01 17:59:00+00 │ 500 │ 0</span>
<span class="go">2020-08-01 17:59:00+00 │ 404 │ 0</span>
<span class="go">2020-08-01 17:58:00+00 │ 400 │ 2</span>
<span class="go">2020-08-01 17:58:00+00 │ 200 │ 3850</span>
</pre>
</div>
<p>
Note that the table has a row for every minute, even if the status code was never returned in that minute. Given a table of statuses, it's very tempting to do something like this:
</p>
<div class="highlight">
<pre><span class="c1">-- Wrong!</span>
<span class="k">SELECT</span>
<span class="n">date_trunc</span><span class="p">(</span><span class="s1">'minute'</span><span class="p">,</span> <span class="k">timestamp</span><span class="p">)</span> <span class="k">AS</span> <span class="n">period</span><span class="p">,</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">entries</span>
<span class="k">FROM</span>
<span class="n">server_log</span>
<span class="k">GROUP</span> <span class="k">BY</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">status_code</span><span class="p">;</span>
</pre>
</div>
<p>
This is a common mistake and it can leave you with gaps in the data. Zero is a value, and it holds a significant meaning. A better approach is to create an "axis", and join to it:
</p>
<div class="highlight">
<pre><span class="c1">-- Correct!</span>
<span class="k">WITH</span> <span class="n">axis</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">generate_series</span><span class="p">(</span>
<span class="n">date_trunc</span><span class="p">(</span><span class="s1">'minute'</span><span class="p">,</span> <span class="n">now</span><span class="p">()),</span>
<span class="n">date_trunc</span><span class="p">(</span><span class="s1">'minute'</span><span class="p">,</span> <span class="n">now</span><span class="p">()</span> <span class="o">-</span> <span class="nb">interval</span> <span class="s1">'1 hour'</span><span class="p">),</span>
<span class="nb">interval</span> <span class="s1">'1 minute'</span> <span class="o">*</span> <span class="o">-</span><span class="mi">1</span>
<span class="p">)</span> <span class="k">AS</span> <span class="n">period</span>
<span class="k">FROM</span> <span class="p">(</span>
<span class="k">VALUES</span> <span class="p">(</span><span class="mi">200</span><span class="p">),</span> <span class="p">(</span><span class="mi">400</span><span class="p">),</span> <span class="p">(</span><span class="mi">404</span><span class="p">),</span> <span class="p">(</span><span class="mi">500</span><span class="p">)</span>
<span class="p">)</span> <span class="k">AS</span> <span class="n">t</span><span class="p">(</span><span class="n">status_code</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">SELECT</span>
<span class="n">a</span><span class="p">.</span><span class="n">period</span><span class="p">,</span>
<span class="n">a</span><span class="p">.</span><span class="n">status_code</span><span class="p">,</span>
<span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">entries</span>
<span class="k">FROM</span>
<span class="n">axis</span> <span class="n">a</span>
<span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">server_log</span> <span class="n">l</span> <span class="k">ON</span> <span class="p">(</span>
<span class="n">date_trunc</span><span class="p">(</span><span class="s1">'minute'</span><span class="p">,</span> <span class="n">l</span><span class="p">.</span><span class="k">timestamp</span><span class="p">)</span> <span class="o">=</span> <span class="n">a</span><span class="p">.</span><span class="n">period</span>
<span class="k">AND</span> <span class="n">l</span><span class="p">.</span><span class="n">status_code</span> <span class="o">=</span> <span class="n">a</span><span class="p">.</span><span class="n">status_code</span>
<span class="p">)</span>
<span class="k">GROUP</span> <span class="k">BY</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">status_code</span><span class="p">;</span>
</pre>
</div>
<p>
First we generate an axis using a cartesian join between the status codes we want to track, and the times we want to monitor. To generate the axis we used two nice features of PostgreSQL:
</p>
<ul>
<li>
<a href="https://www.postgresql.org/docs/current/functions-srf.html" rel="noopener"><code>generate_series</code></a>: function that generates a range of values.
</li>
<li>
<a href="https://www.postgresql.org/docs/current/queries-values.html" rel="noopener"><code>VALUES</code> list</a>: special clause that can generate "constant tables", as the documentation calls it. You might be familiar with the <code>VALUES</code> clause from <code>INSERT</code> statements. In the old days, to generate data we had to use a bunch of <code>SELECT ... UNION ALL</code>... using <code>VALUES</code> is much nicer.
</li>
</ul>
<p>
After generating the axis, we left join the actual data into it to get a complete series for each status code. The resulting data has no gaps, and is ready for analysis.
</p>
<h3 id="getting-a-sense-of-the-data">
<a class="toclink" href="#getting-a-sense-of-the-data">Getting a Sense of the Data</a>
</h3>
<p>
To get a sense of the data, let's draw a stacked bar chart by status:
</p>
<figure>
<img alt="stacked bar chart by status, over time" src="https://hakibenita.com/images/00-sql-anomaly-detection-chart-by-status-over-time.png" />
<figcaption>
stacked bar chart by status, over time
</figcaption>
</figure>
<p>
The chart shows a period of 12 hours. It looks like we have a nice trend with two peaks at around 09:30 and again at 18:00.
</p>
<p>
We also spot right away that at ~11:30 there was a significant increase in 500 errors. The burst died down after around 10 minutes. This is the type of anomalies we want to identify early on.
</p>
<p>
It's entirely possible that there were other problems during that time, we just can't spot them with a naked eye.
</p>
<h3 id="identifying-anomalies">
<a class="toclink" href="#identifying-anomalies">Identifying Anomalies</a>
</h3>
<p>
In anomaly detection systems, we usually want to identify if we have an anomaly <em>right now</em>, and send an alert.
</p>
<p>
To identify if the last datapoint is an anomaly, we start by calculating the mean and standard deviation for each status code in the past hour:
</p>
<div class="highlight">
<pre><span class="gp">db=#</span> <span class="k">WITH</span> <span class="n">stats</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="p">(</span><span class="n">MAX</span><span class="p">(</span><span class="k">ARRAY</span><span class="p">[</span><span class="k">EXTRACT</span><span class="p">(</span><span class="s1">'epoch'</span> <span class="k">FROM</span> <span class="n">period</span><span class="p">),</span> <span class="n">entries</span><span class="p">]))[</span><span class="mf">2</span><span class="p">]</span> <span class="k">AS</span> <span class="n">last_value</span><span class="p">,</span>
<span class="n">AVG</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">AS</span> <span class="n">mean_entries</span><span class="p">,</span>
<span class="n">STDDEV</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">AS</span> <span class="n">stddev_entries</span>
<span class="k">FROM</span>
<span class="n">server_log_summary</span>
<span class="k">WHERE</span>
<span class="c1">-- In the demo data use:</span>
<span class="c1">-- period > '2020-08-01 17:00 UTC'::timestamptz</span>
<span class="n">period</span> <span class="o">></span> <span class="n">now</span><span class="p">()</span> <span class="o">-</span> <span class="nb">interval</span> <span class="s1">'1 hour'</span>
<span class="k">GROUP</span> <span class="k">BY</span>
<span class="n">status_code</span>
<span class="p">)</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">stats</span><span class="p">;</span>
<span class="go">status_code │ last_value │ mean_entries │ stddev_entries</span>
<span class="go">────────────┼────────────┼────────────────────────┼────────────────────────</span>
<span class="go"> 404 │ 0 │ 0.13333333333333333333 │ 0.34280333180088158345</span>
<span class="go"> 500 │ 0 │ 0.15000000000000000000 │ 0.36008473579027553993</span>
<span class="go"> 200 │ 4084 │ 2779.1000000000000000 │ 689.219644702665</span>
<span class="go"> 400 │ 24 │ 0.73333333333333333333 │ 3.4388935285299212</span>
</pre>
</div>
<p>
To get the last value in a GROUP BY in addition to the mean and standard deviation <a href="/sql-group-by-first-last-value">we used a little array trick</a>.
</p>
<p>
Next, we calculate the z-score for the last value for each status code:
</p>
<div class="highlight">
<pre><span class="gp">db=#</span> <span class="k">WITH</span> <span class="n">stats</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="p">(</span><span class="n">MAX</span><span class="p">(</span><span class="k">ARRAY</span><span class="p">[</span><span class="k">EXTRACT</span><span class="p">(</span><span class="s1">'epoch'</span> <span class="k">FROM</span> <span class="n">period</span><span class="p">),</span> <span class="n">entries</span><span class="p">]))[</span><span class="mf">2</span><span class="p">]</span> <span class="k">AS</span> <span class="n">last_value</span><span class="p">,</span>
<span class="n">AVG</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">AS</span> <span class="n">mean_entries</span><span class="p">,</span>
<span class="n">STDDEV</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">AS</span> <span class="n">stddev_entries</span>
<span class="k">FROM</span>
<span class="n">server_log_summary</span>
<span class="k">WHERE</span>
<span class="c1">-- In the demo data use:</span>
<span class="c1">-- period > '2020-08-01 17:00 UTC'::timestamptz</span>
<span class="n">period</span> <span class="o">></span> <span class="n">now</span><span class="p">()</span> <span class="o">-</span> <span class="nb">interval</span> <span class="s1">'1 hour'</span>
<span class="k">GROUP</span> <span class="k">BY</span>
<span class="n">status_code</span>
<span class="p">)</span>
<span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="hll"> <span class="p">(</span><span class="n">last_value</span> <span class="o">-</span> <span class="n">mean_entries</span><span class="p">)</span> <span class="o">/</span> <span class="k">NULLIF</span><span class="p">(</span><span class="n">stddev_entries</span><span class="o">::</span><span class="k">float</span><span class="p">,</span> <span class="mf">0</span><span class="p">)</span> <span class="k">as</span> <span class="n">zscore</span>
</span><span class="k">FROM</span>
<span class="n">stats</span><span class="p">;</span>
<span class="go">status_code │ last_value │ mean_entries │ stddev_entries │ zscore</span>
<span class="go">────────────┼────────────┼──────────────┼────────────────┼────────</span>
<span class="go"> 404 │ 0 │ 0.133 │ 0.3428 │ -0.388</span>
<span class="go"> 500 │ 0 │ 0.150 │ 0.3600 │ -0.416</span>
<span class="go"> 200 │ 4084 │ 2779.100 │ 689.2196 │ 1.893</span>
<span class="hll"><span class="go"> 400 │ 24 │ 0.733 │ 3.4388 │ 6.765</span>
</span></pre>
</div>
<p>
We calculated the z-score by finding the number of standard deviations between the last value and the mean. To <a href="/sql-dos-and-donts#guard-against-division-by-zero-errors">avoid a "division by zero" error</a> we transform the denominator to NULL if it's zero.
</p>
<p>
Looking at the z-scores we got, we can spot that status code 400 got a very high z-score of 6. In the past minute we returned a 400 status code 24 times, which is significantly higher than the average of 0.73 in the past hour.
</p>
<p>
Let's take a look at the raw data:
</p>
<div class="highlight">
<pre><span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">server_log_summary</span>
<span class="k">WHERE</span> <span class="n">status_code</span> <span class="o">=</span> <span class="mf">400</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">period</span> <span class="k">DESC</span>
<span class="k">LIMIT</span> <span class="mf">20</span><span class="p">;</span>
<span class="go"> period │ status_code │ entries</span>
<span class="go">───────────────────────┼─────────────┼─────────</span>
<span class="go">2020-08-01 18:00:00+00 │ 400 │ 24</span>
<span class="go">2020-08-01 17:59:00+00 │ 400 │ 12</span>
<span class="go">2020-08-01 17:58:00+00 │ 400 │ 2</span>
<span class="go">2020-08-01 17:57:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:56:00+00 │ 400 │ 1</span>
<span class="go">2020-08-01 17:55:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:54:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:53:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:52:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:51:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:50:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:49:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:48:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:47:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:46:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:45:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:44:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:43:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:42:00+00 │ 400 │ 0</span>
<span class="go">2020-08-01 17:41:00+00 │ 400 │ 0</span>
</pre>
</div>
<p>
It does look like in the last couple of minutes we are getting more errors than expected.
</p>
<figure>
<img alt="Status 400 in the past hour" src="https://hakibenita.com/images/00-sql-anomaly-detection-400.png" />
<figcaption>
Status 400 in the past hour
</figcaption>
</figure>
<p>
What our naked eye missed in the chart and in the raw data, was found by the query, and was classified as an anomaly. We are off to a great start!
</p>
<hr />
<h2 id="backtesting">
<a class="toclink" href="#backtesting">Backtesting</a>
</h2>
<p>
In the previous section we identified an anomaly. We found an increase in 400 status code because the z-score was 6. But how do we set the threshold for the z-score? Is a z-score of 3 an anomaly? What about 2, or 1?
</p>
<p>
To find thresholds that fit our needs, we can run simulations on past data with different values, and evaluate the results. This is often called backtesting.
</p>
<h3 id="finding-past-anomalies">
<a class="toclink" href="#finding-past-anomalies">Finding Past Anomalies</a>
</h3>
<p>
The first thing we need to do is to calculate the mean and the standard deviation for each status code up until every row, just as if it’s the current value. This is a classic job for a <a href="https://www.postgresql.org/docs/current/tutorial-window.html" rel="noopener">window function</a>:
</p>
<div class="highlight">
<pre><span class="k">WITH</span> <span class="n">calculations_over_window</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">entries</span><span class="p">,</span>
<span class="n">AVG</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">OVER</span> <span class="n">status_window</span> <span class="k">as</span> <span class="n">mean_entries</span><span class="p">,</span>
<span class="n">STDDEV</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">OVER</span> <span class="n">status_window</span> <span class="k">as</span> <span class="n">stddev_entries</span>
<span class="k">FROM</span>
<span class="n">server_log_summary</span>
<span class="k">WINDOW</span> <span class="n">status_window</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">status_code</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">period</span>
<span class="k">ROWS</span> <span class="k">BETWEEN</span> <span class="mf">60</span> <span class="k">PRECEDING</span> <span class="k">AND</span> <span class="k">CURRENT</span> <span class="k">ROW</span>
<span class="p">)</span>
<span class="p">)</span>
<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">calculations_over_window</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">period</span> <span class="k">DESC</span>
<span class="k">LIMIT</span> <span class="mf">20</span><span class="p">;</span>
<span class="go">status_code │ period │ entries │ mean_entries │ stddev_entries</span>
<span class="go">────────────┼────────────────────────┼─────────┼────────────────────────┼────────────────────────</span>
<span class="go"> 200 │ 2020-08-01 18:00:00+00 │ 4084 │ 2759.9672131147540984 │ 699.597407256800</span>
<span class="go"> 400 │ 2020-08-01 18:00:00+00 │ 24 │ 0.72131147540983606557 │ 3.4114080550460080</span>
<span class="go"> 404 │ 2020-08-01 18:00:00+00 │ 0 │ 0.13114754098360655738 │ 0.34036303344446665347</span>
<span class="go"> 500 │ 2020-08-01 18:00:00+00 │ 0 │ 0.14754098360655737705 │ 0.35758754516763638735</span>
<span class="go"> 500 │ 2020-08-01 17:59:00+00 │ 0 │ 0.16393442622950819672 │ 0.37328844382740000274</span>
<span class="go"> 400 │ 2020-08-01 17:59:00+00 │ 12 │ 0.32786885245901639344 │ 1.5676023249473471</span>
<span class="go"> 200 │ 2020-08-01 17:59:00+00 │ 3927 │ 2718.6721311475409836 │ 694.466863171826</span>
<span class="go"> 404 │ 2020-08-01 17:59:00+00 │ 0 │ 0.13114754098360655738 │ 0.34036303344446665347</span>
<span class="go"> 500 │ 2020-08-01 17:58:00+00 │ 0 │ 0.16393442622950819672 │ 0.37328844382740000274</span>
<span class="go"> 404 │ 2020-08-01 17:58:00+00 │ 0 │ 0.13114754098360655738 │ 0.34036303344446665347</span>
<span class="go"> 200 │ 2020-08-01 17:58:00+00 │ 3850 │ 2680.4754098360655738 │ 690.967283512936</span>
<span class="go"> 400 │ 2020-08-01 17:58:00+00 │ 2 │ 0.13114754098360655738 │ 0.38623869286861001780</span>
<span class="go"> 404 │ 2020-08-01 17:57:00+00 │ 0 │ 0.13114754098360655738 │ 0.34036303344446665347</span>
<span class="go"> 400 │ 2020-08-01 17:57:00+00 │ 0 │ 0.09836065573770491803 │ 0.30027309973793774423</span>
<span class="go"> 500 │ 2020-08-01 17:57:00+00 │ 1 │ 0.16393442622950819672 │ 0.37328844382740000274</span>
<span class="go"> 200 │ 2020-08-01 17:57:00+00 │ 3702 │ 2643.0327868852459016 │ 688.414796645480</span>
<span class="go"> 200 │ 2020-08-01 17:56:00+00 │ 3739 │ 2607.5081967213114754 │ 688.769908918569</span>
<span class="go"> 404 │ 2020-08-01 17:56:00+00 │ 0 │ 0.14754098360655737705 │ 0.35758754516763638735</span>
<span class="go"> 400 │ 2020-08-01 17:56:00+00 │ 1 │ 0.11475409836065573770 │ 0.32137001808599097120</span>
<span class="go"> 500 │ 2020-08-01 17:56:00+00 │ 0 │ 0.14754098360655737705 │ 0.35758754516763638735</span>
</pre>
</div>
<p>
To calculate the mean and standard deviation over a sliding window of 60 minutes, we use a <a href="https://www.postgresql.org/docs/current/tutorial-window.html" rel="noopener">window function</a>. To avoid having to repeat the <code>WINDOW</code> clause for every aggregate, we define a <a href="https://www.postgresql.org/docs/current/sql-select.html#SQL-WINDOW" rel="noopener">named window</a> called "status_window". This is another nice feature of PostgreSQL.
</p>
<p>
In the results we can now see that for every entry, we have the mean and standard deviation of the previous 60 rows. This is similar to the calculation we did in the previous section, only this time we do it for every row.
</p>
<p>
Now we can calculate the z-score for every row:
</p>
<div class="highlight">
<pre><span class="k">WITH</span> <span class="n">calculations_over_window</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">entries</span><span class="p">,</span>
<span class="n">AVG</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">OVER</span> <span class="n">status_window</span> <span class="k">as</span> <span class="n">mean_entries</span><span class="p">,</span>
<span class="n">STDDEV</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">OVER</span> <span class="n">status_window</span> <span class="k">as</span> <span class="n">stddev_entries</span>
<span class="k">FROM</span>
<span class="n">server_log_summary</span>
<span class="k">WINDOW</span> <span class="n">status_window</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">status_code</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">period</span>
<span class="k">ROWS</span> <span class="k">BETWEEN</span> <span class="mf">60</span> <span class="k">PRECEDING</span> <span class="k">AND</span> <span class="k">CURRENT</span> <span class="k">ROW</span>
<span class="p">)</span>
<span class="p">),</span>
<span class="hll"><span class="n">with_zscore</span> <span class="k">AS</span> <span class="p">(</span>
</span> <span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="hll"> <span class="p">(</span><span class="n">entries</span> <span class="o">-</span> <span class="n">mean_entries</span><span class="p">)</span> <span class="o">/</span> <span class="k">NULLIF</span><span class="p">(</span><span class="n">stddev_entries</span><span class="o">::</span><span class="k">float</span><span class="p">,</span> <span class="mf">0</span><span class="p">)</span> <span class="k">as</span> <span class="n">zscore</span>
</span> <span class="k">FROM</span>
<span class="n">calculations_over_window</span>
<span class="p">)</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">zscore</span>
<span class="k">FROM</span>
<span class="n">with_zscore</span>
<span class="k">ORDER</span> <span class="k">BY</span>
<span class="n">period</span> <span class="k">DESC</span>
<span class="k">LIMIT</span>
<span class="mf">20</span><span class="p">;</span>
<span class="go">status_code │ period │ zscore</span>
<span class="go">────────────┼────────────────────────┼──────────────────────</span>
<span class="go"> 200 │ 2020-08-01 18:00:00+00 │ 1.8925638848161648</span>
<span class="go"> 400 │ 2020-08-01 18:00:00+00 │ 6.823777205473068</span>
<span class="go"> 404 │ 2020-08-01 18:00:00+00 │ -0.38531664163524526</span>
<span class="go"> 500 │ 2020-08-01 18:00:00+00 │ -0.41260101365496504</span>
<span class="go"> 500 │ 2020-08-01 17:59:00+00 │ -0.4391628750910588</span>
<span class="go"> 400 │ 2020-08-01 17:59:00+00 │ 7.445849602151508</span>
<span class="go"> 200 │ 2020-08-01 17:59:00+00 │ 1.7399359608515874</span>
<span class="go"> 404 │ 2020-08-01 17:59:00+00 │ -0.38531664163524526</span>
<span class="go"> 500 │ 2020-08-01 17:58:00+00 │ -0.4391628750910588</span>
<span class="go"> 404 │ 2020-08-01 17:58:00+00 │ -0.38531664163524526</span>
<span class="go"> 200 │ 2020-08-01 17:58:00+00 │ 1.6925903990967166</span>
<span class="go"> 400 │ 2020-08-01 17:58:00+00 │ 4.838594613958412</span>
<span class="go"> 404 │ 2020-08-01 17:57:00+00 │ -0.38531664163524526</span>
<span class="go"> 400 │ 2020-08-01 17:57:00+00 │ -0.32757065425956844</span>
<span class="go"> 500 │ 2020-08-01 17:57:00+00 │ 2.2397306629644</span>
<span class="go"> 200 │ 2020-08-01 17:57:00+00 │ 1.5382691050147506</span>
<span class="go"> 200 │ 2020-08-01 17:56:00+00 │ 1.6427718293547886</span>
<span class="go"> 404 │ 2020-08-01 17:56:00+00 │ -0.41260101365496504</span>
<span class="go"> 400 │ 2020-08-01 17:56:00+00 │ 2.75460015502278</span>
<span class="go"> 500 │ 2020-08-01 17:56:00+00 │ -0.41260101365496504</span>
</pre>
</div>
<p>
We now have z-scores for every row, and we can try to identify anomalies:
</p>
<div class="highlight">
<pre><span class="k">WITH</span> <span class="n">calculations_over_window</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">entries</span><span class="p">,</span>
<span class="n">AVG</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">OVER</span> <span class="n">status_window</span> <span class="k">as</span> <span class="n">mean_entries</span><span class="p">,</span>
<span class="n">STDDEV</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">OVER</span> <span class="n">status_window</span> <span class="k">as</span> <span class="n">stddev_entries</span>
<span class="k">FROM</span>
<span class="n">server_log_summary</span>
<span class="k">WINDOW</span> <span class="n">status_window</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">status_code</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">period</span>
<span class="k">ROWS</span> <span class="k">BETWEEN</span> <span class="mf">60</span> <span class="k">PRECEDING</span> <span class="k">AND</span> <span class="k">CURRENT</span> <span class="k">ROW</span>
<span class="p">)</span>
<span class="p">),</span>
<span class="n">with_zscore</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="p">(</span><span class="n">entries</span> <span class="o">-</span> <span class="n">mean_entries</span><span class="p">)</span> <span class="o">/</span> <span class="k">NULLIF</span><span class="p">(</span><span class="n">stddev_entries</span><span class="o">::</span><span class="k">float</span><span class="p">,</span> <span class="mf">0</span><span class="p">)</span> <span class="k">as</span> <span class="n">zscore</span>
<span class="k">FROM</span>
<span class="n">calculations_over_window</span>
<span class="p">),</span>
<span class="n">with_alert</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="hll"> <span class="n">zscore</span> <span class="o">></span> <span class="mf">3</span> <span class="k">AS</span> <span class="n">alert</span>
</span> <span class="k">FROM</span>
<span class="n">with_zscore</span>
<span class="p">)</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">entries</span><span class="p">,</span>
<span class="n">zscore</span><span class="p">,</span>
<span class="n">alert</span>
<span class="k">FROM</span>
<span class="n">with_alert</span>
<span class="k">WHERE</span>
<span class="n">alert</span>
<span class="k">ORDER</span> <span class="k">BY</span>
<span class="n">period</span> <span class="k">DESC</span>
<span class="k">LIMIT</span>
<span class="mf">20</span><span class="p">;</span>
<span class="go">status_code │ period │ entries │ zscore │ alert</span>
<span class="go">────────────┼────────────────────────┼─────────┼────────────────────┼───────</span>
<span class="go"> 400 │ 2020-08-01 18:00:00+00 │ 24 │ 6.823777205473068 │ t</span>
<span class="go"> 400 │ 2020-08-01 17:59:00+00 │ 12 │ 7.445849602151508 │ t</span>
<span class="go"> 400 │ 2020-08-01 17:58:00+00 │ 2 │ 4.838594613958412 │ t</span>
<span class="go"> 500 │ 2020-08-01 17:29:00+00 │ 1 │ 3.0027309973793774 │ t</span>
<span class="go"> 500 │ 2020-08-01 17:20:00+00 │ 1 │ 3.3190952747131184 │ t</span>
<span class="go"> 500 │ 2020-08-01 17:18:00+00 │ 1 │ 3.7438474117708043 │ t</span>
<span class="go"> 500 │ 2020-08-01 17:13:00+00 │ 1 │ 3.7438474117708043 │ t</span>
<span class="go"> 500 │ 2020-08-01 17:09:00+00 │ 1 │ 4.360778994930029 │ t</span>
<span class="go"> 500 │ 2020-08-01 16:59:00+00 │ 1 │ 3.7438474117708043 │ t</span>
<span class="go"> 400 │ 2020-08-01 16:29:00+00 │ 1 │ 3.0027309973793774 │ t</span>
<span class="go"> 404 │ 2020-08-01 16:13:00+00 │ 1 │ 3.0027309973793774 │ t</span>
<span class="go"> 500 │ 2020-08-01 15:13:00+00 │ 1 │ 3.0027309973793774 │ t</span>
<span class="go"> 500 │ 2020-08-01 15:11:00+00 │ 1 │ 3.0027309973793774 │ t</span>
<span class="go"> 500 │ 2020-08-01 14:58:00+00 │ 1 │ 3.0027309973793774 │ t</span>
<span class="go"> 400 │ 2020-08-01 14:56:00+00 │ 1 │ 3.0027309973793774 │ t</span>
<span class="go"> 400 │ 2020-08-01 14:55:00+00 │ 1 │ 3.3190952747131184 │ t</span>
<span class="go"> 400 │ 2020-08-01 14:50:00+00 │ 1 │ 3.3190952747131184 │ t</span>
<span class="go"> 500 │ 2020-08-01 14:37:00+00 │ 1 │ 3.0027309973793774 │ t</span>
<span class="go"> 400 │ 2020-08-01 14:35:00+00 │ 1 │ 3.3190952747131184 │ t</span>
<span class="go"> 400 │ 2020-08-01 14:32:00+00 │ 1 │ 3.3190952747131184 │ t</span>
</pre>
</div>
<p>
We decided to classify values with z-score greater than 3 as anomalies. 3 is usually the magic number you’ll see in textbooks, but don’t get sentimental about it because you can definitely change it to get better results.
</p>
<h3 id="adding-thresholds">
<a class="toclink" href="#adding-thresholds">Adding Thresholds</a>
</h3>
<p>
In the last query we detected a large number of "anomalies" with just one entry. This is very common in errors that don't happen very often. In our case, every once in a while we get a 400 status code, but because it doesn't happen very often, the standard deviation is very low so that even a single error can be considered way above the acceptable value.
</p>
<p>
We don't really want to receive an alert in the middle of the night just because of one 400 status code. We can't have every curious developer fiddling with the devtools in his browser wake us up in the middle of the night.
</p>
<p>
To eliminate rows with only a few entries we set a threshold:
</p>
<div class="highlight">
<pre><span class="k">WITH</span> <span class="n">calculations_over_window</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">entries</span><span class="p">,</span>
<span class="n">AVG</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">OVER</span> <span class="n">status_window</span> <span class="k">as</span> <span class="n">mean_entries</span><span class="p">,</span>
<span class="n">STDDEV</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">OVER</span> <span class="n">status_window</span> <span class="k">as</span> <span class="n">stddev_entries</span>
<span class="k">FROM</span>
<span class="n">server_log_summary</span>
<span class="k">WINDOW</span> <span class="n">status_window</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">status_code</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">period</span>
<span class="k">ROWS</span> <span class="k">BETWEEN</span> <span class="mf">60</span> <span class="k">PRECEDING</span> <span class="k">AND</span> <span class="k">CURRENT</span> <span class="k">ROW</span>
<span class="p">)</span>
<span class="p">),</span>
<span class="n">with_zscore</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="p">(</span><span class="n">entries</span> <span class="o">-</span> <span class="n">mean_entries</span><span class="p">)</span> <span class="o">/</span> <span class="k">NULLIF</span><span class="p">(</span><span class="n">stddev_entries</span><span class="o">::</span><span class="k">float</span><span class="p">,</span> <span class="mf">0</span><span class="p">)</span> <span class="k">as</span> <span class="n">zscore</span>
<span class="k">FROM</span>
<span class="n">calculations_over_window</span>
<span class="p">),</span>
<span class="n">with_alert</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="hll"> <span class="n">entries</span> <span class="o">></span> <span class="mf">10</span> <span class="k">AND</span> <span class="n">zscore</span> <span class="o">></span> <span class="mf">3</span> <span class="k">AS</span> <span class="n">alert</span>
</span> <span class="k">FROM</span>
<span class="n">with_zscore</span>
<span class="p">)</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">entries</span><span class="p">,</span>
<span class="n">zscore</span><span class="p">,</span>
<span class="n">alert</span>
<span class="k">FROM</span>
<span class="n">with_alert</span>
<span class="k">WHERE</span>
<span class="n">alert</span>
<span class="k">ORDER</span> <span class="k">BY</span>
<span class="n">period</span> <span class="k">DESC</span><span class="p">;</span>
<span class="go">status_code │ period │ entries │ zscore │ alert</span>
<span class="go">────────────┼────────────────────────┼─────────┼────────────────────┼───────</span>
<span class="go"> 400 │ 2020-08-01 18:00:00+00 │ 24 │ 6.823777205473068 │ t</span>
<span class="go"> 400 │ 2020-08-01 17:59:00+00 │ 12 │ 7.445849602151508 │ t</span>
<span class="go"> 500 │ 2020-08-01 11:29:00+00 │ 5001 │ 3.172198441961645 │ t</span>
<span class="go"> 500 │ 2020-08-01 11:28:00+00 │ 4812 │ 3.3971646910263917 │ t</span>
<span class="go"> 500 │ 2020-08-01 11:27:00+00 │ 4443 │ 3.5349400089601586 │ t</span>
<span class="go"> 500 │ 2020-08-01 11:26:00+00 │ 4522 │ 4.1264785335553595 │ t</span>
<span class="go"> 500 │ 2020-08-01 11:25:00+00 │ 5567 │ 6.17629336121081 │ t</span>
<span class="go"> 500 │ 2020-08-01 11:24:00+00 │ 3657 │ 6.8689992361141154 │ t</span>
<span class="go"> 500 │ 2020-08-01 11:23:00+00 │ 1512 │ 6.342260662589681 │ t</span>
<span class="go"> 500 │ 2020-08-01 11:22:00+00 │ 1022 │ 7.682189672504754 │ t</span>
<span class="go"> 404 │ 2020-08-01 07:20:00+00 │ 23 │ 5.142126410098476 │ t</span>
<span class="go"> 404 │ 2020-08-01 07:19:00+00 │ 20 │ 6.091200697920824 │ t</span>
<span class="go"> 404 │ 2020-08-01 07:18:00+00 │ 15 │ 7.57547172423804 │ t</span>
</pre>
</div>
<p>
After eliminating potential anomalies with less than 10 entries we get much fewer, and probably more relevant results.
</p>
<h3 id="eliminating-repeating-alerts">
<a class="toclink" href="#eliminating-repeating-alerts">Eliminating Repeating Alerts</a>
</h3>
<p>
In the previous section we eliminated potential anomalies with less than 10 entries. Using thresholds we were able to remove some non interesting anomalies.
</p>
<p>
Let's have a look at the data for status code 400 after applying the threshold:
</p>
<div class="highlight">
<pre>status_code │ period │ entries │ zscore │ alert
────────────┼────────────────────────┼─────────┼────────────────────┼───────
400 │ 2020-08-01 18:00:00+00 │ 24 │ 6.823777205473068 │ t
400 │ 2020-08-01 17:59:00+00 │ 12 │ 7.445849602151508 │ t
</pre>
</div>
<p>
The first alert happened in 17:59, and a minute later the z-score was still high with a large number of entries and so we classified the next rows at 18:00 as an anomaly as well.
</p>
<p>
If you think of an alerting system, we want to send an alert only when an anomaly first happens. We don't want to send an alert every minute until the z-score comes back below the threshold. In this case, we only want to send one alert at 17:59. We don't want to send <em>another</em> alert a minute later at 18:00.
</p>
<p>
Let's remove alerts where the previous period was also classified as an alert:
</p>
<div class="highlight">
<pre><span class="k">WITH</span> <span class="n">calculations_over_window</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">entries</span><span class="p">,</span>
<span class="n">AVG</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">OVER</span> <span class="n">status_window</span> <span class="k">as</span> <span class="n">mean_entries</span><span class="p">,</span>
<span class="n">STDDEV</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">OVER</span> <span class="n">status_window</span> <span class="k">as</span> <span class="n">stddev_entries</span>
<span class="k">FROM</span>
<span class="n">server_log_summary</span>
<span class="k">WINDOW</span> <span class="n">status_window</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">status_code</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">period</span>
<span class="k">ROWS</span> <span class="k">BETWEEN</span> <span class="mf">60</span> <span class="k">PRECEDING</span> <span class="k">AND</span> <span class="k">CURRENT</span> <span class="k">ROW</span>
<span class="p">)</span>
<span class="p">),</span>
<span class="n">with_zscore</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="p">(</span><span class="n">entries</span> <span class="o">-</span> <span class="n">mean_entries</span><span class="p">)</span> <span class="o">/</span> <span class="k">NULLIF</span><span class="p">(</span><span class="n">stddev_entries</span><span class="o">::</span><span class="k">float</span><span class="p">,</span> <span class="mf">0</span><span class="p">)</span> <span class="k">as</span> <span class="n">zscore</span>
<span class="k">FROM</span>
<span class="n">calculations_over_window</span>
<span class="p">),</span>
<span class="n">with_alert</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="n">entries</span> <span class="o">></span> <span class="mf">10</span> <span class="k">AND</span> <span class="n">zscore</span> <span class="o">></span> <span class="mf">3</span> <span class="k">AS</span> <span class="n">alert</span>
<span class="k">FROM</span>
<span class="n">with_zscore</span>
<span class="p">),</span>
<span class="n">with_previous_alert</span> <span class="k">AS</span> <span class="p">(</span>
<span class="k">SELECT</span>
<span class="o">*</span><span class="p">,</span>
<span class="hll"> <span class="n">LAG</span><span class="p">(</span><span class="n">alert</span><span class="p">)</span> <span class="k">OVER</span> <span class="p">(</span><span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">status_code</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">period</span><span class="p">)</span> <span class="k">AS</span> <span class="n">previous_alert</span>
</span> <span class="k">FROM</span>
<span class="n">with_alert</span>
<span class="p">)</span>
<span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">period</span><span class="p">,</span>
<span class="n">entries</span><span class="p">,</span>
<span class="n">zscore</span><span class="p">,</span>
<span class="n">alert</span>
<span class="k">FROM</span>
<span class="n">with_previous_alert</span>
<span class="k">WHERE</span>
<span class="hll"> <span class="n">alert</span> <span class="k">AND</span> <span class="k">NOT</span> <span class="n">previous_alert</span>
</span><span class="k">ORDER</span> <span class="k">BY</span>
<span class="n">period</span> <span class="k">DESC</span><span class="p">;</span>
<span class="go">status_code │ period │ entries │ zscore │ alert</span>
<span class="go">────────────┼────────────────────────┼─────────┼───────────────────┼───────</span>
<span class="go"> 400 │ 2020-08-01 17:59:00+00 │ 12 │ 7.445849602151508 │ t</span>
<span class="go"> 500 │ 2020-08-01 11:22:00+00 │ 1022 │ 7.682189672504754 │ t</span>
<span class="go"> 404 │ 2020-08-01 07:18:00+00 │ 15 │ 7.57547172423804 │ t</span>
</pre>
</div>
<p>
By eliminating alerts that were already triggered we get a very small list of anomalies that may have happened during the day. Looking at the results we can see what anomalies we would have discovered:
</p>
<ul>
<li>Anomaly in status code 400 at 17:59: we also found that one earlier.
</li>
</ul>
<figure>
<img alt="Anomaly in status code 400" src="https://hakibenita.com/images/00-sql-anomaly-detection-400.png" />
<figcaption>
Anomaly in status code 400
</figcaption>
</figure>
<ul>
<li>Anomaly in status code 500: we spotted this one on the chart when we started.
</li>
</ul>
<figure>
<img alt="Anomaly in status code 500" src="https://hakibenita.com/images/00-sql-anomaly-detection-500.png" />
<figcaption>
Anomaly in status code 500
</figcaption>
</figure>
<ul>
<li>Anomaly in status code 404: this is a hidden hidden anomaly which we did not know about until now.
</li>
</ul>
<figure>
<img alt="A hidden anomaly in status code 404" src="https://hakibenita.com/images/00-sql-anomaly-detection-404.png" />
<figcaption>
A hidden anomaly in status code 404
</figcaption>
</figure>
<p>
The query can now be used to fire alerts when it encounters an anomaly.
</p>
<h3 id="experiment-with-different-values">
<a class="toclink" href="#experiment-with-different-values">Experiment With Different Values</a>
</h3>
<p>
In the process so far we’ve used several constants in our calculations:
</p>
<ul>
<li>
<strong>Lookback period</strong>: How far back we calculate the mean and standard deviation for each status code. The value we used is 60 minutes.
</li>
<li>
<strong>Entries Threshold</strong>: The least amount of entries we want to get an alert for. The value we used is 10.
</li>
<li>
<strong>Z-Score Threshold</strong>: The z-score after which we classify the value as an anomaly. The value we used is 6.
</li>
</ul>
<p>
Now that we have a working query to backtest, we can experiment with different values.
</p>
<figure>
<img alt="Experimenting with parameter values" src="https://hakibenita.com/images/00-sql-anomaly-detection-parameters.png" />
<figcaption>
Experimenting with parameter values
</figcaption>
</figure>
<p>
This is a chart showing the alerts our system identified in the past 12 hours:
</p>
<figure>
<img alt="Backtesting with default parameters. <a href="https://popsql.com/queries/-MECQV6GiKr04WdCWM0K/simple-anomaly-detection-with-sql?access_token=2d2c0729f9a1cfa7b6a2dbb5b0adb45c">View in editor</a>" src="https://hakibenita.com/images/00-sql-anomaly-detection-backtest-10-3-60.png" />
<figcaption>
Backtesting with default parameters. <a href="https://popsql.com/queries/-MECQV6GiKr04WdCWM0K/simple-anomaly-detection-with-sql?access_token=2d2c0729f9a1cfa7b6a2dbb5b0adb45c">View in editor</a>
</figcaption>
</figure>
<p>
To get a sense of each parameter, let's adjust the values and see how it affects the number and quality of alerts we get.
</p>
<p>
If we decrease the value of the z-score threshold from 3 to 1, we should get more alerts. With a lower threshold, more values are likely to be considered an anomaly:
</p>
<figure>
<img alt="Backtesting with lower z-score threshold" src="https://hakibenita.com/images/00-sql-anomaly-detection-backtest-10-1-60.png" />
<figcaption>
Backtesting with lower z-score threshold
</figcaption>
</figure>
<p>
If we increase the entries threshold from 10 to 30, we should get less alerts:
</p>
<figure>
<img alt="Backtesting with higher entries threshold" src="https://hakibenita.com/images/00-sql-anomaly-detection-backtest-30-3-60.png" />
<figcaption>
Backtesting with higher entries threshold
</figcaption>
</figure>
<p>
If we increase the backtest period from 60 minutes to 360 minutes, we get more alerts:
</p>
<figure>
<img alt="Backtesting with higher entries threshold" src="https://hakibenita.com/images/00-sql-anomaly-detection-backtest-30-3-360.png" />
<figcaption>
Backtesting with higher entries threshold
</figcaption>
</figure>
<p>
A good alerting system is a system that produces true alerts, at a reasonable time. Using the backtesting query you can experiment with different values that produces quality alerts you can act on.
</p>
<hr />
<h2 id="improving-accuracy">
<a class="toclink" href="#improving-accuracy">Improving Accuracy</a>
</h2>
<p>
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 don't get good alerts using this method, there are some improvements and other methods you can try using just SQL.
</p>
<h3 id="use-weighted-mean">
<a class="toclink" href="#use-weighted-mean">Use Weighted Mean</a>
</h3>
<p>
Our system uses a mean to determine a reasonable value, and a lookback period to determine how long back to calculate that mean over. In our case, we calculated the mean based on data from 1 hour ago.
</p>
<p>
Using this method of calculating mean gives the same weight to entries that happened 1 hour ago and to entries that just happened. If you 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.
</p>
<p>
To give more weight to recent entries, you can use a <a href="https://en.wikipedia.org/wiki/Weighted_arithmetic_mean" rel="noopener">weighted average</a>:
</p>
<div class="highlight">
<pre><span class="k">SELECT</span>
<span class="n">status_code</span><span class="p">,</span>
<span class="n">avg</span><span class="p">(</span><span class="n">entries</span><span class="p">)</span> <span class="k">as</span> <span class="n">mean</span><span class="p">,</span>
<span class="n">sum</span><span class="p">(</span>
<span class="n">entries</span> <span class="o">*</span>
<span class="p">(</span><span class="mf">60</span> <span class="o">-</span> <span class="k">extract</span><span class="p">(</span><span class="s1">'seconds'</span> <span class="k">from</span> <span class="s1">'2020-08-01 17:00 UTC'</span><span class="o">::</span><span class="nb">timestamptz</span> <span class="o">-</span> <span class="n">period</span><span class="p">))</span>
<span class="p">)</span> <span class="o">/</span> <span class="p">(</span><span class="mf">60</span> <span class="o">*</span> <span class="mf">61</span> <span class="o">/</span> <span class="mf">2</span><span class="p">)</span> <span class="k">as</span> <span class="n">weighted_mean</span>
<span class="k">FROM</span>
<span class="n">server_log_summary</span>
<span class="k">WHERE</span>
<span class="c1">-- Last 60 periods</span>
<span class="n">period</span> <span class="o">></span> <span class="s1">'2020-08-01 17:00 UTC'</span><span class="o">::</span><span class="nb">timestamptz</span>
<span class="k">GROUP</span> <span class="k">BY</span>
<span class="n">status_code</span><span class="p">;</span>
<span class="go"> status_code │ mean │ weighted_mean</span>
<span class="go">─────────────┼────────────────────────┼─────────────────────</span>
<span class="go"> 404 │ 0.13333333333333333333 │ 0.26229508196721313</span>
<span class="go"> 500 │ 0.15000000000000000000 │ 0.29508196721311475</span>
<span class="go"> 200 │ 2779.1000000000000000 │ 5467.081967213115</span>
<span class="go"> 400 │ 0.73333333333333333333 │ 1.4426229508196722</span>
</pre>
</div>
<p>
In the results you can see the difference between the mean and the weighted mean for each status code.
</p>
<p>
A weighted average is a very <a href="https://www.investopedia.com/ask/answers/071414/whats-difference-between-moving-average-and-weighted-moving-average.asp" rel="noopener">common indicator used by stock traders</a>. We used a linear weighted average, but there are also exponential weighted averages and others you can try.
</p>
<h3 id="use-median">
<a class="toclink" href="#use-median">Use Median</a>
</h3>
<p>
In statistics, a mean is considered not robust because it is influenced by extreme values. Given our use case, the measure we are using to identify extreme values, is affected by those values we are trying to identify.
</p>
<p>
For example, in the beginning of the article we used this series of values:
</p>
<div class="highlight">
<pre>2, 3, 5, 2, 3, 12, 5, 3, 4
</pre>
</div>
<p>
The mean of this series is 4.33, and we detected 12 as an anomaly.
</p>
<p>
If the 12 were a 120, the mean of the series would have been 16.33. Hence, our "reasonable" value is heavily affected by the values it is supposed to identify.
</p>
<p>
A measure that is considered more robust is a <a href="https://en.wikipedia.org/wiki/Median" rel="noopener">median</a>. The median of a series is the value that half the series is greater than, and half the series is less than:
</p>
<div class="highlight">
<pre><span class="k">SELECT</span> <span class="n">percentile_disc</span><span class="p">(</span><span class="mf">0.5</span><span class="p">)</span> <span class="k">within</span> <span class="k">group</span><span class="p">(</span><span class="k">order</span> <span class="k">by</span> <span class="n">n</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">unnest</span><span class="p">(</span><span class="k">ARRAY</span><span class="p">[</span><span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">2</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">120</span><span class="p">,</span> <span class="mf">5</span><span class="p">,</span> <span class="mf">3</span><span class="p">,</span> <span class="mf">4</span><span class="p">])</span> <span class="k">as</span> <span class="n">n</span><span class="p">;</span>
<span class="go"> median</span>
<span class="go">────────</span>
<span class="go"> 3</span>
</pre>
</div>
<p>
To calculate the median in PostgreSQL we use the function <a href="https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE" rel="noopener"><code>percentile_disc</code></a>. In the series above, the median is 3. If we sort the list and cut it in the middle it will become more clear:
</p>
<div class="highlight">
<pre>2, 2, 3, 3, 3
4, 5, 5, 12
</pre>
</div>
<p>
If we change the value of 12 to 120, the median will not be affected at all:
</p>
<div class="highlight">
<pre>2, 2, 3, 3, 3
4, 5, 5, 120
</pre>
</div>
<p>
This is why a median is considered more robust than mean.
</p>
<h3 id="use-mad">
<a class="toclink" href="#use-mad">Use MAD</a>
</h3>
<p>
<a href="https://en.wikipedia.org/wiki/Median_absolute_deviation" rel="noopener">Median absolute deviation (MAD)</a> is another way of finding anomalies in a series. MAD is considered better than z-score for real life data.
</p>
<p>
MAD is calculated by finding the median of the deviations from the series median. Just for comparison, the standard deviation is the root square of the average square distance from the mean.
</p>
<h3 id="use-different-measures">
<a class="toclink" href="#use-different-measures">Use Different Measures</a>
</h3>
<p>
We used the number of entries per minute as an indicator. However, depending on the use case, there might be other things you can measure that can yield better results. For example:
</p>
<ul>
<li>To try and identify DOS attacks you can monitor the ratio between unique IP addresses to HTTP requests.
</li>
<li>To reduce the amount of false positives, you can normalize the number of responses to the proportion of the total responses. This way, for example, if you're using a flaky remote service that fails once after every certain amount of requests, using the proportion may not trigger an alert when the increase in errors correlates with an increase in overall traffic.
</li>
</ul>
<hr />
<h2 id="conclusion">
<a class="toclink" href="#conclusion">Conclusion</a>
</h2>
<p>
The method presented above is a very simple method to detect anomalies and produce actionable alerts that can potentially save you a lot of grief. There are many tools out there that provide similar functionally, but they require either tight integration or $$$. The main appeal of this approach is that you can get started with tools you probably already have, some SQL and a scheduled task!
</p>
<hr />
<p>
<strong>UPDATE:</strong> many readers asked me how I created the charts in this article... well, I used <a href="https://popsql.com" rel="noopener">PopSQL</a>. It’s a new modern SQL editor focused on collaborative editing. If you're in the market for one, go check it out...
</p>
</article>
<hr />
<section class="subscribe">
<div class="subscribe__image">
<img src="/images/subscribe.png" />
</div>
<form method="post" action="https://mailer.hakibenita.com/subscribe" name="subscribe" validate="" id="subscribe">
<h5>
Want me to send you an email<br />
when I publish something new?
</h5><input type="hidden" name="mailinglist" value="articles@hakibenita.com" /> <input type="hidden" name="token" value="d38VSLEkO15jvpilOSZmATRc6iqEkQM0fyrSsRjmrok=" /> <input type="hidden" name="redirecturl" value="https://hakibenita.com/subscribed" /> <input type="hidden" name="article" value="Simple Anomaly Detection Using Plain SQL" /> <input type="hidden" name="tag" value="SQL" />
<div class="subscribe__input">
<input type="email" name="email" placeholder="your@email.com" required="" /> <input type="submit" value="YES!" />
</div>
</form>
<script>
<![CDATA[
(function(document) {
document.querySelector('form[name=subscribe]').addEventListener('submit', function(e) {
e.target.querySelector('input[type=submit]').disabled = true;
});
})(document);
]]>
</script>
</section>
<hr />
<section class="share">
<h5>
Share to show you care
</h5>
<ul class="share-icons">
<li>
<a href="mailto:?subject=Simple%20Anomaly%20Detection%20Using%20Plain%20SQL%20by%20Haki%20Benita&body=https%3A//hakibenita.com/sql-anomaly-detection" title="Email"><svg viewbox="0 0 24 24" width="1em" height="1em" fill="none" stroke="currentColor" xmlns="http://www.w3.org/2000/svg">
<title>
Email
</title>
<path d="M4 4h16c1.1 0 2 .9 2 2v12c0 1.1-.9 2-2 2H4c-1.1 0-2-.9-2-2V6c0-1.1.9-2 2-2z"></path>
<polyline points="22,6 12,13 2,6"></polyline></svg></a>
</li>
<li>
<a href="https://twitter.com/intent/tweet/?text=Simple%20Anomaly%20Detection%20Using%20Plain%20SQL%20by%20Haki%20Benita&url=https%3A//hakibenita.com/sql-anomaly-detection&via=be_haki&hashtags=SQL" target="_blank" title="Twitter" aria-label="share via twitter"><svg role="img" viewbox="0 0 24 24" width="1em" height="1em" xmlns="http://www.w3.org/2000/svg" fill="currentColor">
<title>
Twitter
</title>
<path d="M23.954 4.569c-.885.389-1.83.654-2.825.775 1.014-.611 1.794-1.574 2.163-2.723-.951.555-2.005.959-3.127 1.184-.896-.959-2.173-1.559-3.591-1.559-2.717 0-4.92 2.203-4.92 4.917 0 .39.045.765.127 1.124C7.691 8.094 4.066 6.13 1.64 3.161c-.427.722-.666 1.561-.666 2.475 0 1.71.87 3.213 2.188 4.096-.807-.026-1.566-.248-2.228-.616v.061c0 2.385 1.693 4.374 3.946 4.827-.413.111-.849.171-1.296.171-.314 0-.615-.03-.916-.086.631 1.953 2.445 3.377 4.604 3.417-1.68 1.319-3.809 2.105-6.102 2.105-.39 0-.779-.023-1.17-.067 2.189 1.394 4.768 2.209 7.557 2.209 9.054 0 13.999-7.496 13.999-13.986 0-.209 0-.42-.015-.63.961-.689 1.8-1.56 2.46-2.548l-.047-.02z"></path></svg></a>
</li>
<li>
<a href="https://facebook.com/sharer/sharer.php?u=https%3A//hakibenita.com/sql-anomaly-detection" target="_blank" title="Facebook" aria-label="Share on Facebook"><svg role="img" viewbox="0 0 24 24" width="1em" height="1em" xmlns="http://www.w3.org/2000/svg" fill="currentColor">
<title>
Facebook
</title>
<path d="M22.676 0H1.324C.593 0 0 .593 0 1.324v21.352C0 23.408.593 24 1.324 24h11.494v-9.294H9.689v-3.621h3.129V8.41c0-3.099 1.894-4.785 4.659-4.785 1.325 0 2.464.097 2.796.141v3.24h-1.921c-1.5 0-1.792.721-1.792 1.771v2.311h3.584l-.465 3.63H16.56V24h6.115c.733 0 1.325-.592 1.325-1.324V1.324C24 .593 23.408 0 22.676 0"></path></svg></a>
</li>
<li>
<a href="https://www.reddit.com/submit?url=https%3A//hakibenita.com/sql-anomaly-detection&title=Simple%20Anomaly%20Detection%20Using%20Plain%20SQL%20by%20Haki%20Benita" target="_blank" title="Reddit" aria-label="Submit to Reddit"><svg role="img" viewbox="0 0 24 24" width="1em" height="1em" xmlns="http://www.w3.org/2000/svg" fill="currentColor">
<title>
Reddit
</title>
<path d="M2.204 14.049c-.06.276-.091.56-.091.847 0 3.443 4.402 6.249 9.814 6.249 5.41 0 9.812-2.804 9.812-6.249 0-.274-.029-.546-.082-.809l-.015-.032c-.021-.055-.029-.11-.029-.165-.302-1.175-1.117-2.241-2.296-3.103-.045-.016-.088-.039-.126-.07-.026-.02-.045-.042-.067-.064-1.792-1.234-4.356-2.008-7.196-2.008-2.815 0-5.354.759-7.146 1.971-.014.018-.029.033-.049.049-.039.033-.084.06-.13.075-1.206.862-2.042 1.937-2.354 3.123 0 .058-.014.114-.037.171l-.008.015zm9.773 5.441c-1.794 0-3.057-.389-3.863-1.197-.173-.174-.173-.457 0-.632.176-.165.46-.165.635 0 .63.629 1.685.943 3.228.943 1.542 0 2.591-.3 3.219-.929.165-.164.45-.164.629 0 .165.18.165.465 0 .645-.809.808-2.065 1.198-3.862 1.198l.014-.028zm-3.606-7.573c-.914 0-1.677.765-1.677 1.677 0 .91.763 1.65 1.677 1.65s1.651-.74 1.651-1.65c0-.912-.739-1.677-1.651-1.677zm7.233 0c-.914 0-1.678.765-1.678 1.677 0 .91.764 1.65 1.678 1.65s1.651-.74 1.651-1.65c0-.912-.739-1.677-1.651-1.677zm4.548-1.595c1.037.833 1.8 1.821 2.189 2.904.45-.336.719-.864.719-1.449 0-1.002-.815-1.816-1.818-1.816-.399 0-.778.129-1.09.363v-.002zM2.711 9.963c-1.003 0-1.817.816-1.817 1.818 0 .543.239 1.048.644 1.389.401-1.079 1.172-2.053 2.213-2.876-.302-.21-.663-.329-1.039-.329v-.002zm9.217 12.079c-5.906 0-10.709-3.205-10.709-7.142 0-.275.023-.544.068-.809C.494 13.598 0 12.729 0 11.777c0-1.496 1.227-2.713 2.725-2.713.674 0 1.303.246 1.797.682 1.856-1.191 4.357-1.941 7.112-1.992l1.812-5.524.404.095s.016 0 .016.002l4.223.993c.344-.798 1.138-1.36 2.065-1.36 1.229 0 2.231 1.004 2.231 2.234 0 1.232-1.003 2.234-2.231 2.234s-2.23-1.004-2.23-2.23l-3.851-.912-1.467 4.477c2.65.105 5.047.854 6.844 2.021.494-.464 1.144-.719 1.833-.719 1.498 0 2.718 1.213 2.718 2.711 0 .987-.54 1.886-1.378 2.365.029.255.059.494.059.749-.015 3.938-4.806 7.143-10.72 7.143l-.034.009zm8.179-19.187c-.74 0-1.34.599-1.34 1.338 0 .738.6 1.34 1.34 1.34.732 0 1.33-.6 1.33-1.334 0-.733-.598-1.332-1.347-1.332l.017-.012z"></path></svg></a>
</li>
<li>
<a style="display: none;" href="#" data-share="Simple Anomaly Detection Using Plain SQL by Haki Benita" data-share-url="sql-anomaly-detection" title="Native"><svg width="1em" height="1em" viewbox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="1.5" xmlns="http://www.w3.org/2000/svg">
<title>
Share
</title>
<circle fill="currentColor" cx="18" cy="5" r="3"></circle>
<circle fill="currentColor" cx="6" cy="12" r="3"></circle>
<circle fill="currentColor" cx="18" cy="19" r="3"></circle>
<line x1="8.59" y1="13.51" x2="15.42" y2="17.49"></line>
<line x1="15.41" y1="6.51" x2="8.59" y2="10.49"></line></svg></a>
</li>
</ul>
<script>
<![CDATA[
(function(window, document) {
navigator.share && document.addEventListener('DOMContentLoaded', () => {
const share = document.querySelector('[data-share]');
share.style.display = 'inherit';
share.addEventListener('click', e => {
e.preventDefault();
navigator.share({
title: share.getAttribute('data-share'),
url: share.getAttribute('data-share-url'),
}).then(() => console.info('shared!'));
});
});
[].forEach.call(document.querySelectorAll('.share-icons a[title]'), link => {
link.addEventListener('click', () => {
ga('send', 'social', link.title, 'share', window.location.href);
ga('send', 'event', 'share', link.title, window.location.href, 1);
});
});
})(window, document);
]]>
</script>
</section>
<hr />
<section class="admonition tip">
<h4 class="admonition-title">
Similar articles
</h4>
<ul class="similar_posts">
<li>
<ul class="article__info">
<li>
<time class="published" datetime="2020-07-27">27 July 2020</time>
</li>
<li>
<a class="tag" href="https://hakibenita.com/tag/sql">SQL</a>, <a class="tag" href="https://hakibenita.com/tag/performance">Performance</a>
</li>
</ul><a href="sql-tricks-application-dba">
<h4>
Some SQL Tricks of an Application DBA
</h4>
<p>
Non-trivial tips for database development
</p></a>
</li>
<li>
<ul class="article__info">
<li>
<time class="published" datetime="2019-08-13">13 August 2019</time>
</li>
<li>
<a class="tag" href="https://hakibenita.com/tag/postgresql">PostgreSQL</a>, <a class="tag" href="https://hakibenita.com/tag/sql">SQL</a>
</li>
</ul><a href="sql-group-by-first-last-value">
<h4>
How to Get the First or Last Value in a Group Using Group By in SQL
</h4>
<p>
A neat little trick using arrays in PostgreSQL
</p></a>
</li>
<li>
<ul class="article__info">
<li>
<time class="published" datetime="2017-05-11">11 May 2017</time>
</li>
<li>
<a class="tag" href="https://hakibenita.com/tag/postgresql">PostgreSQL</a>, <a class="tag" href="https://hakibenita.com/tag/sql">SQL</a>
</li>
</ul><a href="the-many-faces-of-distinct-in-postgre-sql">
<h4>
The Many Faces of DISTINCT in PostgreSQL
</h4>
<p>
3 interesting uses of DISTINCT in PostgreSQL
</p></a>
</li>
<li>
<ul class="article__info">
<li>
<time class="published" datetime="2020-02-11">11 February 2020</time>
</li>
<li>
<a class="tag" href="https://hakibenita.com/tag/sql">SQL</a>, <a class="tag" href="https://hakibenita.com/tag/django">Django</a>, <a class="tag" href="https://hakibenita.com/tag/orm">ORM</a>
</li>
</ul><a href="django-group-by-sql">
<h4>
Understand Group by in Django with SQL
</h4>
<p>
Django QuerySets and SQL side by side
</p></a>
</li>
<li>
<ul class="article__info">
<li>
<time class="published" datetime="2020-10-20">20 October 2020</time>
</li>
<li>
<a class="tag" href="https://hakibenita.com/tag/sql">SQL</a>, <a class="tag" href="https://hakibenita.com/tag/performance">Performance</a>, <a class="tag" href="https://hakibenita.com/tag/postgresql">PostgreSQL</a>
</li>
</ul><a href="sql-medium-text-performance">
<h4>
The Surprising Impact of Medium-Size Texts on PostgreSQL Performance
</h4>
<p>
Why TOAST is the best thing since sliced bread
</p></a>
</li>
</ul>
</section>
</div>
<script>
<![CDATA[
(function(window, document) {
const progressIndicatorForElement = document.querySelector('[data-progress-indicator]');
if (!progressIndicatorForElement) {
return;
}
const progressElement = document.createElement('div');
progressElement.setAttribute('role', 'presentation');
progressElement.classList.add('progress-indicator');
// Initial position.
progressElement.style.left = "-100%";
progressElement.style.transform = "translateX(-100%)";
document.body.appendChild(progressElement);
let lastKnownScrollPosition = 0;
let ticking = false;
window.addEventListener('scroll', function(e) {
lastKnownScrollPosition = window.scrollY;
if (ticking) {
return;
}
window.requestAnimationFrame(function() {
var offset = Math.min(100, lastKnownScrollPosition / (progressIndicatorForElement.scrollHeight - window.innerHeight) * 100);
progressElement.style.transform = `translateX(${offset}%)`;
ticking = false;
});
ticking = true;
});
})(window, document);
]]>
</script>
<script type="application/ld+json">
<![CDATA[
{
"@context": "http://schema.org",
"@type": "Article",
"mainEntityOfPage": {
"@type": "WebPage",
"@id": "https://hakibenita.com/sql-anomaly-detection"
},
"headline": "Simple Anomaly Detection Using Plain SQL",
"description": "Many developers think that having a critical bug in their code is the worse thing that can happen. Well, there is something much worst than that: Having a critical bug in your code and not knowing about it! Using some high school level statistics and a fair knowledge of SQL, I implemented a very simple anomaly detection system.",
"datePublished": "2020-09-21",
"dateModified": "2020-09-21",
"author": {
"@type": "Person",
"name": "Haki Benita"
},
"publisher": {
"@type": "Organization",
"name": "Haki Benita",
"logo": {
"@type": "ImageObject",
"url": "https://hakibenita.com/images/favicon-32x32.png"
}
},
"image": [
"https://hakibenita.com/images/00-sql-anomaly-detection-400.png",
"https://hakibenita.com/images/00-sql-anomaly-detection-404.png",
"https://hakibenita.com/images/00-sql-anomaly-detection-500.png",
"https://hakibenita.com/images/00-sql-anomaly-detection-backtest-10-1-60.png",
"https://hakibenita.com/images/00-sql-anomaly-detection-backtest-10-3-60.png",
"https://hakibenita.com/images/00-sql-anomaly-detection-backtest-30-3-360.png",
"https://hakibenita.com/images/00-sql-anomaly-detection-backtest-30-3-60.png",
"https://hakibenita.com/images/00-sql-anomaly-detection-chart-by-status-over-time.png",
"https://hakibenita.com/images/00-sql-anomaly-detection-parameters.png",
"https://hakibenita.com/images/00-sql-anomaly-detection-scatter-plot.png",
"https://hakibenita.com/images/00-sql-anomaly-detection.png",
"https://hakibenita.com/images/favicon-32x32.png"
]
}
]]>
</script>
</main>
<footer>
<ul>
<li>© Haki Benita 2016-2020
</li>
<li>
<a href="/pages/credits">Credits</a>
</li>
</ul>
<ul>
<li>
<a href="#">↑ Top</a>
</li>
</ul>
</footer>
</body>
</html>