eldenmoon commented on code in PR #2721:
URL: https://github.com/apache/doris-website/pull/2721#discussion_r2278512869
##########
docs/sql-manual/sql-functions/scalar-functions/array-functions/array-avg.md:
##########
@@ -5,58 +5,157 @@
}
---
+## array_avg
+
+<version since="2.0.0">
+
+</version>
+
## Description
-Get the average of all elements in an array (`NULL` values are skipped).
-When the array is empty or all elements in the array are `NULL` values, the
function returns `NULL`.
+
+Calculates the average of all numeric elements in an array. The function skips
null values and non-numeric elements in the array, only calculating the average
for valid numeric elements.
## Syntax
+
```sql
-ARRAY_AVG(<arr>)
+array_avg(ARRAY<T> arr)
```
-## Parameters
-| Parameter | Description |
-|---|---|
-| `<arr>` | The array to calculate the average values from |
+### Parameters
-## Return Value
+- `arr`：ARRAY<T> type, the array for which to calculate the average. Supports
column names or constant values.
-Returns a constant. Special cases:
-- `NULL` values in the array will be skipped.
-- Strings and varchar in the array will be skipped.
+**Supported types for T:**
+- Numeric types: TINYINT, SMALLINT, INT, BIGINT, LARGEINT, FLOAT, DOUBLE,
DECIMAL
+- String types: CHAR, VARCHAR, STRING (will attempt to convert to numeric)
+- Boolean type: BOOLEAN (will attempt to convert to numeric)
-## Example
+### Return Value
+
+Return type: Automatically selected based on input type
+
+Return value meaning:
+- Returns the average of all valid numeric elements in the array
+- NULL: if the array is empty, or all elements are NULL or cannot be converted
to numeric
+
+Usage notes:
+- If the array contains other types (such as strings), it will attempt to
convert elements to DOUBLE type. Elements that fail conversion will be skipped
and not included in the average calculation
+- The function will attempt to convert all elements to compatible numeric
types for average calculation. The return type of the average is automatically
selected based on the input type:
+ - When input is DOUBLE or FLOAT, returns DOUBLE
+ - When input is integer types, returns DOUBLE
+ - When input is DECIMAL, returns DECIMAL, maintaining original precision and
scale
+- Empty arrays return NULL, arrays with only one element return that element's
value
+- If the array is NULL, it will return a type conversion error
+- Nested arrays, MAP, STRUCT and other complex types are not supported for
average calculation, calling will result in an error
+- For null values in array elements: null elements are not included in the
average calculation
+
+### Examples
```sql
-create table array_type_table(
- k1 INT,
- k2 Array<int>
-)
-duplicate key (k1)
-distributed by hash(k1) buckets 1
-properties(
- 'replication_num' = '1'
+CREATE TABLE array_avg_test (
+ id INT,
+ int_array ARRAY<INT>,
+ double_array ARRAY<DOUBLE>,
+ mixed_array ARRAY<STRING>
+)
+DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(id) BUCKETS 3
+PROPERTIES (
+ "replication_num" = "1"
);
-insert into array_type_table values (0, []), (1, [NULL]), (2, [1, 2, 3]), (3,
[1, NULL, 3]);
-select k2, array_avg(k2) from array_type_table;
+
+INSERT INTO array_avg_test VALUES
+(1, [1, 2, 3, 4, 5], [1.1, 2.2, 3.3, 4.4, 5.5], ['1', '2', '3', '4', '5']),
+(2, [10, 20, 30], [10.5, 20.5, 30.5], ['10', '20', '30']),
+(3, [], [], []),
+(4, NULL, NULL, NULL),
+(5, [1, null, 3, null, 5], [1.1, null, 3.3, null, 5.5], ['1', null, '3', null,
'5']);
+```
+
+**Query Examples:**
+
+Calculate the average of double_array:
+```sql
+SELECT array_avg(double_array) FROM array_avg_test WHERE id = 1;
++-------------------------+
+| array_avg(double_array) |
++-------------------------+
+| 3.3 |
++-------------------------+
+```
+
+Calculate the average of a mixed-type array, strings will be converted to
numeric:
+```sql
+SELECT array_avg(mixed_array) FROM array_avg_test WHERE id = 1;
++------------------------+
+| array_avg(mixed_array) |
++------------------------+
+| 3 |
++------------------------+
+```
+
+Empty array returns NULL:
+```sql
+SELECT array_avg(int_array) FROM array_avg_test WHERE id = 3;
++----------------------+
+| array_avg(int_array) |
++----------------------+
+| NULL |
++----------------------+
```
-```text
-+--------------+-----------------+
-| k2 | array_avg(`k2`) |
-+--------------+-----------------+
-| [] | NULL |
-| [NULL] | NULL |
-| [1, 2, 3] | 2 |
-| [1, NULL, 3] | 2 |
-+--------------+-----------------+
+
+NULL array returns NULL:
+```sql
+SELECT array_avg(int_array) FROM array_avg_test WHERE id = 4;
++----------------------+
+| array_avg(int_array) |
++----------------------+
+| NULL |
++----------------------+
+```
+
+Array containing null values, null elements are not included in calculation:
+```sql
+SELECT array_avg(int_array) FROM array_avg_test WHERE id = 5;
++----------------------+
+| array_avg(int_array) |
++----------------------+
+| 3 |
++----------------------+
+```
+
+Complex type examples:
+
+Nested array types are not supported, will error:
+```sql
+SELECT array_avg([[1,2,3]]);
+ERROR 1105 (HY000): errCode = 2, detailMessage = array_avg([[1, 2, 3]]) does
not support type: ARRAY<TINYINT>
+```
+
+Map types are not supported, will error:
+```sql
+SELECT array_avg([{'k':1},{'k':2}]);
+ERROR 1105 (HY000): errCode = 2, detailMessage = array_avg([map('k', 1),
map('k', 2)]) does not support type: MAP<VARCHAR(1),TINYINT>
+```
+
+Error when parameter count is wrong:
+```sql
+SELECT array_avg([1,2,3], [4,5,6]);
+ERROR 1105 (HY000): errCode = 2, detailMessage = Can not found function
'array_avg' which has 2 arity. Candidate functions are: [array_avg(Expression)]
```
+
+Error when passing non-array type:
```sql
-select array_avg(['test',2,1,null]);
+SELECT array_avg('not_an_array');
+ERROR 1105 (HY000): errCode = 2, detailMessage = Can not find the
compatibility function signature: array_avg(VARCHAR(12))
+```
+
+Array is NULL, will return type conversion error
```
-```text
-+------------------------------------------------------------+
-| array_avg(cast(['test', '2', '1', NULL] as ARRAY<DOUBLE>)) |
-+------------------------------------------------------------+
-| 1.5 |
-+------------------------------------------------------------+
+mysql> SELECT array_max(NULL);
+ERROR 1105 (HY000): errCode = 2, detailMessage = class
org.apache.doris.nereids.types.NullType cannot be cast to class
org.apache.doris.nereids.types.ArrayType
(org.apache.doris.nereids.types.NullType and
org.apache.doris.nereids.types.ArrayType are in unnamed module of loader 'app')
Review Comment:
需要修改下报错
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
