Datalayers 文档

简体中文

English

Appearance

时序数据插值功能详解 ​

概述 ​

时序数据插值功能是 Datalayers 提供的数据补全和填充解决方案，用于处理时序数据中的数据缺失和采样窗口空白问题。通过智能的插值算法，确保时序数据的连续性和完整性。

核心价值 ​

  • 缺失值填充：自动识别并填充数据缺失的时间窗口
  • 连续性保持：确保时间序列的连续性，便于分析和可视化
  • 可视化优化：为图表和仪表板提供连续的数据流

基础语法 ​

为了实现插值，您需要：

  1. 对时间戳列应用 date_bin_gapfill 函数，并将其作为分组列之一。
    • 函数签名：date_bin_gapfill(<interval>, <timestamp_column>[, <origin>])。
      • interval：采样窗口大小，可以为 INTERVAL 类型的字面值，或者结果为 INTERVAL 类型的表达式。
      • timestamp_column：时间戳列，列约束中必须包含 NOT NULL。
      • origin：可选参数，指定根据哪个时间戳以对齐时间窗口。
  2. 对聚合列应用插值函数。
    • 线性插值：fill_linear(<aggregate_col>)。
    • 使用该列前一个非空值填充：fill_prev(<aggregate_col>)。
    • 使用给定值填充：fill_value(<aggregate_col>, <value>)。
  3. 使用 WHERE 从句给定查询的时间范围。
    • 时间范围必须为有限范围，即有限的起始时间戳与有限的结束时间戳。
    • 注意，插值的起始时间戳为 min(p_min, d_min)，终止时间戳为 max(p_max, d_max)。
      • p_min 与 p_max 分别为查询条件中的时间范围的下界与上界。
      • d_min 与 d_max 分别为原始数据中最小时间戳与最大时间戳。

语法示例：

sql
SELECT 
    date_bin_gapfill(interval 1 hour, time) as window,
    fill_linear(avg(usage_user)),
    fill_prev(avg(usage_system)),
    fill_value(avg(usage_idle), 25.0)
FROM cpu
WHERE time >= '2024-01-16T08:00:00' AND time <= '2024-01-17T12:00:00'
GROUP BY window

date_bin_gapfill 函数 ​

我们提供了一个 date_bin 函数用来将数据划分到采样窗口。在此基础上，date_bin_gapfill 函数还会额外补全缺失的采样窗口。让我们用一个简单的例子来解释。

给定原始时间序列：

text
+---------------------------+
| 2024-01-16T10:40:00+08:00 |
| 2024-01-16T11:40:00+08:00 |
| 2024-01-16T12:40:00+08:00 |
+---------------------------+

将采样窗口大小设定为 30 分钟，应用 date_bin 函数后，每个时间戳被划分到它所属的采样窗口。 例如， 2024-01-16T10:40:00+08:00 这条记录被划分到起始时间戳为 2024-01-16T10:30:00+08:00 的采样窗口。

text
+---------------------------+
| 2024-01-16T10:30:00+08:00 |
| 2024-01-16T11:30:00+08:00 |
| 2024-01-16T12:30:00+08:00 |
+---------------------------+

使用同样的窗口大小，应用 date_bin_gapfill 函数后，缺失的采样窗口得到补全。 例如，在起始时间为 2024-01-16T10:30:00+08:00 的窗口和起始时间为 2024-01-16T11:30:00+08:00 的窗口之间存在一个缺失的、起始时间为 2024-01-16T11:00:00+08:00 的采样窗口。这些缺失的采样窗口会被补全。

text
+---------------------------+
| 2024-01-16T10:30:00+08:00 |
| 2024-01-16T11:00:00+08:00 |
| 2024-01-16T11:30:00+08:00 |
| 2024-01-16T12:00:00+08:00 |
| 2024-01-16T12:30:00+08:00 |
+---------------------------+

与 date_bin 函数类似，date_bin_gapfill 函数也支持一个可选的 origin 参数。这个参数控制采样窗口根据哪个时间戳进行对齐。 origin 默认为 UTC 时区的 1970-01-01T00:00:00 时间。 对于我们的例子，如果将 origin 设置为 1970-01-01T00:05:00，则应用 date_bin_gapfill 后的结果为：

text
+---------------------------+
| 2024-01-16T10:35:00+08:00 |
| 2024-01-16T11:05:00+08:00 |
| 2024-01-16T11:35:00+08:00 |
| 2024-01-16T12:05:00+08:00 |
| 2024-01-16T12:35:00+08:00 |
+---------------------------+

