You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@iotdb.apache.org by ja...@apache.org on 2023/02/28 04:02:10 UTC
[iotdb] branch master updated: [IOTDB-5590] convert __endTime to UTC in cli
This is an automated email from the ASF dual-hosted git repository.
jackietien 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 4cb5ff1b2c [IOTDB-5590] convert __endTime to UTC in cli
4cb5ff1b2c is described below
commit 4cb5ff1b2cb3e90d6d05ba56e4c71253e961efea
Author: YangCaiyin <yc...@gmail.com>
AuthorDate: Tue Feb 28 12:02:05 2023 +0800
[IOTDB-5590] convert __endTime to UTC in cli
---
.../java/org/apache/iotdb/cli/AbstractCli.java | 8 ++
docs/UserGuide/Query-Data/Group-By.md | 126 ++++++++++-----------
docs/zh/UserGuide/Query-Data/Group-By.md | 126 ++++++++++-----------
3 files changed, 134 insertions(+), 126 deletions(-)
diff --git a/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java b/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java
index bd8a01a7c6..fc260f709a 100644
--- a/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java
+++ b/cli/src/main/java/org/apache/iotdb/cli/AbstractCli.java
@@ -620,10 +620,14 @@ public abstract class AbstractCli {
}
List<List<String>> lists = new ArrayList<>(columnCount);
+ int endTimeIndex = -1;
if (resultSet instanceof IoTDBJDBCResultSet) {
for (int i = 1; i <= columnCount; i++) {
List<String> list = new ArrayList<>(maxPrintRowCount + 1);
String columnLabel = resultSetMetaData.getColumnLabel(i);
+ if (columnLabel.equalsIgnoreCase("__endTime")) {
+ endTimeIndex = i;
+ }
list.add(columnLabel);
lists.add(list);
int count = computeHANCount(columnLabel);
@@ -638,6 +642,10 @@ public abstract class AbstractCli {
tmp =
RpcUtils.formatDatetime(
timeFormat, timestampPrecision, resultSet.getLong(TIMESTAMP_STR), zoneId);
+ } else if (endTimeIndex == i) {
+ tmp =
+ RpcUtils.formatDatetime(
+ timeFormat, timestampPrecision, resultSet.getLong(i), zoneId);
} else {
tmp = resultSet.getString(i);
}
diff --git a/docs/UserGuide/Query-Data/Group-By.md b/docs/UserGuide/Query-Data/Group-By.md
index 96ee621ccb..4acc75b3ca 100644
--- a/docs/UserGuide/Query-Data/Group-By.md
+++ b/docs/UserGuide/Query-Data/Group-By.md
@@ -647,14 +647,14 @@ select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(
```
Get the result below which ignores the row with null value in `s6`.
```
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-| Time|__endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-|1970-01-01T08:00:00.000+08:00| 40| 24.5| 3| 50.0|
-|1970-01-01T08:00:00.050+08:00| 50| null| 1| 50.0|
-|1970-01-01T08:00:00.070+08:00| 90| 84.5| 3| 170.0|
-|1970-01-01T08:00:00.150+08:00| 150| 66.5| 1| 90.0|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| Time| __endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+|1970-01-01T08:00:00.000+08:00|1970-01-01T08:00:00.040+08:00| 24.5| 3| 50.0|
+|1970-01-01T08:00:00.050+08:00|1970-01-01T08:00:00.050+08:00| null| 1| 50.0|
+|1970-01-01T08:00:00.070+08:00|1970-01-01T08:00:00.090+08:00| 84.5| 3| 170.0|
+|1970-01-01T08:00:00.150+08:00|1970-01-01T08:00:00.150+08:00| 66.5| 1| 90.0|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
when ignoreNull is false, the row with null value in `s6` will be considered.
```sql
@@ -662,17 +662,17 @@ select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(
```
Get the following result.
```
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-| Time|__endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-|1970-01-01T08:00:00.000+08:00| 10| 4.5| 2| 10.0|
-|1970-01-01T08:00:00.020+08:00| 30| 29.5| 1| 30.0|
-|1970-01-01T08:00:00.040+08:00| 40| 44.5| 1| 40.0|
-|1970-01-01T08:00:00.050+08:00| 50| null| 1| 50.0|
-|1970-01-01T08:00:00.060+08:00| 60| 64.5| 1| 60.0|
-|1970-01-01T08:00:00.070+08:00| 90| 84.5| 3| 170.0|
-|1970-01-01T08:00:00.150+08:00| 150| 66.5| 1| 90.0|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| Time| __endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+|1970-01-01T08:00:00.000+08:00|1970-01-01T08:00:00.010+08:00| 4.5| 2| 10.0|
+|1970-01-01T08:00:00.020+08:00|1970-01-01T08:00:00.030+08:00| 29.5| 1| 30.0|
+|1970-01-01T08:00:00.040+08:00|1970-01-01T08:00:00.040+08:00| 44.5| 1| 40.0|
+|1970-01-01T08:00:00.050+08:00|1970-01-01T08:00:00.050+08:00| null| 1| 50.0|
+|1970-01-01T08:00:00.060+08:00|1970-01-01T08:00:00.060+08:00| 64.5| 1| 60.0|
+|1970-01-01T08:00:00.070+08:00|1970-01-01T08:00:00.090+08:00| 84.5| 3| 170.0|
+|1970-01-01T08:00:00.150+08:00|1970-01-01T08:00:00.150+08:00| 66.5| 1| 90.0|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
### delta !=0
@@ -682,13 +682,13 @@ select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(
```
Get the result below:
```
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-| Time|__endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-|1970-01-01T08:00:00.000+08:00| 50| 24.5| 4| 100.0|
-|1970-01-01T08:00:00.070+08:00| 90| 84.5| 3| 170.0|
-|1970-01-01T08:00:00.150+08:00| 150| 66.5| 1| 90.0|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| Time| __endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+|1970-01-01T08:00:00.000+08:00|1970-01-01T08:00:00.050+08:00| 24.5| 4| 100.0|
+|1970-01-01T08:00:00.070+08:00|1970-01-01T08:00:00.090+08:00| 84.5| 3| 170.0|
+|1970-01-01T08:00:00.150+08:00|1970-01-01T08:00:00.150+08:00| 66.5| 1| 90.0|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
The sql is shown below:
@@ -697,14 +697,14 @@ select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(
```
Get the result below:
```
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-| Time|__endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-|1970-01-01T08:00:00.000+08:00| 10| 4.5| 2| 10.0|
-|1970-01-01T08:00:00.040+08:00| 50| 44.5| 2| 90.0|
-|1970-01-01T08:00:00.070+08:00| 80| 79.5| 2| 80.0|
-|1970-01-01T08:00:00.090+08:00| 150| 80.5| 2| 180.0|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| Time| __endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+|1970-01-01T08:00:00.000+08:00|1970-01-01T08:00:00.010+08:00| 4.5| 2| 10.0|
+|1970-01-01T08:00:00.040+08:00|1970-01-01T08:00:00.050+08:00| 44.5| 2| 90.0|
+|1970-01-01T08:00:00.070+08:00|1970-01-01T08:00:00.080+08:00| 79.5| 2| 80.0|
+|1970-01-01T08:00:00.090+08:00|1970-01-01T08:00:00.150+08:00| 80.5| 2| 180.0|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
## Aggregation By Condition
@@ -751,30 +751,30 @@ For the following raw data, several query examples are given below:
```
The sql statement to query data with at least two continuous row shown below:
```sql
-select __endTime,max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoringNull=true)
+select max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoringNull=true)
```
Get the result below:
```
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
-| Time|__endTime|max_time(root.sg.beijing.car01.charging_status)|count(root.sg.beijing.car01.vehicle_status)|last_value(root.sg.beijing.car01.soc)|
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
-|1970-01-01T08:00:00.001+08:00| 2| 2| 2| 16.0|
-|1970-01-01T08:00:00.005+08:00| 10| 10| 5| 60.0|
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
+| Time|max_time(root.sg.beijing.car01.charging_status)|count(root.sg.beijing.car01.vehicle_status)|last_value(root.sg.beijing.car01.soc)|
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 2| 2| 16.0|
+|1970-01-01T08:00:00.005+08:00| 10| 5| 60.0|
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
```
When ignoreNull is false, the null value will be treated as a row that doesn't meet the condition.
```sql
-select __endTime,max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoringNull=false)
+select max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoringNull=false)
```
Get the result below, the original group is split.
```
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
-| Time|__endTime|max_time(root.sg.beijing.car01.charging_status)|count(root.sg.beijing.car01.vehicle_status)|last_value(root.sg.beijing.car01.soc)|
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
-|1970-01-01T08:00:00.001+08:00| 2| 2| 2| 16.0|
-|1970-01-01T08:00:00.005+08:00| 7| 7| 3| 36.0|
-|1970-01-01T08:00:00.009+08:00| 10| 10| 2| 60.0|
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
+| Time|max_time(root.sg.beijing.car01.charging_status)|count(root.sg.beijing.car01.vehicle_status)|last_value(root.sg.beijing.car01.soc)|
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 2| 2| 16.0|
+|1970-01-01T08:00:00.005+08:00| 7| 3| 36.0|
+|1970-01-01T08:00:00.009+08:00| 10| 2| 60.0|
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
```
## Aggregation By Session
@@ -833,12 +833,12 @@ select __endTime,count(*) from root.** group by session(1d)
```
Get the result：
```
-+-----------------------------+---------+------------------------------------+---------------------------------+-------------------------------+
-| Time|__endTime|count(root.ln.wf02.wt01.temperature)|count(root.ln.wf02.wt01.hardware)|count(root.ln.wf02.wt01.status)|
-+-----------------------------+---------+------------------------------------+---------------------------------+-------------------------------+
-|1970-01-01T08:00:01.000+08:00| 480000| 15| 18| 15|
-|1970-01-02T08:08:01.000+08:00| 86885000| 5| 5| 5|
-+-----------------------------+---------+------------------------------------+---------------------------------+-------------------------------+
++-----------------------------+-----------------------------+------------------------------------+---------------------------------+-------------------------------+
+| Time| __endTime|count(root.ln.wf02.wt01.temperature)|count(root.ln.wf02.wt01.hardware)|count(root.ln.wf02.wt01.status)|
++-----------------------------+-----------------------------+------------------------------------+---------------------------------+-------------------------------+
+|1970-01-01T08:00:01.000+08:00|1970-01-01T08:08:00.000+08:00| 15| 18| 15|
+|1970-01-02T08:08:01.000+08:00|1970-01-02T08:08:05.000+08:00| 5| 5| 5|
++-----------------------------+-----------------------------+------------------------------------+---------------------------------+-------------------------------+
```
It can be also used with `HAVING` and `ALIGN BY DEVICE` clauses.
```sql
@@ -846,12 +846,12 @@ select __endTime,sum(hardware) from root.ln.wf02.wt01 group by session(50s) havi
```
Get the result below:
```
-+-----------------------------+-----------------+---------+-------------+
-| Time| Device|__endTime|sum(hardware)|
-+-----------------------------+-----------------+---------+-------------+
-|1970-01-01T08:00:01.000+08:00|root.ln.wf02.wt01| 200000| 2475.0|
-|1970-01-01T08:04:20.000+08:00|root.ln.wf02.wt01| 260000| 440.0|
-|1970-01-01T08:05:20.000+08:00|root.ln.wf02.wt01| 320000| 550.0|
-|1970-01-02T08:08:01.000+08:00|root.ln.wf02.wt01| 86885000| 1650.0|
-+-----------------------------+-----------------+---------+-------------+
++-----------------------------+-----------------+-----------------------------+-------------+
+| Time| Device| __endTime|sum(hardware)|
++-----------------------------+-----------------+-----------------------------+-------------+
+|1970-01-01T08:00:01.000+08:00|root.ln.wf02.wt01|1970-01-01T08:03:20.000+08:00| 2475.0|
+|1970-01-01T08:04:20.000+08:00|root.ln.wf02.wt01|1970-01-01T08:04:20.000+08:00| 440.0|
+|1970-01-01T08:05:20.000+08:00|root.ln.wf02.wt01|1970-01-01T08:05:20.000+08:00| 550.0|
+|1970-01-02T08:08:01.000+08:00|root.ln.wf02.wt01|1970-01-02T08:08:05.000+08:00| 1650.0|
++-----------------------------+-----------------+-----------------------------+-------------+
```
\ No newline at end of file
diff --git a/docs/zh/UserGuide/Query-Data/Group-By.md b/docs/zh/UserGuide/Query-Data/Group-By.md
index 4d3c2459c0..efd3c489ea 100644
--- a/docs/zh/UserGuide/Query-Data/Group-By.md
+++ b/docs/zh/UserGuide/Query-Data/Group-By.md
@@ -633,14 +633,14 @@ select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(
```
得到如下的查询结果，这里忽略了s6为null的行
```
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-| Time|__endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-|1970-01-01T08:00:00.000+08:00| 40| 24.5| 3| 50.0|
-|1970-01-01T08:00:00.050+08:00| 50| null| 1| 50.0|
-|1970-01-01T08:00:00.070+08:00| 90| 84.5| 3| 170.0|
-|1970-01-01T08:00:00.150+08:00| 150| 66.5| 1| 90.0|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| Time| __endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+|1970-01-01T08:00:00.000+08:00|1970-01-01T08:00:00.040+08:00| 24.5| 3| 50.0|
+|1970-01-01T08:00:00.050+08:00|1970-01-01T08:00:00.050+08:00| null| 1| 50.0|
+|1970-01-01T08:00:00.070+08:00|1970-01-01T08:00:00.090+08:00| 84.5| 3| 170.0|
+|1970-01-01T08:00:00.150+08:00|1970-01-01T08:00:00.150+08:00| 66.5| 1| 90.0|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
当指定ignoreNull为false时，会将s6为null的数据也考虑进来
```sql
@@ -648,17 +648,17 @@ select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(
```
得到如下的结果
```
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-| Time|__endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-|1970-01-01T08:00:00.000+08:00| 10| 4.5| 2| 10.0|
-|1970-01-01T08:00:00.020+08:00| 30| 29.5| 1| 30.0|
-|1970-01-01T08:00:00.040+08:00| 40| 44.5| 1| 40.0|
-|1970-01-01T08:00:00.050+08:00| 50| null| 1| 50.0|
-|1970-01-01T08:00:00.060+08:00| 60| 64.5| 1| 60.0|
-|1970-01-01T08:00:00.070+08:00| 90| 84.5| 3| 170.0|
-|1970-01-01T08:00:00.150+08:00| 150| 66.5| 1| 90.0|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| Time| __endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+|1970-01-01T08:00:00.000+08:00|1970-01-01T08:00:00.010+08:00| 4.5| 2| 10.0|
+|1970-01-01T08:00:00.020+08:00|1970-01-01T08:00:00.030+08:00| 29.5| 1| 30.0|
+|1970-01-01T08:00:00.040+08:00|1970-01-01T08:00:00.040+08:00| 44.5| 1| 40.0|
+|1970-01-01T08:00:00.050+08:00|1970-01-01T08:00:00.050+08:00| null| 1| 50.0|
+|1970-01-01T08:00:00.060+08:00|1970-01-01T08:00:00.060+08:00| 64.5| 1| 60.0|
+|1970-01-01T08:00:00.070+08:00|1970-01-01T08:00:00.090+08:00| 84.5| 3| 170.0|
+|1970-01-01T08:00:00.150+08:00|1970-01-01T08:00:00.150+08:00| 66.5| 1| 90.0|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
### delta!=0时的差值事件分段
使用如下sql语句
@@ -667,13 +667,13 @@ select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(
```
得到如下的查询结果
```
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-| Time|__endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-|1970-01-01T08:00:00.000+08:00| 50| 24.5| 4| 100.0|
-|1970-01-01T08:00:00.070+08:00| 90| 84.5| 3| 170.0|
-|1970-01-01T08:00:00.150+08:00| 150| 66.5| 1| 90.0|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| Time| __endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+|1970-01-01T08:00:00.000+08:00|1970-01-01T08:00:00.050+08:00| 24.5| 4| 100.0|
+|1970-01-01T08:00:00.070+08:00|1970-01-01T08:00:00.090+08:00| 84.5| 3| 170.0|
+|1970-01-01T08:00:00.150+08:00|1970-01-01T08:00:00.150+08:00| 66.5| 1| 90.0|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
group by子句中的controlExpression同样支持列的表达式
@@ -682,14 +682,14 @@ select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(
```
得到如下的查询结果
```
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-| Time|__endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
-|1970-01-01T08:00:00.000+08:00| 10| 4.5| 2| 10.0|
-|1970-01-01T08:00:00.040+08:00| 50| 44.5| 2| 90.0|
-|1970-01-01T08:00:00.070+08:00| 80| 79.5| 2| 80.0|
-|1970-01-01T08:00:00.090+08:00| 150| 80.5| 2| 180.0|
-+-----------------------------+---------+-----------------+-------------------+-----------------+
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| Time| __endTime|avg(root.sg.d.s1)|count(root.sg.d.s2)|sum(root.sg.d.s3)|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+|1970-01-01T08:00:00.000+08:00|1970-01-01T08:00:00.010+08:00| 4.5| 2| 10.0|
+|1970-01-01T08:00:00.040+08:00|1970-01-01T08:00:00.050+08:00| 44.5| 2| 90.0|
+|1970-01-01T08:00:00.070+08:00|1970-01-01T08:00:00.080+08:00| 79.5| 2| 80.0|
+|1970-01-01T08:00:00.090+08:00|1970-01-01T08:00:00.150+08:00| 80.5| 2| 180.0|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
## 条件分段聚合
当需要根据指定条件对数据进行筛选，并将连续的符合条件的行分为一组进行聚合运算时，可以使用`GROUP BY CONDITION`的分段方式；不满足给定条件的行因为不属于任何分组会被直接简单忽略。
@@ -735,30 +735,30 @@ keep表达式用来指定形成分组所需要连续满足`predict`条件的数
```
查询至少连续两行以上的charging_status=1的数据，sql语句如下:
```sql
-select __endTime,max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoringNull=true)
+select max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoreNull=true)
```
得到结果如下：
```
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
-| Time|__endTime|max_time(root.sg.beijing.car01.charging_status)|count(root.sg.beijing.car01.vehicle_status)|last_value(root.sg.beijing.car01.soc)|
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
-|1970-01-01T08:00:00.001+08:00| 2| 2| 2| 16.0|
-|1970-01-01T08:00:00.005+08:00| 10| 10| 5| 60.0|
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
+| Time|max_time(root.sg.beijing.car01.charging_status)|count(root.sg.beijing.car01.vehicle_status)|last_value(root.sg.beijing.car01.soc)|
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 2| 2| 16.0|
+|1970-01-01T08:00:00.005+08:00| 10| 5| 60.0|
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
```
当设置`ignoreNull`为false时，遇到null值为将其视为一个不满足条件的行，会结束正在计算的分组。
```sql
-select __endTime,max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoringNull=false)
+select max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoreNull=false)
```
得到如下结果，原先的分组被含null的行拆分:
```
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
-| Time|__endTime|max_time(root.sg.beijing.car01.charging_status)|count(root.sg.beijing.car01.vehicle_status)|last_value(root.sg.beijing.car01.soc)|
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
-|1970-01-01T08:00:00.001+08:00| 2| 2| 2| 16.0|
-|1970-01-01T08:00:00.005+08:00| 7| 7| 3| 36.0|
-|1970-01-01T08:00:00.009+08:00| 10| 10| 2| 60.0|
-+-----------------------------+---------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
+| Time|max_time(root.sg.beijing.car01.charging_status)|count(root.sg.beijing.car01.vehicle_status)|last_value(root.sg.beijing.car01.soc)|
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 2| 2| 16.0|
+|1970-01-01T08:00:00.005+08:00| 7| 3| 36.0|
+|1970-01-01T08:00:00.009+08:00| 10| 2| 60.0|
++-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
```
## 会话分段聚合
`GROUP BY SESSION`可以根据时间列的间隔进行分组，在结果集的时间列中，时间间隔小于等于设定阈值的数据会被分为一组。例如在工业场景中，设备并不总是连续运行，`GROUP BY SESSION`会将设备每次接入会话所产生的数据分为一组。
@@ -815,12 +815,12 @@ select __endTime,count(*) from root.** group by session(1d)
```
得到如下结果：
```
-+-----------------------------+---------+------------------------------------+---------------------------------+-------------------------------+
-| Time|__endTime|count(root.ln.wf02.wt01.temperature)|count(root.ln.wf02.wt01.hardware)|count(root.ln.wf02.wt01.status)|
-+-----------------------------+---------+------------------------------------+---------------------------------+-------------------------------+
-|1970-01-01T08:00:01.000+08:00| 480000| 15| 18| 15|
-|1970-01-02T08:08:01.000+08:00| 86885000| 5| 5| 5|
-+-----------------------------+---------+------------------------------------+---------------------------------+-------------------------------+
++-----------------------------+-----------------------------+------------------------------------+---------------------------------+-------------------------------+
+| Time| __endTime|count(root.ln.wf02.wt01.temperature)|count(root.ln.wf02.wt01.hardware)|count(root.ln.wf02.wt01.status)|
++-----------------------------+-----------------------------+------------------------------------+---------------------------------+-------------------------------+
+|1970-01-01T08:00:01.000+08:00|1970-01-01T08:08:00.000+08:00| 15| 18| 15|
+|1970-01-02T08:08:01.000+08:00|1970-01-02T08:08:05.000+08:00| 5| 5| 5|
++-----------------------------+-----------------------------+------------------------------------+---------------------------------+-------------------------------+
```
也可以和`HAVING`、`ALIGN BY DEVICE`共同使用
```sql
@@ -828,12 +828,12 @@ select __endTime,sum(hardware) from root.ln.wf02.wt01 group by session(50s) havi
```
得到如下结果，其中排除了`sum(hardware)`为0的部分
```
-+-----------------------------+-----------------+---------+-------------+
-| Time| Device|__endTime|sum(hardware)|
-+-----------------------------+-----------------+---------+-------------+
-|1970-01-01T08:00:01.000+08:00|root.ln.wf02.wt01| 200000| 2475.0|
-|1970-01-01T08:04:20.000+08:00|root.ln.wf02.wt01| 260000| 440.0|
-|1970-01-01T08:05:20.000+08:00|root.ln.wf02.wt01| 320000| 550.0|
-|1970-01-02T08:08:01.000+08:00|root.ln.wf02.wt01| 86885000| 1650.0|
-+-----------------------------+-----------------+---------+-------------+
++-----------------------------+-----------------+-----------------------------+-------------+
+| Time| Device| __endTime|sum(hardware)|
++-----------------------------+-----------------+-----------------------------+-------------+
+|1970-01-01T08:00:01.000+08:00|root.ln.wf02.wt01|1970-01-01T08:03:20.000+08:00| 2475.0|
+|1970-01-01T08:04:20.000+08:00|root.ln.wf02.wt01|1970-01-01T08:04:20.000+08:00| 440.0|
+|1970-01-01T08:05:20.000+08:00|root.ln.wf02.wt01|1970-01-01T08:05:20.000+08:00| 550.0|
+|1970-01-02T08:08:01.000+08:00|root.ln.wf02.wt01|1970-01-02T08:08:05.000+08:00| 1650.0|
++-----------------------------+-----------------+-----------------------------+-------------+
```
\ No newline at end of file