Hi all, We would like to discuss the design and implementation of three relational table functions in Apache IoTDB: LowPass, HighPass, and XCorr. （pr : https://github.com/apache/iotdb/pull/18539） These functions operate on data within each PARTITION BY group. Since the calculation requires the complete sequence of a partition, the processor keeps its state across multiple TsBlocks. Input rows are collected first, and the calculation is performed when the current partition is complete. ### LowPass and HighPass The LowPass and HighPass functions accept the following parameters: - DATA: the input table. - TIMECOL: the time column used for ordering. The ORDER BY clause must contain only this time column. - WPASS: a double literal in the range (0, 1), which defines the frequency boundary. The columns listed in PARTITION BY are used to split the input into independent sequences. After excluding the partition columns and the time column, all remaining numeric columns are treated as calculation columns. For each calculation column, the function collects the complete sequence of finite, non-null values and applies an FFT-based transformation: - LowPass keeps the low-frequency components and removes the high-frequency components. - HighPass removes the low-frequency components and keeps the high-frequency components. The inverse FFT is then applied to obtain the transformed sequence. The output contains the partition columns, the original time column, and one DOUBLE result column for each calculation column. Null, NaN, and infinite input values are not included in the FFT calculation. Their original row positions are retained, and the corresponding output positions are restored as null. This keeps the output aligned with the original time sequence while avoiding invalid values in the frequency-domain calculation. The current implementation contains an internal FFT implementation whose behavior is aligned with the required JTransforms operations. Because it is not guaranteed to be bit-identical to the original JTransforms implementation, small floating-point rounding differences may occur. ### XCorr The XCorr function accepts: - DATA: the input table. - TIMECOL: the time column used for ordering. The ORDER BY clause must contain only this time column. - PARTITION BY: optional partition columns. - Exactly two calculation columns after excluding the partition columns and the time column. The two calculation columns are treated as two aligned sequences. The function calculates their cross-correlation for all lags from -(n - 1) to n - 1, where n is the number of rows in the partition. For each lag, only pairs in which both values are finite and non-null participate in the calculation. The correlation value is normalized by the number of valid pairs for that lag. If a lag has no valid pairs, the corresponding result is returned as null. The output contains the partition columns and one DOUBLE result column named according to the two calculation columns, for example xcorr(s1, s2). The original time column is used for ordering but is not included in the result. ### Points for Discussion We would appreciate feedback on the following aspects: 1. Whether the parameter and column-selection rules are appropriate for relational table functions. 2. Whether restoring null positions after filtering is the expected behavior for LowPass and HighPass. 3. Whether the per-lag valid-pair normalization is suitable for XCorr. 4. Whether the current output column naming and lag ordering are clear enough for users. ——— 大家好： 我们希望讨论 Apache IoTDB 中三个关系型表函数的设计与实现：LowPass、HighPass 和 XCorr。 这三个函数都在每个 PARTITION BY 分区内进行计算。由于计算需要获取完整的分区序列，因此处理器需要跨多个 TsBlock 保存状态。输入数据首先被完整收集，等当前分区结束后再统一执行计算。 ### LowPass 和 HighPass LowPass 和 HighPass 接受以下参数： - DATA：输入表。 - TIMECOL：用于排序的时间列，ORDER BY 中只能包含该时间列。 - WPASS：范围为 (0, 1) 的 DOUBLE 字面量，用于定义频率分界位置。 PARTITION BY 中的列用于将输入数据划分为相互独立的序列。排除分区列和时间列后，其余数值列都作为计算列处理。 对于每一个计算列，函数会先收集当前分区中完整的、非空且有限的数值序列，然后执行基于 FFT 的变换： - LowPass 保留低频成分，去除高频成分。 - HighPass 去除低频成分，保留高频成分。 之后执行逆 FFT，得到变换后的结果序列。输出结果包含分区列、原始时间列，以及每个计算列对应的一个 DOUBLE 类型结果列。 输入中的 NULL、NaN 和无穷大值不会参与 FFT 计算，但会保留它们在原始序列中的行位置。计算完成后，这些位置在输出中恢复为 NULL。这样既可以避免无效值影响频域计算，也可以保证结果与原始时间序列保持对齐。 当前实现中包含了内部 FFT 实现，其计算语义与所需的 JTransforms 操作保持一致。但由于不能保证与原始 JTransforms 实现逐位一致，计算结果可能存在小范围的浮点舍入误差。 ### XCorr XCorr 接受以下参数： - DATA：输入表。 - TIMECOL：用于排序的时间列，ORDER BY 中只能包含该时间列。 - PARTITION BY：可选的分区列。 - 在排除分区列和时间列后，必须恰好剩余两列计算列。 这两列计算列被视为两个对齐的序列。函数会计算从 -(n - 1) 到 n - 1 的全部 lag，其中 n 为当前分区的行数。 对于每个 lag，只使用两列中同时为有限值且非 NULL 的数据对参与计算。相关性结果按照当前 lag 下的有效数据对数量进行归一化。如果某个 lag 没有任何有效数据对，则该 lag 的结果返回 NULL。 输出结果包含分区列和一个 DOUBLE 类型的结果列，结果列名称根据两个计算列生成，例如 xcorr(s1, s2)。原始时间列仅用于排序，不包含在最终输出中。 ### 希望讨论的问题 希望大家重点反馈以下几个方面： 1. 当前参数定义和计算列选择规则是否适合关系型表函数。 2. LowPass 和 HighPass 在滤除 NULL 后再将 NULL 位置恢复的行为是否符合预期。 3. XCorr 按每个 lag 的有效数据对数量进行归一化是否合理。 4. 当前结果列命名方式以及 lag 的输出顺序是否足够清晰。
Best wishes, Yaobin chen
