InfluxQL mathematical operators - Time Series Database - Alibaba Cloud Documentation Center

Mathematical operations are performed based on the standard order of operators. Parentheses take precedence over division and multiplication, and division and multiplication take precedence over addition and subtraction.

Mathematical operators

Addition

The following code provides examples on how to add by using a constant:

SELECT "A" + 5 FROM "add"

SELECT * FROM "add" WHERE "A" + 5 > 10

The following code provides examples on how to add by using two fields:

SELECT "A" + "B" FROM "add"

SELECT * FROM "add" WHERE "A" + "B" >= 10

Subtraction

The following code provides examples on how to subtract by using a constant.

SELECT 1 - "A" FROM "sub"

SELECT * FROM "sub" WHERE 1 - "A" <= 3

The following code provides examples on how to subtract by using two fields:

SELECT "A" - "B" FROM "sub"

SELECT * FROM "sub" WHERE "A" - "B" <= 1

Multiplication

The following code provides examples on how to multiply by using a constant:

SELECT 10 * "A" FROM "mult"

SELECT * FROM "mult" WHERE "A" * 10 >= 20

The following code provides examples on how to multiply by using two fields:

SELECT "A" * "B" * "C" FROM "mult"

SELECT * FROM "mult" WHERE "A" * "B" <= 80

Multiplication distributes across other operators.

SELECT 10 * ("A" + "B" + "C") FROM "mult"

SELECT 10 * ("A" - "B" - "C") FROM "mult"

SELECT 10 * ("A" + "B" - "C") FROM "mult"

Division

The following code provides examples on how to divide by using a constant:

SELECT 10 / "A" FROM "div"

SELECT * FROM "div" WHERE "A" / 10 <= 2

The following code provides examples on how to divide by using two fields:

SELECT "A" / "B" FROM "div"

SELECT * FROM "div" WHERE "A" / "B" >= 10

Division distributes across other operators.

SELECT 10 / ("A" + "B" + "C") FROM "div"

Modulo

The following code provides examples on how to perform modular arithmetic operations by using a constant:

SELECT "B" % 2 FROM "modulo"

SELECT "B" FROM "modulo" WHERE "B" % 2 = 0

The following code provides examples on how to perform modular arithmetic operations by using two fields:

SELECT "A" % "B" FROM "modulo"

SELECT "A" FROM "modulo" WHERE "A" % "B" = 0

Bitwise AND

You can use this operator to process fields or constants of the INTEGER or BOOLEAN data type. This operator cannot be used on floating-point numbers or strings, and you cannot specify integers and Boolean values together.

SELECT "A" & 255 FROM "bitfields"

SELECT "A" & "B" FROM "bitfields"

SELECT * FROM "data" WHERE "bitfield" & 15 > 0

SELECT "A" & "B" FROM "booleans"

SELECT ("A" ^ true) & "B" FROM "booleans"

Bitwise OR

SELECT "A" | 5 FROM "bitfields"

SELECT "A" | "B" FROM "bitfields"

SELECT * FROM "data" WHERE "bitfield" | 12 = 12

Bitwise Exclusive OR

SELECT "A" ^ 255 FROM "bitfields"

SELECT "A" ^ "B" FROM "bitfields"

SELECT * FROM "data" WHERE "bitfield" ^ 6 > 0

Common issues

Use mathematical operators and wildcards or regular expressions at the same time

Time Series Database (TSDB) for InfluxDB® supports the use of wildcards (*) or regular expressions in SELECT clauses. The queries in the following examples are invalid and the system returns errors:

The following code provides an example of a mathematical operation on a wildcard:

> SELECT * + 2 FROM "nope"
ERR: unsupported expression with wildcard: * + 2

The following code provides an example of a mathematical operation on a wildcard in a function:

> SELECT COUNT(*) / 2 FROM "nope"
ERR: unsupported expression with wildcard: count(*) / 2

The following code provides an example of a mathematical operation on a regular expression:

> SELECT /A/ + 2 FROM "nope"
ERR: error parsing query: found +, expected FROM at line 1, char 12

The following code provides an example of a mathematical operation on a regular expression in a function:

> SELECT COUNT(/A/) + 2 FROM "nope"
ERR: unsupported expression with regex field: count(/A/) + 2

Use mathematical operators and functions at the same time

Mathematical operators cannot be used in function calls. TSDB for InfluxDB® supports the use of functions only in the SELECT clauses.

The following code provides an example:

SELECT 10 * mean("value") FROM "cpu"

The statement is executed and a parser error occurs.

SELECT mean(10 * "value") FROM "cpu"

Note

InfluxQL supports subqueries. Subqueries provide similar functionality to using mathematical operators within function calls. For more information, see Data Exploration.

Unsupported operators

Equality and inequalities

If you use =, !=, <, >, <=, >=, or <> in a SELECT clause, an empty result is returned, regardless of the data type.

Logical operators

If you use !|, NAND, XOR, or NOR in a statement, a parser error occurs.

In addition, if you use AND or OR in a SELECT clause, no mathematical operation is performed and an empty result is returned. This is because AND and OR are tokens in InfluxQL. However, you can use bitwise operators &, |, and ^ to process BOOLEAN data.

Bitwise Not

InfluxQL does not provide the bitwise NOT operator. The results are calculated based on the width of your bitfield. InfluxQL does not know the width of your bitfield, so InfluxQL cannot provide an appropriate bitwise NOT operator.

For example, if the width of your bitfield is 8 bits, the integer 1 represents the bits 0000 0001. After a bitwise NOT operation is performed, the bits 1111 1110 are returned. The returned bits represent the integer 254.

However, if the width of your bitfield is 16 bits, the integer 1 specifies the bits 0000 0000 0000 0001. After a bitwise NOT operation is complete, the bits 1111 1111 1111 1110 are returned. The returned bits represent the integer 65534.

Solutions

To perform a bitwise NOT operation, use the ^ bitwise XOR operator and set the width to the number that indicates the value of all bits. The width of the bitfield that you specify must be the number that indicates that the value of all bits is 1.

The following code provides an example for 8-bit data:

SELECT "A" ^ 255 FROM "data"

The following code provides an example for 16-bit data:

SELECT "A" ^ 65535 FROM "data"

The following code provides an example for 32-bit data:

SELECT "A" ^ 4294967295 FROM "data"

In each case, you can use the following formula to calculate the constant that you need: (2 ** width) - 1.