TimescaleDB API 参考超函数时间加权计算

time_weight()函数

工具包TimescaleDB 工具包函数在 Timescale 社区版下可用。它们与 Timescale 自动包含在一起，但必须为自托管的 TimescaleDB 单独安装。点击了解更多。

介绍

计算时间加权汇总统计信息，例如平均值（均值）和积分。当数据在时间上采样不均匀时，使用时间加权。在这种情况下，直接平均值会给出误导性的结果，因为它会偏向于更频繁采样的值。

例如，传感器可能长时间处于稳定状态，并仅在发生重大变化时才发送数据。常规均值将稳定状态读数仅计为一个点，而时间加权均值则考虑了在稳定状态下花费的长时间。从本质上讲，时间加权均值对时间进行积分，然后除以经过的时间。

本组中的函数

聚合

time_weight
将数据聚合到中间时间加权聚合形式中，以便进一步计算

访问器

average
计算 TimeWeightSummary 中值的时加权平均值
first_time
从 TimeWeightSummary 聚合中获取第一个时间戳
first_val
从 TimeWeightSummary 聚合中获取第一个值
integral
计算 TimeWeightSummary 的积分
interpolated_average
计算时间段内的时加权平均值，同时对时间段边界进行插值
interpolated_integral
计算时间段内的积分，同时对时间段边界进行插值
last_time
从 TimeWeightSummary 聚合中获取最后一个时间戳
last_val
从 TimeWeightSummary 聚合中获取最后一个值

Rollup

rollup
组合多个 TimeWeightSummaries

函数详细信息

time_weight(
    method TEXT,
    ts TIMESTAMPTZ,
    value DOUBLE PRECISION
) RETURNS TimeWeightSummary

这是执行任何时间加权计算的第一步。使用 time_weight 从您的数据中创建中间聚合（TimeWeightSummary）。此中间形式随后可被本组中的一个或多个访问器用来计算最终结果。或者，可以使用 rollup() 在应用访问器之前组合多个这样的中间聚合对象。

必需参数
名称类型描述
methodTEXT要使用的加权方法。可用的方法是 linear（或其别名 trapezoidal，对于熟悉数值积分方法的人来说）和 LOCF，它代表“最后观察值向前携带”。linear 通过在间隙的起点和终点之间线性插值来填充缺失数据。LOCF 通过假设该值保持不变直到看到下一个值来填充间隙。当仅在值发生变化时进行测量时，LOCF 最有用。如果对测量没有此类保证，则 linear 最有用。方法名称不区分大小写。
tsTIMESTAMPTZ每个点的时。空值将被忽略。仅对 null 值计算的聚合返回 null。
valueDOUBLE PRECISION每个点的值，用于时间加权聚合。空值将被忽略。仅对 null 值计算的聚合返回 null。
返回
列类型描述
time_weightTimeWeightSummary一个 TimeWeightSummary 对象，可以传递给时间加权 API 中的其他函数
示例

使用线性插值方法将列 val 中的数据聚合到每日时间加权聚合中

SELECT
    time_bucket('1 day'::interval, ts) as dt,
    time_weight('Linear', ts, val) AS tw
FROM foo
GROUP BY time_bucket('1 day'::interval, ts)
average(
    tws TimeWeightSummary
) RETURNS DOUBLE PRECISION

计算时间加权平均值。等于 integral 除以经过的时间。

必需参数
名称类型描述
twsTimeWeightSummary来自 time_weight() 调用的输入 TimeWeightSummary。
返回
列类型描述
averageDOUBLE PRECISION时间加权平均值。
示例

使用“最后观察值向前携带”插值方法计算列 val 的时间加权平均值

SELECT
    id,
    average(tws)
FROM (
    SELECT
        id,
        time_weight('LOCF', ts, val) AS tws
    FROM foo
    GROUP BY id
) t
first_time(
    tw TimeWeightSummary
) RETURNS TIMESTAMPTZ

获取 TimeWeightSummary 聚合中第一个点的时戳。

