[Feature request] Support FFT and DFT functions in the table model

[JackieTien97]

Search before asking

• I searched in the issues and found nothing similar.

Motivation

Motivation

IoTDB already provides frequency-domain analysis capabilities in the tree model through UDFs such as fft/ifft in library-udf. However, users who work with the table model currently do not have an equivalent built-in or library-level way to run FFT/DFT analysis directly in SQL.

For table-model workloads, frequency-domain analysis is useful for scenarios such as vibration monitoring, rotating machinery diagnosis, signal periodicity analysis, anomaly detection, and sensor data preprocessing. Users should be able to calculate Fourier transform results over a time-ordered numeric column, optionally partitioned by device/tag columns, without switching back to the tree model or exporting data to external tools.

Solution

Add FFT and DFT support for the IoTDB table model.

A possible design is to provide table-model functions such as fft and dft that can process a numeric column ordered by time and return frequency-domain results. The exact SQL syntax can be discussed, but the function should support table-model usage patterns, for example:

SELECT *
FROM TABLE(
  fft(
    TABLE(
      SELECT time, device_id, value
      FROM vibration
      WHERE time >= 0 AND time < 10000
    )
    PARTITION BY device_id
    ORDER BY time
  )
);

### Solution

_No response_

### Alternatives

_No response_

### Are you willing to submit a PR?

- [ ] I'm willing to submit a PR!

[dahyvuun]

I'd like to work on this

[dahyvuun]

Hi @JackieTien97, I've been exploring the codebase and looking at the existing FFT implementation in library-udf and the TableFunction examples. Could you provide some guidance on the expected approach? Should I implement this in library-udf following the TableFunction interface?

[dahyvuun]

I'm planning to implement this in library-udf by creating a new TableFFT class implementing the TableFunction interface, reusing the existing FFTUtil logic.
I'll support result parameter (real/imag/abs/angle) and compress parameter.

[DaZuiZui]

1. 功能定义

FFT 是 IoTDB table model 中的内置表值函数，用于对按分区、按时间排序的实值数值序列执行快速傅里叶变换，并返回频域结果。

它适合处理如下场景：

• 对单个设备的一段时序数据做频域分析；
• 对多个设备或 tag group 分别做 FFT；
• 同时对多个数值列做 FFT，例如 temperature、speed、voltage；
• 输出每个频率 bin 对应的复数结果，即实部和虚部。

v1 只提供 FFT，不单独暴露 DFT。FFT 可视为频域分析的实际高性能实现。是否增加单独的 DFT TVF，可以在后续版本根据明确需求再讨论。

---

2. 语法定义

2.1 基本语法

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature, speed
    FROM sensor
  ) PARTITION BY device_id ORDER BY time
);

2.2 带显式采样间隔

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature, speed
    FROM sensor
  ) PARTITION BY device_id ORDER BY time,
  SAMPLE_INTERVAL => 1ms
);

2.3 只转换部分列

FFT v1 不提供 VALUE 参数。如果用户只想转换部分数值列，应在 DATA 子查询中提前投影。

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature
    FROM sensor
  ) PARTITION BY device_id ORDER BY time,
  SAMPLE_INTERVAL => 1s
);

---

3. 参数定义

DATA

必选参数。

DATA 是输入表参数，提供待转换的时间序列数据。

DATA => (
  SELECT time, device_id, temperature, speed
  FROM sensor
) PARTITION BY device_id ORDER BY time

含义：

• PARTITION BY：定义 FFT 的分组单位。每个 partition 独立执行一次 FFT。
• ORDER BY：定义输入序列顺序。v1 要求按时间列升序。
• 非时间列、非分区列中的数值列会被自动识别为待转换 value columns。

SAMPLE_INTERVAL

可选参数。

表示采样间隔，使用 duration literal，例如：

SAMPLE_INTERVAL => 1ms
SAMPLE_INTERVAL => 1s

含义：

