什么是时间加权平均值以及为什么你应该关心

了解时间加权平均值是如何计算的，为什么它们对数据分析如此强大，以及如何使用 TimescaleDB 超函数来更快地计算它们——所有这些都使用 SQL。许多处理时间序列数据的人都有很好的、定期采样的数据集。数据可以每隔几秒、几毫秒或他们选择的任何时间进行采样，但通过定期采样，我们的意思是数据点之间的时间基本上是恒定的。计算常规数据集中指定时间段内数据点的平均值是一个相对容易理解的查询。但是对于那些没有定期采样数据的人来说，获得一段时间内的代表性平均值可能是一个复杂且耗时的查询。当您处理不规则采样的数据时，时间加权平均值是一种获得无偏平均值的方法。时间序列数据很快就会出现，有时每秒生成数百万个数据点（阅读更多关于时间序列数据的信息）。由于信息量和信息量巨大，时间序列数据的查询和分析已经很复杂，这就是我们构建 TimescaleDB 的原因，这是一个多节点、PB 级、完全免费的时间序列关系数据库。不规则采样的时间序列数据只会增加另一层复杂性——而且比您想象的更常见。例如，不规则采样的数据，因此需要时间加权平均值，经常发生在： 工业物联网，团队通过仅在值变化时发送点来“压缩”数据 遥感，可以从边缘发送回数据成本高昂，因此您只能为最关键的操作发送高频数据 基于触发器的系统，其中一个传感器的采样率受到另一个传感器读数的影响（即，当运动传感器处于异常状态时，安全系统会更频繁地发送数据触发）

在 Timescale，我们一直在寻找使开发人员的生活更轻松的方法，尤其是在他们处理时间序列数据时。为此，我们引入了超函数，这是一种新的 SQL 函数，可简化 PostgreSQL 中时间序列数据的处理。这些超函数之一使您能够快速有效地计算时间加权平均值，从而提高工作效率。继续阅读时间加权平均值的示例，它们是如何计算的，如何在 TimescaleDB 中使用时间加权平均值超函数，以及关于如何使用它们来提高项目生产力的一些想法，无论领域如何.如果您想立即开始使用 time_weight 超函数 - 以及更多 - 立即启动一个完全托管的 TimescaleDB 服务：创建一个帐户以免费试用 30 天。在 Timescale Forge 的每个新数据库服务上都预加载了超函数，因此在创建新服务后，您就可以使用它们了。如果您更喜欢管理自己的数据库实例，可以在 GitHub 上下载并安装 timescaledb_toolkit 扩展，之后您就可以使用 time_weight 和其他超函数。如果您对此博文有任何问题或意见，我们已在 GitHub 页面上展开讨论，我们很乐意听取您的意见。 （而且，如果你喜欢你所看到的，GitHub ⭐ 也总是受到欢迎和赞赏！）你可以在 GitHub 上查看我们即将发布的路线图，以获取建议的功能列表，以及我们目前正在实施的功能和可用的功能今天使用。我在 Timescale 做了 3 年多的开发人员，在数据库方面工作了大约 5 年，但在此之前我是一名电化学家。作为一名电化学家，我在一家电池制造商工作，看到了很多这样的图表：

这是一条电池放电曲线，它描述了电池可以为某物供电多长时间。 x 轴以安培小时为单位显示容量，由于这是恒流放电，因此 x 轴实际上只是时间的代表。 y 轴显示电压，它决定了电池的功率输出；当您继续为电池放电时，电压会下降，直到达到需要充电的程度。当我们为新电池配方进行研发时，我们会多次循环使用许多电池，以找出哪种配方使电池使用寿命最长。如果您更仔细地观察放电曲线，您会注意到只有两个“有趣”的部分：它们是放电开始和结束时电压快速变化的部分。在这两个部分之间，中间有一段很长的时间，电压几乎没有变化：现在，当我之前说我是电化学家时，我承认我有点夸大其词。我对电化学的了解足够危险，但我与拥有博士学位的人一起工作，他们知道的比我多得多。但是，在处理数据方面，我通常比他们做得更好，所以我会做一些事情，比如对恒电位仪进行编程，这是为了执行这些测试而连接电池的设备。对于放电周期的有趣部分（开始和结束的那些部分），我们可以将恒电位仪样品置于其最大速率，通常每 10 毫秒左右一个点。我们不想在电压不变的冗长乏味的部分采样尽可能多的数据点，因为这意味着保存大量具有不变值的数据并浪费存储空间。

