Add count_time aggregation usage doc (#73)
diff --git a/src/UserGuide/Master/stage/Operators-Functions/Aggregation.md b/src/UserGuide/Master/stage/Operators-Functions/Aggregation.md index 332e2cd..201fa0b 100644 --- a/src/UserGuide/Master/stage/Operators-Functions/Aggregation.md +++ b/src/UserGuide/Master/stage/Operators-Functions/Aggregation.md
@@ -27,21 +27,23 @@ The aggregate functions supported by IoTDB are as follows: -| Function Name | Description | Allowed Input Series Data Types | Required Attributes | Output Series Data Type | -| ------------- | ------------------------------------------------------------ | ------------------------------- | ------------------------------------------------------------ | ----------------------------------- | -| SUM | Summation. | INT32 INT64 FLOAT DOUBLE | / | DOUBLE | -| COUNT | Counts the number of data points. | All data types | / | INT | -| AVG | Average. | INT32 INT64 FLOAT DOUBLE | / | DOUBLE | -| EXTREME | Finds the value with the largest absolute value. Returns a positive value if the maximum absolute value of positive and negative values is equal. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | -| MAX_VALUE | Find the maximum value. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | -| MIN_VALUE | Find the minimum value. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | -| FIRST_VALUE | Find the value with the smallest timestamp. | All data types | / | Consistent with input data type | -| LAST_VALUE | Find the value with the largest timestamp. | All data types | / | Consistent with input data type | -| MAX_TIME | Find the maximum timestamp. | All data Types | / | Timestamp | -| MIN_TIME | Find the minimum timestamp. | All data Types | / | Timestamp | -| COUNT_IF | Find the number of data points that continuously meet a given condition and the number of data points that meet the condition (represented by keep) meet the specified threshold. | BOOLEAN | `[keep >=/>/=/!=/</<=]threshold`：The specified threshold or threshold condition, it is equivalent to `keep >= threshold` if `threshold` is used alone, type of `threshold` is `INT64` `ignoreNull`：Optional, default value is `true`；If the value is `true`, null values are ignored, it means that if there is a null value in the middle, the value is ignored without interrupting the continuity. If the value is `true`, null values are not ignored, it means that if there are null values in the middle, continuity will be broken | INT64 | -| TIME_DURATION | Find the difference between the timestamp of the largest non-null value and the timestamp of the smallest non-null value in a column | All data Types | / | INT64 | -| MODE | Find the mode. Note: 1.Having too many different values in the input series risks a memory exception; 2.If all the elements have the same number of occurrences, that is no Mode, return the value with earliest time; 3.If there are many Modes, return the Mode with earliest time. | All data Types | / | Consistent with the input data type | +| Function Name | Description | Allowed Input Series Data Types | Required Attributes | Output Series Data Type | +| ------------- | ------------------------------------------------------------ |-----------------------------------------------------| ------------------------------------------------------------ | ----------------------------------- | +| SUM | Summation. | INT32 INT64 FLOAT DOUBLE | / | DOUBLE | +| COUNT | Counts the number of data points. | All data types | / | INT | +| AVG | Average. | INT32 INT64 FLOAT DOUBLE | / | DOUBLE | +| EXTREME | Finds the value with the largest absolute value. Returns a positive value if the maximum absolute value of positive and negative values is equal. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | +| MAX_VALUE | Find the maximum value. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | +| MIN_VALUE | Find the minimum value. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | +| FIRST_VALUE | Find the value with the smallest timestamp. | All data types | / | Consistent with input data type | +| LAST_VALUE | Find the value with the largest timestamp. | All data types | / | Consistent with input data type | +| MAX_TIME | Find the maximum timestamp. | All data Types | / | Timestamp | +| MIN_TIME | Find the minimum timestamp. | All data Types | / | Timestamp | +| COUNT_IF | Find the number of data points that continuously meet a given condition and the number of data points that meet the condition (represented by keep) meet the specified threshold. | BOOLEAN | `[keep >=/>/=/!=/</<=]threshold`：The specified threshold or threshold condition, it is equivalent to `keep >= threshold` if `threshold` is used alone, type of `threshold` is `INT64` `ignoreNull`：Optional, default value is `true`；If the value is `true`, null values are ignored, it means that if there is a null value in the middle, the value is ignored without interrupting the continuity. If the value is `true`, null values are not ignored, it means that if there are null values in the middle, continuity will be broken | INT64 | +| TIME_DURATION | Find the difference between the timestamp of the largest non-null value and the timestamp of the smallest non-null value in a column | All data Types | / | INT64 | +| MODE | Find the mode. Note: 1.Having too many different values in the input series risks a memory exception; 2.If all the elements have the same number of occurrences, that is no Mode, return the value with earliest time; 3.If there are many Modes, return the Mode with earliest time. | All data Types | / | Consistent with the input data type | +| COUNT_TIME | The number of timestamps in the query data set. When used with `align by device`, the result is the number of timestamps in the data set per device. | All data Types, the input parameter can only be `*` | / | INT64 | + ## COUNT @@ -179,4 +181,113 @@ | 1677570933| +----------------------------+ ``` -> Note: Returns 0 if there is only one data point, or null if the data point is null. \ No newline at end of file +> Note: Returns 0 if there is only one data point, or null if the data point is null. + +## COUNT_TIME +### Grammar +```sql + count_time(*) +``` +### Example +#### raw data +``` ++----------+-------------+-------------+-------------+-------------+ +| Time|root.db.d1.s1|root.db.d1.s2|root.db.d2.s1|root.db.d2.s2| ++----------+-------------+-------------+-------------+-------------+ +| 0| 0| null| null| 0| +| 1| null| 1| 1| null| +| 2| null| 2| 2| null| +| 4| 4| null| null| 4| +| 5| 5| 5| 5| 5| +| 7| null| 7| 7| null| +| 8| 8| 8| 8| 8| +| 9| null| 9| null| null| ++----------+-------------+-------------+-------------+-------------+ +``` +#### Insert sql +```sql +CREATE DATABASE root.db; +CREATE TIMESERIES root.db.d1.s1 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d1.s2 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d2.s1 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d2.s2 WITH DATATYPE=INT32, ENCODING=PLAIN; +INSERT INTO root.db.d1(time, s1) VALUES(0, 0), (4,4), (5,5), (8,8); +INSERT INTO root.db.d1(time, s2) VALUES(1, 1), (2,2), (5,5), (7,7), (8,8), (9,9); +INSERT INTO root.db.d2(time, s1) VALUES(1, 1), (2,2), (5,5), (7,7), (8,8); +INSERT INTO root.db.d2(time, s2) VALUES(0, 0), (4,4), (5,5), (8,8); +``` + +Query-Example - 1: +```sql +select count_time(*) from root.db.** +``` + +Result +``` ++-------------+ +|count_time(*)| ++-------------+ +| 8| ++-------------+ +``` + +Query-Example - 2: +```sql +select count_time(*) from root.db.d1, root.db.d2 +``` + +Result +``` ++-------------+ +|count_time(*)| ++-------------+ +| 8| ++-------------+ +``` + +Query-Example - 3: +```sql +select count_time(*) from root.db.** group by([0, 10), 2ms) +``` + +Result +``` ++-----------------------------+-------------+ +| Time|count_time(*)| ++-----------------------------+-------------+ +|1970-01-01T08:00:00.000+08:00| 2| +|1970-01-01T08:00:00.002+08:00| 1| +|1970-01-01T08:00:00.004+08:00| 2| +|1970-01-01T08:00:00.006+08:00| 1| +|1970-01-01T08:00:00.008+08:00| 2| ++-----------------------------+-------------+ +``` + +Query-Example - 4: +```sql +select count_time(*) from root.db.** group by([0, 10), 2ms) align by device +``` + +Result +``` ++-----------------------------+----------+-------------+ +| Time| Device|count_time(*)| ++-----------------------------+----------+-------------+ +|1970-01-01T08:00:00.000+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.002+08:00|root.db.d1| 1| +|1970-01-01T08:00:00.004+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.006+08:00|root.db.d1| 1| +|1970-01-01T08:00:00.008+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.000+08:00|root.db.d2| 2| +|1970-01-01T08:00:00.002+08:00|root.db.d2| 1| +|1970-01-01T08:00:00.004+08:00|root.db.d2| 2| +|1970-01-01T08:00:00.006+08:00|root.db.d2| 1| +|1970-01-01T08:00:00.008+08:00|root.db.d2| 1| ++-----------------------------+----------+-------------+ +``` + +> Note: +> 1. The parameter in count_time can only be *. +> 2. Count_time aggregation cannot be used with other aggregation functions. +> 3. Count_time aggregation used with having statement is not supported, and count_time aggregation can not appear in the having statement. +> 4. Count_time does not support use with group by level, group by tag. \ No newline at end of file
diff --git a/src/UserGuide/V1.2.x/stage/Operators-Functions/Aggregation.md b/src/UserGuide/V1.2.x/stage/Operators-Functions/Aggregation.md index 332e2cd..385aefc 100644 --- a/src/UserGuide/V1.2.x/stage/Operators-Functions/Aggregation.md +++ b/src/UserGuide/V1.2.x/stage/Operators-Functions/Aggregation.md
@@ -27,21 +27,23 @@ The aggregate functions supported by IoTDB are as follows: -| Function Name | Description | Allowed Input Series Data Types | Required Attributes | Output Series Data Type | -| ------------- | ------------------------------------------------------------ | ------------------------------- | ------------------------------------------------------------ | ----------------------------------- | -| SUM | Summation. | INT32 INT64 FLOAT DOUBLE | / | DOUBLE | -| COUNT | Counts the number of data points. | All data types | / | INT | -| AVG | Average. | INT32 INT64 FLOAT DOUBLE | / | DOUBLE | -| EXTREME | Finds the value with the largest absolute value. Returns a positive value if the maximum absolute value of positive and negative values is equal. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | -| MAX_VALUE | Find the maximum value. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | -| MIN_VALUE | Find the minimum value. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | -| FIRST_VALUE | Find the value with the smallest timestamp. | All data types | / | Consistent with input data type | -| LAST_VALUE | Find the value with the largest timestamp. | All data types | / | Consistent with input data type | -| MAX_TIME | Find the maximum timestamp. | All data Types | / | Timestamp | -| MIN_TIME | Find the minimum timestamp. | All data Types | / | Timestamp | -| COUNT_IF | Find the number of data points that continuously meet a given condition and the number of data points that meet the condition (represented by keep) meet the specified threshold. | BOOLEAN | `[keep >=/>/=/!=/</<=]threshold`：The specified threshold or threshold condition, it is equivalent to `keep >= threshold` if `threshold` is used alone, type of `threshold` is `INT64` `ignoreNull`：Optional, default value is `true`；If the value is `true`, null values are ignored, it means that if there is a null value in the middle, the value is ignored without interrupting the continuity. If the value is `true`, null values are not ignored, it means that if there are null values in the middle, continuity will be broken | INT64 | -| TIME_DURATION | Find the difference between the timestamp of the largest non-null value and the timestamp of the smallest non-null value in a column | All data Types | / | INT64 | -| MODE | Find the mode. Note: 1.Having too many different values in the input series risks a memory exception; 2.If all the elements have the same number of occurrences, that is no Mode, return the value with earliest time; 3.If there are many Modes, return the Mode with earliest time. | All data Types | / | Consistent with the input data type | +| Function Name | Description | Allowed Input Series Data Types | Required Attributes | Output Series Data Type | +| ------------- |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------------------------------| +| SUM | Summation. | INT32 INT64 FLOAT DOUBLE | / | DOUBLE | +| COUNT | Counts the number of data points. | All data types | / | INT | +| AVG | Average. | INT32 INT64 FLOAT DOUBLE | / | DOUBLE | +| EXTREME | Finds the value with the largest absolute value. Returns a positive value if the maximum absolute value of positive and negative values is equal. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | +| MAX_VALUE | Find the maximum value. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | +| MIN_VALUE | Find the minimum value. | INT32 INT64 FLOAT DOUBLE | / | Consistent with the input data type | +| FIRST_VALUE | Find the value with the smallest timestamp. | All data types | / | Consistent with input data type | +| LAST_VALUE | Find the value with the largest timestamp. | All data types | / | Consistent with input data type | +| MAX_TIME | Find the maximum timestamp. | All data Types | / | Timestamp | +| MIN_TIME | Find the minimum timestamp. | All data Types | / | Timestamp | +| COUNT_IF | Find the number of data points that continuously meet a given condition and the number of data points that meet the condition (represented by keep) meet the specified threshold. | BOOLEAN | `[keep >=/>/=/!=/</<=]threshold`：The specified threshold or threshold condition, it is equivalent to `keep >= threshold` if `threshold` is used alone, type of `threshold` is `INT64` `ignoreNull`：Optional, default value is `true`；If the value is `true`, null values are ignored, it means that if there is a null value in the middle, the value is ignored without interrupting the continuity. If the value is `true`, null values are not ignored, it means that if there are null values in the middle, continuity will be broken | INT64 | +| TIME_DURATION | Find the difference between the timestamp of the largest non-null value and the timestamp of the smallest non-null value in a column | All data Types | / | INT64 | +| MODE | Find the mode. Note: 1.Having too many different values in the input series risks a memory exception; 2.If all the elements have the same number of occurrences, that is no Mode, return the value with earliest time; 3.If there are many Modes, return the Mode with earliest time. | All data Types | / | Consistent with the input data type | +| COUNT_TIME | The number of timestamps in the query data set. When used with `align by device`, the result is the number of timestamps in the data set per device. | All data Types, the input parameter can only be `*` | / | INT64 | + ## COUNT @@ -179,4 +181,113 @@ | 1677570933| +----------------------------+ ``` -> Note: Returns 0 if there is only one data point, or null if the data point is null. \ No newline at end of file +> Note: Returns 0 if there is only one data point, or null if the data point is null. + +## COUNT_TIME +### Grammar +```sql + count_time(*) +``` +### Example +#### raw data +``` ++----------+-------------+-------------+-------------+-------------+ +| Time|root.db.d1.s1|root.db.d1.s2|root.db.d2.s1|root.db.d2.s2| ++----------+-------------+-------------+-------------+-------------+ +| 0| 0| null| null| 0| +| 1| null| 1| 1| null| +| 2| null| 2| 2| null| +| 4| 4| null| null| 4| +| 5| 5| 5| 5| 5| +| 7| null| 7| 7| null| +| 8| 8| 8| 8| 8| +| 9| null| 9| null| null| ++----------+-------------+-------------+-------------+-------------+ +``` +#### Insert sql +```sql +CREATE DATABASE root.db; +CREATE TIMESERIES root.db.d1.s1 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d1.s2 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d2.s1 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d2.s2 WITH DATATYPE=INT32, ENCODING=PLAIN; +INSERT INTO root.db.d1(time, s1) VALUES(0, 0), (4,4), (5,5), (8,8); +INSERT INTO root.db.d1(time, s2) VALUES(1, 1), (2,2), (5,5), (7,7), (8,8), (9,9); +INSERT INTO root.db.d2(time, s1) VALUES(1, 1), (2,2), (5,5), (7,7), (8,8); +INSERT INTO root.db.d2(time, s2) VALUES(0, 0), (4,4), (5,5), (8,8); +``` + +Query-Example - 1: +```sql +select count_time(*) from root.db.** +``` + +Result +``` ++-------------+ +|count_time(*)| ++-------------+ +| 8| ++-------------+ +``` + +Query-Example - 2: +```sql +select count_time(*) from root.db.d1, root.db.d2 +``` + +Result +``` ++-------------+ +|count_time(*)| ++-------------+ +| 8| ++-------------+ +``` + +Query-Example - 3: +```sql +select count_time(*) from root.db.** group by([0, 10), 2ms) +``` + +Result +``` ++-----------------------------+-------------+ +| Time|count_time(*)| ++-----------------------------+-------------+ +|1970-01-01T08:00:00.000+08:00| 2| +|1970-01-01T08:00:00.002+08:00| 1| +|1970-01-01T08:00:00.004+08:00| 2| +|1970-01-01T08:00:00.006+08:00| 1| +|1970-01-01T08:00:00.008+08:00| 2| ++-----------------------------+-------------+ +``` + +Query-Example - 4: +```sql +select count_time(*) from root.db.** group by([0, 10), 2ms) align by device +``` + +Result +``` ++-----------------------------+----------+-------------+ +| Time| Device|count_time(*)| ++-----------------------------+----------+-------------+ +|1970-01-01T08:00:00.000+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.002+08:00|root.db.d1| 1| +|1970-01-01T08:00:00.004+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.006+08:00|root.db.d1| 1| +|1970-01-01T08:00:00.008+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.000+08:00|root.db.d2| 2| +|1970-01-01T08:00:00.002+08:00|root.db.d2| 1| +|1970-01-01T08:00:00.004+08:00|root.db.d2| 2| +|1970-01-01T08:00:00.006+08:00|root.db.d2| 1| +|1970-01-01T08:00:00.008+08:00|root.db.d2| 1| ++-----------------------------+----------+-------------+ +``` + +> Note: +> 1. The parameter in count_time can only be *. +> 2. Count_time aggregation cannot be used with other aggregation functions. +> 3. Count_time aggregation used with having statement is not supported, and count_time aggregation can not appear in the having statement. +> 4. Count_time does not support use with group by level, group by tag. \ No newline at end of file
diff --git a/src/zh/UserGuide/Master/stage/Operators-Functions/Aggregation.md b/src/zh/UserGuide/Master/stage/Operators-Functions/Aggregation.md index 5c689b6..7d4366e 100644 --- a/src/zh/UserGuide/Master/stage/Operators-Functions/Aggregation.md +++ b/src/zh/UserGuide/Master/stage/Operators-Functions/Aggregation.md
@@ -40,8 +40,11 @@ | MAX_TIME | 求最大时间戳。 | 所有类型 | 无 | Timestamp | | MIN_TIME | 求最小时间戳。 | 所有类型 | 无 | Timestamp | | COUNT_IF | 求数据点连续满足某一给定条件，且满足条件的数据点个数（用keep表示）满足指定阈值的次数。 | BOOLEAN | `[keep >=/>/=/!=/</<=]threshold`：被指定的阈值或阈值条件，若只使用`threshold`则等价于`keep >= threshold`,`threshold`类型为`INT64` <br> `ignoreNull`：可选，默认为`true`；为`true`表示忽略null值，即如果中间出现null值，直接忽略，不会打断连续性；为`false`表示不忽略null值，即如果中间出现null值，会打断连续性 | INT64 | -| TIME_DURATION | 求某一列最大一个不为NULL的值所在时间戳与最小一个不为NULL的值所在时间戳的时间戳差 | 所有类型 | 无 | INT64 | -| MODE | 求众数。注意：<br>1.输入序列的不同值个数过多时会有内存异常风险; <br>2.如果所有元素出现的频次相同，即没有众数，则返回对应时间戳最小的值; <br>3.如果有多个众数，则返回对应时间戳最小的众数。 | 所有类型 | 无 | 与输入类型一致 | +| TIME_DURATION | 求某一列最大一个不为NULL的值所在时间戳与最小一个不为NULL的值所在时间戳的时间戳差 | 所有类型 | 无 | INT64 | +| MODE | 求众数。注意：<br>1.输入序列的不同值个数过多时会有内存异常风险; <br>2.如果所有元素出现的频次相同，即没有众数，则返回对应时间戳最小的值; <br>3.如果有多个众数，则返回对应时间戳最小的众数。 | 所有类型 | 无 | 与输入类型一致 | +| COUNT_TIME | 查询结果集的时间戳的数量。与 align by device 搭配使用时，得到的结果是每个设备的结果集的时间戳的数量。 | 所有类型，输入参数只能为* | 无 | INT64 | + + ## COUNT_IF ### 语法 @@ -160,3 +163,113 @@ +----------------------------+ ``` > 注：若数据点只有一个，则返回0，若数据点为null，则返回null。 + +## COUNT_TIME +### 语法 +```sql + count_time(*) +``` +### 使用示例 +#### 准备数据 +``` ++----------+-------------+-------------+-------------+-------------+ +| Time|root.db.d1.s1|root.db.d1.s2|root.db.d2.s1|root.db.d2.s2| ++----------+-------------+-------------+-------------+-------------+ +| 0| 0| null| null| 0| +| 1| null| 1| 1| null| +| 2| null| 2| 2| null| +| 4| 4| null| null| 4| +| 5| 5| 5| 5| 5| +| 7| null| 7| 7| null| +| 8| 8| 8| 8| 8| +| 9| null| 9| null| null| ++----------+-------------+-------------+-------------+-------------+ +``` +#### 写入语句 +```sql +CREATE DATABASE root.db; +CREATE TIMESERIES root.db.d1.s1 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d1.s2 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d2.s1 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d2.s2 WITH DATATYPE=INT32, ENCODING=PLAIN; +INSERT INTO root.db.d1(time, s1) VALUES(0, 0), (4,4), (5,5), (8,8); +INSERT INTO root.db.d1(time, s2) VALUES(1, 1), (2,2), (5,5), (7,7), (8,8), (9,9); +INSERT INTO root.db.d2(time, s1) VALUES(1, 1), (2,2), (5,5), (7,7), (8,8); +INSERT INTO root.db.d2(time, s2) VALUES(0, 0), (4,4), (5,5), (8,8); +``` + +查询示例1： +```sql +select count_time(*) from root.db.** +``` + +输出 +``` ++-------------+ +|count_time(*)| ++-------------+ +| 8| ++-------------+ +``` + +查询示例2： +```sql +select count_time(*) from root.db.d1, root.db.d2 +``` + +输出 +``` ++-------------+ +|count_time(*)| ++-------------+ +| 8| ++-------------+ +``` + +查询示例3： +```sql +select count_time(*) from root.db.** group by([0, 10), 2ms) +``` + +输出 +``` ++-----------------------------+-------------+ +| Time|count_time(*)| ++-----------------------------+-------------+ +|1970-01-01T08:00:00.000+08:00| 2| +|1970-01-01T08:00:00.002+08:00| 1| +|1970-01-01T08:00:00.004+08:00| 2| +|1970-01-01T08:00:00.006+08:00| 1| +|1970-01-01T08:00:00.008+08:00| 2| ++-----------------------------+-------------+ +``` + +查询示例4： +```sql +select count_time(*) from root.db.** group by([0, 10), 2ms) align by device +``` + +输出 +``` ++-----------------------------+----------+-------------+ +| Time| Device|count_time(*)| ++-----------------------------+----------+-------------+ +|1970-01-01T08:00:00.000+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.002+08:00|root.db.d1| 1| +|1970-01-01T08:00:00.004+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.006+08:00|root.db.d1| 1| +|1970-01-01T08:00:00.008+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.000+08:00|root.db.d2| 2| +|1970-01-01T08:00:00.002+08:00|root.db.d2| 1| +|1970-01-01T08:00:00.004+08:00|root.db.d2| 2| +|1970-01-01T08:00:00.006+08:00|root.db.d2| 1| +|1970-01-01T08:00:00.008+08:00|root.db.d2| 1| ++-----------------------------+----------+-------------+ + +``` + +> 注： +> 1. count_time里的表达式只能为*。 +> 2. count_time不能和其他的聚合函数一起使用。 +> 3. having语句里不支持使用count_time, 使用count_time聚合函数时不支持使用having语句。 +> 4. count_time不支持与group by level, group by tag一起使用。
diff --git a/src/zh/UserGuide/V1.2.x/stage/Operators-Functions/Aggregation.md b/src/zh/UserGuide/V1.2.x/stage/Operators-Functions/Aggregation.md index 5c689b6..dcf3def 100644 --- a/src/zh/UserGuide/V1.2.x/stage/Operators-Functions/Aggregation.md +++ b/src/zh/UserGuide/V1.2.x/stage/Operators-Functions/Aggregation.md
@@ -42,6 +42,9 @@ | COUNT_IF | 求数据点连续满足某一给定条件，且满足条件的数据点个数（用keep表示）满足指定阈值的次数。 | BOOLEAN | `[keep >=/>/=/!=/</<=]threshold`：被指定的阈值或阈值条件，若只使用`threshold`则等价于`keep >= threshold`,`threshold`类型为`INT64` <br> `ignoreNull`：可选，默认为`true`；为`true`表示忽略null值，即如果中间出现null值，直接忽略，不会打断连续性；为`false`表示不忽略null值，即如果中间出现null值，会打断连续性 | INT64 | | TIME_DURATION | 求某一列最大一个不为NULL的值所在时间戳与最小一个不为NULL的值所在时间戳的时间戳差 | 所有类型 | 无 | INT64 | | MODE | 求众数。注意：<br>1.输入序列的不同值个数过多时会有内存异常风险; <br>2.如果所有元素出现的频次相同，即没有众数，则返回对应时间戳最小的值; <br>3.如果有多个众数，则返回对应时间戳最小的众数。 | 所有类型 | 无 | 与输入类型一致 | +| COUNT_TIME | 查询结果集的时间戳的数量。与 align by device 搭配使用时，得到的结果是每个设备的结果集的时间戳的数量。 | 所有类型，输入参数只能为* | 无 | INT64 | + + ## COUNT_IF ### 语法 @@ -160,3 +163,113 @@ +----------------------------+ ``` > 注：若数据点只有一个，则返回0，若数据点为null，则返回null。 + +## COUNT_TIME +### 语法 +```sql + count_time(*) +``` +### 使用示例 +#### 准备数据 +``` ++----------+-------------+-------------+-------------+-------------+ +| Time|root.db.d1.s1|root.db.d1.s2|root.db.d2.s1|root.db.d2.s2| ++----------+-------------+-------------+-------------+-------------+ +| 0| 0| null| null| 0| +| 1| null| 1| 1| null| +| 2| null| 2| 2| null| +| 4| 4| null| null| 4| +| 5| 5| 5| 5| 5| +| 7| null| 7| 7| null| +| 8| 8| 8| 8| 8| +| 9| null| 9| null| null| ++----------+-------------+-------------+-------------+-------------+ +``` +#### 写入语句 +```sql +CREATE DATABASE root.db; +CREATE TIMESERIES root.db.d1.s1 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d1.s2 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d2.s1 WITH DATATYPE=INT32, ENCODING=PLAIN; +CREATE TIMESERIES root.db.d2.s2 WITH DATATYPE=INT32, ENCODING=PLAIN; +INSERT INTO root.db.d1(time, s1) VALUES(0, 0), (4,4), (5,5), (8,8); +INSERT INTO root.db.d1(time, s2) VALUES(1, 1), (2,2), (5,5), (7,7), (8,8), (9,9); +INSERT INTO root.db.d2(time, s1) VALUES(1, 1), (2,2), (5,5), (7,7), (8,8); +INSERT INTO root.db.d2(time, s2) VALUES(0, 0), (4,4), (5,5), (8,8); +``` + +查询示例1： +```sql +select count_time(*) from root.db.** +``` + +输出 +``` ++-------------+ +|count_time(*)| ++-------------+ +| 8| ++-------------+ +``` + +查询示例2： +```sql +select count_time(*) from root.db.d1, root.db.d2 +``` + +输出 +``` ++-------------+ +|count_time(*)| ++-------------+ +| 8| ++-------------+ +``` + +查询示例3： +```sql +select count_time(*) from root.db.** group by([0, 10), 2ms) +``` + +输出 +``` ++-----------------------------+-------------+ +| Time|count_time(*)| ++-----------------------------+-------------+ +|1970-01-01T08:00:00.000+08:00| 2| +|1970-01-01T08:00:00.002+08:00| 1| +|1970-01-01T08:00:00.004+08:00| 2| +|1970-01-01T08:00:00.006+08:00| 1| +|1970-01-01T08:00:00.008+08:00| 2| ++-----------------------------+-------------+ +``` + +查询示例4： +```sql +select count_time(*) from root.db.** group by([0, 10), 2ms) align by device +``` + +输出 +``` ++-----------------------------+----------+-------------+ +| Time| Device|count_time(*)| ++-----------------------------+----------+-------------+ +|1970-01-01T08:00:00.000+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.002+08:00|root.db.d1| 1| +|1970-01-01T08:00:00.004+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.006+08:00|root.db.d1| 1| +|1970-01-01T08:00:00.008+08:00|root.db.d1| 2| +|1970-01-01T08:00:00.000+08:00|root.db.d2| 2| +|1970-01-01T08:00:00.002+08:00|root.db.d2| 1| +|1970-01-01T08:00:00.004+08:00|root.db.d2| 2| +|1970-01-01T08:00:00.006+08:00|root.db.d2| 1| +|1970-01-01T08:00:00.008+08:00|root.db.d2| 1| ++-----------------------------+----------+-------------+ + +``` + +> 注： +> 1. count_time里的表达式只能为*。 +> 2. count_time不能和其他的聚合函数一起使用。 +> 3. having语句里不支持使用count_time, 使用count_time聚合函数时不支持使用having语句。 +> 4. count_time不支持与group by level, group by tag一起使用。