GroveStreams Help Center
Grammar



Sample Parameters
 		Date Functions
 		Control Functions
Joins
 		String Functions
 		Other Functions
Predicates
 		Numeric Functions
 		Constants
Operators
 		Aggregate Functions



Term Description Example
Query
SELECT Selects data from one or more tables. SELECT column1, column2, ... FROM table_name
DISTINCT Used to return only distinct (different) values. SELECT DISTINCT column1, column2, ... FROM table_name
FROM Selects data from one or more tables. Select cname, name From Stream
WHERE Used to filter records by extracting only those records that fulfill a specified condition. SELECT column1, column2, ... FROM table_name WHERE condition;
GROUP BY The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of components in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

SELECT aliases cannot be used in the GROUP BY statement because GROUP BY is executed before the SELECT statement. 		SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
HAVING A HAVING clause specifies that a SELECT statement must only return rows where aggregate values meet the specified conditions. HAVING and WHERE are often confused by beginners, but they serve different purposes. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
ORDER BY Used to sort the result-set in ascending or descending order. Ascending is the default.

Sort columns can be column names, column indexes, or expressions that resolve to a name or index. 		SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s), ... ASC|DESC;
LIMIT OFFSET Use the LIMIT and OFFSET clauses to retrieve a portion of rows returned. row_count determines the number of rows that will be returned. The optional OFFSET clause skips the offset rows before beginning to return the rows. SELECT column1, column2, ... FROM table_name LIMIT row_count OFFSET offset;



Sample Result Column Parameters
Range Range specifies a time range for samples or indicates to return the last number of samples. SELECT sample( range(sd=-1h, ed=streamEd)) FROM Stream
TimeFilterId The TimeFilterId can be assigned the ID of a GroveStreams TimeFilter and will filter returned samples based on the time filter. SELECT sample(timeFilterId='FallSeasonWeekends') FROM Stream
CycleId
Stat 		The CycleId can be assigned the ID of a GroveStreams cycle and will aggregate samples into intervals that match the cycle size. Stat, optional, indicates the statistic to use for aggregation. SELECT sample(cycleId='day', stat='max')) FROM Stream
GapFill The GapFill parameter is used to fill gaps in interval streams. SELECT sample(gapFill(head='nextVal', middle='avg', tail='previousVal')) FROM Stream



Joins
INNER JOIN Selects records that have matching values in both tables. SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
CROSS JOIN The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join. The ON clause with a CROSS JOIN is ignored. SELECT column_name(s) FROM table1 CROSS JOIN table2
LEFT OUTER JOIN The LEFT OUTER JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match. SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;



Compound Operators
EXCEPT The EXCEPT operator is used to combine two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement. This means EXCEPT returns only rows, which are not available in the second SELECT statement.

Just as with the UNION operator, the same rules apply when using the EXCEPT operator. 		SELECT column_name(s) FROM table1
EXCEPT
SELECT column_name(s) FROM table2
INTERSECT The INTERSECT operator is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement. This means INTERSECT returns only common rows returned by the two SELECT statements.

Just as with the UNION operator, the same rules apply when using the INTERSECT operator. 		SELECT column_name(s) FROM table1
INTERSECT
SELECT column_name(s) FROM table2
UNION The UNION operator is used to combine the result-set of two or more SELECT statements. Only distinct rows are returned.
  • Every SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in every SELECT statement must also be in the same order
 SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2
UNION ALL The UNION ALL operator is the same as UNION except duplicate rows are returned. SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2



Operators
+, - (unary) Makes operand positive or negative. SELECT +3, -4 FROM table
/, *, %, +, -, Divide, Multiply, Modulus, Plus, Subtract SELECT (3/4*2) + 1 - 5 FROM table
<, <=, >, >=,
=, ==,
!=, <> 		Less than: <
Less than or equal: <=
Greater than: >
Greater than or equal: >=
Equal to: =
Equal to: ==
Not equal: !=
Not equal: <>
 SELECT column FROM table WHERE x==10
&, |, <<, >>, ~ 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.
SELECT 5 < 7 FROM table
SELECT ~7 FROM table
String Operators The following operators work on Strings: <, <=, >, >=, =, ==, !=, <>, + SELECT 'hello' + ' ' + 'there!' FROM table



Predicates - An expression that evaluates to TRUE, FALSE, or UNKNOWN.
NOT,
AND, &&,
OR, ||		 NOT: Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.
AND: Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN.
&&: Same as AND
OR: Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise, returns UNKNOWN.
||: Same as OR
 SELECT column FROM table WHERE x==10 AND (y!=2 OR y!=5) AND NOT z!=8)