必需参数
名称类型描述
twsTimeWeightSummary来自 time_weight() 调用的输入 TimeWeightSummary。
返回
列类型描述
first_timeTIMESTAMPTZTimeWeightSummary 中第一个点的时。
示例

在列 val 上生成线性 TimeWeightSummary 并获取第一个时间戳

WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        time_weight('Linear', ts, val) AS tw
    FROM table
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    first_time(tw)
FROM t;
first_val(
    tw TimeWeightSummary
) RETURNS DOUBLE PRECISION

获取 TimeWeightSummary 聚合中第一个点的值。

必需参数
名称类型描述
twsTimeWeightSummary来自 time_weight() 调用的输入 TimeWeightSummary。
返回
列类型描述
first_valDOUBLE PRECISIONTimeWeightSummary 中第一个点的值。
示例

在列 val 上生成线性 TimeWeightSummary 并获取第一个值

WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        time_weight('Linear', ts, val) AS tw
    FROM table
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    first_val(tw)
FROM t;
integral(
    tws TimeWeightSummary
    [, unit TEXT]
) RETURNS DOUBLE PRECISION

计算积分，即由数据点形成的曲线下的面积。等于 average 乘以经过的时间。

必需参数
名称类型描述
twsTimeWeightSummary来自 time_weight() 调用的输入 TimeWeightSummary。
可选参数
名称类型描述
unitTEXT以其表达积分的时间单位。可以是 microsecond、millisecond、second、minute、hour 或 PostgreSQL 支持的任何这些单位的别名。默认为 second。
返回
列类型描述
integralDOUBLE PRECISION时间加权积分。
示例

创建表以跟踪不规则采样的存储使用情况（以字节为单位），并获取以字节小时为单位的总存储使用量。使用“最后观察值向前携带”插值方法

-- Create a table to track irregularly sampled storage usage
CREATE TABLE user_storage_usage(ts TIMESTAMP, storage_bytes BIGINT);
INSERT INTO user_storage_usage(ts, storage_bytes) VALUES
    ('01-01-2022 00:00', 0),
    ('01-01-2022 00:30', 100),
    ('01-01-2022 03:00', 300),
    ('01-01-2022 03:10', 1000),
    ('01-01-2022 03:25', 817);


-- Get the total byte-hours used
SELECT
    integral(time_weight('LOCF', ts, storage_bytes), 'hours')
FROM
    user_storage_usage;
interpolated_average(
    tws TimeWeightSummary,
    start TIMESTAMPTZ,
    interval INTERVAL
    [, prev TimeWeightSummary]
    [, next TimeWeightSummary]
) RETURNS DOUBLE PRECISION

计算时间段内的时加权平均值，同时对时间段边界进行插值。

类似于 average，但允许在数据已按时间段分组且没有精确位于时间段边界的数据点时，跨时间段边界进行准确计算。例如，这在窗口函数中很有用。

使用与 TimeWeightSummary 本身内部相同的插值方法，使用前一个和下一个存储桶的值对边界处的值进行插值。

等于 interpolated_integral 除以经过的时间。

必需参数
名称类型描述
twsTimeWeightSummary来自 time_weight() 调用的输入 TimeWeightSummary。
startTIMESTAMPTZ时间加权平均值应覆盖的时间段的起点（如果存在前一点）。
intervalINTERVAL时间加权平均值应覆盖的时间段的长度。
可选参数
名称类型描述
prevTimeWeightSummary来自先前时间段的 TimeWeightSummary，用于对 start 处的值进行插值。如果为 NULL，则使用 tws 中的第一个时间戳作为起始值。先前时间段可以从 PostgreSQL lag() 函数确定。
nextTimeWeightSummary来自下一个时间段的 TimeWeightSummary，用于对 start + interval 处的值进行插值。如果为 NULL，则使用 tws 中的第一个时间戳作为起始值。下一个时间段可以从 PostgreSQL lead() 函数确定。
返回
列类型描述
averageDOUBLE PRECISION时间段 (start, start + interval) 的时间加权平均值，根据 TimeWeightSummary 以及从 prev 和 next 插值的端点计算
示例

计算列 val 的时间加权每日平均值，使用“最后观察值向前携带”方法对存储桶边界进行插值

