company logo

Aggregation example

The example used in aggregation schema documentation is based on a project management system, that stores time sheets per project and person. The grouping level is projectId( STRING ) , personId( STRING ) , iDate( IDate ) . IDate is a complex grouping dimension that has got three attributes ( year , month and day ). Complex dimension attributes provide aggregation levels for each subordinated dimension attribute, i.e. aggregation levels provided are year , year , month and year , month , day .

The aggregated variable is the time spent on a certain project or by a certain person or in a certain time interval.

STRUCT IDate {

  INT(2)  year;

  INT(2)  month;

  INT(2)  day;    };

  

SELECT ( INT(10,2) time = sum(duration) ) HIERARCHY(SIMPLE)

  FROM (TimeSheet)

  GROUP BY ( STRING(10) personId = person(0).id,

             STRING(10) projectId = task(0).GetProject().id,

             IDate      iDate = GetIDate(start) );

Notes:

GetProject() provides the project a task in a hierarchical task structure belongs too. This may also be the project itself, which also is a task.

GetIDate() is a function that creates an IDate structure from a database date value.