This is an automated email from the ASF dual-hosted git repository. lancelly pushed a commit to branch max_min_by in repository https://gitbox.apache.org/repos/asf/iotdb-docs.git
commit 928841f362c6d3cc16d4c6df079393e129406b1a Author: lancelly <[email protected]> AuthorDate: Wed Feb 28 16:29:08 2024 +0800 docs --- .../Master/User-Manual/Operator-and-Expression.md | 3 + .../stage/Operators-Functions/Aggregation.md | 101 ++++++++++++++- .../latest/User-Manual/Operator-and-Expression.md | 30 ++--- .../stage/Operators-Functions/Aggregation.md | 133 +++++++++++++++++--- .../Master/Reference/Function-and-Expression.md | 135 ++++++++++++++++++--- .../Master/User-Manual/Operator-and-Expression.md | 26 ++-- .../latest/Reference/Function-and-Expression.md | 100 +++++++++++++++ .../latest/User-Manual/Operator-and-Expression.md | 2 + 8 files changed, 470 insertions(+), 60 deletions(-) diff --git a/src/UserGuide/Master/User-Manual/Operator-and-Expression.md b/src/UserGuide/Master/User-Manual/Operator-and-Expression.md index 5d14be3..4454f44 100644 --- a/src/UserGuide/Master/User-Manual/Operator-and-Expression.md +++ b/src/UserGuide/Master/User-Manual/Operator-and-Expression.md @@ -114,6 +114,9 @@ The built-in functions can be used in IoTDB without registration, and the functi | 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 va [...] | 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 | +| MAX_BY | MAX_BY(x, y) returns the value of x corresponding to the maximum value of the input y. MAX_BY(time, x) returns the timestamp when x is at its maximum value. | The first input x can be of any type, while the second input y must be of type INT32, INT64, FLOAT, or DOUBLE. | Consistent with the data type of the first input x. | +| MIN_BY | MIN_BY(x, y) returns the value of x corresponding to the minimum value of the input y. MIN_BY(time, x) returns the timestamp when x is at its minimum value. | The first input x can be of any type, while the second input y must be of type INT32, INT64, FLOAT, or DOUBLE. | Consistent with the data type of the first input x. | + For details and examples, see the document [Aggregate Functions](../Operators-Functions/Aggregation.md). diff --git a/src/UserGuide/Master/stage/Operators-Functions/Aggregation.md b/src/UserGuide/Master/stage/Operators-Functions/Aggregation.md index 201fa0b..afcbada 100644 --- a/src/UserGuide/Master/stage/Operators-Functions/Aggregation.md +++ b/src/UserGuide/Master/stage/Operators-Functions/Aggregation.md @@ -43,6 +43,8 @@ The aggregate functions supported by IoTDB are as follows: | 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 `*` | / [...] +| MAX_BY | MAX_BY(x, y) returns the value of x corresponding to the maximum value of the input y. MAX_BY(time, x) returns the timestamp when x is at its maximum value. | The first input x can be of any type, while the second input y must be of type INT32, INT64, FLOAT, or DOUBLE. | / | Consistent with the data type of the first input x. | +| MIN_BY | MIN_BY(x, y) returns the value of x corresponding to the minimum value of the input y. MIN_BY(time, x) returns the timestamp when x is at its minimum value. | The first input x can be of any type, while the second input y must be of type INT32, INT64, FLOAT, or DOUBLE. | / [...] ## COUNT @@ -290,4 +292,101 @@ Result > 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 +> 4. Count_time does not support use with group by level, group by tag. + + +### MAX_BY +#### Function Definition +max_by(x, y): Returns the value of x at the timestamp when y is at its maximum. +- max_by must have two input parameters x and y. +- Both x and y can use the keyword time, with max_by(time, x) returning the timestamp when x is at its maximum value. +- If x is null at the timestamp corresponding to the maximum value of y, null is returned. +- If y reaches its maximum value at multiple timestamps, the x value corresponding to the smallest timestamp among those maximum values is returned. +- Consistent with IoTDB max_value, only INT32, INT64, FLOAT, DOUBLE are supported as inputs for y, while all six types are supported as inputs for x. +- Direct numerical values are not allowed as inputs for either x or y. + +#### Grammar +```sql +select max_by(x, y) from root.sg +select max_by(time, x) from root.sg +``` + +#### Examples + +##### Input Data +```sql +IoTDB> select * from root.test ++-----------------------------+-----------+-----------+ +| Time|root.test.a|root.test.b| ++-----------------------------+-----------+-----------+ +|1970-01-01T08:00:00.001+08:00| 1.0| 10.0| +|1970-01-01T08:00:00.002+08:00| 2.0| 10.0| +|1970-01-01T08:00:00.003+08:00| 3.0| 10.0| +|1970-01-01T08:00:00.004+08:00| 10.0| 10.0| +|1970-01-01T08:00:00.005+08:00| 10.0| 12.0| +|1970-01-01T08:00:00.006+08:00| 6.0| 6.0| ++-----------------------------+-----------+-----------+ +``` +##### Query Example +Querying the timestamp corresponding to the maximum value: +```sql +IoTDB> select max_by(time, a), max_value(a) from root.test ++-------------------------+------------------------+ +|max_by(Time, root.test.a)| max_value(root.test.a)| ++-------------------------+------------------------+ +| 4| 10.0| ++-------------------------+------------------------+ +``` + +Finding the value of b when a is at its maximum: +```sql +IoTDB> select max_by(b, a) from root.test ++--------------------------------+ +|max_by(root.test.b, root.test.a)| ++--------------------------------+ +| 10.0| ++--------------------------------+ +``` + +Using with expressions: +```sql +IoTDB> select max_by(b + 1, a * 2) from root.test ++----------------------------------------+ +|max_by(root.test.b + 1, root.test.a * 2)| ++----------------------------------------+ +| 11.0| ++----------------------------------------+ +``` + +Using with group by clause： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.000+08:00| 3.0| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +``` + +Using with having clause： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) having max_by(b, a) > 4.0 ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +``` +Using with order by clause： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) order by time desc ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.000+08:00| 3.0| ++-----------------------------+--------------------------------+ +``` \ No newline at end of file diff --git a/src/UserGuide/latest/User-Manual/Operator-and-Expression.md b/src/UserGuide/latest/User-Manual/Operator-and-Expression.md index 5d14be3..4ca8bf8 100644 --- a/src/UserGuide/latest/User-Manual/Operator-and-Expression.md +++ b/src/UserGuide/latest/User-Manual/Operator-and-Expression.md @@ -99,21 +99,23 @@ The built-in functions can be used in IoTDB without registration, and the functi ### Aggregate Functions -| 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 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 va [...] -| 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 | +| 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 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 o [...] +| 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 | +| MAX_BY | MAX_BY(x, y) returns the value of x corresponding to the maximum value of the input y. MAX_BY(time, x) returns the timestamp when x is at its maximum value. | The first input x can be of any type, while the second input y must be of type INT32, INT64, FLOAT, or DOUBLE. | Consistent with the data type of the first input x. | +| MIN_BY | MIN_BY(x, y) returns the value of x corresponding to the minimum value of the input y. MIN_BY(time, x) returns the timestamp when x is at its minimum value. | The first input x can be of any type, while the second input y must be of type INT32, INT64, FLOAT, or DOUBLE. | Consistent with the data type of the first input x. | For details and examples, see the document [Aggregate Functions](../Operators-Functions/Aggregation.md). diff --git a/src/UserGuide/latest/stage/Operators-Functions/Aggregation.md b/src/UserGuide/latest/stage/Operators-Functions/Aggregation.md index 201fa0b..bf36b5b 100644 --- a/src/UserGuide/latest/stage/Operators-Functions/Aggregation.md +++ b/src/UserGuide/latest/stage/Operators-Functions/Aggregation.md @@ -27,22 +27,24 @@ All aggregate functions except `COUNT()`, `COUNT_IF()` ignore null values and re 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 valu [...] -| 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 `*` | / [...] +| Function Name | Description | Allowed Input Series Data Types | Required Attributes [...] +| ------------- | ------------------------------------------------------------ |-----------------------------------------------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [...] +| SUM | Summation. | INT32 INT64 FLOAT DOUBLE | / [...] +| COUNT | Counts the number of data points. | All data types | / [...] +| AVG | Average. | INT32 INT64 FLOAT 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 | / [...] +| MAX_VALUE | Find the maximum value. | INT32 INT64 FLOAT DOUBLE | / [...] +| MIN_VALUE | Find the minimum value. | INT32 INT64 FLOAT DOUBLE | / [...] +| FIRST_VALUE | Find the value with the smallest timestamp. | All data types | / [...] +| LAST_VALUE | Find the value with the largest timestamp. | All data types | / [...] +| MAX_TIME | Find the maximum timestamp. | All data Types | / [...] +| MIN_TIME | Find the minimum timestamp. | All data Types | / [...] +| 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 valu [...] +| 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 | / [...] +| 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 | / [...] +| 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 `*` | / [...] +| MAX_BY | MAX_BY(x, y) returns the value of x corresponding to the maximum value of the input y. MAX_BY(time, x) returns the timestamp when x is at its maximum value. | The first input x can be of any type, while the second input y must be of type INT32, INT64, FLOAT, or DOUBLE. | / | Consistent with the data type of the first input x. | +| MIN_BY | MIN_BY(x, y) returns the value of x corresponding to the minimum value of the input y. MIN_BY(time, x) returns the timestamp when x is at its minimum value. | The first input x can be of any type, while the second input y must be of type INT32, INT64, FLOAT, or DOUBLE. | / [...] ## COUNT @@ -290,4 +292,101 @@ Result > 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 +> 4. Count_time does not support use with group by level, group by tag. + + +### MAX_BY +#### Function Definition +max_by(x, y): Returns the value of x at the timestamp when y is at its maximum. +- max_by must have two input parameters x and y. +- Both x and y can use the keyword time, with max_by(time, x) returning the timestamp when x is at its maximum value. +- If x is null at the timestamp corresponding to the maximum value of y, null is returned. +- If y reaches its maximum value at multiple timestamps, the x value corresponding to the smallest timestamp among those maximum values is returned. +- Consistent with IoTDB max_value, only INT32, INT64, FLOAT, DOUBLE are supported as inputs for y, while all six types are supported as inputs for x. +- Direct numerical values are not allowed as inputs for either x or y. + +#### Grammar +```sql +select max_by(x, y) from root.sg +select max_by(time, x) from root.sg +``` + +#### Examples + +##### Input Data +```sql +IoTDB> select * from root.test ++-----------------------------+-----------+-----------+ +| Time|root.test.a|root.test.b| ++-----------------------------+-----------+-----------+ +|1970-01-01T08:00:00.001+08:00| 1.0| 10.0| +|1970-01-01T08:00:00.002+08:00| 2.0| 10.0| +|1970-01-01T08:00:00.003+08:00| 3.0| 10.0| +|1970-01-01T08:00:00.004+08:00| 10.0| 10.0| +|1970-01-01T08:00:00.005+08:00| 10.0| 12.0| +|1970-01-01T08:00:00.006+08:00| 6.0| 6.0| ++-----------------------------+-----------+-----------+ +``` +##### Query Example +Querying the timestamp corresponding to the maximum value: +```sql +IoTDB> select max_by(time, a), max_value(a) from root.test ++-------------------------+------------------------+ +|max_by(Time, root.test.a)| max_value(root.test.a)| ++-------------------------+------------------------+ +| 4| 10.0| ++-------------------------+------------------------+ +``` + +Finding the value of b when a is at its maximum: +```sql +IoTDB> select max_by(b, a) from root.test ++--------------------------------+ +|max_by(root.test.b, root.test.a)| ++--------------------------------+ +| 10.0| ++--------------------------------+ +``` + +Using with expressions: +```sql +IoTDB> select max_by(b + 1, a * 2) from root.test ++----------------------------------------+ +|max_by(root.test.b + 1, root.test.a * 2)| ++----------------------------------------+ +| 11.0| ++----------------------------------------+ +``` + +Using with group by clause： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.000+08:00| 3.0| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +``` + +Using with having clause： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) having max_by(b, a) > 4.0 ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +``` +Using with order by clause： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) order by time desc ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.000+08:00| 3.0| ++-----------------------------+--------------------------------+ +``` \ No newline at end of file diff --git a/src/zh/UserGuide/Master/Reference/Function-and-Expression.md b/src/zh/UserGuide/Master/Reference/Function-and-Expression.md index 6b126e5..65f1680 100644 --- a/src/zh/UserGuide/Master/Reference/Function-and-Expression.md +++ b/src/zh/UserGuide/Master/Reference/Function-and-Expression.md @@ -29,22 +29,24 @@ IoTDB 支持的聚合函数如下： -| 函数名 | 功能描述 | 允许的输入类型 | 必要的属性参数 | 输出类型 | -|---------------|----------------------------------------------------------------------------------------------------------|--------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------| -| SUM | 求和。 | INT32 INT64 FLOAT DOUBLE | 无 | DOUBLE | -| COUNT | 计算数据点数。 | 所有类型 | 无 | INT64 | -| AVG | 求平均值。 | INT32 INT64 FLOAT DOUBLE | 无 | DOUBLE | -| EXTREME | 求具有最大绝对值的值。如果正值和负值的最大绝对值相等，则返回正值。 | INT32 INT64 FLOAT DOUBLE | 无 | 与输入类型一致 | -| MAX_VALUE | 求最大值。 | INT32 INT64 FLOAT DOUBLE | 无 | 与输入类型一致 | -| MIN_VALUE | 求最小值。 | INT32 INT64 FLOAT DOUBLE | 无 | 与输入类型一致 | -| FIRST_VALUE | 求时间戳最小的值。 | 所有类型 | 无 | 与输入类型一致 | -| LAST_VALUE | 求时间戳最大的值。 | 所有类型 | 无 | 与输入类型一致 | -| 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.如果有多个众数，则返回对应时间戳最小的众数。 | 所有类型 | 无 | 与输入类型一致 | -| COUNT_TIME | 查询结果集的时间戳的数量。与 align by device 搭配使用时，得到的结果是每个设备的结果集的时间戳的数量。 | 所有类型，输入参数只能为* | 无 | INT64 | +| 函数名 | 功能描述 | 允许的输入类型 | 必要的属性参数 | 输出类型 | +|---------------|----------------------------------------------------------------------------------------------------------|-------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------------| +| SUM | 求和。 | INT32 INT64 FLOAT DOUBLE | 无 | DOUBLE | +| COUNT | 计算数据点数。 | 所有类型 | 无 | INT64 | +| AVG | 求平均值。 | INT32 INT64 FLOAT DOUBLE | 无 | DOUBLE | +| EXTREME | 求具有最大绝对值的值。如果正值和负值的最大绝对值相等，则返回正值。 | INT32 INT64 FLOAT DOUBLE | 无 | 与输入类型一致 | +| MAX_VALUE | 求最大值。 | INT32 INT64 FLOAT DOUBLE | 无 | 与输入类型一致 | +| MIN_VALUE | 求最小值。 | INT32 INT64 FLOAT DOUBLE | 无 | 与输入类型一致 | +| FIRST_VALUE | 求时间戳最小的值。 | 所有类型 | 无 | 与输入类型一致 | +| LAST_VALUE | 求时间戳最大的值。 | 所有类型 | 无 | 与输入类型一致 | +| 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.如果有多个众数，则返回对应时间戳最小的众数。 | 所有类型 | 无 | 与输入类型一致 | +| COUNT_TIME | 查询结果集的时间戳的数量。与 align by device 搭配使用时，得到的结果是每个设备的结果集的时间戳的数量。 | 所有类型，输入参数只能为* | 无 | INT64 | +| MAX_BY | MAX_BY(x, y) 求二元输入 x 和 y 在 y 最大时对应的 x 的值。MAX_BY(time, x) 返回 x 取最大值时对应的时间戳。 | 第一个输入 x 可以是任意类型，第二个输入 y 只能是 INT32 INT64 FLOAT DOUBLE | 无 | 与第一个输入 x 的数据类型一致 | +| MIN_BY | MIN_BY(x, y) 求二元输入 x 和 y 在 y 最小时对应的 x 的值。MIN_BY(time, x) 返回 x 取最小值时对应的时间戳。 | 第一个输入 x 可以是任意类型，第二个输入 y 只能是 INT32 INT64 FLOAT DOUBLE | 无 | 与第一个输入 x 的数据类型一致 | ### COUNT_IF @@ -276,6 +278,107 @@ select count_time(*) from root.db.** group by([0, 10), 2ms) align by device > 3. having语句里不支持使用count_time, 使用count_time聚合函数时不支持使用having语句。 > 4. count_time不支持与group by level, group by tag一起使用。 + + +### MAX_BY + +#### 功能定义 +max_by(x, y)： 返回 y 最大时对应时间戳下的 x 值。 + - max_by 必须有两个输入参数 x 和 y。 + - x 和 y 均可以为 time 关键字, max_by(time, x) 返回 x 取最大值时对应的时间戳。 + - 如果 y 最大时对应的时间戳下 x 为 null，则返回 null。 + - 如果 y 可以在多个时间戳下取得最大值，取最大值中最小时间戳对应的 x 值。 + - 与 IoTDB max_value 保持一致，仅支持 INT32、INT64、FLOAT、DOUBLE 作为 y 的输入，支持所有六种类型作为 x 的输入。 + - x, y 的输入均不允许为具体数值。 + + +#### 语法 +```sql +select max_by(x, y) from root.sg +select max_by(time, x) from root.sg +``` + +#### 使用示例 + +##### 原始数据 +```sql +IoTDB> select * from root.test ++-----------------------------+-----------+-----------+ +| Time|root.test.a|root.test.b| ++-----------------------------+-----------+-----------+ +|1970-01-01T08:00:00.001+08:00| 1.0| 10.0| +|1970-01-01T08:00:00.002+08:00| 2.0| 10.0| +|1970-01-01T08:00:00.003+08:00| 3.0| 10.0| +|1970-01-01T08:00:00.004+08:00| 10.0| 10.0| +|1970-01-01T08:00:00.005+08:00| 10.0| 12.0| +|1970-01-01T08:00:00.006+08:00| 6.0| 6.0| ++-----------------------------+-----------+-----------+ +``` +##### 查询示例 +查询最大值对应的时间戳： +```sql +IoTDB> select max_by(time, a), max_value(a) from root.test ++-------------------------+------------------------+ +|max_by(Time, root.test.a)| max_value(root.test.a)| ++-------------------------+------------------------+ +| 4| 10.0| ++-------------------------+------------------------+ +``` + +求 a 最大时对应的 b 值： +```sql +IoTDB> select max_by(b, a) from root.test ++--------------------------------+ +|max_by(root.test.b, root.test.a)| ++--------------------------------+ +| 10.0| ++--------------------------------+ +``` + +结合表达式使用： +```sql +IoTDB> select max_by(b + 1, a * 2) from root.test ++----------------------------------------+ +|max_by(root.test.b + 1, root.test.a * 2)| ++----------------------------------------+ +| 11.0| ++----------------------------------------+ +``` + +结合 group by time 子句使用： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.000+08:00| 3.0| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +``` + +结合 having 子句使用： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) having max_by(b, a) > 4.0 ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +``` +结合 order by 子句使用： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) order by time desc ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.000+08:00| 3.0| ++-----------------------------+--------------------------------+ +``` + + <!-- Licensed to the Apache Software Foundation (ASF) under one diff --git a/src/zh/UserGuide/Master/User-Manual/Operator-and-Expression.md b/src/zh/UserGuide/Master/User-Manual/Operator-and-Expression.md index 3ff9f86..79f39ae 100644 --- a/src/zh/UserGuide/Master/User-Manual/Operator-and-Expression.md +++ b/src/zh/UserGuide/Master/User-Manual/Operator-and-Expression.md @@ -89,18 +89,20 @@ OR, |, || ### 聚合函数 -| 函数名 | 功能描述 | 允许的输入类型 | 输出类型 | -| ----------- | ------------------------------------------------------------ | ------------------------ | -------------- | -| SUM | 求和。 | INT32 INT64 FLOAT DOUBLE | DOUBLE | -| COUNT | 计算数据点数。 | 所有类型 | INT | -| AVG | 求平均值。 | INT32 INT64 FLOAT DOUBLE | DOUBLE | -| EXTREME | 求具有最大绝对值的值。如果正值和负值的最大绝对值相等，则返回正值。 | INT32 INT64 FLOAT DOUBLE | 与输入类型一致 | -| MAX_VALUE | 求最大值。 | INT32 INT64 FLOAT DOUBLE | 与输入类型一致 | -| MIN_VALUE | 求最小值。 | INT32 INT64 FLOAT DOUBLE | 与输入类型一致 | -| FIRST_VALUE | 求时间戳最小的值。 | 所有类型 | 与输入类型一致 | -| LAST_VALUE | 求时间戳最大的值。 | 所有类型 | 与输入类型一致 | -| MAX_TIME | 求最大时间戳。 | 所有类型 | Timestamp | -| MIN_TIME | 求最小时间戳。 | 所有类型 | Timestamp | +| 函数名 | 功能描述 | 允许的输入类型 | 输出类型 | +|-------------|----------------------------------------------------------------------------| ------------------------ | -------------- | +| SUM | 求和。 | INT32 INT64 FLOAT DOUBLE | DOUBLE | +| COUNT | 计算数据点数。 | 所有类型 | INT | +| AVG | 求平均值。 | INT32 INT64 FLOAT DOUBLE | DOUBLE | +| EXTREME | 求具有最大绝对值的值。如果正值和负值的最大绝对值相等，则返回正值。 | INT32 INT64 FLOAT DOUBLE | 与输入类型一致 | +| MAX_VALUE | 求最大值。 | INT32 INT64 FLOAT DOUBLE | 与输入类型一致 | +| MIN_VALUE | 求最小值。 | INT32 INT64 FLOAT DOUBLE | 与输入类型一致 | +| FIRST_VALUE | 求时间戳最小的值。 | 所有类型 | 与输入类型一致 | +| LAST_VALUE | 求时间戳最大的值。 | 所有类型 | 与输入类型一致 | +| MAX_TIME | 求最大时间戳。 | 所有类型 | Timestamp | +| MIN_TIME | 求最小时间戳。 | 所有类型 | Timestamp | +| MAX_BY | MAX_BY(x, y) 求二元输入 x 和 y 在 y 最大时对应的 x 的值。MAX_BY(time, x) 返回 x 取最大值时对应的时间戳。 | 第一个输入 x 可以是任意类型，第二个输入 y 只能是 INT32 INT64 FLOAT DOUBLE | 与第一个输入 x 的数据类型一致 | +| MIN_BY | MIN_BY(x, y) 求二元输入 x 和 y 在 y 最小时对应的 x 的值。MIN_BY(time, x) 返回 x 取最小值时对应的时间戳。 | 第一个输入 x 可以是任意类型，第二个输入 y 只能是 INT32 INT64 FLOAT DOUBLE | 与第一个输入 x 的数据类型一致 | 详细说明及示例见文档 [聚合函数](../Reference/Function-and-Expression.md#聚合函数)。 diff --git a/src/zh/UserGuide/latest/Reference/Function-and-Expression.md b/src/zh/UserGuide/latest/Reference/Function-and-Expression.md index 6b126e5..aeb74e6 100644 --- a/src/zh/UserGuide/latest/Reference/Function-and-Expression.md +++ b/src/zh/UserGuide/latest/Reference/Function-and-Expression.md @@ -45,6 +45,8 @@ IoTDB 支持的聚合函数如下： | TIME_DURATION | 求某一列最大一个不为NULL的值所在时间戳与最小一个不为NULL的值所在时间戳的时间戳差 | 所有类型 | 无 | INT64 | | MODE | 求众数。注意：<br>1.输入序列的不同值个数过多时会有内存异常风险; <br>2.如果所有元素出现的频次相同，即没有众数，则返回对应时间戳最小的值; <br>3.如果有多个众数，则返回对应时间戳最小的众数。 | 所有类型 | 无 | 与输入类型一致 | | COUNT_TIME | 查询结果集的时间戳的数量。与 align by device 搭配使用时，得到的结果是每个设备的结果集的时间戳的数量。 | 所有类型，输入参数只能为* | 无 | INT64 | +| MAX_BY | MAX_BY(x, y) 求二元输入 x 和 y 在 y 最大时对应的 x 的值。MAX_BY(time, x) 返回 x 取最大值时对应的时间戳。 | 第一个输入 x 可以是任意类型，第二个输入 y 只能是 INT32 INT64 FLOAT DOUBLE | 无 | 与第一个输入 x 的数据类型一致 | +| MIN_BY | MIN_BY(x, y) 求二元输入 x 和 y 在 y 最小时对应的 x 的值。MIN_BY(time, x) 返回 x 取最小值时对应的时间戳。 | 第一个输入 x 可以是任意类型，第二个输入 y 只能是 INT32 INT64 FLOAT DOUBLE | 无 | 与第一个输入 x 的数据类型一致 | ### COUNT_IF @@ -276,6 +278,104 @@ select count_time(*) from root.db.** group by([0, 10), 2ms) align by device > 3. having语句里不支持使用count_time, 使用count_time聚合函数时不支持使用having语句。 > 4. count_time不支持与group by level, group by tag一起使用。 +### MAX_BY + +#### 功能定义 +max_by(x, y)： 返回 y 最大时对应时间戳下的 x 值。 +- max_by 必须有两个输入参数 x 和 y。 +- x 和 y 均可以为 time 关键字, max_by(time, x) 返回 x 取最大值时对应的时间戳。 +- 如果 y 最大时对应的时间戳下 x 为 null，则返回 null。 +- 如果 y 可以在多个时间戳下取得最大值，取最大值中最小时间戳对应的 x 值。 +- 与 IoTDB max_value 保持一致，仅支持 INT32、INT64、FLOAT、DOUBLE 作为 y 的输入，支持所有六种类型作为 x 的输入。 +- x, y 的输入均不允许为具体数值。 + + +#### 语法 +```sql +select max_by(x, y) from root.sg +select max_by(time, x) from root.sg +``` + +#### 使用示例 + +##### 原始数据 +```sql +IoTDB> select * from root.test ++-----------------------------+-----------+-----------+ +| Time|root.test.a|root.test.b| ++-----------------------------+-----------+-----------+ +|1970-01-01T08:00:00.001+08:00| 1.0| 10.0| +|1970-01-01T08:00:00.002+08:00| 2.0| 10.0| +|1970-01-01T08:00:00.003+08:00| 3.0| 10.0| +|1970-01-01T08:00:00.004+08:00| 10.0| 10.0| +|1970-01-01T08:00:00.005+08:00| 10.0| 12.0| +|1970-01-01T08:00:00.006+08:00| 6.0| 6.0| ++-----------------------------+-----------+-----------+ +``` +##### 查询示例 +查询最大值对应的时间戳： +```sql +IoTDB> select max_by(time, a), max_value(a) from root.test ++-------------------------+------------------------+ +|max_by(Time, root.test.a)| max_value(root.test.a)| ++-------------------------+------------------------+ +| 4| 10.0| ++-------------------------+------------------------+ +``` + +求 a 最大时对应的 b 值： +```sql +IoTDB> select max_by(b, a) from root.test ++--------------------------------+ +|max_by(root.test.b, root.test.a)| ++--------------------------------+ +| 10.0| ++--------------------------------+ +``` + +结合表达式使用： +```sql +IoTDB> select max_by(b + 1, a * 2) from root.test ++----------------------------------------+ +|max_by(root.test.b + 1, root.test.a * 2)| ++----------------------------------------+ +| 11.0| ++----------------------------------------+ +``` + +结合 group by time 子句使用： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.000+08:00| 3.0| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +``` + +结合 having 子句使用： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) having max_by(b, a) > 4.0 ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +``` +结合 order by 子句使用： +```sql +IoTDB> select max_by(b, a) from root.test group by ([0,7),4ms) order by time desc ++-----------------------------+--------------------------------+ +| Time|max_by(root.test.b, root.test.a)| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.004+08:00| 10.0| ++-----------------------------+--------------------------------+ +|1970-01-01T08:00:00.000+08:00| 3.0| ++-----------------------------+--------------------------------+ +``` + <!-- Licensed to the Apache Software Foundation (ASF) under one diff --git a/src/zh/UserGuide/latest/User-Manual/Operator-and-Expression.md b/src/zh/UserGuide/latest/User-Manual/Operator-and-Expression.md index 3ff9f86..57cb255 100644 --- a/src/zh/UserGuide/latest/User-Manual/Operator-and-Expression.md +++ b/src/zh/UserGuide/latest/User-Manual/Operator-and-Expression.md @@ -101,6 +101,8 @@ OR, |, || | LAST_VALUE | 求时间戳最大的值。 | 所有类型 | 与输入类型一致 | | MAX_TIME | 求最大时间戳。 | 所有类型 | Timestamp | | MIN_TIME | 求最小时间戳。 | 所有类型 | Timestamp | +| MAX_BY | MAX_BY(x, y) 求二元输入 x 和 y 在 y 最大时对应的 x 的值。MAX_BY(time, x) 返回 x 取最大值时对应的时间戳。 | 第一个输入 x 可以是任意类型，第二个输入 y 只能是 INT32 INT64 FLOAT DOUBLE | 与第一个输入 x 的数据类型一致 | +| MIN_BY | MIN_BY(x, y) 求二元输入 x 和 y 在 y 最小时对应的 x 的值。MIN_BY(time, x) 返回 x 取最小值时对应的时间戳。 | 第一个输入 x 可以是任意类型，第二个输入 y 只能是 INT32 INT64 FLOAT DOUBLE | 与第一个输入 x 的数据类型一致 | 详细说明及示例见文档 [聚合函数](../Reference/Function-and-Expression.md#聚合函数)。
