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.

This chapter includes the following sections:

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 Stream Analytics evaluates different operators in the same expression. When evaluating an expression containing multiple operators, Oracle Stream Analytics evaluates operators with higher precedence before evaluating those with lower precedence. Oracle Stream Analytics 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 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.

4.2 Arithmetic Operators

Table 4-2 lists arithmetic operators that Oracle Stream Analytics 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 data types or to any data type that can be implicitly converted to a numeric data type.

In certain cases, Oracle Stream Analytics converts the arguments to the data type as required by the operation. For example, when an integer and a float are added, the integer argument is converted to a float. The data type of the resulting expression is a float. For more information, see Implicit Data Type 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 Stream Analytics supports arithmetic operations using numeric literals and using datetime and interval literals.

For more information, see:

4.3 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 data type CHAR, then the result has data type CHAR and is limited to 2000 characters. Trailing blanks in character strings are preserved by concatenation, regardless of the data types of the string.

Although Oracle Stream Analytics 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 Stream Analytics. To concatenate an expression that might be null, use the NVL function to explicitly convert the expression to a zero-length string.

4.4 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.

The following example 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".

<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 Grouping and Alternation in the PATTERN Clause.

4.5 Range-Based Stream-to-Relation Window Operators

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

Note:

Very large numbers must be suffixed. Without the suffix, Java treats very large numbers like an integer and the value might be out of range for an integer, which throws an error.

Add a suffix as follows:

l or L for Long

f or F for float

d or D for double

n or N for big decimal

For example:

SELECT * FROM channel0[RANGE 1368430107027000000l nanoseconds]

window_type_range::=

For more information, see:

4.5.1 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 Stream Analytics is nanoseconds and hence all these tuples expire 1 nanosecond later.

For an example, see S [now] Example.

4.5.1.1 Examples

S [now] Example

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

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

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

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

4.5.2 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.

4.5.2.1 Examples

S [range T] Example

Consider the query q1. Given the data stream S, the query returns the relation. By default, the range time unit is second, 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 Stream Analytics that no further input will have a timestamp lesser than the heartbeat value.

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

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

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

4.5.3 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).

Suppose a tuple arrives at a time represented by t. Assuming a slide value represented by T2, the tuple will be visible and sent to output at one of the following timestamps:

t -- If the timestamp t is a multiple of slide T2
Math.ceil(t/T2)*T2 -- If the timestamp is not a multiple of slide T2

Assuming a range value represented by T1, a tuple that arrives at timestamp t will expire at timestamp t + T1. However, if a slide is specified and its value is non-zero, then the expired tuple will not necessarily output at timestamp t + T1.

The expired tuple (expired timestamp is t + T1) will be visible at one of the following timestamps:

(t + T1) -- If the timestamp (t+T1) is a multiple of slide T2.
Math.ceil((t+T1)/T2)*T2 -- If the timestamp (t+T1) is not a multiple of slide T2.

For an example, seeS [range T1 slide T2] Example.

4.5.3.1 Examples

S [range T1 slide T2] Example

Consider the query q1. Given the data stream S, the query returns the relation. By default, the range time unit is second, 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.

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

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

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

4.5.4 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.

4.5.4.1 Examples

S [range unbounded] Example

Consider the query q1 and the data stream . Timestamps are shown in milliseconds (1 s = 1000 ms). Elements are inserted into the relation as they arrive. No elements are subject to deletion. The following example shows the relation that the query returns at time 5000 ms and the relation that the query returns at time 205000 ms.

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

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

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

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

4.5.5 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:

4.5.5.1 Examples

S [range C on E] Example: Constant Value

Consider the query tkdata56_q0 and the data stream tkdata56_S0 . Stream tkdata56_S0 has schema (c1 integer, c2 float). The following example 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.

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

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

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 and the data stream tkdata56_S1. Stream tkdata56_S1 has schema (c1 timestamp, c2 double). The following example shows the relation that the query returns.

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

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

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

4.6 Tuple-Based Stream-to-Relation Window Operators

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

window_type_tuple::=

S [rows N]

For more information, see:

4.6.1 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 Stream Analytics 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.

4.6.1.1 Examples

S [rows N] Example

Consider the query q1 and the data stream S. 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).

The following example 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).

The following example 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).

The following example 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).

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

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

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

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

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

4.6.2 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).

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

For examples, see S [rows N] Example.

