time_bucket_gapfill() 函数

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

自托管产品

MST

按时间间隔聚合数据，同时填补缺失数据的空白。

time_bucket_gapfill 的工作原理与 time_bucket 类似，但增加了缺口填充功能。此组中的其他函数必须与 time_bucket_gapfill 在同一查询中使用。它们控制如何处理缺失值。

重要提示

time_bucket_gapfill 必须用作查询或子查询中的顶层表达式。例如，您不能将 time_bucket_gapfill 嵌套在另一个函数中（如 round(time_bucket_gapfill(...))），也不能转换缺口填充调用的结果。如果需要转换，可以在子查询中使用 time_bucket_gapfill，然后让外部查询执行类型转换。

此组中的函数

time_bucket_gapfill: 按时间间隔对行进行分桶，同时填充数据中的空缺

interpolate: 通过线性插值填充缺失值
locf: 通过将最后观察到的值向前填充来填充缺失值

函数详情

time_bucket_gapfill(
  bucket_width INTERVAL | INTEGER,
  time TIMESTAMPTZ | INTEGER,
  [, timezone TEXT]
  [, start TIMESTAMPTZ | INTEGER]
  [, finish TIMESTAMPTZ | INTEGER]
) RETURNS TIMESTAMPTZ

根据时间间隔将数据分组到桶中，同时填补缺失数据的空缺。如果您不提供缺口填充算法，例如 locf 或 interpolate，则返回的数据中空缺将保留为 NULL。

必需参数

名称	类型	描述
`bucket_width`	`INTERVAL`, `INTEGER`	一个 PostgreSQL 时间间隔，用于指定每个桶的长度。例如，使用 `1 day` 获取每日桶。仅当您的时间列是基于整数时才使用 `INTEGER`。
`time`	`TIMESTAMPTZ`, `INTEGER`	作为分桶依据的时间戳

可选参数

名称	类型	描述
`timezone`	`TEXT`	用于分桶的时区。例如，`Europe/Berlin`。在 TimescaleDB 2.9 或更高版本中可用。不适用于基于整数的时间。如果您有未类型化的 `start` 或 `finish` 参数以及 `timezone` 参数，您可能会遇到未能将参数传递给预期参数的问题。要解决此问题，请命名您的参数或显式地进行类型转换。
`start`	`TIMESTAMPTZ`, `INTEGER`	要进行缺口填充的起始时间。`start` 之前的值将直接通过，但不会执行缺口填充。仅当您的时间列基于整数时才使用 `INTEGER`。最佳实践是使用 `WHERE` 子句。指定 `start` 是旧式做法。`WHERE` 的性能更好，因为查询规划器可以通过约束排除过滤掉块。
`finish`	`TIMESTAMPTZ`, `INTEGER`	要进行缺口填充的结束时间。`finish` 之后的值将直接通过，但不会执行缺口填充。仅当您的时间列基于整数时才使用 `INTEGER`。最佳实践是使用 `WHERE` 子句。指定 `finish` 是旧式做法。`WHERE` 的性能更好，因为查询规划器可以通过约束排除过滤掉块。

列	类型	描述
`time_bucket_gapfill`	`TIMESTAMPTZ`	时间桶的开始时间。

interpolate(
  value SMALLINT | INTEGER | BIGINT | REAL | DOUBLE PRECISION
  [, prev EXPRESSION]
  [, next EXPRESSION]
) RETURNS SMALLINT | INTEGER | BIGINT | REAL | DOUBLE PRECISION

通过线性插值填充缺失值。与 time_bucket_gapfill 在同一查询中使用。interpolate 不能嵌套在另一个函数调用中。

必需参数

名称	类型	描述
`value`	`SMALLINT`, `INTEGER`, `BIGINT`, `REAL`, `DOUBLE PRECISION`	要插值的值

可选参数

名称	类型	描述
`prev`	`EXPRESSION`	如果没有可用于缺口填充的先前值，请使用 `prev` 查找表达式来获取先前值。例如，您可以使用 `prev` 填充查询时间范围内的第一个桶。该表达式必须返回一个 `(time, value)` 元组，其类型应与分桶的时间和值相对应。
`next`	`EXPRESSION`	如果没有可用于缺口填充的后续值，请使用 `next` 查找表达式来获取后续值。例如，您可以使用 `next` 填充查询时间范围内的最后一个桶。该表达式必须返回一个 `(time, value)` 元组，其类型应与分桶的时间和值相对应。

