ARRAY_AVG

Return the arithmetic mean of the numeric elements in an array.

Category: collectionReturns: DOUBLEDialect: PostgreSql

Syntax

ARRAY_AVG(array)

Description

## Overview Computes the arithmetic mean of the non-NULL numeric elements in an array. ARRAY_AVG is the array-level analogue of the scalar AVG aggregate and is useful when per-row arrays already contain the sample of interest (for example, per-session latencies or per-order line totals) without first unnesting and re-aggregating. Typical uses include computing an average timing metric from a per-session latency array, summarising a histogram of measurements stored inline in a nested column, and producing denormalised KPI columns that describe the central tendency of each row's collection payload. ## Behavior - Returns a DOUBLE result for floating-point and integer input element types; DECIMAL inputs return DECIMAL with scale adjusted per standard averaging rules. - NULL elements are excluded from both the sum and the count; they do not bias the mean toward zero. - An all-NULL array produces NULL because the effective count is zero. - Works only on numeric element types. Averaging an array of STRING, ARRAY, MAP, or STRUCT raises a type error. - Computation is non-destructive; the input array is not modified. ## Null and empty handling - NULL input array returns NULL. - Empty array returns NULL (zero-sample mean is undefined). - Array whose elements are all NULL returns NULL for the same reason. - Mixed arrays of NULL and numeric elements compute the mean over the non-NULL subset only. ## Compatibility - Equivalent to UNNEST followed by a scalar AVG, without the overhead of materialising a relation. Engines that expose array/map types typically include this helper. - Floating-point accumulation uses pairwise summation where supported. Very large arrays of wildly different magnitudes can still incur minor rounding error.

Parameters

Name	Type	Description
`array`		Specifies the numeric array whose elements are averaged. Accepts INT, BIGINT, FLOAT, DOUBLE, or DECIMAL elements. NULL elements are excluded from the calculation.

Examples

-- Basic integer average
SELECT ARRAY_AVG(ARRAY[10, 20, 30]);  -- 20.0

-- Average a column of measurements per session
SELECT session_id, ARRAY_AVG(measurements) AS mean_measurement
FROM analytics.events.user_sessions;

-- NULL elements are ignored in the denominator
SELECT ARRAY_AVG(ARRAY[10, CAST(NULL AS INT), 30]);  -- 20.0

-- NULL array returns NULL
SELECT ARRAY_AVG(CAST(NULL AS ARRAY<DOUBLE>));  -- NULL

-- Empty array and all-NULL array both return NULL
SELECT ARRAY_AVG(CAST(ARRAY() AS ARRAY<INT>)) AS empty_avg,
       ARRAY_AVG(ARRAY[CAST(NULL AS INT), CAST(NULL AS INT)]) AS all_null_avg;

-- Combine with ARRAY_AGG to get per-group mean over a detail table
SELECT user_id, ARRAY_AVG(ARRAY_AGG(page_ms)) AS avg_page_ms
FROM analytics.events.user_sessions
GROUP BY user_id;

Pitfalls

An empty array and an all-NULL array both return NULL, not 0. Guard downstream arithmetic with COALESCE when a numeric default is required.
Integer element arrays are promoted to DOUBLE before the mean is computed. If you need a DECIMAL result, cast the array or its elements explicitly.
Very large magnitude differences between elements can erode precision even with pairwise summation. For regulated aggregates consider DECIMAL input arrays.
ARRAY_AVG is element-wise and ignores NULLs in the denominator. If NULL should count as zero in your domain, replace NULLs with 0 before calling the function.