You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@iotdb.apache.org by hu...@apache.org on 2022/12/12 07:15:54 UTC
[iotdb] branch master updated: [IOTDB-5169] Add UserGuide for Order By in Align By Device (#8416)
This is an automated email from the ASF dual-hosted git repository.
hui pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new e68f560463 [IOTDB-5169] Add UserGuide for Order By in Align By Device (#8416)
e68f560463 is described below
commit e68f560463c7c31ef0bb0fbaf74335cf6386ff5a
Author: YangCaiyin <yc...@gmail.com>
AuthorDate: Mon Dec 12 15:15:48 2022 +0800
[IOTDB-5169] Add UserGuide for Order By in Align By Device (#8416)
---
docs/UserGuide/Query-Data/Align-By.md | 7 +-
docs/UserGuide/Query-Data/Order-By.md | 111 ++++++++++++++++++++++++++++++-
docs/UserGuide/Query-Data/Overview.md | 3 +-
docs/zh/UserGuide/Query-Data/Align-By.md | 4 ++
docs/zh/UserGuide/Query-Data/Order-By.md | 109 +++++++++++++++++++++++++++++-
docs/zh/UserGuide/Query-Data/Overview.md | 7 +-
6 files changed, 232 insertions(+), 9 deletions(-)
diff --git a/docs/UserGuide/Query-Data/Align-By.md b/docs/UserGuide/Query-Data/Align-By.md
index 87e11f8194..c7e32c5d2d 100644
--- a/docs/UserGuide/Query-Data/Align-By.md
+++ b/docs/UserGuide/Query-Data/Align-By.md
@@ -54,4 +54,9 @@ The result shows below:
+-----------------------------+-----------------+-----------+------+--------+
Total line number = 6
It costs 0.012s
-```
\ No newline at end of file
+```
+### ordering in ALIGN BY DEVICE
+
+ALIGN BY DEVICE mode arranges according to the device first, and sort each device in ascending order according to the timestamp. The ordering and priority can be adjusted through `ORDER BY` clause.
+
+For details and examples, see the document [Order By](./Order-By.md).
diff --git a/docs/UserGuide/Query-Data/Order-By.md b/docs/UserGuide/Query-Data/Order-By.md
index ecac0ef18a..9f451f85ef 100644
--- a/docs/UserGuide/Query-Data/Order-By.md
+++ b/docs/UserGuide/Query-Data/Order-By.md
@@ -21,4 +21,113 @@
# Order By
-TODO @yang caiyin
\ No newline at end of file
+## order by in ALIGN BY TIME mode
+
+The result set of IoTDB is in ALIGN BY TIME mode by default and `ORDER BY TIME` clause can also be used to specify the ordering of timestamp. The SQL statement is:
+```sql
+select * from root.ln.** where time <= 2017-11-01T00:01:00 order by time desc;
+```
+执行结果：
+
+```
++-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
+| Time|root.ln.wf02.wt02.hardware|root.ln.wf02.wt02.status|root.ln.wf01.wt01.temperature|root.ln.wf01.wt01.status|
++-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
+|2017-11-01T00:01:00.000+08:00| v2| true| 24.36| true|
+|2017-11-01T00:00:00.000+08:00| v2| true| 25.96| true|
+|1970-01-01T08:00:00.002+08:00| v2| false| null| null|
+|1970-01-01T08:00:00.001+08:00| v1| true| null| null|
++-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
+Total line number = 4
+```
+
+## order by in ALIGN BY DEVICE mode
+When querying in ALIGN BY DEVICE mode, `ORDER BY` clause can be used to specify the ordering of result set.
+
+ALIGN BY DEVICE mode supports four kinds of clauses with two sort keys which are `Device` and `Time`.
+
+1. ``ORDER BY DEVICE``: sort by the alphabetical order of the device name. The devices with the same column names will be clustered in a group view.
+
+2. ``ORDER BY TIME``: sort by the timestamp, the data points from different devices will be shuffled according to the timestamp.
+
+3. ``ORDER BY DEVICE,TIME``: sort by the alphabetical order of the device name. The data points with the same device name will be sorted by timestamp.
+
+4. ``ORDER BY TIME,DEVICE``: sort by timestamp. The data points with the same time will be sorted by the alphabetical order of the device name.
+
+> To make the result set more legible, when `ORDER BY` clause is not used, default settings will be provided.
+> The default ordering clause is `ORDER BY DEVICE,TIME` and the default ordering is `ASC`.
+
+When `Device` is the main sort key, the result set is sorted by device name first, then by timestamp in the group with the same device name, the SQL statement is:
+```sql
+select * from root.ln.** where time <= 2017-11-01T00:01:00 order by device desc,time asc align by device;
+```
+The result shows below:
+
+```
++-----------------------------+-----------------+--------+------+-----------+
+| Time| Device|hardware|status|temperature|
++-----------------------------+-----------------+--------+------+-----------+
+|1970-01-01T08:00:00.001+08:00|root.ln.wf02.wt02| v1| true| null|
+|1970-01-01T08:00:00.002+08:00|root.ln.wf02.wt02| v2| false| null|
+|2017-11-01T00:00:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
+|2017-11-01T00:00:00.000+08:00|root.ln.wf01.wt01| null| true| 25.96|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| true| 24.36|
++-----------------------------+-----------------+--------+------+-----------+
+Total line number = 6
+```
+When `Time` is the main sort key, the result set is sorted by timestamp first, then by device name in data points with the same timestamp. The SQL statement is:
+```sql
+select * from root.ln.** where time <= 2017-11-01T00:01:00 order by time asc,device desc align by device;
+```
+The result shows below:
+```
++-----------------------------+-----------------+--------+------+-----------+
+| Time| Device|hardware|status|temperature|
++-----------------------------+-----------------+--------+------+-----------+
+|1970-01-01T08:00:00.001+08:00|root.ln.wf02.wt02| v1| true| null|
+|1970-01-01T08:00:00.002+08:00|root.ln.wf02.wt02| v2| false| null|
+|2017-11-01T00:00:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
+|2017-11-01T00:00:00.000+08:00|root.ln.wf01.wt01| null| true| 25.96|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| true| 24.36|
++-----------------------------+-----------------+--------+------+-----------+
+Total line number = 6
+```
+When `ORDER BY` clause is not used, sort in default way, the SQL statement is：
+```sql
+select * from root.ln.** where time <= 2017-11-01T00:01:00 align by device;
+```
+The result below indicates `ORDER BY DEVICE ASC,TIME ASC` is the clause in default situation.
+`ASC` can be omitted because it's the default ordering.
+```
++-----------------------------+-----------------+--------+------+-----------+
+| Time| Device|hardware|status|temperature|
++-----------------------------+-----------------+--------+------+-----------+
+|2017-11-01T00:00:00.000+08:00|root.ln.wf01.wt01| null| true| 25.96|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| true| 24.36|
+|1970-01-01T08:00:00.001+08:00|root.ln.wf02.wt02| v1| true| null|
+|1970-01-01T08:00:00.002+08:00|root.ln.wf02.wt02| v2| false| null|
+|2017-11-01T00:00:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
++-----------------------------+-----------------+--------+------+-----------+
+Total line number = 6
+```
+Besides，`ALIGN BY DEVICE` and `ORDER BY` clauses can be used with aggregate query，the SQL statement is：
+```sql
+select count(*) from root.ln.** group by ((2017-11-01T00:00:00.000+08:00,2017-11-01T00:03:00.000+08:00],1m) order by device asc,time asc align by device
+```
+The result shows below:
+```
++-----------------------------+-----------------+---------------+-------------+------------------+
+| Time| Device|count(hardware)|count(status)|count(temperature)|
++-----------------------------+-----------------+---------------+-------------+------------------+
+|2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| 1| 1|
+|2017-11-01T00:02:00.000+08:00|root.ln.wf01.wt01| null| 0| 0|
+|2017-11-01T00:03:00.000+08:00|root.ln.wf01.wt01| null| 0| 0|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| 1| 1| null|
+|2017-11-01T00:02:00.000+08:00|root.ln.wf02.wt02| 0| 0| null|
+|2017-11-01T00:03:00.000+08:00|root.ln.wf02.wt02| 0| 0| null|
++-----------------------------+-----------------+---------------+-------------+------------------+
+Total line number = 6
+```
\ No newline at end of file
diff --git a/docs/UserGuide/Query-Data/Overview.md b/docs/UserGuide/Query-Data/Overview.md
index 99904aed45..462b9c07f8 100644
--- a/docs/UserGuide/Query-Data/Overview.md
+++ b/docs/UserGuide/Query-Data/Overview.md
@@ -89,7 +89,8 @@ SELECT [LAST] selectExpr [, selectExpr] ...
- The `ORDER BY` clause is used to specify how the result set is sorted.
- In ALIGN BY TIME mode: By default, they are sorted in ascending order of timestamp size, and `ORDER BY TIME DESC` can be used to specify that the result set is sorted in descending order of timestamp.
-- In ALIGN BY DEVICE mode: arrange according to the device first, and sort each device in ascending order according to the timestamp. The `ORDER BY` clause is not supported now.
+- In ALIGN BY DEVICE mode: arrange according to the device first, and sort each device in ascending order according to the timestamp. The ordering and priority can be adjusted by `ORDER BY` clause.
+- For details and examples, see the document [Order By](./Order-By.md).
### `FILL` clause
diff --git a/docs/zh/UserGuide/Query-Data/Align-By.md b/docs/zh/UserGuide/Query-Data/Align-By.md
index 83b9bbc18f..77bc9fc3e0 100644
--- a/docs/zh/UserGuide/Query-Data/Align-By.md
+++ b/docs/zh/UserGuide/Query-Data/Align-By.md
@@ -59,3 +59,7 @@ select * from root.ln.** where time <= 2017-11-01T00:01:00 align by device;
Total line number = 6
It costs 0.012s
```
+## 设备对齐模式下的排序
+在设备对齐模式下，默认按照设备名的字典序升序排列，每个设备内部按照时间戳大小升序排列，可以通过 `ORDER BY` 子句调整设备列和时间列的排序优先级。
+
+详细说明及示例见文档 [结果集排序](./Order-By.md)。
\ No newline at end of file
diff --git a/docs/zh/UserGuide/Query-Data/Order-By.md b/docs/zh/UserGuide/Query-Data/Order-By.md
index 20cdc6b82c..051f837afc 100644
--- a/docs/zh/UserGuide/Query-Data/Order-By.md
+++ b/docs/zh/UserGuide/Query-Data/Order-By.md
@@ -21,4 +21,111 @@
# 结果集排序
-TODO @yang caiyin
\ No newline at end of file
+## 时间对齐模式下的排序
+IoTDB的查询结果集默认按照时间对齐，可以使用`ORDER BY TIME`的子句指定时间戳的排列顺序。示例代码如下：
+```sql
+select * from root.ln.** where time <= 2017-11-01T00:01:00 order by time desc;
+```
+执行结果：
+
+```
++-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
+| Time|root.ln.wf02.wt02.hardware|root.ln.wf02.wt02.status|root.ln.wf01.wt01.temperature|root.ln.wf01.wt01.status|
++-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
+|2017-11-01T00:01:00.000+08:00| v2| true| 24.36| true|
+|2017-11-01T00:00:00.000+08:00| v2| true| 25.96| true|
+|1970-01-01T08:00:00.002+08:00| v2| false| null| null|
+|1970-01-01T08:00:00.001+08:00| v1| true| null| null|
++-----------------------------+--------------------------+------------------------+-----------------------------+------------------------+
+Total line number = 4
+```
+## 设备对齐模式下的排序
+当使用`ALIGN BY DEVICE`查询对齐模式下的结果集时，可以使用`ORDER BY`子句对返回的结果集顺序进行规定。
+
+在设备对齐模式下支持4种排序模式的子句,其中包括两种排序键，`DEVICE`和`TIME`，靠前的排序键为主排序键，每种排序键都支持`ASC`和`DESC`两种排列顺序。
+1. ``ORDER BY DEVICE``: 按照设备名的字典序进行排序，排序方式为字典序排序，在这种情况下，相同名的设备会以组的形式进行展示。
+
+2. ``ORDER BY TIME``: 按照时间戳进行排序，此时不同的设备对应的数据点会按照时间戳的优先级被打乱排序。
+
+3. ``ORDER BY DEVICE,TIME``: 按照设备名的字典序进行排序，设备名相同的数据点会通过时间戳进行排序。
+
+4. ``ORDER BY TIME,DEVICE``: 按照时间戳进行排序，时间戳相同的数据点会通过设备名的字典序进行排序。
+
+> 为了保证结果的可观性，当不使用`ORDER BY`子句，仅使用`ALIGN BY DEVICE`时，会为设备视图提供默认的排序方式。其中默认的排序视图为``ORDER BY DEVCE,TIME``，默认的排序顺序为`ASC`，
+> 即结果集默认先按照设备名升序排列，在相同设备名内再按照时间戳升序排序。
+
+
+当主排序键为`DEVICE`时，结果集的格式与默认情况类似：先按照设备名对结果进行排列，在相同的设备名下内按照时间戳进行排序。示例代码如下：
+```sql
+select * from root.ln.** where time <= 2017-11-01T00:01:00 order by device desc,time asc align by device;
+```
+执行结果：
+
+```
++-----------------------------+-----------------+--------+------+-----------+
+| Time| Device|hardware|status|temperature|
++-----------------------------+-----------------+--------+------+-----------+
+|1970-01-01T08:00:00.001+08:00|root.ln.wf02.wt02| v1| true| null|
+|1970-01-01T08:00:00.002+08:00|root.ln.wf02.wt02| v2| false| null|
+|2017-11-01T00:00:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
+|2017-11-01T00:00:00.000+08:00|root.ln.wf01.wt01| null| true| 25.96|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| true| 24.36|
++-----------------------------+-----------------+--------+------+-----------+
+Total line number = 6
+```
+主排序键为`Time`时，结果集会先按照时间戳进行排序，在时间戳相等时按照设备名排序。
+示例代码如下：
+```sql
+select * from root.ln.** where time <= 2017-11-01T00:01:00 order by time asc,device desc align by device;
+```
+执行结果：
+```
++-----------------------------+-----------------+--------+------+-----------+
+| Time| Device|hardware|status|temperature|
++-----------------------------+-----------------+--------+------+-----------+
+|1970-01-01T08:00:00.001+08:00|root.ln.wf02.wt02| v1| true| null|
+|1970-01-01T08:00:00.002+08:00|root.ln.wf02.wt02| v2| false| null|
+|2017-11-01T00:00:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
+|2017-11-01T00:00:00.000+08:00|root.ln.wf01.wt01| null| true| 25.96|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| true| 24.36|
++-----------------------------+-----------------+--------+------+-----------+
+Total line number = 6
+```
+当没有显式指定时，主排序键默认为`Device`，排序顺序默认为`ASC`，示例代码如下：
+```sql
+select * from root.ln.** where time <= 2017-11-01T00:01:00 align by device;
+```
+结果如图所示，可以看出，`ORDER BY DEVICE ASC,TIME ASC`就是默认情况下的排序方式，由于`ASC`是默认排序顺序，此处可以省略。
+```
++-----------------------------+-----------------+--------+------+-----------+
+| Time| Device|hardware|status|temperature|
++-----------------------------+-----------------+--------+------+-----------+
+|2017-11-01T00:00:00.000+08:00|root.ln.wf01.wt01| null| true| 25.96|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| true| 24.36|
+|1970-01-01T08:00:00.001+08:00|root.ln.wf02.wt02| v1| true| null|
+|1970-01-01T08:00:00.002+08:00|root.ln.wf02.wt02| v2| false| null|
+|2017-11-01T00:00:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| v2| true| null|
++-----------------------------+-----------------+--------+------+-----------+
+Total line number = 6
+```
+同样，可以在聚合查询中使用`ALIGN BY DEVICE`和`ORDER BY`子句，对聚合后的结果进行排序，示例代码如下所示：
+```sql
+select count(*) from root.ln.** group by ((2017-11-01T00:00:00.000+08:00,2017-11-01T00:03:00.000+08:00],1m) order by device asc,time asc align by device
+```
+执行结果：
+```
++-----------------------------+-----------------+---------------+-------------+------------------+
+| Time| Device|count(hardware)|count(status)|count(temperature)|
++-----------------------------+-----------------+---------------+-------------+------------------+
+|2017-11-01T00:01:00.000+08:00|root.ln.wf01.wt01| null| 1| 1|
+|2017-11-01T00:02:00.000+08:00|root.ln.wf01.wt01| null| 0| 0|
+|2017-11-01T00:03:00.000+08:00|root.ln.wf01.wt01| null| 0| 0|
+|2017-11-01T00:01:00.000+08:00|root.ln.wf02.wt02| 1| 1| null|
+|2017-11-01T00:02:00.000+08:00|root.ln.wf02.wt02| 0| 0| null|
+|2017-11-01T00:03:00.000+08:00|root.ln.wf02.wt02| 0| 0| null|
++-----------------------------+-----------------+---------------+-------------+------------------+
+Total line number = 6
+```
\ No newline at end of file
diff --git a/docs/zh/UserGuide/Query-Data/Overview.md b/docs/zh/UserGuide/Query-Data/Overview.md
index 4941cf0394..7d2207e324 100644
--- a/docs/zh/UserGuide/Query-Data/Overview.md
+++ b/docs/zh/UserGuide/Query-Data/Overview.md
@@ -89,11 +89,8 @@ SELECT [LAST] selectExpr [, selectExpr] ...
- `ORDER BY` 子句用于指定结果集的排序方式。
- 按时间对齐模式下：默认按照时间戳大小升序排列，可以通过 `ORDER BY TIME DESC` 指定结果集按照时间戳大小降序排列。
-- 按设备对齐模式下：先按照设备排列，每个设备内部按照时间戳大小升序排列，暂不支持使用 `ORDER BY` 子句。
-
-[comment]: <> (- 按设备对齐模式下：默认按照设备名的字典序升序排列，每个设备内部按照时间戳大小升序排列，可以通过 `ORDER BY` 子句调整设备列和时间列的排序优先级。)
-
-[comment]: <> (- 详细说明及示例见文档 [结果集排序](./Order-By.md) 。)
+- 按设备对齐模式下：默认按照设备名的字典序升序排列，每个设备内部按照时间戳大小升序排列，可以通过 `ORDER BY` 子句调整设备列和时间列的排序优先级。
+- 详细说明及示例见文档 [结果集排序](./Order-By.md)。
### `FILL` 子句