Skip Headers
Oracle® Fusion Middleware CQL Language Reference for Oracle Complex Event Processing
11g Release 1 (11.1.1.6.3)

Part Number E12048-10
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Operators

This chapter provides a reference for operators in Oracle Continuous Query Language (Oracle CQL). An operator manipulates data items and returns a result. Syntactically, an operator appears before or after an operand or between two operands.

4.1 Introduction to Operators

Operators manipulate individual data items called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*).

Oracle CQL provides the following operators:

4.1.1 What You May Need to Know About Unary and Binary Operators

The two general classes of operators are:

  • unary: A unary operator operates on only one operand. A unary operator typically appears with its operand in this format:

    operator operand
    
  • binary: A binary operator operates on two operands. A binary operator appears with its operands in this format:

    operand1 operator operand2
    

Other operators with special formats accept more than two operands. If an operator is given a null operand, then the result is always null. The only operator that does not follow this rule is concatenation (||).

4.1.2 What You May Need to Know About Operator Precedence

Precedence is the order in which Oracle CEP evaluates different operators in the same expression. When evaluating an expression containing multiple operators, Oracle CEP evaluates operators with higher precedence before evaluating those with lower precedence. Oracle CEP evaluates operators with equal precedence from left to right within an expression.

Table 4-1 lists the levels of precedence among Oracle CQL operators from high to low. Operators listed on the same line have the same precedence.

Table 4-1 Oracle CQL Operator Precedence

Operator Operation

+, - (as unary operators)

Identity, negation

*, /

Multiplication, division

+, - (as binary operators), ||

Addition, subtraction, concatenation

Oracle CQL conditions are evaluated after Oracle CQL operators

See Chapter 6, "Conditions"


Precedence Example In the following expression, multiplication has a higher precedence than addition, so Oracle first multiplies 2 by 3 and then adds the result to 1.

1+2*3 

You can use parentheses in an expression to override operator precedence. Oracle evaluates expressions inside parentheses before evaluating those outside.


Arithmetic Operators

Table 4-2 lists arithmetic operators that Oracle CEP supports. You can use an arithmetic operator with one or two arguments to negate, add, subtract, multiply, and divide numeric values. Some of these operators are also used in datetime and interval arithmetic. The arguments to the operator must resolve to numeric datatypes or to any datatype that can be implicitly converted to a numeric datatype.

In certain cases, Oracle CEP converts the arguments to the datatype as required by the operation. For example, when an integer and a float are added, the integer argument is converted to a float. The datatype of the resulting expression is a float. For more information, see "Implicit Datatype Conversion".

Table 4-2 Arithmetic Operators

Operator Purpose Example

+ -

When these denote a positive or negative expression, they are unary operators.

<query id="q1"><![CDATA[ 
    select * from orderitemsstream
    where quantity = -1
]]></query>

+ -

When they add or subtract, they are binary operators.

<query id="q1"><![CDATA[ 
    select hire_date 
    from employees
    where sysdate - hire_date
  > 365
]]></query>

* /

Multiply, divide. These are binary operators.

<query id="q1"><![CDATA[ 
    select hire_date 
    from employees
    where bonus > salary * 1.1
]]></query>

Do not use two consecutive minus signs (--) in arithmetic expressions to indicate double negation or the subtraction of a negative value. You should separate consecutive minus signs with a space or parentheses.

Oracle CEP supports arithmetic operations using numeric literals and using datetime and interval literals.

For more information, see:


Concatenation Operator

The concatenation operator manipulates character strings. Table 4-3 describes the concatenation operator.

Table 4-3 Concatenation Operator

Operator Purpose Example

||

Concatenates character strings.

<query id="q263"><![CDATA[ 
    select length(c2 || c2) + 1 from S10 where length(c2) = 2
]]></query>

The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, then the result has datatype CHAR and is limited to 2000 characters. Trailing blanks in character strings are preserved by concatenation, regardless of the datatypes of the string.

Although Oracle CEP treats zero-length character strings as nulls, concatenating a zero-length character string with another operand always results in the other operand, so null can result only from the concatenation of two null strings. However, this may not continue to be true in future versions of Oracle CEP. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length string.

Example 4-1 shows how to use the concatenation operator to append the String "xyz" to the value of c2 in a select statement.

Example 4-1 Concatenation Operator (||)

<query id="q264"><![CDATA[ 
    select c2 || "xyz" from S10
]]></query>

Alternation Operator

The alternation operator allows you to refine the sense of a PATTERN clause. Table 4-4 describes the concatenation operator.

Table 4-4 Alternation Operator

Operator Purpose Example

|

Changes the sense of a PATTERN clause to mean one or the other correlation variable rather than one followed by the other correlation variable.

<query id="q263"><![CDATA[ 
select T.p1, T.p2, T.p3 from S MATCH_RECOGNIZE(
    MEASURES
        A.ELEMENT_TIME as p1,
        B.ELEMENT_TIME as p2
        B.c2 as p3
    PATTERN (A+ | B+)
    DEFINE
        A as A.c1 = 10,
        B as B.c1 = 20
) as T
]]></query>

The alternation operator is applicable only within a PATTERN clause.

Example 4-2 shows how to use the alternation operator to change the sense of the PATTERN clause to mean "A one or more times followed by either B one or more times or C one or more times, whichever comes first".

Example 4-2 Alternation Operator (|)

<query id="q264"><![CDATA[ 
select T.p1, T.p2, T.p3 from S MATCH_RECOGNIZE(
    MEASURES
        A.ELEMENT_TIME as p1,
        B.ELEMENT_TIME as p2
        B.c2 as p3
    PATTERN (A+ (B+ | C+))
    DEFINE
        A as A.c1 = 10,
        B as B.c1 = 20
        C as C.c1 = 30
) as T
]]></query>

For more information, see Section 19.3.2, "Grouping and Alternation in the PATTERN Clause".


Range-Based Stream-to-Relation Window Operators

Oracle CQL supports the following range-based stream-to-relation window operators:

window_type_range::=

Surrounding text describes window_type_range.png.

(time_spec::=)

For more information, see:


S[now]

This time-based range window outputs an instantaneous relation. So at time t the output of this now window is all the tuples that arrive at that instant t. The smallest granularity of time in Oracle CEP is nanoseconds and hence all these tuples expire 1 nanosecond later.

For an example, see "S [now] Example".

Examples

S [now] Example

Consider the query q1 in Example 4-3 and the data stream S in Example 4-4. Timestamps are shown in nanoseconds (1 sec = 10^9 nanoseconds). Example 4-5 shows the relation that the query returns at time 5000 ms. At time 5002 ms, the query would return an empty relation.

Example 4-3 S [now] Query

<query id="q1"><![CDATA[ 
    SELECT * FROM S [now]
]]></query>

Example 4-4 S [now] Stream Input

Timestamp         Tuple
  1000000000      10,0.1
  1002000000      15,0.14
  5000000000      33,4.4
  5000000000      23,56.33
 10000000000      34,4.4
200000000000      20,0.2
209000000000      45,23.44
400000000000      30,0.3
h 800000000000

Example 4-5 S [now] Relation Output at Time 5000000000 ns

Timestamp   Tuple Kind  Tuple
5000000000  +           33,4.4
5000000000  +           23,56.33
5000000001  -           33,4.4
5000000001  -           23,56.33

S[range T]

This time-based range window defines its output relation over time by sliding an interval of size T time units capturing the latest portion of an ordered stream.

For an example, see "S [range T] Example".

Examples

S [range T] Example

Consider the query q1 in Example 4-6. Given the data stream S in Example 4-7, the query returns the relation in Example 4-8. By default, the range time unit is second (see time_spec::=) so S[range 1] is equivalent to S[range 1 second]. Timestamps are shown in milliseconds (1 s = 1000 ms). As many elements as there are in the first 1000 ms interval enter the window, namely tuple (10,0.1). At time 1002 ms, tuple (15,0.14) enters the window. At time 2000 ms, any tuples that have been in the window longer than the range interval are subject to deletion from the relation, namely tuple (10,0.1). Tuple (15,0.14) is still in the relation at this time. At time 2002 ms, tuple (15,0.14) is subject to deletion because by that time, it has been in the window longer than 1000 ms.

