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 |
|---|---|
|
|
Identity, negation |
|
|
Multiplication, division |
|
|
Addition, subtraction, concatenation |
|
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
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.
See Also:
Example 4-1 shows how to use the concatenation operator to append the String "xyz" to the value of c2 in a select statement.
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 |
<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:
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".
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-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".
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 withh (such as h 3800) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.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".
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".
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:
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>
Tuple-Based Stream-to-Relation Window Operators
Oracle CQL supports the following tuple-based stream-to-relation window operators:
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".
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 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-25 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-26 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-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 1002: + 15,0.14 1004: + 33,4.4 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: - 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".
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:
(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:
-
Logically partitions
Sinto different substreams based on equality of attributesA1,... Ak(similar to SQLGROUP BY). -
Computes a tuple-based sliding window of size
Nindependently on each substream.
For an example, see "S[partition by A1, ..., Ak rows N] Example".
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 withh (such as h 3800) are heartbeat input tuples. These inform Oracle CEP that no further input will have a timestamp lesser than the heartbeat value.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:
-
Logically partitions
Sinto different substreams based on equality of attributesA1,... Ak(similar to SQLGROUP BY). -
Computes a tuple-based sliding window of size
Nand rangeTindependently on each substream.
For an example, see "S[partition by A1, ..., Ak rows N range T] Example".
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:
-
Logically partitions
Sinto different substreams based on equality of attributesA1,... Ak(similar to SQLGROUP BY). -
Computes a tuple-based sliding window of size
N, rangeT1, and slideT2independently on each substream.
For an example, see "S[partition by A1, ..., Ak rows N] Example".
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.
<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.
<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 |
|---|---|---|---|
|
|
|
|
|
|
|
|
+ |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
+ |
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.
<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::=.