GROUP FILTER Clause

Filters data in a group before the aggregation operation is performed.

Syntax

GROUP FILTER expression

Components

expression

Any Boolean expression that does not use aggregate functions such as min() or max(). The expression may use columns from the source streams or windows.

Usage

The GROUP FILTER clause filters data before the aggregation operations are applied to the rows. The GROUP FILTER clause is used with the GROUP BY clause. If GROUP FILTER is used with the GROUP ORDER BY clause, GROUP ORDER BY is executed before GROUP FILTER.

The expression in the GROUP FILTER clause often uses filters based on functions such as rank(). These functions restrict rows that are used in the aggregation. The rank() function assigns a rank to each of the individual records in a group. rank() is meaningful only when used with the GROUP ORDER BY clause.

Example

The GROUP FILTER clause filters out the chosen rows, keeping only those with a rank of less than 10:

CREATE WINDOW Window1 SCHEMA (Symbol STRING, MaxPrice INTEGER)
PRIMARY KEY DEDUCED
KEEP ALL
AS 
SELECT T.Symbol, max(T.Price) MaxPrice
FROM Trades T
GROUP FILTER rank() < 10
GROUP BY T.Symbol
GROUP ORDER BY T.Volume DESC
HAVING max(T.Price) > 100 AND T.Symbol ='IBM';