数学函数

参考

四舍五入

round(DOUBLE a) 或 round(DOUBLE a, INT d)

select round(314.15926);
select round(314.15926, 2);
select round(314.15926, -2);

011

“HALF_EVEN rounding mode” 五舍五入

bround(DOUBLE a) 或 bround(DOUBLE a, INT d)

  • 与普通四舍五入的区别：

    • 普通四舍五入 “HALF_EVEN rounding mode” 五舍五入
      4 及以下舍去 4 及以下舍去
      逢 5 ，分别舍去和进位获得两个数，取其中的偶数（简单说就是取最近的偶数）
      5 及以上进位 6 及以上进位

  • 这么设计的目的是：

    • 从总体上，最小化 “五舍五入” 之后的数据，与源数据的偏差
select round(1.4), round(2.4), round(3.4), round(4.4);
select round(1.5), round(2.5), round(3.5), round(4.5);
select bround(1.4), bround(2.4), bround(3.4), bround(4.4);
select bround(1.5), bround(2.5), bround(3.5), bround(4.5);
select bround(1.6), bround(2.6), bround(3.6), bround(4.6);

012

取整、截断

  • floor(DOUBLE a)
    • 向下取整
  • ceil(DOUBLE a) 或 ceiling(DOUBLE a)
    • 向上取整
  • trunc(N,D)
    • 按需求截断
    • 该函数，除了此处 trunc(N,D) （用于截断数值）的形式，还有一种 trunc(date, fmt) （用于获取指定日期所在的指定周期的第一天）的形式，参考
select
    floor(3.1415926), ceil(3.1415926), ceiling(3.1415926);
select
    trunc(314.15926), trunc(314.15926, 1), trunc(314.15926, -1);

012a

随机数

  • rand()
    • 生成随机数
    • 返回 0, 1 之间的一个随机 DOUBLE 值
  • rand(INT seed)
    • 指定随机种子，生成随机数
    • Specifying the seed will make sure the generated random number sequence is deterministic.
select rand(), rand(), rand(), rand(), rand();
select rand(7), rand(7), rand(7), rand(7), rand(7);

013

绝对值、positive、negative、sign

  • abs(DOUBLE a)
    • 绝对值
  • positive(INT a) 或 positive(DOUBLE a)
    • 相当于 “一元前缀” 中的 +a
  • negative(INT a) 或 negative(DOUBLE a)
    • 相当于 “一元前缀” 中的 -a
  • sign(DOUBLE a) 或 sign(DECIMAL a)
    • 相当于取符号
select abs(-5), abs(0), abs(1.2);
select positive(-3), positive(0), positive(2.1);
select negative(-1), negative(0), negative(7);
select sign(-10), sign(0), sign(200);

014

两个常数

  • e()
    • 自然常数 e
  • pi()
    • 圆周率 π

幂运算、开方、对数运算

  • pow(DOUBLE a, DOUBLE p) 或 power(DOUBLE a, DOUBLE p)
    • 幂运算：a 为底，p 为幂
  • sqrt(DOUBLE a) 或 sqrt(DECIMAL a)
    • 开平方
  • cbrt(DOUBLE a)
    • 开立方
  • log10(DOUBLE a) 或 log10(DECIMAL a)
    • 以 10 为底的对数运算
  • log2(DOUBLE a) 或 log2(DECIMAL a)
    • 以 2 为底的对数运算
  • log(DOUBLE base, DOUBLE a) 或 log(DECIMAL base, DECIMAL a)
    • 指定底数的对数运算
select pow(2, 2), power(3, 3);
select sqrt(4), cbrt(27);
select log10(100), log2(64);
select log(3, 27);

015

自然常数的指数运算，对数运算

  • exp(DOUBLE a) 或 exp(DECIMAL a)
    • 以自然常数 e 为底的指数运算
  • ln(DOUBLE a) 或 ln(DECIMAL a)
    • 以自然常数 e 为底的对数运算

