Hive系列（二）—— 常用函数汇总

初稿 2020-08-27

Hive 函数比较多，之前总是随用随查，今天把常用的函数总结一下，以后方便查阅。

前言
Hive内部提供了很多函数给开发者使用，包括数学函数，类型转换函数，条件函数，字符函数，聚合函数，表生成函数等等，这些函数都统称为内置函数。
如图所示：

数学函数

1. round()

   #返回对a四舍五入的bigint值
   SELECT round(1.5)        # 输出  2
   SELECT round(-1.5)        # 输出 -2 该四舍五入计算方式为：绝对值四舍五入加负号
   
   #返回DOUBLE型d的保留n位小数的DOUBLW型的近似值
   select round(6.58354,3)		#输出	6.584
   

2. bround()：

   Returns the rounded BIGINT value of a using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Also known as Gaussian rounding or bankers’ rounding.

   银行家舍入法（1-4：舍，6~9：进，5->前位数是偶：舍，5->前位数是奇：进）

   select bround(2.5) 		#输出	2 
   bround(3.5) 			#输出	4
   

   Returns a rounded to d decimal places using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0).

   银行家舍入法，保留d位小数

   select bround(8.25, 1) 	#输出	8.2 
   select bround(8.35, 1) 	#输出	8.4
   

3. floor()： 舍1
   向下取整，返回<=该参数的最大整数

   SELECT FLOOR(1.5);        # 输出  1
   SELECT FLOOR(-1.5);        # 输出 -2
   

4. ceil()，ceiling()：进一
   向上取整,返回>=该参数的最小整数

   SELECT CEIL(1.5);        # 输出  2
   SELECT CEIL(-1.5);        # 输出 -1
   

5. rand(), rand(INT seed)：每行返回一个DOUBLE型随机数，seed是随机因子

   种子seed相同，会出现伪随机现象，即每次生成的随机数相同。

   # 多次执行，结果相同，为0.7308781907032909
   select rand(1)
   

6. exp(DOUBLE a), exp(DECIMAL a)：返回e的a幂次方， a可为小数

7. ln(DOUBLE a), ln(DECIMAL a)：以自然数为底的a对数，a可为小数

8. log10(DOUBLE a), log10(DECIMAL a)：以10为底a的对数，a可为小数

9. log2(DOUBLE a), log2(DECIMAL a)：以2为底数a的对数，a可为小数

10. log(DOUBLE base, DOUBLE a)，log(DECIMAL base, DECIMAL a)：以base为底a的对数，base 与 a都是DOUBLE类型

11. pow(DOUBLE a, DOUBLE p)，power(DOUBLE a, DOUBLE p)：计算a的p次幂

12. sqrt(DOUBLE a), sqrt(DECIMAL a)：计算a的平方根

13. bin(BIGINT a)：计算二进制a的STRING类型，a为BIGINT类型

    select bin(54)	#输出	110110
    

14. hex(BIGINT a)，hex(STRING a)，hex(BINARY a)：计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制

    select hex(54)			#输出	36
    select hex('bigdata')	#输出	62696764617461
    

15. unhex(STRING a)：hex的逆方法

    select unhex(62696764617461)	#输出	bigdata
    

16. conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)：将GIGINT/STRING类型的num从from_base进制转换成to_base进制

    SELECT CONV('a',16,2)	输出1010
    

17. abs(DOUBLE a)：计算a的绝对值

18. pmod(INT a, INT b)，pmod(DOUBLE a, DOUBLE b)：a对b取模

    select mod(-9,4)	#输出	3
    

19. sin(DOUBLE a), sin(DECIMAL a)：求a的正弦值

20. asin(DOUBLE a), asin(DECIMAL a)：求a的反正弦值

21. cos(DOUBLE a), cos(DECIMAL a)：求余弦值

22. acos(DOUBLE a), acos(DECIMAL a)：求反余弦值

23. tan(DOUBLE a), tan(DECIMAL a)：求正切值

24. atan(DOUBLE a), atan(DECIMAL a)：求反正切值

25. degrees(DOUBLE a), degrees(DECIMAL a)：将弧度值转换角度值

26. radians(DOUBLE a), radians(DOUBLE a)：将角度值转换成弧度值

27. positive(INT a), positive(DOUBLE a)：返回a

28. negative(INT a), negative(DOUBLE a)：返回a的相反数

29. sign(DOUBLE a), sign(DECIMAL a)：如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0；就是数学里的符号函数

30. e()：数学常数e

31. pi()：数学常数pi

32. factorial(INT a)：求a的阶乘

    Returns the factorial of a (as of Hive 1.2.0). Valid a is [0…20].

33. cbrt(DOUBLE a)：求a的立方根

    Returns the cube root of a double value (as of Hive 1.2.0).

34. shiftleft(TINYINT|SMALLINT|INT a, INT b)，shiftleft(BIGINT a, INT b)：按位左移

    Bitwise left shift (as of Hive 1.2.0). Shifts a b positions to the left.
    Returns int for tinyint, smallint and int a. Returns bigint for bigint a.

35. shiftright(TINYINT|SMALLINT|INT a, INTb)，shiftright(BIGINT a, INT b)：按拉右移

    Bitwise right shift (as of Hive 1.2.0). Shifts a b positions to the right.
    Returns int for tinyint, smallint and int a. Returns bigint for bigint a.

36. shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b)，shiftrightunsigned(BIGINT a, INT b)：无符号按位右移（<<<）

Bitwise unsigned right shift (as of Hive 1.2.0). Shifts a b positions to the right.
Returns int for tinyint, smallint and int a. Returns bigint for bigint a.

37. greatest(T v1, T v2, …)：求最大值

    Returns the greatest value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with “>” operator (as of Hive 2.0.0).

select greatest(12,34,56)	#输出	56

38. least(T v1, T v2, …)：求最小值

    Returns the least value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with “<” operator (as of Hive 2.0.0).

集合函数

1. size(Map<K.V>)、size(Array)：返回map或者数组的长度

   # array		长度	3
   select size(array('2018',2348,'2019',948534,'2020',9483543))
   
   # map		长度	3
   select size(map('2018',2348,'2019',948534,'2020',9483543))
   

2. map_keys(Map<K.V>)：返回map中的所有key

   select map_keys(map('2018',2348,'2019',948534,'2020',943543))
   
   结果：
   ["2018","2019","2020"]
   

3. map_values(Map<K.V>)：返回map中的所有value

   select map_values(map('2018',2348,'2019',948534,'2020',943543))
   
   结果：
   [2348,948534,943543]
   

4. array_contains(Array, value)：如该数组Array包含value返回true，否则返回false

   select array_contains(array('aa','bb','cc'),'cc')
   
   结果：
   true
   

5. sort_array(Array) ：按自然顺序对数组进行排序并返回（升序）

   select sort_array(array(4,46,15,6,84,64,89