Aggregator Operators

Aggregator operators enable you to aggregate data and to perform additional operations on that data to obtain output.

The following table lists the input and output for each operator.

Relation Aggregator

The Relation Aggregator operator takes as input the output of a relation, treats that output as if it were a database table, and performs a SQL SELECT on that table. The Relation Aggregator operator issues output in the form of a relation.

Use the Relation Aggregator operator when you want to perform SQL operations on a relation.

To Create a Relation Aggregator Operator

1. Drag a Relation Aggregator operator from the Palette to the Design view.

2. Double-click the Relation Aggregator operator.

   The property editor opens with the default name of the Relation Aggregator operator and the output schema name populated. The Property Window displays the schema of the data that is input to the Relation Aggregator operator.

3. In the Select field, specify attributes from which to select.

   Any attribute that you specify must appear in the group-by clause. Attributes that you select as expression entries must be in the form of an attribute name, a literal, or an aggregate function supported by the database you use. Examples include COUNT, MAX, MIN, and AVG.

4. In the Expression field, provide an SQL expression to further delimit your Select statement.

   To save some typing, you can drag input attribute field names from the Inputs area into the Expression field.

5. Optionally specify a Where statement in the Where field to provide a search condition, which cannot have a subquery.

6. In the group by field, specify a comma-separated list of attribute names, indicating how you want to group the attributes in the relation that is output from this operator.

7. Click OK.

Time Based Aggregator

The Time Based Aggregator operator performs statistical analysis within a specified amount of time that you provide as a size, which is period of time over which you can perform a calculation, or the time slot. Increment specifies the frequency of the calculation; that is, how often you calculate the statistical analysis.

Assume that you want to calculate a stock price's 20–day moving average. You can supply a size of 20 in the property editor, and an increment that specifies how often you want to perform that calculation (for example, once a day).

Statistics that you can compute via SQL statements in the property editor of the Time Based Aggregator operator include:

• Sum

• Average

• Minimum

• Maximum

• COUNT

Use the Time Based Aggregator operator when you want to perform real-time statistical analysis. You can do simple or complex SQL manipulation within the time frame that you specify, by using the Select, From and Where clauses, as indicated in the property editor.

For example, given a stream of transactions of a stock, you can compute the new stream that holds the hourly minimum average and the maximum of the stock price.

To Create a Time Based Aggregator Operator

1. Drag a Time Based Aggregator operator from the Palette to the Design view.

2. Double-click the Time Based Aggregator operator.

   The property editor opens.

3. In the Start field, enter the time to start calculating the tasks that are to be performed by the Time Based Aggregator within the process.

4. In the Increment field, enter the time increment for the Time Based Aggregator to perform the analysis.

5. In the Size field, enter the time range for the Time Based Aggregator to perform the analysis.

6. In the Expression box, enter the expression for the SQL SELECT statement to specify the input attribute upon which the SELECT is performed. Add attribute names, data types. and sizes, as applicable.

7. In the From box, define the input on which to perform the selection.

8. In the Where clause box, provide additional filtering on records.

   For example:

   WHERE price > 30.00 AND stockDate < '2006-01-01' ;

9. In the Group by box, group a result into subsets that have matching values for one or more columns of the database by specifying a comma-separated list of qualified attribute names.

10. Click OK.

Tuple Based Aggregator

The Tuple Based Aggregator operator performs statistical analysis for a specified number of records (also called tuples) that you provide as a size, and also for an increment that indicates how often you want the operation performed.

Statistics that you can compute via SQL statements in the property editor of the Tuple Based Aggregator operator include:

• Sum

• Average

• Minimum

• Maximum

• COUNT

Use the Tuple Based Aggregator operator when you want to perform statistical analysis on a specified number of tuples.

For example, provided a stream of stock transactions, the Tuple Based Aggregator operator computes a new stream that holds the minimum, average, and maximum of the stock price of every 10 transactions, in which the size is 10.

To Create a Tuple Based Aggregator Operator

1. Drag a Tuple Based Aggregator operator from the Palette to the Design view.

2. Double-click the Tuple Based Aggregator operator.

   The property editor opens.

3. In the Start field, enter the record in which you want the operator calculations to begin.

4. In the Increment field, enter how often you want the analysis performed.

5. In the Expression box, enter the expression for the SQL SELECT statement that is used to specify the input attribute upon which the SELECT is performed. You can add, delete, or move attributes.

6. In the From box, define the input on which to perform the selection.

7. In the Where clause box, provide additional filtering on records.

   For example:

   WHERE price > 30.00 AND stockDate < '2006-01-01' ;

8. In the Group by box, group a result into subsets that have matching values for one or more columns of the database by specifying a comma-separated list of qualified attribute names.

9. Click OK.