SELECT
    id,
    time,
    interpolated_average(
        tws,
        time,
        '1 day',
        LAG(tws) OVER (PARTITION BY id ORDER by time),
        LEAD(tws) OVER (PARTITION BY id ORDER by time)
    )
FROM (
    SELECT
        id,
        time_bucket('1 day', ts) AS time,
        time_weight('LOCF', ts, val) AS tws
    FROM foo
    GROUP BY id, time
) t
interpolated_integral(
    tws TimeWeightSummary,
    start TIMESTAMPTZ,
    interval INTERVAL
    [, prev TimeWeightSummary]
    [, next TimeWeightSummary]
    [, unit TEXT]
) RETURNS DOUBLE PRECISION

计算时间段内的积分，同时对时间段边界进行插值。

类似于 integral，但允许在数据已按时间段分组且没有精确位于时间段边界的数据点时，跨时间段边界进行准确计算。例如，这在窗口函数中很有用。

使用与 TimeWeightSummary 本身内部相同的插值方法，使用前一个和下一个存储桶的值对边界处的值进行插值。

等于 interpolated_average 乘以经过的时间。

必需参数
名称类型描述
twsTimeWeightSummary来自 time_weight() 调用的输入 TimeWeightSummary。
startTIMESTAMPTZ时间加权积分应覆盖的时间段的起点（如果存在前一点）。
intervalINTERVAL时间加权积分应覆盖的时间段的长度。
可选参数
名称类型描述
prevTimeWeightSummary来自先前时间段的 TimeWeightSummary，用于对 start 处的值进行插值。如果为 NULL，则使用 tws 中的第一个时间戳作为起始值。先前时间段可以从 PostgreSQL lag() 函数确定。
nextTimeWeightSummary来自下一个时间段的 TimeWeightSummary，用于对 start + interval 处的值进行插值。如果为 NULL，则使用 tws 中的第一个时间戳作为起始值。下一个时间段可以从 PostgreSQL lead() 函数确定。
unitTEXT以其表达积分的时间单位。可以是 microsecond、millisecond、second、minute、hour 或 PostgreSQL 支持的任何这些单位的别名。默认为 second。
返回
列类型描述
integralDOUBLE PRECISION时间段 (start, start + interval) 的时间加权积分，根据 TimeWeightSummary 以及从 prev 和 next 插值的端点计算
示例

创建表以跟踪不规则采样的存储使用情况（以字节为单位），并获取 1 月 1 日至 1 月 6 日之间的总存储使用量（以字节小时为单位）。使用“最后观察值向前携带”插值方法

-- Create a table to track irregularly sampled storage usage
CREATE TABLE user_storage_usage(ts TIMESTAMP, storage_bytes BIGINT);
INSERT INTO user_storage_usage(ts, storage_bytes) VALUES
    ('01-01-2022 20:55', 27),
    ('01-02-2022 18:33', 100),
    ('01-03-2022 03:05', 300),
    ('01-04-2022 12:13', 1000),
    ('01-05-2022 07:26', 817);




-- Get the total byte-hours used between Jan. 1 and Jan. 6
SELECT
    interpolated_integral(
        time_weight('LOCF', ts, storage_bytes),
        '01-01-2022',
        '5 days',
        NULL,
        NULL,
        'hours'
    )
FROM
    user_storage_usage;
last_time(
    tw TimeWeightSummary
) RETURNS TIMESTAMPTZ

获取 TimeWeightSummary 聚合中最后一个点的时戳。

必需参数
名称类型描述
twsTimeWeightSummary来自 time_weight() 调用的输入 TimeWeightSummary。
返回
列类型描述
last_timeTIMESTAMPTZTimeWeightSummary 中最后一个点的时。
示例

在列 val 上生成线性 TimeWeightSummary 并获取最后一个时间戳

WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        time_weight('Linear', ts, val) AS tw
    FROM table
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    last_time(tw)
FROM t;
last_val(
    tw TimeWeightSummary
) RETURNS DOUBLE PRECISION

获取 TimeWeightSummary 聚合中最后一个点的值。

