如何在Apache IoTDB表模型中实现类M4函数的降采样？

阿华AIGC实验室

2026-6-11

Apache IoTDB 2.0.5表模型不支持M4函数的替代方案

核心结论

Apache IoTDB 2.0.5的表模型暂不支持M4函数，该函数仅在树形（时间序列）模型中提供，表模型的函数支持范围与树形模型存在差异，官方文档未提及表模型支持M4也验证了这一点。

替代实现方案

要在表模型中实现按指定时间窗口提取首点、末点、最小值、最大值并保留时间戳的效果，可以结合TIME_SLICE窗口划分函数、聚合函数（MIN/MAX）以及窗口函数（FIRST_VALUE/LAST_VALUE）来实现，具体SQL如下：

完整查询SQL

-- 提取每个窗口的首点
SELECT first_ts AS `Time`, first_val AS feature_value, 'First' AS feature_type
FROM (
  SELECT 
    TIME_SLICE(timestamp, 3000) AS window_start,
    FIRST_VALUE(timestamp) OVER (PARTITION BY TIME_SLICE(timestamp, 3000) ORDER BY timestamp) AS first_ts,
    FIRST_VALUE(value) OVER (PARTITION BY TIME_SLICE(timestamp, 3000) ORDER BY timestamp) AS first_val
  FROM sensor_readings 
  WHERE device='device01'
) t
GROUP BY window_start

UNION ALL

-- 提取每个窗口的末点
SELECT last_ts AS `Time`, last_val AS feature_value, 'Last' AS feature_type
FROM (
  SELECT 
    TIME_SLICE(timestamp, 3000) AS window_start,
    LAST_VALUE(timestamp) OVER (PARTITION BY TIME_SLICE(timestamp, 3000) ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_ts,
    LAST_VALUE(value) OVER (PARTITION BY TIME_SLICE(timestamp, 3000) ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val
  FROM sensor_readings 
  WHERE device='device01'
) t
GROUP BY window_start

UNION ALL

-- 提取每个窗口的最小值点（取第一个出现最小值的时间戳）
SELECT min_ts AS `Time`, min_val AS feature_value, 'Min' AS feature_type
FROM (
  SELECT 
    TIME_SLICE(timestamp, 3000) AS window_start,
    MIN(value) AS min_val,
    (SELECT timestamp FROM sensor_readings WHERE device='device01' AND value = MIN(value) AND TIME_SLICE(timestamp, 3000) = window_start LIMIT 1) AS min_ts
  FROM sensor_readings
  WHERE device='device01'
  GROUP BY window_start
) t

UNION ALL

-- 提取每个窗口的最大值点（取第一个出现最大值的时间戳）
SELECT max_ts AS `Time`, max_val AS feature_value, 'Max' AS feature_type
FROM (
  SELECT 
    TIME_SLICE(timestamp, 3000) AS window_start,
    MAX(value) AS max_val,
    (SELECT timestamp FROM sensor_readings WHERE device='device01' AND value = MAX(value) AND TIME_SLICE(timestamp, 3000) = window_start LIMIT 1) AS max_ts
  FROM sensor_readings
  WHERE device='device01'
  GROUP BY window_start
) t

ORDER BY `Time`, feature_type;

结果说明

执行上述SQL后，会按3秒时间窗口输出每个窗口的四个特征点，每行包含：

Time：特征点的原始时间戳
feature_value：特征点的数值
feature_type：特征类型（首点/末点/最小值/最大值）

针对你的测试数据，返回结果示例如下：

+-----------------------------+---------------+--------------+
| `Time`                      | feature_value | feature_type |
+-----------------------------+---------------+--------------+
| 1970-01-01T08:00:01.000+08:00| 10.0          | First        |
| 1970-01-01T08:00:01.000+08:00| 10.0          | Min          |
| 1970-01-01T08:00:02.000+08:00| 15.0          | Max          |
| 1970-01-01T08:00:03.000+08:00| 12.0          | Last         |
| 1970-01-01T08:00:04.000+08:00| 18.0          | First        |
| 1970-01-01T08:00:04.000+08:00| 18.0          | Max          |
| 1970-01-01T08:00:05.000+08:00| 11.0          | Min          |
| 1970-01-01T08:00:06.000+08:00| 16.0          | Last         |
+-----------------------------+---------------+--------------+