mean aggregate function

Returns the mean calculated from values of a group.

Syntax 

mean ( [ALL | DISTINCT] expr ) [FILTER ( WHERE cond ) ]

Arguments

  • expr: An expression that evaluates to a numeric.

  • cond: An optional boolean expression filtering the rows used for aggregation.

Returns

The result type is computed as for the arguments:

  • DECIMAL(p, s): The result type is a` DECIMAL(p + 4, s + 4)`. If the maximum precision for DECIMAL is reached the increase in scale will be limited to avoid loss of significant digits.

  • year-month interval: The result is an INTERVAL YEAR TO MONTH.

  • day-time interval: The result is an INTERVAL YEAR TO SECOND.

  • In all other cases the result is a DOUBLE.

Nulls within the group are ignored. If a group is empty or consists only of nulls the result is NULL.

If DISTINCT is specified the mean is computed after duplicates have been removed.

If the result overflows the result type, Databricks Runtime raises an overflow error. To return a NULL instead use try_avg.

Warning

If spark.sql.ansi.enabled is false an overflow will not cause an error but return NULL.

This function is a synonym for avg aggregate function.

Examples 

> SELECT mean(col) FROM VALUES (1), (2), (3) AS tab(col);
 2.0

> SELECT avg(DISTINCT col) FROM VALUES (1), (1), (2) AS tab(col);
 1.5

> SELECT mean(col) FROM VALUES (1), (2), (NULL) AS tab(col);
 1.5

> SELECT mean(col) FROM VALUES (INTERVAL '1' YEAR), (INTERVAL '2' YEAR) AS tab(col);
 1-6