Current commercial DBMS SQL2 have five built-in aggregates and do not provide any mechanism to define new aggregates. Here is an example usage of the pre-defined aggregate avg.
The above SQL query computes the average salary of the employees from the
emp table. This computes a single, final average. In case of a very large
table, the user might be interested in partial results, rather than just the
final result. Say that the user wants to find the average salary every 5
record. Then he/she will define a new aggregate, say average, that
computes average every 5
record. And will like to use this newly defined
aggregate as follows:
The user might also use average in a group-by query, such as
or with a where clause condition.
The above queries will compute the aggregate in the same way as avg but
will return partial results, in this case every 5
record. SQL-AG
implementation of average is given in section 4.2.
The user can also use the pre-defined aggregates in nested queries. For example the following query finds the name of the employee with salaries greater than the average salary of their respective department.
These queries require the aggregates to return partial results. Hence the user-defined aggregates should also be able to return partial results. SQL-AG implementation of the above sub-query is given in section 4.5.
The above usages can be written in two generic forms as:
Where InputTable is the table on which the aggregate is being
computed, ResponsibleFields are the fields of
InputTable on which aggregate is computed and GroupFields are the
fields of InputTable on which user wants to group by.
Records of InputTable satisfying the WhereClause
condition are used in the computation of the aggregate.
InputTable
and InputTable
are name of the input
tables. Fields and Field
are fields of
InputTable
. WhereClause
is a condition
dependent on zero or more fields of the input tables.
One can easily note that ResponsibleFields, GroupFields and
Fields are set of fields, rather than just a field and
GroupFields
and Fields can be an empty set
also. WhereClause
can also be null.
The syntactic requirements posed by the above usages can be summarized as the requirements that SQL-AG aggregates must :
To support return of partial results the user-defined aggregates should be interactive and persistent. The aggregates created should store information persistently and return partial-results as the computation progresses.