CountIf function

The CountIf function counts the number of elements that match the criteria of a simple arithmetic expression.

Syntax

CountIf (
	ts          TimeSeries,
	expr        lvarchar,
	begin_stamp datetime year to fraction(5) default null,
	end_stamp   datetime year to fraction(5) default null) 
returns integer

CountIf (
	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 integer

CountIf (
	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 integer

Figure 1: Syntax of expr

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

Use the CountIf function to determine how many elements fit criteria that are based on the values of the columns within the TimeSeries subtype. For example, you can apply criteria on multiple columns or determine whether a column has any null values. You can select a time range or query the entire time series.

Returns

An integer that represents the number of elements that fit the criteria.

Examples

Most examples are based on the following time series:

INSERT INTO CalendarTable(c_name, c_calendar)
  VALUES ('sm_15min',
          'startdate(2011-07-11 00:00:00.00000),
           pattstart(2011-07-11 00:00:00.00000),
           pattern({1 on,14 off}, minute)');
1 row(s) inserted.

EXECUTE PROCEDURE TSContainerCreate('sm0', 'tsspace0', 'sm_row', 0, 0);
Routine executed.

CREATE ROW TYPE sm_row
(
        t          datetime year to fraction(5),
        energy     smallint,
        ind        smallint,
        createdate datetime year to day
);
Row type created.

CREATE TABLE sm (
        meter_id varchar(255) primary key,
        readings TimeSeries(sm_row)
) IN tsspace;
Table created.

INSERT INTO sm VALUES ('met0', 'origin(2011-07-11 00:00:00.00000),
                       calendar(sm_15min),container(sm0),threshold(0),
                       regular,[(1,0),(2,1),(3,0),(4,2),(5,3),(6,9),
                       (7,3),(8,0),(9,0),(-123,0),(NULL,0),(NULL,0),
                       (400,3)]');
1 row(s) inserted.

Example: Count elements before the date value

The following statement counts the number of elements where the value of createdate column is before 2004-01-01:

SELECT CountIf(readings,'createdate < "2004-01-01"')
FROM sm;

Example: Count elements when a column is null

The following statement counts the number of elements where the energy column has a null value:

SELECT CountIf(readings,'energy IS NULL') 
   FROM sm;

(expression)

          2

1 row(s) retrieved.

Two elements contain null values for the energy column.

Example: Count elements that match a value in one of two columns

The following statement counts the number of elements where either the value of the energy column is equal to 1 or the value of the ind column is equal to 0:

SELECT CountIf(readings,'energy = 1 or ind = 0') 
   FROM sm;

(expression)

           5

1 row(s) retrieved.

Five elements meet the criteria.

Example: Count elements in a specific time range

The following statement counts the number of elements where the value of the energy column is greater than or equal to 5, from 2011-07-11 01:00:00.00000 until the end of the time series:

SELECT CountIf(readings,'energy >= 5','2011-07-11 01:00:00.00000'::datetime 
               year to fraction(5)) 
  FROM sm;

(expression)

           6

1 row(s) retrieved.

Six elements meet the criteria.

Example: Count elements greater than a value

The following statement counts the number of elements where the value of the energy column is greater than -128:

SELECT CountIf(readings,'energy > -128') 
   FROM sm;

(expression)

          11

1 row(s) retrieved.

The following statement is equivalent to the previous statement, except that the format uses separate arguments for the column name, the operator, and the comparison value instead of a single expression argument:

SELECT CountIf(readings,'energy', '>', -128) 
   FROM sm;

(expression)

          11

1 row(s) retrieved.

Example: Count elements in a BSON column

The following statements create a time series with a BSON column, create a virtual table on the time series, and insert data into the time series:

create row type if not exists iot_row(
   tstamp datetime year to fraction (5),
   value bson);
Row type created.

create table if not exists cf (pid int primary key, ts timeseries(iot_row));
Table created.

EXECUTE PROCEDURE TSContainerCreate("ctn1" ,
        "iot_dbs",
        "iot_row",256,512);
Routine executed.

execute procedure TSCreateVirtualTab('iot_vti', 'cf', 'elem_insert');
Routine executed.

insert into cf values(1,
'origin(2016-01-01 00:00:00.00000),calendar(ts_1min),container(ctn1),threshold(0), 
        regular');
1 row(s) inserted.

insert into iot_vti values(1,
        '2016-01-01 00:00:00.00000',
        '{"outside":{"temp":21,"pressure":1003.2},"engine":{"temp":140,
                     "pressure":2001}}'::json::bson);
1 row(s) inserted.

insert into iot_vti values(1,
        '2016-01-01 00:01:00.00000',
        '{"outside":{"temp":23,"pressure":1003.2},"engine":{"temp":145,
                     "pressure":2020}}'::json::bson);
1 row(s) inserted.

The following statement counts the number of elements in the engine.temp document in the BSON column value:

select countif(ts, 'value.engine.temp > 100')
   from cf;

(expression)

           2

1 row(s) retrieved.

The following statement is equivalent because the TimeSeries data type does not contain a column that is named employee:

select countif(ts, 'engine.temp > 100')
   from cf;

(expression)

           2

1 row(s) retrieved.