进制转换

  • bin(BIGINT a)

    • 将 BIGINT 转换为二进制字符串（返回 String）

  • hex(BIGINT a)

    • 将 BIGINT 转换为十六进制字符串（返回 String）

    016

  • hex(BINARY a)

    • 将 BINARY 转换为十六进制字符串（返回 String）

    • 这个没查到用法（BINARY 并不是指二进制数值，而是类似于 byte[] 字节序列，所以使用效果有点像 String ）

      • select hex(1010);			-- 这个执行的是 hex(BIGINT a)
select hex(conv(7,10,2));	-- 这个执行的是 hex(STRING a)
-- select hex(0x1010);		-- 这个会报错
select hex('0x1010');		-- 这个执行的是 hex(STRING a)
select unhex(3030),unhex('3030');
select hex(unhex(3030));	-- 这个执行的是 hex(STRING a)
-- 使用表中的 BINARY 类型字段也不行
drop table if exists test.test;
create table IF NOT EXISTS test.test(
    bin BINARY
);
insert into test.test values
(1001),
('1010'),
-- (0x1011), --报错
('0x1100');
desc test.test;
select bin, hex(bin) from test.test;

      • 017

  • hex(STRING a)

    • 将字符串逐个字符转换为十六进制 ASCII 码，拼接返回（返回 String）

  • unhex(STRING a)

    • 018
    • 输入的字符串必须是十六进制数值，只能包含：数字、大小写 A ～ F
      • 可以解析所有 ASCII 字符（不可见字符不显示，扩展 ASCII 会显示为 ? ）
    • 将字符串按照 “十六进制 ASCII 码” 格式解析成字符，返回（返回 BINARY）
      • 这个方法的返回值并不是一个二进制数值，更像是一个字符串

  • hex(STRING a) 和 unhex(STRING a) 应该是互为相反的操作

  • conv(BIGINT num, INT from_base, INT to_base) 或 conv(STRING num, INT from_base, INT to_base)

    • 将 BIGINT 或 STRING 从指定的原进制，转换为目标进制（返回 String）

    • select conv(2, 10, 2), conv('10', 10, 8), conv(12, 10, 16);

    • 019

取模

pmod(INT a, INT b) 或 pmod(DOUBLE a, DOUBLE b)

和取模运算符 a % b 效果一样

a 除以 b 取余数

阶乘

factorial(INT a)

支持范围：[0, 20]

020

位移操作

  • shiftleft(TINYINT|SMALLINT|INT|BIGINT a, INT b)
    • 左移
  • shiftright(TINYINT|SMALLINT|INT|BIGINT a, INT b)
    • 右移
  • shiftrightunsigned(TINYINT|SMALLINT|INT|BIGINT a, INT b)
    • 无符号右移（逻辑右移）
select shiftleft(2, 1), shiftright(2, 1);
select shiftleft(-2, 1), shiftright(-2, 1);
select shiftrightunsigned(2, 1), shiftrightunsigned(-2, 1);

021

最大值、最小值

  • greatest(T v1, T v2, ...)
    • 最大值
  • least(T v1, T v2, ...)
    • 最小值
select greatest(1, 7, 2), least(1, 7, 2);

三角函数

  • sin(DOUBLE a) 或 sin(DECIMAL a)
    • 正弦
  • asin(DOUBLE a) 或 asin(DECIMAL a)
    • 反正弦
  • cos(DOUBLE a) 或 cos(DECIMAL a)
    • 余弦
  • acos(DOUBLE a) 或 acos(DECIMAL a)
    • 反余弦
  • tan(DOUBLE a) 或 tan(DECIMAL a)
    • 正切
  • atan(DOUBLE a) 或 atan(DECIMAL a)
    • 反正切
  • degrees(DOUBLE a) 或 degrees(DECIMAL a)
    • 弧度转角度
  • radians(DOUBLE a) 或 radians(DOUBLE a)
    • 角度转弧度

width_bucket 函数

  • width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value, INT num_buckets)
    • 作用未明
    • Hive 3.0.0 开始支持
    • Returns an integer between 0 and num_buckets+1 by mapping expr into the ith equally sized bucket. Buckets are made by dividing [min_value, max_value] into equally sized regions. If expr < min_value, return 1, if expr > max_value return num_buckets+1. See https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions214.htm (as of Hive 3.0.0)