Aggregate function

In database management, an aggregate function or aggregation function is a function where multiple values are processed together to form a single summary statistic.

Common aggregate functions include:

Average (i.e., arithmetic mean)

Count

Maximum

Median

Minimum

Mode

Range

Sum

Others include:

Nanmean (mean ignoring NaN values, also known as "nil" or "null")

Stddev

Formally, an aggregate function takes as input a set, a multiset (bag), or a list from some input domain I and outputs an element of an output domain O. The input and output domains may be the same, such as for SUM, or may be different, such as for COUNT.

Aggregate functions occur commonly in numerous programming languages, in spreadsheets, and in relational algebra.

The listagg function, as defined in the SQL:2016 standard

aggregates data from multiple rows into a single concatenated string.

In the entity relationship diagram, aggregation is represented as seen in Figure 1 with a rectangle around the relationship and its entities to indicate that it is being treated as an aggregate entity.

Decomposable aggregate functions

Aggregate functions present a bottleneck, because they potentially require having all input values at once. In distributed computing, it is desirable to divide such computations into smaller pieces, and distribute the work, usually computing in parallel, via a divide and conquer algorithm.

Some aggregate functions can be computed by computing the aggregate for subsets, and then aggregating these aggregates; examples include COUNT, MAX, MIN, and SUM. In other cases the aggregate can be computed by computing auxiliary numbers for subsets, aggregating these auxiliary numbers, and finally computing the overall number at the end; examples include AVERAGE (tracking sum and count, dividing at the end) and RANGE (tracking max and min, subtracting at the end). In other cases the aggregate cannot be computed without analyzing the entire set at once, though in some cases approximations can be distributed; examples include DISTINCT COUNT (Count-distinct problem), MEDIAN, and MODE.

Such functions are called decomposable aggregation functions or decomposable aggregate functions. The simplest may be referred to as self-decomposable aggregation functions, which are defined as those functions f such that there is a merge operator ⁠

⋄

{\displaystyle \diamond }

⁠ such that

f

(

X

⊎

Y

)

=

f

(

X

)

⋄

f

(

Y

)

{\displaystyle f(X\uplus Y)=f(X)\diamond f(Y)}

where ⁠

⊎

{\displaystyle \uplus }

⁠ is the union of multisets (see monoid homomorphism).

For example, SUM:

SUM

⁡

(

x

)

=

x

{\displaystyle \operatorname {SUM} ({x})=x}

, for a singleton;

SUM

⁡

(

X

⊎

Y

)

=

SUM

⁡

(

X

)

+

SUM

⁡

(

Y

)

{\displaystyle \operatorname {SUM} (X\uplus Y)=\operatorname {SUM} (X)+\operatorname {SUM} (Y)}

, meaning that merge ⁠

⋄

{\displaystyle \diamond }

⁠ is simply addition.