You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@iotdb.apache.org by hx...@apache.org on 2020/02/08 05:55:15 UTC
[incubator-iotdb] 01/01: add documents for group by device syntax
This is an automated email from the ASF dual-hosted git repository.
hxd pushed a commit to branch iotdb-447
in repository https://gitbox.apache.org/repos/asf/incubator-iotdb.git
commit 4ba3dcf0a4c08e34c5abf4c09d0fd9dcb086a30d
Author: xiangdong huang <sa...@gmail.com>
AuthorDate: Sat Feb 8 13:54:51 2020 +0800
add documents for group by device syntax
---
.../2-DML (Data Manipulation Language).md | 15 ++++
.../5-Operation Manual/4-SQL Reference.md | 89 +++++++++++++++++++---
.../2-DML (Data Manipulation Language).md | 16 ++++
.../5-Operation Manual/4-SQL Reference.md | 24 +++---
4 files changed, 122 insertions(+), 22 deletions(-)
diff --git a/docs/Documentation-CHN/UserGuide/5-Operation Manual/2-DML (Data Manipulation Language).md b/docs/Documentation-CHN/UserGuide/5-Operation Manual/2-DML (Data Manipulation Language).md
index c7f6e46..08c48fd 100644
--- a/docs/Documentation-CHN/UserGuide/5-Operation Manual/2-DML (Data Manipulation Language).md
+++ b/docs/Documentation-CHN/UserGuide/5-Operation Manual/2-DML (Data Manipulation Language).md
@@ -145,6 +145,21 @@ select wf01.wt01.status,wf02.wt02.hardware from root.ln where (time > 2017-11-01
该SQL语句的执行结果如下：
<center><img style="width:100%; max-width:800px; max-height:600px; margin-left:auto; margin-right:auto; display:block;" src="https://user-images.githubusercontent.com/13203019/51577450-dcfe0800-1ef4-11e9-9399-4ba2b2b7fb73.jpg"></center>
+#### 其他结果返回形式
+
+IoTDB支持另外两种结果返回形式: 按设备时间对齐 'group by device' 和 时序不对齐 'disable align'.
+
+'group by device' 对齐方式下，设备ID会单独作为一列出现。在select 子句中写了多少列，最终结果就会有该列数+2 （时间列和设备名字列）。SQL形如:
+
+```
+select s1,s2 from root.sg1.* GROUP BY DEVICE
+```
+
+更多语法请参照 SQL REFERENCE.
+
+'disable align' 意味着每条时序就有3列存在。更多语法请参照 SQL REFERENCE.
+
+
### 降频聚合查询
本章节主要介绍降频聚合查询的相关示例，
diff --git a/docs/Documentation-CHN/UserGuide/5-Operation Manual/4-SQL Reference.md b/docs/Documentation-CHN/UserGuide/5-Operation Manual/4-SQL Reference.md
index 2507c71..b34ac01 100644
--- a/docs/Documentation-CHN/UserGuide/5-Operation Manual/4-SQL Reference.md
+++ b/docs/Documentation-CHN/UserGuide/5-Operation Manual/4-SQL Reference.md
@@ -334,23 +334,94 @@ Note: The order of <LIMITClause> and <SLIMITClause> does not affect the grammati
Note: <FillClause> can not use <LIMITClause> but not <SLIMITClause>.
```
+* Group by device语句
+```
+GroupbyDeviceClause : GROUP BY DEVICE
+
+规则:
+1. 大小写不敏感.
+正例: select * from root.sg1 group by device
+正例: select * from root.sg1 GROUP BY DEVICE
+
+2. GroupbyDeviceClause 只能放在末尾.
+正例: select * from root.sg1 where time > 10 group by device
+错例: select * from root.sg1 group by device where time > 10
+
+3. Select子句中的path只能是单层，或者通配符，不允许有path分隔符"."。
+正例: select s0,s1 from root.sg1.* group by device
+正例: select s0,s1 from root.sg1.d0, root.sg1.d1 group by device
+正例: select * from root.sg1.* group by device
+正例: select * from root group by device
+正例: select s0,s1,* from root.*.* group by device
+错例: select d0.s1, d0.s2, d1.s0 from root.sg1 group by device
+错例: select *.s0, *.s1 from root.* group by device
+错例: select *.*.* from root group by device
+
+4.相同measurement的各设备的数据类型必须都相同，
+
+正例: select s0 from root.sg1.d0,root.sg1.d1 group by device
+root.sg1.d0.s0 and root.sg1.d1.s0 are both INT32.
+
+正例: select count(s0) from root.sg1.d0,root.sg1.d1 group by device
+count(root.sg1.d0.s0) and count(root.sg1.d1.s0) are both INT64.
+
+错例: select s0 from root.sg1.d0, root.sg2.d3 group by device
+root.sg1.d0.s0 is INT32 while root.sg2.d3.s0 is FLOAT.
+
+5. 结果集的展示规则：对于select中给出的列，不论是否有数据（是否被注册），均会被显示。此外，select子句中还支持常数列（例如，'a', '123'等等）。
+例如, "select s0,s1,s2,'abc',s1,s2 from root.sg.d0, root.sg.d1, root.sg.d2 group by device". 假设只有下述三列有数据：
+- root.sg.d0.s0
+- root.sg.d0.s1
+- root.sg.d1.s0
+
+结果集形如:
+
+| Time | Device | s0 | s1 | s2 | 'abc' | s1 | s2 |
+| --- | --- | ---| ---| null | 'abc' | ---| null |
+| 1 |root.sg.d0| 20 | 2.5| null | 'abc' | 2.5| null |
+| 2 |root.sg.d0| 23 | 3.1| null | 'abc' | 3.1| null |
+| ... | ... | ...| ...| null | 'abc' | ...| null |
+| 1 |root.sg.d1| 12 |null| null | 'abc' |null| null |
+| 2 |root.sg.d1| 19 |null| null | 'abc' |null| null |
+| ... | ... | ...| ...| null | 'abc' | ...| null |
+
+注意注意 设备'root.sg.d1'的's0'的值全为null
+
+6. 在From中重复写设备名字或者设备前缀是没有任何作用的。
+例如, "select s0,s1 from root.sg.d0,root.sg.d0,root.sg.d1 group by device" 等于 "select s0,s1 from root.sg.d0,root.sg.d1 group by device".
+例如. "select s0,s1 from root.sg.*,root.sg.d0 group by device" 等于 "select s0,s1 from root.sg.* group by device".
+
+7. 在Select子句中重复写列名是生效的。例如, "select s0,s0,s1 from root.sg.* group by device" 不等于 "select s0,s1 from root.sg.* group by device".
+
+8. 更多正例:
+ - select * from root.vehicle group by device
+ - select s0,s0,s1 from root.vehicle.* group by device
+ - select s0,s1 from root.vehicle.* limit 10 offset 1 group by device
+ - select * from root.vehicle slimit 10 soffset 2 group by device
+ - select * from root.vehicle where time > 10 group by device
+ - select * from root.vehicle where root.vehicle.d0.s0>0 group by device
+ - select count(*) from root.vehicle group by device
+ - select sum(*) from root.vehicle GROUP BY (20ms,0,[2,50]) group by device
+ - select * from root.vehicle where time = 3 Fill(int32[previous, 5ms]) group by device
+```
+
* Disable align语句
```
规则:
1. 大小写均可.
-Correct example: select * from root.sg1 disable align
-Correct example: select * from root.sg1 DISABLE ALIGN
+正例: select * from root.sg1 disable align
+正例: select * from root.sg1 DISABLE ALIGN
2. Disable Align只能用于查询语句句尾.
-Correct example: select * from root.sg1 where time > 10 disable align
-Wrong example: select * from root.sg1 disable align where time > 10
+正例: select * from root.sg1 where time > 10 disable align
+错例: select * from root.sg1 disable align where time > 10
3. Disable Align 不能用于聚合查询、Fill语句、Group by或Group by device语句，但可用于Limit语句。
-Correct example: select * from root.sg1 limit 3 offset 2 disable align
-Correct example: select * from root.sg1 slimit 3 soffset 2 disable align
-Wrong example: select count(s0),count(s1) from root.sg1.d1 disable align
-Wrong example: select * from root.vehicle where root.vehicle.d0.s0>0 disable align
-Wrong example: select * from root.vehicle group by device disable align
+正例: select * from root.sg1 limit 3 offset 2 disable align
+正例: select * from root.sg1 slimit 3 soffset 2 disable align
+错例: select count(s0),count(s1) from root.sg1.d1 disable align
+错例: select * from root.vehicle where root.vehicle.d0.s0>0 disable align
+错例: select * from root.vehicle group by device disable align
4. 结果显示若无数据显示为空白.
diff --git a/docs/Documentation/UserGuide/5-Operation Manual/2-DML (Data Manipulation Language).md b/docs/Documentation/UserGuide/5-Operation Manual/2-DML (Data Manipulation Language).md
index 7ce3b7d..56f4a63 100644
--- a/docs/Documentation/UserGuide/5-Operation Manual/2-DML (Data Manipulation Language).md
+++ b/docs/Documentation/UserGuide/5-Operation Manual/2-DML (Data Manipulation Language).md
@@ -512,6 +512,22 @@ The result is shown below:
<center><img style="width:100%; max-width:800px; max-height:600px; margin-left:auto; margin-right:auto; display:block;" src="https://user-images.githubusercontent.com/13203019/51577879-64984680-1ef6-11e9-9d7b-57dd60fab60e.jpg"></center>
+#### Other ResultSet Format
+
+In addition, IoTDB supports two another resultset format: 'group by device' and 'disable align'.
+
+The 'group by device' indicates that the deviceId is considered as a column. Therefore, there are totally limited columns in the dataset.
+
+The SQL statement is:
+
+```
+select s1,s2 from root.sg1.* GROUP BY DEVICE
+```
+
+For more syntax description, please read SQL REFERENCE.
+
+The 'disable align' indicaes that there are 3 columns for each time series in the resultset. For more syntax description, please read SQL REFERENCE.
+
#### Error Handling
When the parameter N/SN of LIMIT/SLIMIT exceeds the size of the result set, IoTDB will return all the results as expected. For example, the query result of the original SQL statement consists of six rows, and we select the first 100 rows through the LIMIT clause:
diff --git a/docs/Documentation/UserGuide/5-Operation Manual/4-SQL Reference.md b/docs/Documentation/UserGuide/5-Operation Manual/4-SQL Reference.md
index d9d70e5..050f08c 100644
--- a/docs/Documentation/UserGuide/5-Operation Manual/4-SQL Reference.md
+++ b/docs/Documentation/UserGuide/5-Operation Manual/4-SQL Reference.md
@@ -377,26 +377,24 @@ count(root.sg1.d0.s0) and count(root.sg1.d1.s0) are both INT64.
Wrong example: select s0 from root.sg1.d0, root.sg2.d3 group by device
root.sg1.d0.s0 is INT32 while root.sg2.d3.s0 is FLOAT.
-5. The display principle of the result table is that only when the column (or row) has existing data will the column (or row) be shown, with nonexistent cells being null.
-For example, "select s0,s1,s2 from root.sg.d0, root.sg.d1, root.sg.d2 group by device". Suppose that the actual existing timeseries are as follows:
+5. The display principle of the result table is that all the columns (no matther whther a column has has existing data) will be shown, with nonexistent cells being null. Besides, the select clause support const column (e.g., 'a', '123' etc..).
+For example, "select s0,s1,s2,'abc',s1,s2 from root.sg.d0, root.sg.d1, root.sg.d2 group by device". Suppose that the actual existing timeseries are as follows:
- root.sg.d0.s0
- root.sg.d0.s1
- root.sg.d1.s0
-Then the header of the result table will be: [Time, Device, s0, s1].
-And you could expect a table like:
+Then you could expect a table like:
-| Time | Device | s0 | s1 |
-| --- | --- | ---| ---|
-| 1 |root.sg.d0| 20 | 2.5|
-| 2 |root.sg.d0| 23 | 3.1|
-| ... | ... | ...| ...|
-| 1 |root.sg.d1| 12 |null|
-| 2 |root.sg.d1| 19 |null|
-| ... | ... | ...| ...|
+| Time | Device | s0 | s1 | s2 | 'abc' | s1 | s2 |
+| --- | --- | ---| ---| null | 'abc' | ---| null |
+| 1 |root.sg.d0| 20 | 2.5| null | 'abc' | 2.5| null |
+| 2 |root.sg.d0| 23 | 3.1| null | 'abc' | 3.1| null |
+| ... | ... | ...| ...| null | 'abc' | ...| null |
+| 1 |root.sg.d1| 12 |null| null | 'abc' |null| null |
+| 2 |root.sg.d1| 19 |null| null | 'abc' |null| null |
+| ... | ... | ...| ...| null | 'abc' | ...| null |
Note that the cells of measurement 's0' and device 'root.sg.d1' are all null.
-Also note that the column of 's2' and the rows of 'root.sg.d2' are not existent.
6. The duplicated devices in the prefix paths are neglected.
For example, "select s0,s1 from root.sg.d0,root.sg.d0,root.sg.d1 group by device" is equal to "select s0,s1 from root.sg.d0,root.sg.d1 group by device".