4.6.2.1 Examples

S [rows N1 slide N2] Example

Consider the query tkdata55_q0 and the data stream tkdata55_S55. Stream tkdata55_S55 has schema (c1 integer, c2 float).

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).

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

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

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

4.7 Partitioned Stream-to-Relation Window Operators

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

window_type_partition::=

For more information, see:

4.7.1 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:

Logically partitions S into different substreams based on equality of attributes A1,... Ak (similar to SQL GROUP BY).
Computes a tuple-based sliding window of size N independently on each substream.

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

4.7.1.1 Examples

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

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

Note:

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

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

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

4.7.2 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:

Logically partitions S into different substreams based on equality of attributes A1,... Ak (similar to SQL GROUP BY).
Computes a tuple-based sliding window of size N and range T independently on each substream.

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

4.7.2.1 Examples

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

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

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

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

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

4.7.3 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:

Logically partitions S into different substreams based on equality of attributes A1,... Ak (similar to SQL GROUP BY).
Computes a tuple-based sliding window of size N, range T1, and slide T2 independently on each substream.

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

4.7.3.1 Examples

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

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

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

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

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

4.8 User-Defined Stream-to-Relation Window Operators

You can write user-defined (or extensible) windows in Java to create stream-to-relation operators that are more advanced or application-specific than the built-in stream-to-relation operators that Oracle Stream Analytics provides. User-defined windows can appear in Oracle CQL statements wherever a built-in stream-to-relation window operator can occur.

Note:

You can also create user-defined functions (see User-Defined Functions).

4.8.1 Implementing a User-Defined Window

Using the classes in the oracle.cep.extensibility.windows package you can create the following types of user-defined windows:

generic time window.

4.8.1.1 How to Implement a User-Defined Generic Time Window

You implement a user-defined generic time window by implementing a Java class that implements the GenericTimeWindow interface.

To implement a user-defined generic time window:

Implement a Java class.

import java.io.IOException;
import java.sql.Timestamp;

import oracle.cep.extensibility.windows.GenericTimeWindow;

public class MyRangeSlideWindow implements GenericTimeWindow {
    private long range;
    private long slide;

    public void setInputParams(Object[] obj) throws IOException{
        if(obj.length != 2)
            throw new IOException("inappropriate number of arguments");
        range = (((Integer)obj[0]).intValue())*1000;
        slide = (((Integer)obj[1]).intValue())*1000;
    }

    public boolean visibleW(Timestamp t, Timestamp visTs) {
        long actual = t.getTime();
        if(getVisibleTs(actual) < actual)
            return false;
        visTs.setTime(getVisibleTs(actual));
        return true;
    }

    private long getVisibleTs(long time) {
        if(slide > 1) {
            long t = time / slide;
            if((time % slide) == 0)
                return(t*slide);
            else
                return((t+1)*slide);
        } else
            return time;
    }

    public boolean canOutputTsGTInputTs() {
        if(slide > 1)
            return true;
        return false;
    }
 
    public boolean expiredW(Timestamp ts, Timestamp expTs) {
        long actual = ts.getTime();
        long visibleTs = getVisibleTs(actual);
        long expiredTs = visibleTs + range;
        expTs.setTime(expiredTs);
        // This is the border line case, when range > slide and visibleTs < range
            if(visibleTs < range)
                return false;
        return true;
    }
}

...
<rule id="range_slide"><![CDATA[ 
    register window range_slide(winrange int, winslide int) implement using "MyRangeSlideWindow"
]]></rule>
...
<query id="q79"><![CDATA[ 
    select * from S12 [range_slide(10,5)] 
]]></query>
...

Use the user-defined window in the FROM clause.

4.9 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.

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.

<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:

Detecting Differences in Query Results.

4.10 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. If a tuple happens to be both inserted and deleted with the same timestamp, then IStream does not output the insertion.

In the following example, 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.

<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}`
`05 1,1`	`- 15 1,1`	`{}`	+`15 1,1`
`25 2,2`	`+ 25 2,2`	`{2,2}`
`25 2,2`	`- 35 2,2`	`{}`	`+35 2,2`
`50 3,3`	`+ 50 3,3`	`{3,3}`
`50 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:

Detecting Differences in Query Results.

4.11 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 the following example, Rstream outputs the entire state of the relation at time Now and filtered by the where clause.

<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>