为了减少我们必须处理的无聊数据而不丢失有趣的位，我们将程序设置为每 3 分钟采样一次，或者当电压变化合理的数量时，比如超过 5 mV。通过以这种方式对数据进行采样，我们将在有趣的部分获得更多的数据，而在无聊的中间部分获得更少的数据。那太棒了！它让我们回答了关于曲线快速变化部分的更多有趣问题，并为我们提供了关于缓慢变化部分所需的所有信息——无需存储大量冗余数据。但是，这里有一个问题：给定这个数据集，我们如何找到放电期间的平均电压？这个问题很重要，因为它是我们可以在这条放电曲线和未来曲线之间进行比较的事情之一，比如 10 或 100 个循环后。随着电池老化，其平均电压会下降，并且随着时间的推移下降的幅度可以告诉我们电池在其生命周期中的存储容量有多好——以及它是否可以变成有用的产品。问题是感兴趣位中的数据被更频繁地采样（即，感兴趣位有更多数据点），这会在计算平均值时赋予它更多权重，即使它不应该这样做。如果我们只是在整个曲线上取一个简单的平均值，将每个点的值相加并除以点数，这将意味着我们采样率的变化可能会改变我们计算的平均值......即使潜在的影响是真的一样！我们很容易忽略我们试图识别的任何差异——关于我们如何改进电池的任何线索都可能会在我们的采样协议的变化中丢失。

现在，有些人会说：好吧，为什么不以恒电位仪的最大速率采样，即使是在无聊的部分？嗯，这些放电测试运行的时间真的很长。它们需要 10 到 12 个小时才能完成，但有趣的部分可能很短，从几秒钟到几分钟不等。如果我们以最高速率进行采样，每 10 毫秒左右采样一次，这意味着要存储的数据量要多几个数量级，即使我们几乎不会使用任何数据！数量级的数据意味着更多的成本、更多的分析时间以及各种各样的问题。所以最大的问题是：当我们处理不规则间隔的数据点时，我们如何获得具有代表性的平均值？ （下一点是有点方程重，但我认为它们是相对简单的方程，并且它们很好地映射到它们的图形表示上。当人们给我计算背后的数学和图形直觉时，我总是喜欢它 - 但如果您想跳过以查看如何使用时间加权平均值，数学位到此结束。）然后，正常平均值将是值的总和除以总点数：但是，因为它们是不规则的间隔，我们需要一些方法来解决这个问题。考虑它的一种方法是在每个时间点获取一个值，然后将其除以总时间。这就像获得曲线下的总面积并除以总时间量 ΔT。 （在这种情况下，我们在点之间进行线性插值）。所以，让我们专注于寻找那个区域。前两点之间的区域是梯形：

面积 = Δ t 1 v 1 ⏟ 矩形面积 + Δ t 1 Δ v 1 2 ⏟ 三角形面积 \begin{equation} area = \underbrace{\Delta t_1 v_1}_\text{矩形面积} + \underbrace{ \frac{\Delta t_1 \Delta v_1}{2}}_\text{三角形的面积} \end{equation} 需要注意的一件很酷的事情是，这为我们提供了一种思考此解决方案的新方法：它是每对相邻值，由它们之间的时间加权： \begin{equation} area = \underbrace{\frac{(v_1 + v_2)}{2}}_{\text{average of } v_1 \text{ & } v_2} \Delta t_1 \end{equation} 也等于绘制到 v1 和 v2 中点的矩形面积：既然我们已经推导出了两个相邻点的公式，我们可以对每对相邻点重复这个数据集中的点。然后我们需要做的就是把它加起来，这就是时间加权和，它等于曲线下的面积。 （学过微积分的人可能在他们学习积分和积分近似时实际上还记得其中的一些内容！）计算曲线下的总面积后，我们要做的就是将时间加权总和除以总 ΔT 和我们有我们的时间加权平均值。 💥 现在我们已经完成了理论上的时间加权平均值，让我们在 SQL 中进行测试。让我们考虑正在监控冰柜的冰淇淋制造商或店主的场景。事实证明，冰淇淋需要保持在相对较窄的温度范围内（~0-10℉）[1]，这样它就不会融化和重新冻结，从而导致那些没人喜欢的奇怪晶体。同样，如果冰淇淋太冷，就很难舀起来。

