This is an automated email from the ASF dual-hosted git repository. critas pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/iotdb-docs.git
The following commit(s) were added to refs/heads/main by this push:
new e0147450 add greatest least and countif in table basic function (#715)
e0147450 is described below
commit e01474506225bb2dc07125f505b78ecf52738fc3
Author: leto-b <[email protected]>
AuthorDate: Thu May 15 10:18:57 2025 +0800
add greatest least and countif in table basic function (#715)
* add greatest least and countif in table basic function
* adjust countif number
---
.../Master/Table/SQL-Manual/Basis-Function.md | 174 +++++++++++++--------
.../latest-Table/SQL-Manual/Basis-Function.md | 108 +++++++++----
.../Master/Table/SQL-Manual/Basis-Function.md | 160 ++++++++++++-------
.../latest-Table/SQL-Manual/Basis-Function.md | 105 +++++++++----
4 files changed, 377 insertions(+), 170 deletions(-)
diff --git a/src/UserGuide/Master/Table/SQL-Manual/Basis-Function.md
b/src/UserGuide/Master/Table/SQL-Manual/Basis-Function.md
index 328f7fa8..98f992ba 100644
--- a/src/UserGuide/Master/Table/SQL-Manual/Basis-Function.md
+++ b/src/UserGuide/Master/Table/SQL-Manual/Basis-Function.md
@@ -119,6 +119,33 @@ Example 3: Query records where region is not 'Beijing' or
'Shanghai'
SELECT * FROM table1 WHERE region NOT IN ('Beijing', 'Shanghai');
```
+### 1.5 GREATEST and LEAST
+
+The `GREATEST` function returns the maximum value from a list of arguments,
while the `LEAST` function returns the minimum value. The return type matches
the input data type.
+
+Key Behaviors:
+1. NULL Handling: Returns NULL if all arguments are NULL.
+2. Parameter Requirements: Requires at least 2 arguments.
+3. Type Constraints: All arguments must have the same data type.
+4. Supported Types:
`BOOLEAN`、`FLOAT`、`DOUBLE`、`INT32`、`INT64`、`STRING`、`TEXT`、`TIMESTAMP`、`DATE`
+
+**Syntax:**
+
+```sql
+ greatest(value1, value2, ..., valueN)
+ least(value1, value2, ..., valueN)
+```
+
+**Examples:**
+
+```sql
+-- Retrieve the maximum value between `temperature` and `humidity` in `table2`
+SELECT GREATEST(temperature,humidity) FROM table2;
+
+-- Retrieve the minimum value between `temperature` and `humidity` in `table2`
+SELECT LEAST(temperature,humidity) FROM table2;
+```
+
## 2. Aggregate functions
### 2.1 Overview
@@ -127,29 +154,31 @@ SELECT * FROM table1 WHERE region NOT IN ('Beijing',
'Shanghai');
2. Except for `COUNT()`, all other aggregate functions ignore null values and
return null when there are no input rows or all values are null. For example,
`SUM()` returns null instead of zero, and `AVG()` does not include null values
in the count.
-### 2.2 Supported Aggregate Functions
-
-| Function Name | Description
| Allowed Input Types | Output Type |
-| :------------ | :-----------------------------------------------------------
| :------------------------- | :----------------------------------------- |
-| COUNT | Counts the number of data points.
| All types | INT64 |
-| SUM | Calculates the sum.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| AVG | Calculates the average.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| MAX | Finds the maximum value.
| All types | Same as input type |
-| MIN | Finds the minimum value.
| All types | Same as input type |
-| FIRST | Finds the value with the smallest timestamp that is not
NULL. | All types | Same as input type
|
-| LAST | Finds the value with the largest timestamp that is not NULL.
| All types | Same as input type |
-| STDDEV | Alias for STDDEV_SAMP, calculates the sample standard
deviation. | INT32 INT64 FLOAT DOUBLE | DOUBLE
|
-| STDDEV_POP | Calculates the population standard deviation.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| STDDEV_SAMP | Calculates the sample standard deviation.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| VARIANCE | Alias for VAR_SAMP, calculates the sample variance.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| VAR_POP | Calculates the population variance.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| VAR_SAMP | Calculates the sample variance.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| EXTREME | Finds the value with the largest absolute value. If the
largest absolute values of positive and negative values are equal, returns the
positive value. | INT32 INT64 FLOAT DOUBLE | Same as input type
|
-| MODE | Finds the mode. Note: 1. There is a risk of memory exception
when the number of distinct values in the input sequence is too large; 2. If
all elements have the same frequency, i.e., there is no mode, a random element
is returned; 3. If there are multiple modes, a random mode is returned; 4. NULL
values are also counted in frequency, so even if not all values in the input
sequence are NULL, the final result may still be NULL. | All types
| Same as input [...]
-| MAX_BY | MAX_BY(x, y) finds the value of x corresponding to the
maximum y in the binary input x and y. MAX_BY(time, x) returns the timestamp
when x is at its maximum. | x and y can be of any type | Same as the data type
of the first input x |
-| MIN_BY | MIN_BY(x, y) finds the value of x corresponding to the
minimum y in the binary input x and y. MIN_BY(time, x) returns the timestamp
when x is at its minimum. | x and y can be of any type | Same as the data type
of the first input x |
-| FIRST_BY | FIRST_BY(x, y) finds the value of x in the same row when y
is the first non-null value. | x and y can be of any type | Same as the data
type of the first input x |
-| LAST_BY | LAST_BY(x, y) finds the value of x in the same row when y is
the last non-null value. | x and y can be of any type | Same as the data type
of the first input x |
+### 2.2 Supported Aggregate Functions
+
+| Function Name | Description
| Allowed Input Types
[...]
+|:--------------|:---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:-------------------------------------------
[...]
+| COUNT | Counts the number of data points.
| All types
[...]
+| COUNT_IF | COUNT_IF(exp) counts the number of rows that satisfy a
specified boolean expression.
| `exp` must be a boolean
expression,(e.g. `c [...]
+| SUM | Calculates the sum.
| INT32 INT64 FLOAT DOUBLE
[...]
+| AVG | Calculates the average.
| INT32 INT64 FLOAT DOUBLE
[...]
+| MAX | Finds the maximum value.
| All types
[...]
+| MIN | Finds the minimum value.
| All types
[...]
+| FIRST | Finds the value with the smallest timestamp that is not
NULL.
| All types
[...]
+| LAST | Finds the value with the largest timestamp that is not NULL.
| All types
[...]
+| STDDEV | Alias for STDDEV_SAMP, calculates the sample standard
deviation.
| INT32 INT64 FLOAT
DOUBLE [...]
+| STDDEV_POP | Calculates the population standard deviation.
| INT32 INT64 FLOAT DOUBLE
[...]
+| STDDEV_SAMP | Calculates the sample standard deviation.
| INT32 INT64 FLOAT DOUBLE
[...]
+| VARIANCE | Alias for VAR_SAMP, calculates the sample variance.
| INT32 INT64 FLOAT DOUBLE
[...]
+| VAR_POP | Calculates the population variance.
| INT32 INT64 FLOAT DOUBLE
[...]
+| VAR_SAMP | Calculates the sample variance.
| INT32 INT64 FLOAT DOUBLE
[...]
+| EXTREME | Finds the value with the largest absolute value. If the
largest absolute values of positive and negative values are equal, returns the
positive value.
| INT32 INT64 FLOAT
DOUBLE [...]
+| MODE | Finds the mode. Note: 1. There is a risk of memory exception
when the number of distinct values in the input sequence is too large; 2. If
all elements have the same frequency, i.e., there is no mode, a random element
is returned; 3. If there are multiple modes, a random mode is returned; 4. NULL
values are also counted in frequency, so even if not all values in the input
sequence are NULL, the final result may still be NULL. | All types
[...]
+| MAX_BY | MAX_BY(x, y) finds the value of x corresponding to the
maximum y in the binary input x and y. MAX_BY(time, x) returns the timestamp
when x is at its maximum.
| x and y can be of
any type [...]
+| MIN_BY | MIN_BY(x, y) finds the value of x corresponding to the
minimum y in the binary input x and y. MIN_BY(time, x) returns the timestamp
when x is at its minimum.
| x and y can be of
any type [...]
+| FIRST_BY | FIRST_BY(x, y) finds the value of x in the same row when y
is the first non-null value.
| x and y can be of any type
[...]
+| LAST_BY | LAST_BY(x, y) finds the value of x in the same row when y is
the last non-null value.
| x and y can be of any type
[...]
+
### 2.3 Examples
@@ -179,7 +208,29 @@ Total line number = 1
It costs 0.834s
```
-#### 2.3.3 First
+
+#### 2.3.3 Count_if
+
+Count `Non-Null` `arrival_time` Records in `table2`
+
+```sql
+select count_if(arrival_time is not null) from table2;
+```
+
+The execution result is as follows:
+
+```sql
++-----+
+|_col0|
++-----+
+| 4|
++-----+
+Total line number = 1
+It costs 0.047s
+```
+
+
+#### 2.3.4 First
Finds the values with the smallest timestamp that are not NULL in the
`temperature` and `humidity` columns.
@@ -199,7 +250,7 @@ Total line number = 1
It costs 0.170s
```
-#### 2.3.4 Last
+#### 2.3.5 Last
Finds the values with the largest timestamp that are not NULL in the
`temperature` and `humidity` columns.
@@ -219,7 +270,7 @@ Total line number = 1
It costs 0.211s
```
-#### 2.3.5 First_by
+#### 2.3.6 First_by
Finds the `time` value of the row with the smallest timestamp that is not NULL
in the `temperature` column, and the `humidity` value of the row with the
smallest timestamp that is not NULL in the `temperature` column.
@@ -239,7 +290,7 @@ Total line number = 1
It costs 0.269s
```
-#### 2.3.6 Last_by
+#### 2.3.7 Last_by
Queries the `time` value of the row with the largest timestamp that is not
NULL in the `temperature` column, and the `humidity` value of the row with the
largest timestamp that is not NULL in the `temperature` column.
@@ -259,7 +310,7 @@ Total line number = 1
It costs 0.070s
```
-#### 2.3.7 Max_by
+#### 2.3.8 Max_by
Queries the `time` value of the row where the `temperature` column is at its
maximum, and the `humidity` value of the row where the `temperature` column is
at its maximum.
@@ -279,7 +330,7 @@ Total line number = 1
It costs 0.172s
```
-#### 2.3.8 Min_by
+#### 2.3.9 Min_by
Queries the `time` value of the row where the `temperature` column is at its
minimum, and the `humidity` value of the row where the `temperature` column is
at its minimum.
@@ -299,6 +350,7 @@ Total line number = 1
It costs 0.244s
```
+
## 3. Logical operators
### 3.1 Overview
@@ -343,7 +395,7 @@ NULL OR true -- true
##### 3.2.2.1 Truth Table
-The following truth table illustrates how `NULL` is handled in `AND` and `OR`
operators:
+The following truth table illustrates how `NULL` is handled in `AND` and `OR`
operators:
| a | b | a AND b | a OR b |
| :---- | :---- | :------ | :----- |
@@ -417,7 +469,7 @@ date_bin(interval,source,origin)
4. If `source` is `null`, the function returns `null`.
5. Mixing months and non-month time units (e.g., `1 MONTH 1 DAY`) is not
supported due to ambiguity.
-> For example, if the starting point is **April 30, 2000**, calculating `1
DAY` first and then `1 MONTH` results in **June 1, 2000**, whereas calculating
`1 MONTH` first and then `1 DAY` results in **May 31, 2000**. The resulting
dates are different.
+> For example, if the starting point is **April 30, 2000**, calculating `1
DAY` first and then `1 MONTH` results in **June 1, 2000**, whereas calculating
`1 MONTH` first and then `1 DAY` results in **May 31, 2000**. The resulting
dates are different.
#### 4.2.2 Examples
@@ -635,31 +687,31 @@ It costs 0.319s
### 5.2 Mathematical functions
-| Function Name | Description
| Input | Output | Usage
|
-|:--------------|:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:----------------------------|:--------------------|
:--------- |
-| sin | Sine
| double, float, INT64, INT32 | double |
sin(x) |
-| cos | Cosine
| double, float, INT64, INT32 | double |
cos(x) |
-| tan | Tangent
| double, float, INT64, INT32 | double |
tan(x) |
-| asin | Inverse Sine
| double, float, INT64, INT32 | double |
asin(x) |
-| acos | Inverse Cosine
| double, float, INT64, INT32 | double |
acos(x) |
-| atan | Inverse Tangent
| double, float, INT64, INT32 | double |
atan(x) |
-| sinh | Hyperbolic Sine
| double, float, INT64, INT32 | double |
sinh(x) |
-| cosh | Hyperbolic Cosine
| double, float, INT64, INT32 | double |
cosh(x) |
-| tanh | Hyperbolic Tangent
| double, float, INT64, INT32 | double |
tanh(x) |
-| degrees | Converts angle `x` in radians to degrees
| double, float, INT64, INT32 | double |
degrees(x) |
-| radians | Radian Conversion from Degrees
| double, float, INT64, INT32 | double |
radians(x) |
-| abs | Absolute Value
| double, float, INT64, INT32 | Same as input type |
abs(x) |
-| sign | Returns the sign of `x`: - If `x = 0`, returns `0` - If `x
> 0`, returns `1` - If `x < 0`, returns `-1` For `double/float` inputs: - If
`x = NaN`, returns `NaN` - If `x = +Infinity`, returns `1.0` - If `x =
-Infinity`, returns `-1.0` | double, float, INT64, INT32 | Same as input type
| sign(x) |
-| ceil | Rounds `x` up to the nearest integer
| double, float, INT64, INT32 | double |
ceil(x) |
-| floor | Rounds `x` down to the nearest integer
| double, float, INT64, INT32 | double |
floor(x) |
-| exp | Returns `e^x` (Euler's number raised to the power of `x`)
| double, float, INT64, INT32 | double |
exp(x) |
-| ln | Returns the natural logarithm of `x`
| double, float, INT64, INT32 | double | ln(x)
|
-| log10 | Returns the base 10 logarithm of `x`
| double, float, INT64, INT32 | double |
log10(x) |
-| round | Rounds `x` to the nearest integer
| double, float, INT64, INT32 | double |
round(x) |
-| round | Rounds `x` to `d` decimal places
| double, float, INT64, INT32 | double |
round(x, d)|
-| sqrt | Returns the square root of `x`.
| double, float, INT64, INT32 | double |
sqrt(x) |
-| e | Returns Euler’s number `e`.
| | double | e()
|
-| pi | Pi (π)
| | double | pi()
|
+| Function Name | Description
| Input | Output | Usage
|
+|:--------------|:-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:----------------------------|:-------------------|
:--------- |
+| sin | Sine
| double, float, INT64, INT32 | double | sin(x)
|
+| cos | Cosine
| double, float, INT64, INT32 | double | cos(x)
|
+| tan | Tangent
| double, float, INT64, INT32 | double | tan(x)
|
+| asin | Inverse Sine
| double, float, INT64, INT32 | double |
asin(x) |
+| acos | Inverse Cosine
| double, float, INT64, INT32 | double |
acos(x) |
+| atan | Inverse Tangent
| double, float, INT64, INT32 | double |
atan(x) |
+| sinh | Hyperbolic Sine
| double, float, INT64, INT32 | double |
sinh(x) |
+| cosh | Hyperbolic Cosine
| double, float, INT64, INT32 | double |
cosh(x) |
+| tanh | Hyperbolic Tangent
| double, float, INT64, INT32 | double |
tanh(x) |
+| degrees | Converts angle `x` in radians to degrees
| double, float, INT64, INT32 | double |
degrees(x) |
+| radians | Radian Conversion from Degrees
| double, float, INT64, INT32 | double |
radians(x) |
+| abs | Absolute Value
| double, float, INT64, INT32 | Same as input type | abs(x)
|
+| sign | Returns the sign of `x`: - If `x = 0`, returns `0` - If `x
> 0`, returns `1` - If `x < 0`, returns `-1` For `double/float` inputs: - If
`x = NaN`, returns `NaN` - If `x = +Infinity`, returns `1.0` - If `x =
-Infinity`, returns `-1.0` | double, float, INT64, INT32 | Same as input type |
sign(x) |
+| ceil | Rounds `x` up to the nearest integer
| double, float, INT64, INT32 | double |
ceil(x) |
+| floor | Rounds `x` down to the nearest integer
| double, float, INT64, INT32 | double |
floor(x) |
+| exp | Returns `e^x` (Euler's number raised to the power of `x`)
| double, float, INT64, INT32 | double | exp(x)
|
+| ln | Returns the natural logarithm of `x`
| double, float, INT64, INT32 | double | ln(x)
|
+| log10 | Returns the base 10 logarithm of `x`
| double, float, INT64, INT32 | double |
log10(x) |
+| round | Rounds `x` to the nearest integer
| double, float, INT64, INT32 | double |
round(x) |
+| round | Rounds `x` to `d` decimal places
| double, float, INT64, INT32 | double |
round(x, d) |
+| sqrt | Returns the square root of `x`.
| double, float, INT64, INT32 | double |
sqrt(x) |
+| e | Returns Euler’s number `e`.
| | double | e()
|
+| pi | Pi (π)
| | double | pi()
|
## 6. Conditional Expressions
@@ -928,10 +980,10 @@ Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701:
Invalid format string: %.5f (
```
3. Invalid Invocation Errors
- Triggered if:
+Triggered if:
- * Total arguments < 2 (must include `pattern` and at least one argument).•
- * `pattern` is not of type `STRING`/`TEXT`.
+* Total arguments < 2 (must include `pattern` and at least one argument).•
+* `pattern` is not of type `STRING`/`TEXT`.
```SQL
-- Example 1
@@ -954,7 +1006,7 @@ The `||` operator is used for string concatenation and
functions the same as the
#### 8.1.2 LIKE Statement
- The `LIKE` statement is used for pattern matching. For detailed usage, refer
to Pattern Matching:[LIKE](#1-like-运算符).
+The `LIKE` statement is used for pattern matching. For detailed usage, refer
to Pattern Matching:[LIKE](#1-like-运算符).
### 8.2 String Functions
@@ -1089,7 +1141,7 @@ SELECT regexp_like('1a 2b 14m', '\\d+b'); -- true
- `\d+` means "one or more digits".
- `b` represents the letter b.
- In `'1a 2b 14m'`, the substring `'2b'` matches this pattern, so it returns
`true`.
- -
+
#### **Example 2: Matching the entire string**
diff --git a/src/UserGuide/latest-Table/SQL-Manual/Basis-Function.md
b/src/UserGuide/latest-Table/SQL-Manual/Basis-Function.md
index 75734cc1..5c97d743 100644
--- a/src/UserGuide/latest-Table/SQL-Manual/Basis-Function.md
+++ b/src/UserGuide/latest-Table/SQL-Manual/Basis-Function.md
@@ -119,6 +119,33 @@ Example 3: Query records where region is not 'Beijing' or
'Shanghai'
SELECT * FROM table1 WHERE region NOT IN ('Beijing', 'Shanghai');
```
+### 1.5 GREATEST and LEAST
+
+The `GREATEST` function returns the maximum value from a list of arguments,
while the `LEAST` function returns the minimum value. The return type matches
the input data type.
+
+Key Behaviors:
+1. NULL Handling: Returns NULL if all arguments are NULL.
+2. Parameter Requirements: Requires at least 2 arguments.
+3. Type Constraints: All arguments must have the same data type.
+4. Supported Types:
`BOOLEAN`、`FLOAT`、`DOUBLE`、`INT32`、`INT64`、`STRING`、`TEXT`、`TIMESTAMP`、`DATE`
+
+**Syntax:**
+
+```sql
+ greatest(value1, value2, ..., valueN)
+ least(value1, value2, ..., valueN)
+```
+
+**Examples:**
+
+```sql
+-- Retrieve the maximum value between `temperature` and `humidity` in `table2`
+SELECT GREATEST(temperature,humidity) FROM table2;
+
+-- Retrieve the minimum value between `temperature` and `humidity` in `table2`
+SELECT LEAST(temperature,humidity) FROM table2;
+```
+
## 2. Aggregate functions
### 2.1 Overview
@@ -129,27 +156,29 @@ SELECT * FROM table1 WHERE region NOT IN ('Beijing',
'Shanghai');
### 2.2 Supported Aggregate Functions
-| Function Name | Description
| Allowed Input Types | Output Type |
-| :------------ | :-----------------------------------------------------------
| :------------------------- | :----------------------------------------- |
-| COUNT | Counts the number of data points.
| All types | INT64 |
-| SUM | Calculates the sum.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| AVG | Calculates the average.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| MAX | Finds the maximum value.
| All types | Same as input type |
-| MIN | Finds the minimum value.
| All types | Same as input type |
-| FIRST | Finds the value with the smallest timestamp that is not
NULL. | All types | Same as input type
|
-| LAST | Finds the value with the largest timestamp that is not NULL.
| All types | Same as input type |
-| STDDEV | Alias for STDDEV_SAMP, calculates the sample standard
deviation. | INT32 INT64 FLOAT DOUBLE | DOUBLE
|
-| STDDEV_POP | Calculates the population standard deviation.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| STDDEV_SAMP | Calculates the sample standard deviation.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| VARIANCE | Alias for VAR_SAMP, calculates the sample variance.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| VAR_POP | Calculates the population variance.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| VAR_SAMP | Calculates the sample variance.
| INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| EXTREME | Finds the value with the largest absolute value. If the
largest absolute values of positive and negative values are equal, returns the
positive value. | INT32 INT64 FLOAT DOUBLE | Same as input type
|
-| MODE | Finds the mode. Note: 1. There is a risk of memory exception
when the number of distinct values in the input sequence is too large; 2. If
all elements have the same frequency, i.e., there is no mode, a random element
is returned; 3. If there are multiple modes, a random mode is returned; 4. NULL
values are also counted in frequency, so even if not all values in the input
sequence are NULL, the final result may still be NULL. | All types
| Same as input [...]
-| MAX_BY | MAX_BY(x, y) finds the value of x corresponding to the
maximum y in the binary input x and y. MAX_BY(time, x) returns the timestamp
when x is at its maximum. | x and y can be of any type | Same as the data type
of the first input x |
-| MIN_BY | MIN_BY(x, y) finds the value of x corresponding to the
minimum y in the binary input x and y. MIN_BY(time, x) returns the timestamp
when x is at its minimum. | x and y can be of any type | Same as the data type
of the first input x |
-| FIRST_BY | FIRST_BY(x, y) finds the value of x in the same row when y
is the first non-null value. | x and y can be of any type | Same as the data
type of the first input x |
-| LAST_BY | LAST_BY(x, y) finds the value of x in the same row when y is
the last non-null value. | x and y can be of any type | Same as the data type
of the first input x |
+| Function Name | Description
| Allowed Input Types
[...]
+|:--------------|:---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:-------------------------------------------
[...]
+| COUNT | Counts the number of data points.
| All types
[...]
+| COUNT_IF | COUNT_IF(exp) counts the number of rows that satisfy a
specified boolean expression.
| `exp` must be a boolean
expression,(e.g. `c [...]
+| SUM | Calculates the sum.
| INT32 INT64 FLOAT DOUBLE
[...]
+| AVG | Calculates the average.
| INT32 INT64 FLOAT DOUBLE
[...]
+| MAX | Finds the maximum value.
| All types
[...]
+| MIN | Finds the minimum value.
| All types
[...]
+| FIRST | Finds the value with the smallest timestamp that is not
NULL.
| All types
[...]
+| LAST | Finds the value with the largest timestamp that is not NULL.
| All types
[...]
+| STDDEV | Alias for STDDEV_SAMP, calculates the sample standard
deviation.
| INT32 INT64 FLOAT
DOUBLE [...]
+| STDDEV_POP | Calculates the population standard deviation.
| INT32 INT64 FLOAT DOUBLE
[...]
+| STDDEV_SAMP | Calculates the sample standard deviation.
| INT32 INT64 FLOAT DOUBLE
[...]
+| VARIANCE | Alias for VAR_SAMP, calculates the sample variance.
| INT32 INT64 FLOAT DOUBLE
[...]
+| VAR_POP | Calculates the population variance.
| INT32 INT64 FLOAT DOUBLE
[...]
+| VAR_SAMP | Calculates the sample variance.
| INT32 INT64 FLOAT DOUBLE
[...]
+| EXTREME | Finds the value with the largest absolute value. If the
largest absolute values of positive and negative values are equal, returns the
positive value.
| INT32 INT64 FLOAT
DOUBLE [...]
+| MODE | Finds the mode. Note: 1. There is a risk of memory exception
when the number of distinct values in the input sequence is too large; 2. If
all elements have the same frequency, i.e., there is no mode, a random element
is returned; 3. If there are multiple modes, a random mode is returned; 4. NULL
values are also counted in frequency, so even if not all values in the input
sequence are NULL, the final result may still be NULL. | All types
[...]
+| MAX_BY | MAX_BY(x, y) finds the value of x corresponding to the
maximum y in the binary input x and y. MAX_BY(time, x) returns the timestamp
when x is at its maximum.
| x and y can be of
any type [...]
+| MIN_BY | MIN_BY(x, y) finds the value of x corresponding to the
minimum y in the binary input x and y. MIN_BY(time, x) returns the timestamp
when x is at its minimum.
| x and y can be of
any type [...]
+| FIRST_BY | FIRST_BY(x, y) finds the value of x in the same row when y
is the first non-null value.
| x and y can be of any type
[...]
+| LAST_BY | LAST_BY(x, y) finds the value of x in the same row when y is
the last non-null value.
| x and y can be of any type
[...]
+
### 2.3 Examples
@@ -179,7 +208,29 @@ Total line number = 1
It costs 0.834s
```
-#### 2.3.3 First
+
+#### 2.3.3 Count_if
+
+Count `Non-Null` `arrival_time` Records in `table2`
+
+```sql
+select count_if(arrival_time is not null) from table2;
+```
+
+The execution result is as follows:
+
+```sql
++-----+
+|_col0|
++-----+
+| 4|
++-----+
+Total line number = 1
+It costs 0.047s
+```
+
+
+#### 2.3.4 First
Finds the values with the smallest timestamp that are not NULL in the
`temperature` and `humidity` columns.
@@ -199,7 +250,7 @@ Total line number = 1
It costs 0.170s
```
-#### 2.3.4 Last
+#### 2.3.5 Last
Finds the values with the largest timestamp that are not NULL in the
`temperature` and `humidity` columns.
@@ -219,7 +270,7 @@ Total line number = 1
It costs 0.211s
```
-#### 2.3.5 First_by
+#### 2.3.6 First_by
Finds the `time` value of the row with the smallest timestamp that is not NULL
in the `temperature` column, and the `humidity` value of the row with the
smallest timestamp that is not NULL in the `temperature` column.
@@ -239,7 +290,7 @@ Total line number = 1
It costs 0.269s
```
-#### 2.3.6 Last_by
+#### 2.3.7 Last_by
Queries the `time` value of the row with the largest timestamp that is not
NULL in the `temperature` column, and the `humidity` value of the row with the
largest timestamp that is not NULL in the `temperature` column.
@@ -259,7 +310,7 @@ Total line number = 1
It costs 0.070s
```
-#### 2.3.7 Max_by
+#### 2.3.8 Max_by
Queries the `time` value of the row where the `temperature` column is at its
maximum, and the `humidity` value of the row where the `temperature` column is
at its maximum.
@@ -279,7 +330,7 @@ Total line number = 1
It costs 0.172s
```
-#### 2.3.8 Min_by
+#### 2.3.9 Min_by
Queries the `time` value of the row where the `temperature` column is at its
minimum, and the `humidity` value of the row where the `temperature` column is
at its minimum.
@@ -299,6 +350,7 @@ Total line number = 1
It costs 0.244s
```
+
## 3. Logical operators
### 3.1 Overview
@@ -1089,7 +1141,7 @@ SELECT regexp_like('1a 2b 14m', '\\d+b'); -- true
- `\d+` means "one or more digits".
- `b` represents the letter b.
- In `'1a 2b 14m'`, the substring `'2b'` matches this pattern, so it returns
`true`.
- -
+
#### **Example 2: Matching the entire string**
diff --git a/src/zh/UserGuide/Master/Table/SQL-Manual/Basis-Function.md
b/src/zh/UserGuide/Master/Table/SQL-Manual/Basis-Function.md
index de1f2fd1..62ad1263 100644
--- a/src/zh/UserGuide/Master/Table/SQL-Manual/Basis-Function.md
+++ b/src/zh/UserGuide/Master/Table/SQL-Manual/Basis-Function.md
@@ -120,6 +120,32 @@ SELECT * FROM table1 WHERE temperature IN (85.0, 90.0);
SELECT * FROM table1 WHERE region NOT IN ('北京', '上海');
```
+### 1.5 GREATEST 和 LEAST
+
+`Greatest` 函数用于返回参数列表中的最大值，`Least` 函数用于返回参数列表中的最小值，返回数据类型与输入类型相同。
+1. 空值处理：若所有参数均为 NULL，则返回 NULL。
+2. 参数要求：必须提供 至少 2 个参数。
+3. 类型约束：仅支持 相同数据类型 的参数比较。
+4. 支持类型：
`BOOLEAN`、`FLOAT`、`DOUBLE`、`INT32`、`INT64`、`STRING`、`TEXT`、`TIMESTAMP`、`DATE`
+
+**语法：**
+
+```sql
+ greatest(value1, value2, ..., valueN)
+ least(value1, value2, ..., valueN)
+```
+
+**示例：**
+
+```sql
+-- 查询 table2 中 temperature 和 humidity 的最大记录
+SELECT GREATEST(temperature,humidity) FROM table2;
+
+-- 查询 table2 中 temperature 和 humidity 的最小记录
+SELECT LEAST(temperature,humidity) FROM table2;
+```
+
+
## 2. 聚合函数
### 2.1 概述
@@ -127,29 +153,31 @@ SELECT * FROM table1 WHERE region NOT IN ('北京', '上海');
1. 聚合函数是多对一函数。它们对一组值进行聚合计算，得到单个聚合结果。
2. 除了 `COUNT()`之外，其他所有聚合函数都忽略空值，并在没有输入行或所有值为空时返回空值。 例如，`SUM()` 返回 null 而不是零，而
`AVG()` 在计数中不包括 null 值。
-### 2.2 支持的聚合函数
-
-| 函数名 | 功能描述 |
允许的输入类型 | 输出类型 |
-| ----------- | ------------------------------------------------------------ |
------------------------ | ----------------------------- |
-| COUNT | 计算数据点数。 | 所有类型
| INT64 |
-| SUM | 求和。 |
INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| AVG | 求平均值。 |
INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| MAX | 求最大值。 | 所有类型
| 与输入类型一致 |
-| MIN | 求最小值。 | 所有类型
| 与输入类型一致 |
-| FIRST | 求时间戳最小且不为 NULL 的值。 | 所有类型
| 与输入类型一致 |
-| LAST | 求时间戳最大且不为 NULL 的值。 | 所有类型
| 与输入类型一致 |
-| STDDEV | STDDEV_SAMP 的别名，求样本标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| STDDEV_POP | 求总体标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| STDDEV_SAMP | 求样本标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| VARIANCE | VAR_SAMP 的别名，求样本方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| VAR_POP | 求总体方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| VAR_SAMP | 求样本方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| EXTREME | 求具有最大绝对值的值。如果正值和负值的最大绝对值相等，则返回正值。 | INT32 INT64 FLOAT DOUBLE |
与输入类型一致 |
-| MODE | 求众数。注意： 1.输入序列的不同值个数过多时会有内存异常风险;
2.如果所有元素出现的频次相同，即没有众数，则随机返回一个元素; 3.如果有多个众数，则随机返回一个众数； 4. NULL
值也会被统计频次，所以即使输入序列的值不全为 NULL，最终结果也可能为 NULL。 | 所有类型 | 与输入类型一致
|
-| MAX_BY | MAX_BY(x, y) 求二元输入 x 和 y 在 y 最大时对应的 x 的值。MAX_BY(time, x) 返回 x
取最大值时对应的时间戳。 | x 和 y 可以是任意类型 | 与第一个输入 x 的数据类型一致 |
-| MIN_BY | MIN_BY(x, y) 求二元输入 x 和 y 在 y 最小时对应的 x 的值。MIN_BY(time, x) 返回 x
取最小值时对应的时间戳。 | x 和 y 可以是任意类型 | 与第一个输入 x 的数据类型一致 |
-| FIRST_BY | FIRST_BY(x, y) 求当 y 为第一个不为 NULL 的值时，同一行里对应的 x 值。 | x 和 y
可以是任意类型 | 与第一个输入 x 的数据类型一致 |
-| LAST_BY | LAST_BY(x, y) 求当 y 为最后一个不为 NULL 的值时，同一行里对应的 x 值。 | x 和 y
可以是任意类型 | 与第一个输入 x 的数据类型一致 |
+### 2.2 支持的聚合函数
+
+| 函数名 | 功能描述 |
允许的输入类型 | 输出类型 |
+| ----------- | ------------------------------------------------------------
|-----------------------------------------------|------------------|
+| COUNT | 计算数据点数。 | 所有类型
| INT64 |
+| COUNT_IF | COUNT_IF(exp) 用于统计满足指定布尔表达式的记录行数 | exp 必须是一个布尔类型的表达式，例如
count_if(temperature>20) | INT64 |
+| SUM | 求和。 |
INT32 INT64 FLOAT DOUBLE | DOUBLE |
+| AVG | 求平均值。 |
INT32 INT64 FLOAT DOUBLE | DOUBLE |
+| MAX | 求最大值。 | 所有类型
| 与输入类型一致 |
+| MIN | 求最小值。 | 所有类型
| 与输入类型一致 |
+| FIRST | 求时间戳最小且不为 NULL 的值。 | 所有类型
| 与输入类型一致 |
+| LAST | 求时间戳最大且不为 NULL 的值。 | 所有类型
| 与输入类型一致 |
+| STDDEV | STDDEV_SAMP 的别名，求样本标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| STDDEV_POP | 求总体标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| STDDEV_SAMP | 求样本标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| VARIANCE | VAR_SAMP 的别名，求样本方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| VAR_POP | 求总体方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| VAR_SAMP | 求样本方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| EXTREME | 求具有最大绝对值的值。如果正值和负值的最大绝对值相等，则返回正值。 | INT32 INT64 FLOAT DOUBLE
| 与输入类型一致 |
+| MODE | 求众数。注意： 1.输入序列的不同值个数过多时会有内存异常风险;
2.如果所有元素出现的频次相同，即没有众数，则随机返回一个元素; 3.如果有多个众数，则随机返回一个众数； 4. NULL
值也会被统计频次，所以即使输入序列的值不全为 NULL，最终结果也可能为 NULL。 | 所有类型
| 与输入类型一致 |
+| MAX_BY | MAX_BY(x, y) 求二元输入 x 和 y 在 y 最大时对应的 x 的值。MAX_BY(time, x) 返回 x
取最大值时对应的时间戳。 | x 和 y 可以是任意类型 | 与第一个输入 x 的数据类型一致
|
+| MIN_BY | MIN_BY(x, y) 求二元输入 x 和 y 在 y 最小时对应的 x 的值。MIN_BY(time, x) 返回 x
取最小值时对应的时间戳。 | x 和 y 可以是任意类型 | 与第一个输入 x 的数据类型一致
|
+| FIRST_BY | FIRST_BY(x, y) 求当 y 为第一个不为 NULL 的值时，同一行里对应的 x 值。 | x 和 y
可以是任意类型 | 与第一个输入 x 的数据类型一致 |
+| LAST_BY | LAST_BY(x, y) 求当 y 为最后一个不为 NULL 的值时，同一行里对应的 x 值。 | x 和 y
可以是任意类型 | 与第一个输入 x 的数据类型一致 |
+
### 2.3 示例
@@ -179,7 +207,29 @@ Total line number = 1
It costs 0.834s
```
-#### 2.3.3 First
+
+#### 2.3.3 Count_if
+
+统计 `table2` 中 到达时间 `arrival_time` 不是 `null` 的记录行数。
+
+```sql
+select count_if(arrival_time is not null) from table2;
+```
+
+执行结果如下：
+
+```sql
++-----+
+|_col0|
++-----+
+| 4|
++-----+
+Total line number = 1
+It costs 0.047s
+```
+
+
+#### 2.3.4 First
查询`temperature`列、`humidity`列时间戳最小且不为 NULL 的值。
@@ -199,7 +249,7 @@ Total line number = 1
It costs 0.170s
```
-#### 2.3.4 Last
+#### 2.3.5 Last
查询`temperature`列、`humidity`列时间戳最大且不为 NULL 的值。
@@ -219,7 +269,7 @@ Total line number = 1
It costs 0.211s
```
-#### 2.3.5 First_by
+#### 2.3.6 First_by
查询 `temperature` 列中非 NULL 且时间戳最小的行的 `time` 值，以及 `temperature` 列中非 NULL
且时间戳最小的行的 `humidity` 值。
@@ -239,7 +289,7 @@ Total line number = 1
It costs 0.269s
```
-#### 2.3.6 Last_by
+#### 2.3.7 Last_by
查询`temperature` 列中非 NULL 且时间戳最大的行的 `time` 值，以及 `temperature` 列中非 NULL
且时间戳最大的行的 `humidity` 值。
@@ -259,7 +309,7 @@ Total line number = 1
It costs 0.070s
```
-#### 2.3.7 Max_by
+#### 2.3.8 Max_by
查询`temperature` 列中最大值所在行的 `time` 值，以及`temperature` 列中最大值所在行的 `humidity` 值。
@@ -279,7 +329,7 @@ Total line number = 1
It costs 0.172s
```
-#### 2.3.8 Min_by
+#### 2.3.9 Min_by
查询`temperature` 列中最小值所在行的 `time` 值，以及`temperature` 列中最小值所在行的 `humidity` 值。
@@ -299,6 +349,8 @@ Total line number = 1
It costs 0.244s
```
+
+
## 3. 逻辑运算符
### 3.1 概述
@@ -636,31 +688,31 @@ It costs 0.319s
### 5.2 数学函数
-| 函数名 | 描述
| 输入 | 输出 | 用法 |
-|-----------|------------------------------------------------------------------------------------------------------------------------------------------------------------|
--------------------------- | ---------------------- | ---------- |
-| sin | 正弦函数
| double、float、INT64、INT32 | double | sin(x) |
-| cos | 余弦函数
| double、float、INT64、INT32 | double | cos(x) |
-| tan | 正切函数
| double、float、INT64、INT32 | double | tan(x) |
-| asin | 反正弦函数
| double、float、INT64、INT32 | double | asin(x) |
-| acos | 反余弦函数
| double、float、INT64、INT32 | double | acos(x) |
-| atan | 反正切函数
| double、float、INT64、INT32 | double | atan(x) |
-| sinh | 双曲正弦函数
| double、float、INT64、INT32 | double | sinh(x) |
-| cosh | 双曲余弦函数
| double、float、INT64、INT32 | double | cosh(x) |
-| tanh | 双曲正切函数
| double、float、INT64、INT32 | double | tanh(x) |
-| degrees | 将弧度角 x 转换为度
| double、float、INT64、INT32 | double | degrees(x) |
-| radians | 将度转换为弧度
| double、float、INT64、INT32 | double | radians(x) |
-| abs | 绝对值
| double、float、INT64、INT32 | 返回与输入类型相同的值 | abs(x) |
-| sign | 返回 x 的符号函数，即：如果参数为 0，则返回 0，如果参数大于 0，则返回 1，如果参数小于 0，则返回 -1。对于
double/float 类型的参数，函数还会返回：如果参数为 NaN，则返回 NaN，如果参数为 +Infinity，则返回 1.0，如果参数为
-Infinity，则返回 -1.0。 | double、float、INT64、INT32 | 返回与输入类型相同的值 | sign(x) |
-| ceil | 返回 x 向上取整到最近的整数。
| double、float、INT64、INT32 | double | ceil(x) |
-| floor | 返回 x 向下取整到最近的整数。
| double、float、INT64、INT32 | double | floor(x) |
-| exp | 返回欧拉数 e 的 x 次幂。
| double、float、INT64、INT32 | double | exp(x) |
-| ln | 返回 x 的自然对数。
| double、float、INT64、INT32 | double | ln(x) |
-| log10 | 返回 x 的以 10 为底的对数。
| double、float、INT64、INT32 | double | log10(x) |
-| round | 返回 x 四舍五入到最近的整数。
| double、float、INT64、INT32 | double | round(x) |
-| round | 返回 x 四舍五入到 d 位小数。
| double、float、INT64、INT32 | double
| round(x, d) |
-| sqrt | 返回 x 的平方根。
| double、float、INT64、INT32 | double | sqrt(x) |
-| e | 自然指数
| | double | e() |
-| pi | π
| | double | pi() |
+| 函数名 | 描述
| 输入 | 输出 |
用法 |
+|-------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------|
---------------------- | ---------- |
+| sin | 正弦函数
| double、float、INT64、INT32 | double |
sin(x) |
+| cos | 余弦函数
| double、float、INT64、INT32 | double |
cos(x) |
+| tan | 正切函数
| double、float、INT64、INT32 | double |
tan(x) |
+| asin | 反正弦函数
| double、float、INT64、INT32 | double |
asin(x) |
+| acos | 反余弦函数
| double、float、INT64、INT32 | double |
acos(x) |
+| atan | 反正切函数
| double、float、INT64、INT32 | double |
atan(x) |
+| sinh | 双曲正弦函数
| double、float、INT64、INT32 | double |
sinh(x) |
+| cosh | 双曲余弦函数
| double、float、INT64、INT32 | double |
cosh(x) |
+| tanh | 双曲正切函数
| double、float、INT64、INT32 | double |
tanh(x) |
+| degrees | 将弧度角 x 转换为度
| double、float、INT64、INT32 | double |
degrees(x) |
+| radians | 将度转换为弧度
| double、float、INT64、INT32 | double |
radians(x) |
+| abs | 绝对值
| double、float、INT64、INT32 | 返回与输入类型相同的值 | abs(x)
|
+| sign | 返回 x 的符号函数，即：如果参数为 0，则返回 0，如果参数大于 0，则返回 1，如果参数小于 0，则返回
-1。对于 double/float 类型的参数，函数还会返回：如果参数为 NaN，则返回 NaN，如果参数为 +Infinity，则返回 1.0，如果参数为
-Infinity，则返回 -1.0。 | double、float、INT64、INT32 | 返回与输入类型相同的值 | sign(x)
|
+| ceil | 返回 x 向上取整到最近的整数。
| double、float、INT64、INT32 | double |
ceil(x) |
+| floor | 返回 x 向下取整到最近的整数。
| double、float、INT64、INT32 | double |
floor(x) |
+| exp | 返回欧拉数 e 的 x 次幂。
| double、float、INT64、INT32 | double |
exp(x) |
+| ln | 返回 x 的自然对数。
| double、float、INT64、INT32 | double |
ln(x) |
+| log10 | 返回 x 的以 10 为底的对数。
| double、float、INT64、INT32 | double |
log10(x) |
+| round | 返回 x 四舍五入到最近的整数。
| double、float、INT64、INT32 | double |
round(x) |
+| round | 返回 x 四舍五入到 d 位小数。
| double、float、INT64、INT32 | double
| round(x, d) |
+| sqrt | 返回 x 的平方根。
| double、float、INT64、INT32 | double |
sqrt(x) |
+| e | 自然指数
| | double |
e() |
+| pi | π
| | double |
pi() |
## 6. 条件表达式
diff --git a/src/zh/UserGuide/latest-Table/SQL-Manual/Basis-Function.md
b/src/zh/UserGuide/latest-Table/SQL-Manual/Basis-Function.md
index 383c53dd..00e421f3 100644
--- a/src/zh/UserGuide/latest-Table/SQL-Manual/Basis-Function.md
+++ b/src/zh/UserGuide/latest-Table/SQL-Manual/Basis-Function.md
@@ -120,6 +120,32 @@ SELECT * FROM table1 WHERE temperature IN (85.0, 90.0);
SELECT * FROM table1 WHERE region NOT IN ('北京', '上海');
```
+### 1.5 GREATEST 和 LEAST
+
+`Greatest` 函数用于返回参数列表中的最大值，`Least` 函数用于返回参数列表中的最小值，返回数据类型与输入类型相同。
+1. 空值处理：若所有参数均为 NULL，则返回 NULL。
+2. 参数要求：必须提供 至少 2 个参数。
+3. 类型约束：仅支持 相同数据类型 的参数比较。
+4. 支持类型：
`BOOLEAN`、`FLOAT`、`DOUBLE`、`INT32`、`INT64`、`STRING`、`TEXT`、`TIMESTAMP`、`DATE`
+
+**语法：**
+
+```sql
+ greatest(value1, value2, ..., valueN)
+ least(value1, value2, ..., valueN)
+```
+
+**示例：**
+
+```sql
+-- 查询 table2 中 temperature 和 humidity 的最大记录
+SELECT GREATEST(temperature,humidity) FROM table2;
+
+-- 查询 table2 中 temperature 和 humidity 的最小记录
+SELECT LEAST(temperature,humidity) FROM table2;
+```
+
+
## 2. 聚合函数
### 2.1 概述
@@ -129,27 +155,29 @@ SELECT * FROM table1 WHERE region NOT IN ('北京', '上海');
### 2.2 支持的聚合函数
-| 函数名 | 功能描述 |
允许的输入类型 | 输出类型 |
-| ----------- | ------------------------------------------------------------ |
------------------------ | ----------------------------- |
-| COUNT | 计算数据点数。 | 所有类型
| INT64 |
-| SUM | 求和。 |
INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| AVG | 求平均值。 |
INT32 INT64 FLOAT DOUBLE | DOUBLE |
-| MAX | 求最大值。 | 所有类型
| 与输入类型一致 |
-| MIN | 求最小值。 | 所有类型
| 与输入类型一致 |
-| FIRST | 求时间戳最小且不为 NULL 的值。 | 所有类型
| 与输入类型一致 |
-| LAST | 求时间戳最大且不为 NULL 的值。 | 所有类型
| 与输入类型一致 |
-| STDDEV | STDDEV_SAMP 的别名，求样本标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| STDDEV_POP | 求总体标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| STDDEV_SAMP | 求样本标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| VARIANCE | VAR_SAMP 的别名，求样本方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| VAR_POP | 求总体方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| VAR_SAMP | 求样本方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
-| EXTREME | 求具有最大绝对值的值。如果正值和负值的最大绝对值相等，则返回正值。 | INT32 INT64 FLOAT DOUBLE |
与输入类型一致 |
-| MODE | 求众数。注意： 1.输入序列的不同值个数过多时会有内存异常风险;
2.如果所有元素出现的频次相同，即没有众数，则随机返回一个元素; 3.如果有多个众数，则随机返回一个众数； 4. NULL
值也会被统计频次，所以即使输入序列的值不全为 NULL，最终结果也可能为 NULL。 | 所有类型 | 与输入类型一致
|
-| MAX_BY | MAX_BY(x, y) 求二元输入 x 和 y 在 y 最大时对应的 x 的值。MAX_BY(time, x) 返回 x
取最大值时对应的时间戳。 | x 和 y 可以是任意类型 | 与第一个输入 x 的数据类型一致 |
-| MIN_BY | MIN_BY(x, y) 求二元输入 x 和 y 在 y 最小时对应的 x 的值。MIN_BY(time, x) 返回 x
取最小值时对应的时间戳。 | x 和 y 可以是任意类型 | 与第一个输入 x 的数据类型一致 |
-| FIRST_BY | FIRST_BY(x, y) 求当 y 为第一个不为 NULL 的值时，同一行里对应的 x 值。 | x 和 y
可以是任意类型 | 与第一个输入 x 的数据类型一致 |
-| LAST_BY | LAST_BY(x, y) 求当 y 为最后一个不为 NULL 的值时，同一行里对应的 x 值。 | x 和 y
可以是任意类型 | 与第一个输入 x 的数据类型一致 |
+| 函数名 | 功能描述 |
允许的输入类型 | 输出类型 |
+| ----------- | ------------------------------------------------------------
|-----------------------------------------------|------------------|
+| COUNT | 计算数据点数。 | 所有类型
| INT64 |
+| COUNT_IF | COUNT_IF(exp) 用于统计满足指定布尔表达式的记录行数 | exp 必须是一个布尔类型的表达式，例如
count_if(temperature>20) | INT64 |
+| SUM | 求和。 |
INT32 INT64 FLOAT DOUBLE | DOUBLE |
+| AVG | 求平均值。 |
INT32 INT64 FLOAT DOUBLE | DOUBLE |
+| MAX | 求最大值。 | 所有类型
| 与输入类型一致 |
+| MIN | 求最小值。 | 所有类型
| 与输入类型一致 |
+| FIRST | 求时间戳最小且不为 NULL 的值。 | 所有类型
| 与输入类型一致 |
+| LAST | 求时间戳最大且不为 NULL 的值。 | 所有类型
| 与输入类型一致 |
+| STDDEV | STDDEV_SAMP 的别名，求样本标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| STDDEV_POP | 求总体标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| STDDEV_SAMP | 求样本标准差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| VARIANCE | VAR_SAMP 的别名，求样本方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| VAR_POP | 求总体方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| VAR_SAMP | 求样本方差。 | INT32
INT64 FLOAT DOUBLE | DOUBLE |
+| EXTREME | 求具有最大绝对值的值。如果正值和负值的最大绝对值相等，则返回正值。 | INT32 INT64 FLOAT DOUBLE
| 与输入类型一致 |
+| MODE | 求众数。注意： 1.输入序列的不同值个数过多时会有内存异常风险;
2.如果所有元素出现的频次相同，即没有众数，则随机返回一个元素; 3.如果有多个众数，则随机返回一个众数； 4. NULL
值也会被统计频次，所以即使输入序列的值不全为 NULL，最终结果也可能为 NULL。 | 所有类型
| 与输入类型一致 |
+| MAX_BY | MAX_BY(x, y) 求二元输入 x 和 y 在 y 最大时对应的 x 的值。MAX_BY(time, x) 返回 x
取最大值时对应的时间戳。 | x 和 y 可以是任意类型 | 与第一个输入 x 的数据类型一致
|
+| MIN_BY | MIN_BY(x, y) 求二元输入 x 和 y 在 y 最小时对应的 x 的值。MIN_BY(time, x) 返回 x
取最小值时对应的时间戳。 | x 和 y 可以是任意类型 | 与第一个输入 x 的数据类型一致
|
+| FIRST_BY | FIRST_BY(x, y) 求当 y 为第一个不为 NULL 的值时，同一行里对应的 x 值。 | x 和 y
可以是任意类型 | 与第一个输入 x 的数据类型一致 |
+| LAST_BY | LAST_BY(x, y) 求当 y 为最后一个不为 NULL 的值时，同一行里对应的 x 值。 | x 和 y
可以是任意类型 | 与第一个输入 x 的数据类型一致 |
+
### 2.3 示例
@@ -179,7 +207,29 @@ Total line number = 1
It costs 0.834s
```
-#### 2.3.3 First
+
+#### 2.3.3 Count_if
+
+统计 `table2` 中 到达时间 `arrival_time` 不是 `null` 的记录行数。
+
+```sql
+select count_if(arrival_time is not null) from table2;
+```
+
+执行结果如下：
+
+```sql
++-----+
+|_col0|
++-----+
+| 4|
++-----+
+Total line number = 1
+It costs 0.047s
+```
+
+
+#### 2.3.4 First
查询`temperature`列、`humidity`列时间戳最小且不为 NULL 的值。
@@ -199,7 +249,7 @@ Total line number = 1
It costs 0.170s
```
-#### 2.3.4 Last
+#### 2.3.5 Last
查询`temperature`列、`humidity`列时间戳最大且不为 NULL 的值。
@@ -219,7 +269,7 @@ Total line number = 1
It costs 0.211s
```
-#### 2.3.5 First_by
+#### 2.3.6 First_by
查询 `temperature` 列中非 NULL 且时间戳最小的行的 `time` 值，以及 `temperature` 列中非 NULL
且时间戳最小的行的 `humidity` 值。
@@ -239,7 +289,7 @@ Total line number = 1
It costs 0.269s
```
-#### 2.3.6 Last_by
+#### 2.3.7 Last_by
查询`temperature` 列中非 NULL 且时间戳最大的行的 `time` 值，以及 `temperature` 列中非 NULL
且时间戳最大的行的 `humidity` 值。
@@ -259,7 +309,7 @@ Total line number = 1
It costs 0.070s
```
-#### 2.3.7 Max_by
+#### 2.3.8 Max_by
查询`temperature` 列中最大值所在行的 `time` 值，以及`temperature` 列中最大值所在行的 `humidity` 值。
@@ -279,7 +329,7 @@ Total line number = 1
It costs 0.172s
```
-#### 2.3.8 Min_by
+#### 2.3.9 Min_by
查询`temperature` 列中最小值所在行的 `time` 值，以及`temperature` 列中最小值所在行的 `humidity` 值。
@@ -299,6 +349,7 @@ Total line number = 1
It costs 0.244s
```
+
## 3. 逻辑运算符
### 3.1 概述
