时间窗口

在时序场景中，数据通常是流式的，流数据通常是无穷无尽的，我们无法知道什么时候数据源会继续/停止发送数据，所以在流上处理聚合事件（count、sum 等）的处理方式与批处理中的处理方式会有所差异。在时序数据流上一般用时间窗口（Windows）来限定聚合的范围，例如“过去 2 分钟网站点击量的计数”。时间窗口的概念相当于帮我们根据采集时间收集了一张有限数据的动态表，我们可以对表中的数据进行聚合计算。随着时间的推移，这个窗口会向前滑动，持续捕捉新的数据进行计算。

时间窗口分为 Tumble Window（翻滚窗口）和 Sliding Window（滑动窗口）。Tumble Window 的时间窗口长度固定，每个窗口时间不重叠。而 Sliding Window 的时间窗口长度也固定，但是窗口之间有重叠部分，可以更频繁地捕捉数据变化。

用户使用时间窗口功能时，可以在每个时间窗口内做计算，窗口随着时间的流动向前滑动。在定义连续查询的时候需要指定时间窗口的大小和下个窗口向前的增量时间。

降采样 (Downsampling)

降采样是指从大量数据中提取较小、管理性更强的数据子集的过程。这在处理大规模时间序列数据时尤为重要，可以减少存储需求，提高查询效率，并在数据可视化中提供更清晰的趋势图。时间窗口功能是数据库实现降采样功能的核心能力，通过定义时间窗口，我们可以在每个窗口内对数据进行聚合，从而实现降采样。时间窗口的大小和滑动距离决定了降采样的粒度。

时序表与时间窗口语法

在 MatrixOne 中，时间窗口需要结合时序表来使用，时序表是指建表时必须以 ts 列为主键的表，且类型为 timestamp.

  1. DDL Clause:
  2.     CREATE TABLE TS_TBL (ts timestamp(6) primary key, SIGNAL1 FLOAT, SIGNAL2 DOUBLE, ...);
  4. time_window_clause:
  5.     INTERVAL(timestamp_col, interval_val, time_unit) [SLIDING (sliding_val)] [fill_clause]
  7. time_unit:
  8.     SECOND | MINUTE | HOUR | DAY
  10. fill_clause:
  11.     FILL(NONE | PREV | NEXT | NULL | VALUE, val | LINEAR)

创建时序表的时候 ts 列可以指定 timestamp 的精度，最大为 timestamp(6)（微秒级别）。

INTERVAL 语句中的参数含义：

  • timestamp_col：时间戳列。
  • interval_val：时间窗口的长度。
  • time_unit：时间单位（秒、分钟、小时、天）。
  • SLIDING (sliding_val)：可选，指定窗口滑动的时间距离。
  • FILL(fill_method)：可选，指定如何填充窗口内的数据。

INTERVAL (timestamp_col, interval_val) 作用于数据产生相当时间周期 interval_val 窗口，SLIDING 用以指定窗口向前滑动的 sliding_val 时间距离。

  • 当 interval_val 等于 sliding_val 时为 Tumble window。

  • 当 interval_val 大于 sliding_val 时为 Sliding window。

其他使用说明：

  • INTERVAL 和 SLIDING 子句需要配合聚合或选择函数来使用，目前时间窗口中会支持：max, min, sum, avg, count 聚合函数。
  • 聚合时间段的窗口宽度由关键词 INTERVAL 指定，最短时间间隔 1 秒。
  • 时间窗口返回的结果中时间序列严格单调递增。
  • interval_val 必须为正整数。
  • 用 INTERVAL 查询时，_wstart(ts)，_wend(ts) 是根据窗口生成出来的伪列，分别是窗口的开始时间和结束时间。

使用示例：