必需参数
名称类型描述
twsTimeWeightSummary来自 time_weight() 调用的输入 TimeWeightSummary。
返回
列类型描述
last_valDOUBLE PRECISIONTimeWeightSummary 中最后一个点的值。
示例

在列 val 上生成线性 TimeWeightSummary 并获取最后一个值

WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        time_weight('Linear', ts, val) AS tw
    FROM table
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    last_val(tw)
FROM t;
rollup(
    tws TimeWeightSummary
) RETURNS TimeWeightSummary

将由time_weight()生成的多个中间时间加权聚合（TimeWeightSummary）对象合并到单个中间TimeWeightSummary对象中。例如，您可以使用rollup将来自15分钟时段的时间加权聚合合并到每日时段中。

必需参数
名称类型描述
time_weightTimeWeightSummary由time_weight调用生成的TimeWeightSummary聚合
返回
列类型描述
rollupStatsSummary1D通过组合输入TimeWeightSummary聚合生成的新TimeWeightSummary聚合

扩展示例

将数据聚合到TimeWeightSummary中并计算平均值

给定一个名为foo的表，其中包含val列中的数据，将数据聚合到每日TimeWeightSummary中。使用它计算val列的平均值

WITH t as (
    SELECT
        time_bucket('1 day'::interval, ts) as dt,
        time_weight('Linear', ts, val) AS tw
    FROM foo
    WHERE measure_id = 10
    GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
    dt,
    average(tw)
FROM t;

高级用法

并行性和排序

如PostgreSQL所定义，时间加权平均值计算不是严格可并行的。这些计算要求输入严格排序，但通常情况下，PostgreSQL通过将行随机分配给工作进程来实现并行化。

但是，如果保证某个时间范围内的所有行都进入同一个工作进程，则算法可以实现并行化。这对于连续聚合和分布式超表都是适用的。（请注意，分布式超表的划分键必须位于GROUP BY子句中，但这通常是情况。）

跨测量系列组合聚合

如果您尝试组合重叠的TimeWeightSummaries，则会抛出错误。例如，您可能为device_1创建一个TimeWeightSummary，并为device_2创建一个单独的TimeWeightSummary，两者都涵盖相同的时期。您无法将它们组合在一起，因为插值技术只有在限制到单个测量系列时才有意义。

如果您想计算跨所有设备的单个摘要统计信息，请使用简单的平均值，例如

WITH t as (SELECT measure_id,
        average(
            time_weight('LOCF', ts, val)
        ) as time_weighted_average
    FROM foo
    GROUP BY measure_id)
SELECT avg(time_weighted_average) -- use the normal avg function to average the time-weighted averages
FROM t;

多节点中的并行性

时间加权平均函数在PostgreSQL意义上不是严格可并行的。PostgreSQL要求可并行函数接受潜在重叠的输入。如上所述，时间加权函数不这样做。但是，它们确实支持部分聚合和多节点设置中的分区级聚合。

减少内存使用

由于时间加权聚合需要排序集，因此它们会构建一个输入数据的缓冲区，对其进行排序，然后执行聚合步骤。当内存太小而无法构建足够的点缓冲区时，您可能会看到内存不足错误或其他问题。在这种情况下，请尝试使用多级聚合。例如

WITH t as (SELECT measure_id,
    time_bucket('1 day'::interval, ts),
    time_weight('LOCF', ts, val)
    FROM foo
    GROUP BY measure_id, time_bucket('1 day'::interval, ts)
    )
SELECT measure_id,
    average(
        rollup(time_weight)
    )
FROM t
GROUP BY measure_id;

关键字

时间加权计算超函数工具包

发现此页面存在问题？报告问题 或 编辑此页面 在GitHub上。

上一页gauge_agg下一页降采样

相关内容

超函数
使用超函数简化数据分析
降采样函数
降采样函数的API参考
days_in_month()
计算给定timestamptz的月份天数
month_normalize()
根据月份的天数对月度指标进行归一化
饱和数学函数
饱和数学函数的API参考
state_agg()
state_agg() 函数的API参考