Mathematical Operation Functions
Relational Operators
All data types can be compared by using relational operators and the result is returned as a BOOLEAN value.
Relationship operators are binary operators. Two compared data types must be of the same type or they must support implicit conversion.
Table 1 lists all relational operators supported by Flink SQL.
- 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.
Operator |
Result 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. |
Only data of the Boolean type can be used for calculation using logical operators. Implicit type conversion is not supported.
Arithmetic 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 Flink SQL.
Operator |
Result 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 type is associated with the operation data type. For example, if floating-point number is added to an integer, the result will be a floating-point number. |
A - B |
All numeric types |
A minus B. The result type is associated with the operation data type. |
A * B |
All numeric types |
Multiply A and B. The result type is associated with the operation data type. |
A / B |
All numeric types |
Divide A by B. The result is a number of the double type (double-precision number). |
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 |
Return the value of e raised to the power of a. |
CEIL(A) CEILING(A) |
All numeric types |
Return the smallest integer that is greater than or equal to a. For example: ceil(21.2) = 22. |
FLOOR(A) |
All numeric types |
Return 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 |
Convert the value of a from radians to degrees. |
RADIANS(A) |
All numeric types |
Convert the value of 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 |
Return the value of pi. |
Data of the string type is not allowed in arithmetic operations.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.