Aggregate functions

Aggregate functions compute a single result from the elements in a bag.

Generalized aggregate functions return new bags and collections for given argument bags. They can also have more that one bag argument.

Basic aggregate functions have one of its arguments declared as a bag and return a single result:

    aggfn(Bag of Type1 x) -> Type2

Aggregate functions can be used in group by queries.

The following basic aggregate function are predefined:

   avg(Bag of Number b) -> Real               average of elements
   concatagg(Bag of Object b) -> Charstring   concatenate elements
   count(Bag of Object b) -> Integer          count number of elements
   max(Bag of TP b) -> TP                     largest element
   maxagg(Bag of TP b) -> TP                  same as max(b)
   min(Bag of TP b) -> TP                     smallest element
   minagg(Bag of TP b) -> TP                  same as min(b)
   stdev(Bag of Number b) -> Real             standard deviation of elements
   sum(Bag of Number b) -> Number             sum of elements

Descriptions:

Compute the average of the elements in a bag:

  avg(Bag of Number b) -> Real

For example avg(iota(1,100000)); returns 50000.5.

String concatenate the elements in a bag:

  concatagg(Bag b)-> Charstring s

For example: concatagg(bag("ab ",2,"cd ")); returns the string "ab2cd ".

Count the number of elements in a bag:

   count(Bag of Object b) -> Integer

For example count(iota(1,100000)); returns 100000.

Compute the largest element in a bag:

   max(Bag of TP b) -> TP r
   maxagg(Bag of Tp b) -> TP r

The type of the result is the same as the type of elements of argument bag. maxagg() is an alias for max(). For example max(bag(3,4,2))+2; returns 6.

Compute the smallest element in a bag:

   min(Bag of TP b) -> TP r
   minagg(Bag of Tp b) -> TP r

The type of the result is the same as the type of elements of argument bag. minagg() is an alias for min(). For example minagg(bag(3,4,2))+2; returns 4.

Compute the standard deviation of the elements in a bag:

   stdev(Bag of Number b) -> Real

For example stdev(iota(1,100000)); returns 28867.6577966877.

Sum the elements in a bag:

   sum(Bag of Number b) -> Number

For example sum(iota(1,100000)); returns 705082704.

Generalized aggregate functions

Normal aggregate functions return only single values. In sa.amos they may return collections as well, which is called generalized aggregate functions. Generalized aggregate functions can furthermofe have more that one bag arguments.

The following generalized aggregate functions are predefined:

   bequal(Bag b1, Bag b2) -> Boolean                           Test equality of bags
   exclusive(Bag of TP b) -> Bag of TP r                       Non-duplicated elements
   inject(Bag b, Object x) -> Bag of Object r                  Insert x between elements
   unique(Bag of TP b) -> Bag of TP r                          Remove duplicates
   tuples(Bag of Object b) -> Bag of Vector                    Return elements as vectors

Descriptions:

Test that two bags are equal:

bequal(Bag x, Bag y) -> Boolean

Example:

bequal(bag(1,2,3),bag(3,2,1)); returns TRUE;

Notice that bequal() materializes its arguments before doing the equality computation, which may occupy a lot of temporary space in the database image if the bags are large.

Extract non-duplicated elements from a bag:

   exclusive(Bag of TP b) -> Bag of TP r

The type of the result bag is the same as the type of elements of b. For example exclusive(bag(1,2,1,4)); returns the bag:

   2
   4

Remove duplicates from a bag:

   unique(Bag of TP b) -> Bag of TP r

The type of the result bag is the same as the type of elements of argument bag. unique() implements the keyword distinct in select statements. For example unique(bag(1,2,1,4)); returns the bag:

   1
   2
   4

Insert object between elements in a bag:

   inject(Bag b, Object x) -> Bag of Object r

For example inject(bag(1,2,3),0); returns the bag

   1
   0
   2
   0
   3

The query concatagg(inject(bag("ab",2,"cd"),", ")); returns the string "ab,2,cd".

Convert the tuples in a bag to vectors:

   tuples(Bag b) -> Bag of Vector