Help Center> Cloud Stream Service> SQL Syntax Reference> Built-In Functions> Mathematical Operation Functions
View PDF

Mathematical Operation Functions

Relational Operators

All data types can be compared by using relational operators and the result is returned as a Boolean value.

Relational operators are binary operators. Types of the compared data must be the same or the types must support implicit conversion.

Table 1 lists all relational operators supported by Stream SQL.

Table 1 Relational operators

Operator

Returned Data Type

Description

A = B

BOOLEAN

If A equals B, TRUE is returned. Otherwise, FALSE is returned. This operator is used for value assignment.

A <> B

BOOLEAN

If A is not equal to B, TRUE is returned. Otherwise, FALSE is returned. If A or B is NULL, NULL is returned. This operator follows the standard SQL syntax.

A < B

BOOLEAN

If A is less than B, TRUE is returned. Otherwise, FALSE is returned. If A or B is NULL, NULL is returned.

A <= B

BOOLEAN

If A is less than or equal to B, TRUE is returned. Otherwise, FALSE is returned. If A or B is NULL, NULL is returned.

A > B

BOOLEAN

If A is greater than B, TRUE is returned. Otherwise, FALSE is returned. If A or B is NULL, NULL is returned.

A >= B

BOOLEAN

If A is greater than or equal to B, TRUE is returned. Otherwise, FALSE is returned. If A or B is NULL, NULL is returned.

A IS NULL

BOOLEAN

If A is NULL, TRUE is returned. Otherwise, FALSE is returned.

A IS NOT NULL

BOOLEAN

If A is not NULL, TRUE is returned. Otherwise, FALSE is returned.

A IS DISTINCT FROM B

BOOLEAN

If A is not equal to B, TRUE is returned. NULL indicates A equals B.

A IS NOT DISTINCT FROM B

BOOLEAN

If A is equal to B, TRUE is returned. NULL indicates A equals B.

A BETWEEN [ASYMMETRIC | SYMMETRIC] B AND C

BOOLEAN

If A is greater than or equal to B but less than or equal to C, TRUE is returned.

  • ASYMMETRIC: indicates that B and C are location-related.

    For example, "A BETWEEN ASYMMETRIC B AND C" is equivalent to "A BETWEEN B AND C".

  • SYMMETRIC: indicates that B and C are not location-related.

    For example, "A BETWEEN SYMMETRIC B AND C" is equivalent to "A BETWEEN B AND C) OR (A BETWEEN C AND B".

A NOT BETWEEN B AND C

BOOLEAN

If A is less than B or greater than C, TRUE is returned.

A LIKE B [ ESCAPE C ]

BOOLEAN

If A matches pattern B, TRUE is returned. The escape character C can be defined as required.

A NOT LIKE B [ ESCAPE C ]

BOOLEAN

If A does not match pattern B, TRUE is returned. The escape character C can be defined as required.

A SIMILAR TO B [ ESCAPE C ]

BOOLEAN

If A matches regular expression B, TRUE is returned. The escape character C can be defined as required.

A NOT SIMILAR TO B [ ESCAPE C ]

BOOLEAN

If A does not match regular expression B, TRUE is returned. The escape character C can be defined as required.

value IN (value [, value]* )

BOOLEAN

If the value is equal to any value in the list, TRUE is returned.

value NOT IN (value [, value]* )

BOOLEAN

If the value is not equal to any value in the list, TRUE is returned.

Precautions

  • Values of the double, real, and float types may be different in precision. The equal sign (=) is not recommended for comparing two values of the double type. You are advised to obtain the absolute value by subtracting these two values of the double type and determine whether they are the same based on the absolute value. If the absolute value is small enough, the two values of the double data type are regarded equal. For example: 
    abs(0.9999999999 - 1.0000000000) < 0.000000001 //The precision decimal places of 0.9999999999 and 1.0000000000 are 10, while the precision decimal place of 0.000000001 is 9. Therefore, 0.9999999999 can be regarded equal to 1.0000000000.
  • Comparison between data of the numeric type and character strings is allowed. During comparison using relational operators, including >, <, ≤, and ≥, data of the string type is converted to numeric type by default. No characters other than numeric characters are allowed.
  • Character strings can be compared using relational operators.

Logical Operators

Common logical operators are AND, OR, and NOT. Their priority order is NOT > AND > OR.

Table 2 lists the calculation rules. A and B indicate logical expressions.

Table 2 Logical operators

Operator

Returned Data Type

Description

A OR B

BOOLEAN

If A or B is TRUE, TRUE is returned. Three-valued logic is supported.

A AND B

BOOLEAN

If both A and B are TRUE, TRUE is returned. Three-valued logic is supported.

NOT A

BOOLEAN

If A is not TRUE, TRUE is returned. If A is UNKNOWN, UNKNOWN is returned.

A IS FALSE

BOOLEAN

If A is TRUE, TRUE is returned. If A is UNKNOWN, FALSE is returned.

A IS NOT FALSE

BOOLEAN

If A is not FALSE, TRUE is returned. If A is UNKNOWN, TRUE is returned.

A IS TRUE

BOOLEAN

If A is TRUE, TRUE is returned. If A is UNKNOWN, FALSE is returned.

A IS NOT TRUE

BOOLEAN

If A is not TRUE, TRUE is returned. If A is UNKNOWN, TRUE is returned.

A IS UNKNOWN

BOOLEAN

If A is UNKNOWN, TRUE is returned.

A IS NOT UNKNOWN

BOOLEAN

If A is not UNKNOWN, TRUE is returned.

Precautions

Only data of the Boolean type can be used for calculation using logical operators. Implicit type conversion is not supported.

Arithmetical Operators

Arithmetic operators include binary operators and unary operators, for all of which, the returned results are of the numeric type. Table 3 lists arithmetic operators supported by Stream SQL.

Table 3 Arithmetical operators

Operator

Returned Data Type

Description

+ numeric

All numeric types

Returns numbers.

- numeric

All numeric types

Returns negative numbers.

A + B

All numeric types

A plus B. The result data type depends on the operation data types. For example, if a floating-point number is added to an integer, a floating-point number will be returned.

A - B

All numeric types

A minus B. The result data type depends on the operation data types.

A * B

All numeric types

Multiplies A and B. The result data type depends on the operation data types.

A / B

All numeric types

Divides A by B. The result is a number of the double data type (double-precision).

POWER(A, B)

All numeric types

Returns the value of A raised to the power B.

ABS(numeric)

All numeric types

Returns the absolute value of a specified value.

MOD(A, B)

All numeric types

Returns the remainder (modulus) of A divided by B. A negative value is returned only when A is a negative value.

SQRT(A)

All numeric types

Returns the square root of A.

LN(A)

All numeric types

Returns the nature logarithm of A (base e).

LOG10(A)

All numeric types

Returns the base 10 logarithms of A.

EXP(A)

All numeric types

Returns the value of e raised to the power of A.

CEIL(A)

CEILING(A)

All numeric types

Returns the smallest integer that is greater than or equal to A. For example: ceil(21.2) = 22.

FLOOR(A)

All numeric types

Returns the largest integer that is less than or equal to A. For example: floor(21.2) = 21.

SIN(A)

All numeric types

Returns the sine value of A.

COS(A)

All numeric types

Returns the cosine value of A.

TAN(A)

All numeric types

Returns the tangent value of A.

COT(A)

All numeric types

Returns the cotangent value of A.

ASIN(A)

All numeric types

Returns the arc sine value of A.

ACOS(A)

All numeric types

Returns the arc cosine value of A.

ATAN(A)

All numeric types

Returns the arc tangent value of A.

DEGREES(A)

All numeric types

Converts the value A from radians to degrees.

RADIANS(A)

All numeric types

Converts the value A from degrees to radians.

SIGN(A)

All numeric types

Returns the sign of A. 1 is returned if A is positive. –1 is returned if A is negative. Otherwise, 0 is returned.

ROUND(A, d)

All numeric types

Round A to d places right to the decimal point. d is an int type. For example: round(21.263,2) = 21.26.

PI()

All numeric types

Returns the value of pi.

Precautions

Data of the string type is not allowed in arithmetic operations.

Parent topic: Built-In Functions