插值函数 ​

插值函数用来对每个缺失的采样窗口中的聚合列进行填充。我们目前支持三种填充策略，分别对应三个插值函数：

  • fill_linear：对于一个缺失行，使用该列前一个非空行以及后一个非空行的线性插值进行填充。如果前或后没有非空行，则不填充。
  • fill_prev：对于一个缺失行，使用该列前一个非空行的值进行填充。如果前面没有非空行，则不填充。
  • fill_value：对于一个缺失行，使用给定的值进行填充。

特别注意，如果 SQL 语句中包含时间戳列之外的分组列，那么插值会针对每个分组单独执行。这是考虑到不同分组的数据可能没有显著的关联，如果允许跨分组插值，可能得到异常点（Outlier）。

date_bin、date_bin_gapfill 函数的起始时间 ​

默认情况下，date_bin 和 date_bin_gapfill 的起始时间对齐参数 origin 是 ​UTC 时区的 1970-01-01T00:00:00​（Unix Epoch）。因此，如果函数中未显式指定 origin，窗口会从 ​UTC 时间的 0 点​ 开始对齐，而非本地时区的 0 点。

若数据时间戳为 2024-01-16T10:40:00+08:00（东八区），date_bin('1 day', time) 会将其对齐到 ​UTC 的 0 点​（即东八区的早上 8 点），而非东八区本地 0 点。 若需从北京时间 0 点对齐，需显式指定 origin 如下：

sql
date_bin('1 day', time, '2024-01-01T00:00:00+08:00')

其他注意事项 ​

  • date_bin_gapfill 只能作为投影列，且必须作为分组列。
  • 插值函数只能作为投影列，不能出现在其他从句中。
  • date_bin_gapfill 和插值函数只能作为最外层的表达式。例如 count(date_bin_gapfill(..))、max(fill_prev(avg(usage_user))) 都是不允许的。
  • date_bin_gapfill 在一个 SQL 中最多只能出现一次。
  • date_bin_gapfill 的 interval 和 origin 参数可以为字面量，也可以为表达式，只要表达式的类型符合要求。
  • 插值函数必须和 date_bin_gapfill 函数联合使用，但是 date_bin_gapfill 函数可以单独使用。
  • 每个聚合列最多只能应用一个插值函数。例如 SELECT date_bin_gapfill(...), fill_linear(avg(usage_user)), fill_prev(avg(usage_user)) FROM CPU ... 是不允许的。
  • 插值后的结果总会使用分组列进行排序。
  • 插值后的数据的时间范围为 WHERE 从句中指定的时间范围与每个分组内数据的时间范围的较大者。
  • fill_linear、fill_prev、fill_value 实际上是函数的别名。它们的标准名称分别为 interpolate、locf、value。在 SQL 语句中，可以随意使用标准名称或者别名。但是在打印的结果集中，我们总是使用标准名称作为列名。

示例 ​

给定 cpu 表的原始数据如下：

text
+---------------------------+------------+------------+--------------+------------+
| time                      | region     | usage_user | usage_system | usage_idle |
+---------------------------+------------+------------+--------------+------------+
| 2024-01-16T10:40:00+08:00 | us-west    | 55.0       | 25.0         | 20.0       |
| 2024-01-16T11:40:00+08:00 | us-east    | 56.5       | 24.0         | 19.5       |
| 2024-01-16T12:40:00+08:00 | us-central | 54.0       | 26.0         | 20.0       |
| 2024-01-16T15:40:00+08:00 | eu-west    | 58.0       | 22.0         | 20.0       |
| 2024-01-16T18:40:00+08:00 | ap-south   | 55.5       | 25.0         | 19.5       |
| 2024-01-16T19:40:00+08:00 | eu-east    | 59.0       | 21.5         | 19.5       |
| 2024-01-16T22:40:00+08:00 | us-west    | 57.0       | 23.5         | 19.5       |
| 2024-01-17T04:40:00+08:00 | us-east    | 60.0       | 20.5         | 19.5       |
| 2024-01-17T05:40:00+08:00 | us-central | 58.5       | 22.5         | 19.0       |
| 2024-01-17T08:40:00+08:00 | eu-west    | 61.0       | 19.5         | 19.5       |
| 2024-01-17T11:40:00+08:00 | ap-south   | 59.5       | 20.0         | 20.5       |
| 2024-01-17T15:20:00+08:00 | eu-east    | 60.5       | 19.0         | 20.5       |
| 2024-01-17T18:20:00+08:00 | us-west    | 62.0       | 18.5         | 19.5       |
| 2024-01-17T21:20:00+08:00 | us-east    | 63.0       | 18.0         | 19.0       |
| 2024-01-18T00:20:00+08:00 | us-central | 61.5       | 19.0         | 19.5       |
| 2024-01-18T03:20:00+08:00 | eu-west    | 62.5       | 17.5         | 20.0       |
| 2024-01-18T06:20:00+08:00 | ap-south   | 63.5       | 17.0         | 19.5       |
+---------------------------+------------+------------+--------------+------------+

