A user defined aggregate function (UDAF) works like built-in aggregate functions, such as SUM() or AVG(); in which a group of 0, 1, or more input rows generates a single result row. For example:
SELECT MIN(price) AS MinPrice FROM priceTable;
Although there are multiple input rows, there is only a single output row, in this case:
| MinPrice |
|---|
| 1.00 |
UDAFs, like built-in aggregate functions, can be used with the GROUP BY and HAVING clauses. Here is an example of a built-in aggregate function with GROUP BY:
SELECT department, MIN(price) AS MinPrice FROM priceTable GROUP BY department;
| Department | MinPrice |
|---|---|
| frozen foods | 5.00 |
| deli | 15.00 |
Many aggregate functions, including many UDAFs must keep some "state" information in between calls. For example, consider the SUM() function:
SELECT SUM(price) ...;
Unlike the salesTax() function, which is a scalar function rather than an aggregate function, the SUM() function must keep some internal state information between calls. In particular, as the function iterates through the list of rows in the table, for each row the function needs to know not only the price of the item in that row, but also the partial sum, or running total, of all of the rows processed so far, so that the function can add the current row's price to the sum of the previous rows' prices.