GetElementsIf function

Syntax

GetElementsIf (
	ts          TimeSeries,
	expr        lvarchar,
	begin_stamp datetime year to fraction(5) default null,
	end_stamp   datetime year to fraction(5) default null) 
returns  TimeSeries (TSMatchingCount);

GetElementsIf (
	ts          TimeSeries,
	col         lvarchar,
	op          lvarchar,
	value       lvarchar,
	begin_stamp datetime year to fraction(5) default null,
	end_stamp   datetime year to fraction(5) default null) 
returns TimeSeries (TSMatchingCount);

GetElementsIf (
	ts          TimeSeries,
	col         lvarchar,
	op          lvarchar,
	value       decimal,
	begin_stamp datetime year to fraction(5) default null,
	end_stamp   datetime year to fraction(5) default null) 
returns TimeSeries (TSMatchingCount);

ts

The time series to count.

expr

An expression to filter elements by comparing element values to a number or string. You can combine multiple expressions with the AND or the OR operator and use parentheses to nest multiple expressions. Use the following arguments within an expression:

expr_col

The name of the column within a TimeSeries data type. The supported column data types are: SMALLINT, INT, BIGINT, INT8, REAL, FLOAT, DECIMAL, DATE, MONEY, INTERVAL, DATETIME, CHAR, NCHAR, VARCHAR, NVARCHAR, LVARCHAR, BOOLEAN and BSON. If the column is of type BSON, the expr_col must be the path to a field in one or more BSON documents in the BSON column. If the BSON top-level field name is the same as another column in the TimeSeries data type, you must precede the field name with the BSON column name and a dot: bson_column_name.bson_path. The BSON field is automatically cast to a FLOAT data type, however, you can explicitly cast a BSON field to an INTEGER, BIGINT, or LVARCHAR data type. For example, the following expression extracts values as INTEGER data types from a BSON field: 'engine.temp::integer > 100'

expr_value

The value that is used in the comparison. Can be either a number, a string, or NULL. When comparing a column of type BOOLEAN, INTERVAL or DATETIME, the expr_value must be a string that is surrounded by quotation marks. This string is cast to a appropriate type depending on the type of the expr_col column in the expression. Provide the string in the appropriate format. If the expr_col is a DATEMTIME column, the expr_value must be in DATETIME format: 'datetime_col < "2016-04-12" '. If the expr_col is an INTERVAL column, the expr_value must be in INTERVAL format, for example: 'interval_col > "2 12:00:00" '. If the expr_col is a BOOLEAN column, the expr_value must be in BOOLEAN format, for example: 'boolean_col = "t" '.

logical_operator

The AND or the OR operator.

begin_stamp (optional)

The begin point of the range. Can be NULL. By default, begin_stamp is the beginning of the time series.

end_stamp (optional)

The end point of the range. Can be NULL. By default, end_stamp is the end of the time series.

col

The name of the column within a TimeSeries data type. Can be prefixed with the words IS NULL OR. Must be surrounded by quotation marks. The supported column data types are: SMALLINT, INT, BIGINT, INT8, REAL, FLOAT, DECIMAL, DATE, MONEY, INTERVAL, DATETIME, CHAR, NCHAR, VARCHAR, NVARCHAR, LVARCHAR, BOOLEAN and BSON. If the column is of type BSON, the col must be the path to a field in one or more BSON documents in the BSON column. If the BSON top-level field name is the same as another column in the TimeSeries data type, you must precede the field name with the BSON column name and a dot: bson_column_name.bson_path. The BSON field is automatically cast to a FLOAT data type, however, you can explicitly cast a BSON field to an INTEGER, BIGINT, or LVARCHAR data type. For example, the following expression extracts values as INTEGER data types from a BSON field: 'engine.temp::integer > 100'

op

An operator. Can be <, <=, =, !=, >=, or >. Must be surrounded by quotation marks.

value

The value that is used in the comparison. Can be either a number, a string, or NULL. When comparing a column of type BOOLEAN, INTERVAL or DATETIME, the value must be a string that is surrounded by quotation marks. This string is cast to a appropriate type depending on the type of the col column in the expression. Provide the string in the appropriate format. If the col is a DATEMTIME column, the value must be in DATETIME format: 'datetime_col < "2016-04-12" '. If the col is an INTERVAL column, the value must be in INTERVAL format, for example: 'interval_col > "2 12:00:00" '. If the col is a BOOLEAN column, the value must be in BOOLEAN format, for example: 'boolean_col = "t" '.

Usage

The GetElementsIf function is an extension of the CountIf function. Use the GetElementsIf function to get more details than the GetMatchingIf function. GetElementsIf function provides more details by returning a timeseries, with the same type as the input timeseries, of the elements that match the expression. So, the user can see the actual data at the matching time points.

Returns

CREATE ROW TYPE TSMatchingCount (
        tstamp      datetime year to fraction(5),
        count       integer
);

The tstamp is the position in the timeseries where the start of the expression match occurs. The count is the number of consecutive elements that match the expression.

Note that when using GetElementsIf, it is not possible to return missing elements. The returning timeseries is irregular so, unlike a regular timeseries, it can not have empty slots. In this case use the function variant called GetNullElementsIf which will return an element with NULL values whenever a missing slot is encountered.