Expression Grammar
|
Operators |
Date Functions |
String Functions |
Other Functions |
|
JSON Functions |
Statistical Functions |
Rounding Functions |
Trig Functions |
|
Log and Exp Functions |
Constants |
System Variables |
Variable Types |
Variable Types in Expressions
When you reference a stream as a variable in a derivation expression, the stream's value type determines what operations are available. The table below lists how each type appears inside an expression.| Stream Type | Available Operations | Notes |
| Smallint, Integer, Bigint, Real, Double precision, Numeric, Latitude, Longitude, Elevation, Direction360 | Arithmetic (+ - * / %), comparisons, all math / trig / log / rounding / statistical functions. |
Standard numeric behavior. |
| Boolean | Logical operators (&&, ||, !), comparisons. |
|
| Varchar (String), File | String functions (len, substr, left, right, mid, lower, upper, trim, replaceAll, replaceFirst, etc.), + concatenation, comparisons. |
|
| Date, Timestamp, Timestamptz (DateTime) | Numeric arithmetic on the underlying epoch-millisecond value (e.g. d / 1000 for epoch seconds, d - 86400000 to subtract a day), comparisons, dtFormatter(d, pattern, tz) for formatted display strings. |
Stored as epoch milliseconds; behaves identically to the legacy DateTime type inside expressions. |
| Time | Comparison and equality only. | Stored as microseconds since midnight. Date helpers like dtFormatter assume milliseconds and will misinterpret a Time variable, so don't pass one. Dedicated time-of-day helpers may arrive in a later release. |
| JSON, JSONB | String functions on the raw JSON text (len, substr, etc.), isJSON, comparisons, JSON_Value / JSON_Query / JSON_Exists / JSON_Modify for path-based access. |
|
| UUID | String functions on the canonical 8-4-4-4-12 hex text form (substr, lower, upper, len, equality, concatenation). |
The 16-byte storage is auto-converted to text inside the expression. When deriving a UUID-typed output, the result string must parse as a valid UUID or the sample is set to NULL. |
| Interval | Not supported in derivations. | An Interval column can't appear as a variable or as the derived output. Compose intervals at query time with the GS SQL Interval functions instead. |
| Bytea (Byte Array) | Not supported in derivations. | Raw bytes have no expression operations in this release. |
| Term | Description | Example |
| Operators |
||
| +, - (unary) | Makes operand positive or negative. | +x -x |
| /, *, %, +, -,^ | Divide, Multiply, Modulus, Plus, Subtract, Power | (a/b) * (c%d) + e - (f+1)^2 |
| .,^^ | Dot product, Cross product | (a/b) * (c%d) + e - (f+1)^2 |
| ! | Boolean Not | !(a > b) |
| = | Assignment | a = 3 |
| <, <=, >, >=, =, ==, !=, <> |
Less than: < Less than or equal: <= Greater than: > Greater than or equal: >= Equal to: == Not equal: != Not equal: <> |
a < 10 a < 10 a <= b a > 10 a >= 10 a == 10 a != (c + 1) a <>10 |
| &&, || | Boolean And, Boolean Or | a > 0 && b == "hello" |
| &, |, <<, >>, ~,**,<<< | Bitwise: &: If both bits are 1, it gives 1, else it gives 0 |: If either of the bits is 1, it gives 1, else it gives 0. <<: Shifts the bits of the number to the right and fills 0 on voids left as a result. >>: Shifts the bits of the number to the right and fills the voids left with the sign bit. ~: It makes every 0 to 1, and every 1 to 0. **: Xor. The result is a 1 if only one of the bits is 1, and a 0 if both bits are 0 or both bits are 1. <<<: Right shift with zero extension |
a & b a | b a << b a >> b a ~ b a ** b a <<< b |
| String Operators | The following operators work on Strings: <, <=, >, >=, =, ==, !=, <>, + | x != "hello" |
| Trig Functions |
||
| sin(x) | Sine | sin(a) |
| cos(x) | Cosine | cos(a) |
| tan(x) | Tangent | tan(a) |
| asin(x) | Arc Sine | aaa(a) |
| acos(x) | Arc Cosine | acos(a) |
| atan(y,x) | Arc Tangent. | atan(a,b) |
| sec(x) | Secant | sec(a) |
| cosec(x) | Cosecant | cosec(a) |
| cot(x) | Co-tangent | cot(a) |
| sinh(x) | Hyperbolic Sine | sinh(a) |
| cosh(x) | Hyperbolic Cosine | cosh(a) |
| tanh(x) | Hyperbolic Tangent | tanh(a) |
| asinh(x) | Inverse Hyperbolic Sine | asinh(a) |
| acosh(x) | Inverse hyperbolic Cosine | acosh(a) |
| atanh(x) | Inverse Hyperbolic Tangent | atanh(a) |
| Log and Exponential Functions |
||
| ln(x) | Natural Logarithm. | ln(a) |
| log(x) | Logarithm base 10. | log(a) |
| lg(x) | Logarithm base 2 | lg(a) |
| exp(x) | Exponential (e^x) | exp(a) |
| pow(x) | Computes the power of an number | pow(a) |
| Statistical Functions |
||
| avg(x1, x2, x3, ...) | Average. Calculates the average of all its arguments. | avg(x1, x2, x3, ...) |
| min(x1, x2, x3, ...) | Minimum. Returns the smallest of all its arguments. | min(x1, x2, x3, ...) |
| max(x1, x2, x3, ...) | Maximum. Returns the largest of all its arguments. | max(x1, x2, x3, ...) |
| vsum(x1, x2, x3, ...) | Vector Sum. Summation function which expands the arguments. The VSum function will expand any arrays given as arguments so vsum([1,2],[3,4]) will be 10. This differs from the standard Sum function which does not expand them. The result of sum([1,2],[3,4]) in contrast would be [4,6]. | vsum(x1, x2, x3, ...) |
| avgNg(x1, previousSampleCount [, respectTod] [, maxLookBackIntvls] [, maxLookBackIncludesFilteredIntvls] [, minSampleCount])) |
Calculates the rolling average while ignoring gaps. Ng stands for no gaps. Can only be used with interval streams. The respect-time-of-day argument, in combination with a Time Filter on the stream, allows for calculating rolling averages such as the rolling average at a specific time and day of week. It was orginally developed for for complex time-of-use energy rate calculations. x1: The stream variable being averaged. previousSampleCount: The number of previous, or earlier, samples to use for the average calculation. The calculation will use a lesser number if there are not enough samples to average. See minSampleCount to restrict this behavior. respectTod: Optional. Defaults to false. Stands for Respect-Time-Of-Day and when set to true, it averages only one sample per day that occurs at the same time interval for each day. maxLookBackIntvls: Optional. Defaults to 50. The maximum number of intervals to look back before returning NULL. This value prevents calculating a result when a large number of samples are null. maxLookBackIncludesFilteredIntvls: Optional. Defaults to false. Whether maxLookBackIntvls includes intervals filtered out with a time filter. minSampleCount: Optional. Defaults to no minimum. The minimum number of non-Null samples required for averaging. Will return NULL if the minimum is not met. |
avgNg(var1, 10, true, 45, false, 5): Look back 45 days and average 10 non-nulls, or if not 10, then 9, if not 9, then 8…. if not at least 5, then NULL. |
| avgPeaksNg(x1, previousSampleCount [, respectTod] [, maxLookBackIntvls] [, maxLookBackIncludesFilteredIntvls] [, minSampleCount])) | Just like avgNg but returns the Maximum sample instead of the average. | avgPeaksNg(var1, 10, true, 45, 5): Look back 45 days and return the maximum, or peak, of 10 non-nulls, or if not 10, then 9, if not 9, then 8…. if not at least 5, then NULL. |
| avgNgForward(x1, futureSampleCount [, respectTod] [, maxLookForwardIntvls] [, maxLookForwardIncludesFilteredIntvls] [, minSampleCount])) | Just like avgNg but looks forward instead of backwards to calculate the average. Ignores the current day. | avgPeaksNg(var1, 10, true, 45, 5): Look forward 45 days and return the maximum, or peak, of 10 non-nulls, or if not 10, then 9, if not 9, then 8…. if not at least 5, then NULL. |
| ruPercentile(x1, percentileDecimal)) |
Calculates the roll-up percentile. x1: The stream variable being processed. percentileDecimal: A number between zero and one. |
ruPercentile(var1, 0.8) |
| Rounding Functions |
||
| round(x), round(x, p) |
Rounds a number. round(a) adds 0.5 to the argument and returns the closest integer. round(a,3) rounds the argument to 3 decimal places. |
round(x), round(x, p) |
| rint(x), rint(x, p) |
Rounds a number to the closest integer. |
rint(2.4) finds the closest integer to the argument (2) rint(2.1234, 3) rounds the argument to 3 decimal places (2.123) |
| floor(x) | Finds the largest integer above the number floor(pi) give 3 floor(-i) give -4 | floor(x) |
| ceil(x) | Finds the smallest integer above the number ceil(pi) give 4 ceil(-i) give -3 | ceil(pi) give 4 ceil(-i) give -3 |
| String Functions |
||
| str(num) | Convert a number to a string. | str(1.2) -> "1.2" |
| fromBase(str,base) | Converts a string to a number in a given base. Instance of these classes can be specified with two different behaviours either with the base specified in the constructor or with no base specified in which case the function requires two arguments, the second of which is the base. A third form allows a prefix to be specified, this prefix must be present in the Jep expression. | fromBase("256", 16) -> 100 |
| left(str, len) |
Extract the left most n characters from a string:
|
left("abcdefg",2) -> "ab" |
| right(str, len) |
Extract the right most n characters from a string:
|
right("abcdefg",2) -> "fg" |
| mid(str, start, len) |
Extract substring of first argument. Syntax: mid(str,start,len) Second argument is starting index,
with the first character being at index 0. Third argument is number of characters to return.
Note this differs from Excel's mid function which has 1 as the index of the first character.
|
mid("abcdefg",2,3) -> "cde" |
| substr(str, start, len) |
Extract substring of first argument. Syntax substring(str,start,[end]).
Second argument is starting index, third argument is optional,
if not present return rest of string if present third argument is the ending index, exclusive. Note that the indices are zero-based.
|
substring("abcdefg",2,4) -> "cd" |
| lower(str) | Convert a string to lowercase. | lower("abCDefg") -> "abcdefg" |
| upper(str) | Convert a string to uppercase. | upper("Hello") -> "HELLO" |
| len(str) | Returns the length of a string. | len("Hello") -> 5 |
| trim(str) | Trims leading and trailing whitespace. | trim(" Hello ") ->"Hello" |
| replaceAll(str, regex, replacement) | Replaces each substring of this string that matches the given regular expression with the given replacement. | replaceAll("I love cats. Cats are very easy to love. Cats are very popular.", "(?i)cat", "dog") -> "I love dogs. dogs are very easy to love. dogs are very popular." |
| replaceFirst(str, regex, replacement) | Replaces the first substring of this string that matches the given regular expression with the given replacement. | replaceAll("I love cats. Cats are very easy to love. Cats are very popular.", "(?i)cat", "dog") -> "I love dogs. Cats are very easy to love. Cats are very popular." |
| parseCSV(str, index, [separator],[stringDelimiter]) |
Parses a delimited string as if it were a line in an import file. The parsed substring at the passed in column index is returned. str is the string to split into substrings, or columns. index is the column index of the parsed substring to return. separator is optional. Defaults to comma. It is the character that separates substrings, or columnsone . stringDelimiter is optional. Defaults to double quote. It is the character that delimits text data. |
parseCSV('aa,bb,cc,dd', 0) -> 'aa' parseCSV('aa,bb,cc,dd',1, ',') -> 'bb' parseCSV('"aa","bb","cc","dd"', 3, ',') -> 'dd' parseCSV('"a,a","bb","cc","dd"', 0, ',') -> 'a,a' parseCSV("'a,a','bb','cc','dd'", 0, ',', \"'\") -> 'a,a' single quote separator parseCSV('aa|bb|cc|dd', 3,'|') -> 'dd' pipe separator parseCSV('aa\tbb\tcc\tdd', 3,'\t') -> 'dd' tab separator parseCSV('11,22,33,44', 2) -> '33' parseCSV('', 0) -> Throws Exception parseCSV('aa,bb,cc,dd', 4) -> Throws Exception |
| Date and Time Functions |
||
| time() | Current Time in epoch milliseconds. Epoch milliseconds, (or Unix epoch milliseconds), refers to the number of milliseconds that have elapsed since the Unix epoch, which is January 1, 1970, at 00:00:00 Coordinated Universal Time (UTC) | time() -> 1734382894177 |
| dtFormatter(epochTime, pattern, timeZoneId) |
Formats an epoch millis datetime into a readable string. This method maps
to Java’s DateFormatter List of Time Zones |
dtFormatter(time(), "dd/MM/yyyy, hh:mm:ss", "US/Central") -> "17/04/2021, 08:29:15" |
| JSON Functions |
||
| isJSON(str), isJSON(str, [typeConstraint]) |
Determine whether a given string is valid JSON. typeConstraint is optional. Type Constraint are: VALUE: Tests for a valid JSON value. This can be a JSON object, array, number, string or one of the three literal values (false, true, null) SCALAR: Tests for a valid JSON scalar – number or string OBJECT: Tests for a valid JSON object ARRAY: Tests for a valid JSON array It defaults to VALUE. Specifying the typeConstraint argument puts a constraint on which type of JSON object is allowed. If the string is valid JSON, but not that type, false is returned. |
isJSON(1) -> throws exception - not a string isJSON('1') -> true isJSON('"1"') -> true isJSON('x') -> false isJSON('"x"') -> true per RFC 8259. isJSON('[]') -> true isJSON('{}') -> true isJSON(NULL) -> false isJSON('null') -> true isJSON(1, "SCALAR") -> throws exception - not a string isJSON('1', "SCALAR") -> true isJSON('"1"', "SCALAR") -> true isJSON('1', "ARRAY") -> false isJSON('1', "OBJECT") -> false isJSON('{}', "SCALAR") -> false isJSON('{}', "ARRAY") -> false isJSON('{}', "OBJECT") -> true isJSON('[]', "ARRAY") -> true |
| JSON_Exists(jsonstr, jsonPath) |
Determines whether a JSON string satisfies a given path search criterion. |
JSON_Exists('{"info":{"address":[{"town":"LA"},{"town":"Atlanta"}]}}', '$.info.address') -> true JSON_Exists('{"info":{"address":[{"town":"LA"},{"town":"Atlanta"}]}}', '$.info.addresses') -> false JSON_Exists('{"a": [{ "b": 1 }]}', '$.a[0].b') -> true JSON_Exists('{"a": true}', "$.[]b") -> Throws path parsing exception JSON_Exists('{\"user\":{\"name\":null}}', '$.user.name') -> false |
| JSON_Object(x1, x2, x3, x4, ...) |
Builds a JSON object string from a list of key-value pairs. Returns a JSON string. Key names must be non-null non-empty strings. String values that are detected as JSON_Array or JSON_Object are inserted without escaping quotes. This allows building nested JSON structures. |
JSON_Object() -> '{}' JSON_Object('a', 1, 'b', 2) -> '{"a":1.0,"b":2.0}' JSON_Object('a', '[]') -> '{"a":[]}' JSON_Object('a', NULL, 'b', JSON_Array(1,2)) -> '{"a":null,"b":[1.0,2.0]}' JSON_Object('a', null, 'b', JSON_Array(1,2)) -> exception: variable 'null' undefined. Use NULL instead.' JSON_Object("a",NULL,"b",JSON_Object('c','xxx')) -> '{"a":null,"b":{"c":"xxx"}}' |
| JSON_Array(x1, x2, x3, ...) |
Constructs JSON array text from zero or more values. The return value will be the array as JSON text. String values that are detected as JSON_Array or JSON_Object are inserted without escaping quotes. This allows building nested JSON structures. Returns a JSON string. |
JSON_Array() -> '[]' JSON_Array('') -> '[""]' JSON_Array(1, '2') -> '[1.0,"2"]' JSON_Array(1,null) -> exception: variable 'null' undefined. Use NULL instead. JSON_Array(1,NULL) -> '[1.0,null]' JSON_Array(JSON_Array(1)) -> '[[1]]]' JSON_Array(1, JSON_Object('a', 1, 'b', 'hello')) -> '[1.0,{"a":[1.0,"b":"hello"]}]' |
| JSON_Value(jsonstr, jsonPath, [defaultValue]) |
Extracts a scalar value from a JSON string. null is returned if there are no search results and a defaultValue was not included.
An exception is thrown if a JSON Object or JSON Array is attempting to be returned. defaultValue is optional. The default will be returned if the path does not exist. The default value can be a scalar value. String default values are not delimited before returned. Use JSON_Query to return objects and arrays. |
JSON_Value('{"a": true}', '$.a') -> true JSON_Value('{"a": "xxx"}', '$.a') -> 'xxx' JSON_Value('{"a": 1}', '$.a') -> 1.0 JSON_Value('{"a": 1.0}', '$.a') -> 1.0 JSON_Value('{"a": null}', '$.a') -> null JSON_Value('{"a": 'null'}', '$.a') -> 'null' JSON_Value('{"a": true}', '$.b') -> Throws exception - path doesn't exist and no default value JSON_Value('{"a": { "b": "hello" } }', '$.a.b') -> Throws Exception - not returning a scalar JSON_Value('{"a": { "b": [] } }', '$.a.b') -> Throws Exception - not returning a scalar With default values: JSON_Value('{"a": null}', '$.a', 'xx') -> null JSON_Value('{"a": null}', '$.b', true) -> true |
| JSON_Query(jsonstr, jsonPath, [defaultValue]) |
Extracts JSON values from a JSON string. The result is always returned as a STRING or null. defaultValue is optional. It must be a STRING. The default will be returned if the path does not exist. The default value is not delimited before returned. This method searches a JSON string for a given path expression and returns the value. The value is returned as STRING. Use JSON_Value to return scalars. |
JSON_Query('{"a": true}', '$.a') -> 'true' JSON_Query('{"a": 1}', '$.a') -> '1' JSON_Query('{"a": 1.0}', '$.a') -> '1.0' JSON_Query('{"a": null}', '$.a') -> null JSON_Query('{"a": "null"}', '$.a') -> '"null"' JSON_Query('{"a": true}', '$.b') -> throws exception JSON_Query('{"a": { "b": "hello" } }', $.a) -> '{"b":"hello"}' JSON_Query('{"a": null}', '$.a', 'xx') -> null JSON_Query('{"a": null}', '$.b', 'true') -> '"true"' JSON_Query('{"a": 1}', '$.b', null) -> null JSON_Query(JSON_Object('a', JSON_Array('c','d')), '$.a[1]' -> '"d"' |
| JSON_Modify(jsonstr, jsonPath, newValue) |
Updates the value of a property in a JSON string and returns the updated JSON string.
The result is always returned as a STRING. If the property isn't present, JSON_Modify tries to insert the new value on the specified path. Insertion can fail if the property can't be inserted on the path. Nested arrays are supported. newValue can be a scalar value (number, boolean, string). JSON_Modify deletes the specified key if newValue is NULL. JSON_Modify escapes all special characters in the new value if the type of the value is STRING. A STRING value isn't escaped if it's properly formatted JSON produced by FOR JSON, JSON_QUERY, or JSON_MODIFY. |
JSON_Modify('{"a": true}', '$.a', false) -> '{"a": false}' JSON_Modify('{"a": 1}', '$.a', '1') -> '{"a": "1"}' JSON_Modify('{"a": 1.0}', '$.a', 1) -> '{"a": 1.0}' JSON_Modify('{"a": null}', '$.a', NULL) -> '{}' JSON_Modify('[0]', '$[1]', 1) -> '[0,1.0]' JSON_Modify('[0]', '$[1][0][1]', 1) -> '[0,[[null,1.0]]]' JSON_Modify('{\"a\":{\"b\":{\"c\":[\"xxx\"]}}}', '$.a.b.c[0]', 'yyy') -> '{\"a\":{\"b\":{\"c\":[\"yyy\"]}}}' JSON_Modify('{\"name\":\"John\",\"skills\":[\"C#\",[\"SQL\",\"Java\"]]}', '$.skills[1][4].b', 'xxx') -> '{\"name\":\"John\",\"skills\":[\"C#\",[\"SQL\",\"Java\",null,null,{\"b\":\"xxx\"}]]}' |
| Other Functions |
||
| if(cond, trueval, falseval) | The if(condExpr, posExpr, negExpr) function. The value of trueExpr will be returned if condExpr is >0 or Boolean.TRUE and value of negExpr will be returned if condExpr is x<= 0 or Boolean.TRUE. If condExpr is NaN then NaN is returned. | if(kWh > 50, cost*0.5, cost*0.4) if(isNull(a), 0, if(kWh > 50, cost*0.5, cost*0.4)) |
| abs(x) | Absolute value function. For Numbers it returns the absolute value of same type, so a Double will return a Double, Integer will return an Integer. For Complex it returns the modulus. | abs(-1) -> 1.0 |
| rand() | Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0. Utilizes Java's Math.random() | rand() -> 0.18752629517947317 |
| sqrt(x) | Square root function. Positive Number arguments returns double results. Number arguments return Complex results by default. | sqrt(10) -> 3.1622776601683795 sqrt(-10) -> (0.0, 3.1622776601683795) |
| sum(x,y,...) | Adds it arguments. Does not expand array in its arguments. | sum(1,2,3,4,5) -> 15 sum([1,2],[3,4]) -> [4,6] |
| binom(n,i) |
Binomial coefficients. Requires n,i integers >=0. Often written as nCi or column vector (n,i). The following rules apply:
|
binom(6, 3) -> 20 |
| signum(x) | The signum function returns -1 if x<0, 1 if x>0, 0 if x==0. | signum(10) -> 1 |
| isNull(x) | Tests if the argument is null. | isNull(10) -> false isNull(NULL) --> true |
| Constants |
||
| pi | Pi is a mathematical constant that represents the ratio of a circle's circumference to its diameter. | pi -> 3.141592653589793 |
| e | The number e, also known as Euler's number, is a mathematical constant that represents the base of the natural logarithm. | e -> 2.718281828459045 |
| NULL | Indicates no value. Return NULL to indicate no value was calculated. For interval stream calculations returning NULL, the result for the time being processed will be missing or a Gap. The expression engine is a bity flaky when it comes to working with null. It will not accept null as a direct function parameter, but it will accept NULL. For example, it will not accept isNull(null), but it will accept isNull(NULL); | if(state==0, NULL, state)) |
| System Variables |
||
| LAST_VALUE | The derived stream's last value prior to the current calculation. The tail of the derived stream | if(var2 > LAST_VALUE, 1, 0) |
| SAMPLE_TIME | The time of the regular stream sample being calculated in epoch Milliseconds. | SAMPLE_TIME |
| SAMPLE_SDTIME | The start date time of the interval stream interval being calculated in epoch Milliseconds. | SAMPLE_SDTIME |
| SAMPLE_EDTIME | The end date time of the interval stream interval being calculated in epoch Milliseconds | SAMPLE_EDTIME |
Expression examples (Derivation will not occur if the expression returns a result data type that cannot be converted to the derived stream's data type):
- 1
- This is a derived stream with no stream variables. All interval values are set to one during derivation. This kind of derivation is convenient for testing. It is also known as a kicker stream because it could be evaluated every time derivation runs. To avoid running everytime, make the kicker an interval streams with a base cycle of Day to force precedent streams to calculate once a day.
- 2*(n+3)
- n^2
- left("test string", 4)
- 2-cos(n)
- if(n<n2, true, false)
- if (n > n2, "stream1 value has exceeded stream2 value", "stream1 value is equal to or less than stream2 value")
- dtFormatter(SAMPLE_TIME, "MM/dd/YYY", "US/Central")
When does derivation occur?
1. When a dependent stream has data uploaded.
2. When a dependent stream is modified within the studio.
3. Manually from within the studio. Right-click on a derived stream and choose Re-derive Stream
4. GroveStream's will also
periodically detect (usually every minute or two) when all dependent
stream intervals have arrived before doing a derivation. By default,
all dependents must have data arrive since the last derivation for
derivation to occur. Derivation will also occur if the advanced
setting Perform derivation when is set to Any dependent
values have arrived and at least one dependent value arrives since
the last derivation. Derivation of an interval may be performed
several times depending on the size of the interval and the size of
its dependent intervals. If new (not updated) dependent intervals
arrive and fall within the interval time range being derived, then the
derived interval will be calculated with the new interval values. The
algorithm for this is a bit complicated as an interval might be
derived from another derived variable which is derived from smaller
intervals. GroveStreams tracks the smallest dependent interval size
end datetime and uses that value within the algorithm to determine
interval derivation.
Example: A derived stream has one
day cycles but its dependents are one hour cycles. The dependent one
hour cycles arrive at different times. The derivation engine will
update the derived one day interval several times during a day -
whenever all dependent one hour intervals exist for an hour that
hasn't been used in a prior derivation.
A stream has
three internal datetimes: start, end and completed. Start is the start
datetime of the first interval in the store. End is the end datetime
of the last interval in the store. Completed datetimes are used for
derivation and are the earliest completed datetime of all dependent
streams. If a stream is not a derived stream then its end datetime
will be equal to its completed datetime.
Derivation
occurs for a stream when all of its dependent streams have completed
datetimes that are less than the derived streams' completed datetime
(unless Any dependent values have arrived is selected then only
one needs to be less). Once a derived interval's end datetime is less
than or equal to the streams' completed datetime the derived interval
enters a "closed" state. It will not be derived again and derivation
moves on to the next set of intervals in the cycle.
Although a derived interval in a "closed" state will not be re-derived
but it can be changed via the API or from within the GroveStreams user
interface just like any other stream. To force a derivation on
"closed" intervals, delete all interval data from a derived stream and
the derivation engine will recalculate it all. Not auto deriving
"closed" intervals avoids doing dependency tree look-ups every time an
interval value changes (as feeds are uploaded). GroveStreams is all
about performance and we feel this limitation is acceptable to keep
performance high.
Cycles
and Functions
A stream can be derived from other streams with
different cycle sizes. If the dependent stream's cycle is smaller it
will be rolled up to match the size of the derivation stream. The
smaller cycle must be able to rollup evenly into the derived interval
(1 second into 10 second is an even rollup - 7 second into 1 minute is
uneven). The cycle function will be used for the rollup method. If the
dependent stream's cycle is larger then the dependent interval with an
end datetime that falls within the derived interval will be used.
You can optionally leave the cycle blank (or set to automatic
in the user interface). When it is left blank, GroveStreams will
select a cycle for you and perform an on-the-fly rollup if needed.
Cycles and Functions are ignored for point stream dependents.
Expressions
Expressions are composed of
variables which are stream. If a variable is an interval stream then
the user can choose the interval offset for that variable. For
example, if an hourly interval with a time span of 2:00 pm to 3:00 pm
is being calculated a dependent variables' offset can be set to -1 and
that variables' data for time span 1:00 pm to 2:00 will be used in the
calculation. Offsets are useful for things like rolling averages.
Example of a Derived Stream Expression that calculates one second 3 pt
rolling averages from Component1.Stream1.
Variables:
| Variable |
Offset |
Type |
Stream/Characteristic |
Cycle |
Cycle Function |
| n |
0 |
Stream |
Component1.Stream1 |
Second |
- |
| n_minus_1 |
-1 |
Stream |
Component1.Stream1 | Second | - |
| n_minus_2 | -2 |
Stream | Component1.Stream1 | Second | - |
Expression:
(n + n_minus_1 + n_minus_2) / 3