ABS

abs(tinyint)
abs(smallint)
abs(int)
abs(bigint)
abs(double)
  • 命令说明:求绝对值
  • 返回值类型:LONG或DOUBLE
  • 示例:
      select abs(-9);
      +-------------+
      | _col0       |
      +-------------+
      |   9         |

MOD

  • 命令说明:求余
  • 示例:
      SELECT mod(cast(4.5 as tinyint), 3);
      +-------+
      | _col0 |
      +-------+
      |     2 |
      SELECT mod(cast(4.5 as smallint), 3);
      +-------+
      | _col0 |
      +-------+
      |     2 |
      SELECT mod(cast(4.5 as int), 3);
      +-------+
      | _col0 |
      +-------+
      |     2 |
      SELECT mod(cast(4.5 as bigint), 3);
      +-------+
      | _col0 |
      +-------+
      |     2 |
      SELECT mod(cast(4.5 as double), 3);
      +-------+
      | _col0 |
      +-------+
      |   1.5 |

ROUND

round(double)
round(tinyint)
round(smallint)
round(int)
round(bigint)
  • 命令说明:四舍五入
  • 返回值类型:BIGINT或DOUBLE
  • 示例:
      SELECT round(cast(4.5 as tinyint), 3);
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT round(cast(4.5 as smallint), 3);
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT round(cast(4.5 as int), 3);
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT round(cast(4.5 as bigint), 3);
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT round(cast(4.5 as double), 3);
      +-------+
      | _col0 |
      +-------+
      |   4.5 |
      SELECT round(cast(4.5 as tinyint));
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT round(cast(4.5 as smallint));
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT round(cast(4.5 as int));
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT round(cast(4.5 as bigint));
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT round(cast(4.5 as double));
      +-------+
      | _col0 |
      +-------+
      |   5.0 |

SQRT

sqrt(double)
  • 命令说明:求平方根
  • 返回值类型:DOUBLE
  • 示例:
      select sqrt(4);
      +-------+
      | _col0 |
      +-------+
      |   2.0 |

CBRT

cbrt(double)
  • 命令说明:求立方根
  • 返回值类型:DOUBLE
  • 示例:
      select cbrt(8);
      +-------+
      | _col0 |
      +-------+
      |   2.0 |

E

e()
  • 命令说明:求自然对数
  • 返回值类型:DOUBLE
  • 示例:
      select e();
      +-------------------+
      | _col0             |
      +-------------------+
      | 2.718281828459045 |

LN

ln(double)
  • 命令说明:求自然对数
  • 返回值类型:DOUBLE
  • 示例:
      select ln(2.718281828459045);
      +-------+
      | _col0 |
      +-------+
      |   1.0 |

LOG

log(double)
  • 命令说明:求对数
  • 返回值类型:DOUBLE
  • 示例:
      select log(10,100);
      +-------+
      | _col0 |
      +-------+
      |   2.0 |

LOG2

log2(double)
  • 命令说明:求以2为底的对数
  • 返回值类型:DOUBLE
  • 示例:
      select log2(8);
      +-------+
      | _col0 |
      +-------+
      |   3.0 |

LOG10

log10(double)
  • 命令说明:求以10位底的对数
  • 返回值类型:DOUBLE
  • 示例:
      select log10(100);
      +-------+
      | _col0 |
      +-------+
      |   2.0 |

PI

pi()
  • 命令说明:返回pi
  • 返回值类型:DOUBLE
  • 示例:
      select pi();
      +-------------------+
      | _col0             |
      +-------------------+
      | 3.141592653589793 |

POWER

power(double, double)
  • 命令说明:指数函数
  • 返回值类型:DOUBLE
  • 示例:
      select power(1.2,3.4);
      +-------------------+
      | _col0             |
      +-------------------+
      | 1.858729691979481 |

RANDOM

random()
random(tinyint)
random(smallint)
random(int)
random(bigint)
  • 命令说明:随机函数
  • 返回值类型:BIGINT
  • 示例:
      select random();
      +--------------------+
      | _col0              |
      +--------------------+
      | 0.5709993917553757 |
      select random(cast(3 as tinyint));
      +--------------------+
      | _col0              |
      +--------------------+
      |     2              |
      select random(cast(3 as smallint));
      +--------------------+
      | _col0              |
      +--------------------+
      |     0              |
      select random(cast(3 as int));
      +--------------------+
      | _col0              |
      +--------------------+
      |     1              |
      select random(cast(3 as bigint));
      +--------------------+
      | _col0              |
      +--------------------+
      |     0              |

RADIANS

radians(double)
  • 命令说明:角度转度
  • 返回值类型:DOUBLE
  • 示例:
      select radians(60.0);
      +--------------------+
      | _col0              |
      +--------------------+
      | 1.0471975511965976 |

DEGREES

degrees(double)
  • 命令说明:把弧度转化为度
  • 返回值类型:DOUBLE
  • 示例:
      select degrees(1.3);
      +-------------------+
      | _col0             |
      +-------------------+
      | 74.48451336700703 |

SIGN

sign(bigint)
sign(int)
sign(smallint)
sign(tinyint)
sign(double)
  • 命令说明:符号函数
  • 返回值类型:BIGINT或DOUBLE
  • 示例:
      SELECT sign(cast(4.5 as bigint));
      +-------+
      | _col0 |
      +-------+
      |     1 |
      SELECT sign(cast(4.5 as int));
      +-------+
      | _col0 |
      +-------+
      |     1 |
      SELECT sign(cast(4.5 as smallint));
      +-------+
      | _col0 |
      +-------+
      |     1 |
      SELECT sign(cast(4.5 as tinyint));
      +-------+
      | _col0 |
      +-------+
      |     1 |
      SELECT sign(cast(4.5 as double));
      +-------+
      | _col0 |
      +-------+
      |   1.0 |

CEILING/CEIL

ceiling(tinyint)
ceiling(smallint)
ceiling(int)
ceiling(bitint)
ceiling(double)
  • 命令说明:向上取整
  • 返回值类型:LONG
  • 示例:
      select ceiling(2.3);
      +-------+
      | _col0 |
      +-------+
      |     3 |

FLOOR

floor(tinyint)
floor(smallint)
floor(int)
floor(bigint)
floor(double)
  • 命令说明:向下取整
  • 返回值类型:LONG
  • 示例:
      select floor(7.8);
      +-------+
      | _col0 |
      +-------+
      |     7 |

TRUNCATE

truncate(double)
  • 命令说明:截断函数
  • 返回值类型:LONG或DOUBLE
  • 示例:
      select truncate(2.3);
      +-------+
      | _col0 |
      +-------+
      |   2.0 |
      select truncate(2.3456,2);
      +--------+
      | _col0  |
      +--------+
      | 2.3400 |
      SELECT truncate(cast(4.5 as tinyint), 3);
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT truncate(cast(4.5 as smallint), 3);
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT truncate(cast(4.5 as int), 3);
      +-------+
      | _col0 |
      +-------+
      |     5 |
      SELECT truncate(cast(4.5 as bigint), 3);
      +-------+
      | _col0 |
      +-------+
      |     5 |

COS

cos(double)
  • 命令说明:求余弦
  • 返回值类型:DOUBLE
  • 示例:
      select cos(1.3);
      +---------------------+
      | _col0               |
      +---------------------+
      | 0.26749882862458735 |

COSH

cosh(double)
  • 命令说明:求双曲余弦
  • 返回值类型:DOUBLE
  • 示例:
      select cosh(1.3);
      +--------------------+
      | _col0              |
      +--------------------+
      | 1.9709142303266285 |

ACOS

acos(double)
  • 命令说明:求反余弦函数值
    说明 小数点后第16位与MySQL不同。
  • 返回值类型:DOUBLE
  • 示例:
      select acos(0.5);
      +--------------------+
      | _col0              |
      +--------------------+
      | 1.0471975511965979 |

TAN

tan(double)
  • 命令说明:求正切
    说明 与MySQL第16位精度不同。
  • 返回值类型:DOUBLE
  • 示例:
      select tan(8);
      +--------------------+
      | _col0              |
      +--------------------+
      | -6.799711455220379 |

ATAN

atan(double)
  • 命令说明:求反正切函数值
    说明 MySQL16位精度,分析型数据库MySQL版14位精度。
  • 返回值类型:DOUBLE
  • 示例:
      select atan(0.5);
      +--------------------+
      | _col0              |
      +--------------------+
      | 0.4636476090008061 |

ATAN2

atan2(double, double)
  • 命令说明:参数1除参数2后的反正切值
  • 返回值类型:DOUBLE
  • 示例:
      select atan2(0.5,0.3);
      +--------------------+
      | _col0              |
      +--------------------+
      | 1.0303768265243125 |

ASIN

asin(double)
  • 命令说明:求反正弦函数值
    说明 小数点后第16位与MySQL不同。
  • 返回值类型:DOUBLE
  • 示例:
      select asin(0.5);
      +--------------------+
      | _col0              |
      +--------------------+
      | 0.5235987755982989 |

UUID

uuid()
  • 命令说明:求uuid
  • 返回值类型:VARCHAR
  • 示例:
      select uuid();
      +---------------------------+
      | _col0                     |
      +---------------------------+
      | M5be8ec4f80c98679b3badcb3 |

TO_BASE

to_base(bigint, bigint)
  • 命令说明:根据base将数字转为字符串
  • 返回值类型:VARCHAR
  • 示例:
      SELECT to_base(8,8);
      +-------+
      | _col0 |
      +-------+
      | 10    |

FROM_BASE

from_base(varchar, bigint)
  • 命令说明:根据base把字符串转为数字
  • 返回值类型:BIGINT
  • 示例:
      SELECT from_base('10',8);
      +-------+
      | _col0 |
      +-------+
      |     8 |