查找历史异常

在上一节中，我们发现了一个异常情况：400 状态码的请求量上升，其 z 分数达到了 6。但如何设定 z 分数的阈值呢？z 分数为 3 是否算异常？那 2 或 1 呢？

为了找到符合我们需求的阈值，我们可以使用历史数据进行不同阈值的模拟测试，这通常被称为回测。

首先，我们需要计算每个状态码在每一行之前（包括当前行）的均值和标准差，就像当前值是最新的一样。这正是窗口函数的经典应用场景：

1WITH calculations_over_window AS (
2   SELECT
3      status_code,
4      period,
5      entries,
6      AVG(entries) OVER status_window AS mean_entries,
7      STDDEV(entries) OVER status_window AS stddev_entries
8   FROM
9      server_log_summary
10   WINDOW status_window AS (
11      PARTITION BY status_code
12      ORDER BY period
13      ROWS BETWEEN 60 PRECEDING AND CURRENT ROW
14   )
15)
16SELECT *
17FROM calculations_over_window
18ORDER BY period DESC
19LIMIT 20;

我们使用窗口函数，在一个滑动的 60 分钟窗口内计算均值和标准差。为了避免在每个聚合函数中重复书写 WINDOW 子句，我们定义了一个名为 status_window 的命名窗口。这是 PostgreSQL 的一个便捷功能。

在结果中，我们可以看到每条记录都包含了前 60 行数据的均值和标准差。这与上一节的计算类似，但这次我们对每一行都进行了计算。

接下来，我们可以为每一行计算 z 分数：

1WITH calculations_over_window AS (
2   SELECT
3      status_code,
4      period,
5      entries,
6      AVG(entries) OVER status_window AS mean_entries,
7      STDDEV(entries) OVER status_window AS stddev_entries
8   FROM
9      server_log_summary
10   WINDOW status_window AS (
11      PARTITION BY status_code
12      ORDER BY period
13      ROWS BETWEEN 60 PRECEDING AND CURRENT ROW
14   )
15),
16
17with_zscore AS (
18   SELECT
19       *,
20       (entries - mean_entries) / NULLIF(stddev_entries::float, 0) AS zscore
21   FROM
22       calculations_over_window
23)
24
25SELECT
26   status_code,
27   period,
28   zscore
29FROM
30   with_zscore
31ORDER BY
32   period DESC
33LIMIT
34   20;

现在我们得到了每一行的 z 分数，可以尝试识别异常：

1WITH calculations_over_window AS (
2   SELECT
3       status_code,
4       period,
5       entries,
6       AVG(entries) OVER status_window AS mean_entries,
7       STDDEV(entries) OVER status_window AS stddev_entries
8   FROM
9       server_log_summary
10   WINDOW status_window AS (
11       PARTITION BY status_code
12       ORDER BY period
13       ROWS BETWEEN 60 PRECEDING AND CURRENT ROW
14   )
15),
16
17with_zscore AS (
18   SELECT
19       *,
20       (entries - mean_entries) / NULLIF(stddev_entries::float, 0) AS zscore
21   FROM
22       calculations_over_window
23),
24
25with_alert AS (
26   SELECT
27       *,
28       zscore > 3 AS alert
29   FROM
30       with_zscore
31)
32
33SELECT
34   status_code,
35   period,
36   entries,
37   zscore,
38   alert
39FROM
40   with_alert
41WHERE
42   alert
43ORDER BY
44   period DESC
45LIMIT
46   20;

我们决定将 z 分数大于 3 的值标记为异常。你常常会在教科书中看到 3 这个数字，但不要因此对其产生情感依赖，因为你可以根据实际效果调整这个阈值以获得更好的结果。