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: 组合多个 TimeWeightSummary

函数详情

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

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

必需参数

名称	类型	描述
`method`	`TEXT`	要使用的加权方法。可用方法为 `linear` （或其别名 `trapezoidal`，适用于熟悉数值积分方法的人）和 `LOCF`，代表“最后观测值前向结转”。 `linear` 通过在线性插值间隙的起点和终点之间填充缺失数据。 `LOCF` 通过假设该值保持恒定直到看到下一个值来填充间隙。当仅在值更改时才进行测量时，`LOCF` 最有用。如果对测量没有此类保证，则 `linear` 最有用。方法名称不区分大小写。
`ts`	`TIMESTAMPTZ`	每个点的时间。空值将被忽略。在仅 `null` 值上评估的聚合返回 `null`。
`value`	`DOUBLE PRECISION`	每个点的值，用于时间加权聚合。空值将被忽略。在仅 `null` 值上评估的聚合返回 `null`。

返回值

列	类型	描述
`time_weight`	`TimeWeightSummary`	一个 `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 除以经过的时间。

必需参数

名称	类型	描述
`tws`	`TimeWeightSummary`	来自 `time_weight()` 调用的输入 `TimeWeightSummary`。

返回值

列	类型	描述
`average`	`DOUBLE 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 聚合中第一个点的时间戳。

必需参数

名称	类型	描述
`tws`	`TimeWeightSummary`	来自 `time_weight()` 调用的输入 `TimeWeightSummary`。

返回值

列	类型	描述
`first_time`	`TIMESTAMPTZ`	`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 聚合中第一个点的值。

必需参数

名称	类型	描述
`tws`	`TimeWeightSummary`	来自 `time_weight()` 调用的输入 `TimeWeightSummary`。

返回值

列	类型	描述
`first_val`	`DOUBLE 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 乘以经过的时间。

必需参数

名称	类型	描述
`tws`	`TimeWeightSummary`	来自 `time_weight()` 调用的输入 `TimeWeightSummary`。

可选参数

名称	类型	描述
`unit`	`TEXT`	表达积分的时间单位。可以是 `microsecond`、`millisecond`、`second`、`minute`、`hour` 或 PostgreSQL 支持的这些单位的任何别名。默认为 `second`。

返回值

列	类型	描述
`integral`	`DOUBLE 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 除以经过的时间。

必需参数

名称	类型	描述
`tws`	`TimeWeightSummary`	来自 `time_weight()` 调用的输入 `TimeWeightSummary`。
`start`	`TIMESTAMPTZ`	时间加权平均值应覆盖的间隔的开始（如果存在前一个点）。
`interval`	`INTERVAL`	时间加权平均值应覆盖的间隔的长度。

可选参数

名称	类型	描述
`prev`	`TimeWeightSummary`	来自先前间隔的 `TimeWeightSummary`，用于插值 `start` 处的值。如果为 NULL，则 `tws` 中的第一个时间戳用于起始值。先前间隔可以从 PostgreSQL `lag()` 函数确定。
`next`	`TimeWeightSummary`	来自下一个间隔的 `TimeWeightSummary`，用于插值 `start` + `interval` 处的值。如果为 NULL，则 `tws` 中的第一个时间戳用于起始值。下一个间隔可以从 PostgreSQL `lead()` 函数确定。

返回值

列	类型	描述
`average`	`DOUBLE 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 乘以经过的时间。

必需参数

名称	类型	描述
`tws`	`TimeWeightSummary`	来自 `time_weight()` 调用的输入 `TimeWeightSummary`。
`start`	`TIMESTAMPTZ`	时间加权积分应覆盖的间隔的开始（如果存在前一个点）。
`interval`	`INTERVAL`	时间加权积分应覆盖的间隔的长度。

可选参数

名称	类型	描述
`prev`	`TimeWeightSummary`	来自先前间隔的 `TimeWeightSummary`，用于插值 `start` 处的值。如果为 NULL，则 `tws` 中的第一个时间戳用于起始值。先前间隔可以从 PostgreSQL `lag()` 函数确定。
`next`	`TimeWeightSummary`	来自下一个间隔的 `TimeWeightSummary`，用于插值 `start` + `interval` 处的值。如果为 NULL，则 `tws` 中的第一个时间戳用于起始值。下一个间隔可以从 PostgreSQL `lead()` 函数确定。
`unit`	`TEXT`	表达积分的时间单位。可以是 `microsecond`、`millisecond`、`second`、`minute`、`hour` 或 PostgreSQL 支持的这些单位的任何别名。默认为 `second`。

返回值

列	类型	描述
`integral`	`DOUBLE 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 聚合中最后一个点的时间戳。

必需参数

名称	类型	描述
`tws`	`TimeWeightSummary`	来自 `time_weight()` 调用的输入 `TimeWeightSummary`。

返回值

列	类型	描述
`last_time`	`TIMESTAMPTZ`	`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 聚合中最后一个点的值。

必需参数

名称	类型	描述
`tws`	`TimeWeightSummary`	来自 `time_weight()` 调用的输入 `TimeWeightSummary`。

返回值

列	类型	描述
`last_val`	`DOUBLE 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_weight`	`TimeWeightSummary`	由 `time_weight` 调用生成的 `TimeWeightSummary` 聚合

返回值

列	类型	描述
`rollup`	`StatsSummary1D`	通过组合输入 `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 子句中，但这通常是这种情况。）

跨测量序列组合聚合

如果您尝试组合重叠的 TimeWeightSummary，则会抛出错误。例如，您可以为 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;

time_weight() 函数

两步聚合

此组中的函数

聚合

访问器

汇总

函数详情

time_weight()

average()

first_time()

first_val()

integral()

interpolated_average()

interpolated_integral()

last_time()

last_val()

rollup()

扩展示例

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

高级用法

并行性和排序

跨测量序列组合聚合

多节点中的并行性

减少内存使用量

简介

两步聚合

聚合

访问器

汇总

time_weight()

average()

first_time()

first_val()

integral()

interpolated_average()

interpolated_integral()

last_time()

last_val()

rollup()

相关内容