Detect Outliers using BigQuery with Standard Deviation

Question

I currently have a table in BigQuery that contains some outliers

Example table:

port - qty - datetime
--------------------------------
TCP1 - 13 - 2018/06/11 11:20:23
UDP2 - 15 - 2018/06/11 11:24:24
TCP3 - 14 - 2018/06/11 11:24:27
TCP1 - 2  - 2018/06/11 11:24:26 
UDP2 - 15 - 2018/06/11 11:35:32
TCP3 - 13 - 2018/06/11 11:45:23
TCP3 - 14 - 2018/06/11 11:54:22
TCP3 - 30 - 2018/06/11 11:55:33

I would like to be able to sift out the outliers on the various ports at 2018/06/11 using SQL and standard deviation

Result:

TCP1 - 2  - 2018/06/11 11:24:26
TCP3 - 30 - 2018/06/11 11:55:33

I have done some research and found that standard deviation is able to help sift out the outliers. However, i do not know how to write the SQL query to make this work. Any help will be greatly appreciated.

(this is the closest thread that i could find on this topic: Using BigQuery to find outliers with standard deviation results combined with WHERE clause)

score 3 · Accepted Answer · answered Oct 25 '18 at 08:03

Below example is for BigQuery Standard SQL

#standardSQL
WITH stats AS (
  SELECT DATE(PARSE_TIMESTAMP('%Y/%m/%d %T', datetime)) dt,
    AVG(qty) - 1.5 * STDDEV(qty) down,
    AVG(qty) + 1.5 * STDDEV(qty) up
  FROM `project.dataset.table`
  GROUP BY dt
)
SELECT port, qty, datetime 
FROM `project.dataset.table`
JOIN stats 
ON dt = DATE(PARSE_TIMESTAMP('%Y/%m/%d %T', datetime))
WHERE NOT qty BETWEEN down AND up

You can test, play with above using dummy data from your question:

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'TCP1' port, 13 qty, '2018/06/11 11:20:23' datetime UNION ALL
  SELECT 'UDP2', 15, '2018/06/11 11:24:24' UNION ALL
  SELECT 'TCP3', 14, '2018/06/11 11:24:27' UNION ALL
  SELECT 'TCP1', 2 , '2018/06/11 11:24:26' UNION ALL 
  SELECT 'UDP2', 15, '2018/06/11 11:35:32' UNION ALL
  SELECT 'TCP3', 13, '2018/06/11 11:45:23' UNION ALL
  SELECT 'TCP3', 14, '2018/06/11 11:54:22' UNION ALL
  SELECT 'TCP3', 30, '2018/06/11 11:55:33' 
), stats AS (
  SELECT DATE(PARSE_TIMESTAMP('%Y/%m/%d %T', datetime)) dt,
    AVG(qty) - 1.5 * STDDEV(qty) down,
    AVG(qty) + 1.5 * STDDEV(qty) up
  FROM `project.dataset.table`
  GROUP BY dt
)
SELECT port, qty, datetime 
FROM `project.dataset.table`
JOIN stats 
ON dt = DATE(PARSE_TIMESTAMP('%Y/%m/%d %T', datetime))
WHERE NOT qty BETWEEN down AND up

with result as

Row port    qty datetime     
1   TCP1    2   2018/06/11 11:24:26  
2   TCP3    30  2018/06/11 11:55:33

Detect Outliers using BigQuery with Standard Deviation

1 Answers1