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

time_weight() 函数

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

Timescale Cloud：性能、规模、企业级

自托管产品

MST

简介

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

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

此组中的函数

聚合

time_weight
将数据聚合为中间时间加权聚合形式以进行进一步计算

访问器

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

汇总

rollup
组合多个 `TimeWeightSummaries`

函数详情

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

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

必需参数
名称类型描述
methodTEXT要使用加权方法。可用方法是 `linear` (或其别名 `trapezoidal`，对于熟悉数值积分方法的人) 和 `LOCF`，它代表“最后一次观察值前向填充”（last observation carried forward）。`linear` 通过在线性插值间隔的起点和终点之间填充缺失数据。`LOCF` 通过假设值保持不变直到下一个值出现来填充间隔。`LOCF` 在仅当值改变时才进行测量时最有用。如果测量没有此类保证，则 `linear` 最有用。方法名称不区分大小写。
tsTIMESTAMPTZ每个点的时间。空值将被忽略。仅对空值进行评估的聚合将返回空值。
valueDOUBLE PRECISION每个点用于时间加权聚合的值。空值将被忽略。仅对空值进行评估的聚合将返回空值。
返回
列类型描述
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_timeTIMESTAMPTZ`TimeWeightSummary` 中第一个点的时间
示例

对列 `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 PRECISION`TimeWeightSummary` 中第一个点的值
示例

对列 `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_timeTIMESTAMPTZ`TimeWeightSummary` 中最后一个点的时间
示例

对列 `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 PRECISION`TimeWeightSummary` 中最后一个点的值
示例

对列 `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` 并计算平均值

给定一个包含 `val` 列数据的表 `foo`，将数据聚合到每日 `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下一页降采样

相关内容

超函数
Timescale Cloud 中可用的超函数完整列表，包含必需参数、返回值和完整使用示例
降采样函数
降采样函数的 API 参考
days_in_month()
根据 timestamptz 计算月份天数
month_normalize()
根据月份天数规范化月度指标
饱和数学函数
饱和数学函数的 API 参考
state_agg()
state_agg() 函数的 API 参考