将 region 和 time 作为分组列，使用 date_bin_gapfill 对数据进行分组以及窗口补全， 查询 region = 'eu-west' 分组内 usage_user、usage_system、usage_idle 三个列的平均值：

text
+---------------------------+---------+---------------------+-----------------------+---------------------+
| bin                       | region  | avg(cpu.usage_user) | avg(cpu.usage_system) | avg(cpu.usage_idle) |
+---------------------------+---------+---------------------+-----------------------+---------------------+
| 2024-01-16T08:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-16T11:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-16T14:00:00+08:00 | eu-west | 58.0                | 22.0                  | 20.0                |
| 2024-01-16T17:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-16T20:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-16T23:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-17T02:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-17T05:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-17T08:00:00+08:00 | eu-west | 61.0                | 19.5                  | 19.5                |
| 2024-01-17T11:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-17T14:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-17T17:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-17T20:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-17T23:00:00+08:00 | eu-west |                     |                       |                     |
| 2024-01-18T02:00:00+08:00 | eu-west | 62.5                | 17.5                  | 20.0                |
| 2024-01-18T05:00:00+08:00 | eu-west |                     |                       |                     |
+---------------------------+---------+---------------------+-----------------------+---------------------+

在以上查询中加入插值函数，使用 interpolate、locf、value 插值函数分别对 usage_user、usage_system、usage_idle 列的平均值，进行填充：

text
+---------------------------+---------+----------------------------------+-----------------------------+----------------------------------------+
| window                    | region  | interpolate(avg(cpu.usage_user)) | locf(avg(cpu.usage_system)) | value(avg(cpu.usage_idle),Float64(25)) |
+---------------------------+---------+----------------------------------+-----------------------------+----------------------------------------+
| 2024-01-16T08:00:00+08:00 | eu-west |                                  |                             | 25.0                                   |
| 2024-01-16T11:00:00+08:00 | eu-west |                                  |                             | 25.0                                   |
| 2024-01-16T14:00:00+08:00 | eu-west | 58.0                             | 22.0                        | 20.0                                   |
| 2024-01-16T17:00:00+08:00 | eu-west | 58.5                             | 22.0                        | 25.0                                   |
| 2024-01-16T20:00:00+08:00 | eu-west | 59.0                             | 22.0                        | 25.0                                   |
| 2024-01-16T23:00:00+08:00 | eu-west | 59.5                             | 22.0                        | 25.0                                   |
| 2024-01-17T02:00:00+08:00 | eu-west | 60.0                             | 22.0                        | 25.0                                   |
| 2024-01-17T05:00:00+08:00 | eu-west | 60.5                             | 22.0                        | 25.0                                   |
| 2024-01-17T08:00:00+08:00 | eu-west | 61.0                             | 19.5                        | 19.5                                   |
| 2024-01-17T11:00:00+08:00 | eu-west | 61.25                            | 19.5                        | 25.0                                   |
| 2024-01-17T14:00:00+08:00 | eu-west | 61.5                             | 19.5                        | 25.0                                   |
| 2024-01-17T17:00:00+08:00 | eu-west | 61.75                            | 19.5                        | 25.0                                   |
| 2024-01-17T20:00:00+08:00 | eu-west | 62.0                             | 19.5                        | 25.0                                   |
| 2024-01-17T23:00:00+08:00 | eu-west | 62.25                            | 19.5                        | 25.0                                   |
| 2024-01-18T02:00:00+08:00 | eu-west | 62.5                             | 17.5                        | 20.0                                   |
| 2024-01-18T05:00:00+08:00 | eu-west |                                  | 17.5                        | 25.0                                   |
+---------------------------+---------+----------------------------------+-----------------------------+----------------------------------------+

© 2025 Datalayers All rights reserved