SELECT column FROM table WHERE x==10 && (y!=2 || y!=5) && NOT z!=8)
BETWEEN,
NOT BETWEEN		 The BETWEEN operator selects values within a given range. The values can be numbers or text. The BETWEEN operator is inclusive: begin and end values are included. SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
SELECT column_name(s) FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;
EXISTS,
NOT EXISTS		 The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records. SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
IN,
NOT IN		 The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions. SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
SELECT column_name(s) FROM table_name WHERE column_name NOT IN (value1, value2, ...);
IN,
NOT IN		 The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions. SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
SELECT column_name(s) FROM table_name WHERE column_name NOT IN (value1, value2, ...);
LIKE,
NOT LIKE		 The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator:
  • The star sign (*) represents zero, one, or multiple characters
  • The question mark sign (?) represents one, single character
 SELECT column_name(s) FROM table_name WHERE columnN LIKE pattern;
SELECT column_name(s) FROM table_name WHERE columnN NOT LIKE pattern;
SELECT fruit FROM table_name WHERE fruit LIKE '?pple';
GLOB,
NOT BLOB		 Same as the LIKE predicate. SELECT column_name(s) FROM table_name WHERE column GLOB pattern;
REGEXP,
NOT REGEXP		 REGEXP performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument. If the pattern finds a match in the expression, the function returns TRUE, else it returns FALSE. Based off of Java java.util.regex.pattern. SELECT column_name(s) FROM table_name WHERE column REGEXP pattern;
SELECT column_name(s) FROM table_name WHERE column NOT REGEXP pattern;
IS NOT NULL,
IS NULL		 It is not possible to test for NULL values with comparison operators, such as =, ==, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead. SELECT column_names FROM table_name WHERE column_name IS NULL;
SELECT column_name(s) FROM table_name WHERE column IS NOT NULL;



Date Functions
FORMATDATE FORMATDATE(timeAsEpochMillis [,pattern] [,timeZone])

Formats an epoch millis datetime into a readable string. This method maps to Java’s DateFormatter.

timeAsEpochMillis: A Long datatype that represents a date in epoch milliseconds.
pattern: Optional. The format pattern. The default pattern is "yyyy-MM-dd'T'HH:mm:ss".
timeZone: Optional. The Time Zone is used for conversion and is optional. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

List of Patterns
List of Time Zones 		SELECT FormatDate(time, pattern, timeZone) FROM table
SELECT FORMATDATE(now) → ‘2021-04-17T20:27:09’
SELECT FORMATDATE(startDate, 'dd/MM/yyyy, hh:mm:ss') → ‘17/04/2021, 08:28:35’
SELECT FORMATDATE(timeEd, 'dd/MM/yyyy, hh:mm:ss', 'US/Central') → ‘17/04/2021, 08:29:15’
SELECT FORMATDATE(now, 'MMM dd, yyyy (E), h:mm a', 'US/Central') → ‘Apr 17, 2021 (Sat), 8:41 PM’
TOEPOCHMILLIS TOEPOCHMILLIS(timeAsString[,pattern] [,timeZone])

Parses a String datetime into an epoch millisecond Long. This method maps to Java’s DateFormatter.

timeAsString: A String datatype that represents a date and time.
pattern: Optional. The format pattern used or parsing. The default pattern is "yyyy-MM-dd'T'HH:mm:ss".
timeZone: Optional. The Time Zone is used for conversion and is optional. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

List of Patterns
List of Time Zones 		SELECT TOEPOCHMILLIS('01/02/2021, 00:00:00', 'dd/MM/yyyy, HH:mm:ss', 'US/Central') → 1612159200000
SELECT TOEPOCHMILLIS('01/02/2021, 00:00:00', 'dd/MM/yyyy, HH:mm:ss') → 1612159200000
SELECT TOEPOCHMILLIS('2021-02-01T00:00:00') → 1612159200000
DATEADD DATEADD(epochMillis, spanType, spanAmount [,timeZone])

Adds time spans to an existing epoch millis date. Returns epoch millis date as Long data type.

epochMillis: Datetime to be added to. Epoch Milliseconds Long data type.
spanType: One of the following (case sensitive):
  1. s: Second
  2. m: Minute
  3. h: Hour
  4. d: Day
  5. w: Week
  6. M: Month
  7. Y: Year
spanAmount: The number of spanTypes to add or subtract.
timeZone: Optional. The Time Zone is used for calculating the new datetime. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

SELECT DATEADD(epochMillis, 'd', 2 'US/Central') → Adds 2 days to the epochMillis
SELECT DATEADD(epochMillis, 'd', -2 'US/Central') → Subtracts 2 days to the epochMillis
SELECT DATEADD(epochMillis, 'y', 1 'US/Central') → Adds 1 year to the epochMillis
DAYOFWEEK DAYOFWEEK(epochMillis [,timeZone])

The DAYOFWEEK() function returns the weekday index for a given date (a number from 1 to 7).
Note: 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday.

epochMillis: Datetime to find the Day of Week for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

