全部产品

数值函数

更新时间:2019-04-29 15:51:48

ABS

  1. abs(tinyint)
  2. abs(smallint)
  3. abs(int)
  4. abs(bigint)
  5. abs(double)
  • 命令说明:求绝对值

  • 返回值类型:LONG或DOUBLE

  • 示例:

    1. select abs(-9);
    2. +-------------+
    3. | _col0 |
    4. +-------------+
    5. | 9 |

    MOD

  • 命令说明:求余

  • 示例:

    1. SELECT mod(cast(4.5 as tinyint), 3);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2 |
    6. SELECT mod(cast(4.5 as smallint), 3);
    7. +-------+
    8. | _col0 |
    9. +-------+
    10. | 2 |
    11. SELECT mod(cast(4.5 as int), 3);
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 2 |
    16. SELECT mod(cast(4.5 as bigint), 3);
    17. +-------+
    18. | _col0 |
    19. +-------+
    20. | 2 |
    21. SELECT mod(cast(4.5 as double), 3);
    22. +-------+
    23. | _col0 |
    24. +-------+
    25. | 1.5 |

ROUND

  1. round(double)
  2. round(tinyint)
  3. round(smallint)
  4. round(int)
  5. round(bigint)
  • 命令说明:四舍五入

  • 返回值类型:BIGINT或DOUBLE

  • 示例:

    1. SELECT round(cast(4.5 as tinyint), 3);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 5 |
    6. SELECT round(cast(4.5 as smallint), 3);
    7. +-------+
    8. | _col0 |
    9. +-------+
    10. | 5 |
    11. SELECT round(cast(4.5 as int), 3);
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 5 |
    16. SELECT round(cast(4.5 as bigint), 3);
    17. +-------+
    18. | _col0 |
    19. +-------+
    20. | 5 |
    21. SELECT round(cast(4.5 as double), 3);
    22. +-------+
    23. | _col0 |
    24. +-------+
    25. | 4.5 |
    26. SELECT round(cast(4.5 as tinyint));
    27. +-------+
    28. | _col0 |
    29. +-------+
    30. | 5 |
    31. SELECT round(cast(4.5 as smallint));
    32. +-------+
    33. | _col0 |
    34. +-------+
    35. | 5 |
    36. SELECT round(cast(4.5 as int));
    37. +-------+
    38. | _col0 |
    39. +-------+
    40. | 5 |
    41. SELECT round(cast(4.5 as bigint));
    42. +-------+
    43. | _col0 |
    44. +-------+
    45. | 5 |
    46. SELECT round(cast(4.5 as double));
    47. +-------+
    48. | _col0 |
    49. +-------+
    50. | 5.0 |

SQRT

  1. sqrt(double)
  • 命令说明:求平方根

  • 返回值类型:DOUBLE

  • 示例:

    1. select sqrt(4);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2.0 |

CBRT

  1. cbrt(double)
  • 命令说明:求立方根

  • 返回值类型:DOUBLE

  • 示例:

    1. select cbrt(8);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2.0 |

E

  1. e()
  • 命令说明:求自然对数

  • 返回值类型:DOUBLE

  • 示例:

    1. select e();
    2. +-------------------+
    3. | _col0 |
    4. +-------------------+
    5. | 2.718281828459045 |

LN

  1. ln(double)
  • 命令说明:求自然对数

  • 返回值类型:DOUBLE

  • 示例:

    1. select ln(2.718281828459045);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 1.0 |

LOG

  1. log(double)
  • 命令说明:求对数

  • 返回值类型:DOUBLE

  • 示例:

    1. select log(10,100);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2.0 |

LOG2

  1. log2(double)
  • 命令说明:求以2为底的对数

  • 返回值类型:DOUBLE

  • 示例:

    1. select log2(8);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 3.0 |

LOG10

  1. log10(double)
  • 命令说明:求以10位底的对数

  • 返回值类型:DOUBLE

  • 示例:

    1. select log10(100);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2.0 |

PI

  1. pi()
  • 命令说明:返回pi

  • 返回值类型:DOUBLE

  • 示例:

    1. select pi();
    2. +-------------------+
    3. | _col0 |
    4. +-------------------+
    5. | 3.141592653589793 |

POWER

  1. power(double, double)
  • 命令说明:指数函数

  • 返回值类型:DOUBLE

  • 示例:

    1. select power(1.2,3.4);
    2. +-------------------+
    3. | _col0 |
    4. +-------------------+
    5. | 1.858729691979481 |

RANDOM

  1. random()
  2. random(tinyint)
  3. random(smallint)
  4. random(int)
  5. random(bigint)
  • 命令说明:随机函数

  • 返回值类型:BIGINT

  • 示例:

    1. select random();
    2. +--------------------+
    3. | _col0 |
    4. +--------------------+
    5. | 0.5709993917553757 |
    6. select random(cast(3 as tinyint));
    7. +--------------------+
    8. | _col0 |
    9. +--------------------+
    10. | 2 |
    11. select random(cast(3 as smallint));
    12. +--------------------+
    13. | _col0 |
    14. +--------------------+
    15. | 0 |
    16. select random(cast(3 as int));
    17. +--------------------+
    18. | _col0 |
    19. +--------------------+
    20. | 1 |
    21. select random(cast(3 as bigint));
    22. +--------------------+
    23. | _col0 |
    24. +--------------------+
    25. | 0 |

    RADIANS

  1. radians(double)
  • 命令说明:角度转度

  • 返回值类型:DOUBLE

  • 示例:

    1. select radians(60.0);
    2. +--------------------+
    3. | _col0 |
    4. +--------------------+
    5. | 1.0471975511965976 |

