AVG

avg([distinct] expr)

Description

The AVG function is used to calculate the arithmetic average of a specified expression within a set of data. When the distinct keyword is specified, the average of the deduplicated values will be calculated.

Parameter Description

• expr: Numeric type expression, which can be of type tinyint, smallint, int, bigint, float, double, or decimal.

Return Type

• For decimal type expressions, the AVG function returns a result of decimal type. The precision and scale of the return value may increase to accommodate the calculation result.
• For other numeric type expressions, the AVG function returns a result of double type.

Notes

• During the function calculation, null values will be ignored and will not participate in the average calculation.

Usage Example

1. Calculate the average value of a numeric column (excluding null values):

SELECT avg(col) FROM VALUES (1), (2), (3), (4), (null) AS tab(col);
+------------+
| `avg`(col) |
+------------+
| 2.5        |
+------------+

2. Calculate the average value of the numerical column after deduplication:

SELECT avg(DISTINCT col) FROM VALUES (1), (1), (2), (3), (4), (null) AS tab(col);
+---------------------+
| `avg`(DISTINCT col) |
+---------------------+
| 2.5                 |
+---------------------+