列	类型	描述
`interpolate`	`SMALLINT`, `INTEGER`, `BIGINT`, `REAL`, `DOUBLE PRECISION`	填充后的值。返回类型与 `value` 的类型相同。

locf(
  value ANY ELEMENT
  [, prev EXPRESSION]
  [, treat_null_as_missing BOOLEAN]
) RETURNS ANY ELEMENT

通过将最后观察到的值向前填充来填充缺失值。与 time_bucket_gapfill 在同一查询中使用。locf 不能嵌套在另一个函数调用中。

必需参数

名称	类型	描述
`value`	`ANY ELEMENT`	要向前填充的值

可选参数

名称	类型	描述
`prev`	`EXPRESSION`	如果没有可用于缺口填充的先前值，请使用 `prev` 查找表达式来获取先前值。例如，您可以使用 `prev` 填充查询时间范围内的第一个桶。该表达式必须只返回一个值（而不是像 `interpolate` 函数期望的元组），且其类型与 `value` 参数的类型相同。
`treat_null_as_missing`	`BOOLEAN`	当 `true` 时，`NULL` 值将被忽略，只有非 `NULL` 值被向前填充。

列	类型	描述
`locf`	`ANY ELEMENT`	填充后的值。返回类型与 `value` 的类型相同。

扩展示例

不使用缺口填充算法的 `time_bucket_gapfill`

获取每日平均指标值。使用 time_bucket_gapfill 而不指定缺口填充算法。这将使缺失值保留为 NULL。


SELECT time_bucket_gapfill('1 day', time) AS day,
    avg(value) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;


day                    |              value
-----------------------+--------------------
2022-01-09 00:00:00+00 |                   
2022-01-08 00:00:00+00 |  48.61293155993108
2022-01-07 00:00:00+00 | 54.388267525986485
2022-01-06 00:00:00+00 |                   
2022-01-05 00:00:00+00 | 58.257520634785266
2022-01-04 00:00:00+00 |  46.09172424261765
2022-01-03 00:00:00+00 |  42.53498707820027
2022-01-02 00:00:00+00 |                   
2022-01-01 00:00:00+00 |  47.84420001415975
2021-12-31 00:00:00+00 |                   
(10 rows)

使用 `time_bucket_gapfill` 并向前填充最后一个值

获取每日平均指标值。如果值缺失，使用 locf 将最后一个值向前填充。请注意，avg 嵌套在 locf 内部，而不是反过来。


SELECT time_bucket_gapfill('1 day', time) AS day,
    locf(avg(value)) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;


day                    |              value
-----------------------+--------------------
2022-01-09 00:00:00+00 |  48.61293155993108
2022-01-08 00:00:00+00 |  48.61293155993108
2022-01-07 00:00:00+00 | 54.388267525986485
2022-01-06 00:00:00+00 | 58.257520634785266
2022-01-05 00:00:00+00 | 58.257520634785266
2022-01-04 00:00:00+00 |  46.09172424261765
2022-01-03 00:00:00+00 |  42.53498707820027
2022-01-02 00:00:00+00 |  47.84420001415975
2022-01-01 00:00:00+00 |  47.84420001415975
2021-12-31 00:00:00+00 |                   
(10 rows)

使用 `time_bucket_gapfill` 并通过 `prev` 表达式向前填充最后一个值

获取每日平均指标值。使用 locf 的可选 prev 参数来填充查询时间范围开始处的空缺。请注意，prev 表达式只返回一个值来填充空缺。这已足够，因为该值只是向前填充，不再进行进一步处理。


SELECT time_bucket_gapfill('1 day', time) AS day,
    locf(
        avg(value),
        (
            SELECT value
            FROM metrics
            WHERE time > '2021-12-31 00:00:00+00'::timestamptz
            ORDER BY time ASC
            LIMIT 1
        )
    ) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;


day                    |              value
-----------------------+--------------------
2022-01-09 00:00:00+00 |  48.61293155993108
2022-01-08 00:00:00+00 |  48.61293155993108
2022-01-07 00:00:00+00 | 54.388267525986485
2022-01-06 00:00:00+00 | 58.257520634785266
2022-01-05 00:00:00+00 | 58.257520634785266
2022-01-04 00:00:00+00 |  46.09172424261765
2022-01-03 00:00:00+00 |  42.53498707820027
2022-01-02 00:00:00+00 |  47.84420001415975
2022-01-01 00:00:00+00 |  47.84420001415975
2021-12-31 00:00:00+00 |  47.84420001415975
(10 rows)

使用 `time_bucket_gapfill` 并使用线性插值

获取每日平均指标值。如果值缺失，使用 interpolate 进行线性插值。请注意，avg 嵌套在 interpolate 内部。


SELECT time_bucket_gapfill('1 day', time) AS day,
    interpolate(avg(value)) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;


day                    |              value
-----------------------+--------------------
2022-01-09 00:00:00+00 |                   
2022-01-08 00:00:00+00 |  48.61293155993108
2022-01-07 00:00:00+00 | 54.388267525986485
2022-01-06 00:00:00+00 |  56.32289408038588
2022-01-05 00:00:00+00 | 58.257520634785266
2022-01-04 00:00:00+00 |  46.09172424261765
2022-01-03 00:00:00+00 |  42.53498707820027
2022-01-02 00:00:00+00 | 45.189593546180014
2022-01-01 00:00:00+00 |  47.84420001415975
2021-12-31 00:00:00+00 |                   
(10 rows)

使用 `time_bucket_gapfill` 并通过 `prev` 和 `next` 表达式使用线性插值

获取每日平均指标值。使用 interpolate 的可选 prev 和 next 参数，外推查询时间范围开始和结束处的缺失值。请注意，prev 和 next 表达式都返回一个包含时间和值的元组。时间对于正确计算缺失值是必要的。


SELECT time_bucket_gapfill('1 day', time) AS day,
    interpolate(
        avg(value),
        (
            SELECT (time, value)
            FROM metrics
            WHERE time > '2021-12-31 00:00:00+00'::timestamptz
            ORDER BY time ASC
            LIMIT 1
        ),
        (
            SELECT (time, value)
            FROM metrics
            WHERE time < '2021-12-10 00:00:00-00'::timestamptz
            ORDER BY time DESC
            LIMIT 1
        )
    ) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;


day                    |              value
-----------------------+--------------------
2022-01-09 00:00:00+00 |  48.61293155993108
2022-01-08 00:00:00+00 |  48.61293155993108
2022-01-07 00:00:00+00 | 54.388267525986485
2022-01-06 00:00:00+00 |  56.32289408038588
2022-01-05 00:00:00+00 | 58.257520634785266
2022-01-04 00:00:00+00 |  46.09172424261765
2022-01-03 00:00:00+00 |  42.53498707820027
2022-01-02 00:00:00+00 | 45.189593546180014
2022-01-01 00:00:00+00 |  47.84420001415975
2021-12-31 00:00:00+00 |  47.84420001415975
(10 rows)

使用带有 timezone 参数的 `time_bucket_gapfill`

获取每日平均指标值，使用 Europe/Berlin 作为时区。请注意，每日时间桶现在从 UTC 23:00 开始，这相当于所选日期在柏林时间的午夜。


SELECT time_bucket_gapfill('1 day', time, 'Europe/Berlin') AS day,
    interpolate(avg(value)) as value
    FROM metrics
    WHERE time > '2021-12-31 00:00:00+00'::timestamptz
        AND time < '2022-01-10 00:00:00-00'::timestamptz
    GROUP BY day
    ORDER BY day desc;


day                    |              value
-----------------------+--------------------
2022-01-09 23:00:00+00 |                   
2022-01-08 23:00:00+00 |  48.65079127913703
2022-01-07 23:00:00+00 |  47.31847777099154
2022-01-06 23:00:00+00 |  55.98845740343859
2022-01-05 23:00:00+00 |  55.61667401777108
2022-01-04 23:00:00+00 |  58.74115574522012
2022-01-03 23:00:00+00 |  45.77993635988273
2022-01-02 23:00:00+00 |  41.78689923453202
2022-01-01 23:00:00+00 | 24.324313477743974
2021-12-31 23:00:00+00 |  48.86680377661261
2021-12-30 23:00:00+00 |                   
(11 rows)

time_bucket_gapfill() 函数

重要提示

此组中的函数

分桶

插值器

函数详情