DEGREES

  1. degrees(double)
  • 命令说明:把弧度转化为度

  • 返回值类型:DOUBLE

  • 示例:

    1. select degrees(1.3);
    2. +-------------------+
    3. | _col0 |
    4. +-------------------+
    5. | 74.48451336700703 |

SIGN

  1. sign(bigint)
  2. sign(int)
  3. sign(smallint)
  4. sign(tinyint)
  5. sign(double)
  • 命令说明:符号函数

  • 返回值类型:BIGINT或DOUBLE

  • 示例:

    1. SELECT sign(cast(4.5 as bigint));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 1 |
    6. SELECT sign(cast(4.5 as int));
    7. +-------+
    8. | _col0 |
    9. +-------+
    10. | 1 |
    11. SELECT sign(cast(4.5 as smallint));
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 1 |
    16. SELECT sign(cast(4.5 as tinyint));
    17. +-------+
    18. | _col0 |
    19. +-------+
    20. | 1 |
    21. SELECT sign(cast(4.5 as double));
    22. +-------+
    23. | _col0 |
    24. +-------+
    25. | 1.0 |

CEILING/CEIL

  1. ceiling(tinyint)
  2. ceiling(smallint)
  3. ceiling(int)
  4. ceiling(bitint)
  5. ceiling(double)
  • 命令说明:向上取整

  • 返回值类型:LONG

  • 示例:

    1. select ceiling(2.3);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 3 |

FLOOR

  1. floor(tinyint)
  2. floor(smallint)
  3. floor(int)
  4. floor(bigint)
  5. floor(double)
  • 命令说明:向下取整

  • 返回值类型:LONG

  • 示例:

    1. select floor(7.8);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 7 |

TRUNCATE

  1. truncate(double)
  • 命令说明:截断函数

  • 返回值类型:LONG或DOUBLE

  • 示例:

    1. select truncate(2.3);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2.0 |
    6. select truncate(2.3456,2);
    7. +--------+
    8. | _col0 |
    9. +--------+
    10. | 2.3400 |
    11. SELECT truncate(cast(4.5 as tinyint), 3);
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 5 |
    16. SELECT truncate(cast(4.5 as smallint), 3);
    17. +-------+
    18. | _col0 |
    19. +-------+
    20. | 5 |
    21. SELECT truncate(cast(4.5 as int), 3);
    22. +-------+
    23. | _col0 |
    24. +-------+
    25. | 5 |
    26. SELECT truncate(cast(4.5 as bigint), 3);
    27. +-------+
    28. | _col0 |
    29. +-------+
    30. | 5 |

COS

  1. cos(double)
  • 命令说明:求余弦

  • 返回值类型:DOUBLE

  • 示例:

    1. select cos(1.3);
    2. +---------------------+
    3. | _col0 |
    4. +---------------------+
    5. | 0.26749882862458735 |

COSH

  1. cosh(double)
  • 命令说明:求双曲余弦

  • 返回值类型:DOUBLE

  • 示例:

    1. select cosh(1.3);
    2. +--------------------+
    3. | _col0 |
    4. +--------------------+
    5. | 1.9709142303266285 |

ACOS

  1. acos(double)
  • 命令说明:求反余弦函数值

    小数点后第16位与MySQL不同

  • 返回值类型:DOUBLE

  • 示例:

    1. select acos(0.5);
    2. +--------------------+
    3. | _col0 |
    4. +--------------------+
    5. | 1.0471975511965979 |

TAN

  1. tan(double)
  • 命令说明:求正切

    与MySQL第16位精度不同

  • 返回值类型:DOUBLE

  • 示例:

    1. select tan(8);
    2. +--------------------+
    3. | _col0 |
    4. +--------------------+
    5. | -6.799711455220379 |

ATAN

  1. atan(double)
  • 命令说明:求反正切函数值

    MySQL 16位精度,分析型数据库MySQL版 14位精度。

  • 返回值类型:DOUBLE

  • 示例:

    1. select atan(0.5);
    2. +--------------------+
    3. | _col0 |
    4. +--------------------+
    5. | 0.4636476090008061 |

ATAN2

  1. atan2(double, double)
  • 命令说明:参数1除参数2后的反正切值

  • 返回值类型:DOUBLE

  • 示例:

    1. select atan2(0.5,0.3);
    2. +--------------------+
    3. | _col0 |
    4. +--------------------+
    5. | 1.0303768265243125 |

ASIN

  1. asin(double)
  • 命令说明:求反正弦函数值

    小数点后第16位与MySQL不同

  • 返回值类型:DOUBLE

  • 示例:

    1. select asin(0.5);
    2. +--------------------+
    3. | _col0 |
    4. +--------------------+
    5. | 0.5235987755982989 |

UUID

  1. uuid()
  • 命令说明:求uuid

  • 返回值类型:VARCHAR

  • 示例:

    1. select uuid();
    2. +---------------------------+
    3. | _col0 |
    4. +---------------------------+
    5. | M5be8ec4f80c98679b3badcb3 |

TO_BASE

  1. to_base(bigint, bigint)
  • 命令说明:根据base将数字转为字符串

  • 返回值类型:VARCHAR

  • 示例:

    1. SELECT to_base(8,8);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 10 |

FROM_BASE

  1. from_base(varchar, bigint)
  • 命令说明:根据base把字符串转为数字

  • 返回值类型:BIGINT

  • 示例:

    1. SELECT from_base('10',8);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 8 |