使用标准差在BigQuery中检测异常值

Question

我目前在BigQuery中有一个包含一些异常值的表。

例如表格：

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

我想使用SQL和标准偏差在2018/06/11筛选出各个端口的异常值。

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

我已经做了一些研究，发现标准差能够帮助筛选异常值。然而，我不知道如何编写SQL查询使其起作用。任何帮助将不胜感激。

（这是我在这个主题上找到的最接近的帖子：使用BigQuery结合WHERE子句查找具有标准差结果的异常值）

- taN

Answer 1

以下示例适用于BigQuery标准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  

您可以使用来自您的问题的虚拟数据进行测试和操作，如上所示：

#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  

- Mikhail Berlyant