# 4 Operators

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)`, ||`

Oracle CQL conditions are evaluated after Oracle CQL operators

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.

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

## Range-Based Stream-to-Relation Window Operators

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

window_type_range::=

### 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-2 and the data stream `S` in Example 4-3. Timestamps are shown in nanoseconds (`1 sec = 10^9 nanoseconds`). Example 4-4 shows the relation that the query returns at time `5000 ms`. At time `5002 ms`, the query would return an empty relation.

Example 4-2 S [now] Query

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

Example 4-3 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-4 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-5. Given the data stream `S` in Example 4-6, the query returns the relation in Example 4-7. 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-5 S [range T] Query

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

Example 4-6 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-7 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-8. Given the data stream `S` in Example 4-9, the query returns the relation in Example 4-10. 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-8 S [range T1 slide T2] Query

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

Example 4-9 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-10 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-11 and the data stream `S` in Example 4-12. 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-13 shows the relation that the query returns at time `5000 ms` and Example 4-14 shows the relation that the query returns at time `205000 ms`.

Example 4-11 S [range unbounded] Query

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

Example 4-12 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-13 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-14 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-15 and the data stream `tkdata56_S0` in Example 4-16. Stream `tkdata56_S0` has schema `(c1 integer, c2 float)`. Example 4-17 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-15 S [range C on E] Constant Value: Query

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

Example 4-16 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-17 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-18 and the data stream `tkdata56_S1` in Example 4-19. Stream `tkdata56_S1` has schema `(c1 timestamp, c2 double)`. Example 4-20 shows the relation that the query returns.

Example 4-18 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-19 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-20 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::=

### 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-21 and the data stream `S` in Example 4-22. 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-23 shows the relation that the query returns at time `1003 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-24 shows the relation that the query returns at time `1007 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 `1007 ms`, namely tuples `(15,0.14)`, `(33,4.4)`, and `(23,56.33)`.

Example 4-25 shows the relation that the query returns at time `2001 ms`. At this time, Oracle CEP deletes elements that have been in the window longer than the default range (and slide) of `1000 ms` (`1 s`). 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 tuple `(17,1.3)`.

Example 4-21 S [rows N] Query

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

Example 4-22 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-23 S [rows N] Relation Output at Time 1003 ms

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

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

```Timestamp   Tuple Kind  Tuple
1002:     +           15,0.14
1004:     +           33,4.4
1006:     +           23,56.33
```

Example 4-25 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:     -           23,56.33
1008:     -           34,4.4
1010:     -           20,0.2
1012:     -           45,23.44
1014:     -           30,0.3
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-26 and the data stream `tkdata55_S55` in Example 4-27. Stream `tkdata55_S55` has schema `(c1 integer, c2 float)`. The output relation is shown in Example 4-28.

As Example 4-28 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-26 S [rows N1 slide N2] Query

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

Example 4-27 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-28 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::=

### 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".

Examples

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

Consider the query `qPart_row2` in Example 4-29 and the data stream `SP1` in Example 4-30. Stream `SP1` has schema `(c1 integer, name char(10))`. The query returns the relation in Example 4-31. 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-29 S[partition by A1, ..., Ak rows N] Query

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

Example 4-30 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-31 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:

• 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".

Examples

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

Consider the query `qPart_range2` in Example 4-32 and the data stream `SP5` in Example 4-33. Stream `SP5` has schema `(c1 integer, name char(10))`. The query returns the relation in Example 4-34. 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-32 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-33 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-34 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:

• 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".

Examples

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

Consider the query `qPart_rangeslide` in Example 4-35 and the data stream `SP1` in Example 4-36. Stream `SP1` has schema `(c1 integer, name char(10))`. The query returns the relation in Example 4-37. 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-35 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-36 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-37 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-38, 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-38 IStream

```<query id="q3Txns"><![CDATA[
Istream(
select
TxnId,
ValidLoopCashForeignTxn.ACCT_INTRL_ID,
TRXN_BASE_AM,
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`.

## 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-39, 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-39 DStream

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

Assume that the granularity of time is minutes. Table 4-4 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-4 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`.

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

Example 4-40 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>
```