SELECT DAYOFWEEK(now, 'US/Central') → 7
SELECT DAYOFWEEK(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 2
SELECT DAYOFWEEK(TOEPOCHMILLIS('2021-02-06T00:00:00')) → 7
DAY DAY(epochMillis [,timeZone])

The DAY() function returns the day number of the month. 1 is returned for the 1st day. Return data type is Long.

epochMillis: Datetime to find the day for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

SELECT DAY(now, 'US/Central') → 25
SELECT DAY(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 1
SELECT DAY(TOEPOCHMILLIS('2021-02-06T00:00:00')) → 6
MONTH MONTH(epochMillis [,timeZone])

The MONTH() function returns the month number of the datetime. 1 is returned for the 1st month of the year. Return data type is Long.

epochMillis: Datetime to find the month for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

SELECT MONTH(now, 'US/Central') → 4
SELECT MONTH(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 2
SELECT MONTH(TOEPOCHMILLIS('2021-06-06T00:00:00')) → 6
YEAR YEAR(epochMillis [,timeZone])

The YEAR() function returns the year number of the datetime. Return data type is Long.

epochMillis: Datetime to find the Day for. Epoch Milliseconds Long data type.
timeZone: Optional. The Time Zone is used for calculating time. The default is the user’s browser’s Time Zone which is usually the Operating System’s Time Zone.

SELECT YEAR(now, 'US/Central') → 4
SELECT YEAR(TOEPOCHMILLIS('2021-02-01T00:00:00'), 'US/Central') → 2
SELECT YEAR(TOEPOCHMILLIS('2021-06-06T00:00:00')) → 6
DATEDIFF DATEDIFF(datePart, startDate, endDate)

Returns, as a Long data type, the requested elapsed time amount. This calculation is based on milliseconds ellapsed and does not take into consideration items like daylight savings or leap years. The result will be the floor for fractional results.

Millisecond difference isn't supported below, but can be calculated by subtracting the two datetimes: endDate-startDate

datePart: The units in which DATEDIFF reports the difference between the startdate and enddate. One of the following (case sensitive):
  1. s: Second
  2. m: Minute
  3. h: Hour
  4. d: Day
startDate: Epoch milliseconds Long data type.
endDate: Epoch milliseconds Long data type.


 SELECT DATEDIFF('s', now, now + 1000) → 1
SELECT DATEDIFF('h', TOEPOCHMILLIS('2021-02-01T00:00:00'), TOEPOCHMILLIS('2021-02-01T05:30:00')) → 5
SELECT DATEDIFF('d', TOEPOCHMILLIS('2021-02-01T00:00:00'), TOEPOCHMILLIS('2021-02-02T05:00:00')) → 1



String Functions
INSTR INSTR(string1, string2)

Returns the position of the first occurrence of a string in another string. 1 being the 1st position. This function performs a case-insensitive search.

string1: The string to be searched.
string2: The string to search for in string1. If string2 is not found, this function returns 0 		SELECT INSTR('Apple Tree', 'Tree') → 7
SELECT INSTR('Apple Tree', 'apple') → 1
SELECT INSTR('Peach Tree', 'Apple')→ 0
LEFT LEFT(string1, count)

Returns the left part of a character string with the specified number of characters.

string1: The string to be trimmed.
count: The number of characters to extract. If the number exceeds the number of characters in string, it returns string 		SELECT LEFT('Grizzly bear', 7) → ‘Grizzly
SELECT LEFT('Grizzly bear', 100) → ‘Grizzly bear’
RIGHT RIGHT(string1, count)

Returns the right part of a character string with the specified number of characters.

string1: The string to be trimmed.
count: The number of characters to extract. If the number exceeds the number of characters in string, it returns string 		SELECT RIGHT('Grizzly bear', 7) → ‘Grizzly
SELECT RIGHT('Grizzly bear', 100) → ‘Grizzly bear’
LENGTH LENGTH(string1)

Return the number of characters (or length) of a specified string.

string1: The string to find the length of.
SELECT LENGTH(‘Rocket’) → 6
LOWER LOWER(string1)

Return a string with all characters converted to lowercase.

string1: The string to lower case.
SELECT LOWER(‘Rocket’) → ‘rocket’
UPPER UPPER(string1)

Return a string with all characters converted to uppercase.

string1: The string to upper case.
SELECT UPPER(‘Rocket’) → ‘ROCKET’
TRIM TRIM(string1 [,chars])

Removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.

string1: The string to trim.
chars: Optional. Defaults to space. The characters to remove.
SELECT TRIM(‘ CAT ‘) → ‘ CAT’
SELECT TRIM(‘??CAT?’, ‘?‘) → ‘CAT’
LTRIM LTRIM(string1 [,chars])

Remove spaces or other specified characters in a set from the left end of a string.

string1: The string to trim.
chars: Optional. Defaults to space. The characters to remove.
SELECT LTRIM(‘ CAT ‘) → ‘CAT ‘
SELECT LTRIM(‘??CAT?’, ‘?‘) → ‘CAT?’
RTRIM RTRIM(string1 [,chars])

Remove all spaces or specified characters in a set from the right end of a string.

string1: The string to trim.
chars: Optional. Defaults to space. The characters to remove.
SELECT RTRIM(‘ CAT ‘) → ‘ CAT’
SELECT RTRIM(‘??CAT?’, ‘?‘) → ‘??CAT’
STARTSWITH STARTSWITH(string1, chars)

Used for checking prefix of a String. It returns a boolean value true or false based on whether the given string begins with the specified letter or word. Case insensitive.

string1: The string to test.
chars: The characters to test for at the start of the string.
SELECT STARTSWITH(‘blue sky‘, ‘blue’) → true
SELECT STARTSWITH(‘blue sky‘, ‘red’) → false
REPLACE REPLACE(string1, substring, replacement)

Replace all occurrences of a substring by another substring in a string.

string1: The string to test.
substring: The characters to be replaced.
replacement: The characters to be inserted.
SELECT REPLACE(‘Up the Hill‘, ‘the’, ‘a’) → ‘Up a Hill’’



Numeric Functions
ROUND ROUND(number [,amount])

Returns a numeric value, rounded to the specified length or precision. Rounds half up. The function returns the same data type as the numeric data type of the number passed in.

number: The number to be rounded.
amount: Optional. The precision to which the number is to be rounded 		SELECT ROUND(5.1) → 5
SELECT ROUND(5.9) → 6
SELECT ROUND(5.5) → 6
SELECT ROUND(5.123456, 3) → 5.123
SELECT ROUND(5.123456, 10) → 5.123456
FLOOR FLOOR(number)

Returns the largest integer less than or equal to the specified numeric expression. The function returns the same data type as the numeric data type of the argument.

number: The number to calculate the floor for.
SELECT FLOOR(5.7) → 5
CEIL CEIL(number)

Return the smallest integer value that is greater than or equal to a number. The function returns the same data type as the numeric data type of the argument.

number: The number to calculate the ceiling for.
SELECT CEIL(5.1) → 6
TRUNC TRUNC(number [,amount])

The TRUNC(n1,n2) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point. The function returns the same data type as the numeric data type of the argument.

number: The number to truncate.
amount: Optional. The number of decimal places to truncate.
SELECT TRUNC(5.1) → 5
SELECT TRUNC(5.7) → 5
SELECT TRUNC(5.68,1) → 5.6
SELECT TRUNC(55.68,-1) → 50
PI PI()

The double value that is closer than any other to pi, the ratio of the circumference of a circle to its diameter. Utilizes the Java Math.PI constant. 		SELECT PI() → 3.141592653589793
RADIANS RADIANS()

Converts a number from degrees into radians. 		SELECT RADIANS(45) → 0.7853981633974483
RANDOM RANDOM()

Returns a double value with a positive sign, greater than or equal to 0.0 and less than 1.0. Returned values are chosen pseudorandomly with (approximately) uniform distribution from that range. Utilizes Java Random() function. 		SELECT RANDOM() → 0.48882270224969493
SIGN SIGN(number)

Returns the signum function of the given numeric expression as a Long. The return value is -1 if the given numeric expression is negative; 0 if the given numeric expression is zero; and 1 if the given numeric expression is positive.

number: The number to signum. 		SELECT SIGN(-10) → -1
SELECT SIGN(5) → 1
SELECT SIGN(0) → 0
ABS ABS(number)

Return the absolute value of a number. The return type will be the type of the argument.

number: The number to process. 		SELECT ABS(-10) → 10
SELECT ABS(0) → 0
SELECT ABS(99) → 99
SQRT SQRT(number)

Returns the square root of the specified value. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. 		SELECT SQRT(2) → 1.4142135623730951
EXP EXP(number)

Returns the exponential value of the specified numeric expression.

The constant e (2.718281...), is the base of natural logarithms.

The exponent of a number is the constant e raised to the power of the number. For example EXP(1.0) = e^1.0 = 2.71828182845905 and EXP(10) = e^10 = 22026.4657948067.

The return type will be double or big decimal if the argument is big decimal.

number: The number to process. 		SELECT EXP(2) → 7.38905609893065
POWER POWER(number, power)

Returns the value of the specified expression to the specified power. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. power: The power to which to raise the number. 		SELECT POWER(2.5, 4) → 39.0625
LN LN(number)

Returns the natural logarithm (base e) of a double, long, or big decimal value. Special cases:
  1. If the argument is NaN or less than zero, then the result is NaN.
  2. If the argument is positive infinity, then the result is positive infinity.
  3. If the argument is positive zero or negative zero, then the result is negative infinity.
Returns a Double type.

number: The number to process. 		SELECT LN(10) → 2.302585092994046
LOG LOG(number [,base])

Returns the logarithm of the first argument computed at the base of the second argument. Returns a Double type.

number: The number to process. base: Optional. Defaults to base 10. 		SELECT LOG(3, 2) → 1.5849625007211563
SELECT LOG(3) → 0.47712125471966244
DEGREES DEGREES(number)

Converts a number from radians into degrees. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. 		SELECT DEGREES(.33) → 18.90760723931717
SIN SIN(number)

Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. 		SELECT SIN(45) → 0.8509035245341184
SINH SINH(number)

Returns the hyperbolic sine of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. 		SELECT SINH(.5) → 0.5210953054937474
ASIN ASIN(number)

Returns the arcsine of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. 		SELECT ASIN(0.5) →0.5235987755982989
ASINH ASINH(number)

Returns the hyperbolic arcsine of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal.

number: The number to process. 		SELECT ASINH(0.5) → 0.48121182505960347
COS COS(number)

A mathematical function that returns the trigonometric cosine of the specified angle - measured in radians - in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. 		SELECT COS(90) → -0.4480736161291702
COSH COSH(number)

A mathematical function that returns the hyperbolic cosine of the specified angle in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. 		SELECT COSH(0.5) → 1.1276259652063807
ACOS ACOS(number)

A mathematical function that returns the arccosine of the specified angle in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. 		SELECT ACOS(.5) → 1.0471975511
ACOSH ACOSH(number)

A mathematical function that returns the hyperbolic arccosine of the specified angle in the specified expression. The return type will be double or big decimal if the argument is big decimal

number: The number to process. 		SELECT ACOSH(10) → 2.993222846126381
TAN TAN(number)

Returns the trigonometric tangent of the specified angle, in radians, and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. 		SELECT TAN(33) →-75.31301480008509
TANH TANH(number)

Returns the hyperbolic tangent of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. 		SELECT TANH(33) →-75.31301480008509
ATAN ATAN(number)

Returns the arctangent of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. 		SELECT ATAN(33) → 1.54050257
ATANH ATANH(number)

Returns the hyperbolic arctangent of the specified angle and in an approximate numeric expression. The return type will be double or big decimal if the argument is big decimal. .

number: The number to process. 		SELECT ATANH(0.5) → 0.5493061443340548



Aggregate Functions
COUNT COUNT([DISTINCT] expression)

The COUNT function is an aggregate function that returns the number of rows returned by a query as a Long data type.

The result of the COUNT function depends on the argument that you pass to it:
  1. By default, the COUNT function uses the ALL keyword whether you specify it or not. The ALL keyword means that all items in the group are considered including the duplicate values. For example, if you have a group (1, 2, 3, 3, 4, 4) and apply the COUNT function, the result is 6
  2. If you specify the DISTINCT keyword explicitly, only unique non-null values are considered. The COUNT function returns 4 if you apply it to the group (1,2,3,3,4,4).
The COUNT(*) function returns the number of rows in a table including the rows that contain the NULL values.

DISTINCT: Optional. Indicates whether to include duplicate values. .
expression: *, column name or an expression.
SELECT COUNT(*) FROM Stream → 10233
SELECT COUNT(name) FROM STREAM → 10233
SELECT cname, COUNT(name) AS streamCount FROM Stream WHERE GROUP BY cname, COUNT(name) ORDER BY cname, streamCount → Stream Count Per Component
SUM SUM([DISTINCT] expression)

The SUM function is an aggregate function that returns the sum of all non-null.

The SUM function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the SUM function performs the calculation.

If you want to calculate the sum of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the SUM function is NULL if and only if all input values are NULL. DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name or an expression.
SELECT SUM(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 10233
SELECT cname, name, SUM(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream Totals Per Component Stream for the last 12 hours
TOTAL TOTAL([DISTINCT] expression)

The same as the SUM function except the result of the TOTAL function is zero, not NULL, if all input values are NULL.

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name, or an expression.
SELECT TOTAL(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 300
SELECT cname, name, TOTAL(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream Totals Per Component Stream for the last 12 hours
MIN MIN([DISTINCT] expression)

The MIN function is an aggregate function that returns the mimimum of all non-null rows.

The MIN function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the MIN function performs the comparison.

If you want to calculate the minimum of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be the type of the input. The logic uses the same logic as comparison operators.

The result of the MIN function is NULL if and only if all input values are NULL. 		SELECT MIN(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 0
SELECT cname, name, MIN(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream mimimums per Component Stream for the last 12 hours
MAX MAX([DISTINCT] expression)

The MAX function is an aggregate function that returns the maximum of all non-null rows.

The MAX function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the MAX function performs the comparison.

If you want to calculate the maximum of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be the type of the input. The logic uses the same logic as comparison operators.

The result of the MAX function is NULL if and only if all input values are NULL. 		SELECT MAX(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 24
SELECT cname, name, MAX(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream maximums per Component Stream for the last 12 hours
AVG AVG([DISTINCT] expression)

The AVG function is an aggregate function that returns the average of all non-null rows.

The AVG function uses ALL clause by default. It means that all the input values, whether duplicate or not, are considered when the AVG function performs the comparison.

If you want to calculate the average of unique values, you have to specify the DISTINCT clause explicitly in the expression.

The result will be the type of the input. The logic uses the same logic as comparison operators.

The result of the AVG function is NULL if and only if all input values are NULL. 		SELECT AVG(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 12.5
SELECT cname, name, AVG(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream average per Component Stream for the last 12 hours
VAR VAR([DISTINCT] expression)

Returns the statistical variance of all values in the specified expression when the group of numbers being evaluated are only a partial sampling of the whole population.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the VAR function is NULL if and only if all input values are NULL or the set has only one value. 		SELECT VAR(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 7.07
SELECT cname, name, VAR(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream average per Component Stream for the last 12 hours
VARP VARP([DISTINCT] expression)

Returns the statistical variance for the population for all values in the specified expression when the group of numbers being evaluated is complete - it's the entire population of values.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the VARP function is NULL if and only if all input values are NULL or the set has only one value. 		SELECT VARP(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 7.07
SELECT cname, name, VARP(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream average per Component Stream for the last 12 hours
STDDEV STDDEV([DISTINCT] expression)

STDDEV is used when the group of numbers being evaluated are only a partial sampling of the whole population. The denominator for dividing the sum of squared deviations is N-1, where N is the number of observations ( a count of items in the data set ). Technically, subtracting the 1 is referred to as "non-biased.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the STDDEV function is NULL if and only if all input values are NULL or the set has only one value. 		SELECT STDDEV(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 7.07
SELECT cname, name, STDDEV(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream average per Component Stream for the last 12 hours
STDDEVP STDDEVP([DISTINCT] expression)

STDDEVP is used when the group of numbers being evaluated is complete - it's the entire population of values. In this case, the 1 is NOT subtracted and the denominator for dividing the sum of squared deviations is simply N itself, the number of observations ( a count of items in the data set ). Technically, this is referred to as "biased." Remembering that the P in STDEVP stands for "population" may be helpful. Since the data set is not a mere sample, but constituted of ALL the actual values, this standard deviation function can return a more precise result.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the STDDEVP function is NULL if and only if all input values are NULL or the set has only one value. 		SELECT STDDEVP(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 7.07
SELECT cname, name, STDDEVP(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream average per Component Stream for the last 12 hours
PERCENTILEINC PERCENTILEINC([DISTINCT] expression , percent)

Returns the k-th percentile of values in a range, where k is in the range 0..1, inclusive. You can use this function to establish a threshold of acceptance. For example, you can decide to examine candidates who score above the 90th percentile.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the PERCENTILEINC function is NULL if and only if all input values are NULL.

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name or an expression.
percentile: The percentile numeric value greater than or equal to zero and less than or equal to one.
SELECT PERCENTILEINC(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 7.07
SELECT cname, name, PERCENTILEINC(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream average per Component Stream for the last 12 hours
PERCENTILEEXC PERCENTILEEXC([DISTINCT] expression , percent)

The PERCENTILE.EXC function returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive.

The result will be a Double type if the inputs are Long or Double. It will be BigDecimal if the inputs are BigDecimal.

The result of the PERCENTILEEXC function is NULL if and only if all input values are NULL.

DISTINCT: Optional. Indicates whether to include duplicate values.
expression: column name or an expression.
percentile: The percentile numeric value greater than zero and less than one.
SELECT PERCENTILEEXC(sample) FROM Stream WHERE cid='c1' AND id='rdm_dbl' → 7.07
SELECT cname, name, PERCENTILEEXC(sample(Range(sd=-12h))) FROM Stream WHERE id='rdm_lng' GROUP BY cname, name ORDER BY cname, name → Stream average per Component Stream for the last 12 hours



Control Functions
IIF IIF(condition, value_if_true, value_if_false)

It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, a nd the false_value is returned if the Boolean expression is false.

Returns the data type of the value_if_true or value_if_false.

condition: The number to be rounded.
value_if_true: Optional. The precision to which the number is to be rounded
value_if_false: Optional. The precision to which the number is to be rounded 		IIF(condition, value_if_true, value_if_false)
SELECT IIF( kWh > 50, cost*0.5, cost)
SELECT IIF( 100 > 50,'High', 'Low') → 'High'



Other Functions
CAST CAST(value AS typename([precision][,scale]))

The CAST() function converts a value (of any type) into a specified data type. Also look at the CONVERT() function.

Cast Type Names (case insensitive):
  1. STRING
  2. BOOLEAN
  3. LONG
  4. DOUBLE
  5. BIGDECIMAL
value: The number to be rounded.
typename: The type to cast to (see list above).
precision: Optional. Precision of BigDecimal casts.
scale: Optional. Scale of BigDecimal casts.
SELECT CAST(25.65 AS LONG) → 25
SELECT CAST('25.65' AS LONG) → 25
SELECT CAST('25.65' AS DOUBLE) → 25.65
SELECT CAST('25.6534' AS BIGDECIMAL) → 25.6534
SELECT CAST('25.6534' AS BIGDECIMAL(10,2)) → 25.65
SELECT CAST(25.65 AS STRING) + 'gallons' → '25.65 gallons'
SELECT CAST(1 AS BOOLEAN) → true
SELECT CAST(2 AS BOOLEAN) → true
SELECT CAST(0 AS BOOLEAN) → false
SELECT CAST('off' AS BOOLEAN) → false
SELECT CAST('no' AS BOOLEAN) → false
SELECT CAST('abc' AS BOOLEAN) → true



Literals - Constants
NOW The current datetime in Long epoch milliseconds. The NOW literal is set at the start of the SQL process and used by all steps of the query so that the NOW time is the same for all operations. SELECT NOW → 1618885675191
SELECT FormatDate(NOW) → '2021-04-19T21:28:37'
CURRENT_TIMESTAMP Same as NOW. See above. SELECT CURRENT_TIMESTAMP → 1618885675191
SELECT FormatDate(CURRENT_TIMESTAMP) → '2021-04-19T21:28:37'
CURRENT_DATE Returns Now formatted as a string using the format "yyyy-MM-dd". SELECT CURRENT_DATE → '2021-04-19'
CURRENT_TIME Returns Now formatted as a string using the format "HH:mm:ss". SELECT CURRENT_TIME → '21:28:37'


Sample Result Column Parameters


The Sample result column, within a Select statement, allows for optional parameters to filter and aggregate the list of samples returned. Each parameter may contain other parameters such as the Range () parameter.

Sample Range Parameter:

Example: Select Sample( Range(sd=-1d, ed=now) ) From Stream

The range parameter is used to filter samples by time. A Range has a start datetime (inclusive) and end datetime (exclusive) and is represented by sd and ed parameters.

There are four ways to specify a Range:
  1. 1. No range specified: The stream's first and last sample's datetime are used. All samples will be returned.
  2. 2. sd and ed parameters are used
  3. 3. currentCycle or previousCycle are used
  4. 4. last is used and returns the last number of samples from ed (exclusive)
sd and ed can be set by using the following:
  • ISO8601 formatted string → sd='2021-03-01T00:00:00-05:00
  • Epoch Millis Long number → sd=1617253200000
  • now: The current time → ed=now
  • streamSd: The stream's earliest sample's datetime → sd=streamSd
  • streamEd: The stream's latest sample's datetime → ed=streamEd
  • currentCycle: The ID of a cycle used. The current datetime will be used to determine the cycle boundaries for sd and ed → currentCycle='day'
  • previousCycle: The ID of a cycle to be used. The current datetime will be used to determine the current cycle boundaries and then the previous cycle will be used for sd and ed → previousCycle='day'
  • if sd is missing, it is assigned to streamSd
  • if ed is missing, it is assigned to streamEd
  • if sd is used with a modifier, the modifier is applied to the ed's datetime.
Case-sensitive datetime Modifiers can be used to add or subtract time spans while setting the sd or ed parameters:
  1. s: Second
  2. m: Minute
  3. h: Hour
  4. d: Day
  5. w: Week
  6. M: Month
  7. Y: Year

Range Examples:

SELECT Sample FROM Stream Return all samples
SELECT Sample( Range(sd='2021-03-01T00:00:00-05:00', ed='2021-04-01T00:00:00-05:00') ) FROM Stream Return fixed range using ISO8601
SELECT Sample( Range(sd=1614578400000, ed=1617253200000) ) FROM Stream Return fixed range using epoch millis
SELECT Sample( Range(sd=-1d, ed=now) ) FROM Stream Return last day from current datetime
SELECT Sample( Range(sd=now, ed=now+1y) ) FROM Stream Return from now to one year into the future
SELECT Sample( Range(sd=-1d, ed=streamEd) ) FROM Stream Return last day from stream's last sample datetime
SELECT Sample( Range(sd=-1hr ed=streamEd-1hr) ) FROM Stream Return last hour from stream's last sample datetime
SELECT Sample( Range(currentCycle='month') ) FROM Stream Return current month
SELECT Sample( Range(previousCycle='month') ) FROM Stream Return previous month
SELECT Sample( Range(last=100, ed=streamEd) ) FROM Stream Return the last 100 samples
SELECT Sample( Range(last=100, ed=now-1w) ) FROM Stream Return the last 100 samples from one week ago


Sample TimeFilterId Parameter:

Example: Select Sample( TimeFilterId='weekdayOnly' ) From Stream

The TimeFilterId property will apply the GroveStreams Time Filter, with the assigned ID, used to filter samples based on the Time Filter definition and sample times.

Time filtering will be applied to intervals after they are rolled-up (aggregated) when the CycleId parameter is present.

TimeFilter Examples:

SELECT Sample(TimeFilterId='weekends') FROM Stream Return all samples with Saturday or Sunday times.
SELECT Sample(TimeFilterId='weekends', Range(sd=-4w)) FROM Stream TimeFilterId with a Range. Return all samples with Saturday or Sunday times over the last 4 weeks.


Sample CycleId and Stat Parameters:

Example: Select Sample( cycleId='day', stat='max' ) as maximum, FormatDate(Sample( cycleId='day', stat='MAXOCCURRENCE' )) as 'maximum time' From Stream

The CycleId is the ID of a GroveStreams cycle defined in your Organization. Samples returned will be aggregated into Intervals to match the cycle using the default rollup method in the stream definition or by using the statistic passed in.

Most of the statistics below are calculated for each cycle in a stream's rollup calendar as samples arrive an are saved. The timezone of the cycle or the component is used to determine each cycle range. The query engine will identify if the requested stat has been precalculated and return that result quickly. The query engine will calculate cycle statistics if they have not already been calculated and saved. Configure stream rollup calendars to improve the retrieval of interval statistics.

Stat options (case insensitive):
  1. FIRST
  2. LAST
  3. MIN
  4. MAX
  5. AVG
  6. SUM
  7. MINOCCURRENCE - Epoch Millis datetime of the minimum
  8. MAXOCCURRENCE - Epoch Millis datetime of the maximum
  9. GAPCOUNT
  10. NONGAPCOUNT
  11. INTVLCOUNT
  12. MILLISECCOUNT
  13. NONGAPMILLISECCOUNT
  14. STDDEVP
  15. STDDEVA
  16. SAMPLECOUNT
  17. SUMSIN
  18. SUMCOS

CycleId and Stat Examples:

SELECT Sample(cycleId='month') FROM Stream Return all samples aggregated into month intervals using the default stream rollup method
SELECT Sample(cycleId='month', stat='sum') FROM Stream Return all samples aggregated into month intervals. Samples are aggregated using SUM.
SELECT Sample(cycleId='month', stat='sum', Range(currentCycle='year')) FROM Stream Return 12 month intervals for the current year. Samples are aggregated into each month using SUM.
Select Sample( cycleId='day', stat='max' ) as maximum, FormatDate(Sample( cycleId='day', stat='MAXOCCURRENCE' )) as 'maximum time' From Stream Return 12 month intervals each with the maximum sample value for each month and the datetime it occurred.


Sample GapFill Parameter:

Example: Select Sample( cycleId='day', GapFill(head=0 , middle='avg', tail='previousval'), Range(currentCycle='year') ) from Stream Where cid='mainSubstation' and id='kw'

The GapFill parameter allows gaps, NULL values, to be replaced for interval streams. GapFilling takes place after all other Sample parameter logic has been applied and prior to other SQL logic such as WHERE filtering and aggregation functions. GapFilling respects the TimeFilter parameter when it is included. TimeFiltered intervals will remain as gaps (NULLS) during gap filling.

Gap filling will take place after intervals are rolled-up (aggregated) when the CycleId parameter is present.

GapFilling will only use values within the time range selected for calculating gap replacement values. For example, it will not attempt to find a 'previousVal' from the store prior to the time range being processed by the SQL statement.

GapFill options (case insensitive):

HEAD : How to fill gaps at the start of the request time range :
  1. NEXTVAL: Use the first non-gap value encountered after gap sequence. Must be in single quotes: head='NEXTVAL'
  2. Value: A value to assign head gaps. Numbers (not in quotes), Strings (in quotes), Boolean (not in quotes)
MIDDLE : How to fill gaps at the middle of the request time range:
  1. PREVIOUSVAL: Use the non-gap value encountered prior to the gap sequence. Must be in single quotes: head='PREVIOUSVAL'
  2. NEXTVAL: Use the first non-gap value encountered after gap sequence. Must be in single quotes: head='NEXTVAL'
  3. MIN: Fill with minimum of the PREVIOUSVAL and NEXTVAL. Must be in single quotes: middle='MIN'
  4. MAX: Fill with maximum of the PREVIOUSVAL and NEXTVAL. Must be in single quotes: middle='MAX'
  5. AVG: Fill with average of the PREVIOUSVAL and NEXTVAL. Must be in single quotes: middle='AVG'
  6. Value: A value to assign middle gaps. Numbers (not in quotes), Strings (in quotes), Boolean (not in quotes)
TAIL : How to fill gaps at the end of the request time range:
  1. PREVIOUSVAL: Use the non-gap value encountered prior to the gap sequence. Must be in single quotes: tail='PREVIOUSVAL'
  2. Value: A value to assign tail gaps. Numbers (not in quotes), Strings (in quotes), Boolean (not in quotes)

Process order is HEAD, TAIL, MIDDLE If all three options are used. Use head=value to fill an entire result set with a value if a requested result is all gaps.

GapFill Examples:

SELECT Sample( GapFill(middle=0.0) ) FROM Stream WHERE id='xx' Return all interval samples with gaps set to zero.
SELECT Sample( GapFill(middle='off') ) FROM Stream WHERE id='xx' Return all interval samples with gaps set to the string 'off'.
SELECT Sample( cycleId='hour', stat='sum', timeFilterId='weekendAfternoon', GapFill(head='nextval', middle='avg', tail=0), Range(currentCycle='year) ) FROM Stream WHERE id='xx' Return all hourly interval samples with weekend afternoon hour gaps filled for the current year.