• 如果提供 SAMPLE_INTERVAL，则使用该值计算物理频率；
• 如果未提供，则在每个 partition 内根据时间列推断：

sample_interval = (last_time - first_time) / (row_count - 1)

v1 中暂不提供 SAMPLE_RATE，避免和 SAMPLE_INTERVAL 同时存在时产生歧义。

---

4. 输入列规则

4.1 时间列

v1 默认使用 time 列作为时间列。

时间列用于：

• 定义输入顺序；
• 在未提供 SAMPLE_INTERVAL 时推断采样间隔；
• 校验每个 partition 内时间是否升序。

v1 暂不提供 TIMECOL 参数。如果未来 table model 中存在非默认时间列需求，可以再扩展。

4.2 分区列

PARTITION BY 中出现的列作为 partition columns。

这些列不会参与 FFT，但会原样保留到输出中，用于标识每一组 FFT 结果属于哪个设备或 tag group。

4.3 待转换数值列

除时间列和分区列外，所有支持的数值列都会被转换。

v1 支持的数据类型：

INT32
INT64
FLOAT
DOUBLE

这些列会被转换为 double 数组后参与 FFT。

v1 不支持以下类型作为 value column：

BOOLEAN
TEXT / STRING
BLOB
DATE
TIMESTAMP

如果 DATA 中除时间列和分区列外没有任何可转换的数值列，应报错。

---

5. 输出 Schema

对于如下输入：

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature, speed
    FROM sensor
  ) PARTITION BY device_id ORDER BY time,
  SAMPLE_INTERVAL => 1ms
);

输出 schema 为：

device_id
frequency_index
frequency
temperature_real
temperature_imag
speed_real
speed_imag

输出列含义

partition columns

来自 PARTITION BY 的列，例如：

device_id
region
factory_id

用于标识当前 FFT 结果所属的输入分区。

frequency_index

类型：

INT64

含义：

FFT 输出数组中的下标，也就是 frequency bin index。

对于长度为 n 的输入序列，v1 输出完整频谱，因此：

frequency_index = 0, 1, 2, ..., n - 1

frequency

类型：

DOUBLE

含义：

当前 frequency bin 对应的物理频率。

计算逻辑与 numpy.fft.fftfreq(n, d=sample_interval) 对齐。

对于长度为 n、采样间隔为 d 的输入序列：

frequency = frequency_index / (n * d)              when frequency_index < ceil(n / 2)
frequency = (frequency_index - n) / (n * d)        otherwise

因此 full spectrum 中后半部分会出现负频率。

<column>_real

类型：

DOUBLE

含义：

对应输入数值列 FFT 结果的实部。

例如：

temperature_real
speed_real

<column>_imag

类型：

DOUBLE

含义：

对应输入数值列 FFT 结果的虚部。

例如：

temperature_imag
speed_imag

---

6. 行为细节

6.1 每个 partition 独立执行 FFT

例如：

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature
    FROM sensor
  ) PARTITION BY device_id ORDER BY time
);

如果有三个 device_id，则每个设备各自执行一次 FFT，各自产生一组 frequency bins。

6.2 多个数值列共享同一频率轴

对于同一个 partition，temperature 和 speed 使用相同的：

frequency_index
frequency

但各自有独立的复数结果：

temperature_real
temperature_imag
speed_real
speed_imag

6.3 时间排序要求

v1 要求每个 partition 内时间升序。

如果发现某个 partition 中时间不是升序，应抛出异常。

是否要求严格递增建议定义为：

time[i] < time[i + 1]

也就是不允许相同时间戳。这样可以避免采样间隔推断和频率轴计算出现歧义。

6.4 采样间隔推断

如果未提供 SAMPLE_INTERVAL：

sample_interval = (last_time - first_time) / (row_count - 1)

如果某个 partition 的 row_count < 2，则无法推断采样间隔，应报错。

如果提供了 SAMPLE_INTERVAL，则即使 partition 只有 1 行，也可以计算 FFT 和 frequency。

