You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@iotdb.apache.org by qi...@apache.org on 2019/11/28 12:24:32 UTC
[incubator-iotdb] branch rel/0.9 updated: [IOTDB-321] Improve some
SQL related definition in documents (#592)
This is an automated email from the ASF dual-hosted git repository.
qiaojialin pushed a commit to branch rel/0.9
in repository https://gitbox.apache.org/repos/asf/incubator-iotdb.git
The following commit(s) were added to refs/heads/rel/0.9 by this push:
new b4bf968 [IOTDB-321] Improve some SQL related definition in documents (#592)
new b61455d Merge remote-tracking branch 'origin/rel/0.9' into rel/0.9
b4bf968 is described below
commit b4bf9683767fb966cf7d390ec39f6eeaa076f0e8
Author: Zesong Sun <sz...@mails.tsinghua.edu.cn>
AuthorDate: Thu Nov 28 20:21:12 2019 +0800
[IOTDB-321] Improve some SQL related definition in documents (#592)
* Improvement some SQL related definition in documents
(cherry picked from commit 955cd99cd6941f8cdcf953790e87c8c7dfedbca7)
---
.../1-DDL (Data Definition Language).md | 37 +++++++++++++++++++++
.../5-Operation Manual/4-SQL Reference.md | 12 +++++--
.../1-DDL (Data Definition Language).md | 38 +++++++++++++++++++++-
.../5-Operation Manual/4-SQL Reference.md | 12 +++++--
.../java/org/apache/iotdb/jdbc/IoTDBStatement.java | 2 +-
5 files changed, 93 insertions(+), 8 deletions(-)
diff --git a/docs/Documentation-CHN/UserGuide/5-Operation Manual/1-DDL (Data Definition Language).md b/docs/Documentation-CHN/UserGuide/5-Operation Manual/1-DDL (Data Definition Language).md
index 42a1d3b..d0ae818 100644
--- a/docs/Documentation-CHN/UserGuide/5-Operation Manual/1-DDL (Data Definition Language).md
+++ b/docs/Documentation-CHN/UserGuide/5-Operation Manual/1-DDL (Data Definition Language).md
@@ -102,6 +102,43 @@ IoTDB > COUNT TIMESERIES root.ln.*.*.status
IoTDB > COUNT TIMESERIES root.ln.wf01.wt01.status
```
+除此之外，还可以通过定义`LEVEL`来统计指定层级下的时间序列个数。这条语句可以用来统计每一个设备下的传感器数量，语法为：`COUNT TIMESERIES <Path> GROUP BY LEVEL=<INTEGER>`。
+
+例如有如下时间序列（可以使用`show timeseries`展示所有时间序列）：
+
+<center><img style="width:100%; max-width:800px; margin-left:auto; margin-right:auto; display:block;" src="https://user-images.githubusercontent.com/19167280/69792072-cdc8a480-1200-11ea-8cec-321fef618a12.png"></center>
+
+那么Metadata Tree如下所示：
+
+<center><img style="width:100%; max-width:600px; margin-left:auto; margin-right:auto; display:block;" src="https://user-images.githubusercontent.com/19167280/69792176-1718f400-1201-11ea-861a-1a83c07ca144.jpg"></center>
+
+可以看到，`root`被定义为`LEVEL=0`。那么当你输入如下语句时：
+
+```
+IoTDB > COUNT TIMESERIES root GROUP BY LEVEL=1
+IoTDB > COUNT TIMESERIES root.ln GROUP BY LEVEL=2
+IoTDB > COUNT TIMESERIES root.ln.wf01 GROUP BY LEVEL=2
+```
+
+你将得到以下结果：
+<center><img style="width:100%; max-width:800px; margin-left:auto; margin-right:auto; display:block;" src="https://user-images.githubusercontent.com/19167280/69792071-cb664a80-1200-11ea-8386-02dd12046c4b.png"></center>
+
+> 注意：时间序列的路径只是过滤条件，与level的定义无关。
+
+### 统计节点数
+IoTDB支持使用`COUNT NODES <Path> LEVEL=<INTEGER>`来统计当前Metadata树下指定层级的节点个数，这条语句可以用来统计设备数。例如：
+
+```
+IoTDB > COUNT NODES root LEVEL=2
+IoTDB > COUNT NODES root.ln LEVEL=2
+IoTDB > COUNT NODES root.ln.wf01 LEVEL=3
+```
+
+对于上面提到的例子和Metadata Tree，你可以获得如下结果：
+<center><img style="width:100%; max-width:800px; margin-left:auto; margin-right:auto; display:block;" src="https://user-images.githubusercontent.com/19167280/69792060-c73a2d00-1200-11ea-8ec4-be7145fd6c8c.png"></center>
+
+> 注意：时间序列的路径只是过滤条件，与level的定义无关。
+
### 删除时间序列
我们可以使用`DELETE TimeSeries <PrefixPath>`语句来删除我们之前创建的时间序列。SQL语句如下所示：
```
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 44094e7..260acab 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
@@ -212,7 +212,9 @@ FromClause : <PrefixPath> (COMMA <PrefixPath>)?
WhereClause : <Condition> [(AND | OR) <Condition>]*
Condition : <Expression> [(AND | OR) <Expression>]*
Expression : [NOT | !]? <TimeExpr> | [NOT | !]? <SensorExpr>
-TimeExpr : TIME PrecedenceEqualOperator <TimeValue>
+TimeExpr : TIME PrecedenceEqualOperator (<TimeValue> | <RelativeTime>)
+RelativeTimeDurationUnit = Integer ('Y'|'MO'|'W'|'D'|'H'|'M'|'S'|'MS'|'US'|'NS')
+RelativeTime : (now() | <TimeValue>) [(+|-) RelativeTimeDurationUnit]+
SensorExpr : (<Timeseries> | <Path>) PrecedenceEqualOperator <PointValue>
Eg: IoTDB > SELECT status, temperature FROM root.ln.wf01.wt01 WHERE temperature < 24 and time > 2017-11-1 0:13:00
Eg. IoTDB > SELECT * FROM root
@@ -238,7 +240,9 @@ FromClause : <PrefixPath>
WhereClause : <Condition> [(AND | OR) <Condition>]*
Condition : <Expression> [(AND | OR) <Expression>]*
Expression : [NOT | !]? <TimeExpr> | [NOT | !]? <SensorExpr>
-TimeExpr : TIME PrecedenceEqualOperator <TimeValue>
+TimeExpr : TIME PrecedenceEqualOperator (<TimeValue> | <RelativeTime>)
+RelativeTimeDurationUnit = Integer ('Y'|'MO'|'W'|'D'|'H'|'M'|'S'|'MS'|'US'|'NS')
+RelativeTime : (now() | <TimeValue>) [(+|-) RelativeTimeDurationUnit]+
SensorExpr : (<Timeseries> | <Path>) PrecedenceEqualOperator <PointValue>
GroupByClause : LPAREN <TimeUnit> (COMMA TimeValue)? COMMA <TimeInterval> (COMMA <TimeInterval>)* RPAREN
TimeUnit : Integer <DurationUnit>
@@ -292,7 +296,9 @@ FromClause : <Path>
WhereClause : <Condition> [(AND | OR) <Condition>]*
Condition : <Expression> [(AND | OR) <Expression>]*
Expression: [NOT|!]?<TimeExpr> | [NOT|!]?<SensorExpr>
-TimeExpr : TIME PrecedenceEqualOperator <TimeValue>
+TimeExpr : TIME PrecedenceEqualOperator (<TimeValue> | <RelativeTime>)
+RelativeTimeDurationUnit = Integer ('Y'|'MO'|'W'|'D'|'H'|'M'|'S'|'MS'|'US'|'NS')
+RelativeTime : (now() | <TimeValue>) [(+|-) RelativeTimeDurationUnit]+
SensorExpr : (<Timeseries>|<Path>) PrecedenceEqualOperator <PointValue>
LIMITClause : <N> [OFFSETClause]?
N : NonNegativeInteger
diff --git a/docs/Documentation/UserGuide/5-Operation Manual/1-DDL (Data Definition Language).md b/docs/Documentation/UserGuide/5-Operation Manual/1-DDL (Data Definition Language).md
index 85b27ba..59b9007 100644
--- a/docs/Documentation/UserGuide/5-Operation Manual/1-DDL (Data Definition Language).md
+++ b/docs/Documentation/UserGuide/5-Operation Manual/1-DDL (Data Definition Language).md
@@ -91,7 +91,7 @@ The results are shown below respectly:
It is worth noting that when the queried path does not exist, the system will return no timeseries.
### Count Timeseries
-IoTDB are able to use `COUNT TIMESERIES<Path>` to count the amount of timeseries in the path. SQL statements are as follows:
+IoTDB is able to use `COUNT TIMESERIES<Path>` to count the number of timeseries in the path. SQL statements are as follows:
```
IoTDB > COUNT TIMESERIES root
IoTDB > COUNT TIMESERIES root.ln
@@ -99,6 +99,42 @@ IoTDB > COUNT TIMESERIES root.ln.*.*.status
IoTDB > COUNT TIMESERIES root.ln.wf01.wt01.status
```
+Besides, `LEVEL` could be defined to show count the number of timeseries of each node at the given level in current Metadata Tree. This could be used to query the number of sensors under each device. The grammar is: `COUNT TIMESERIES <Path> GROUP BY LEVEL=<INTEGER>`.
+
+For example, if there are several timeseires (use `show timeseries` to show all timeseries):
+<center><img style="width:100%; max-width:800px; margin-left:auto; margin-right:auto; display:block;" src="https://user-images.githubusercontent.com/19167280/69792072-cdc8a480-1200-11ea-8cec-321fef618a12.png"></center>
+
+Then the Metadata Tree will be as below:
+<center><img style="width:100%; max-width:600px; margin-left:auto; margin-right:auto; display:block;" src="https://user-images.githubusercontent.com/19167280/69792176-1718f400-1201-11ea-861a-1a83c07ca144.jpg"></center>
+
+As can be seen, `root` is considered as `LEVEL=0`. So when you enter statements such as:
+
+```
+IoTDB > COUNT TIMESERIES root GROUP BY LEVEL=1
+IoTDB > COUNT TIMESERIES root.ln GROUP BY LEVEL=2
+IoTDB > COUNT TIMESERIES root.ln.wf01 GROUP BY LEVEL=2
+```
+
+You will get following results:
+
+<center><img style="width:100%; max-width:800px; margin-left:auto; margin-right:auto; display:block;" src="https://user-images.githubusercontent.com/19167280/69792071-cb664a80-1200-11ea-8386-02dd12046c4b.png"></center>
+
+> Note: The path of timeseries is just a filter condition, which has no relationship with the definition of level.
+
+### Count Nodes
+IoTDB is able to use `COUNT NODES <Path> LEVEL=<INTEGER>` to count the number of nodes at the given level in current Metadata Tree. This could be used to query the number of devices. The usage are as follows:
+```
+IoTDB > COUNT NODES root LEVEL=2
+IoTDB > COUNT NODES root.ln LEVEL=2
+IoTDB > COUNT NODES root.ln.wf01 LEVEL=3
+```
+
+As for the above mentioned example and Metadata tree, you can get following results:
+
+<center><img style="width:100%; max-width:800px; margin-left:auto; margin-right:auto; display:block;" src="https://user-images.githubusercontent.com/19167280/69792060-c73a2d00-1200-11ea-8ec4-be7145fd6c8c.png"></center>
+
+> Note: The path of timeseries is just a filter condition, which has no relationship with the definition of level.
+
### Delete Timeseries
To delete the timeseries we created before, we are able to use `DELETE TimeSeries <PrefixPath>` statement.
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 f15e0c8..c44eae5 100644
--- a/docs/Documentation/UserGuide/5-Operation Manual/4-SQL Reference.md
+++ b/docs/Documentation/UserGuide/5-Operation Manual/4-SQL Reference.md
@@ -221,7 +221,9 @@ FromClause : <PrefixPath> (COMMA <PrefixPath>)?
WhereClause : <Condition> [(AND | OR) <Condition>]*
Condition : <Expression> [(AND | OR) <Expression>]*
Expression : [NOT | !]? <TimeExpr> | [NOT | !]? <SensorExpr>
-TimeExpr : TIME PrecedenceEqualOperator <TimeValue>
+TimeExpr : TIME PrecedenceEqualOperator (<TimeValue> | <RelativeTime>)
+RelativeTimeDurationUnit = Integer ('Y'|'MO'|'W'|'D'|'H'|'M'|'S'|'MS'|'US'|'NS')
+RelativeTime : (now() | <TimeValue>) [(+|-) RelativeTimeDurationUnit]+
SensorExpr : (<Timeseries> | <Path>) PrecedenceEqualOperator <PointValue>
Eg: IoTDB > SELECT status, temperature FROM root.ln.wf01.wt01 WHERE temperature < 24 and time > 2017-11-1 0:13:00
Eg. IoTDB > SELECT * FROM root
@@ -247,7 +249,9 @@ FromClause : <PrefixPath>
WhereClause : <Condition> [(AND | OR) <Condition>]*
Condition : <Expression> [(AND | OR) <Expression>]*
Expression : [NOT | !]? <TimeExpr> | [NOT | !]? <SensorExpr>
-TimeExpr : TIME PrecedenceEqualOperator <TimeValue>
+TimeExpr : TIME PrecedenceEqualOperator (<TimeValue> | <RelativeTime>)
+RelativeTimeDurationUnit = Integer ('Y'|'MO'|'W'|'D'|'H'|'M'|'S'|'MS'|'US'|'NS')
+RelativeTime : (now() | <TimeValue>) [(+|-) RelativeTimeDurationUnit]+
SensorExpr : (<Timeseries> | <Path>) PrecedenceEqualOperator <PointValue>
GroupByClause : LPAREN <TimeUnit> (COMMA TimeValue)? COMMA <TimeInterval> (COMMA <TimeInterval>)* RPAREN
TimeUnit : Integer <DurationUnit>
@@ -301,7 +305,9 @@ FromClause : <Path>
WhereClause : <Condition> [(AND | OR) <Condition>]*
Condition : <Expression> [(AND | OR) <Expression>]*
Expression: [NOT|!]?<TimeExpr> | [NOT|!]?<SensorExpr>
-TimeExpr : TIME PrecedenceEqualOperator <TimeValue>
+TimeExpr : TIME PrecedenceEqualOperator (<TimeValue> | <RelativeTime>)
+RelativeTimeDurationUnit = Integer ('Y'|'MO'|'W'|'D'|'H'|'M'|'S'|'MS'|'US'|'NS')
+RelativeTime : (now() | <TimeValue>) [(+|-) RelativeTimeDurationUnit]+
SensorExpr : (<Timeseries>|<Path>) PrecedenceEqualOperator <PointValue>
LIMITClause : <N> [OFFSETClause]?
N : PositiveInteger
diff --git a/jdbc/src/main/java/org/apache/iotdb/jdbc/IoTDBStatement.java b/jdbc/src/main/java/org/apache/iotdb/jdbc/IoTDBStatement.java
index ee7ded2..47168a9 100644
--- a/jdbc/src/main/java/org/apache/iotdb/jdbc/IoTDBStatement.java
+++ b/jdbc/src/main/java/org/apache/iotdb/jdbc/IoTDBStatement.java
@@ -295,7 +295,7 @@ public class IoTDBStatement implements Statement {
return true;
}
} else if (sqlToLowerCase.startsWith(COUNT_NODES_COMMAND_LOWERCASE)) {
- String[] cmdSplit = sql.split("\\s+", 4);
+ String[] cmdSplit = sqlToLowerCase.split("\\s+", 4);
if (!(cmdSplit.length == 4 && cmdSplit[3].startsWith("level"))) {
throw new SQLException("Error format of \'COUNT NODES <PATH> LEVEL=<INTEGER>\'");
} else {