This is an automated email from the ASF dual-hosted git repository.
critas pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/iotdb-docs.git
The following commit(s) were added to refs/heads/main by this push:
new cbf22224 add asof join in table model (#771)
cbf22224 is described below
commit cbf2222478599c9a1dcad799f30cb11aa2aae67f
Author: leto-b <bingqian....@timecho.com>
AuthorDate: Wed Jun 4 09:41:16 2025 +0800
add asof join in table model (#771)
---
.../Master/Table/SQL-Manual/From-Join-Clause.md | 95 +++++++++++++++++++++-
.../latest-Table/SQL-Manual/From-Join-Clause.md | 95 +++++++++++++++++++++-
.../Master/Table/SQL-Manual/From-Join-Clause.md | 93 ++++++++++++++++++++-
.../latest-Table/SQL-Manual/From-Join-Clause.md | 93 ++++++++++++++++++++-
4 files changed, 372 insertions(+), 4 deletions(-)
diff --git a/src/UserGuide/Master/Table/SQL-Manual/From-Join-Clause.md
b/src/UserGuide/Master/Table/SQL-Manual/From-Join-Clause.md
index 2e686131..eb437981 100644
--- a/src/UserGuide/Master/Table/SQL-Manual/From-Join-Clause.md
+++ b/src/UserGuide/Master/Table/SQL-Manual/From-Join-Clause.md
@@ -34,6 +34,8 @@ relation
joinType
: INNER?
| FULL OUTER?
+ | CROSS?
+ | ASOF?
;
joinCriteria
@@ -73,6 +75,7 @@ In the current version of IoTDB, the following joins are
supported:
1. **Inner Join**: Combines rows that meet the join condition, effectively
returning the intersection of the two tables. The join condition must be an
equality condition on the `time` column.
2. **Full Outer Join**: Returns all records from both tables, inserting `NULL`
values for unmatched rows. The join condition can be any equality expression.
3. **Cross Join**: Represents the Cartesian product of two tables.
+4. **ASOF JOIN** (AS OF a specific point in time) is a specialized join
operation based on temporal or approximate matching conditions, designed for
scenarios where timestamps between two datasets are not perfectly aligned. It
matches each row from the left table with the closest corresponding row in the
right table that meets the specified conditions (typically the nearest
preceding or succeeding timestamp). This operation is widely used for
time-series data analysis (e.g., sensor d [...]
### 3.1 Inner Join
@@ -132,6 +135,43 @@ joinCriteria
### 3.3 Cross Join
A cross join represents the Cartesian product of two tables, returning all
possible combinations of the N rows from the left table and the M rows from the
right table, resulting in N*M rows. This type of join is the least commonly
used in practice.
+### 3.4 Asof Join
+
+IoTDB ASOF JOIN is an approximate point join method that allows users to
perform matching based on the closest timestamp according to specified rules.
The current version only supports ASOF INNER JOIN for Time columns.
+
+The SQL syntax is as follows:
+
+```SQL
+SELECT selectExpr [, selectExpr] ... FROM
+<TABLE_NAME1> ASOF[(tolerance theta)] [INNER] JOIN <TABLE_NAME2> joinCriteria
+[WHERE whereCondition]
+WHERE a.time = tolerance(b.time, 1s)
+
+joinCriteria
+ : ON <TABLE_NAME1>.time comparisonOperator <TABLE_NAME2>.time
+ ;
+
+comparisonOperator
+ : < | <= | > | >=
+ ;
+```
+
+**Notes:**
+
+* ASOF JOIN defaults to ASOF INNER JOIN implementation.
+* When using the ON keyword for joining, the join condition must include an
inequality join condition for the Time column. Only four operators are
supported: `">", ">=", "<", "<="`. The corresponding join matching rules are as
follows (where lt represents the left table and rt represents the right table):
+
+| Operator | Join Method |
+| -------------------------- | ---------------------------------------------- |
+| `lt.time >= rt.time` | The closest timestamp in the left table that is
greater than or equal to the right table's timestamp. |
+| `lt.time > rt.time` | The closest timestamp in the left table that is
greater than the right table's timestamp. |
+| `lt.time <= rt.time` | The closest timestamp in the left table that is less
than or equal to the right table's timestamp. |
+| `lt.time < rt.time` | The closest timestamp in the left table that is less
than the right table's timestamp. |
+
+* `Tolerance parameter`: The maximum allowed time difference for searching
data in the right table (expressed as a TimeDuration, e.g., 1d for one day). If
the Tolerance parameter is not specified, the search time range defaults to
infinity. Note: Currently, this parameter is only supported in ASOF
INNER JOIN.
+
+
+
## 4. Example Queries
The [Example Data page](../Reference/Sample-Data.md)page provides SQL
statements to construct table schemas and insert data. By downloading and
executing these statements in the IoTDB CLI, you can import the data into
IoTDB. This data can be used to test and run the example SQL queries included
in this documentation, allowing you to reproduce the described results.
@@ -499,4 +539,57 @@ Query Results:
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 8
It costs 0.047s
-```
\ No newline at end of file
+```
+
+#### 4.2.4 Asof join
+
+Example 1: Without specifying the tolerance parameter, where the timestamp
in table1 is greater than or equal to and closest to the timestamp in table2.
+
+```SQL
+SELECT t1.time as time1, t1.device_id as device1, t1.temperature as
temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as
temperature2 FROM table1 t1 ASOF JOIN table2 t2 ON t1.time>=t2.time;
+```
+
+Query Results:
+
+```SQL
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+| time1|device1|temperature1|
time2|device2|temperature2|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+|2024-11-30T14:30:00.000+08:00| 101|
90.0|2024-11-30T00:00:00.000+08:00| 101| 90.0|
+|2024-11-30T09:30:00.000+08:00| 101|
90.0|2024-11-30T00:00:00.000+08:00| 101| 90.0|
+|2024-11-29T18:30:00.000+08:00| 100|
90.0|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-29T11:00:00.000+08:00| 100|
null|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-29T10:00:00.000+08:00| 101|
85.0|2024-11-29T00:00:00.000+08:00| 101| 85.0|
+|2024-11-28T11:00:00.000+08:00| 100|
88.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T10:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T09:00:00.000+08:00| 100|
null|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T08:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-27T16:44:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:43:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:42:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:41:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:40:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:39:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:38:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-26T13:38:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
+|2024-11-26T13:37:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+```
+
+Example 2: With the tolerance parameter specified, where the timestamp in
table1 is greater than or equal to and closest to the timestamp in table2.
+
+```SQL
+SELECT t1.time as time1, t1.device_id as device1, t1.temperature as
temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as
temperature2 FROM table1 t1 ASOF(tolerance 2s) JOIN table2 t2 ON
t1.time>=t2.time;
+```
+
+Query Results:
+
+```SQL
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+| time1|device1|temperature1|
time2|device2|temperature2|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+|2024-11-29T11:00:00.000+08:00| 100|
null|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-28T08:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-26T13:37:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+```
diff --git a/src/UserGuide/latest-Table/SQL-Manual/From-Join-Clause.md
b/src/UserGuide/latest-Table/SQL-Manual/From-Join-Clause.md
index 2e686131..eb437981 100644
--- a/src/UserGuide/latest-Table/SQL-Manual/From-Join-Clause.md
+++ b/src/UserGuide/latest-Table/SQL-Manual/From-Join-Clause.md
@@ -34,6 +34,8 @@ relation
joinType
: INNER?
| FULL OUTER?
+ | CROSS?
+ | ASOF?
;
joinCriteria
@@ -73,6 +75,7 @@ In the current version of IoTDB, the following joins are
supported:
1. **Inner Join**: Combines rows that meet the join condition, effectively
returning the intersection of the two tables. The join condition must be an
equality condition on the `time` column.
2. **Full Outer Join**: Returns all records from both tables, inserting `NULL`
values for unmatched rows. The join condition can be any equality expression.
3. **Cross Join**: Represents the Cartesian product of two tables.
+4. **ASOF JOIN** (AS OF a specific point in time) is a specialized join
operation based on temporal or approximate matching conditions, designed for
scenarios where timestamps between two datasets are not perfectly aligned. It
matches each row from the left table with the closest corresponding row in the
right table that meets the specified conditions (typically the nearest
preceding or succeeding timestamp). This operation is widely used for
time-series data analysis (e.g., sensor d [...]
### 3.1 Inner Join
@@ -132,6 +135,43 @@ joinCriteria
### 3.3 Cross Join
A cross join represents the Cartesian product of two tables, returning all
possible combinations of the N rows from the left table and the M rows from the
right table, resulting in N*M rows. This type of join is the least commonly
used in practice.
+### 3.4 Asof Join
+
+IoTDB ASOF JOIN is an approximate point join method that allows users to
perform matching based on the closest timestamp according to specified rules.
The current version only supports ASOF INNER JOIN for Time columns.
+
+The SQL syntax is as follows:
+
+```SQL
+SELECT selectExpr [, selectExpr] ... FROM
+<TABLE_NAME1> ASOF[(tolerance theta)] [INNER] JOIN <TABLE_NAME2> joinCriteria
+[WHERE whereCondition]
+WHERE a.time = tolerance(b.time, 1s)
+
+joinCriteria
+ : ON <TABLE_NAME1>.time comparisonOperator <TABLE_NAME2>.time
+ ;
+
+comparisonOperator
+ : < | <= | > | >=
+ ;
+```
+
+**Notes:**
+
+* ASOF JOIN defaults to ASOF INNER JOIN implementation.
+* When using the ON keyword for joining, the join condition must include an
inequality join condition for the Time column. Only four operators are
supported: `">", ">=", "<", "<="`. The corresponding join matching rules are as
follows (where lt represents the left table and rt represents the right table):
+
+| Operator | Join Method |
+| -------------------------- | ---------------------------------------------- |
+| `lt.time >= rt.time` | The closest timestamp in the left table that is
greater than or equal to the right table's timestamp. |
+| `lt.time > rt.time` | The closest timestamp in the left table that is
greater than the right table's timestamp. |
+| `lt.time <= rt.time` | The closest timestamp in the left table that is less
than or equal to the right table's timestamp. |
+| `lt.time < rt.time` | The closest timestamp in the left table that is less
than the right table's timestamp. |
+
+* `Tolerance parameter`: The maximum allowed time difference for searching
data in the right table (expressed as a TimeDuration, e.g., 1d for one day). If
the Tolerance parameter is not specified, the search time range defaults to
infinity. Note: Currently, this parameter is only supported in ASOF
INNER JOIN.
+
+
+
## 4. Example Queries
The [Example Data page](../Reference/Sample-Data.md)page provides SQL
statements to construct table schemas and insert data. By downloading and
executing these statements in the IoTDB CLI, you can import the data into
IoTDB. This data can be used to test and run the example SQL queries included
in this documentation, allowing you to reproduce the described results.
@@ -499,4 +539,57 @@ Query Results:
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 8
It costs 0.047s
-```
\ No newline at end of file
+```
+
+#### 4.2.4 Asof join
+
+Example 1: Without specifying the tolerance parameter, where the timestamp
in table1 is greater than or equal to and closest to the timestamp in table2.
+
+```SQL
+SELECT t1.time as time1, t1.device_id as device1, t1.temperature as
temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as
temperature2 FROM table1 t1 ASOF JOIN table2 t2 ON t1.time>=t2.time;
+```
+
+Query Results:
+
+```SQL
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+| time1|device1|temperature1|
time2|device2|temperature2|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+|2024-11-30T14:30:00.000+08:00| 101|
90.0|2024-11-30T00:00:00.000+08:00| 101| 90.0|
+|2024-11-30T09:30:00.000+08:00| 101|
90.0|2024-11-30T00:00:00.000+08:00| 101| 90.0|
+|2024-11-29T18:30:00.000+08:00| 100|
90.0|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-29T11:00:00.000+08:00| 100|
null|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-29T10:00:00.000+08:00| 101|
85.0|2024-11-29T00:00:00.000+08:00| 101| 85.0|
+|2024-11-28T11:00:00.000+08:00| 100|
88.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T10:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T09:00:00.000+08:00| 100|
null|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T08:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-27T16:44:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:43:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:42:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:41:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:40:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:39:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:38:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-26T13:38:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
+|2024-11-26T13:37:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+```
+
+Example 2: With the tolerance parameter specified, where the timestamp in
table1 is greater than or equal to and closest to the timestamp in table2.
+
+```SQL
+SELECT t1.time as time1, t1.device_id as device1, t1.temperature as
temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as
temperature2 FROM table1 t1 ASOF(tolerance 2s) JOIN table2 t2 ON
t1.time>=t2.time;
+```
+
+Query Results:
+
+```SQL
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+| time1|device1|temperature1|
time2|device2|temperature2|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+|2024-11-29T11:00:00.000+08:00| 100|
null|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-28T08:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-26T13:37:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+```
diff --git a/src/zh/UserGuide/Master/Table/SQL-Manual/From-Join-Clause.md
b/src/zh/UserGuide/Master/Table/SQL-Manual/From-Join-Clause.md
index 8a94550e..0ad97598 100644
--- a/src/zh/UserGuide/Master/Table/SQL-Manual/From-Join-Clause.md
+++ b/src/zh/UserGuide/Master/Table/SQL-Manual/From-Join-Clause.md
@@ -35,6 +35,7 @@ joinType
: INNER?
| FULL OUTER?
| CROSS?
+ | ASOF?
;
joinCriteria
@@ -74,6 +75,7 @@ JOIN 用于将两个表基于某些条件连接起来，通常，连接条件是
* 内连接（Inner Join），连接条件只能是时间列的等值连接。
* 全外连接（Full Outer Join），连接条件可以是任意等值表达式。
* 交叉连接（Cross Join）
+* ASOF JOIN（AS OF a specific point in
time，特定时间点），是一种基于时间或近似匹配条件的特殊连接操作，适用于两个数据集的时间戳不完全对齐的场景。它能够为左表的每一行找到右表中时间最接近且满足条件的对应行，常用于处理时间序列数据（如传感器数据、金融行情等）。
### 3.1 内连接（Inner Join）
@@ -135,6 +137,42 @@ joinCriteria
### 3.3 交叉连接（Cross Join）
交叉连接表示两个表的的笛卡尔积，返回左表N行记录和右表M行记录的N*M种组合。该种连接方式在实际中使用最少。
+### 3.4 非精确点连接（ASOF JOIN）
+
+IoTDB ASOF JOIN 即非精确点连接方式，允许用户按照指定的规则以时间戳最接近的方式进行匹配。**目前版本只支持针对 Time 列的 ASOF
INNER JOIN。**
+
+SQL语法如下所示：
+
+```SQL
+SELECT selectExpr [, selectExpr] ... FROM
+<TABLE_NAME1> ASOF[(tolerance theta)] [INNER] JOIN <TABLE_NAME2> joinCriteria
+[WHERE whereCondition]
+WHERE a.time = tolerance(b.time, 1s)
+
+joinCriteria
+ : ON <TABLE_NAME1>.time comparisonOperator <TABLE_NAME2>.time
+ ;
+
+comparisonOperator
+ : < | <= | > | >=
+ ;
+```
+
+**说明：**
+
+* ASOF JOIN 默认使用 ASOF INNER JOIN 实现
+* 当使用 ON 关键字进行连接时，连接条件里必须包含针对 Time 列的不等式连接条件，不等式仅支持 `">", ">=", "<", "<="`
四种操作符，其代表的连接匹配规则如下：其中 lt 表示 left table, rt 表示 right table
+
+| 运算符 | 连接方式 |
+| -------------------------- | ---------------------------------------------- |
+| `lt.time >= rt.time` | 左表中时间戳大于等于右表时间戳且时间戳最接近 |
+| `lt.time > rt.time` | 左表中时间戳大于左表时间戳且时间戳最接近 |
+| `lt.time <= rt.time` | 左表中时间戳小于等于右表时间戳且时间戳最接近 |
+| `lt.time < rt.time` | 左表中时间戳小于右表时间戳且时间戳最接近 |
+
+* Tolerance 参数：容差，表示右表数据查找允许的最大时间差。（用 TimeDuration 表示，如 1d 表示1天）。如果不指定
Tolerance 参数，则表示查找时间范围为**正无穷**。注意：目前仅 ASOF **INNER** JOIN 中支持该参数
+
+
## 4. 示例数据
@@ -493,4 +531,57 @@ SELECT table1.*, table2.* FROM table1, table2 LIMIT 8;
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 8
It costs 0.047s
-```
\ No newline at end of file
+```
+
+#### 4.2.4 Asof join
+
+示例1：不指定 Tolerance 参数，且 table1 中时间戳大于等于 table2 时间戳且时间戳最接近
+
+```SQL
+SELECT t1.time as time1, t1.device_id as device1, t1.temperature as
temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as
temperature2 FROM table1 t1 ASOF JOIN table2 t2 ON t1.time>=t2.time;
+```
+
+查询结果
+
+```SQL
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+| time1|device1|temperature1|
time2|device2|temperature2|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+|2024-11-30T14:30:00.000+08:00| 101|
90.0|2024-11-30T00:00:00.000+08:00| 101| 90.0|
+|2024-11-30T09:30:00.000+08:00| 101|
90.0|2024-11-30T00:00:00.000+08:00| 101| 90.0|
+|2024-11-29T18:30:00.000+08:00| 100|
90.0|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-29T11:00:00.000+08:00| 100|
null|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-29T10:00:00.000+08:00| 101|
85.0|2024-11-29T00:00:00.000+08:00| 101| 85.0|
+|2024-11-28T11:00:00.000+08:00| 100|
88.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T10:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T09:00:00.000+08:00| 100|
null|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T08:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-27T16:44:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:43:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:42:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:41:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:40:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:39:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:38:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-26T13:38:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
+|2024-11-26T13:37:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+```
+
+示例2：指定 Tolerance 参数，且 table1 中时间戳大于等于 table2 时间戳且时间戳最接近
+
+```SQL
+SELECT t1.time as time1, t1.device_id as device1, t1.temperature as
temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as
temperature2 FROM table1 t1 ASOF(tolerance 2s) JOIN table2 t2 ON
t1.time>=t2.time;
+```
+
+查询结果
+
+```SQL
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+| time1|device1|temperature1|
time2|device2|temperature2|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+|2024-11-29T11:00:00.000+08:00| 100|
null|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-28T08:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-26T13:37:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+```
diff --git a/src/zh/UserGuide/latest-Table/SQL-Manual/From-Join-Clause.md
b/src/zh/UserGuide/latest-Table/SQL-Manual/From-Join-Clause.md
index 8a94550e..0ad97598 100644
--- a/src/zh/UserGuide/latest-Table/SQL-Manual/From-Join-Clause.md
+++ b/src/zh/UserGuide/latest-Table/SQL-Manual/From-Join-Clause.md
@@ -35,6 +35,7 @@ joinType
: INNER?
| FULL OUTER?
| CROSS?
+ | ASOF?
;
joinCriteria
@@ -74,6 +75,7 @@ JOIN 用于将两个表基于某些条件连接起来，通常，连接条件是
* 内连接（Inner Join），连接条件只能是时间列的等值连接。
* 全外连接（Full Outer Join），连接条件可以是任意等值表达式。
* 交叉连接（Cross Join）
+* ASOF JOIN（AS OF a specific point in
time，特定时间点），是一种基于时间或近似匹配条件的特殊连接操作，适用于两个数据集的时间戳不完全对齐的场景。它能够为左表的每一行找到右表中时间最接近且满足条件的对应行，常用于处理时间序列数据（如传感器数据、金融行情等）。
### 3.1 内连接（Inner Join）
@@ -135,6 +137,42 @@ joinCriteria
### 3.3 交叉连接（Cross Join）
交叉连接表示两个表的的笛卡尔积，返回左表N行记录和右表M行记录的N*M种组合。该种连接方式在实际中使用最少。
+### 3.4 非精确点连接（ASOF JOIN）
+
+IoTDB ASOF JOIN 即非精确点连接方式，允许用户按照指定的规则以时间戳最接近的方式进行匹配。**目前版本只支持针对 Time 列的 ASOF
INNER JOIN。**
+
+SQL语法如下所示：
+
+```SQL
+SELECT selectExpr [, selectExpr] ... FROM
+<TABLE_NAME1> ASOF[(tolerance theta)] [INNER] JOIN <TABLE_NAME2> joinCriteria
+[WHERE whereCondition]
+WHERE a.time = tolerance(b.time, 1s)
+
+joinCriteria
+ : ON <TABLE_NAME1>.time comparisonOperator <TABLE_NAME2>.time
+ ;
+
+comparisonOperator
+ : < | <= | > | >=
+ ;
+```
+
+**说明：**
+
+* ASOF JOIN 默认使用 ASOF INNER JOIN 实现
+* 当使用 ON 关键字进行连接时，连接条件里必须包含针对 Time 列的不等式连接条件，不等式仅支持 `">", ">=", "<", "<="`
四种操作符，其代表的连接匹配规则如下：其中 lt 表示 left table, rt 表示 right table
+
+| 运算符 | 连接方式 |
+| -------------------------- | ---------------------------------------------- |
+| `lt.time >= rt.time` | 左表中时间戳大于等于右表时间戳且时间戳最接近 |
+| `lt.time > rt.time` | 左表中时间戳大于左表时间戳且时间戳最接近 |
+| `lt.time <= rt.time` | 左表中时间戳小于等于右表时间戳且时间戳最接近 |
+| `lt.time < rt.time` | 左表中时间戳小于右表时间戳且时间戳最接近 |
+
+* Tolerance 参数：容差，表示右表数据查找允许的最大时间差。（用 TimeDuration 表示，如 1d 表示1天）。如果不指定
Tolerance 参数，则表示查找时间范围为**正无穷**。注意：目前仅 ASOF **INNER** JOIN 中支持该参数
+
+
## 4. 示例数据
@@ -493,4 +531,57 @@ SELECT table1.*, table2.* FROM table1, table2 LIMIT 8;
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 8
It costs 0.047s
-```
\ No newline at end of file
+```
+
+#### 4.2.4 Asof join
+
+示例1：不指定 Tolerance 参数，且 table1 中时间戳大于等于 table2 时间戳且时间戳最接近
+
+```SQL
+SELECT t1.time as time1, t1.device_id as device1, t1.temperature as
temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as
temperature2 FROM table1 t1 ASOF JOIN table2 t2 ON t1.time>=t2.time;
+```
+
+查询结果
+
+```SQL
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+| time1|device1|temperature1|
time2|device2|temperature2|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+|2024-11-30T14:30:00.000+08:00| 101|
90.0|2024-11-30T00:00:00.000+08:00| 101| 90.0|
+|2024-11-30T09:30:00.000+08:00| 101|
90.0|2024-11-30T00:00:00.000+08:00| 101| 90.0|
+|2024-11-29T18:30:00.000+08:00| 100|
90.0|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-29T11:00:00.000+08:00| 100|
null|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-29T10:00:00.000+08:00| 101|
85.0|2024-11-29T00:00:00.000+08:00| 101| 85.0|
+|2024-11-28T11:00:00.000+08:00| 100|
88.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T10:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T09:00:00.000+08:00| 100|
null|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-28T08:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-27T16:44:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:43:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:42:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:41:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:40:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:39:00.000+08:00| 101|
85.0|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-27T16:38:00.000+08:00| 101|
null|2024-11-27T00:00:00.000+08:00| 101| 85.0|
+|2024-11-26T13:38:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
+|2024-11-26T13:37:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+```
+
+示例2：指定 Tolerance 参数，且 table1 中时间戳大于等于 table2 时间戳且时间戳最接近
+
+```SQL
+SELECT t1.time as time1, t1.device_id as device1, t1.temperature as
temperature1, t2.time as time2, t2.device_id as device2, t2.temperature as
temperature2 FROM table1 t1 ASOF(tolerance 2s) JOIN table2 t2 ON
t1.time>=t2.time;
+```
+
+查询结果
+
+```SQL
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+| time1|device1|temperature1|
time2|device2|temperature2|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+|2024-11-29T11:00:00.000+08:00| 100|
null|2024-11-29T11:00:00.000+08:00| 100| null|
+|2024-11-28T08:00:00.000+08:00| 100|
85.0|2024-11-28T08:00:00.000+08:00| 100| 85.0|
+|2024-11-26T13:37:00.000+08:00| 100|
90.0|2024-11-26T13:37:00.000+08:00| 100| 90.0|
++-----------------------------+-------+------------+-----------------------------+-------+------------+
+```