6.5 均匀采样假设

v1 假设输入序列是均匀采样的。

暂不校验每一对相邻时间戳的间隔是否完全一致，只校验时间升序。用户需要保证输入数据语义上是均匀采样序列。

6.6 Null 值处理

建议 v1 采用简单规则：

如果待转换数值列中存在 NULL，则报错。

原因是 FFT 需要 dense numeric array。插值、填充、忽略 null 等行为都属于数据预处理策略，不建议在 v1 的 FFT TVF 内隐式完成。用户可以在 DATA 子查询中提前过滤或填充。

---

7. v1 暂不支持的能力

v1 不支持：

• DFT 独立 TVF；
• VALUE 参数；
• TIMECOL 参数；
• SAMPLE_RATE 参数；
• N 参数；
• NORM 参数；
• SPECTRUM 参数；
• one-sided spectrum；
• amplitude / phase 输出列；
• 自动插值或补齐不规则采样数据；
• 对复数输入做 FFT。

这些能力可以根据后续用户需求逐步加入。

---

8. 示例

示例 1：单设备单列 FFT

SELECT *
FROM FFT(
  DATA => (
    SELECT time, temperature
    FROM sensor
    WHERE device_id = 'd1'
  ) ORDER BY time,
  SAMPLE_INTERVAL => 1s
);

输出：

frequency_index | frequency | temperature_real | temperature_imag
0               | 0.0       | ...              | ...
1               | 0.25      | ...              | ...
2               | -0.5      | ...              | ...
3               | -0.25     | ...              | ...

示例 2：按设备分区

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature
    FROM sensor
  ) PARTITION BY device_id ORDER BY time,
  SAMPLE_INTERVAL => 1ms
);

输出：

device_id | frequency_index | frequency | temperature_real | temperature_imag
d1        | 0               | ...       | ...              | ...
d1        | 1               | ...       | ...              | ...
d2        | 0               | ...       | ...              | ...
d2        | 1               | ...       | ...              | ...

示例 3：同一分区内转换多个数值列

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature, speed
    FROM sensor
  ) PARTITION BY device_id ORDER BY time
);

输出：

device_id
frequency_index
frequency
temperature_real
temperature_imag
speed_real
speed_imag

示例 4：用户自行选择需要转换的列

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, speed
    FROM sensor
  ) PARTITION BY device_id ORDER BY time,
  SAMPLE_INTERVAL => 10ms
);

这里只有 speed 会被转换。

---

9. 推荐 v1 最小语法

最终建议 v1 先落到这个最小形态：

SELECT *
FROM FFT(
  DATA => (
    SELECT time, partition_columns..., numeric_columns...
    FROM table_name
  ) PARTITION BY partition_columns... ORDER BY time,
  SAMPLE_INTERVAL => duration_literal
);

其中 SAMPLE_INTERVAL 可省略：

SELECT *
FROM FFT(
  DATA => (
    SELECT time, partition_columns..., numeric_columns...
    FROM table_name
  ) PARTITION BY partition_columns... ORDER BY time
);

[DaZuiZui]

1. Function Definition

FFT is a built-in table-valued function in the IoTDB table model. It performs Fast Fourier Transform on real-valued numeric sequences that are partitioned and ordered by time, and returns frequency-domain results.

It is suitable for the following scenarios:

• performing frequency-domain analysis on a time series from a single device;
• performing FFT independently for multiple devices or tag groups;
• transforming multiple numeric columns at the same time, such as temperature, speed, and voltage;
• returning the complex result for each frequency bin, including the real and imaginary parts.

In v1, only FFT is provided. DFT is not exposed as a separate TVF. FFT can be treated as the practical high-performance implementation for frequency-domain analysis. A separate DFT TVF can be discussed later if there is a clear use case.

---

2. Syntax Definition

2.1 Basic Syntax

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature, speed
    FROM sensor
  ) PARTITION BY device_id ORDER BY time
);