当人们打开和关闭门时，冰箱中的空气温度会发生更大的变化，但冰淇淋温度需要更长的时间才能改变。因此，只有长时间暴露在极端温度下，才会出现问题（融化、讨厌的冰晶）。通过测量这些数据，冰淇淋制造商可以对他们储存在冰箱中的每批产品进行质量控制。考虑到这一点，冷冻室中的传感器以下列方式测量温度：当门关闭且处于最佳范围时，传感器每 5 分钟测量一次；当门打开时，传感器每 30 秒进行一次测量，直到门关闭，温度恢复到 10 ℉ 以下。 INSERT INTO freezer_temps 值 ( 1, '2020-01-01 00:00:00+00', 4.0), ( 1, '2020-01-01 00:05:00+00', 5.5), ( 1, ' 2020-01-01 00:10:00+00', 3.0), ( 1, '2020-01-01 00:15:00+00', 4.0), ( 1, '2020-01-01 00:20 :00+00', 3.5), ( 1, '2020-01-01 00:25:00+00', 8.0), ( 1, '2020-01-01 00:30:00+00', 9.0) , ( 1, '2020-01-01 00:31:00+00', 10.5), -- 门开了！( 1, '2020-01-01 00:31:30+00', 11.0), ( 1 , '2020-01-01 00:32:00+00', 15.0), ( 1, '2020-01-01 00:32:30+00', 20.0), -- 门关闭( 1, '2020- 01-01 00:33:00+00', 18.5), ( 1, '2020-01-01 00:33:30+00', 17.0), ( 1, '2020-01-01 00:34:00 +00', 15.5), ( 1, '2020-01-01 00:34:30+00', 14.0), ( 1, '2020-01-01 00:35:00+00', 12.5), ( 1, '2020-01-01 00:35:30+00', 11.0), ( 1, '2020-01-01 00:36:00+00', 10.0), -- 温度稳定( 1, '2020) -01-01 00:40:00+00', 7.0),(1, '2020-01-01 00:45:00+00', 5.0);开门后的第 31-36 分钟有更多的数据点。如果我们取所有点的平均值，我们会得到一个误导性的值。冷冻室仅在 45 分钟中的 5 分钟（时间段的 11%）高于阈值温度，但这些分钟构成了 20 个数据点中的 10 个（50%！），因为我们在门关闭后更频繁地采样冷冻室温度打开。为了找到更准确的时间加权平均温度，让我们为上面处理这种情况的公式编写 SQL。为了比较，我们还将获得正常平均值。 （如果您在阅读时遇到问题，请不要担心，我们稍后会编写一个更简单的版本）。 WITH setup AS ( SELECT lag(temperature) OVER (PARTITION BY freezer_id ORDER BY ts) as prev_temp, extract('epoch' FROM ts) as ts_e, extract('epoch' FROM lag(ts) OVER (PARTITION BY freezer_id ORDER BY ts) )) 作为 prev_ts_e, * FROM freezer_temps), nextstep AS (SELECT CASE WHEN prev_temp is NULL THEN NULL ELSE (prev_temp + temperature) / 2 * (ts_e - prev_ts_e) END as weighted_sum, * FROM setup)SELECT freezer_id, avg(temperature) , -- 常规平均总和(weighted_sum) / (max(ts_e) - min(ts_e)) as time_weighted_average -- 我们导出的averageFROM nextstepGROUP BY freezer_id;它确实返回了我们想要的东西，并让我们对发生的事情有更好的了解，但是写起来并不是很有趣，是吗？

我们在那里有一些窗口函数，一些处理空值的 case 语句，以及一些 CTE 试图使其合理地清楚发生了什么。当人们试图弄清楚正在发生的事情并对其进行调整时，这种情况确实会导致代码维护问题。代码就是管理复杂性。完成一个相对简单的任务的长而复杂的查询使得接下来出现的开发人员（即你在 3 个月内）了解正在发生的事情、如何使用它或如果他们（或你！）需要一个不同的结果。或者，更糟糕的是，这意味着代码永远不会被更改，因为人们不太了解查询在做什么，并且它只是变成了一个没有人（包括你在内）想要触及的黑盒子。这就是我们创建超函数的原因，使复杂的时间序列数据分析变得不那么复杂。如果我们使用超函数计算时间加权平均值，让我们看看时间加权平均冷冻温度查询是什么样的：这不是更简洁吗？！使用“线性”加权方法（即上文 [2] 得出的那种加权）计算 time_weight，然后取加权值的平均值，我们就完成了。我更喜欢那个 API（我会更好，因为我设计了它！）。更重要的是，我们不仅可以避免编写所有 SQL，而且编写起来也变得容易得多（在时间加权平均值之上构建更复杂的分析）。这是超功能背后设计理念的重要组成部分；我们想让基本的东西变得简单，以便您可以轻松地使用它们来构建更复杂的、特定于应用程序的分析。假设我们对整个数据集的平均值不满意，我们希望获得每 10 分钟存储桶的时间加权平均值： SELECT time_bucket('10 mins'::interval, ts) as bucket, freezer_id, avg(temperature), average(time_weight('Linear', ts, temperature)) as time_weighted_average FROM freezer_tempsGROUP BY bucket, freezer_id;

