tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Data Lake Compute
    Documentation
    Download PDF

    Mathematical Functions

    Last updated: 2024-08-07 17:32:33
    Download PDF

      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
      
      
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy