GetMatchingIf function

The GetMatchingIf function returns the timeseries that describes the locations of the elements that match the criteria of a simple arithmetic expression.

Syntax

GetMatchingIf (
	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);

GetMatchingIf (
	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);

GetMatchingIf (
	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 GetMatchingIf function is an extension of the CountIf function that supplies more details about the actual rows that match the expression. It returns a timeseries that describes the locations of the elements that match the expression.

Returns

GetMatchingIf function returns a timeseries that describes the locations of the elements that match the expression. The returned timeseries has a fixed row type that indicates the number of sequential elements that match the expression. This row type is created during the initialisation of the TimeSeries data type. It has this structure:

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.

Example

CREATE ROW TYPE sm_row
(
	tstamp	DATETIME YEAR TO FRACTION(5),
	energy	SMALLINT,
	temp_c	SMALLINT
);

CREATE TABLE sm
(
	meter_id	VARCHAR(255) PRIMARY KEY,
	readings	TimeSeries (sm_row)
);

INSERT INTO sm VALUES ('met1',
	'origin(2017-09-11 00:00:00.00000),calendar(ts_15min),
	regular,[(1,2),(2,1),(3,0),(4,-1),(5,0),(6,1),(7,1),(8,0),(9,1),
	(-123,1),NULL, NULL,(NULL,2),(NULL,1),(400,1)]');
);

A CountIf for when temp_c dropped to freezing would only give a basic count of 4. Using the GetMatchingIf function shows that it was during two distinct periods when this happened.

SELECT r.* FROM Table (Transpose ((
	SELECT GetMatchingIf(readings,'temp_c <= 0')
	FROM sm WHERE meter_id = 'met1'
))) AS t(r)

tstamp                          count
2017-09-11 00:30:00.00000           3
2017-09-11 01:45:00.00000           1

There is a sequence of 3 freezing temperature readings from 00:30 onwards, and then a single reading at 01:45.