目录
- 1.round(DOUBLE a)
- 2.round(DOUBLE a, INT d)
- 3.bround(DOUBLE a)
- 4.bround(DOUBLE a, INT d)
- 5.floor(DOUBLE a)
- 6.ceil(DOUBLE a), ceiling(DOUBLE a)
- 7. rand(), rand(INT seed)
- 8.exp(DOUBLE a), exp(DECIMAL a)
- 9.ln(DOUBLE a), ln(DECIMAL a)
- 10.log10(DOUBLE a), log10(DECIMAL a)
- 11. log2(DOUBLE a), log2(DECIMAL a)
- 12.log(DOUBLE base, DOUBLE a),log(DECIMAL base, DECIMAL a)
- 13.pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)
- 14.sqrt(DOUBLE a), sqrt(DECIMAL a)
- 15. bin(BIGINT a)
- 16.hex(BIGINT a) hex(STRING a) hex(BINARY a)
- 17.unhex(STRING a)
- 18.conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)
- 19.abs(DOUBLE a)
- 20.pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)
- 21. sin(DOUBLE a), sin(DECIMAL a)
- 22.asin(DOUBLE a), asin(DECIMAL a)
- 23. cos(DOUBLE a), cos(DECIMAL a)
- 24.acos(DOUBLE a), acos(DECIMAL a)
- 25.tan(DOUBLE a), tan(DECIMAL a)
- 26.atan(DOUBLE a), atan(DECIMAL a)
- 27.degrees(DOUBLE a), degrees(DECIMAL a)
- 28.radians(DOUBLE a), radians(DOUBLE a)
- 29.positive(INT a), positive(DOUBLE a)
- 30.negative(INT a), negative(DOUBLE a)
- 31.sign(DOUBLE a), sign(DECIMAL a)
- 32.e()
- 33.pi()
- 34.factorial(INT a)
- 35.cbrt(DOUBLE a)
- 36.shiftleft(TINYINT|SMALLINT|INT a, INT b) shiftleft(BIGINT a, INT b)
- 37.shiftright(TINYINT|SMALLINT|INT a, INT b) shiftright(BIGINT a, INT b)
- 38.shiftrightunsigned(TINYINT|SMALLINT|INT a, INT b), shiftrightunsigned(BIGINT a, INT b)
- 39.greatest(T v1, T v2, ...)
- 40.least(T v1, T v2, ...)
- 41. width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value, INT num_buckets)
- 参考
1.round(DOUBLE a)
返回值:DOUBLE
功能:返回a的四舍五入后的BIGINT值
示例:
> select round(2.334) as f1;
+-----+
| f1 |
+-----+
| 2 |
+-----+
2.round(DOUBLE a, INT d)
返回值:DOUBLE
功能:返回a的四舍五入的值,保留小数点后d位
示例:
> select round(2.334678,3) as f1;
+--------+
| f1 |
+--------+
| 2.335 |
+--------+
3.bround(DOUBLE a)
返回值:DOUBLE
功能:返回使用半偶数舍入模式(也称为高斯舍入或银行家舍入)的舍入BIGINT值
示例:
> select bround(2.5) as f1;
+-----+
| f1 |
+-----+
| 2 |
+-----+
> select bround(2.6) as f1;
+-----+
| f1 |
+-----+
| 3 |
+-----+
> select bround(3.5) as f1;
+-----+
| f1 |
+-----+
| 4 |
+-----+
说明:银行家舍入法,其实质是一种四舍六入五取偶(又称四舍六入五留双)法。其规则是:当舍去位的数值小于5时,直接舍去该位;当舍去位的数值大于等于6时,在舍去该位的同时向前位进一;当舍去位的数值等于5且(5后不为空且非全0)时,在舍去该位的同时向前位进一;当舍去的数值等于5且(5后为空或全0)时,如果前位数值为奇,则在舍去该位的同时向前位进一,如果前位数值为偶,则直接舍去该位。简单的说,就是:四舍六入五考虑,五后非空就进一,五后为空看奇偶,五前为偶应舍去,五前为奇要进一 (来自百度百科)
4.bround(DOUBLE a, INT d)
返回值:DOUBLE
功能:返回使用半偶数舍入模式(也称为高斯舍入或银行家舍入)的舍入值,保留小数点后d位
示例:
> select bround(3.535,2) as f1;
+-------+
| f1 |
+-------+
| 3.54 |
+-------+
> select bround(3.545,2) as f1;
+-------+
| f1 |
+-------+
| 3.54 |
+-------+
5.floor(DOUBLE a)
返回值:BIGINT
功能:向下取整,返回小于或等于a的BIGINT值
示例:
> select floor(8.9) as f1;
+-----+
| f1 |
+-----+
| 8 |
+-----+
> select floor(-5.7) as f1;
+-----+
| f1 |
+-----+
| -6 |
+-----+
6.ceil(DOUBLE a), ceiling(DOUBLE a)
返回值:BIGINT
功能:向上取整,返回大于或者等于a的最小整数
示例:
> select ceil(-5.7) as f1;
+-----+
| f1 |
+-----+
| -5 |
+-----+
> select ceiling(8.1) as f1;
+-----+
| f1 |
+-----+
| 9 |
+-----+
7. rand(), rand(INT seed)
返回值:DOUBLE
功能:返回从0到1均匀分布的随机数,指定seed将确保生成的随机数序列是确定的
示例:
> select rand() as f1;
+----------------------+
| f1 |
+----------------------+
| 0.46754666692966473 |
+----------------------+
> select rand(8) as f1;
+---------------------+
| f1 |
+---------------------+
| 0.7302511331990172 |
+---------------------+
8.exp(DOUBLE a), exp(DECIMAL a)
返回值:DOUBLE
功能:返回e的a次幂,其中e是自然对数的底
示例:
> select exp(2) as f1;
+-------------------+
| f1 |
+-------------------+
| 7.38905609893065 |
+-------------------+
9.ln(DOUBLE a), ln(DECIMAL a)
返回值:DOUBLE
功能:返回参数a的自然对数。
示例:
> select ln(2) as f1;
+---------------------+
| f1 |
+---------------------+
| 0.6931471805599453 |
+---------------------+
10.log10(DOUBLE a), log10(DECIMAL a)
返回值:DOUBLE
功能:返回参数a的以10为底的对数
示例:
> select log10(2) as f1;
+---------------------+
| f1 |
+---------------------+
| 0.3010299956639812 |
+---------------------+
11. log2(DOUBLE a), log2(DECIMAL a)
返回值:DOUBLE
功能:返回参数a的以2为底的对数
示例:
> select log2(2) as f1;
+------+
| f1 |
+------+
| 1.0 |
+------+
12.log(DOUBLE base, DOUBLE a),log(DECIMAL base, DECIMAL a)
返回值:DOUBLE
功能:返回参数a的以base为底的对数
示例:
> select log(2,2) as f1;
+------+
| f1 |
+------+
| 1.0 |
+------+
> select log(10,2) as f1;
+----------------------+
| f1 |
+----------------------+
| 0.30102999566398114 |
+----------------------+
13.pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)
返回值:DOUBLE
功能:返回a 的p次幂
示例:
> select pow(10,2) as f1;
+--------+
| f1 |
+--------+
| 100.0 |
+--------+
14.sqrt(DOUBLE a), sqrt(DECIMAL a)
返回值:DOUBLE
功能:返回a的平方根
示例:
> select sqrt(4) as f1;
+------+
| f1 |
+------+
| 2.0 |
+------+
15. bin(BIGINT a)
返回值:STRING
功能:返回a的二进制数
示例:
> select bin(9) as f1;
+-------+
| f1 |
+-------+
| 1001 |
+-------+
16.hex(BIGINT a) hex(STRING a) hex(BINARY a)
返回值:STRING
功能:返回a十六进制数,如果a是string类型就将每个字符依次转换为十六进制
示例:
> select hex(90) as f1;
+-----+
| f1 |
+-----+
| 5A |
+-----+
> select hex('abc') as f1;
+---------+
| f1 |
+---------+
| 616263 |
+---------+
17.unhex(STRING a)
返回值:BINARY
功能:hex的逆方法
示例:
> select unhex('616263') as f1;
+------+
| f1 |
+------+
| abc |
+------+
18.conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)
返回值:STRING
功能:把num从指定的进制 from_base转换为另一个进制to_base。见 https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_conv
示例:
> select conv('a',16,2) as f1;
+-------+
| f1 |
+-------+
| 1010 |
+-------+
> select conv('6e',18,8) as f1;
+------+
| f1 |
+------+
| 172 |
+------+
19.abs(DOUBLE a)
返回值:DOUBLE
功能:返回a的绝对值
示例:
> select abs(-18) as f1;
+-----+
| f1 |
+-----+
| 18 |
+-----+
20.pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)
返回值:INT或者DOUBLE
功能:返回a对b取模的值
示例:
> SELECT pmod(8,9) as f1;
+-----+
| f1 |
+-----+
| 8 |
+-----+
> SELECT pmod(8.1,2.3) as f1;
+------+
| f1 |
+------+
| 1.2 |
+------+
21. sin(DOUBLE a), sin(DECIMAL a)
返回值:DOUBLE
功能:返回a的正弦值
示例:
> SELECT sin(90) as f1;
+---------------------+
| f1 |
+---------------------+
| 0.8939966636005579 |
+---------------------+
22.asin(DOUBLE a), asin(DECIMAL a)
返回值:DOUBLE
功能:返回a的反正弦值,-1<=a<=1。否则返回的是NULL
示例:
> SELECT asin(0.5) as f1;
+---------------------+
| f1 |
+---------------------+
| 0.5235987755982989 |
+---------------------+
> SELECT asin(1.5) as f1;
+-------+
| f1 |
+-------+
| NULL |
+-------+
23. cos(DOUBLE a), cos(DECIMAL a)
返回值:DOUBLE
功能:返回a的余弦值
示例:
> SELECT cos(90) as f1;
+----------------------+
| f1 |
+----------------------+
| -0.4480736161291702 |
+----------------------+
24.acos(DOUBLE a), acos(DECIMAL a)
返回值:DOUBLE
功能:返回a的反余弦值,-1<=a<=1。否则返回的是NULL
示例:
> SELECT acos(0.63) as f1;
+---------------------+
| f1 |
+---------------------+
| 0.8892431152317797 |
+---------------------+
25.tan(DOUBLE a), tan(DECIMAL a)
返回值:DOUBLE
功能:返回a的正切值
示例:
> SELECT tan(90) as f1;
+---------------------+
| f1 |
+---------------------+
| -1.995200412208242 |
+---------------------+
26.atan(DOUBLE a), atan(DECIMAL a)
返回值:DOUBLE
功能:返回a的反正切值
示例:
> SELECT atan(90) as f1;
+---------------------+
| f1 |
+---------------------+
| 1.5596856728972892 |
+---------------------+
27.degrees(DOUBLE a), degrees(DECIMAL a)
返回值:DOUBLE
功能:将弧度值转换为角度值
示例:
> SELECT degrees(pi()) as f1;
+--------+
| f1 |
+--------+
| 180.0 |
+--------+
28.radians(DOUBLE a), radians(DOUBLE a)
返回值:DOUBLE
功能:将角度值转换为弧度值
示例:
> SELECT radians(180) as f1;
+--------------------+
| f1 |
+--------------------+
| 3.141592653589793 |
+--------------------+
29.positive(INT a), positive(DOUBLE a)
返回值:INT 或 DOUBLE
功能:返回a
示例:
> SELECT positive(23) as f1;
+-----+
| f1 |
+-----+
| 23 |
+-----+
30.negative(INT a), negative(DOUBLE a)
返回值:INT 或 DOUBLE
功能:返回-a
示例:
> SELECT negative(23) as f1;
+------+
| f1 |
+------+
| -23 |
+------+
31.sign(DOUBLE a), sign(DECIMAL a)
返回值:INT 或 DOUBLE
功能:如果a为正则返回1.0,为负则返回-1.0,否则返回0.0
示例:
> SELECT sign(23) as f1;
+------+
| f1 |
+------+
| 1.0 |
+------+
> SELECT sign(-23) as f1;
+-------+
| f1 |
+-------+
| -1.0 |
+-------+
> SELECT sign(0) as f1;
+------+
| f1 |
+------+
| 0.0 |
+------+
32.e()
返回值:DOUBLE
功能:返回e的值
示例:
> SELECT e() as f1;
+--------------------+
| f1 |
+--------------------+
| 2.718281828459045 |
+--------------------+
33.pi()
返回值:DOUBLE
功能:返回pi的值
示例:
> SELECT pi() as f1;
+--------------------+
| f1 |
+--------------------+
| 3.141592653589793 |
+--------------------+
34.factorial(INT a)
返回值:BIGINT
功能:返回a的阶乘,a的取值范围是 [0…20]
示例:
> SELECT factorial(5) as f1;
+------+
| f1 |
+------+
| 120 |
+------+
35.cbrt(DOUBLE a)
返回值:DOUBLE
功能:返回a的立方根
示例:
> SELECT cbrt(8) as f1;
+------+
| f1 |
+------+
| 2.0 |
+------+
36.shiftleft(TINYINT|SMALLINT|INT a, INT b) shiftleft(BIGINT a, INT b)
返回值:INT 或 DOUBLE,对于tinyint, smallint,int类型的a返回int,对于bigint类型返回bigint
功能:将a按位左移b位
示例:
> SELECT shiftleft(8,2) as f1;
+-----+
| f1 |
+-----+
| 32 |
+-----+
37.shiftright(TINYINT|SMALLINT|INT a, INT b) shiftright(BIGINT a, INT b)
返回值:INT 或 DOUBLE,对于tinyint, smallint,int类型的a返回int,对于bigint类型返回bigint
功能:将a按位右移b位
示例:
> SELECT shiftright(8,2) as f1;
+-----+
| f1 |
+-----+
| 2 |
+-----+
38.shiftrightunsigned(TINYINT|SMALLINT|INT a, INT b), shiftrightunsigned(BIGINT a, INT b)
返回值:INT 或 DOUBLE,对于tinyint, smallint,int类型的a返回int,对于bigint类型返回bigint
功能:将a无符号按位右移b位
示例:
> SELECT shiftrightunsigned(8,6) as f1;
+-----+
| f1 |
+-----+
| 0 |
+-----+
39.greatest(T v1, T v2, …)
返回值:T
功能:返回参数列表中的最大值
示例:
> SELECT greatest(8,6,111.23,90) as f1;
+---------+
| f1 |
+---------+
| 111.23 |
+---------+
40.least(T v1, T v2, …)
返回值:T
功能:返回参数列表中的最小值
示例:
> SELECT least(8,6,111.23,90) as f1;
+-------+
| f1 |
+-------+
| 6.00 |
+-------+
41. width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value, INT num_buckets)
返回值:INT
功能:Hive 3.0.0 新增方法。根据参数设置,返回当前记录expr所属的bucket number;第二个参数min_value为某范围的下限;第三个参数为某范围max_value的上限;第四个参数 num_buckets为对范围进行等值划分bucket的数量。如果 expr < min_value, 则返回 1, 如果 expr > max_value 则返回 num_buckets+1。见 https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions214.htm
示例:
> select name,width_bucket(age,16,35,4) agegroup from students limit 5;
+------------------+-----------+
| name | agegroup |
+------------------+-----------+
| barney rubble | 4 |
| fred flintstone | 5 |
| hehe | 1 |
| haha | 5 |
+------------------+-----------+
参考
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-MathematicalFunctions
https://www.cnblogs.com/MOBIN/p/5618747.html#1