我们添加了一个 time_bucket，按它分组，大功告成！让我们来看看超函数支持的其他一些复杂的分析。继续我们的冰淇淋示例，假设我们已经设置了阈值，因为我们知道如果冰淇淋在 15 ℉ 以上的温度超过 15 分钟，它会形成冰晶，使其具有沙质/颗粒味。我们可以在窗口函数中使用时间加权平均值来查看是否发生了这种情况： SELECT *,average(time_weight('Linear', ts, temperature) OVER十五_分钟) 作为滚动_twaFROM freezer_tempsWINDOW十五_分钟 AS (PARTITION BY freezer_id ORDER BY ts RANGE ' 15 分钟'::间隔 PRECEDING) ORDER BY freezer_id, ts; freezer_id | ts |温度 |滚动_twa ------------+------------------------+----------- --+----------------- 1 | 2020-01-01 00:00:00+00 | 4 | 1 | 2020-01-01 00:05:00+00 | 5.5 | 4.75 1 | 2020-01-01 00:10:00+00 | 3 | 4.5 1 | 2020-01-01 00:15:00+00 | 4 | 4.166666666666667 1 | 2020-01-01 00:20:00+00 | 3.5 | 3.8333333333333335 1 | 2020-01-01 00:25:00+00 | 8 | 4.333333333333333 1 | 2020-01-01 00:30:00+00 | 9 | 6 1 | 2020-01-01 00:31:00+00 | 10.5 | 7.363636363636363 1 | 2020-01-01 00:31:30+00 | 11 | 7.510869565217392 1 | 2020-01-01 00:32:00+00 | 15 | 7.739583333333333 1 | 2020-01-01 00:32:30+00 | 20 | 8.13 1 | 2020-01-01 00:33:00+00 | 18.5 | 8.557692307692308 1 | 2020-01-01 00:33:30+00 | 17 | 8.898148148148149 1 | 2020-01-01 00:34:00+00 | 15.5 | 9.160714285714286 1 | 2020-01-01 00:34:30+00 | 14 | 9.35344827586207 1 | 2020-01-01 00:35:00+00 | 12.5 | 9.483333333333333 1 | 2020-01-01 00:35:30+00 | 11 | 11.369047619047619 1 | 2020-01-01 00:36:00+00 | 10 | 11.329545454545455 1 | 2020-01-01 00:40:00+00 | 7 | 10.575 1 | 2020-01-01 00:45:00+00 | 5 | 9.741666666666667 这里的窗口是前15分钟，按时间排序。看起来我们一直低于我们的冰结晶温度！我们还提供了一个特殊的汇总函数，以便您可以从子查询中重新聚合时间加权值。例如： SELECT average(rollup(time_weight)) as time_weighted_average FROM (SELECT time_bucket('10 mins'::interval, ts) as bucket, freezer_id, time_weight('Linear', ts, temperature) FROM freezer_temps GROUP BY bucket, freezer_id ) t;

这将为我们提供与第一个方程的总计相同的输出，因为我们只是重新聚合了分桶值。但这主要是为了让您可以进行更有趣的分析，例如，将冷冻机的每个十分钟时间加权平均值归一化为整体时间加权平均值。 WITH t as (SELECT time_bucket('10 mins'::interval, ts) as bucket, freezer_id, time_weight('Linear', ts, temperature) FROM freezer_temps GROUP BY bucket, freezer_id) SELECT bucket, freezer_id, average(time_weight) as bucketed_twa, (SELECT average(rollup(time_weight)) FROM t) as total_twa, average(time_weight) / (SELECT average(rollup(time_weight)) FROM t) as normalized_twaFROM t;这种功能（存储时间权重以供稍后分析）在连续聚合中最有用，而我们设计的时间加权平均值恰好在这种情况下可用！我们将在以后的文章中详细介绍这一点，因此请务必订阅我们的时事通讯，以便在我们发布新的技术内容时收到通知。如果您想立即开始使用 time_weight 超函数 - 以及更多 - 立即启动一个完全托管的 TimescaleDB 服务：创建一个帐户以免费试用 30 天。 Timescale Forge 上的每个新数据库服务都预加载了超函数，因此在创建新服务后，您就可以开始使用它们了！如果您更喜欢管理自己的数据库实例，可以在 GitHub 上下载并安装 timescaledb_toolkit 扩展，之后您就可以使用 time_weight 和所有其他超函数。

**如果您对此博文有任何问题或意见，我们已在 GitHub 页面上展开讨论，我们很乐意听取您的意见。 （而且，如果你喜欢你所看到的，GitHub ⭐ 也总是受到欢迎和赞赏！）我们喜欢公开构建，你可以在 GitHub 上查看我们即将推出的路线图，以获取建议的功能列表、我们目前正在实施的功能以及今天可以使用的功能。我们要特别感谢......