As mentioned earlier, aggregate functions work just like standard functions except that they have a couple of extra routines. Those routines drive the aggregation for each group of data:
This routine is called at the beginning of each group of data. It resets any local data used to aggregate the data so that each group starts with an empty aggregation.
This routine is called for each row of data within a group. The purpose of the function is to perform any data aggregation required for the group.
The order in which routines are called illustrates the differences between standard and aggregate functions:
init reset add add add ... main reset add add add ... main reset add add add ... main ... deinit
While a standard function is comprised of a single loop, an aggregate
function is a loop of loops. For a standard function, the
main routine is called for each row of data, which
corresponds directly with each row of the result set. For an
aggregate function, the rows of the result set are made up of groups,
in which each row represents a group of rows of data. The
add routine is called for each row of data (except
the first), while the
main routine is called only
once for each group (resulting in one row of the result set).
While the signatures of the routines called within a standard function do not differ for an aggregate function, their roles are somewhat different.
Unlike a standard function, the
init function is almost always used for aggregate ...