数学函数
所有文档

          数据仓库 Palo

          数学函数

          Palo 支持以下数学函数:

          1.sin
          2.asin
          3.tan
          4.atan
          5.cos
          6.acos
          7.abs
          8.bin
          9.ceil
          10.floor
          11.conv
          12.degrees
          13.e
          14.exp
          15.mod
          16.fmod
          17.pmod
          18.greatest
          19.least
          20.hex
          21.unhex
          22.ln
          23.dlog1
          24.log
          25.negative
          26.positive
          27.pi
          28.pow
          29.radians
          30.rand
          31.round
          32.sign
          33.sqrt
          34.truncate

          SIN

          Description

          sin(double a)
          • 功能:返回a的正弦值
          • 返回类型:double类型

          Example

          mysql> select sin(1), sin(0.5 * pi());
          +--------------------+-----------------+
          | sin(1.0)           | sin(0.5 * pi()) |
          +--------------------+-----------------+
          | 0.8414709848078965 |               1 |
          +--------------------+-----------------+

          Keywords

          sin

          ASIN

          Description

          asin(double a)
          • 功能: 反正弦函数,a必须在 -1 到 1 之间。
          • 返回类型:double类型

          Example

          mysql> select asin(0.8414709848078965), asin(2);
          +--------------------------+-----------+
          | asin(0.8414709848078965) | asin(2.0) |
          +--------------------------+-----------+
          |                        1 |       nan |
          +--------------------------+-----------+

          Keywords

          asin

          TAN

          Description

          tan(double a)
          • 功能:返回a的正切值
          • 返回类型:double类型

          Example

          mysql> select tan(pi()/4);
          +---------------------+
          | tan(pi() / 4.0)     |
          +---------------------+
          | 0.99999999999999989 |
          +---------------------+

          Keywords

          tan

          ATAN

          Description

          atan(double a)
          • 功能: 反正切值函数
          • 返回类型:double类型

          Example

          mysql> SELECT ATAN(1.5574077246549023),ATAN(0);
          +--------------------------+---------+
          | ATAN(1.5574077246549023) | ATAN(0) |
          +--------------------------+---------+
          |                        1 |       0 |
          +--------------------------+---------+

          Keywords

          atan

          COS

          Description

          cos(double a)
          • 功能:返回参数的余弦值
          • 返回类型:double类型

          Example

          mysql> select cos(1), cos(0), cos(pi());
          +---------------------+----------+-----------+
          | cos(1.0)            | cos(0.0) | cos(pi()) |
          +---------------------+----------+-----------+
          | 0.54030230586813977 |        1 |        -1 |
          +---------------------+----------+-----------+

          Keywords

          cos

          ACOS

          Description

          acos(double a)
          • 功能: 反余弦函数,a必须在 -1 到 1 之间。
          • 返回类型:double类型

          Example

          mysql> select acos(2), acos(1), acos(-1);
          +-----------+-----------+--------------------+
          | acos(2.0) | acos(1.0) | acos(-1.0)         |
          +-----------+-----------+--------------------+
          |       nan |         0 | 3.1415926535897931 |
          +-----------+-----------+--------------------+

          Keywords

          acos

          ABS

          Description

          abs(numeric a)
          • 功能: 返回参数的绝对值
          • 返回类型:数字类型

          Example

          mysql> select abs(-1.2);
          +-----------+
          | abs(-1.2) |
          +-----------+
          |       1.2 |
          +-----------+
          
          mysql> select abs(-10);
          +----------+
          | abs(-10) |
          +----------+
          |       10 |
          +----------+

          Keywords

          abs

          BIN

          Description

          bin(bigint a)
          • 功能: 返回整型的二进制表示形式(即0 和1 序列)
          • 返回类型:string类型

          Example

          mysql> select bin(10);
          +---------+
          | bin(10) |
          +---------+
          | 1010    |
          +---------+

          Keywords

          bin

          CEIL,CEILING,DCEIL

          Description

          ceil(double a)
          ceiling(double a)
          dceil(double a)
          • 功能: 返回大于等于该参数的最小整数
          • 返回类型:int类型

          Example

          mysql> select dceil(1.2), ceiling(1.2), ceil(1.2);
          +------------+--------------+-----------+
          | dceil(1.2) | ceiling(1.2) | ceil(1.2) |
          +------------+--------------+-----------+
          |          2 |            2 |         2 |
          +------------+--------------+-----------+

          Keywords

          dceil, ceiling, ceil

          FLOOR

          Description

          floor(double a)
          dfloor(double a)
          • 功能:返回小于等于该参数的最大整数
          • 返回类型:int类型

          Example

          mysql> select floor(2.9);
          +------------+
          | floor(2.9) |
          +------------+
          |          2 |
          +------------+
          
          mysql> select dfloor(2.9);
          +-------------+
          | dfloor(2.9) |
          +-------------+
          |           2 |
          +-------------+

          Keywords

          floor, dfloor

          CONV

          Description

          conv(bigint num, int from_base, int to_base)
          conv(string num,int from_base, int to_base)
          • 功能: 进制转换函数,返回某个整数在特定进制下的的字符串形式。输入参数可以是整型的字符串形式。如果想要将函数的返回值转换成整数,可以使用 CAST 函数。
          • 返回类型:string类型

          Example

          mysql> select conv(64,10,8);
          +-----------------+
          | conv(64, 10, 8) |
          +-----------------+
          | 100             |
          +-----------------+
          
          mysql> select cast(conv('fe', 16, 10) as int) as "transform_string_to_int";
          +-------------------------+
          | transform_string_to_int |
          +-------------------------+
          |                     254 |
          +-------------------------+

          Keywords

          conv

          DEGREES

          Description

          degrees(double a)
          • 功能:将弧度转成角度
          • 返回类型:double类型

          Example

          mysql> select degrees(pi());
          +---------------+
          | degrees(pi()) |
          +---------------+
          |           180 |
          +---------------+

          Keywords

          degrees

          E

          Description

          e()
          • 功能:返回数学上的常量e
          • 返回类型:double类型

          Example

          mysql> select e();
          +--------------------+
          | e()                |
          +--------------------+
          | 2.7182818284590451 |
          +--------------------+

          Keywords

          e

          EXP,DEXP

          Description

          exp(double a)
          dexp(double a)
          • 功能: 返回 e 的 a 次幂
          • 返回类型: double 类型

          Example

          mysql> select exp(2);
          +------------------+
          | exp(2.0)         |
          +------------------+
          | 7.38905609893065 |
          +------------------+
          
          mysql> select dexp(2);
          +------------------+
          | dexp(2.0)        |
          +------------------+
          | 7.38905609893065 |
          +------------------+

          Keywords

          exp, dexp

          MOD

          Description

          mod(numeric_type a, same_type b)
          • 功能:返回a除以b的余数。等价于%算术符。
          • 返回类型:和输入类型相同

          Example

          mysql> select mod(10,3);
          +------------+
          | mod(10, 3) |
          +------------+
          |          1 |
          +------------+
          
          mysql> select mod(5.5,2);
          +-------------+
          | mod(5.5, 2) |
          +-------------+
          |         1.5 |
          +-------------+

          Keywords

          mod

          FMOD

          Description

          fmod(double a, double b)
          fmod(float a, float b)
          • 功能:返回a除以b的余数。等价于 % 算术符
          • 返回类型:float或者double类型

          Example

          mysql> select fmod(10,3);
          +-----------------+
          | fmod(10.0, 3.0) |
          +-----------------+
          |               1 |
          +-----------------+
          
          mysql> select fmod(5.5,2);
          +----------------+
          | fmod(5.5, 2.0) |
          +----------------+
          |            1.5 |
          +----------------+

          Keywords

          fmod

          PMOD

          Description

          pmod(int a, int b)
          pmod(double a, double b)
          • 功能:正取余函数
          • 返回类型:int类型或者double类型(由输入参数决定)

          Example

          mysql> select pmod(3, 2), pmod(1.1, 2);
          +------------+------------+
          | pmod(3, 2) | pmod(1, 2) |
          +------------+------------+
          |          1 |          1 |
          +------------+------------+

          Keywords

          pmod

          GREATEST

          Description

          greatest(bigint a[, bigint b ...])
          greatest(double a[, double b ...])
          greatest(decimal(p,s) a[, decimal(p,s) b ...])
          greatest(string a[, string b ...])
          greatest(timestamp a[, timestamp b ...])
          • 功能:返回列表里的最大值
          • 返回类型:和参数类型相同

          Example

          mysql> select greatest(1,2,3);
          +-------------------+
          | greatest(1, 2, 3) |
          +-------------------+
          |                 3 |
          +-------------------+
          
          mysql> select greatest("a", "b", "c");
          +-------------------------+
          | greatest('a', 'b', 'c') |
          +-------------------------+
          | c                       |
          +-------------------------+

          Keywords

          greatest

          LEAST

          Description

          least(bigint a[, bigint b ...])
          least(double a[, double b ...])
          least(decimal(p,s) a[, decimal(p,s) b ...])
          least(string a[, string b ...])
          least(timestamp a[, timestamp b ...])
          • 功能:返回列表里的最小值
          • 返回类型:和参数类型相同

          Example

          mysql>  select least(1,2,3);
          +----------------+
          | least(1, 2, 3) |
          +----------------+
          |              1 |
          +----------------+
          
          mysql>  select least("a", "b", "c");
          +----------------------+
          | least('a', 'b', 'c') |
          +----------------------+
          | a                    |
          +----------------------+

          Keywords

          least

          HEX

          Description

          hex(bigint a)
          hex(string a)
          • 功能:返回整型或字符串中各个字符的16进制表示形式。
          • 返回类型:string类型

          Example

          mysql> select hex('abc');
          +------------+
          | hex('abc') |
          +------------+
          | 616263     |
          +------------+
          
          mysql> select unhex(616263);
          +---------------+
          | unhex(616263) |
          +---------------+
          | abc           |
          +---------------+

          Keywords

          hex

          UNHEX

          Description

          unhex(string a)
          • 功能:把十六进制格式的字符串转化为原来的格式
          • 返回类型:string类型

          Example

          mysql> select hex('abc');
          +------------+
          | hex('abc') |
          +------------+
          | 616263     |
          +------------+
          
          mysql> select unhex(616263);
          +---------------+
          | unhex(616263) |
          +---------------+
          | abc           |
          +---------------+

          Keywords

          unhex

          LN

          Description

          ln(double a)
          • 功能:返回2的自然对数
          • 返回类型:double 类型

          Example

          mysql> select ln(2);
          +---------------------+
          | ln(2.0)             |
          +---------------------+
          | 0.69314718055994529 |
          +---------------------+

          Keywords

          ln

          DLOG1

          Description

          dlog1(double a)
          • 功能:返回参数的自然对数形式
          • 返回类型:double类型

          Example

          mysql> select dlog1(2);
          +---------------------+
          | dlog1(2.0)          |
          +---------------------+
          | 0.69314718055994529 |
          +---------------------+

          Keywords

          dlog1

          LOG,LOG10,DLOG10,LOG2

          Description

          log(double base, double a)
          • 功能:返回log以base为底数,以a为指数的对数值。
          • 返回类型:double类型
          log10(double a)
          dlog10(double a)
          • 功能:返回log以10为底数,以a为指数的对数值。
          • 返回类型:double类型
          log2(double a)
          • 功能:返回log以2为底数,以a为指数的对数值。
          • 返回类型:double类型

          Example

          mysql> select log(2, 65536);
          +-------------------+
          | log(2.0, 65536.0) |
          +-------------------+
          |                16 |
          +-------------------+
          
          mysql> select log10(2);
          +--------------------+
          | log10(2.0)         |
          +--------------------+
          | 0.3010299956639812 |
          +--------------------+
          
          mysql> select dlog10(2);
          +--------------------+
          | dlog10(2.0)        |
          +--------------------+
          | 0.3010299956639812 |
          +--------------------+
          
          mysql> select log2(2);
          +-----------+
          | log2(2.0) |
          +-----------+
          |         1 |
          +-----------+

          Keywords

          log, log10, dlog, log2

          NEGATIVE

          Description

          negative(int a)
          negative(double a)
          • 功能:将参数a的符号位取反,如果参数是负值,则返回正值
          • 返回类型:根据输入参数类型返回int类型或double类型
          • 使用说明:如果你需要确保所有返回值都是负值,可以使用 -abs(a) 函数。

          Example

          mysql> select negative(1.0);
          +---------------+
          | negative(1.0) |
          +---------------+
          |            -1 |
          +---------------+
          1 row in set (0.02 sec)
          
          mysql> select negative(-1);
          +--------------+
          | negative(-1) |
          +--------------+
          |            1 |
          +--------------+

          Keywords

          negative

          POSITIVE

          Description

          positive(int a)
          • 功能:返回参数的原值,即使参数是负的,仍然返回原值。
          • 返回类型:int类型
          • 使用说明:如果你需要确保所有返回值都是正值,可以使用 abs() 函数。

          Example

          mysql> select positive(-1), positive(1);
          +--------------+-------------+
          | positive(-1) | positive(1) |
          +--------------+-------------+
          |           -1 |           1 |
          +--------------+-------------+

          Keywords

          positive

          PI

          Description

          pi()
          • 功能:返回常量Pi
          • 返回类型: double类型

          Example

          mysql> select pi();
          +--------------------+
          | pi()               |
          +--------------------+
          | 3.1415926535897931 |
          +--------------------+

          Keywords

          pi

          POW,POWER,DPOW,FPOW

          Description

          pow(double a, double p)
          power(double a, double p)
          dpow(double a, double p)
          fpow(double a, double p)
          • 功能:返回a的p次幂
          • 返回类型:double类型

          Example

          mysql> select pow(2, 10), power(2, 10), dpow(2, 10), fpow(2, 10);
          +----------------+------------------+-----------------+-----------------+
          | pow(2.0, 10.0) | power(2.0, 10.0) | dpow(2.0, 10.0) | fpow(2.0, 10.0) |
          +----------------+------------------+-----------------+-----------------+
          |           1024 |             1024 |            1024 |            1024 |
          +----------------+------------------+-----------------+-----------------+

          Keywords

          POW, POWER, DPOW, FPOW

          RADIANS

          Description

          radians(double a)
          • 功能:将弧度转换成角度
          • 返回类型:double类型

          Example

          mysql> select radians(90);
          +--------------------+
          | radians(90.0)      |
          +--------------------+
          | 1.5707963267948966 |
          +--------------------+

          Keywords

          radians

          RAND,RANDOM

          Description

          rand()
          random()
          • 功能:返回0~1之间的随机值。
          • 返回类型:double

          Example

          mysql> select rand(), rand(), random();
          +---------------------+---------------------+---------------------+
          | rand()              | rand()              | random()            |
          +---------------------+---------------------+---------------------+
          | 0.39794450929180808 | 0.34321919244300736 | 0.38788449829415106 |
          +---------------------+---------------------+---------------------+

          Keywords

          rand, random

          ROUND

          Description

          round(double a)
          round(double a, int d)
          • 功能:取整函数。如果只带一个参数,该函数会返回距离该值最近的整数。如果带2个参数,第二个参数为小数点后面保留的位数。
          • 返回类型:如果参数是浮点类型则返回bigint。如果第二个参数大于1,则返回double类型。

          Example

          mysql> select round(100.456, 2);
          +-------------------+
          | round(100.456, 2) |
          +-------------------+
          |            100.46 |
          +-------------------+

          Keywords

          round

          SIGN

          Description

          sign(double a)
          • 功能:如果a是整数或者0,返回1;如果a是负数,则返回-1
          • 返回类型:int类型

          Example

          mysql> select sign(-1), sign(1.2);
          +------------+-----------+
          | sign(-1.0) | sign(1.2) |
          +------------+-----------+
          |         -1 |         1 |
          +------------+-----------+

          Keywords

          sign

          SQRT,DSQRT

          Description

          sqrt(double a)
          dsqrt(double a)
          • 功能:返回a的平方根
          • 返回类型:double类型

          Example

          mysql> select sqrt(4), dsqrt(10);
          +-----------+--------------------+
          | sqrt(4.0) | dsqrt(10.0)        |
          +-----------+--------------------+
          |         2 | 3.1622776601683795 |
          +-----------+--------------------+

          Keywords

          sqrt, dsqrt

          TRUNCATE

          Description

          truncate(double num, int len)
          • 功能:截取num保留len指定小数位数
          • 返回类型:double类型

          Example

          select truncate(1.1234, 2); 
          +---------------------+
          | truncate(1.1234, 2) |
          +---------------------+
          |                1.12 |
          +---------------------+

          Keywords

          truncate
          上一篇
          字符串函数
          下一篇
          位操作函数