作者：Anber Arif
如果您正在使用数据驱动决策（我们难道不都在尝试这样做吗？），您就会知道统计分析绝对至关重要，它可以洞察数据集中的潜在模式和趋势。标准差是一个重要的指标，可以帮助了解数据的变异性。这个统计指标量化了数据点与数据集平均值的离散程度，并为解释观察到的值的显著性提供了宝贵的上下文。
在这篇文章中，我们将深入探讨标准差的基本概念（包括示例，以便更容易理解），以及如何使用 PostgreSQL 的内置函数（你好，stddev()）在 PostgreSQL 中计算标准差，这些函数可以简化这个过程。
但是，如果您想让事情变得真正简单，请跳到关于 Timescale 超函数 的部分：这组函数、过程和数据类型针对数据分析进行了优化，可以让您使用更少的代码行来查询、聚合和分析数据。 免费试用，方法是创建一个 Timescale 帐户。有关标准差的构建块的更多详细信息，请继续阅读！
标准差是一种统计指标，可以量化一组值的变异程度或离散程度。本质上，它提供了一种表达各个数据点与数据集平均值偏差多少的方法。标准差越高，表示变异性越大，而标准差越低，表示数据点更接近平均值。
为了说明这个概念，请考虑两个数据集
数据集 A [5, 10, 15, 20, 25]
数据集 B [9, 10, 11, 9, 11]
虽然两个数据集的平均值不同，但标准差揭示了有关每个数据集内部值分布的额外信息
数据集 A
数据集 A 中的值分布在更广泛的范围内，从 5 到 25 不等。
各个值与平均值（15）的偏差更大，导致标准差更高。
较高的标准差表示数据集中存在较高的变异性或离散程度。
数据集 B
数据集 B 中的值紧密地聚集在平均值（10）附近。
各个值与平均值的偏差最小，导致标准差较低。
较低的标准差表示较低的变异性，因为值更紧密地围绕平均值分布。
标准差在时间序列数据分析中很重要，它是了解和解释数据模式的宝贵指标。以下是标准差在这个背景下重要的原因
波动性测量：标准差量化了时间序列数据集中变异程度或波动的程度。较高的标准差值表示数据波动较大，而较低的值表示随着时间的推移趋势更稳定。
风险评估：例如，在金融市场中，标准差对于评估投资组合或资产价格相关的风险至关重要。较高的标准差意味着风险和潜在回报更大，使其成为投资者和分析师的重要因素。
识别趋势和异常值：标准差有助于识别时间序列中与预期或平均行为的偏差。如果您是分析师，可以使用标准差来识别数据中的异常峰值或下降，帮助检测异常值。
季节性分析：当分析具有季节性的时间序列数据时，标准差可以洞察季节性变异的幅度。它允许识别给定时间范围内活动增加或减少的时期。
预测分析：了解标准差有助于预测建模，使分析师能够做出更准确的预测。将标准差纳入模型可以更好地考虑潜在的变异性，从而产生更稳健的预测。
制造业中的质量控制：制造业中的时间序列数据通常涉及监控过程的一致性和质量。标准差用于识别产品质量随时间的变化，以便及时进行调整和改进。
现在我们已经解释了标准差为什么对任何使用统计分析的人都有帮助，让我们来探索一步一步计算标准差的过程。
1. 计算平均值（平均数）
将数据集中的所有数据点加起来。
将总和除以数据点的总数，得到平均值。
2. 计算差值
从每个单独的数据点中减去平均值。
此步骤量化了每个数据点与平均值的偏差。
3. 对差值平方
将上一步中获得的每个差值平方。
平方确保所有偏差都是正数，强调偏差的大小。
4. 对平方差求和
将上一步中获得的所有平方差加起来。
结果是平方偏差的总和。
5. 除以数据点的数量
将平方差的总和除以总数据点的数量（N）。
6. 取平方根
最后一步是取上一步中获得的值的平方根。
结果是标准差，表示数据点与平均值的平均偏差。
以下是数学形式：
σ: 总体标准差
N: 总体大小
xi: 总体中的每个值
μ: 总体平均值
样本标准差是标准差的一种变体，专门针对代表样本而不是整个总体的数据集而设计。计算样本标准差的公式涉及除以 (**n-1**) 而不是 **n**。这种修正称为 **贝塞尔修正**，它考虑了使用的是样本而不是整个总体这一事实。
这里，xi 代表每个数据点，x 是样本均值，n 是样本中的数据点数。
理解和正确应用样本标准差至关重要，尤其是在处理有限数据集时。它确保了对样本中潜在变异性的更准确表示，从而有助于进行稳健的统计分析和明智的决策。
在 PostgreSQL 中计算标准差涉及利用内置的 **stddev()** 函数，这是一个强大的 SQL 工具，旨在简化数据库中的统计分析。此函数简化了计算过程，并且效率很高。
PostgreSQL 中的 stddev() 函数是一个统计聚合函数，它计算所有非空输入值的样本标准差。它本质上是 stddev_samp() 的别名，这两个函数可以在 PostgreSQL 中互换使用。
以下是此函数的语法
SELECT stddev(column_name) FROM table_name;
或
SELECT stddev_samp(column_name) FROM table_name;
**stddev()/stddev_samp()**: 样本标准差聚合函数。
**column_name**: 要计算标准差的特定列。
**table_name**: 包含目标列的表。
相反，stddev_pop() 函数计算总体标准差，考虑整个数据集。以下是它的语法
SELECT stddev_pop(column_name) FROM table_name;
**stddev_pop(column_name)**: 计算指定列的总体标准差。
SELECT stddev(sales) FROM transactions;
在此示例中，计算了 transactions 表中 sales 列的标准差，提供了对销售数据的变异性的见解。
SELECT city, stddev(temperature) FROM weather GROUP BY city;
此示例展示了独立地计算每个组（在本例中为每个城市）的标准差的能力，提供了对不同位置温度变化的见解。
SELECT stddev(customer_age) FROM customer_data WHERE total_purchases > 1000;
在此，stddev() 函数应用于数据子集，展示了它在分析数据集特定部分的灵活性。请注意，PostgreSQL 中的 stddev() 函数专门处理非空值，即此函数会忽略空值。
PostgreSQL 中的 **stddev()** 函数在各种场景中都有应用，有助于统计分析和决策。以下是一些常见的用例
stddev() 函数通过计算数值变量的每日标准差，在时间序列数据分析中证明了其价值。这有助于理解数据点随时间的变化方式，便于识别趋势、模式和潜在的不规则性。
SELECT time_bucket('1 day', timestamp_column) AS day,
stddev(value_column) AS daily_stddev
FROM time_series_data
GROUP BY day;
将 stddev() 与分类数据（例如产品类别）一起使用，可以比较数值（例如销售额）的变异性。这有助于评估不同类别中的相对稳定性或波动性。
SELECT category,
stddev(sales_amount) AS sales_variation
FROM sales_data
GROUP BY category;
在直方图分析中包含 stddev() 函数，可以提供对指定桶中数据分布的精细视图。这有助于理解数据的分布和集中程度，支持数据质量评估和规范化工作。
SELECT width_bucket(data_column, min_value, max_value, num_buckets) AS bucket,
stddev(data_column) AS bucket_stddev
FROM dataset
GROUP BY bucket
ORDER BY bucket;
在 PostgreSQL 中处理时间序列数据时，在特定时间范围内计算标准差是提取有价值见解的常见需求。让我们考虑一个名为 sensor_data 的数据集，其列为 timestamp 和 value。目标是计算落在定义的时间范围内的部分数据点的 value 列的标准差。
以下是计算特定时间范围内的标准差的 SQL 查询，同时包含动态日期函数和额外的过滤条件
WITH time_frame AS (
SELECT
NOW() AS end_timestamp,
NOW() - INTERVAL '30 days' AS start_timestamp
)
SELECT
stddev(value)
FROM
sensor_data
WHERE
timestamp BETWEEN (SELECT start_timestamp FROM time_frame) AND (SELECT end_timestamp FROM time_frame)
AND value > 50;
**公用表表达式 (CTE)**: 查询从名为 time_frame 的公用表表达式开始。此 CTE 通过计算当前时间戳 NOW() 作为结束时间戳，并减去 30 天间隔来获得开始时间戳，来定义动态时间范围。
**主查询**: 主查询使用 CTE 根据定义的时间范围过滤 sensor_data。BETWEEN 子句确保仅考虑指定时间范围内的数据点。
**附加条件**: 引入了附加条件 AND value > 50，指定仅对值大于 50 的数据点计算标准差。
建立在 PostgreSQL 之上但速度更快 - Timescale 引入了一种健壮且简化的超函数方法来计算标准差。Timescale 的超函数代表为时间序列数据量身定制的一组高级分析函数。
这些函数专门设计用于无缝地操作时间数据集，提供了一种简化且高效的方式来执行复杂的计算。它们增强了传统 SQL 函数的功能，但也提供专门的统计和回归分析函数，包括标准差。更详细地了解 Timescale 超函数.
💡如果您想 了解 PostgreSQL 聚合以及它是如何影响我们超函数设计的，请查看这篇文章。
Timescale 中的 stats_agg() 函数遵循两步聚合模式，为时间序列数据上的统计分析提供了一种更有效、更灵活的方法。在此模式中，计算分为两个阶段
**中间聚合**: 最初，使用聚合函数创建中间聚合。此步骤涉及在指定时间间隔内聚合数据。
**最终结果计算**: 然后通过对中间聚合应用一个或多个访问器来计算最终结果。这些访问器使用户能够从聚合数据中提取特定信息。
两步聚合模式提供了一些优势
**效率**: 多个访问器可以重用相同的中间聚合，从而提高计算过程的效率。
**性能推理**: 将聚合与最终计算分离，使得更容易对性能进行推理，从而使分析过程更加清晰。
**理解**: 特别是在窗口函数和连续聚合中，两步聚合模式使得更容易理解可以汇总到更大间隔中的计算。在这里探索更多关于连续聚合和窗口函数的信息.
**回顾性分析**: 中间聚合存储了最终结果中没有的额外信息，即使基础数据被删除，也能够进行回顾性分析。
用于 Timescale 中单变量统计聚合的 stats_agg() 超函数为常见的统计分析提供了强大的工具包。这些函数类似于 PostgreSQL 统计聚合，在 连续聚合和窗口函数 中提供了额外的功能和增强的易用性。它们专门为一维数据设计，使用户能够轻松地执行诸如计算平均值和标准差之类的分析。
要使用 Timescale 中的 stats_agg() 函数计算包含从 0 到 100 的整数的样本的标准差，您可以使用以下 SQL 查询
SELECT stddev(stats_agg(data))
FROM generate_series(0, 100) data;
generate_series 函数创建一个从 0 到 100 的整数序列，代表您的样本数据。别名 data 被分配给此序列。 stats_agg() 函数聚合样本数据，为统计分析做好准备。在本例中，它正在计算标准差。外部 stddev() 函数然后根据聚合的样本数据计算标准差。因此，该查询有效地利用了 stats_agg() 函数执行两步聚合，首先聚合样本数据，然后计算标准差。结果提供了包含从 0 到 100 的整数的指定样本的标准差。
以下示例创建一个统计聚合，以汇总关于变量 val1 的每日统计数据。然后，它使用统计聚合来计算该变量的标准差
WITH t as (
SELECT
time_bucket('1 day'::interval, ts) as dt,
stats_agg(val1) AS stats1D
FROM foo
WHERE id = 'bar'
GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
stddev(stats1D),
FROM t;
在此，名为 t 的公用表表达式 (CTE) 利用 time_bucket 函数创建每日间隔，确保统计数据在每个间隔内聚合。聚合使用 stats_agg(val1) 进行，汇总指定变量的数据。随后，主查询专注于计算聚合统计数据的标准差 stddev(stats1D)。结果是一个简洁且有效的查询，专门针对 val1 在每日间隔内的变异性分析，展示了使用 stats_agg() 的两步聚合模式的灵活性和适应性，用于精确的统计计算。探索 stats_agg() 函数在一维数据中的强大功能.
Timescale 中用于双变量统计聚合的 stats_agg() 函数为对二维数据进行线性回归分析提供了一套强大的工具。此功能允许计算基本指标，例如两个变量之间的相关系数和协方差。此外，用户可以分别计算每个维度的标准统计信息，例如平均值和标准差。
与 PostgreSQL 的统计聚合函数类似，这些函数提供了扩展的功能和增强的可用性，特别是在连续聚合和窗口函数中。这些函数执行的线性回归基于标准最小二乘拟合方法，确保了分析的准确性和可靠性。
要使用 Timescale 中的 stats_agg() 函数计算二维样本的标准差，请考虑以下 SQL 查询
SELECT stddev_y(stats_agg(data, data))
FROM generate_series(0, 100) data;
generate_series 函数创建一个从 0 到 100 的整数序列，代表样本数据。别名 data 被分配给此序列。此外，stats_agg() 函数聚合二维样本数据，同时考虑由 data 代表的两个维度。然后，stddev_y() 函数计算聚合后的二维样本数据沿 y 轴（第二维）的标准差。
以下示例创建了一个统计聚合，总结了关于两个变量 val2 和 val1 的每日统计数据，其中 val2 是因变量，val1 是自变量。然后，我们使用统计聚合计算因变量的标准差。
WITH t as (
SELECT
time_bucket('1 day'::interval, ts) as dt,
stats_agg(val2, val1) AS stats2D,
FROM foo
WHERE id = 'bar'
GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
stddev_y(stats2D)
FROM t;
在此查询中，名为 t 的公共表表达式 (CTE) 利用 time_bucket 函数创建每日间隔，并使用 stats_agg() 聚合每个每日间隔内两个变量的统计数据。后续的主查询侧重于计算因变量 val2 沿 y 轴 stddev_y(stats2D) 的标准差。这种方法提供了对每个每日间隔内 val2 分散性的见解，突出了其值的变异性。
在以下示例中，窗口函数用于计算滚动窗口统计聚合，重点关注标准差。
SELECT
bucket,
stddev(rolling(stats_agg) OVER fifteen_min, 'pop')
FROM (SELECT
time_bucket('1 min'::interval, ts) AS bucket,
stats_agg(val)
FROM measurements
GROUP BY 1) AS stats
WINDOW fifteen_min as (ORDER BY bucket ASC RANGE '15 minutes' PRECEDING);
stats_agg(val) 函数最初用于聚合每分钟的数据，然后应用滚动函数对每 15 分钟期间重新聚合标准差。参数 pop 指定了总体标准差。
如前所述，Timescale 的超函数为数据分析提供了几个优势，尤其是在时间序列数据集的背景下。
针对时间序列数据优化：Timescale 的超函数专门针对时间序列数据集设计，在处理时间数据时提供优化的性能。它们经过专门设计，能够比通用统计函数更有效地处理时间戳数据集的独特特性。
高效聚合：超函数简化了聚合统计度量（如标准差）随时间的推移的过程。它们优化的算法提高了这些计算的效率，即使对于庞大和复杂的数据集也是如此。
易于使用：Timescale 的超函数简化了复杂统计计算的语法，使其更易于用户使用。实现诸如 stddev() 等函数的标准化方法确保了一致性，并降低了查询构造的复杂性。
二维聚合：Timescale 的超函数包括二维聚合功能，允许同时分析两个变量（Y、X）。这对于您需要同时对多个维度执行统计分析的情况很有用。
高级分析功能：超函数扩展了基本的 SQL 函数，提供了针对时间序列场景量身定制的高级分析功能。这不仅包括标准差，还包括其他统计和回归分析函数，它们提供了对数据变异性的全面视图。
从了解标准差在 时间序列数据 中的重要性到学习总体和样本标准差的数学公式，我们希望这篇文章能帮助您学习如何在 PostgreSQL 中进行稳健的统计分析。
此外，我们还详细介绍了一种更先进、更有效的方法，即引入了 Timescale 的超函数。这些专门的函数专为时间序列数据量身定制，提供了一种简化且强大的方式来执行统计计算。 详细了解 Timescale 的超函数，因为它们提高了易用性、效率和分析能力，使它们对处理 时间数据集 的任何人都很有价值。
要试用超函数，您可以 自行托管 TimescaleDB 并安装 Timescale Toolkit 或只需 创建一个免费的 Timescale 帐户。