9.27. Standard and User-Defined Aggregate
In addition to whole set of standard SQL aggregate functions,
Virtuoso provides a method to create application-specific aggregate
functions to create complex data structures inside SQL queries.
The most evident way to calculate some value that depend on
number of rows of a table is to write a stored procedure that opens
a cursor, then fetches row after row in a loop and repeatedly
modifies some intermediate values ("accumulators") inside the loop.
When the fetch operation signals that there are no more data, a
final result is calculated from values of "accumulators"
E.g. to find an average of all values in a table's column COL,
one may open a cursor to fetch values from the COL, then set two
"accumulators" TOTAL and CNT to zero, then fetch row after row
adding 1 to CNT and adding current value of COL to TOTAL. At the
end of loop, an error should be signalled if CNT is zero, otherwise
the result is TOTAL divided by CNT.
Obviously, a competent programmer will use built-in AVG()
aggregate function inside a single SELECT statement: the code is
much more readable and the use of aggregate reduces the overhead in
filling in result-set and fetching from it.
More important advantage of aggregate functions is the ability
to process many groups of records in parallel, performing one table
scan instead of many. SQL optimizer may use sophisticated
heuristics to find the fastest way of doing a complex query with
aggregates but it cannot optimize the code of a stored