Creating and Modifying Aggregate Queries

Produce a simple query that combines data, similar to the CCL GROUP BY, GROUP FILTER, and GROUP ORDER clauses.

  1. In the Visual editor Palette, in Streams and Windows, select Aggregate (aggregate_template.gif).
  2. Select a location in the diagram and click to add the shape.
  3. Connect the Aggregate shape to an input.
    The aggregate border changes from red to black, indicating that it is valid, now that it has input.
  4. Add columns:
    1. Click Copy Columns from Input (copycolumn.gif) in the shape toolbar to select the columns to copy into the schema for the Aggregate window.
    2. Add additional columns by clicking Add Column Expression compute_template.gif in the shape toolbar.
    3. Edit a column expression by double-clicking to open the inline editor, or by selecting the expression and pressing Ctrl+F2 to open it in the pop-up editor.
    Tip: When entering column names and their datatypes, use Tab to easily move between cells in the table.
  5. Click Add GroupBy Clause (group.gif) in the shape toolbar to edit the grouping of columns in the aggregate object.
    Note: The Aggregate shape must have exactly one GROUP BY expression.
  6. (Optional) Click Set Keep Policy (retention_window.gif) to create a retention window.
    The default policy is to keep all rows of incoming data. You can also choose to keep only the last row, a specific number of rows, or keep the rows for a specific time. This defines the KEEP clause. You can also go further, and retain the rows defined by the KEEP clause to span retention across multiple specified columns. This spanning of retention across columns is done by listing column names in the PER clause.
  7. (Optional) Use the Toggle window.gif option to designate the aggregate object as LOCAL or OUTPUT. By default, aggregates are Output.
Related concepts
Simple Queries
Specifying a Retention Policy
Related tasks
Creating and Modifying Filter Queries
Creating and Modifying Compute Queries
Creating and Modifying Join Queries
Creating and Modifying Union Queries
Creating and Modifying Pattern Queries