这个示例演示如何在 10 分钟的时间窗口内，每 5 分钟滑动一次，给出每 5 分钟的温度最大最小值。

  1. mysql> drop table if exists sensor_data;
  2. CREATE TABLE sensor_data (ts timestamp(3) primary key, temperature FLOAT);
  3. INSERT INTO sensor_data VALUES('2023-08-01 00:00:00', 25.0);
  4. INSERT INTO sensor_data VALUES('2023-08-01 00:05:00', 26.0);
  5. INSERT INTO sensor_data VALUES('2023-08-01 00:15:00', 28.0);
  6. INSERT INTO sensor_data VALUES('2023-08-01 00:20:00', 30.0);
  7. INSERT INTO sensor_data VALUES('2023-08-01 00:25:00', 27.0);
  8. INSERT INTO sensor_data VALUES('2023-08-01 00:30:00', null);
  9. INSERT INTO sensor_data VALUES('2023-08-01 00:35:00', null);
  10. INSERT INTO sensor_data VALUES('2023-08-01 00:40:00', 28);
  11. INSERT INTO sensor_data VALUES('2023-08-01 00:45:00', 38);
  12. INSERT INTO sensor_data VALUES('2023-08-01 00:50:00', 31);
  13. insert into sensor_data values('2023-07-31 23:55:00', 22);
  14. mysql> select _wstart, _wend, max(temperature), min(temperature) from sensor_data where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00" interval(ts, 10, minute) sliding(5, minute);
  15. +-------------------------+-------------------------+------------------+------------------+
  16. | _wstart                 | _wend                   | max(temperature) | min(temperature) |
  17. +-------------------------+-------------------------+------------------+------------------+
  18. | 2023-08-01 00:00:00.000 | 2023-08-01 00:10:00.000 |               26 |               26 |
  19. | 2023-08-01 00:05:00.000 | 2023-08-01 00:15:00.000 |               26 |               26 |
  20. | 2023-08-01 00:10:00.000 | 2023-08-01 00:20:00.000 |               28 |               28 |
  21. | 2023-08-01 00:15:00.000 | 2023-08-01 00:25:00.000 |               30 |               28 |
  22. | 2023-08-01 00:20:00.000 | 2023-08-01 00:30:00.000 |               30 |               27 |
  23. | 2023-08-01 00:25:00.000 | 2023-08-01 00:35:00.000 |               27 |               27 |
  24. | 2023-08-01 00:30:00.000 | 2023-08-01 00:40:00.000 |             NULL |             NULL |
  25. | 2023-08-01 00:35:00.000 | 2023-08-01 00:45:00.000 |               28 |               28 |
  26. | 2023-08-01 00:40:00.000 | 2023-08-01 00:50:00.000 |               38 |               28 |
  27. | 2023-08-01 00:45:00.000 | 2023-08-01 00:55:00.000 |               38 |               38 |
  28. +-------------------------+-------------------------+------------------+------------------+
  29. 10 rows in set (0.04 sec)

插值

在处理时序数据时，经常会遇到缺失值的情况。插值（FILL）功能允许我们以多种方式填充这些缺失值，确保数据的连续性和完整性，这对于数据分析和降采样过程至关重要。时间窗口的 FIll 子句作用于填充聚合结果。

MatrixOne 提供多种插值方法，以适应不同的数据处理需求：

  • FILL(NONE): 不进行填充，即列不变
  • FILL(VALUE, expr): 填充 expr 结果
  • FILL(PREV): 使用前一个非 NULL 值填充数据
  • FILL(NEXT): 使用下一个非 NULL 值填充数据
  • FILL(LINEAR): 根据前后距离最近的非 NULL 值做线性插值填充

使用示例：

这个示例在之前那张表的基础上增加了插值逻辑，会将 NULL 值填充。

  1. select _wstart(ts), _wend(ts), max(temperature), min(temperature) from sensor_data where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00.000" interval(ts, 10, minute) sliding(5, minute) fill(prev);
  2.          _wstart         |          _wend          |   max(temperature)   |   min(temperature)   |
  3. ==================================================================================================
  4.  2023-08-01 00:00:00.000 | 2023-08-01 00:10:00.000 |           26.0000000 |           26.0000000 |
  5.  2023-08-01 00:05:00.000 | 2023-08-01 00:15:00.000 |           26.0000000 |           26.0000000 |
  6.  2023-08-01 00:10:00.000 | 2023-08-01 00:20:00.000 |           28.0000000 |           28.0000000 |
  7.  2023-08-01 00:15:00.000 | 2023-08-01 00:25:00.000 |           30.0000000 |           28.0000000 |
  8.  2023-08-01 00:20:00.000 | 2023-08-01 00:30:00.000 |           30.0000000 |           27.0000000 |
  9.  2023-08-01 00:25:00.000 | 2023-08-01 00:35:00.000 |           27.0000000 |           27.0000000 |
  10.  2023-08-01 00:30:00.000 | 2023-08-01 00:40:00.000 |           27.0000000 |           27.0000000 |
  11.  2023-08-01 00:35:00.000 | 2023-08-01 00:45:00.000 |           28.0000000 |           28.0000000 |
  12.  2023-08-01 00:40:00.000 | 2023-08-01 00:50:00.000 |           38.0000000 |           28.0000000 |
  13.  2023-08-01 00:45:00.000 | 2023-08-01 00:55:00.000 |           38.0000000 |           38.0000000 |