Oracle® CEP CQL Language Reference 11g Release 1 (11.1.1) Part Number E12048-01 |
|
|
View PDF |
Aggregate functions perform a summary operation on all the values that a query returns.
For more information, see Section 1.1.9, "Functions".
Table 6-1 lists the built-in aggregate functions that Oracle CQL provides:
Table 6-1 Oracle CQL Built-in Aggregate Functions
Type | Function |
---|---|
Aggregate |
|
Aggregate (incremental computation) |
|
Extended aggregate |
Specify distinct
if you want Oracle CEP to return only one copy of each set of duplicate tuples selected. Duplicate tuples are those with matching values for each expression in the select list. For more information, see aggr_distinct_expr.
Oracle CEP does not support nested aggregations.
Note:
In stream input examples, lines beginning withh
(such as h 3800
) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.For more information, see:
Syntax
Purpose
AVG
returns average value of expr
.
This function takes as an argument any bigint
, float
, or int
datatype. The function returns a float
regardless of the numeric datatype of the argument.
Examples
Consider the query float_avg
in Example 6-1 and the data stream S3
in Example 6-2. Stream S3
has schema (c1 float)
. The query returns the relation in Example 6-3. Note that the AVG
function returns a result of NaN
if the average value is not a number. For more information, see Section 2.4.2, "Numeric Literals".
Example 6-1 AVG Function Query
<query id="float_avg"><![CDATA[ select avg(c1) from S3[range 5] ]]></query>
Syntax
Purpose
COUNT
returns the number of tuples returned by the query as an int
value.
If you specify expr
, then COUNT
returns the number of tuples where expr
is not null.
If you specify the asterisk (*
), then this function returns a count of all tuples, including duplicates and nulls. COUNT
never returns null.
Example
Consider the query q2
in Example 6-4 and the data stream S2
in Example 6-5. Stream S2
has schema (c1 integer, c2 integer)
. The query returns the relation in Example 6-6. For more information on range
windows, see "Range-Based Stream-to-Relation Window Operators".
Syntax
Purpose
FIRST
returns the value of the specified stream element the first time the specified pattern is matched.
The type of the specified stream element may be any of:
bigint
integer
byte
char
float
interval
timestamp
The return type of this function depends on the type of the specified stream element.
This function takes a single argument made up of the following period-separated values:
identifier1
: the name of a pattern as specified in a DEFINE
clause.
identifier2
: the name of a stream element as specified in a CREATE STREAM
statement.
See Also:
Examples
Consider the query q9
in Example 6-7 and the data stream S0
in Example 6-8. Stream S0
has schema (c1 integer, c2 float)
. This example defines pattern C
as C.c1 = 7
. It defines firstc
as first(C.c2)
. In other words, firstc
will equal the value of c2
the first time c1 = 7
. The query returns the relation in Example 6-9.
Example 6-7 FIRST Function Query
<query id="q9"><![CDATA[ select T.firstc, T.lastc, T.Ac1, T.Bc1, T.avgCc1, T.Dc1 from S0 MATCH_RECOGNIZE ( MEASURES first(C.c2) as firstc, last(C.c2) as lastc, avg(C.c1) as avgCc1, A.c1 as Ac1, B.c1 as Bc1, D.c1 as Dc1 PATTERN(A B C* D) DEFINE A as A.c1 = 30, B as B.c2 = 10.0, C as C.c1 = 7, D as D.c1 = 40 ) as T ]]></query>
Syntax
Purpose
LAST
returns the value of the specified stream element the last time the specified pattern is matched.
The type of the specified stream element may be any of:
bigint
integer
byte
char
float
interval
timestamp
The return type of this function depends on the type of the specified stream element.
This function takes a single argument made up of the following period-separated values:
identifier1
: the name of a pattern as specified in a DEFINE
clause.
identifier2
: the name of a stream element as specified in a CREATE STREAM
statement.
See Also:
Examples
Consider the query q9
in Example 6-10 and the data stream S0
in Example 6-11. Stream S1
has schema (c1 integer, c2 float)
. This example defines pattern C
as C.c1 = 7
. It defines lastc
as last(C.c2)
. In other words, lastc
will equal the value of c2
the last time c1 = 7
. The query returns the relation in Example 6-12.
Example 6-10 LAST Function Query
<query id="q9"><![CDATA[ select T.firstc, T.lastc, T.Ac1, T.Bc1, T.avgCc1, T.Dc1 from S0 MATCH_RECOGNIZE ( MEASURES first(C.c2) as firstc, last(C.c2) as lastc, avg(C.c1) as avgCc1, A.c1 as Ac1, B.c1 as Bc1, D.c1 as Dc1 PATTERN(A B C* D) DEFINE A as A.c1 = 30, B as B.c2 = 10.0, C as C.c1 = 7, D as D.c1 = 40 ) as T ]]></query>
Syntax
Purpose
MAX
returns maximum value of expr
. Its datatype depends on the datatype of the argument.
Examples
Consider the query test_max_timestamp
in Example 6-13 and the data stream S15
in Example 6-14. Stream S15
has schema (c1 int, c2 timestamp)
. The query returns the relation in Example 6-15.
Example 6-13 MAX Function Query
<query id="test_max_timestamp"><![CDATA[ select max(c2) from S15[range 2] ]]></query>
Example 6-14 MAX Function Stream Input
Timestamp Tuple 10 1,"08/07/2004 11:13:48" 2000 ,"08/07/2005 11:13:48" 3400 3,"08/07/2006 11:13:48" 4700 ,"08/07/2007 11:13:48" h 8000 h 200000000
Example 6-15 MAX Function Relation Output
Timestamp Tuple Kind Tuple 0: + 10: - 10: + 08/07/2004 11:13:48 2000: - 08/07/2004 11:13:48 2000: + 08/07/2005 11:13:48 2010: - 08/07/2005 11:13:48 2010: + 08/07/2005 11:13:48 3400: - 08/07/2005 11:13:48 3400: + 08/07/2006 11:13:48 4000: - 08/07/2006 11:13:48 4000: + 08/07/2006 11:13:48 4700: - 08/07/2006 11:13:48 4700: + 08/07/2007 11:13:48 5400: - 08/07/2007 11:13:48 5400: + 08/07/2007 11:13:48 6700: - 08/07/2007 11:13:48 6700: +
Syntax
Purpose
MIN
returns minimum value of expr
. Its datatype depends on the datatype of its argument.
Examples
Consider the query test_min_timestamp
in Example 6-16 and the data stream S15
in Example 6-17. Stream S15
has schema (c1 int, c2 timestamp)
. The query returns the relation in Example 6-18.
Example 6-16 MIN Function Query
<query id="test_min_timestamp"><![CDATA[ select min(c2) from S15[range 2] ]]></query>
Example 6-17 MIN Function Stream Input
Timestamp Tuple 10 1,"08/07/2004 11:13:48" 2000 ,"08/07/2005 11:13:48" 3400 3,"08/07/2006 11:13:48" 4700 ,"08/07/2007 11:13:48" h 8000 h 200000000
Example 6-18 MIN Function Relation Output
Timestamp Tuple Kind Tuple 0: + 10: - 10: + 08/07/2004 11:13:48 2000: - 08/07/2004 11:13:48 2000: + 08/07/2004 11:13:48 2010: - 08/07/2004 11:13:48 2010: + 08/07/2005 11:13:48 3400: - 08/07/2005 11:13:48 3400: + 08/07/2005 11:13:48 4000: - 08/07/2005 11:13:48 4000: + 08/07/2006 11:13:48 4700: - 08/07/2006 11:13:48 4700: + 08/07/2006 11:13:48 5400: - 08/07/2006 11:13:48 5400: + 08/07/2007 11:13:48 6700: - 08/07/2007 11:13:48 6700: +
Syntax
Purpose
SUM
returns the sum of values of expr
. This function takes as an argument any bigint
, float
, or integer
expression. The function returns the same datatype as the numeric datatype of the argument.
Examples
Consider the query q3
in Example 6-19 and the data stream S1
in Example 6-20. Stream S1
has schema (c1 integer, c2 bigint)
. The query returns the relation in Example 6-21. For more information on range
, see "Range-Based Stream-to-Relation Window Operators".
Syntax
Purpose
XMLAGG
returns a collection of XML fragments as an aggregated XML document. Arguments that return null are dropped from the result.
You can control the order of fragments using an order_by_clause
. For more information, see Section 14.2.6, "Sorting Query Results".
Examples
This section describes the following XMLAGG
examples:
XMLAGG and XMLELEMENT
Consider the query tkdata67_q1
in Example 6-22 and the input relation in Example 6-23. Stream tkdata67_S0
has schema (c1 integer, c2 float)
. This query uses XMLELEMENT
to create XML fragments from stream elements and then uses XMLAGG
to aggregate these XML fragments into an XML document. The query returns the relation in Example 6-24.
For more information about XMLELEMENT, see "xmlelement_expr".
Example 6-22 XMLAGG Query
<query id="tkdata67_q1"><![CDATA[ select c1, xmlagg(xmlelement("c2",c2)) from tkdata67_S0[rows 10] group by c1 ]]></query>
Example 6-23 XMLAGG Relation Input
Timestamp Tuple 1000 15, 0.1 1000 20, 0.14 1000 15, 0.2 4000 20, 0.3 10000 15, 0.04 h 12000
Example 6-24 XMLAGG Relation Output
Timestamp Tuple Kind Tuple 1000: + 15,<c2>0.1</c2> <c2>0.2</c2> 1000: + 20,<c2>0.14</c2> 4000: - 20,<c2>0.14</c2> 4000: + 20,<c2>0.14</c2> <c2>0.3</c2> 10000: - 15,<c2>0.1</c2> <c2>0.2</c2> 10000: + 15,<c2>0.1</c2> <c2>0.2</c2> <c2>0.04</c2>
XMLAGG and ORDER BY
Consider the query tkxmlAgg_q5
in Example 6-25 and the input relation in Example 6-26. Stream tkxmlAgg_S1
has schema (c1 int, c2 xmltype)
. These query selects xmltype
stream elements and uses XMLAGG to aggregate them into an XML document. This query uses an ORDER BY
clause to order XML fragments. The query returns the relation in Example 6-27.
Example 6-25 XMLAGG and ORDER BY Query
<query id="tkxmlAgg_q5"><![CDATA[ select xmlagg(c2), xmlagg(c2 order by c1) from tkxmlAgg_S1[range 2] ]]></query>
Example 6-26 XMLAGG and ORDER BY Relation Input
Timestamp Tuple 1000 1, "<a>hello</a>" 2000 10, "<b>hello1</b>" 3000 15, "<PDRecord><PDName>hello</PDName></PDRecord>" 4000 5, "<PDRecord><PDName>hello</PDName><PDName>hello1</PDName></PDRecord>" 5000 51, "<PDRecord><PDId>6</PDId><PDName>hello1</PDName></PDRecord>" 6000 15, "<PDRecord><PDId>46</PDId><PDName>hello2</PDName></PDRecord>" 7000 55, "<PDRecord><PDId>6</PDId><PDName>hello2</PDName><PDName>hello3</PDName></PDRecord>"
Example 6-27 XMLAGG and ORDER BY Relation Output
Timestamp Tuple Kind Tuple 0: + 1000: - 1000: + <a>hello</a> ,<a>hello</a> 2000: - <a>hello</a> ,<a>hello</a> 2000: + <a>hello</a> <b>hello1</b> ,<a>hello</a> <b>hello1</b> 3000: - <a>hello</a> <b>hello1</b> ,<a>hello</a> <b>hello1</b> 3000: + <b>hello1</b> <PDRecord> <PDName>hello</PDName> </PDRecord> ,<b>hello1</b> <PDRecord> <PDName>hello</PDName> </PDRecord> 4000: - <b>hello1</b> <PDRecord> <PDName>hello</PDName> </PDRecord> ,<b>hello1</b> <PDRecord> <PDName>hello</PDName> </PDRecord> 4000: + <PDRecord> <PDName>hello</PDName> </PDRecord> <PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> ,<PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDName>hello</PDName> </PDRecord> 5000: - <PDRecord> <PDName>hello</PDName> </PDRecord> <PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> ,<PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDName>hello</PDName> </PDRecord> 5000: + <PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> ,<PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> 6000: - <PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> ,<PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> 6000: + <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>46</PDId> <PDName>hello2</PDName> </PDRecord> ,<PDRecord> <PDId>46</PDId> <PDName>hello2</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> 7000: - <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>46</PDId> <PDName>hello2</PDName> </PDRecord> ,<PDRecord> <PDId>46</PDId> <PDName>hello2</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord>