Help Center/ Log Tank Service/ User Guide/ Log Search and Analysis/ SQL Syntax/ Mathematical Functions
Updated on 2024-07-23 GMT+08:00
View PDF

Mathematical Functions

Description

Mathematical functions are one type of scalar functions. They support only numeric fields and can be used to round values, obtain absolute values, and perform modulo operations. For details, see Table 1.

In mathematical operations, if the operands involved in an expression are all integers, SQL uses integer operations. Otherwise, SQL switches to floating-point operations. You can force the switch by converting one of the operands to float type, and the runtime SQL extends the 32-bit floats in most expressions to 64-bit floats.

Syntax

SELECT ABS(fieldname1) AS fieldname1_abs

Mathematical Function Statements

Table 1 Mathematical function statements

Statement

Description

Example

ABS(expr)

Returns the absolute value.

SELECT ABS(fieldname1)

CEIL(expr)

Rounds up to the nearest integer.

SELECT CEIL(fieldname1)

FLOOR(expr)

Rounds down to the nearest integer.

SELECT FLOOR(fieldname1)

TRUNCATE(expr, digits)

Truncates expr to a specified digits. If the number is negative, many positions to the left of the decimal point are truncated. If not specified, the number is zero by default.

SELECT TRUNCATE(fieldname1, 2)

ROUND(expr, digits)

Rounds expr to a specified number of decimal places. The result is of the same type as that of expr. expr can be an integer or a floating point number, but digits must be an integer. If digits is not specified, the default value 0 is used. If digits is a negative number, the value of expr is rounded off. If expr is a non-numeric value, it will be converted to 0. If expr is an infinite number, it is converted to a number with a finite number of digits of the closest double type.

SELECT ROUND(fieldname1, 2)

x + y

Addition.

SELECT fieldname1 + fieldname2

x - y

Subtraction.

SELECT fieldname1 - fieldname2

x * y

Multiplication.

SELECT fieldname1 * fieldname2

x / y

Division.

SELECT fieldname1 / fieldname2

MOD(x, y)

Modulo.

SELECT MOD(fieldname1, fieldname2)

LN(expr)

Logarithm (base: e).

SELECT ln(expr)

LOG10(expr)

Logarithm (base 10).

SELECT LOG10(expr)

POWER(expr,power)

Power of expr.

SELECT POWER(expr ,2)

SQRT(expr)

Square root of expr.

SELECT SQRT(expr)

SIN(expr)

Sine.

SELECT SIN(expr)

COS(expr)

Cosine.

SELECT COS(expr)

TAN(expr)

Tangent.

SELECT TAN(expr)

COT(expr)

Cotangent.

SELECT COT(expr)

ASIN(expr)

Arcsine.

SELECT ASIN(expr)

ACOS(expr)

Arc cosine.

SELECT ACOS(expr)

ATAN(expr)

Inverse tangent.

SELECT ATAN(expr)

Examples

ACOS(expr)

Calculates the arccosine of a parameter value. y = arccosx, where x is within [-1,1].

  1. Example field

    x:0.5

  2. Query and analysis statement
    select  ACOS(x)
  3. Query and analysis result
    Table 2 Query and analysis result

    x

    EXPR$1

    0.5

    1.0471975511965979

ATAN(expr) function

Calculates the arctangent of a parameter value. y = arctanx, where x is R.

  1. Example field

    x:0.5

  2. Query and analysis statement
    select  ATAN(X)
  3. Query and analysis result
    Table 3 Query and analysis result

    x

    EXPR$1

    0.5

    1.0471975511965979

ATAN2(expr)

Returns the angle θ from rectangular coordinates (x, y) to polar coordinates (r, θ).

  1. Example field

    x:3; y:4

  2. Query and analysis statement
    SELECT x , y, ATAN2(x,y)
  3. Query and analysis result
    Table 4 Query and analysis result

    x

    y

    EXPR$0

    3

    4

    0.6435011087932844
Parent topic: SQL Syntax