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/06/09 06:04:27 UTC
[iotdb] branch rel/1.2 updated: [IOTDB-5982] Modify the docs of GROUP BY
This is an automated email from the ASF dual-hosted git repository.
jackietien pushed a commit to branch rel/1.2
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/rel/1.2 by this push:
new cf957663539 [IOTDB-5982] Modify the docs of GROUP BY
cf957663539 is described below
commit cf957663539f196e471b82644e16c3469c6a6dc3
Author: YangCaiyin <yc...@gmail.com>
AuthorDate: Fri Jun 9 14:04:20 2023 +0800
[IOTDB-5982] Modify the docs of GROUP BY
---
docs/UserGuide/Query-Data/Group-By.md | 1204 +++++++++++++++---------------
docs/UserGuide/Query-Data/Overview.md | 2 +-
docs/zh/UserGuide/Query-Data/Group-By.md | 880 +++++++++++-----------
3 files changed, 1050 insertions(+), 1036 deletions(-)
diff --git a/docs/UserGuide/Query-Data/Group-By.md b/docs/UserGuide/Query-Data/Group-By.md
index eb90e5871b4..bf4fac2739e 100644
--- a/docs/UserGuide/Query-Data/Group-By.md
+++ b/docs/UserGuide/Query-Data/Group-By.md
@@ -21,110 +21,20 @@
# Group By Aggregate
-## Aggregation By Level
+IoTDB supports using `GROUP BY` clause to aggregate the time series by segment and group.
-Aggregation by level statement is used to group the query result whose name is the same at the given level.
-
-- Keyword `LEVEL` is used to specify the level that need to be grouped. By convention, `level=0` represents *root* level.
-- All aggregation functions are supported. When using five aggregations: sum, avg, min_value, max_value and extreme, please make sure all the aggregated series have exactly the same data type. Otherwise, it will generate a syntax error.
-
-**Example 1:** there are multiple series named `status` under different databases， like "root.ln.wf01.wt01.status", "root.ln.wf02.wt02.status", and "root.sgcc.wf03.wt01.status". If you need to count the number of data points of the `status` sequence under different databases, use the following query:
-
-```sql
-select count(status) from root.** group by level = 1
-```
-
-Result：
-
-```
-+-------------------------+---------------------------+
-|count(root.ln.*.*.status)|count(root.sgcc.*.*.status)|
-+-------------------------+---------------------------+
-| 20160| 10080|
-+-------------------------+---------------------------+
-Total line number = 1
-It costs 0.003s
-```
-
-**Example 2:** If you need to count the number of data points under different devices, you can specify level = 3,
-
-```sql
-select count(status) from root.** group by level = 3
-```
-
-Result：
-
-```
-+---------------------------+---------------------------+
-|count(root.*.*.wt01.status)|count(root.*.*.wt02.status)|
-+---------------------------+---------------------------+
-| 20160| 10080|
-+---------------------------+---------------------------+
-Total line number = 1
-It costs 0.003s
-```
-
-**Example 3:** Attention，the devices named `wt01` under databases `ln` and `sgcc` are grouped together, since they are regarded as devices with the same name. If you need to further count the number of data points in different devices under different databases, you can use the following query:
-
-```sql
-select count(status) from root.** group by level = 1, 3
-```
-
-Result：
-
-```
-+----------------------------+----------------------------+------------------------------+
-|count(root.ln.*.wt01.status)|count(root.ln.*.wt02.status)|count(root.sgcc.*.wt01.status)|
-+----------------------------+----------------------------+------------------------------+
-| 10080| 10080| 10080|
-+----------------------------+----------------------------+------------------------------+
-Total line number = 1
-It costs 0.003s
-```
-
-**Example 4:** Assuming that you want to query the maximum value of temperature sensor under all time series, you can use the following query statement:
-
-```sql
-select max_value(temperature) from root.** group by level = 0
-```
+Segmented aggregation refers to segmenting data in the row direction according to the time dimension, aiming at the time relationship between different data points in the same time series, and obtaining an aggregated value for each segment. Currently only **group by time**、**group by variation**、**group by condition**、**group by session** and **group by count** is supported, and more segmentation methods will be supported in the future.
-Result：
-
-```
-+---------------------------------+
-|max_value(root.*.*.*.temperature)|
-+---------------------------------+
-| 26.0|
-+---------------------------------+
-Total line number = 1
-It costs 0.013s
-```
-
-**Example 5:** The above queries are for a certain sensor. In particular, **if you want to query the total data points owned by all sensors at a certain level**, you need to explicitly specify `*` is selected.
-
-```sql
-select count(*) from root.ln.** group by level = 2
-```
-
-Result：
-
-```
-+----------------------+----------------------+
-|count(root.*.wf01.*.*)|count(root.*.wf02.*.*)|
-+----------------------+----------------------+
-| 20160| 20160|
-+----------------------+----------------------+
-Total line number = 1
-It costs 0.013s
-```
+Group aggregation refers to grouping the potential business attributes of time series for different time series. Each group contains several time series, and each group gets an aggregated value. Support **group by path level** and **group by tag** two grouping methods.
-## Downsampling Aggregate Query
+## Aggregate By Segment
+### Aggregate By Time
-Segmentation aggregation is a typical query method for time series data. Data is collected at high frequency and needs to be aggregated and calculated at certain time intervals. For example, to calculate the daily average temperature, the sequence of temperature needs to be segmented by day, and then calculated. average value.
+Aggregate by time is a typical query method for time series data. Data is collected at high frequency and needs to be aggregated and calculated at certain time intervals. For example, to calculate the daily average temperature, the sequence of temperature needs to be segmented by day, and then calculated. average value.
-Downsampling query refers to a query method that uses a lower frequency than the time frequency of data collection, and is a special case of segmented aggregation. For example, the frequency of data collection is one second. If you want to display the data in one minute, you need to use downsampling query.
+Aggregate by time refers to a query method that uses a lower frequency than the time frequency of data collection, and is a special case of segmented aggregation. For example, the frequency of data collection is one second. If you want to display the data in one minute, you need to use time aggregagtion.
-This section mainly introduces the related examples of downsampling aggregation query, using the `GROUP BY` clause. IoTDB supports partitioning result sets according to time interval and customized sliding step. And by default results are sorted by time in ascending order.
+This section mainly introduces the related examples of time aggregation, using the `GROUP BY` clause. IoTDB supports partitioning result sets according to time interval and customized sliding step. And by default results are sorted by time in ascending order.
The GROUP BY statement provides users with three types of specified parameters:
@@ -140,7 +50,7 @@ Among them, the parameter 3 is optional.
There are three typical examples of frequency reduction aggregation:
-### Downsampling Aggregate Query without Specifying the Sliding Step Length
+#### Aggregate By Time without Specifying the Sliding Step Length
The SQL statement is:
@@ -149,13 +59,13 @@ select count(status), max_value(temperature) from root.ln.wf01.wt01 group by ([2
```
which means:
-Since the sliding step length is not specified, the GROUP BY statement by default set the sliding step the same as the time interval which is `1d`.
+Since the sliding step length is not specified, the `GROUP BY` statement by default set the sliding step the same as the time interval which is `1d`.
-The fist parameter of the GROUP BY statement above is the display window parameter, which determines the final display range is [2017-11-01T00:00:00, 2017-11-07T23:00:00).
+The fist parameter of the `GROUP BY` statement above is the display window parameter, which determines the final display range is [2017-11-01T00:00:00, 2017-11-07T23:00:00).
-The second parameter of the GROUP BY statement above is the time interval for dividing the time axis. Taking this parameter (1d) as time interval and startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [0,1d), [1d, 2d), [2d, 3d), etc.
+The second parameter of the `GROUP BY` statement above is the time interval for dividing the time axis. Taking this parameter (1d) as time interval and startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [0,1d), [1d, 2d), [2d, 3d), etc.
-Then the system will use the time and value filtering condition in the WHERE clause and the first parameter of the GROUP BY statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of [2017-11-01T00:00:00, 2017-11-07 T23:00:00]), and map these data to the previously segmented time axis (in this case there are mapped data in every 1-day period from 2017-11-01T00:00:00 to 2017-11-07T23:00:00:00).
+Then the system will use the time and value filtering condition in the `WHERE` clause and the first parameter of the `GROUP BY` statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of [2017-11-01T00:00:00, 2017-11-07 T23:00:00]), and map these data to the previously segmented time axis (in this case there are mapped data in every 1-day period from 2017-11-01T00:00:00 to 2017-11-07T23:00:00:00).
Since there is data for each time period in the result range to be displayed, the execution result of the SQL statement is shown below:
@@ -175,7 +85,7 @@ Total line number = 7
It costs 0.024s
```
-### Downsampling Aggregate Query Specifying the Sliding Step Length
+#### Aggregate By Time Specifying the Sliding Step Length
The SQL statement is:
@@ -185,17 +95,17 @@ select count(status), max_value(temperature) from root.ln.wf01.wt01 group by ([2
which means:
-Since the user specifies the sliding step parameter as 1d, the GROUP BY statement will move the time interval `1 day` long instead of `3 hours` as default.
+Since the user specifies the sliding step parameter as 1d, the `GROUP BY` statement will move the time interval `1 day` long instead of `3 hours` as default.
That means we want to fetch all the data of 00:00:00 to 02:59:59 every day from 2017-11-01 to 2017-11-07.
-The first parameter of the GROUP BY statement above is the display window parameter, which determines the final display range is [2017-11-01T00:00:00, 2017-11-07T23:00:00).
+The first parameter of the `GROUP BY` statement above is the display window parameter, which determines the final display range is [2017-11-01T00:00:00, 2017-11-07T23:00:00).
-The second parameter of the GROUP BY statement above is the time interval for dividing the time axis. Taking this parameter (3h) as time interval and the startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [2017-11-01T00:00:00, 2017-11-01T03:00:00), [2017-11-02T00:00:00, 2017-11-02T03:00:00), [2017-11-03T00:00:00, 2017-11-03T03:00:00), etc.
+The second parameter of the `GROUP BY` statement above is the time interval for dividing the time axis. Taking this parameter (3h) as time interval and the startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [2017-11-01T00:00:00, 2017-11-01T03:00:00), [2017-11-02T00:00:00, 2017-11-02T03:00:00), [2017-11-03T00:00:00, 2017-11-03T03:00:00), etc.
-The third parameter of the GROUP BY statement above is the sliding step for each time interval moving.
+The third parameter of the `GROUP BY` statement above is the sliding step for each time interval moving.
-Then the system will use the time and value filtering condition in the WHERE clause and the first parameter of the GROUP BY statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of [2017-11-01T00:00:00, 2017-11-07T23:00:00]), and map these data to the previously segmented time axis (in this case there are mapped data in every 3-hour period for each day from 2017-11-01T00:00:00 to 2017-11-07T23:00:00:00).
+Then the system will use the time and value filtering condition in the `WHERE` clause and the first parameter of the `GROUP BY` statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of [2017-11-01T00:00:00, 2017-11-07T23:00:00]), and map these data to the previously segmented time axis (in this case there are mapped data in every 3-hour period for each day from 2017-11-01T00:00:00 to 2017-11-07T23:00:00:00).
Since there is data for each time period in the result range to be displayed, the execution result of the SQL statement is shown below:
@@ -239,7 +149,7 @@ Total line number = 5
It costs 0.006s
```
-### Downsampling Aggregate Query by Natural Month
+#### Aggregate by Natural Month
The SQL statement is:
@@ -249,17 +159,17 @@ select count(status) from root.ln.wf01.wt01 group by([2017-11-01T00:00:00, 2019-
which means:
-Since the user specifies the sliding step parameter as `2mo`, the GROUP BY statement will move the time interval `2 months` long instead of `1 month` as default.
+Since the user specifies the sliding step parameter as `2mo`, the `GROUP BY` statement will move the time interval `2 months` long instead of `1 month` as default.
-The first parameter of the GROUP BY statement above is the display window parameter, which determines the final display range is [2017-11-01T00:00:00, 2019-11-07T23:00:00).
+The first parameter of the `GROUP BY` statement above is the display window parameter, which determines the final display range is [2017-11-01T00:00:00, 2019-11-07T23:00:00).
The start time is 2017-11-01T00:00:00. The sliding step will increment monthly based on the start date, and the 1st day of the month will be used as the time interval's start time.
-The second parameter of the GROUP BY statement above is the time interval for dividing the time axis. Taking this parameter (1mo) as time interval and the startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [2017-11-01T00:00:00, 2017-12-01T00:00:00), [2018-02-01T00:00:00, 2018-03-01T00:00:00), [2018-05-03T00:00:00, 2018-06-01T00:00:00)), etc.
+The second parameter of the `GROUP BY` statement above is the time interval for dividing the time axis. Taking this parameter (1mo) as time interval and the startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [2017-11-01T00:00:00, 2017-12-01T00:00:00), [2018-02-01T00:00:00, 2018-03-01T00:00:00), [2018-05-03T00:00:00, 2018-06-01T00:00:00)), etc.
-The third parameter of the GROUP BY statement above is the sliding step for each time interval moving.
+The third parameter of the `GROUP BY` statement above is the sliding step for each time interval moving.
-Then the system will use the time and value filtering condition in the WHERE clause and the first parameter of the GROUP BY statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of (2017-11-01T00:00:00, 2019-11-07T23:00:00], and map these data to the previously segmented time axis (in this case there are mapped data of the first month in every two month period from 2017-11-01T00:00:00 to 2019-11-07T23:00:00).
+Then the system will use the time and value filtering condition in the `WHERE` clause and the first parameter of the `GROUP BY` statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of (2017-11-01T00:00:00, 2019-11-07T23:00:00], and map these data to the previously segmented time axis (in this case there are mapped data of the first month in every two month period from 2017-11-01T00:00:00 to 2019-11-07 [...]
The SQL execution result is:
@@ -291,19 +201,19 @@ select count(status) from root.ln.wf01.wt01 group by([2017-10-31T00:00:00, 2019-
which means:
-Since the user specifies the sliding step parameter as `2mo`, the GROUP BY statement will move the time interval `2 months` long instead of `1 month` as default.
+Since the user specifies the sliding step parameter as `2mo`, the `GROUP BY` statement will move the time interval `2 months` long instead of `1 month` as default.
-The first parameter of the GROUP BY statement above is the display window parameter, which determines the final display range is [2017-10-31T00:00:00, 2019-11-07T23:00:00).
+The first parameter of the `GROUP BY` statement above is the display window parameter, which determines the final display range is [2017-10-31T00:00:00, 2019-11-07T23:00:00).
Different from the previous example, the start time is set to 2017-10-31T00:00:00. The sliding step will increment monthly based on the start date, and the 31st day of the month meaning the last day of the month will be used as the time interval's start time. If the start time is set to the 30th date, the sliding step will use the 30th or the last day of the month.
The start time is 2017-10-31T00:00:00. The sliding step will increment monthly based on the start time, and the 1st day of the month will be used as the time interval's start time.
-The second parameter of the GROUP BY statement above is the time interval for dividing the time axis. Taking this parameter (1mo) as time interval and the startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [2017-10-31T00:00:00, 2017-11-31T00:00:00), [2018-02-31T00:00:00, 2018-03-31T00:00:00), [2018-05-31T00:00:00, 2018-06-31T00:00:00), etc.
+The second parameter of the `GROUP BY` statement above is the time interval for dividing the time axis. Taking this parameter (1mo) as time interval and the startTime of the display window as the dividing origin, the time axis is divided into several continuous intervals, which are [2017-10-31T00:00:00, 2017-11-31T00:00:00), [2018-02-31T00:00:00, 2018-03-31T00:00:00), [2018-05-31T00:00:00, 2018-06-31T00:00:00), etc.
-The third parameter of the GROUP BY statement above is the sliding step for each time interval moving.
+The third parameter of the `GROUP BY` statement above is the sliding step for each time interval moving.
-Then the system will use the time and value filtering condition in the WHERE clause and the first parameter of the GROUP BY statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of [2017-10-31T00:00:00, 2019-11-07T23:00:00) and map these data to the previously segmented time axis (in this case there are mapped data of the first month in every two month period from 2017-10-31T00:00:00 to 2019-11-07T23:00:00).
+Then the system will use the time and value filtering condition in the `WHERE` clause and the first parameter of the `GROUP BY` statement as the data filtering condition to obtain the data satisfying the filtering condition (which in this case is the data in the range of [2017-10-31T00:00:00, 2019-11-07T23:00:00) and map these data to the previously segmented time axis (in this case there are mapped data of the first month in every two month period from 2017-10-31T00:00:00 to 2019-11-07T [...]
The SQL execution result is:
@@ -327,7 +237,7 @@ The SQL execution result is:
+-----------------------------+-------------------------------+
```
-### Left Open And Right Close Range
+#### Left Open And Right Close Range
The SQL statement is:
@@ -354,79 +264,514 @@ We will get the result like following:
Total line number = 7
It costs 0.004s
```
+### Aggregation By Variation
+IoTDB supports grouping by continuous stable values through the `GROUP BY VARIATION` statement.
+
+Group-By-Variation wil set the first point in group as the base point,
+then if the difference between the new data and base point is small than or equal to delta,
+the data point will be grouped together and execute aggregation query (The calculation of difference and the meaning of delte are introduced below). The groups won't overlap and there is no fixed start time and end time.
+The syntax of clause is as follows:
+```sql
+group by variation(controlExpression[,delta][,ignoreNull=true/false])
+```
+The different parameters mean:
+* controlExpression
-## Downsampling Aggregate Query with Level Clause
+The value that is used to calculate difference. It can be any columns or the expression of them.
+* delta
-Level could be defined to show count the number of points of each node at the given level in current Metadata Tree.
+The threshold that is used when grouping. The difference of controlExpression between the first data point and new data point should less than or equal to delta.
+When delta is zero, all the continuous data with equal expression value will be grouped into the same group.
+* ignoreNull
-This could be used to query the number of points under each device.
+Used to specify how to deal with the data when the value of controlExpression is null. When ignoreNull is false, null will be treated as a new value and when ignoreNull is true, the data point will be directly skipped.
-The SQL statement is:
+The supported return types of controlExpression and how to deal with null value when ignoreNull is false are shown in the following table:
+
+| delta | Return Type Supported By controlExpression | The Handling of null when ignoreNull is False |
+|----------|--------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
+| delta!=0 | INT32、INT64、FLOAT、DOUBLE | If the processing group doesn't contains null, null value should be treated as infinity/infinitesimal and will end current group.<br/>Continuous null values are treated as stable values and assigned to the same group. |
+| delta=0 | TEXT、BINARY、INT32、INT64、FLOAT、DOUBLE | Null is treated as a new value in a new group and continuous nulls belong to the same group. |
+
+<img style="width:100%; max-width:800px; max-height:600px; margin-left:auto; margin-right:auto; display:block;" src="https://alioss.timecho.com/docs/img/UserGuide/Process-Data/GroupBy/groupByVariation.jpeg" alt="groupByVariation">
-Get downsampling aggregate query by level.
+#### Precautions for Use
+1. The result of controlExpression should be a unique value. If multiple columns appear after using wildcard stitching, an error will be reported.
+2. For a group in resultSet, the time column output the start time of the group by default. __endTime can be used in select clause to output the endTime of groups in resultSet.
+3. Each device is grouped separately when used with `ALIGN BY DEVICE`.
+4. Delta is zero and ignoreNull is true by default.
+5. Currently `GROUP BY VARIATION` is not supported with `GROUP BY LEVEL`.
+Using the raw data below, several examples of `GROUP BY VARIAITON` queries will be given.
+```
++-----------------------------+-------+-------+-------+--------+-------+-------+
+| Time| s1| s2| s3| s4| s5| s6|
++-----------------------------+-------+-------+-------+--------+-------+-------+
+|1970-01-01T08:00:00.000+08:00| 4.5| 9.0| 0.0| 45.0| 9.0| 8.25|
+|1970-01-01T08:00:00.010+08:00| null| 19.0| 10.0| 145.0| 19.0| 8.25|
+|1970-01-01T08:00:00.020+08:00| 24.5| 29.0| null| 245.0| 29.0| null|
+|1970-01-01T08:00:00.030+08:00| 34.5| null| 30.0| 345.0| null| null|
+|1970-01-01T08:00:00.040+08:00| 44.5| 49.0| 40.0| 445.0| 49.0| 8.25|
+|1970-01-01T08:00:00.050+08:00| null| 59.0| 50.0| 545.0| 59.0| 6.25|
+|1970-01-01T08:00:00.060+08:00| 64.5| 69.0| 60.0| 645.0| 69.0| null|
+|1970-01-01T08:00:00.070+08:00| 74.5| 79.0| null| null| 79.0| 3.25|
+|1970-01-01T08:00:00.080+08:00| 84.5| 89.0| 80.0| 845.0| 89.0| 3.25|
+|1970-01-01T08:00:00.090+08:00| 94.5| 99.0| 90.0| 945.0| 99.0| 3.25|
+|1970-01-01T08:00:00.150+08:00| 66.5| 77.0| 90.0| 945.0| 99.0| 9.25|
++-----------------------------+-------+-------+-------+--------+-------+-------+
+```
+#### delta = 0
+The sql is shown below:
```sql
-select count(status) from root.ln.wf01.wt01 group by ((2017-11-01T00:00:00, 2017-11-07T23:00:00],1d), level=1;
+select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6)
```
-Result:
-
+Get the result below which ignores the row with null value in `s6`.
```
-+-----------------------------+-------------------------+
-| Time|COUNT(root.ln.*.*.status)|
-+-----------------------------+-------------------------+
-|2017-11-02T00:00:00.000+08:00| 1440|
-|2017-11-03T00:00:00.000+08:00| 1440|
-|2017-11-04T00:00:00.000+08:00| 1440|
-|2017-11-05T00:00:00.000+08:00| 1440|
-|2017-11-06T00:00:00.000+08:00| 1440|
-|2017-11-07T00:00:00.000+08:00| 1440|
-|2017-11-07T23:00:00.000+08:00| 1380|
-+-----------------------------+-------------------------+
-Total line number = 7
-It costs 0.006s
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| 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|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
-
-Downsampling aggregate query with sliding step and by level.
-
+when ignoreNull is false, the row with null value in `s6` will be considered.
```sql
-select count(status) from root.ln.wf01.wt01 group by ([2017-11-01 00:00:00, 2017-11-07 23:00:00), 3h, 1d), level=1;
+select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, ignoreNull=false)
```
-
-Result:
-
+Get the following result.
```
-+-----------------------------+-------------------------+
-| Time|COUNT(root.ln.*.*.status)|
-+-----------------------------+-------------------------+
-|2017-11-01T00:00:00.000+08:00| 180|
-|2017-11-02T00:00:00.000+08:00| 180|
-|2017-11-03T00:00:00.000+08:00| 180|
-|2017-11-04T00:00:00.000+08:00| 180|
-|2017-11-05T00:00:00.000+08:00| 180|
-|2017-11-06T00:00:00.000+08:00| 180|
-|2017-11-07T00:00:00.000+08:00| 180|
-+-----------------------------+-------------------------+
-Total line number = 7
-It costs 0.004s
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| 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
-## Aggregation By Tags
+The sql is shown below:
+```sql
+select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, 4)
+```
+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|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:
-IotDB allows you to do aggregation query with the tags defined in timeseries through `GROUP BY TAGS` clause as well.
+```sql
+select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6+s5, 10)
+```
+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|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|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+```
-Firstly, we can put these example data into IoTDB, which will be used in the following feature introduction.
+### Aggregation By Condition
+When you need to filter the data according to a specific condition and group the continuous ones for an aggregation query.
+`GROUP BY CONDITION` is suitable for you.The rows which don't meet the given condition will be simply ignored because they don't belong to any group.
+Its syntax is defined below:
+```sql
+group by condition(predict,[keep>/>=/=/<=/<]threshold,[,ignoreNull=true/false])
+```
+* predict
-These are the temperature data of the workshops, which belongs to the factory `factory1` and locates in different cities. The time range is `[1000, 10000)`.
+Any legal expression return the type of boolean for filtering in grouping.
+* [keep>/>=/=/<=/<]threshold
-The device node of the timeseries path is the ID of the device. The information of city and workshop are modelled in the tags `city` and `workshop`.
-The devices `d1` and `d2` belong to the workshop `d1` in `Beijing`.
-`d3` and `d4` belong to the workshop `w2` in `Beijing`.
-`d5` and `d6` belong to the workshop `w1` in `Shanghai`.
-`d7` belongs to the workshop `w2` in `Shanghai`.
-`d8` and `d9` are under maintenance, and don't belong to any workshops, so they have no tags.
+Keep expression is used to specify the number of continuous rows that meet the `predict` condition to form a group. Only the number of rows in group satisfy the keep condition, the result of group will be output.
+Keep expression consists of a 'keep' string and a threshold of type `long` or a single 'long' type data.
+* ignoreNull=true/false
+Used to specify how to handle data rows that encounter null predict, skip the row when it's true and end current group when it's false.
-```SQL
+#### Precautions for Use
+1. keep condition is required in the query, but you can omit the 'keep' string and given a `long` number which defaults to 'keep=long number' condition.
+2. IgnoreNull defaults to true.
+3. For a group in resultSet, the time column output the start time of the group by default. __endTime can be used in select clause to output the endTime of groups in resultSet.
+4. Each device is grouped separately when used with `ALIGN BY DEVICE`.
+5. Currently `GROUP BY CONDITION` is not supported with `GROUP BY LEVEL`.
+
+For the following raw data, several query examples are given below:
+```
++-----------------------------+-------------------------+-------------------------------------+------------------------------------+
+| Time|root.sg.beijing.car01.soc|root.sg.beijing.car01.charging_status|root.sg.beijing.car01.vehicle_status|
++-----------------------------+-------------------------+-------------------------------------+------------------------------------+
+|1970-01-01T08:00:00.001+08:00| 14.0| 1| 1|
+|1970-01-01T08:00:00.002+08:00| 16.0| 1| 1|
+|1970-01-01T08:00:00.003+08:00| 16.0| 0| 1|
+|1970-01-01T08:00:00.004+08:00| 16.0| 0| 1|
+|1970-01-01T08:00:00.005+08:00| 18.0| 1| 1|
+|1970-01-01T08:00:00.006+08:00| 24.0| 1| 1|
+|1970-01-01T08:00:00.007+08:00| 36.0| 1| 1|
+|1970-01-01T08:00:00.008+08:00| 36.0| null| 1|
+|1970-01-01T08:00:00.009+08:00| 45.0| 1| 1|
+|1970-01-01T08:00:00.010+08:00| 60.0| 1| 1|
++-----------------------------+-------------------------+-------------------------------------+------------------------------------+
+```
+The sql statement to query data with at least two continuous row shown below:
+```sql
+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|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 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|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
+`GROUP BY SESSION` can be used to group data according to the interval of the time. Data with a time interval less than or equal to the given threshold will be assigned to the same group.
+For example, in industrial scenarios, devices don't always run continuously, `GROUP BY SESSION` will group the data generated by each access session of the device.
+Its syntax is defined as follows:
+```sql
+group by session(timeInterval)
+```
+* timeInterval
+
+A given interval threshold to create a new group of data when the difference between the time of data is greater than the threshold.
+
+The figure below is a grouping diagram under `GROUP BY SESSION`.
+
+<img style="width:100%; max-width:800px; max-height:600px; margin-left:auto; margin-right:auto; display:block;" src="https://alioss.timecho.com/docs/img/UserGuide/Process-Data/GroupBy/groupBySession.jpeg" alt="groupBySession">
+
+#### Precautions for Use
+1. For a group in resultSet, the time column output the start time of the group by default. __endTime can be used in select clause to output the endTime of groups in resultSet.
+2. Each device is grouped separately when used with `ALIGN BY DEVICE`.
+3. Currently `GROUP BY SESSION` is not supported with `GROUP BY LEVEL`.
+
+For the raw data below, a few query examples are given:
+```
++-----------------------------+-----------------+-----------+--------+------+
+| Time| Device|temperature|hardware|status|
++-----------------------------+-----------------+-----------+--------+------+
+|1970-01-01T08:00:01.000+08:00|root.ln.wf02.wt01| 35.7| 11| false|
+|1970-01-01T08:00:02.000+08:00|root.ln.wf02.wt01| 35.8| 22| true|
+|1970-01-01T08:00:03.000+08:00|root.ln.wf02.wt01| 35.4| 33| false|
+|1970-01-01T08:00:04.000+08:00|root.ln.wf02.wt01| 36.4| 44| false|
+|1970-01-01T08:00:05.000+08:00|root.ln.wf02.wt01| 36.8| 55| false|
+|1970-01-01T08:00:10.000+08:00|root.ln.wf02.wt01| 36.8| 110| false|
+|1970-01-01T08:00:20.000+08:00|root.ln.wf02.wt01| 37.8| 220| true|
+|1970-01-01T08:00:30.000+08:00|root.ln.wf02.wt01| 37.5| 330| false|
+|1970-01-01T08:00:40.000+08:00|root.ln.wf02.wt01| 37.4| 440| false|
+|1970-01-01T08:00:50.000+08:00|root.ln.wf02.wt01| 37.9| 550| false|
+|1970-01-01T08:01:40.000+08:00|root.ln.wf02.wt01| 38.0| 110| false|
+|1970-01-01T08:02:30.000+08:00|root.ln.wf02.wt01| 38.8| 220| true|
+|1970-01-01T08:03:20.000+08:00|root.ln.wf02.wt01| 38.6| 330| false|
+|1970-01-01T08:04:20.000+08:00|root.ln.wf02.wt01| 38.4| 440| false|
+|1970-01-01T08:05:20.000+08:00|root.ln.wf02.wt01| 38.3| 550| false|
+|1970-01-01T08:06:40.000+08:00|root.ln.wf02.wt01| null| 0| null|
+|1970-01-01T08:07:50.000+08:00|root.ln.wf02.wt01| null| 0| null|
+|1970-01-01T08:08:00.000+08:00|root.ln.wf02.wt01| null| 0| null|
+|1970-01-02T08:08:01.000+08:00|root.ln.wf02.wt01| 38.2| 110| false|
+|1970-01-02T08:08:02.000+08:00|root.ln.wf02.wt01| 37.5| 220| true|
+|1970-01-02T08:08:03.000+08:00|root.ln.wf02.wt01| 37.4| 330| false|
+|1970-01-02T08:08:04.000+08:00|root.ln.wf02.wt01| 36.8| 440| false|
+|1970-01-02T08:08:05.000+08:00|root.ln.wf02.wt01| 37.4| 550| false|
++-----------------------------+-----------------+-----------+--------+------+
+```
+TimeInterval can be set by different time units, the sql is shown below:
+```sql
+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|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
+select __endTime,sum(hardware) from root.ln.wf02.wt01 group by session(50s) having sum(hardware)>0 align by device
+```
+Get the result below:
+```
++-----------------------------+-----------------+-----------------------------+-------------+
+| 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|
++-----------------------------+-----------------+-----------------------------+-------------+
+```
+### Aggregation By Count
+`GROUP BY COUNT`can aggregate the data points according to the number of points. It can group fixed number of continuous data points together for aggregation query.
+Its syntax is defined as follows:
+```sql
+group by count(controlExpression, size[,ignoreNull=true/false])
+```
+
+* controlExpression
+
+The object to count during processing, it can be any column or an expression of columns.
+
+* size
+
+The number of data points in a group, a number of `size` continuous points will be divided to the same group.
+
+* ignoreNull=true/false
+
+Whether to ignore the data points with null in `controlExpression`, when ignoreNull is true, data points with the `controlExpression` of null will be skipped during counting.
+
+#### Precautions for Use
+1. For a group in resultSet, the time column output the start time of the group by default. __endTime can be used in select clause to output the endTime of groups in resultSet.
+2. Each device is grouped separately when used with `ALIGN BY DEVICE`.
+3. Currently `GROUP BY SESSION` is not supported with `GROUP BY LEVEL`.
+4. When the final number of data points in a group is less than `size`, the result of the group will not be output.
+
+For the data below, some examples will be given.
+```
++-----------------------------+-----------+-----------------------+
+| Time|root.sg.soc|root.sg.charging_status|
++-----------------------------+-----------+-----------------------+
+|1970-01-01T08:00:00.001+08:00| 14.0| 1|
+|1970-01-01T08:00:00.002+08:00| 16.0| 1|
+|1970-01-01T08:00:00.003+08:00| 16.0| 0|
+|1970-01-01T08:00:00.004+08:00| 16.0| 0|
+|1970-01-01T08:00:00.005+08:00| 18.0| 1|
+|1970-01-01T08:00:00.006+08:00| 24.0| 1|
+|1970-01-01T08:00:00.007+08:00| 36.0| 1|
+|1970-01-01T08:00:00.008+08:00| 36.0| null|
+|1970-01-01T08:00:00.009+08:00| 45.0| 1|
+|1970-01-01T08:00:00.010+08:00| 60.0| 1|
++-----------------------------+-----------+-----------------------+
+```
+The sql is shown below
+```sql
+select count(charging_stauts), first_value(soc) from root.sg group by count(charging_status,5)
+```
+Get the result below, in the second group from 1970-01-01T08:00:00.006+08:00 to 1970-01-01T08:00:00.010+08:00. There are only four points included which is less than `size`. So it won't be output.
+```
++-----------------------------+-----------------------------+--------------------------------------+
+| Time| __endTime|first_value(root.sg.beijing.car01.soc)|
++-----------------------------+-----------------------------+--------------------------------------+
+|1970-01-01T08:00:00.001+08:00|1970-01-01T08:00:00.005+08:00| 14.0|
++-----------------------------+-----------------------------+--------------------------------------+
+```
+When `ignoreNull=false` is used to take null value into account. There will be two groups with 5 points in the resultSet, which is shown as follows:
+```sql
+select count(charging_stauts), first_value(soc) from root.sg group by count(charging_status,5,ignoreNull=false)
+```
+Get the results:
+```
++-----------------------------+-----------------------------+--------------------------------------+
+| Time| __endTime|first_value(root.sg.beijing.car01.soc)|
++-----------------------------+-----------------------------+--------------------------------------+
+|1970-01-01T08:00:00.001+08:00|1970-01-01T08:00:00.005+08:00| 14.0|
+|1970-01-01T08:00:00.006+08:00|1970-01-01T08:00:00.010+08:00| 24.0|
++-----------------------------+-----------------------------+--------------------------------------+
+```
+## Aggregate By Group
+
+### Aggregation By Level
+
+Aggregation by level statement is used to group the query result whose name is the same at the given level.
+
+- Keyword `LEVEL` is used to specify the level that need to be grouped. By convention, `level=0` represents *root* level.
+- All aggregation functions are supported. When using five aggregations: sum, avg, min_value, max_value and extreme, please make sure all the aggregated series have exactly the same data type. Otherwise, it will generate a syntax error.
+
+**Example 1:** there are multiple series named `status` under different databases， like "root.ln.wf01.wt01.status", "root.ln.wf02.wt02.status", and "root.sgcc.wf03.wt01.status". If you need to count the number of data points of the `status` sequence under different databases, use the following query:
+
+```sql
+select count(status) from root.** group by level = 1
+```
+
+Result：
+
+```
++-------------------------+---------------------------+
+|count(root.ln.*.*.status)|count(root.sgcc.*.*.status)|
++-------------------------+---------------------------+
+| 20160| 10080|
++-------------------------+---------------------------+
+Total line number = 1
+It costs 0.003s
+```
+
+**Example 2:** If you need to count the number of data points under different devices, you can specify level = 3,
+
+```sql
+select count(status) from root.** group by level = 3
+```
+
+Result：
+
+```
++---------------------------+---------------------------+
+|count(root.*.*.wt01.status)|count(root.*.*.wt02.status)|
++---------------------------+---------------------------+
+| 20160| 10080|
++---------------------------+---------------------------+
+Total line number = 1
+It costs 0.003s
+```
+
+**Example 3:** Attention，the devices named `wt01` under databases `ln` and `sgcc` are grouped together, since they are regarded as devices with the same name. If you need to further count the number of data points in different devices under different databases, you can use the following query:
+
+```sql
+select count(status) from root.** group by level = 1, 3
+```
+
+Result：
+
+```
++----------------------------+----------------------------+------------------------------+
+|count(root.ln.*.wt01.status)|count(root.ln.*.wt02.status)|count(root.sgcc.*.wt01.status)|
++----------------------------+----------------------------+------------------------------+
+| 10080| 10080| 10080|
++----------------------------+----------------------------+------------------------------+
+Total line number = 1
+It costs 0.003s
+```
+
+**Example 4:** Assuming that you want to query the maximum value of temperature sensor under all time series, you can use the following query statement:
+
+```sql
+select max_value(temperature) from root.** group by level = 0
+```
+
+Result：
+
+```
++---------------------------------+
+|max_value(root.*.*.*.temperature)|
++---------------------------------+
+| 26.0|
++---------------------------------+
+Total line number = 1
+It costs 0.013s
+```
+
+**Example 5:** The above queries are for a certain sensor. In particular, **if you want to query the total data points owned by all sensors at a certain level**, you need to explicitly specify `*` is selected.
+
+```sql
+select count(*) from root.ln.** group by level = 2
+```
+
+Result：
+
+```
++----------------------+----------------------+
+|count(root.*.wf01.*.*)|count(root.*.wf02.*.*)|
++----------------------+----------------------+
+| 20160| 20160|
++----------------------+----------------------+
+Total line number = 1
+It costs 0.013s
+```
+#### Aggregate By Time with Level Clause
+
+Level could be defined to show count the number of points of each node at the given level in current Metadata Tree.
+
+This could be used to query the number of points under each device.
+
+The SQL statement is:
+
+Get time aggregation by level.
+
+```sql
+select count(status) from root.ln.wf01.wt01 group by ((2017-11-01T00:00:00, 2017-11-07T23:00:00],1d), level=1;
+```
+Result:
+
+```
++-----------------------------+-------------------------+
+| Time|COUNT(root.ln.*.*.status)|
++-----------------------------+-------------------------+
+|2017-11-02T00:00:00.000+08:00| 1440|
+|2017-11-03T00:00:00.000+08:00| 1440|
+|2017-11-04T00:00:00.000+08:00| 1440|
+|2017-11-05T00:00:00.000+08:00| 1440|
+|2017-11-06T00:00:00.000+08:00| 1440|
+|2017-11-07T00:00:00.000+08:00| 1440|
+|2017-11-07T23:00:00.000+08:00| 1380|
++-----------------------------+-------------------------+
+Total line number = 7
+It costs 0.006s
+```
+
+Time aggregation with sliding step and by level.
+
+```sql
+select count(status) from root.ln.wf01.wt01 group by ([2017-11-01 00:00:00, 2017-11-07 23:00:00), 3h, 1d), level=1;
+```
+
+Result:
+
+```
++-----------------------------+-------------------------+
+| Time|COUNT(root.ln.*.*.status)|
++-----------------------------+-------------------------+
+|2017-11-01T00:00:00.000+08:00| 180|
+|2017-11-02T00:00:00.000+08:00| 180|
+|2017-11-03T00:00:00.000+08:00| 180|
+|2017-11-04T00:00:00.000+08:00| 180|
+|2017-11-05T00:00:00.000+08:00| 180|
+|2017-11-06T00:00:00.000+08:00| 180|
+|2017-11-07T00:00:00.000+08:00| 180|
++-----------------------------+-------------------------+
+Total line number = 7
+It costs 0.004s
+```
+
+### Aggregation By Tags
+
+IotDB allows you to do aggregation query with the tags defined in timeseries through `GROUP BY TAGS` clause as well.
+
+Firstly, we can put these example data into IoTDB, which will be used in the following feature introduction.
+
+These are the temperature data of the workshops, which belongs to the factory `factory1` and locates in different cities. The time range is `[1000, 10000)`.
+
+The device node of the timeseries path is the ID of the device. The information of city and workshop are modelled in the tags `city` and `workshop`.
+The devices `d1` and `d2` belong to the workshop `d1` in `Beijing`.
+`d3` and `d4` belong to the workshop `w2` in `Beijing`.
+`d5` and `d6` belong to the workshop `w1` in `Shanghai`.
+`d7` belongs to the workshop `w2` in `Shanghai`.
+`d8` and `d9` are under maintenance, and don't belong to any workshops, so they have no tags.
+
+
+```SQL
CREATE DATABASE root.factory1;
create timeseries root.factory1.d1.temperature with datatype=FLOAT tags(city=Beijing, workshop=w1);
create timeseries root.factory1.d2.temperature with datatype=FLOAT tags(city=Beijing, workshop=w1);
@@ -470,455 +815,116 @@ insert into root.factory1.d7(time, temperature) values(3000, 99.3);
insert into root.factory1.d7(time, temperature) values(5000, 100.1);
insert into root.factory1.d7(time, temperature) values(7000, 99.8);
-insert into root.factory1.d8(time, temperature) values(1000, 50.0);
-insert into root.factory1.d8(time, temperature) values(3000, 52.1);
-insert into root.factory1.d8(time, temperature) values(5000, 50.1);
-insert into root.factory1.d8(time, temperature) values(7000, 50.5);
-
-insert into root.factory1.d9(time, temperature) values(1000, 50.3);
-insert into root.factory1.d9(time, temperature) values(3000, 52.1);
-```
-
-### Aggregation query by one single tag
-
-If the user wants to know the average temperature of each workshop, he can query like this
-
-```SQL
-SELECT AVG(temperature) FROM root.factory1.** GROUP BY TAGS(city);
-```
-
-The query will calculate the average of the temperatures of those timeseries which have the same tag value of the key `city`.
-The results are
-
-```
-+--------+------------------+
-| city| avg(temperature)|
-+--------+------------------+
-| Beijing|104.04666697184244|
-|Shanghai|107.85000076293946|
-| NULL| 50.84999910990397|
-+--------+------------------+
-Total line number = 3
-It costs 0.231s
-```
-
-From the results we can see that the differences between aggregation by tags query and aggregation by time or level query are:
-1. Aggregation query by tags will no longer remove wildcard to raw timeseries, but do the aggregation through the data of multiple timeseries, which have the same tag value.
-2. Except for the aggregate result column, the result set contains the key-value column of the grouped tag. The column name is the tag key, and the values in the column are tag values which present in the searched timeseries.
-If some searched timeseries doesn't have the grouped tag, a `NULL` value in the key-value column of the grouped tag will be presented, which means the aggregation of all the timeseries lacking the tagged key.
-
-### Aggregation query by multiple tags
-
-Except for the aggregation query by one single tag, aggregation query by multiple tags in a particular order is allowed as well.
-
-For example, a user wants to know the average temperature of the devices in each workshop.
-As the workshop names may be same in different city, it's not correct to aggregated by the tag `workshop` directly.
-So the aggregation by the tag `city` should be done first, and then by the tag `workshop`.
-
-SQL
-
-```SQL
-SELECT avg(temperature) FROM root.factory1.** GROUP BY TAGS(city, workshop);
-```
-
-The results
-
-```
-+--------+--------+------------------+
-| city|workshop| avg(temperature)|
-+--------+--------+------------------+
-| NULL| NULL| 50.84999910990397|
-|Shanghai| w1|113.01666768391927|
-| Beijing| w2| 104.4000004359654|
-|Shanghai| w2|100.10000038146973|
-| Beijing| w1|103.73750019073486|
-+--------+--------+------------------+
-Total line number = 5
-It costs 0.027s
-```
-
-We can see that in a multiple tags aggregation query, the result set will output the key-value columns of all the grouped tag keys, which have the same order with the one in `GROUP BY TAGS`.
-
-### Downsampling Aggregation by tags based on Time Window
-
-Downsampling aggregation by time window is one of the most popular features in a time series database. IoTDB supports to do aggregation query by tags based on time window.
-
-For example, a user wants to know the average temperature of the devices in each workshop, in every 5 seconds, in the range of time `[1000, 10000)`.
-
-SQL
-
-```SQL
-SELECT avg(temperature) FROM root.factory1.** GROUP BY ([1000, 10000), 5s), TAGS(city, workshop);
-```
-
-The results
-
-```
-+-----------------------------+--------+--------+------------------+
-| Time| city|workshop| avg(temperature)|
-+-----------------------------+--------+--------+------------------+
-|1970-01-01T08:00:01.000+08:00| NULL| NULL| 50.91999893188476|
-|1970-01-01T08:00:01.000+08:00|Shanghai| w1|113.20000076293945|
-|1970-01-01T08:00:01.000+08:00| Beijing| w2| 103.4|
-|1970-01-01T08:00:01.000+08:00|Shanghai| w2| 100.1999994913737|
-|1970-01-01T08:00:01.000+08:00| Beijing| w1|103.81666692097981|
-|1970-01-01T08:00:06.000+08:00| NULL| NULL| 50.5|
-|1970-01-01T08:00:06.000+08:00|Shanghai| w1| 112.6500015258789|
-|1970-01-01T08:00:06.000+08:00| Beijing| w2| 106.9000015258789|
-|1970-01-01T08:00:06.000+08:00|Shanghai| w2| 99.80000305175781|
-|1970-01-01T08:00:06.000+08:00| Beijing| w1| 103.5|
-+-----------------------------+--------+--------+------------------+
-```
-
-Comparing to the pure tag aggregations, this kind of aggregation will divide the data according to the time window specification firstly, and do the aggregation query by the multiple tags in each time window secondly.
-The result set will also contain a time column, which have the same meaning with the time column of the result in downsampling aggregation query by time window.
-
-### Limitation of Aggregation by Tags
-
-As this feature is still under development, some queries have not been completed yet and will be supported in the future.
-
-> 1. Temporarily not support `HAVING` clause to filter the results.
-> 2. Temporarily not support ordering by tag values.
-> 3. Temporarily not support `LIMIT`，`OFFSET`，`SLIMIT`，`SOFFSET`.
-> 4. Temporarily not support `ALIGN BY DEVICE`.
-> 5. Temporarily not support expressions as aggregation function parameter，e.g. `count(s+1)`.
-> 6. Not support the value filter, which stands the same with the `GROUP BY LEVEL` query.
-
-## Aggregation By Variation
-IoTDB supports grouping by continuous stable values through the `GROUP BY VARIATION` statement.
-
-Group-By-Variation wil set the first point in group as the base point,
-then if the difference between the new data and base point is small than or equal to delta,
-the data point will be grouped together and execute aggregation query (The calculation of difference and the meaning of delte are introduced below). The groups won't overlap and there is no fixed start time and end time.
-The syntax of clause is as follows:
-```sql
-group by variation(controlExpression[,delta][,ignoreNull=true/false])
-```
-The different parameters mean:
-* controlExpression
-
-The value that is used to calculate difference. It can be any columns or the expression of them.
-* delta
-
-The threshold that is used when grouping. The difference of controlExpression between the first data point and new data point should less than or equal to delta.
-When delta is zero, all the continuous data with equal expression value will be grouped into the same group.
-* ignoreNull
-
-Used to specify how to deal with the data when the value of controlExpression is null. When ignoreNull is false, null will be treated as a new value and when ignoreNull is true, the data point will be directly skipped.
-
-The supported return types of controlExpression and how to deal with null value when ignoreNull is false are shown in the following table:
-
-| delta | Return Type Supported By controlExpression | The Handling of null when ignoreNull is False |
-|----------|--------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
-| delta!=0 | INT32、INT64、FLOAT、DOUBLE | If the processing group doesn't contains null, null value should be treated as infinity/infinitesimal and will end current group.<br/>Continuous null values are treated as stable values and assigned to the same group. |
-| delta=0 | TEXT、BINARY、INT32、INT64、FLOAT、DOUBLE | Null is treated as a new value in a new group and continuous nulls belong to the same group. |
-
-<img style="width:100%; max-width:800px; max-height:600px; margin-left:auto; margin-right:auto; display:block;" src="https://alioss.timecho.com/docs/img/UserGuide/Process-Data/GroupBy/groupByVariation.jpeg" alt="groupByVariation">
-
-### Precautions for Use
-1. The result of controlExpression should be a unique value. If multiple columns appear after using wildcard stitching, an error will be reported.
-2. For a group in resultSet, the time column output the start time of the group by default. __endTime can be used in select clause to output the endTime of groups in resultSet.
-3. Each device is grouped separately when used with `ALIGN BY DEVICE`.
-4. Delta is zero and ignoreNull is true by default.
-5. Currently `GROUP BY VARIATION` is not supported with `GROUP BY LEVEL`.
-
-Using the raw data below, several examples of `GROUP BY VARIAITON` queries will be given.
-```
-+-----------------------------+-------+-------+-------+--------+-------+-------+
-| Time| s1| s2| s3| s4| s5| s6|
-+-----------------------------+-------+-------+-------+--------+-------+-------+
-|1970-01-01T08:00:00.000+08:00| 4.5| 9.0| 0.0| 45.0| 9.0| 8.25|
-|1970-01-01T08:00:00.010+08:00| null| 19.0| 10.0| 145.0| 19.0| 8.25|
-|1970-01-01T08:00:00.020+08:00| 24.5| 29.0| null| 245.0| 29.0| null|
-|1970-01-01T08:00:00.030+08:00| 34.5| null| 30.0| 345.0| null| null|
-|1970-01-01T08:00:00.040+08:00| 44.5| 49.0| 40.0| 445.0| 49.0| 8.25|
-|1970-01-01T08:00:00.050+08:00| null| 59.0| 50.0| 545.0| 59.0| 6.25|
-|1970-01-01T08:00:00.060+08:00| 64.5| 69.0| 60.0| 645.0| 69.0| null|
-|1970-01-01T08:00:00.070+08:00| 74.5| 79.0| null| null| 79.0| 3.25|
-|1970-01-01T08:00:00.080+08:00| 84.5| 89.0| 80.0| 845.0| 89.0| 3.25|
-|1970-01-01T08:00:00.090+08:00| 94.5| 99.0| 90.0| 945.0| 99.0| 3.25|
-|1970-01-01T08:00:00.150+08:00| 66.5| 77.0| 90.0| 945.0| 99.0| 9.25|
-+-----------------------------+-------+-------+-------+--------+-------+-------+
-```
-### delta = 0
-The sql is shown below:
-```sql
-select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6)
-```
-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|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
-select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, ignoreNull=false)
-```
-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|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
-
-The sql is shown below:
-```sql
-select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, 4)
-```
-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|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:
-
-```sql
-select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6+s5, 10)
-```
-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|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
-When you need to filter the data according to a specific condition and group the continuous ones for an aggregation query.
-`GROUP BY CONDITION` is suitable for you.The rows which don't meet the given condition will be simply ignored because they don't belong to any group.
-Its syntax is defined below:
-```sql
-group by condition(predict,[keep>/>=/=/<=/<]threshold,[,ignoreNull=true/false])
-```
-* predict
-
-Any legal expression return the type of boolean for filtering in grouping.
-* [keep>/>=/=/<=/<]threshold
+insert into root.factory1.d8(time, temperature) values(1000, 50.0);
+insert into root.factory1.d8(time, temperature) values(3000, 52.1);
+insert into root.factory1.d8(time, temperature) values(5000, 50.1);
+insert into root.factory1.d8(time, temperature) values(7000, 50.5);
-Keep expression is used to specify the number of continuous rows that meet the `predict` condition to form a group. Only the number of rows in group satisfy the keep condition, the result of group will be output.
-Keep expression consists of a 'keep' string and a threshold of type `long` or a single 'long' type data.
-* ignoreNull=true/false
+insert into root.factory1.d9(time, temperature) values(1000, 50.3);
+insert into root.factory1.d9(time, temperature) values(3000, 52.1);
+```
-Used to specify how to handle data rows that encounter null predict, skip the row when it's true and end current group when it's false.
+#### Aggregation query by one single tag
-### Precautions for Use
-1. keep condition is required in the query, but you can omit the 'keep' string and given a `long` number which defaults to 'keep=long number' condition.
-2. IgnoreNull defaults to true.
-3. For a group in resultSet, the time column output the start time of the group by default. __endTime can be used in select clause to output the endTime of groups in resultSet.
-4. Each device is grouped separately when used with `ALIGN BY DEVICE`.
-5. Currently `GROUP BY CONDITION` is not supported with `GROUP BY LEVEL`.
+If the user wants to know the average temperature of each workshop, he can query like this
-For the following raw data, several query examples are given below:
-```
-+-----------------------------+-------------------------+-------------------------------------+------------------------------------+
-| Time|root.sg.beijing.car01.soc|root.sg.beijing.car01.charging_status|root.sg.beijing.car01.vehicle_status|
-+-----------------------------+-------------------------+-------------------------------------+------------------------------------+
-|1970-01-01T08:00:00.001+08:00| 14.0| 1| 1|
-|1970-01-01T08:00:00.002+08:00| 16.0| 1| 1|
-|1970-01-01T08:00:00.003+08:00| 16.0| 0| 1|
-|1970-01-01T08:00:00.004+08:00| 16.0| 0| 1|
-|1970-01-01T08:00:00.005+08:00| 18.0| 1| 1|
-|1970-01-01T08:00:00.006+08:00| 24.0| 1| 1|
-|1970-01-01T08:00:00.007+08:00| 36.0| 1| 1|
-|1970-01-01T08:00:00.008+08:00| 36.0| null| 1|
-|1970-01-01T08:00:00.009+08:00| 45.0| 1| 1|
-|1970-01-01T08:00:00.010+08:00| 60.0| 1| 1|
-+-----------------------------+-------------------------+-------------------------------------+------------------------------------+
-```
-The sql statement to query data with at least two continuous row shown below:
-```sql
-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|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 max_time(charging_status),count(vehicle_status),last_value(soc) from root.** group by condition(charging_status=1,KEEP>=2,ignoringNull=false)
+```SQL
+SELECT AVG(temperature) FROM root.factory1.** GROUP BY TAGS(city);
```
-Get the result below, the original group is split.
+
+The query will calculate the average of the temperatures of those timeseries which have the same tag value of the key `city`.
+The results are
+
```
-+-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
-| 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|
-+-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
++--------+------------------+
+| city| avg(temperature)|
++--------+------------------+
+| Beijing|104.04666697184244|
+|Shanghai|107.85000076293946|
+| NULL| 50.84999910990397|
++--------+------------------+
+Total line number = 3
+It costs 0.231s
```
-## Aggregation By Session
-`GROUP BY SESSION` can be used to group data according to the interval of the time. Data with a time interval less than or equal to the given threshold will be assigned to the same group.
-For example, in industrial scenarios, devices don't always run continuously, `GROUP BY SESSION` will group the data generated by each access session of the device.
-Its syntax is defined as follows:
-```sql
-group by session(timeInterval)
-```
-* timeInterval
+From the results we can see that the differences between aggregation by tags query and aggregation by time or level query are:
+1. Aggregation query by tags will no longer remove wildcard to raw timeseries, but do the aggregation through the data of multiple timeseries, which have the same tag value.
+2. Except for the aggregate result column, the result set contains the key-value column of the grouped tag. The column name is the tag key, and the values in the column are tag values which present in the searched timeseries.
+If some searched timeseries doesn't have the grouped tag, a `NULL` value in the key-value column of the grouped tag will be presented, which means the aggregation of all the timeseries lacking the tagged key.
-A given interval threshold to create a new group of data when the difference between the time of data is greater than the threshold.
+#### Aggregation query by multiple tags
-The figure below is a grouping diagram under `GROUP BY SESSION`.
+Except for the aggregation query by one single tag, aggregation query by multiple tags in a particular order is allowed as well.
-<img style="width:100%; max-width:800px; max-height:600px; margin-left:auto; margin-right:auto; display:block;" src="https://alioss.timecho.com/docs/img/UserGuide/Process-Data/GroupBy/groupBySession.jpeg" alt="groupBySession">
+For example, a user wants to know the average temperature of the devices in each workshop.
+As the workshop names may be same in different city, it's not correct to aggregated by the tag `workshop` directly.
+So the aggregation by the tag `city` should be done first, and then by the tag `workshop`.
-### Precautions for Use
-1. For a group in resultSet, the time column output the start time of the group by default. __endTime can be used in select clause to output the endTime of groups in resultSet.
-2. Each device is grouped separately when used with `ALIGN BY DEVICE`.
-3. Currently `GROUP BY SESSION` is not supported with `GROUP BY LEVEL`.
+SQL
-For the raw data below, a few query examples are given:
-```
-+-----------------------------+-----------------+-----------+--------+------+
-| Time| Device|temperature|hardware|status|
-+-----------------------------+-----------------+-----------+--------+------+
-|1970-01-01T08:00:01.000+08:00|root.ln.wf02.wt01| 35.7| 11| false|
-|1970-01-01T08:00:02.000+08:00|root.ln.wf02.wt01| 35.8| 22| true|
-|1970-01-01T08:00:03.000+08:00|root.ln.wf02.wt01| 35.4| 33| false|
-|1970-01-01T08:00:04.000+08:00|root.ln.wf02.wt01| 36.4| 44| false|
-|1970-01-01T08:00:05.000+08:00|root.ln.wf02.wt01| 36.8| 55| false|
-|1970-01-01T08:00:10.000+08:00|root.ln.wf02.wt01| 36.8| 110| false|
-|1970-01-01T08:00:20.000+08:00|root.ln.wf02.wt01| 37.8| 220| true|
-|1970-01-01T08:00:30.000+08:00|root.ln.wf02.wt01| 37.5| 330| false|
-|1970-01-01T08:00:40.000+08:00|root.ln.wf02.wt01| 37.4| 440| false|
-|1970-01-01T08:00:50.000+08:00|root.ln.wf02.wt01| 37.9| 550| false|
-|1970-01-01T08:01:40.000+08:00|root.ln.wf02.wt01| 38.0| 110| false|
-|1970-01-01T08:02:30.000+08:00|root.ln.wf02.wt01| 38.8| 220| true|
-|1970-01-01T08:03:20.000+08:00|root.ln.wf02.wt01| 38.6| 330| false|
-|1970-01-01T08:04:20.000+08:00|root.ln.wf02.wt01| 38.4| 440| false|
-|1970-01-01T08:05:20.000+08:00|root.ln.wf02.wt01| 38.3| 550| false|
-|1970-01-01T08:06:40.000+08:00|root.ln.wf02.wt01| null| 0| null|
-|1970-01-01T08:07:50.000+08:00|root.ln.wf02.wt01| null| 0| null|
-|1970-01-01T08:08:00.000+08:00|root.ln.wf02.wt01| null| 0| null|
-|1970-01-02T08:08:01.000+08:00|root.ln.wf02.wt01| 38.2| 110| false|
-|1970-01-02T08:08:02.000+08:00|root.ln.wf02.wt01| 37.5| 220| true|
-|1970-01-02T08:08:03.000+08:00|root.ln.wf02.wt01| 37.4| 330| false|
-|1970-01-02T08:08:04.000+08:00|root.ln.wf02.wt01| 36.8| 440| false|
-|1970-01-02T08:08:05.000+08:00|root.ln.wf02.wt01| 37.4| 550| false|
-+-----------------------------+-----------------+-----------+--------+------+
-```
-TimeInterval can be set by different time units, the sql is shown below:
-```sql
-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|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
-select __endTime,sum(hardware) from root.ln.wf02.wt01 group by session(50s) having sum(hardware)>0 align by device
-```
-Get the result below:
+```SQL
+SELECT avg(temperature) FROM root.factory1.** GROUP BY TAGS(city, workshop);
```
-+-----------------------------+-----------------+-----------------------------+-------------+
-| 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|
-+-----------------------------+-----------------+-----------------------------+-------------+
+
+The results
+
```
-## Aggregation By Count
-`GROUP BY COUNT`can aggregate the data points according to the number of points. It can group fixed number of continuous data points together for aggregation query.
-Its syntax is defined as follows:
-```sql
-group by count(controlExpression, size[,ignoreNull=true/false])
++--------+--------+------------------+
+| city|workshop| avg(temperature)|
++--------+--------+------------------+
+| NULL| NULL| 50.84999910990397|
+|Shanghai| w1|113.01666768391927|
+| Beijing| w2| 104.4000004359654|
+|Shanghai| w2|100.10000038146973|
+| Beijing| w1|103.73750019073486|
++--------+--------+------------------+
+Total line number = 5
+It costs 0.027s
```
-* controlExpression
+We can see that in a multiple tags aggregation query, the result set will output the key-value columns of all the grouped tag keys, which have the same order with the one in `GROUP BY TAGS`.
-The object to count during processing, it can be any column or an expression of columns.
+#### Downsampling Aggregation by tags based on Time Window
-* size
+Downsampling aggregation by time window is one of the most popular features in a time series database. IoTDB supports to do aggregation query by tags based on time window.
-The number of data points in a group, a number of `size` continuous points will be divided to the same group.
+For example, a user wants to know the average temperature of the devices in each workshop, in every 5 seconds, in the range of time `[1000, 10000)`.
-* ignoreNull=true/false
+SQL
-Whether to ignore the data points with null in `controlExpression`, when ignoreNull is true, data points with the `controlExpression` of null will be skipped during counting.
+```SQL
+SELECT avg(temperature) FROM root.factory1.** GROUP BY ([1000, 10000), 5s), TAGS(city, workshop);
+```
-### Precautions for Use
-1. For a group in resultSet, the time column output the start time of the group by default. __endTime can be used in select clause to output the endTime of groups in resultSet.
-2. Each device is grouped separately when used with `ALIGN BY DEVICE`.
-3. Currently `GROUP BY SESSION` is not supported with `GROUP BY LEVEL`.
-4. When the final number of data points in a group is less than `size`, the result of the group will not be output.
+The results
-For the data below, some examples will be given.
```
-+-----------------------------+-----------+-----------------------+
-| Time|root.sg.soc|root.sg.charging_status|
-+-----------------------------+-----------+-----------------------+
-|1970-01-01T08:00:00.001+08:00| 14.0| 1|
-|1970-01-01T08:00:00.002+08:00| 16.0| 1|
-|1970-01-01T08:00:00.003+08:00| 16.0| 0|
-|1970-01-01T08:00:00.004+08:00| 16.0| 0|
-|1970-01-01T08:00:00.005+08:00| 18.0| 1|
-|1970-01-01T08:00:00.006+08:00| 24.0| 1|
-|1970-01-01T08:00:00.007+08:00| 36.0| 1|
-|1970-01-01T08:00:00.008+08:00| 36.0| null|
-|1970-01-01T08:00:00.009+08:00| 45.0| 1|
-|1970-01-01T08:00:00.010+08:00| 60.0| 1|
-+-----------------------------+-----------+-----------------------+
-```
-The sql is shown below
-```sql
-select count(charging_stauts), first_value(soc) from root.sg group by count(charging_status,5)
-```
-Get the result below, in the second group from 1970-01-01T08:00:00.006+08:00 to 1970-01-01T08:00:00.010+08:00. There are only four points included which is less than `size`. So it won't be output.
-```
-+-----------------------------+-----------------------------+--------------------------------------+
-| Time| __endTime|first_value(root.sg.beijing.car01.soc)|
-+-----------------------------+-----------------------------+--------------------------------------+
-|1970-01-01T08:00:00.001+08:00|1970-01-01T08:00:00.005+08:00| 14.0|
-+-----------------------------+-----------------------------+--------------------------------------+
-```
-When `ignoreNull=false` is used to take null value into account. There will be two groups with 5 points in the resultSet, which is shown as follows:
-```sql
-select count(charging_stauts), first_value(soc) from root.sg group by count(charging_status,5,ignoreNull=false)
-```
-Get the results:
++-----------------------------+--------+--------+------------------+
+| Time| city|workshop| avg(temperature)|
++-----------------------------+--------+--------+------------------+
+|1970-01-01T08:00:01.000+08:00| NULL| NULL| 50.91999893188476|
+|1970-01-01T08:00:01.000+08:00|Shanghai| w1|113.20000076293945|
+|1970-01-01T08:00:01.000+08:00| Beijing| w2| 103.4|
+|1970-01-01T08:00:01.000+08:00|Shanghai| w2| 100.1999994913737|
+|1970-01-01T08:00:01.000+08:00| Beijing| w1|103.81666692097981|
+|1970-01-01T08:00:06.000+08:00| NULL| NULL| 50.5|
+|1970-01-01T08:00:06.000+08:00|Shanghai| w1| 112.6500015258789|
+|1970-01-01T08:00:06.000+08:00| Beijing| w2| 106.9000015258789|
+|1970-01-01T08:00:06.000+08:00|Shanghai| w2| 99.80000305175781|
+|1970-01-01T08:00:06.000+08:00| Beijing| w1| 103.5|
++-----------------------------+--------+--------+------------------+
```
-+-----------------------------+-----------------------------+--------------------------------------+
-| Time| __endTime|first_value(root.sg.beijing.car01.soc)|
-+-----------------------------+-----------------------------+--------------------------------------+
-|1970-01-01T08:00:00.001+08:00|1970-01-01T08:00:00.005+08:00| 14.0|
-|1970-01-01T08:00:00.006+08:00|1970-01-01T08:00:00.010+08:00| 24.0|
-+-----------------------------+-----------------------------+--------------------------------------+
-```
\ No newline at end of file
+
+Comparing to the pure tag aggregations, this kind of aggregation will divide the data according to the time window specification firstly, and do the aggregation query by the multiple tags in each time window secondly.
+The result set will also contain a time column, which have the same meaning with the time column of the result in downsampling aggregation query by time window.
+
+#### Limitation of Aggregation by Tags
+
+As this feature is still under development, some queries have not been completed yet and will be supported in the future.
+
+> 1. Temporarily not support `HAVING` clause to filter the results.
+> 2. Temporarily not support ordering by tag values.
+> 3. Temporarily not support `LIMIT`，`OFFSET`，`SLIMIT`，`SOFFSET`.
+> 4. Temporarily not support `ALIGN BY DEVICE`.
+> 5. Temporarily not support expressions as aggregation function parameter，e.g. `count(s+1)`.
+> 6. Not support the value filter, which stands the same with the `GROUP BY LEVEL` query.
\ No newline at end of file
diff --git a/docs/UserGuide/Query-Data/Overview.md b/docs/UserGuide/Query-Data/Overview.md
index 9bfd6f9162c..d2357e28ae7 100644
--- a/docs/UserGuide/Query-Data/Overview.md
+++ b/docs/UserGuide/Query-Data/Overview.md
@@ -77,7 +77,7 @@ SELECT [LAST] selectExpr [, selectExpr] ...
### `GROUP BY` clause
- The `GROUP BY` clause specifies how the time series are aggregated by segment or group.
-- Segmented aggregation refers to segmenting data in the row direction according to the time dimension, aiming at the time relationship between different data points in the same time series, and obtaining an aggregated value for each segment. Currently only **segmentation by time interval**、**group by variation**、**group by series**、**group by session** and **group by count** is supported, and more segmentation methods will be supported in the future.
+- Segmented aggregation refers to segmenting data in the row direction according to the time dimension, aiming at the time relationship between different data points in the same time series, and obtaining an aggregated value for each segment. Currently only **group by time**、**group by variation**、**group by condition**、**group by session** and **group by count** is supported, and more segmentation methods will be supported in the future.
- Group aggregation refers to grouping the potential business attributes of time series for different time series. Each group contains several time series, and each group gets an aggregated value. Support **group by path level** and **group by tag** two grouping methods.
- Segment aggregation and group aggregation can be mixed.
- For details and examples, see the document [Group By Aggregation](./Group-By.md).
diff --git a/docs/zh/UserGuide/Query-Data/Group-By.md b/docs/zh/UserGuide/Query-Data/Group-By.md
index c9374e172c7..d6d1d3fe04b 100644
--- a/docs/zh/UserGuide/Query-Data/Group-By.md
+++ b/docs/zh/UserGuide/Query-Data/Group-By.md
@@ -20,10 +20,17 @@
-->
## 分段分组聚合
+IoTDB支持通过`GROUP BY`子句对序列进行分段或者分组聚合。
-### 时间区间分段聚合
+分段聚合是指按照时间维度，针对同时间序列中不同数据点之间的时间关系，对数据在行的方向进行分段，每个段得到一个聚合值。目前支持**时间区间分段**、**差值分段**、**条件分段**、**会话分段**和**点数分段**，未来将支持更多分段方式。
-分段聚合是一种时序数据典型的查询方式，数据以高频进行采集，需要按照一定的时间间隔进行聚合计算，如计算每天的平均气温，需要将气温的序列按天进行分段，然后计算平均值。
+分组聚合是指针对不同时间序列，在时间序列的潜在业务属性上分组，每个组包含若干条时间序列，每个组得到一个聚合值。支持**按路径层级分组**和**按序列标签分组**两种分组方式。
+
+### 分段聚合
+
+#### 时间区间分段聚合
+
+时间区间分段聚合是一种时序数据典型的查询方式，数据以高频进行采集，需要按照一定的时间间隔进行聚合计算，如计算每天的平均气温，需要将气温的序列按天进行分段，然后计算平均值。
在 IoTDB 中，聚合查询可以通过 `GROUP BY` 子句指定按照时间区间分段聚合。用户可以指定聚合的时间间隔和滑动步长，相关参数如下：
@@ -37,7 +44,7 @@
接下来，我们给出几个典型例子：
-#### 未指定滑动步长的时间区间分组聚合查询
+##### 未指定滑动步长的时间区间分段聚合查询
对应的 SQL 语句是：
@@ -72,7 +79,7 @@ Total line number = 7
It costs 0.024s
```
-#### 指定滑动步长的时间区间分组聚合查询
+##### 指定滑动步长的时间区间分段聚合查询
对应的 SQL 语句是：
@@ -135,7 +142,7 @@ Total line number = 5
It costs 0.006s
```
-#### 按照自然月份的时间区间分组聚合查询
+##### 按照自然月份的时间区间分段聚合查询
对应的 SQL 语句是：
@@ -225,7 +232,7 @@ select count(status) from root.ln.wf01.wt01 group by([2017-10-31T00:00:00, 2019-
+-----------------------------+-------------------------------+
```
-#### 左开右闭区间
+##### 左开右闭区间
每个区间的结果时间戳为区间右端点，对应的 SQL 语句是：
@@ -253,467 +260,150 @@ Total line number = 7
It costs 0.004s
```
-#### 与分组聚合混合使用
-
-通过定义 LEVEL 来统计指定层级下的数据点个数。
-
-例如：
-
-统计降采样后的数据点个数
-
+#### 差值分段聚合
+IoTDB支持通过`GROUP BY VARIATION`语句来根据差值进行分组。`GROUP BY VARIATION`会将第一个点作为一个组的**基准点**，每个新的数据在按照给定规则与基准点进行差值运算后，
+如果差值小于给定的阈值则将该新点归于同一组，否则结束当前分组，以这个新的数据为新的基准点开启新的分组。
+该分组方式不会重叠，且没有固定的开始结束时间。其子句语法如下：
```sql
-select count(status) from root.ln.wf01.wt01 group by ((2017-11-01T00:00:00, 2017-11-07T23:00:00],1d), level=1;
-```
-
-结果：
-
-```
-+-----------------------------+-------------------------+
-| Time|COUNT(root.ln.*.*.status)|
-+-----------------------------+-------------------------+
-|2017-11-02T00:00:00.000+08:00| 1440|
-|2017-11-03T00:00:00.000+08:00| 1440|
-|2017-11-04T00:00:00.000+08:00| 1440|
-|2017-11-05T00:00:00.000+08:00| 1440|
-|2017-11-06T00:00:00.000+08:00| 1440|
-|2017-11-07T00:00:00.000+08:00| 1440|
-|2017-11-07T23:00:00.000+08:00| 1380|
-+-----------------------------+-------------------------+
-Total line number = 7
-It costs 0.006s
+group by variation(controlExpression[,delta][,ignoreNull=true/false])
```
+不同的参数含义如下
+* controlExpression
-加上滑动 Step 的降采样后的结果也可以汇总
+分组所参照的值，**可以是查询数据中的某一列或是多列的表达式
+（多列表达式计算后仍为一个值，使用多列表达式时指定的列必须都为数值列）**， 差值便是根据数据的controlExpression的差值运算。
+* delta
-```sql
-select count(status) from root.ln.wf01.wt01 group by ([2017-11-01 00:00:00, 2017-11-07 23:00:00), 3h, 1d), level=1;
-```
+分组所使用的阈值，同一分组中**每个点的controlExpression对应的值与该组中基准点对应值的差值都小于`delta`**。当`delta=0`时，相当于一个等值分组，所有连续且expression值相同的数据将被分到一组。
-```
-+-----------------------------+-------------------------+
-| Time|COUNT(root.ln.*.*.status)|
-+-----------------------------+-------------------------+
-|2017-11-01T00:00:00.000+08:00| 180|
-|2017-11-02T00:00:00.000+08:00| 180|
-|2017-11-03T00:00:00.000+08:00| 180|
-|2017-11-04T00:00:00.000+08:00| 180|
-|2017-11-05T00:00:00.000+08:00| 180|
-|2017-11-06T00:00:00.000+08:00| 180|
-|2017-11-07T00:00:00.000+08:00| 180|
-+-----------------------------+-------------------------+
-Total line number = 7
-It costs 0.004s
-```
+* ignoreNull
-### 路径层级分组聚合
+用于指定`controlExpression`的值为null时对数据的处理方式，当`ignoreNull`为false时，该null值会被视为新的值，`ignoreNull`为true时，则直接跳过对应的点。
-在时间序列层级结构中，分层聚合查询用于**对某一层级下同名的序列进行聚合查询**。
+在`delta`取不同值时，`controlExpression`支持的返回数据类型以及当`ignoreNull`为false时对于null值的处理方式可以见下表：
-- 使用 `GROUP BY LEVEL = INT` 来指定需要聚合的层级，并约定 `ROOT` 为第 0 层。若统计 "root.ln" 下所有序列则需指定 level 为 1。
-- 分层聚合查询支持使用所有内置聚合函数。对于 `sum`，`avg`，`min_value`， `max_value`， `extreme` 五种聚合函数，需保证所有聚合的时间序列数据类型相同。其他聚合函数没有此限制。
+| delta | controlExpression支持的返回类型 | ignoreNull=false时对于Null值的处理 |
+|----------|--------------------------------------|-----------------------------------------------------------------|
+| delta!=0 | INT32、INT64、FLOAT、DOUBLE | 若正在维护分组的值不为null,null视为无穷大/无穷小，结束当前分组。连续的null视为差值相等的值，会被分配在同一个分组 |
+| delta=0 | TEXT、BINARY、INT32、INT64、FLOAT、DOUBLE | null被视为新分组中的新值，连续的null属于相同的分组 |
-**示例1：** 不同 database 下均存在名为 status 的序列， 如 "root.ln.wf01.wt01.status", "root.ln.wf02.wt02.status", 以及 "root.sgcc.wf03.wt01.status", 如果需要统计不同 database 下 status 序列的数据点个数，使用以下查询：
+下图为差值分段的一个分段方式示意图，与组中第一个数据的控制列值的差值在delta内的控制列对应的点属于相同的分组。
-```sql
-select count(status) from root.** group by level = 1
-```
+<img style="width:100%; max-width:800px; max-height:600px; margin-left:auto; margin-right:auto; display:block;" src="https://alioss.timecho.com/docs/img/UserGuide/Process-Data/GroupBy/groupByVariation.jpeg" alt="groupByVariation">
-运行结果为：
+##### 使用注意事项
+1. `controlExpression`的结果应该为唯一值，如果使用通配符拼接后出现多列，则报错。
+2. 对于一个分组，默认Time列输出分组的开始时间，查询时可以使用select `__endTime`的方式来使得结果输出分组的结束时间。
+3. 与`ALIGN BY DEVICE`搭配使用时会对每个device进行单独的分组操作。
+4. 当没有指定`delta`和`ignoreNull`时，`delta`默认为0，`ignoreNull`默认为true。
+5. 当前暂不支持与`GROUP BY LEVEL`搭配使用。
+使用如下的原始数据，接下来会给出几个事件分段查询的使用样例
```
-+-------------------------+---------------------------+
-|count(root.ln.*.*.status)|count(root.sgcc.*.*.status)|
-+-------------------------+---------------------------+
-| 20160| 10080|
-+-------------------------+---------------------------+
-Total line number = 1
-It costs 0.003s
++-----------------------------+-------+-------+-------+--------+-------+-------+
+| Time| s1| s2| s3| s4| s5| s6|
++-----------------------------+-------+-------+-------+--------+-------+-------+
+|1970-01-01T08:00:00.000+08:00| 4.5| 9.0| 0.0| 45.0| 9.0| 8.25|
+|1970-01-01T08:00:00.010+08:00| null| 19.0| 10.0| 145.0| 19.0| 8.25|
+|1970-01-01T08:00:00.020+08:00| 24.5| 29.0| null| 245.0| 29.0| null|
+|1970-01-01T08:00:00.030+08:00| 34.5| null| 30.0| 345.0| null| null|
+|1970-01-01T08:00:00.040+08:00| 44.5| 49.0| 40.0| 445.0| 49.0| 8.25|
+|1970-01-01T08:00:00.050+08:00| null| 59.0| 50.0| 545.0| 59.0| 6.25|
+|1970-01-01T08:00:00.060+08:00| 64.5| 69.0| 60.0| 645.0| 69.0| null|
+|1970-01-01T08:00:00.070+08:00| 74.5| 79.0| null| null| 79.0| 3.25|
+|1970-01-01T08:00:00.080+08:00| 84.5| 89.0| 80.0| 845.0| 89.0| 3.25|
+|1970-01-01T08:00:00.090+08:00| 94.5| 99.0| 90.0| 945.0| 99.0| 3.25|
+|1970-01-01T08:00:00.150+08:00| 66.5| 77.0| 90.0| 945.0| 99.0| 9.25|
++-----------------------------+-------+-------+-------+--------+-------+-------+
```
-
-**示例2：** 统计不同设备下 status 序列的数据点个数，可以规定 level = 3，
-
+##### delta=0时的等值事件分段
+使用如下sql语句
```sql
-select count(status) from root.** group by level = 3
+select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6)
```
-
-运行结果为：
-
+得到如下的查询结果，这里忽略了s6为null的行
```
-+---------------------------+---------------------------+
-|count(root.*.*.wt01.status)|count(root.*.*.wt02.status)|
-+---------------------------+---------------------------+
-| 20160| 10080|
-+---------------------------+---------------------------+
-Total line number = 1
-It costs 0.003s
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| 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|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
-
-注意，这时会将 database `ln` 和 `sgcc` 下名为 `wt01` 的设备视为同名设备聚合在一起。
-
-**示例3：** 统计不同 database 下的不同设备中 status 序列的数据点个数，可以使用以下查询：
-
+当指定ignoreNull为false时，会将s6为null的数据也考虑进来
```sql
-select count(status) from root.** group by level = 1, 3
+select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, ignoreNull=false)
```
-
-运行结果为：
-
+得到如下的结果
```
-+----------------------------+----------------------------+------------------------------+
-|count(root.ln.*.wt01.status)|count(root.ln.*.wt02.status)|count(root.sgcc.*.wt01.status)|
-+----------------------------+----------------------------+------------------------------+
-| 10080| 10080| 10080|
-+----------------------------+----------------------------+------------------------------+
-Total line number = 1
-It costs 0.003s
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| 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|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
-
-**示例4：** 查询所有序列下温度传感器 temperature 的最大值，可以使用下列查询语句：
-
+##### delta!=0时的差值事件分段
+使用如下sql语句
```sql
-select max_value(temperature) from root.** group by level = 0
+select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, 4)
```
-
-运行结果：
-
+得到如下的查询结果
```
-+---------------------------------+
-|max_value(root.*.*.*.temperature)|
-+---------------------------------+
-| 26.0|
-+---------------------------------+
-Total line number = 1
-It costs 0.013s
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| 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|
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
```
-
-**示例5：** 上面的查询都是针对某一个传感器，特别地，**如果想要查询某一层级下所有传感器拥有的总数据点数，则需要显式规定测点为 `*`**
+group by子句中的controlExpression同样支持列的表达式
```sql
-select count(*) from root.ln.** group by level = 2
+select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6+s5, 10)
```
-
-运行结果：
-
+得到如下的查询结果
```
-+----------------------+----------------------+
-|count(root.*.wf01.*.*)|count(root.*.wf02.*.*)|
-+----------------------+----------------------+
-| 20160| 20160|
-+----------------------+----------------------+
-Total line number = 1
-It costs 0.013s
++-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
+| 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`的分段方式；不满足给定条件的行因为不属于任何分组会被直接简单忽略。
+其语法定义如下：
+```sql
+group by condition(predict,[keep>/>=/=/<=/<]threshold,[,ignoreNull=true/false])
+```
+* predict
-### 标签分组聚合
-
-IoTDB 支持通过 `GROUP BY TAGS` 语句根据时间序列中定义的标签的键值做聚合查询。
-
-我们先在 IoTDB 中写入如下示例数据，稍后会以这些数据为例介绍标签聚合查询。
-
-这些是某工厂 `factory1` 在多个城市的多个车间的设备温度数据， 时间范围为 [1000, 10000)。
-
-时间序列路径中的设备一级是设备唯一标识。城市信息 `city` 和车间信息 `workshop` 则被建模在该设备时间序列的标签中。
-其中，设备 `d1`、`d2` 在 `Beijing` 的 `w1` 车间， `d3`、`d4` 在 `Beijing` 的 `w2` 车间，`d5`、`d6` 在 `Shanghai` 的 `w1` 车间，`d7` 在 `Shanghai` 的 `w2` 车间。
-`d8` 和 `d9` 设备目前处于调试阶段，还未被分配到具体的城市和车间，所以其相应的标签值为空值。
+返回boolean数据类型的合法表达式，用于分组的筛选。
+* keep[>/>=/=/<=/<]threshold
-```SQL
-create database root.factory1;
-create timeseries root.factory1.d1.temperature with datatype=FLOAT tags(city=Beijing, workshop=w1);
-create timeseries root.factory1.d2.temperature with datatype=FLOAT tags(city=Beijing, workshop=w1);
-create timeseries root.factory1.d3.temperature with datatype=FLOAT tags(city=Beijing, workshop=w2);
-create timeseries root.factory1.d4.temperature with datatype=FLOAT tags(city=Beijing, workshop=w2);
-create timeseries root.factory1.d5.temperature with datatype=FLOAT tags(city=Shanghai, workshop=w1);
-create timeseries root.factory1.d6.temperature with datatype=FLOAT tags(city=Shanghai, workshop=w1);
-create timeseries root.factory1.d7.temperature with datatype=FLOAT tags(city=Shanghai, workshop=w2);
-create timeseries root.factory1.d8.temperature with datatype=FLOAT;
-create timeseries root.factory1.d9.temperature with datatype=FLOAT;
+keep表达式用来指定形成分组所需要连续满足`predict`条件的数据行数，只有行数满足keep表达式的分组才会被输出。keep表达式由一个'keep'字符串和`long`类型的threshold组合或者是单独的`long`类型数据构成。
-insert into root.factory1.d1(time, temperature) values(1000, 104.0);
-insert into root.factory1.d1(time, temperature) values(3000, 104.2);
-insert into root.factory1.d1(time, temperature) values(5000, 103.3);
-insert into root.factory1.d1(time, temperature) values(7000, 104.1);
+* ignoreNull=true/false
-insert into root.factory1.d2(time, temperature) values(1000, 104.4);
-insert into root.factory1.d2(time, temperature) values(3000, 103.7);
-insert into root.factory1.d2(time, temperature) values(5000, 103.3);
-insert into root.factory1.d2(time, temperature) values(7000, 102.9);
+用于指定遇到predict为null的数据行时的处理方式，为true则跳过该行，为false则结束当前分组。
-insert into root.factory1.d3(time, temperature) values(1000, 103.9);
-insert into root.factory1.d3(time, temperature) values(3000, 103.8);
-insert into root.factory1.d3(time, temperature) values(5000, 102.7);
-insert into root.factory1.d3(time, temperature) values(7000, 106.9);
-
-insert into root.factory1.d4(time, temperature) values(1000, 103.9);
-insert into root.factory1.d4(time, temperature) values(5000, 102.7);
-insert into root.factory1.d4(time, temperature) values(7000, 106.9);
-
-insert into root.factory1.d5(time, temperature) values(1000, 112.9);
-insert into root.factory1.d5(time, temperature) values(7000, 113.0);
-
-insert into root.factory1.d6(time, temperature) values(1000, 113.9);
-insert into root.factory1.d6(time, temperature) values(3000, 113.3);
-insert into root.factory1.d6(time, temperature) values(5000, 112.7);
-insert into root.factory1.d6(time, temperature) values(7000, 112.3);
-
-insert into root.factory1.d7(time, temperature) values(1000, 101.2);
-insert into root.factory1.d7(time, temperature) values(3000, 99.3);
-insert into root.factory1.d7(time, temperature) values(5000, 100.1);
-insert into root.factory1.d7(time, temperature) values(7000, 99.8);
-
-insert into root.factory1.d8(time, temperature) values(1000, 50.0);
-insert into root.factory1.d8(time, temperature) values(3000, 52.1);
-insert into root.factory1.d8(time, temperature) values(5000, 50.1);
-insert into root.factory1.d8(time, temperature) values(7000, 50.5);
-
-insert into root.factory1.d9(time, temperature) values(1000, 50.3);
-insert into root.factory1.d9(time, temperature) values(3000, 52.1);
-```
-
-#### 单标签聚合查询
-
-用户想统计该工厂每个地区的设备的温度的平均值，可以使用如下查询语句
-
-```SQL
-SELECT AVG(temperature) FROM root.factory1.** GROUP BY TAGS(city);
-```
-
-该查询会将具有同一个 `city` 标签值的时间序列的所有满足查询条件的点做平均值计算，计算结果如下
-
-```
-+--------+------------------+
-| city| avg(temperature)|
-+--------+------------------+
-| Beijing|104.04666697184244|
-|Shanghai|107.85000076293946|
-| NULL| 50.84999910990397|
-+--------+------------------+
-Total line number = 3
-It costs 0.231s
-```
-
-从结果集中可以看到，和时间区间聚合、按层次聚合相比，标签聚合的查询结果的不同点是：
-1. 标签聚合查询的聚合结果不会再做去星号展开，而是将多个时间序列的数据作为一个整体进行聚合计算。
-2. 标签聚合查询除了输出聚合结果列，还会输出聚合标签的键值列。该列的列名为聚合指定的标签键，列的值则为所有查询的时间序列中出现的该标签的值。
-如果某些时间序列未设置该标签，则在键值列中有一行单独的 `NULL` ，代表未设置标签的所有时间序列数据的聚合结果。
-
-#### 多标签聚合查询
-
-除了基本的单标签聚合查询外，还可以按顺序指定多个标签进行聚合计算。
-
-例如，用户想统计每个城市的每个车间内设备的平均温度。但因为各个城市的车间名称有可能相同，所以不能直接按照 `workshop` 做标签聚合。必须要先按照城市，再按照车间处理。
-
-SQL 语句如下
-
-```SQL
-SELECT avg(temperature) FROM root.factory1.** GROUP BY TAGS(city, workshop);
-```
-
-查询结果如下
-
-```
-+--------+--------+------------------+
-| city|workshop| avg(temperature)|
-+--------+--------+------------------+
-| NULL| NULL| 50.84999910990397|
-|Shanghai| w1|113.01666768391927|
-| Beijing| w2| 104.4000004359654|
-|Shanghai| w2|100.10000038146973|
-| Beijing| w1|103.73750019073486|
-+--------+--------+------------------+
-Total line number = 5
-It costs 0.027s
-```
-
-从结果集中可以看到，和单标签聚合相比，多标签聚合的查询结果会根据指定的标签顺序，输出相应标签的键值列。
-
-#### 基于时间区间的标签聚合查询
-
-按照时间区间聚合是时序数据库中最常用的查询需求之一。IoTDB 在基于时间区间的聚合基础上，支持进一步按照标签进行聚合查询。
-
-例如，用户想统计时间 `[1000, 10000)` 范围内，每个城市每个车间中的设备每 5 秒内的平均温度。
-
-SQL 语句如下
-
-```SQL
-SELECT AVG(temperature) FROM root.factory1.** GROUP BY ([1000, 10000), 5s), TAGS(city, workshop);
-```
-
-查询结果如下
-
-```
-+-----------------------------+--------+--------+------------------+
-| Time| city|workshop| avg(temperature)|
-+-----------------------------+--------+--------+------------------+
-|1970-01-01T08:00:01.000+08:00| NULL| NULL| 50.91999893188476|
-|1970-01-01T08:00:01.000+08:00|Shanghai| w1|113.20000076293945|
-|1970-01-01T08:00:01.000+08:00| Beijing| w2| 103.4|
-|1970-01-01T08:00:01.000+08:00|Shanghai| w2| 100.1999994913737|
-|1970-01-01T08:00:01.000+08:00| Beijing| w1|103.81666692097981|
-|1970-01-01T08:00:06.000+08:00| NULL| NULL| 50.5|
-|1970-01-01T08:00:06.000+08:00|Shanghai| w1| 112.6500015258789|
-|1970-01-01T08:00:06.000+08:00| Beijing| w2| 106.9000015258789|
-|1970-01-01T08:00:06.000+08:00|Shanghai| w2| 99.80000305175781|
-|1970-01-01T08:00:06.000+08:00| Beijing| w1| 103.5|
-+-----------------------------+--------+--------+------------------+
-```
-
-和标签聚合相比，基于时间区间的标签聚合的查询会首先按照时间区间划定聚合范围，在时间区间内部再根据指定的标签顺序，进行相应数据的聚合计算。在输出的结果集中，会包含一列时间列，该时间列值的含义和时间区间聚合查询的相同。
-
-#### 标签聚合查询的限制
-
-由于标签聚合功能仍然处于开发阶段，目前有如下未实现功能。
-
-> 1. 暂不支持 `HAVING` 子句过滤查询结果。
-> 2. 暂不支持结果按照标签值排序。
-> 3. 暂不支持 `LIMIT`，`OFFSET`，`SLIMIT`，`SOFFSET`。
-> 4. 暂不支持 `ALIGN BY DEVICE`。
-> 5. 暂不支持聚合函数内部包含表达式，例如 `count(s+1)`。
-> 6. 不支持值过滤条件聚合，和分层聚合查询行为保持一致。
-
-### 差值分段聚合
-IoTDB支持通过`GROUP BY VARIATION`语句来根据差值进行分组。`GROUP BY VARIATION`会将第一个点作为一个组的**基准点**，每个新的数据在按照给定规则与基准点进行差值运算后，
-如果差值小于给定的阈值则将该新点归于同一组，否则结束当前分组，以这个新的数据为新的基准点开启新的分组。
-该分组方式不会重叠，且没有固定的开始结束时间。其子句语法如下：
-```sql
-group by variation(controlExpression[,delta][,ignoreNull=true/false])
-```
-不同的参数含义如下
-* controlExpression
-
-分组所参照的值，**可以是查询数据中的某一列或是多列的表达式
-（多列表达式计算后仍为一个值，使用多列表达式时指定的列必须都为数值列）**， 差值便是根据数据的controlExpression的差值运算。
-* delta
-
-分组所使用的阈值，同一分组中**每个点的controlExpression对应的值与该组中基准点对应值的差值都小于`delta`**。当`delta=0`时，相当于一个等值分组，所有连续且expression值相同的数据将被分到一组。
-
-* ignoreNull
-
-用于指定`controlExpression`的值为null时对数据的处理方式，当`ignoreNull`为false时，该null值会被视为新的值，`ignoreNull`为true时，则直接跳过对应的点。
-
-在`delta`取不同值时，`controlExpression`支持的返回数据类型以及当`ignoreNull`为false时对于null值的处理方式可以见下表：
-
-| delta | controlExpression支持的返回类型 | ignoreNull=false时对于Null值的处理 |
-|----------|--------------------------------------|-----------------------------------------------------------------|
-| delta!=0 | INT32、INT64、FLOAT、DOUBLE | 若正在维护分组的值不为null,null视为无穷大/无穷小，结束当前分组。连续的null视为差值相等的值，会被分配在同一个分组 |
-| delta=0 | TEXT、BINARY、INT32、INT64、FLOAT、DOUBLE | null被视为新分组中的新值，连续的null属于相同的分组 |
-
-下图为差值分段的一个分段方式示意图，与组中第一个数据的控制列值的差值在delta内的控制列对应的点属于相同的分组。
-
-<img style="width:100%; max-width:800px; max-height:600px; margin-left:auto; margin-right:auto; display:block;" src="https://alioss.timecho.com/docs/img/UserGuide/Process-Data/GroupBy/groupByVariation.jpeg" alt="groupByVariation">
-
-#### 使用注意事项
-1. `controlExpression`的结果应该为唯一值，如果使用通配符拼接后出现多列，则报错。
-2. 对于一个分组，默认Time列输出分组的开始时间，查询时可以使用select `__endTime`的方式来使得结果输出分组的结束时间。
-3. 与`ALIGN BY DEVICE`搭配使用时会对每个device进行单独的分组操作。
-4. 当没有指定`delta`和`ignoreNull`时，`delta`默认为0，`ignoreNull`默认为true。
-5. 当前暂不支持与`GROUP BY LEVEL`搭配使用。
-
-使用如下的原始数据，接下来会给出几个事件分段查询的使用样例
-```
-+-----------------------------+-------+-------+-------+--------+-------+-------+
-| Time| s1| s2| s3| s4| s5| s6|
-+-----------------------------+-------+-------+-------+--------+-------+-------+
-|1970-01-01T08:00:00.000+08:00| 4.5| 9.0| 0.0| 45.0| 9.0| 8.25|
-|1970-01-01T08:00:00.010+08:00| null| 19.0| 10.0| 145.0| 19.0| 8.25|
-|1970-01-01T08:00:00.020+08:00| 24.5| 29.0| null| 245.0| 29.0| null|
-|1970-01-01T08:00:00.030+08:00| 34.5| null| 30.0| 345.0| null| null|
-|1970-01-01T08:00:00.040+08:00| 44.5| 49.0| 40.0| 445.0| 49.0| 8.25|
-|1970-01-01T08:00:00.050+08:00| null| 59.0| 50.0| 545.0| 59.0| 6.25|
-|1970-01-01T08:00:00.060+08:00| 64.5| 69.0| 60.0| 645.0| 69.0| null|
-|1970-01-01T08:00:00.070+08:00| 74.5| 79.0| null| null| 79.0| 3.25|
-|1970-01-01T08:00:00.080+08:00| 84.5| 89.0| 80.0| 845.0| 89.0| 3.25|
-|1970-01-01T08:00:00.090+08:00| 94.5| 99.0| 90.0| 945.0| 99.0| 3.25|
-|1970-01-01T08:00:00.150+08:00| 66.5| 77.0| 90.0| 945.0| 99.0| 9.25|
-+-----------------------------+-------+-------+-------+--------+-------+-------+
-```
-#### delta=0时的等值事件分段
-使用如下sql语句
-```sql
-select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6)
-```
-得到如下的查询结果，这里忽略了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|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
-select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, ignoreNull=false)
-```
-得到如下的结果
-```
-+-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
-| 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语句
-```sql
-select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6, 4)
-```
-得到如下的查询结果
-```
-+-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
-| 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同样支持列的表达式
-
-```sql
-select __endTime, avg(s1), count(s2), sum(s3) from root.sg.d group by variation(s6+s5, 10)
-```
-得到如下的查询结果
-```
-+-----------------------------+-----------------------------+-----------------+-------------------+-----------------+
-| 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`的分段方式；不满足给定条件的行因为不属于任何分组会被直接简单忽略。
-其语法定义如下：
-```sql
-group by condition(predict,[keep>/>=/=/<=/<]threshold,[,ignoreNull=true/false])
-```
-* predict
-
-返回boolean数据类型的合法表达式，用于分组的筛选。
-* keep[>/>=/=/<=/<]threshold
-
-keep表达式用来指定形成分组所需要连续满足`predict`条件的数据行数，只有行数满足keep表达式的分组才会被输出。keep表达式由一个'keep'字符串和`long`类型的threshold组合或者是单独的`long`类型数据构成。
-
-* ignoreNull=true/false
-
-用于指定遇到predict为null的数据行时的处理方式，为true则跳过该行，为false则结束当前分组。
-
-#### 使用注意事项
-1. keep条件在查询中是必需的，但可以省略掉keep字符串给出一个`long`类型常数，默认为`keep=该long型常数`的等于条件。
-2. `ignoreNull`默认为true。
-3. 对于一个分组，默认Time列输出分组的开始时间，查询时可以使用select `__endTime`的方式来使得结果输出分组的结束时间。
-4. 与`ALIGN BY DEVICE`搭配使用时会对每个device进行单独的分组操作。
-5. 当前暂不支持与`GROUP BY LEVEL`搭配使用。
+##### 使用注意事项
+1. keep条件在查询中是必需的，但可以省略掉keep字符串给出一个`long`类型常数，默认为`keep=该long型常数`的等于条件。
+2. `ignoreNull`默认为true。
+3. 对于一个分组，默认Time列输出分组的开始时间，查询时可以使用select `__endTime`的方式来使得结果输出分组的结束时间。
+4. 与`ALIGN BY DEVICE`搭配使用时会对每个device进行单独的分组操作。
+5. 当前暂不支持与`GROUP BY LEVEL`搭配使用。
对于如下原始数据,下面会给出几个查询样例:
@@ -760,7 +450,7 @@ select max_time(charging_status),count(vehicle_status),last_value(soc) from root
|1970-01-01T08:00:00.009+08:00| 10| 2| 60.0|
+-----------------------------+-----------------------------------------------+-------------------------------------------+-------------------------------------+
```
-### 会话分段聚合
+#### 会话分段聚合
`GROUP BY SESSION`可以根据时间列的间隔进行分组，在结果集的时间列中，时间间隔小于等于设定阈值的数据会被分为一组。例如在工业场景中，设备并不总是连续运行，`GROUP BY SESSION`会将设备每次接入会话所产生的数据分为一组。
其语法定义如下：
```sql
@@ -774,7 +464,7 @@ group by session(timeInterval)
<img style="width:100%; max-width:800px; max-height:600px; margin-left:auto; margin-right:auto; display:block;" src="https://alioss.timecho.com/docs/img/UserGuide/Process-Data/GroupBy/groupBySession.jpeg">
-#### 使用注意事项
+##### 使用注意事项
1. 对于一个分组，默认Time列输出分组的开始时间，查询时可以使用select `__endTime`的方式来使得结果输出分组的结束时间。
2. 与`ALIGN BY DEVICE`搭配使用时会对每个device进行单独的分组操作。
3. 当前暂不支持与`GROUP BY LEVEL`搭配使用。
@@ -837,7 +527,7 @@ select __endTime,sum(hardware) from root.ln.wf02.wt01 group by session(50s) havi
|1970-01-02T08:08:01.000+08:00|root.ln.wf02.wt01|1970-01-02T08:08:05.000+08:00| 1650.0|
+-----------------------------+-----------------+-----------------------------+-------------+
```
-## 点数分段聚合
+#### 点数分段聚合
`GROUP BY COUNT`可以根据点数分组进行聚合运算，将连续的指定数量数据点分为一组，即按照固定的点数进行分组。
其语法定义如下：
```sql
@@ -855,7 +545,7 @@ group by count(controlExpression, size[,ignoreNull=true/false])
是否忽略`controlExpression`为null的数据点，当ignoreNull为true时，在计数时会跳过`controlExpression`结果为null的数据点
-### 使用注意事项
+##### 使用注意事项
1. 对于一个分组，默认Time列输出分组的开始时间，查询时可以使用select `__endTime`的方式来使得结果输出分组的结束时间。
2. 与`ALIGN BY DEVICE`搭配使用时会对每个device进行单独的分组操作。
3. 当前暂不支持与`GROUP BY LEVEL`搭配使用。
@@ -902,4 +592,322 @@ select count(charging_stauts), first_value(soc) from root.sg group by count(char
|1970-01-01T08:00:00.001+08:00|1970-01-01T08:00:00.005+08:00| 14.0|
|1970-01-01T08:00:00.006+08:00|1970-01-01T08:00:00.010+08:00| 24.0|
+-----------------------------+-----------------------------+--------------------------------------+
-```
\ No newline at end of file
+```
+### 分组聚合
+
+#### 路径层级分组聚合
+
+在时间序列层级结构中，路径层级分组聚合查询用于**对某一层级下同名的序列进行聚合查询**。
+
+- 使用 `GROUP BY LEVEL = INT` 来指定需要聚合的层级，并约定 `ROOT` 为第 0 层。若统计 "root.ln" 下所有序列则需指定 level 为 1。
+- 路径层次分组聚合查询支持使用所有内置聚合函数。对于 `sum`，`avg`，`min_value`， `max_value`， `extreme` 五种聚合函数，需保证所有聚合的时间序列数据类型相同。其他聚合函数没有此限制。
+
+**示例1：** 不同 database 下均存在名为 status 的序列， 如 "root.ln.wf01.wt01.status", "root.ln.wf02.wt02.status", 以及 "root.sgcc.wf03.wt01.status", 如果需要统计不同 database 下 status 序列的数据点个数，使用以下查询：
+
+```sql
+select count(status) from root.** group by level = 1
+```
+
+运行结果为：
+
+```
++-------------------------+---------------------------+
+|count(root.ln.*.*.status)|count(root.sgcc.*.*.status)|
++-------------------------+---------------------------+
+| 20160| 10080|
++-------------------------+---------------------------+
+Total line number = 1
+It costs 0.003s
+```
+
+**示例2：** 统计不同设备下 status 序列的数据点个数，可以规定 level = 3，
+
+```sql
+select count(status) from root.** group by level = 3
+```
+
+运行结果为：
+
+```
++---------------------------+---------------------------+
+|count(root.*.*.wt01.status)|count(root.*.*.wt02.status)|
++---------------------------+---------------------------+
+| 20160| 10080|
++---------------------------+---------------------------+
+Total line number = 1
+It costs 0.003s
+```
+
+注意，这时会将 database `ln` 和 `sgcc` 下名为 `wt01` 的设备视为同名设备聚合在一起。
+
+**示例3：** 统计不同 database 下的不同设备中 status 序列的数据点个数，可以使用以下查询：
+
+```sql
+select count(status) from root.** group by level = 1, 3
+```
+
+运行结果为：
+
+```
++----------------------------+----------------------------+------------------------------+
+|count(root.ln.*.wt01.status)|count(root.ln.*.wt02.status)|count(root.sgcc.*.wt01.status)|
++----------------------------+----------------------------+------------------------------+
+| 10080| 10080| 10080|
++----------------------------+----------------------------+------------------------------+
+Total line number = 1
+It costs 0.003s
+```
+
+**示例4：** 查询所有序列下温度传感器 temperature 的最大值，可以使用下列查询语句：
+
+```sql
+select max_value(temperature) from root.** group by level = 0
+```
+
+运行结果：
+
+```
++---------------------------------+
+|max_value(root.*.*.*.temperature)|
++---------------------------------+
+| 26.0|
++---------------------------------+
+Total line number = 1
+It costs 0.013s
+```
+
+**示例5：** 上面的查询都是针对某一个传感器，特别地，**如果想要查询某一层级下所有传感器拥有的总数据点数，则需要显式规定测点为 `*`**
+
+```sql
+select count(*) from root.ln.** group by level = 2
+```
+
+运行结果：
+
+```
++----------------------+----------------------+
+|count(root.*.wf01.*.*)|count(root.*.wf02.*.*)|
++----------------------+----------------------+
+| 20160| 20160|
++----------------------+----------------------+
+Total line number = 1
+It costs 0.013s
+```
+
+##### 与时间区间分段聚合混合使用
+
+通过定义 LEVEL 来统计指定层级下的数据点个数。
+
+例如：
+
+统计降采样后的数据点个数
+
+```sql
+select count(status) from root.ln.wf01.wt01 group by ((2017-11-01T00:00:00, 2017-11-07T23:00:00],1d), level=1;
+```
+
+结果：
+
+```
++-----------------------------+-------------------------+
+| Time|COUNT(root.ln.*.*.status)|
++-----------------------------+-------------------------+
+|2017-11-02T00:00:00.000+08:00| 1440|
+|2017-11-03T00:00:00.000+08:00| 1440|
+|2017-11-04T00:00:00.000+08:00| 1440|
+|2017-11-05T00:00:00.000+08:00| 1440|
+|2017-11-06T00:00:00.000+08:00| 1440|
+|2017-11-07T00:00:00.000+08:00| 1440|
+|2017-11-07T23:00:00.000+08:00| 1380|
++-----------------------------+-------------------------+
+Total line number = 7
+It costs 0.006s
+```
+
+加上滑动 Step 的降采样后的结果也可以汇总
+
+```sql
+select count(status) from root.ln.wf01.wt01 group by ([2017-11-01 00:00:00, 2017-11-07 23:00:00), 3h, 1d), level=1;
+```
+
+```
++-----------------------------+-------------------------+
+| Time|COUNT(root.ln.*.*.status)|
++-----------------------------+-------------------------+
+|2017-11-01T00:00:00.000+08:00| 180|
+|2017-11-02T00:00:00.000+08:00| 180|
+|2017-11-03T00:00:00.000+08:00| 180|
+|2017-11-04T00:00:00.000+08:00| 180|
+|2017-11-05T00:00:00.000+08:00| 180|
+|2017-11-06T00:00:00.000+08:00| 180|
+|2017-11-07T00:00:00.000+08:00| 180|
++-----------------------------+-------------------------+
+Total line number = 7
+It costs 0.004s
+```
+
+#### 标签分组聚合
+
+IoTDB 支持通过 `GROUP BY TAGS` 语句根据时间序列中定义的标签的键值做分组聚合查询。
+
+我们先在 IoTDB 中写入如下示例数据，稍后会以这些数据为例介绍标签聚合查询。
+
+这些是某工厂 `factory1` 在多个城市的多个车间的设备温度数据， 时间范围为 [1000, 10000)。
+
+时间序列路径中的设备一级是设备唯一标识。城市信息 `city` 和车间信息 `workshop` 则被建模在该设备时间序列的标签中。
+其中，设备 `d1`、`d2` 在 `Beijing` 的 `w1` 车间， `d3`、`d4` 在 `Beijing` 的 `w2` 车间，`d5`、`d6` 在 `Shanghai` 的 `w1` 车间，`d7` 在 `Shanghai` 的 `w2` 车间。
+`d8` 和 `d9` 设备目前处于调试阶段，还未被分配到具体的城市和车间，所以其相应的标签值为空值。
+
+```SQL
+create database root.factory1;
+create timeseries root.factory1.d1.temperature with datatype=FLOAT tags(city=Beijing, workshop=w1);
+create timeseries root.factory1.d2.temperature with datatype=FLOAT tags(city=Beijing, workshop=w1);
+create timeseries root.factory1.d3.temperature with datatype=FLOAT tags(city=Beijing, workshop=w2);
+create timeseries root.factory1.d4.temperature with datatype=FLOAT tags(city=Beijing, workshop=w2);
+create timeseries root.factory1.d5.temperature with datatype=FLOAT tags(city=Shanghai, workshop=w1);
+create timeseries root.factory1.d6.temperature with datatype=FLOAT tags(city=Shanghai, workshop=w1);
+create timeseries root.factory1.d7.temperature with datatype=FLOAT tags(city=Shanghai, workshop=w2);
+create timeseries root.factory1.d8.temperature with datatype=FLOAT;
+create timeseries root.factory1.d9.temperature with datatype=FLOAT;
+
+insert into root.factory1.d1(time, temperature) values(1000, 104.0);
+insert into root.factory1.d1(time, temperature) values(3000, 104.2);
+insert into root.factory1.d1(time, temperature) values(5000, 103.3);
+insert into root.factory1.d1(time, temperature) values(7000, 104.1);
+
+insert into root.factory1.d2(time, temperature) values(1000, 104.4);
+insert into root.factory1.d2(time, temperature) values(3000, 103.7);
+insert into root.factory1.d2(time, temperature) values(5000, 103.3);
+insert into root.factory1.d2(time, temperature) values(7000, 102.9);
+
+insert into root.factory1.d3(time, temperature) values(1000, 103.9);
+insert into root.factory1.d3(time, temperature) values(3000, 103.8);
+insert into root.factory1.d3(time, temperature) values(5000, 102.7);
+insert into root.factory1.d3(time, temperature) values(7000, 106.9);
+
+insert into root.factory1.d4(time, temperature) values(1000, 103.9);
+insert into root.factory1.d4(time, temperature) values(5000, 102.7);
+insert into root.factory1.d4(time, temperature) values(7000, 106.9);
+
+insert into root.factory1.d5(time, temperature) values(1000, 112.9);
+insert into root.factory1.d5(time, temperature) values(7000, 113.0);
+
+insert into root.factory1.d6(time, temperature) values(1000, 113.9);
+insert into root.factory1.d6(time, temperature) values(3000, 113.3);
+insert into root.factory1.d6(time, temperature) values(5000, 112.7);
+insert into root.factory1.d6(time, temperature) values(7000, 112.3);
+
+insert into root.factory1.d7(time, temperature) values(1000, 101.2);
+insert into root.factory1.d7(time, temperature) values(3000, 99.3);
+insert into root.factory1.d7(time, temperature) values(5000, 100.1);
+insert into root.factory1.d7(time, temperature) values(7000, 99.8);
+
+insert into root.factory1.d8(time, temperature) values(1000, 50.0);
+insert into root.factory1.d8(time, temperature) values(3000, 52.1);
+insert into root.factory1.d8(time, temperature) values(5000, 50.1);
+insert into root.factory1.d8(time, temperature) values(7000, 50.5);
+
+insert into root.factory1.d9(time, temperature) values(1000, 50.3);
+insert into root.factory1.d9(time, temperature) values(3000, 52.1);
+```
+
+##### 单标签聚合查询
+
+用户想统计该工厂每个地区的设备的温度的平均值，可以使用如下查询语句
+
+```SQL
+SELECT AVG(temperature) FROM root.factory1.** GROUP BY TAGS(city);
+```
+
+该查询会将具有同一个 `city` 标签值的时间序列的所有满足查询条件的点做平均值计算，计算结果如下
+
+```
++--------+------------------+
+| city| avg(temperature)|
++--------+------------------+
+| Beijing|104.04666697184244|
+|Shanghai|107.85000076293946|
+| NULL| 50.84999910990397|
++--------+------------------+
+Total line number = 3
+It costs 0.231s
+```
+
+从结果集中可以看到，和分段聚合、按层次分组聚合相比，标签聚合的查询结果的不同点是：
+1. 标签聚合查询的聚合结果不会再做去星号展开，而是将多个时间序列的数据作为一个整体进行聚合计算。
+2. 标签聚合查询除了输出聚合结果列，还会输出聚合标签的键值列。该列的列名为聚合指定的标签键，列的值则为所有查询的时间序列中出现的该标签的值。
+如果某些时间序列未设置该标签，则在键值列中有一行单独的 `NULL` ，代表未设置标签的所有时间序列数据的聚合结果。
+
+##### 多标签分组聚合查询
+
+除了基本的单标签聚合查询外，还可以按顺序指定多个标签进行聚合计算。
+
+例如，用户想统计每个城市的每个车间内设备的平均温度。但因为各个城市的车间名称有可能相同，所以不能直接按照 `workshop` 做标签聚合。必须要先按照城市，再按照车间处理。
+
+SQL 语句如下
+
+```SQL
+SELECT avg(temperature) FROM root.factory1.** GROUP BY TAGS(city, workshop);
+```
+
+查询结果如下
+
+```
++--------+--------+------------------+
+| city|workshop| avg(temperature)|
++--------+--------+------------------+
+| NULL| NULL| 50.84999910990397|
+|Shanghai| w1|113.01666768391927|
+| Beijing| w2| 104.4000004359654|
+|Shanghai| w2|100.10000038146973|
+| Beijing| w1|103.73750019073486|
++--------+--------+------------------+
+Total line number = 5
+It costs 0.027s
+```
+
+从结果集中可以看到，和单标签聚合相比，多标签聚合的查询结果会根据指定的标签顺序，输出相应标签的键值列。
+
+##### 基于时间区间的标签聚合查询
+
+按照时间区间聚合是时序数据库中最常用的查询需求之一。IoTDB 在基于时间区间的聚合基础上，支持进一步按照标签进行聚合查询。
+
+例如，用户想统计时间 `[1000, 10000)` 范围内，每个城市每个车间中的设备每 5 秒内的平均温度。
+
+SQL 语句如下
+
+```SQL
+SELECT AVG(temperature) FROM root.factory1.** GROUP BY ([1000, 10000), 5s), TAGS(city, workshop);
+```
+
+查询结果如下
+
+```
++-----------------------------+--------+--------+------------------+
+| Time| city|workshop| avg(temperature)|
++-----------------------------+--------+--------+------------------+
+|1970-01-01T08:00:01.000+08:00| NULL| NULL| 50.91999893188476|
+|1970-01-01T08:00:01.000+08:00|Shanghai| w1|113.20000076293945|
+|1970-01-01T08:00:01.000+08:00| Beijing| w2| 103.4|
+|1970-01-01T08:00:01.000+08:00|Shanghai| w2| 100.1999994913737|
+|1970-01-01T08:00:01.000+08:00| Beijing| w1|103.81666692097981|
+|1970-01-01T08:00:06.000+08:00| NULL| NULL| 50.5|
+|1970-01-01T08:00:06.000+08:00|Shanghai| w1| 112.6500015258789|
+|1970-01-01T08:00:06.000+08:00| Beijing| w2| 106.9000015258789|
+|1970-01-01T08:00:06.000+08:00|Shanghai| w2| 99.80000305175781|
+|1970-01-01T08:00:06.000+08:00| Beijing| w1| 103.5|
++-----------------------------+--------+--------+------------------+
+```
+
+和标签聚合相比，基于时间区间的标签聚合的查询会首先按照时间区间划定聚合范围，在时间区间内部再根据指定的标签顺序，进行相应数据的聚合计算。在输出的结果集中，会包含一列时间列，该时间列值的含义和时间区间聚合查询的相同。
+
+##### 标签分组聚合的限制
+
+由于标签聚合功能仍然处于开发阶段，目前有如下未实现功能。
+
+> 1. 暂不支持 `HAVING` 子句过滤查询结果。
+> 2. 暂不支持结果按照标签值排序。
+> 3. 暂不支持 `LIMIT`，`OFFSET`，`SLIMIT`，`SOFFSET`。
+> 4. 暂不支持 `ALIGN BY DEVICE`。
+> 5. 暂不支持聚合函数内部包含表达式，例如 `count(s+1)`。
+> 6. 不支持值过滤条件聚合，和分层聚合查询行为保持一致。