time_bucket_gapfill() 函数

社区社区函数在 Timescale Community Edition 下可用。点击了解更多。

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

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

重要提示

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

此组中的函数

time_bucket_gapfill: 按时间间隔对 bucket 行进行分组，同时填充数据中的空白

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

函数详情

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

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

必需参数

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

可选参数

名称	类型	描述
`timezone`	`TEXT`	用于 bucketing 的时区。例如，`Europe/Berlin`。在 TimescaleDB 2.9 或更高版本中可用。不适用于基于整数的时间。如果您有一个未类型的 `start` 或 `finish` 参数和一个 `timezone` 参数，您可能会遇到未将参数传递给您期望的参数的问题。为了解决这个问题，可以命名您的参数或显式地进行类型转换。
`start`	`TIMESTAMPTZ`, `INTEGER`	要填充空白的期间的开始。`start` 之前的值会传递，但不执行 gapfilling。仅当您的时间列是基于整数时才使用 `INTEGER`。最佳实践是使用 `WHERE` 子句。指定 `start` 是遗留方法。`WHERE` 性能更高，因为查询计划器可以通过约束排除来过滤数据块。
`finish`	`TIMESTAMPTZ`, `INTEGER`	要填充空白的期间的结束。`finish` 之后的值会传递，但不执行 gapfilling。仅当您的时间列是基于整数时才使用 `INTEGER`。最佳实践是使用 `WHERE` 子句。指定 `finish` 是遗留方法。`WHERE` 性能更高，因为查询计划器可以通过约束排除来过滤数据块。

返回值

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

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`	如果没有可用于 gapfilling 的先前值，请使用 `prev` 查找表达式来获取先前值。例如，您可以使用 `prev` 来填充查询时间范围中的第一个 bucket。表达式必须返回一个 `(time, value)` 元组，其类型与 bucket 时间和值相对应。
`next`	`EXPRESSION`	如果没有可用于 gapfilling 的下一个值，请使用 `next` 查找表达式来获取下一个值。例如，您可以使用 `next` 来填充查询时间范围中的最后一个 bucket。表达式必须返回一个 `(time, value)` 元组，其类型与 bucket 时间和值相对应。

返回值

列	类型	描述
`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`	如果没有可用于 gapfilling 的先前值，请使用 `prev` 查找表达式来获取先前值。例如，您可以使用 `prev` 来填充查询时间范围中的第一个 bucket。表达式必须仅返回一个值（而不是 `interpolate` 函数预期的元组），其类型与 `value` 参数的类型相同。
`treat_null_as_missing`	`BOOLEAN`	当为 `true` 时，`NULL` 值将被忽略，并且仅向前结转非 `NULL` 值。

返回值

列	类型	描述
`locf`	`ANY ELEMENT`	填充空白的值。返回类型是 `value` 的类型。

扩展示例

使用不带 gapfilling 算法的 `time_bucket_gapfill`

获取每日平均指标值。使用 time_bucket_gapfill 而不指定 gapfilling 算法。这会将缺失值保留为 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 作为时区获取每日平均指标值。请注意，每日时间 bucket 现在在 23:00 UTC 开始，这相当于所选日期的柏林午夜


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() 函数

重要提示

此组中的函数

Bucket

插值器

函数详情