try_avg
aggregate function
Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above
Returns the mean calculated from values of a group. If there is an overflow, returns NULL.
Syntax
try_avg( [ALL | DISTINCT] expr) [FILTER ( WHERE cond ) ]
This function can also be invoked as a window function using the OVER
clause.
Arguments
expr
: An expression that returns a numeric or an interval value.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 average is computed after duplicates are removed.
To raise an error instead of NULL in case of an overflow use avg.
Examples
> SELECT try_avg(col) FROM VALUES (1), (2), (3) AS tab(col);
2.0
> SELECT try_avg(DISTINCT col) FROM VALUES (1), (1), (2) AS tab(col);
1.5
> SELECT try_avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
1.5
> SELECT try_avg(col) FROM VALUES (INTERVAL '1' YEAR), (INTERVAL '2' YEAR) AS tab(col);
1-6
-- Overflow results in NULL for try_avg()
> SELECT try_avg(col) FROM VALUES (5e37::DECIMAL(38, 0)), (5e37::DECIMAL(38, 0)) AS tab(col);
NULL
-- Overflow causes error for avg() in ANSI mode.
> SELECT avg(col) FROM VALUES (5e37::DECIMAL(38, 0)), (5e37::DECIMAL(38, 0)) AS tab(col);
Error: CANNOT_CHANGE_DECIMAL_PRECISION