2.2 With Explicit Sample Interval

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature, speed
    FROM sensor
  ) PARTITION BY device_id ORDER BY time,
  SAMPLE_INTERVAL => 1ms
);

2.3 Transforming Only Selected Columns

FFT v1 does not provide a VALUE parameter. If users only want to transform a subset of numeric columns, they should project only those columns in the DATA subquery.

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature
    FROM sensor
  ) PARTITION BY device_id ORDER BY time,
  SAMPLE_INTERVAL => 1s
);

---

3. Parameters

DATA

Required.

DATA is a table argument that provides the input time series data.

DATA => (
  SELECT time, device_id, temperature, speed
  FROM sensor
) PARTITION BY device_id ORDER BY time

Meaning:

• PARTITION BY: defines the grouping unit for FFT. Each partition is transformed independently.
• ORDER BY: defines the input sequence order. In v1, data must be ordered by the time column in ascending order.
• Numeric columns other than the time column and partition columns are automatically treated as value columns to transform.

SAMPLE_INTERVAL

Optional.

Specifies the sampling interval as a duration literal, for example:

SAMPLE_INTERVAL => 1ms
SAMPLE_INTERVAL => 1s

Meaning:

• If SAMPLE_INTERVAL is provided, it is used to calculate the physical frequency.
• If it is not provided, the interval is inferred within each partition from the time column:

sample_interval = (last_time - first_time) / (row_count - 1)

SAMPLE_RATE is not provided in v1, so we can avoid ambiguity between SAMPLE_RATE and SAMPLE_INTERVAL.

---

4. Input Column Rules

4.1 Time Column

In v1, the default time column is time.

The time column is used to:

• define the input order;
• infer the sampling interval when SAMPLE_INTERVAL is not provided;
• validate that timestamps are ascending within each partition.

TIMECOL is not provided in v1. If non-default time columns are needed in the table model in the future, this can be extended later.

4.2 Partition Columns

Columns specified in PARTITION BY are partition columns.

They do not participate in FFT calculation, but are preserved in the output so users can identify which input series each frequency-domain row belongs to.

4.3 Value Columns

All supported numeric columns other than the time column and partition columns are transformed.

Supported input types in v1:

INT32
INT64
FLOAT
DOUBLE

These columns are converted to double arrays before FFT calculation.

Unsupported value column types in v1:

BOOLEAN
TEXT / STRING
BLOB
DATE
TIMESTAMP

If there is no transformable numeric column in DATA after excluding the time column and partition columns, an error should be reported.

---

5. Output Schema

For the following input:

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature, speed
    FROM sensor
  ) PARTITION BY device_id ORDER BY time,
  SAMPLE_INTERVAL => 1ms
);

The output schema is:

device_id
frequency_index
frequency
temperature_real
temperature_imag
speed_real
speed_imag

Output Columns

Partition Columns

Partition columns come from PARTITION BY, for example:

device_id
region
factory_id

They identify which input partition the current FFT result belongs to.

frequency_index

Type:

INT64

Meaning:

The index in the FFT output array, also known as the frequency bin index.

For an input sequence of length n, v1 outputs the full spectrum:

frequency_index = 0, 1, 2, ..., n - 1

frequency

Type:

DOUBLE

Meaning:

The physical frequency corresponding to the current frequency bin.

The calculation is aligned with numpy.fft.fftfreq(n, d=sample_interval).

For an input sequence of length n and sampling interval d:

frequency = frequency_index / (n * d)              when frequency_index < ceil(n / 2)
frequency = (frequency_index - n) / (n * d)        otherwise

Therefore, the second half of the full spectrum contains negative frequencies.

<column>_real

Type:

DOUBLE

Meaning:

The real part of the FFT result for the corresponding input numeric column.

Examples:

temperature_real
speed_real

<column>_imag

Type:

DOUBLE

Meaning:

The imaginary part of the FFT result for the corresponding input numeric column.

Examples:

temperature_imag
speed_imag

---

6. Behavior Details

6.1 FFT Is Executed Independently for Each Partition

Example:

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature
    FROM sensor
  ) PARTITION BY device_id ORDER BY time
);

If there are three device_id values, FFT is executed independently for each device, and each device produces its own frequency bins.

6.2 Multiple Numeric Columns Share the Same Frequency Axis

Within the same partition, temperature and speed share the same:

frequency_index
frequency

However, they have independent complex FFT results:

temperature_real
temperature_imag
speed_real
speed_imag

6.3 Time Ordering Requirement

In v1, timestamps must be ascending within each partition.

If timestamps in a partition are not ascending, the function should throw an exception.

It is recommended to define this as strictly ascending:

time[i] < time[i + 1]

This means duplicate timestamps are not allowed, which avoids ambiguity in sampling interval inference and frequency-axis calculation.

6.4 Sampling Interval Inference

If SAMPLE_INTERVAL is not provided:

sample_interval = (last_time - first_time) / (row_count - 1)

If a partition has row_count < 2, the sampling interval cannot be inferred, so an error should be reported.

If SAMPLE_INTERVAL is provided, FFT and frequency calculation can still be performed even if a partition contains only one row.

6.5 Uniform Sampling Assumption

v1 assumes that the input sequence is uniformly sampled.

It does not validate whether every adjacent timestamp interval is exactly the same. It only validates that timestamps are ascending. Users are responsible for ensuring that the input data semantically represents a uniformly sampled sequence.

6.6 Null Value Handling

The recommended v1 behavior is simple:

If any value column contains NULL, an error should be reported.

Reason:

FFT requires a dense numeric array. Interpolation, filling, or ignoring null values are data preprocessing strategies and should not be done implicitly inside the FFT TVF in v1. Users can filter or fill null values in the DATA subquery before calling FFT.

---

7. Features Not Supported in v1

The following features are not supported in v1:

• separate DFT TVF;
• VALUE parameter;
• TIMECOL parameter;
• SAMPLE_RATE parameter;
• N parameter;
• NORM parameter;
• SPECTRUM parameter;
• one-sided spectrum;
• amplitude / phase output columns;
• automatic interpolation or padding for irregularly sampled data;
• FFT on complex input.

These features can be added later based on user requirements.

---

8. Examples

Example 1: FFT on a Single Column from a Single Device

SELECT *
FROM FFT(
  DATA => (
    SELECT time, temperature
    FROM sensor
    WHERE device_id = 'd1'
  ) ORDER BY time,
  SAMPLE_INTERVAL => 1s
);

Output:

frequency_index | frequency | temperature_real | temperature_imag
0               | 0.0       | ...              | ...
1               | 0.25      | ...              | ...
2               | -0.5      | ...              | ...
3               | -0.25     | ...              | ...

Example 2: Partitioned by Device

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature
    FROM sensor
  ) PARTITION BY device_id ORDER BY time,
  SAMPLE_INTERVAL => 1ms
);

Output:

device_id | frequency_index | frequency | temperature_real | temperature_imag
d1        | 0               | ...       | ...              | ...
d1        | 1               | ...       | ...              | ...
d2        | 0               | ...       | ...              | ...
d2        | 1               | ...       | ...              | ...

Example 3: Transforming Multiple Numeric Columns in the Same Partition

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, temperature, speed
    FROM sensor
  ) PARTITION BY device_id ORDER BY time
);

Output schema:

device_id
frequency_index
frequency
temperature_real
temperature_imag
speed_real
speed_imag

Example 4: Selecting Columns to Transform Manually

SELECT *
FROM FFT(
  DATA => (
    SELECT time, device_id, speed
    FROM sensor
  ) PARTITION BY device_id ORDER BY time,
  SAMPLE_INTERVAL => 10ms
);

Only speed is transformed.

---

9. Recommended Minimal v1 Syntax

The recommended minimal v1 form is:

SELECT *
FROM FFT(
  DATA => (
    SELECT time, partition_columns..., numeric_columns...
    FROM table_name
  ) PARTITION BY partition_columns... ORDER BY time,
  SAMPLE_INTERVAL => duration_literal
);

SAMPLE_INTERVAL can be omitted:

SELECT *
FROM FFT(
  DATA => (
    SELECT time, partition_columns..., numeric_columns...
    FROM table_name
  ) PARTITION BY partition_columns... ORDER BY time
);

[DaZuiZui]

Thanks for offering to help! I'm currently working on this issue, but I'll let you know if I need any input. @dahyvuun

[DaZuiZui]

English

It is reasonable to retain N and NORM in the function signature of v1 because they are standard FFT parameters and their semantics are already well-established in the existing FFT API.

I will update the v1 design accordingly.

N will be an optional integer transform length. If N is not provided, the transform length defaults to the input length of each partition. If N is smaller than the input length, the input sequence will be truncated. If N is larger than the input length, the input sequence will be zero-padded. The number of output frequency bins will be N.

NORM will be an optional string parameter. The supported values will be backward, forward, and ortho. The default value will be backward, aligned with numpy.fft.fft.

The frequency axis will be calculated based on the transform length N and SAMPLE_INTERVAL. If SAMPLE_INTERVAL is provided, it is used directly. Otherwise, it is inferred from the time column within each partition as:

(last_time - first_time) / (row_count - 1)

The frequency unit will be Hz, i.e., cycles per second.

The rest of the v1 design remains unchanged:

1. expose FFT only, not DFT;
2. no VALUE parameter;
3. transform all numeric columns except the time column and PARTITION BY columns;
4. require time to be strictly ascending within each partition;
5. assume uniformly sampled input in v1 without checking every adjacent interval;
6. output full spectrum only;
7. keep the minimal output schema with frequency_index, frequency, and real/imag columns;
8. leave amplitude, phase, and one-sided spectrum for possible future extensions.

I will also make one detail explicit in the design doc: when N is provided, the number of output rows per partition is N, and frequency_index ranges from 0 to N - 1. When N is not provided, N defaults to the input length of that partition.

Chinese

在 v1 的函数签名中保留 N 和 NORM 是合理的，因为它们是标准的 FFT 参数，并且它们的语义在现有 FFT API 中已经比较成熟。

我会相应更新 v1 设计。

N 将作为一个可选的整数参数，表示变换长度。如果用户没有提供 N，则变换长度默认等于每个 partition 的输入长度。如果 N 小于输入长度，则截断输入序列。如果 N 大于输入长度，则对输入序列进行 zero-padding。输出的 frequency bin 数量将等于 N。

NORM 将作为一个可选的字符串参数。支持的取值包括 backward、forward 和 ortho。默认值为 backward，与 numpy.fft.fft 保持一致。

frequency axis 将基于变换长度 N 和 SAMPLE_INTERVAL 计算。如果用户提供了 SAMPLE_INTERVAL，则直接使用该值。否则，在每个 partition 内根据时间列推断：

(last_time - first_time) / (row_count - 1)

frequency 的单位将是 Hz，也就是 cycles per second。

v1 设计中的其他部分保持不变：

1. 只暴露 FFT，不暴露 DFT；
2. 不提供 VALUE 参数；
3. 转换除时间列和 PARTITION BY 列以外的所有数值列；
4. 要求每个 partition 内的时间严格升序；
5. v1 假设输入是均匀采样的，但不检查每一对相邻时间戳的间隔；
6. 只输出 full spectrum；
7. 保持最小输出 schema，即 frequency_index、frequency 以及 real/imag 列；
8. amplitude、phase 和 one-sided spectrum 留作后续可能的扩展。

我也会在设计文档中明确一个细节：当用户提供 N 时，每个 partition 的输出行数为 N，frequency_index 的范围是 0 到 N - 1。当用户没有提供 N 时，N 默认等于该 partition 的输入长度。