DaZuiZui commented on issue #17939:
URL: https://github.com/apache/iotdb/issues/17939#issuecomment-4715622756
## 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
```sql
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
```sql
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.
```sql
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.
```sql
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:
```sql
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:
```text
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:
```text
INT32
INT64
FLOAT
DOUBLE
```
These columns are converted to double arrays before FFT calculation.
Unsupported value column types in v1:
```text
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:
```sql
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:
```text
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:
```text
device_id
region
factory_id
```
They identify which input partition the current FFT result belongs to.
### `frequency_index`
Type:
```text
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:
```text
frequency_index = 0, 1, 2, ..., n - 1
```
### `frequency`
Type:
```text
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`:
```text
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:
```text
DOUBLE
```
Meaning:
The real part of the FFT result for the corresponding input numeric column.
Examples:
```text
temperature_real
speed_real
```
### `<column>_imag`
Type:
```text
DOUBLE
```
Meaning:
The imaginary part of the FFT result for the corresponding input numeric
column.
Examples:
```text
temperature_imag
speed_imag
```
---
## 6. Behavior Details
### 6.1 FFT Is Executed Independently for Each Partition
Example:
```sql
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:
```text
frequency_index
frequency
```
However, they have independent complex FFT results:
```text
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:
```text
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:
```text
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
```sql
SELECT *
FROM FFT(
DATA => (
SELECT time, temperature
FROM sensor
WHERE device_id = 'd1'
) ORDER BY time,
SAMPLE_INTERVAL => 1s
);
```
Output:
```text
frequency_index | frequency | temperature_real | temperature_imag
0 | 0.0 | ... | ...
1 | 0.25 | ... | ...
2 | -0.5 | ... | ...
3 | -0.25 | ... | ...
```
### Example 2: Partitioned by Device
```sql
SELECT *
FROM FFT(
DATA => (
SELECT time, device_id, temperature
FROM sensor
) PARTITION BY device_id ORDER BY time,
SAMPLE_INTERVAL => 1ms
);
```
Output:
```text
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
```sql
SELECT *
FROM FFT(
DATA => (
SELECT time, device_id, temperature, speed
FROM sensor
) PARTITION BY device_id ORDER BY time
);
```
Output schema:
```text
device_id
frequency_index
frequency
temperature_real
temperature_imag
speed_real
speed_imag
```
### Example 4: Selecting Columns to Transform Manually
```sql
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:
```sql
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:
```sql
SELECT *
FROM FFT(
DATA => (
SELECT time, partition_columns..., numeric_columns...
FROM table_name
) PARTITION BY partition_columns... ORDER BY time
);
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
