Data Lake Compute
- Getting Started
- Operation Guide
- Console Operation Introduction
- Data Development and Exploration
- Data Exploration
- Data Query Task
- Resource Management
- Engine Management
- SuperSQL Engine
- Standard Engine
- Resource Group
- Private Connection
- Storage Configuration
- Ops Management
- Insight Management
- System Management
- User and Permission Management
- Monitoring and Alarms
- Development Guide
- System Restraints
- Client Access
- JDBC Access
- Practical Tutorial
- SQL Statement
- SuperSQL Statement
- Unified Statement
- DDL Statement
- ALTER TABLE
- DML Statement
- DQL Statement
- Iceberg Table Statement
- API Documentation
- Making API Requests
- Data Table APIs
- Task APIs
- Metadata APIs
- Service Configuration APIs
- Permission Management APIs
- Data Source Connection APIs
- Data Optimization APIs
- Data Engine APIs
- General Reference
- Operation Guide on Connecting Third-Party Software to DLC
DocumentationData Lake ComputeSQL StatementSuperSQL StatementFunctionsUnified FunctionsMathematical Functions
Mathematical Functions
Last updated: 2024-08-07 17:32:33
ABS
Function statement:
ABS(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the absolute value of expr.
Return type: Same as <expr>.
Example:
> SELECT abs(-1);1
ACOS
Function statement:
ACOS(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the arc cosine of expr.
Return type: double
Example:
> SELECT acos(1);0.0> SELECT acos(2);NaN
ACOSH
Function statement:
ACOSH(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the hyperbolic arc cosine of expr.
Return type: double
Example:
> SELECT acosh(1);0.0> SELECT acosh(0);NaN
ASIN
Function statement:
ASIN(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the arc sine of expr.
Return type: double
Example:
> SELECT asin(0);0.0> SELECT asin(2);NaN
ASINH
Function statement:
ASINH(<expr> integer|double|decimal)
Supported engine: SparkSQL
Usage instructions: Return the hyperbolic arc sine of expr.
Return type: double
Example:
> SELECT asinh(0);0.0
ATAN
Function statement:
ATAN(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the arc tangent of expr.
Return type: double
Example:
> SELECT atan(0);0.0
ATAN2
Function statement:
ATAN2(<x>, integer|double|decimal, <y> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the angle in radians between the positive x-axis and the point given by the coordinates.
Return type: double
Example:
> SELECT atan2(0, 0);0.0
ATANH
Function statement:
ATANH(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the hyperbolic arc tangent of expr.
Return type: double
Example:
> SELECT atanh(0);0.0> SELECT atanh(2);NaN
BIN
Function statement:
BIN(<expr> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Return the string representation of the long value expr in binary format.
Return type: string
Example:
> SELECT bin(13);1101> SELECT bin(-13);1111111111111111111111111111111111111111111111111111111111110011
BROUND
Function statement:
BROUND(<expr> integer|double|decimal, <d> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Return expr rounded to d decimal places using the HALF_EVEN rounding mode.
Return type: decimal
Example:
> SELECT bround(2.5, 0);2
CBRT
Function statement:
CBRT(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the cube root of expr.
Return type: double
Example:
> SELECT cbrt(27.0);3.0
CEIL
Function statement:
CEIL(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the smallest integer not less than expr.
Return type: integer
Example:
> SELECT ceil(-0.1);0> SELECT ceil(5);5
COS
Function statement:
COS(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the cosine of expr.
Return type: double
Example:
> SELECT cos(0);1.0
COSH
Function statement:
COSH(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the hyperbolic cosine of expr.
Return type: double
Example:
> SELECT cosh(0);1.0
CONV
Function statement:
CONV(<num> bigint|string, <from_base> integer, <to_base> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Convert num from from_base to to_base.
Return type: string
Example:
> SELECT conv('100', 2, 10);4> SELECT conv(-10, 16, -10);-16
DEGREES
Function statement:
DEGREES(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Convert radians to degrees.
Return type: double
Example:
> SELECT degrees(3.141592653589793);180.0
E
Function statement:
E()
Supported engines: SparkSQL and Presto
Usage instructions: Return the Euler's constant.
Return type: double
Example:
> SELECT e();2.718281828459045
EXP
Function statement:
EXP(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return Euler's constant e raised to the power of expr.
Return type: double
Example:
> SELECT exp(0);1.0
EXPM1
Function statement:
EXPM1(<expr> integer|double|decimal)
Supported engine: SparkSQL
Usage instructions: Return EXP(expr)-1.
Return type: double
Example:
> SELECT expm1(0);0.0
FLOOR
Function statement:
FLOOR(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the largest integer less than or equal to expr.
Return type: integer
Example:
> SELECT floor(-0.1);-1> SELECT floor(5);5
FACTORIAL
Function statement:
FACTORIAL(<expr> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Return the factorial of expr. expr must be in the range [0..20]. Otherwise, NULL is returned.
Return type: bigint
Example:
> SELECT factorial(5);120
HEX
Function statement:
HEX(<expr> bigint|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return expr as a hexadecimal string.
Return type: string
Example:
> SELECT hex(17);11> SELECT hex('Spark SQL');537061726B2053514C
HYPOT
Function statement:
HYPOT(<expr1> integer|double|decimal, <expr2> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return sqrt(pow(expr1, 2) + pow(expr2, 2)).
Return type: double
Example:
> SELECT hypot(3, 4);5.0
LOG
Function statement:
LOG(<base> integer|double|decimal, <expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the logarithm of expr with base as the base.
Return type: double
Example:
> SELECT log(10, 100);2.0
LOG10
Function statement:
LOG10(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the base-10 logarithm of expr.
Return type: double
Example:
> SELECT log10(10);1.0
LOG1P
Function statement:
LOG1P(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return log(1 + expr).
Return type: double
Example:
> SELECT log1p(0);0.0
LOG2
Function statement:
LOG2(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the base-2 logarithm of expr.
Return type: double
Example:
> SELECT log2(2);1.0
LN
Function statement:
LN(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the natural logarithm of expr (base e).
Return type: double
Example:
> SELECT ln(1);0.0
MOD
Function statement:
MOD(<expr1> integer|double|decimal, <expr2> integer|double|decimal)<expr1> MOD <expr2>
Supported engines: SparkSQL and Presto
Usage instructions: Return the remainder of expr1/expr2.
Return type: double|integer
Example:
> SELECT 2 % 1.8;0.2> SELECT MOD(2, 1.8);0.2
NEGATIVE
Function statement:
NEGATIVE(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the negative of expr.
Return type: Same as expr.
Example:
> SELECT negative(1);-1
PI
Function statement:
PI()
Supported engines: SparkSQL and Presto
Usage instructions: Return PI.
Return type: double
Example:
> SELECT pi();3.141592653589793
PMOD
Function statement:
PMOD(<expr1> integer|double|decimal, <expr2> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the positive value of expr1 mod expr2.
Return type: double
Example:
> SELECT pmod(10, 3);1> SELECT pmod(-10, 3);2
POSITIVE
Function statement:
POSITIVE(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return expr.
Return type: Same as expr.
Example:
> SELECT positive(1);1
POWER
Function statement:
POWER(<base> integer|double|decimal, <number> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return base raised to the power of number.
Return type: double
Example:
> SELECT power(2, 3);8.0
POW
Function statement:
POW(<base> integer|double|decimal, <number> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return base raised to the power of number.
Return type: double
Example:
> SELECT pow(2, 3);8.0
RADIANS
Function statement:
RADIANS(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Convert degrees to radians.
Return type: double
Example:
> SELECT radians(180);3.141592653589793
RINT
Function statement:
RINT(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the double value that is closest to the parameter and equal to a mathematical integer.
Return type: double
Example:
> SELECT rint(12.3456);12.0
ROUND
Function statement:
ROUND(<expr> integer|double|decimal, <d> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Round expr to d decimal places using the half-up rounding mode.
Return type: double
Example:
> SELECT round(2.5, 0);3
SHIFTLEFT
Function statement:
SHIFTLEFT(<base> integer|double|decimal, <expr> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Perform a bitwise left shift.
Return type: int |bigint
Example:
> SELECT shiftleft(2, 1);4
SHIFTRIGHT
Function statement:
SHIFTRIGHT(<base> integer|double|decimal, <expr> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Perform a bitwise right shift.
Return type: int |bigint
Example:
> SELECT shiftright(4, 1);2
SHIFTRIGHTUNSIGNED
Function statement:
SHIFTRIGHTUNSIGNED(<base> integer|double|decimal, <expr> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Perform a bitwise unsigned right shift.
Return type: int |bigint
Example:
> SELECT shiftrightunsigned(4, 1);2
SIGN
Function statement:
SIGN(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return -1.0, 0.0, or 1.0 respectively when expr is negative, 0, or positive.
Return type: double
Example:
> SELECT sign(40);1.0
SIGNUM
Function statement:
SIGNUM(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return -1.0, 0.0, or 1.0 respectively if expr is negative, 0, or positive.
Return type: double
Example:
> SELECT signum(40);1.0
SIN
Function statement:
SIN(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the sine of expr.
Return type: double
Example:
> SELECT sin(0);0.0
SINH
Function statement:
SINH(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the hyperbolic sine of expr.
Return type: double
Example:
> SELECT sinh(0);0.0
SQRT
Function statement:
SQRT(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the square root of expr.
Return type: double
Example:
> SELECT sqrt(4);2.0
TAN
Function statement:
TAN(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the tangent of expr.
Return type: double
Example:
> SELECT tan(0);0.0
COT
Function statement:
COT(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the cotangent of the expression.
Return type: double
Example:
> SELECT cot(1);0.6420926159343306
TANH
Function statement:
TANH(<expr> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return the hyperbolic tangent of expr.
Return type: double
Example:
> SELECT tanh(0);0.0
WIDTH_BUCKET
Function statement:
WIDTH_BUCKET(<value> integer|double|decimal, <min> integer|double|decimal, <max> integer|double|decimal, <num_backet> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Divide the range from min to max into num_bucket equal groups, and return the group number into which value falls.
Return type: integer
Example:
> SELECT width_bucket(5.3, 0.2, 10.6, 5);3> SELECT width_bucket(-2.1, 1.3, 3.4, 3);0> SELECT width_bucket(8.1, 0.0, 5.7, 4);5> SELECT width_bucket(-0.9, 5.2, 0.5, 2);3
TRY_ADD
Function statement:
TRY_ADD(<expr1> integer|double|decimal|date|timestamp, <expr2> integer|double|decimal|date|timestamp)
Supported engines: SparkSQL and Presto
Usage instructions: Return the sum of expr1 and expr2. If there is an overflow, null is returned.
Return type: integer|double|decimal|date|timestamp
Example:
> SELECT try_add(1, 2);3> SELECT try_add(2147483647, 1);NULL> SELECT try_add(date'2021-01-01', 1);2021-01-02
TRY_DIVIDE
Function statement:
TRY_DIVIDE(<dividend> integer|double|decimal, <divisor> integer|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Return dividend divided by divisor.
Return type: double
Example:
> SELECT try_divide(3, 2);1.5> SELECT try_divide(2L, 2L);1.0> SELECT try_divide(1, 0);NULL
RAND
Function statement:
RAND([<seed> integer])
Supported engines: SparkSQL and Presto
Usage instructions: Return a random value that is independently and uniformly distributed within the range [0, 1].
Return type: double
Example:
> SELECT rand();0.9629742951434543> SELECT rand(0);0.7604953758285915> SELECT rand(null);0.7604953758285915
RANDOM
Function statement:
RANDOM([<seed> integer])
Supported engines: SparkSQL and Presto
Usage instructions: Return a random value that is independently and uniformly distributed within the range [0, 1].
Return type: double
Example:
> SELECT rand();0.9629742951434543> SELECT rand(0);0.7604953758285915> SELECT rand(null);0.7604953758285915
RANDN
Function statement:
RANDN([<seed> integer])
Supported engines: SparkSQL and Presto
Usage instructions: Return a random value that is independently and identically distributed (i.i.d.) from a standard normal distribution. The implementation of this function differs between SPARKSQL and PRESTO. Using the same seed may yield different results.
Return type: double
Example:
> SELECT randn();-0.3254147983080288> SELECT randn(0);1.6034991609278433> SELECT randn(null);1.6034991609278433
DIV
Function statement:
<expr1> DIV <expr2>
Supported engine: SparkSQL
Usage instructions: Divide expr1 by expr2.
Return type: integer
Example:
> SELECT 3 div 2;1
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No