Note:

In stream input examples, lines beginning with h (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.

Example 4-6 S [range T] Query

<query id="q1"><![CDATA[ 
    SELECT * FROM S [range 1]
]]></query>

Example 4-7 S [range T] Stream Input

Timestamp   Tuple
     1000   10,0.1
     1002   15,0.14
   200000   20,0.2
   400000   30,0.3
h 800000
100000000   40,4.04
h 200000000

Example 4-8 S [range T] Relation Output

Timestamp   Tuple Kind  Tuple
     1000:  +           10,0.1
     1002:  +           15,0.14
     2000:  -           10,0.1
     2002:  -           15,0.14
   200000:  +           20,0.2
   201000:  -           20,0.2
   400000:  +           30,0.3
   401000:  -           30,0.3
100000000:  +           40,4.04
100001000:  -           40,4.04

S[range T1 slide T2]

This time-based range window allows you to specify the time duration in the past up to which you want to retain the tuples (range) and also how frequently you want to see the output of the tuples (slide). So if two tuples arrive between the time period n*T2 and (n+1)*T2, both of them will be visible (enter the window) only at (n+1)*T2 and will expire from the window at (n+1)*T2+T1.

For an example, see "S [range T1 slide T2] Example".

Examples

S [range T1 slide T2] Example

Consider the query q1 in Example 4-9. Given the data stream S in Example 4-10, the query returns the relation in Example 4-11. By default, the range time unit is second (see time_spec::=) so S[range 10 slide 5] is equivalent to S[range 10 seconds slide 5 seconds]. Timestamps are shown in milliseconds (1 s = 1000 ms). Tuples arriving at 1000, 1002, and 5000 all enter the window at time 5000 since the slide value is 5 sec and that means the user is interested in looking at the output after every 5 sec. Since these tuples enter at 5 sec=5000 ms, they are expired at 15000 ms as the range value is 10 sec = 10000 ms.

Example 4-9 S [range T1 slide T2] Query

<query id="q1"><![CDATA[ 
    SELECT * FROM S [range 10 slide 5]
]]></query>

Example 4-10 S [range T1 slide T2] Stream Input

Timestamp   Tuple
  1000      10,0.1
  1002      15,0.14
  5000      33,4.4
  8000      23,56.33
 10000     34,4.4
200000      20,0.2
209000      45,23.44
400000      30,0.3
h 800000

Example 4-11 S [range T1 slide T2] Relation Output

Timestamp   Tuple Kind  Tuple
  5000:     +           10,0.1
  5000:     +           15,0.14
  5000:     +           33,4.4
 10000:     +           23,56.33
 10000:     +           34,4.4
 15000:     -           10,0.1
 15000:     -           15,0.14
 15000:     -           33,4.4
 20000:     -           23,56.33
 20000:     -           34,44.4
200000:     +           20,0.2
210000:     -           20,0.2
210000:     +           45,23.44
220000:     -           45,23.44
400000:     +           30,0.3
410000:     -           30,0.3

S[range unbounded]

This time-based range window defines its output relation such that, when T = infinity, the relation at time t consists of tuples obtained from all elements of S up to t. Elements remain in the window indefinitely.

For an example, see "S [range unbounded] Example".

Examples

S [range unbounded] Example

Consider the query q1 in Example 4-12 and the data stream S in Example 4-13. Timestamps are shown in milliseconds (1 s = 1000 ms). Elements are inserted into the relation as they arrive. No elements are subject to deletion. Example 4-14 shows the relation that the query returns at time 5000 ms and Example 4-15 shows the relation that the query returns at time 205000 ms.

Example 4-12 S [range unbounded] Query

<query id="q1"><![CDATA[ 
    SELECT * FROM S [range unbounded]
]]></query>

Example 4-13 S [range unbounded] Stream Input

Timestamp   Tuple
  1000      10,0.1
  1002      15,0.14
  5000      33,4.4
  8000      23,56.33
 10000      34,4.4
200000      20,0.2
209000      45,23.44
400000      30,0.3
h 800000

Example 4-14 S [range unbounded] Relation Output at Time 5000 ms

Timestamp   Tuple Kind  Tuple
  1000:     +           10,0.1
  1002:     +           15,0.14
  5000:     +           33,4.4

Example 4-15 S [range unbounded] Relation Output at Time 205000 ms

Timestamp   Tuple Kind  Tuple
  1000:     +           10,0.1
  1002:     +           15,0.14
  5000:     +           33,4.4
  8000:     +           23,56.33
 10000:     +           34,4.4
200000:     +           20,0.2

S[range C on E]

This constant value-based range window defines its output relation by capturing the latest portion of a stream that is ordered on the identifier E made up of tuples in which the values of stream element E differ by less than C. A tuple is subject to deletion when the difference between its stream element E value and that of any tuple in the relation is greater than or equal to C.

For examples, see:

Examples

S [range C on E] Example: Constant Value

Consider the query tkdata56_q0 in Example 4-16 and the data stream tkdata56_S0 in Example 4-17. Stream tkdata56_S0 has schema (c1 integer, c2 float). Example 4-18 shows the relation that the query returns. In this example, at time 200000, the output relation contains the following tuples: (5,0.1), (8,0.14), (10,0.2). The difference between the c1 value of each of these tuples is less than 10. At time 250000, when tuple (15,0.2) is added, tuple (5,0.1) is subject to deletion because the difference 15 - 5 = 10, which not less than 10. Tuple (8,0.14) remains because 15 - 8 = 7, which is less than 10. Likewise, tuple (10,0.2) remains because 15 - 10 = 5, which is less than 10. At time 300000, when tuple (18,0.22) is added, tuple (8,0.14) is subject to deletion because 18 - 8 = 10, which is not less than 10.

Example 4-16 S [range C on E] Constant Value: Query

<query id="tkdata56_q0"><![CDATA[ 
    select * from tkdata56_S0 [range 10 on c1]
]]></query>

Example 4-17 S [range C on E] Constant Value: Stream Input

Timestamp   Tuple
 100000     5, 0.1
 150000     8, 0.14
 200000     10, 0.2
 250000     15, 0.2
 300000     18, 0.22
 350000     20, 0.25
 400000     30, 0.3
 600000     40, 0.4
 650000     45, 0.5
 700000     50, 0.6
1000000     58, 4.04

Example 4-18 S [range C on E] Constant Value: Relation Output

Timestamp   Tuple Kind  Tuple
 100000:    +           5,0.1
 150000:    +           8,0.14
 200000:    +           10,0.2
 250000:    -           5,0.1
 250000:    +           15,0.2
 300000:    -           8,0.14
 300000:    +           18,0.22
 350000:    -           10,0.2
 350000:    +           20,0.25
 400000:    -           15,0.2
 400000:    -           18,0.22
 400000:    -           20,0.25
 400000:    +           30,0.3
 600000:    -           30,0.3
 600000:    +           40,0.4
 650000:    +           45,0.5
 700000:    -           40,0.4
 700000:    +           50,0.6
1000000:    -           45,0.5
1000000:    +           58,4.04

S [range C on E] Example: INTERVAL and TIMESTAMP

Similarly, you can use the S[range C on ID] window with INTERVAL and TIMESTAMP. Consider the query tkdata56_q2 in Example 4-19 and the data stream tkdata56_S1 in Example 4-20. Stream tkdata56_S1 has schema (c1 timestamp, c2 double). Example 4-21 shows the relation that the query returns.

Example 4-19 S [range C on E] INTERVAL Value: Query

<query id="tkdata56_q2"><![CDATA[ 
    select * from tkdata56_S1 [range INTERVAL "530 0:0:0.0" DAY TO SECOND on c1] 
]]></query>

Example 4-20 S [range C on E] INTERVAL Value: Stream Input

Timestamp   Tuple
  10        "08/07/2004 11:13:48", 11.13
2000        "08/07/2005 12:13:48", 12.15
3400        "08/07/2006 10:15:58", 22.25
4700        "08/07/2007 10:10:08", 32.35

Example 4-21 S [range C on E] INTERVAL Value: Relation Output

Timestamp   Tuple Kind  Tuple
  10:       +           08/07/2004 11:13:48,11.13
2000:       +           08/07/2005 12:13:48,12.15
3400:       -           08/07/2004 11:13:48,11.13
3400:       +           08/07/2006 10:15:58,22.25
4700:       -           08/07/2005 12:13:48,12.15
4700:       +           08/07/2007 10:10:08,32.35

Tuple-Based Stream-to-Relation Window Operators

Oracle CQL supports the following tuple-based stream-to-relation window operators:

window_type_tuple::=

Surrounding text describes window_type_tuple.png.

For more information, see:


S [rows N]

A tuple-based window defines its output relation over time by sliding a window of the last N tuples of an ordered stream.

For the output relation R of S [rows N], the relation at time t consists of the N tuples of S with the largest timestamps <= t (or all tuples if the length of S up to t is <= N).

If more than one tuple has the same timestamp, Oracle CEP chooses one tuple in a non-deterministic way to ensure N tuples are returned. For this reason, tuple-based windows may not be appropriate for streams in which timestamps are not unique.

By default, the slide is 1.

For examples, see "S [rows N] Example".

Examples

S [rows N] Example

Consider the query q1 in Example 4-22 and the data stream S in Example 4-23. Timestamps are shown in milliseconds (1 s = 1000 ms). Elements are inserted into and deleted from the relation as in the case of S [Range 1] (see "S [range T] Example").

Example 4-24 shows the relation that the query returns at time 1002 ms. Since the length of S at this point is less than or equal to the rows value (3), the query returns all the tuples of S inserted by that time, namely tuples (10,0.1) and (15,0.14).

Example 4-25 shows the relation that the query returns at time 1006 ms. Since the length of S at this point is greater than the rows value (3), the query returns the 3 tuples of S with the largest timestamps less than or equal to 1006 ms, namely tuples (15,0.14), (33,4.4), and (23,56.33).

Example 4-26 shows the relation that the query returns at time 2000 ms. At this time, the query returns the 3 tuples of S with the largest timestamps less than or equal to 2000 ms, namely tuples (45,23.44), (30,0.3), and (17,1.3).

Example 4-22 S [rows N] Query

<query id="q1"><![CDATA[ 
    SELECT * FROM S [rows 3] 
]]></query>

Example 4-23 S [rows N] Stream Input

Timestamp    Tuple
1000         10,0.1
1002         15,0.14
1004         33,4.4
1006         23,56.33
1008         34,4.4
1010         20,0.2
1012         45,23.44
1014         30,0.3
2000         17,1.3

Example 4-24 S [rows N] Relation Output at Time 1003 ms

Timestamp   Tuple Kind  Tuple
  1000:         +       10,0.1
  1002:         +       15,0.14

Example 4-25 S [rows N] Relation Output at Time 1007 ms

Timestamp   Tuple Kind  Tuple
  1000:         +       10,0.1
  1002:         +       15,0.14
  1004:         +       33,4.4
  1006:         -       10,0.1
  1006:         +       23,56.33

Example 4-26 S [rows N] Relation Output at Time 2001 ms

Timestamp   Tuple Kind  Tuple
  1000          +       10,0.1
  1002          +       15,0.14
  1004          +       33,4.4
  1006          -       10,0.1
  1006          +       23,56.33
  1008          -       15,0.14
  1008          +       34,4.4
  1008          -       33,4.4
  1010          +       20,0.2
  1010          -       23,56.33
  1012          +       45,23.44
  1012          -       34,4.4
  1014          +       30,0.3
  2000          -       20,0.2
  2000          +       17,1.3

S [rows N1 slide N2]

A tuple-based window that defines its output relation over time by sliding a window of the last N1 tuples of an ordered stream.

For the output relation R of S [rows N1 slide N2], the relation at time t consists of the N1 tuples of S with the largest timestamps <= t (or all tuples if the length of S up to t is <= N).

If more than one tuple has the same timestamp, Oracle CEP chooses one tuple in a non-deterministic way to ensure N tuples are returned. For this reason, tuple-based windows may not be appropriate for streams in which timestamps are not unique.

You can configure the slide N2 as an integer number of stream elements. Oracle CEP delays adding stream elements to the relation until it receives N2 number of elements.

For examples, see "S [rows N] Example".

Examples

S [rows N1 slide N2] Example

Consider the query tkdata55_q0 in Example 4-27 and the data stream tkdata55_S55 in Example 4-28. Stream tkdata55_S55 has schema (c1 integer, c2 float). The output relation is shown in Example 4-29.

As Example 4-29 shows, at time 100000, the output relation is empty because only one tuple (20,0.1) has arrived on the stream. By time 150000, the number of tuples that the slide value specifies (2) have arrived: at that time, the output relation contains tuples (20,0.1) and (15,0.14). By time 250000, another slide number of tuples have arrived and the output relation contains tuples (20,0.1), (15,0.14), (5,0.2), and (8,0.2). By time 350000, another slide number of tuples have arrived. At this time, the oldest tuple (20,0.1) is subject to deletion to meet the constraint that the rows value imposes: namely, that the output relation contain no more than 5 elements. At this time, the output relation contains tuples (15,0.14), (5,0.2), (8,0.2), (10,0.22), and (20,0.25). At time 600000, another slide number of tuples have arrived. At this time, the oldest tuples (15,0.14) and (5,0.2) are subject to deletion to observe the rows value constraint. At this time, the output relation contains tuples (8,0.2), (10,0.22), (20,0.25), (30,0.3), and (40,0.4).

Example 4-27 S [rows N1 slide N2] Query

<query id="tkdata55_q0"><![CDATA[ 
    select * from tkdata55_S55 [rows 5 slide 2 ]
]]></query>

Example 4-28 S [rows N1 slide N2] Stream Input

Timestamp   Tuple
   100000   20, 0.1
   150000   15, 0.14
   200000    5, 0.2
   250000    8, 0.2
   300000   10, 0.22
   350000   20, 0.25
   400000   30, 0.3
   600000   40, 0.4
   650000   45, 0.5
   700000   50, 0.6
100000000    8, 4.04

Example 4-29 S [rows N1 slide N2] Relation Output

Timestamp   Tuple Kind  Tuple
150000:     +           20,0.1
150000:     +           15,0.14
250000:     +            5,0.2
250000:     +            8,0.2
350000:     -           20,0.1
350000:     +           10,0.22
350000:     +           20,0.25
600000:     -           15,0.14
600000:     -            5,0.2
600000:     +           30,0.3
600000:     +           40,0.4
700000:     -            8,0.2
700000:     -           10,0.22
700000:     +           45,0.5
700000:     +           50,0.6

Partitioned Stream-to-Relation Window Operators

Oracle CQL supports the following partitioned stream-to-relation window operators:

window_type_partition::=

Surrounding text describes window_type_partition.png.

(time_spec::=, non_mt_attr_list::=)

For more information, see:


S [partition by A1,..., Ak rows N]

This partitioned sliding window on a stream S takes a positive integer number of tuples N and a subset {A1,... Ak} of the stream's attributes as parameters and:

For an example, see "S[partition by A1, ..., Ak rows N] Example".

Examples

S[partition by A1, ..., Ak rows N] Example

Consider the query qPart_row2 in Example 4-30 and the data stream SP1 in Example 4-31. Stream SP1 has schema (c1 integer, name char(10)). The query returns the relation in Example 4-32. By default, the range (and slide) is 1 second. Timestamps are shown in milliseconds (1 s = 1000 ms).

Note:

In stream input examples, lines beginning with h (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.

Example 4-30 S[partition by A1, ..., Ak rows N] Query

<query id="qPart_row2"><![CDATA[ 
    select * from SP1 [partition by c1 rows 2] 
]]></query>

Example 4-31 S[partition by A1, ..., Ak rows N] Stream Input

Timestamp   Tuple
1000        1,abc
1100        2,abc
1200        3,abc
2000        1,def
2100        2,def
2200        3,def
3000        1,ghi
3100        2,ghi
3200        3,ghi
h 3800
4000        1,jkl
4100        2,jkl
4200        3,jkl
5000        1,mno
5100        2,mno
5200        3,mno
h 12000
h 200000000

Example 4-32 S[partition by A1, ..., Ak rows N] Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           1,abc
1100:       +           2,abc
1200:       +           3,abc
2000:       +           1,def
2100:       +           2,def
2200:       +           3,def
3000:       -           1,abc
3000:       +           1,ghi
3100:       -           2,abc
3100:       +           2,ghi
3200:       -           3,abc
3200:       +           3,ghi
4000:       -           1,def
4000:       +           1,jkl
4100:       -           2,def
4100:       +           2,jkl
4200:       -           3,def
4200:       +           3,jkl
5000:       -           1,ghi
5000:       +           1,mno
5100:       -           2,ghi
5100:       +           2,mno
5200:       -           3,ghi
5200:       +           3,mno

S [partition by A1,..., Ak rows N range T]

This partitioned sliding window on a stream S takes a positive integer number of tuples N and a subset {A1,... Ak} of the stream's attributes as parameters and:

For an example, see "S[partition by A1, ..., Ak rows N range T] Example".

Examples

S[partition by A1, ..., Ak rows N range T] Example

Consider the query qPart_range2 in Example 4-33 and the data stream SP5 in Example 4-34. Stream SP5 has schema (c1 integer, name char(10)). The query returns the relation in Example 4-35. By default, the range time unit is second (see time_spec::=) so range 2 is equivalent to range 2 seconds. Timestamps are shown in milliseconds (1 s = 1000 ms).

Example 4-33 S[partition by A1, ..., Ak rows N range T] Query

<query id="qPart_range2"><![CDATA[ 
    select * from SP5 [partition by c1 rows 2 range 2]
]]></query>

Example 4-34 S[partition by A1, ..., Ak rows N range T] Stream Input

Timestamp   Tuple
1000        1,abc
2000        1,abc
3000        1,abc
4000        1,abc
5000        1,def
6000        1,xxx
h 200000000

Example 4-35 S[partition by A1, ..., Ak rows N range T] Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           1,abc
2000:       +           1,abc
3000:       -           1,abc
3000:       +           1,abc
4000:       -           1,abc
4000:       +           1,abc
5000:       -           1,abc
5000:       +           1,def
6000:       -           1,abc
6000:       +           1,xxx
7000:       -           1,def
8000:       -           1,xxx

S [partition by A1,..., Ak rows N range T1 slide T2]

This partitioned sliding window on a stream S takes a positive integer number of tuples N and a subset {A1,... Ak} of the stream's attributes as parameters and:

For an example, see "S[partition by A1, ..., Ak rows N] Example".

Examples

S[partition by A1, ..., Ak rows N range T1 slide T2] Example

Consider the query qPart_rangeslide in Example 4-36 and the data stream SP1 in Example 4-37. Stream SP1 has schema (c1 integer, name char(10)). The query returns the relation in Example 4-38. By default, the range and slide time unit is second (see time_spec::=) so range 1 slide 1 is equivalent to range 1 second slide 1 second. Timestamps are shown in milliseconds (1 s = 1000 ms).

Example 4-36 S[partition by A1, ..., Ak rows N range T1 slide T2] Query

<query id="qPart_rangeslide"><![CDATA[ 
    select * from SP1 [partition by c1 rows 1 range 1 slide 1] 
]]></query>

Example 4-37 S[partition by A1, ..., Ak rows N range T1 slide T2] Stream Input

Timestamp   Tuple
1000        1,abc
1100        2,abc
1200        3,abc
2000        1,def
2100        2,def
2200        3,def
3000        1,ghi
3100        2,ghi
3200        3,ghi
h 3800
4000        1,jkl
4100        2,jkl
4200        3,jkl
5000        1,mno
5100        2,mno
5200        3,mno
h 12000
h 200000000

Example 4-38 S[partition by A1, ..., Ak rows N range T1 slide T2] Relation Output

Timestamp   Tuple Kind  Tuple
1000:       +           1,abc
2000:       +           2,abc
2000:       +           3,abc
2000:       -           1,abc
2000:       +           1,def
3000:       -           2,abc
3000:       +           2,def
3000:       -           3,abc
3000:       +           3,def
3000:       -           1,def
3000:       +           1,ghi
4000:       -           2,def
4000:       +           2,ghi
4000:       -           3,def
4000:       +           3,ghi
4000:       -           1,ghi
4000:       +           1,jkl
5000:       -           2,ghi
5000:       +           2,jkl
5000:       -           3,ghi
5000:       +           3,jkl
5000:       -           1,jkl
5000:       +           1,mno
6000:       -           2,jkl
6000:       +           2,mno
6000:       -           3,jkl
6000:       +           3,mno
6000:       -           1,mno
7000:       -           2,mno
7000:       -           3,mno

IStream Relation-to-Stream Operator

Istream (for "Insert stream") applied to a relation R contains (s,t) whenever tuple s is in R(t) - R(t-1), that is, whenever s is inserted into R at time t. If a tuple happens to be both inserted and deleted with the same timestamp then IStream does not output the insertion.

In Example 4-39, the select will output a stream of tuples satisfying the filter condition (viewq3.ACCT_INTRL_ID = ValidLoopCashForeignTxn.ACCT_INTRL_ID). The now window converts the viewq3 into a relation, which is kept as a relation by the filter condition. The IStream relation-to-stream operator converts the output of the filter back into a stream.

Example 4-39 IStream

<query id="q3Txns"><![CDATA[ 
    Istream(
        select 
            TxnId, 
            ValidLoopCashForeignTxn.ACCT_INTRL_ID, 
            TRXN_BASE_AM, 
            ADDR_CNTRY_CD, 
            TRXN_LOC_ADDR_SEQ_ID 
        from 
            viewq3[NOW], 
            ValidLoopCashForeignTxn 
        where 
            viewq3.ACCT_INTRL_ID = ValidLoopCashForeignTxn.ACCT_INTRL_ID
    )
]]></query>

You can combine the Istream operator with a DIFFERENCES USING clause to succinctly detect differences in the Istream.

For more information, see:


DStream Relation-to-Stream Operator

Dstream (for "Delete stream") applied to a relation R contains (s,t) whenever tuple s is in R(t-1) - R(t), that is, whenever s is deleted from R at time t.

In Example 4-40, the query delays the input on stream S by 10 minutes. The range window operator converts the stream S into a relation, whereas the Dstream converts it back to a stream.

Example 4-40 DStream

<query id="BBAQuery"><![CDATA[ 
    Dstream(select * from S[range 10 minutes])
]]></query>

Assume that the granularity of time is minutes. Table 4-5 illustrates the contents of the range window operator's relation (S[Range 10 minutes] ) and the Dstream stream for the following input stream TradeInputs:

Time    Value
05      1,1
25      2,2
50      3,3

Table 4-5 DStream Example Output

Input Stream S Relation Output Relation Contents DStream Output

05    1,1

+ 05    1,1

{1, 1}

 
 

- 15    1,1

{}

+15    1,1

25    2,2

+ 25    2,2

{2,2}

 
 

- 35    2,2

{}

+35    2,2

50    3,3

+ 50    3,3

{3,3}

 
 

- 60    3,3

{}

+60    3,3


Note that at time 15, 35, and 60, the relation is empty {} (the empty set).

You can combine the Dstream operator with a DIFFERENCES USING clause to succinctly detect differences in the Dstream.

For more information, see:


RStream Relation-to-Stream Operator

The Rstream operator maintains the entire current state of its input relation and outputs all of the tuples as insertions at each time step.

Since Rstream outputs the entire state of the relation at every instant of time, it can be expensive if the relation set is not very small.

In Example 4-41, Rstream outputs the entire state of the relation at time Now and filtered by the where clause.

Example 4-41 RStream

<query id="rstreamQuery"><![CDATA[ 
    Rstream(
        select
            cars.car_id, SegToll.toll 
        from 
            CarSegEntryStr[now] as cars, SegToll 
        where (cars.exp_way = SegToll.exp_way and 
               cars.lane = SegToll.lane and 
               cars.dir = SegToll.dir and 
               cars.seg = SegToll.seg)
    )
]]></query>

For more information, see idstream_clause::=.