Commercial DBMS and SQL3

In response to their market, some commercial DBMS have implemented additional aggregates in their systems. For instance RISQL [Red96] is Red Brick's extension of SQL supporting complex queries and aggregates. RISQL supports pre-defined aggregates such as MovingAvg, MovingSum etc. It also allows the user to define queries with parameters using Create Macro.

The commercial world is expected to move towards user-defined aggregates, once the SQL3 standards, which are still in development, will be introduced. SQL3 [Iso96a,Iso96b,Mat96] proposes a concept of SQL-invoked routines termed as iterative routines to allow user-defined aggregates.

An iterative routine is composed of three routines :

• initialize routine, the routine that gives an initial value to the aggregate.
• iterate routine, the one that computes the intermediate value each time a new record of the table is accessed.
• terminate routine, the routine that returns the final value computed for the aggregate.

For example a user can define an iterative routine for average as :

Here average-init, average-iterate and average-terminate are also user-defined.

• Limitations of SQL3