使用函数查询(一)
使用函数查询
1.数学函数
返回值类型 | 样式 | 描述 |
|---|---|---|
BIGINT | round(DOUBLE d) | 返回DOUBLE型d的BIGINT类型的近似值 |
DOUBLE | round(DOUBLE d,INT n) | 返回DOUBLE型d的保留n位小数的DOUBLE型的近似值 |
BIGINT | floor(DOUBLE d) | d是DOUBLE类型的，返回<=d的最大BIGINT |
BIGINT | ceil(DOUBLE d)ceiling(DOUBLE d) | d是DOUBLE类型的,返回>=d的最小BIGINT |
DOUBLE | rand( ) rand(INT seed) | 每行返回一个 DOUBLE 型随机数，整数 seed 是随即因子 |
DOUBLE | exp(DOUBLE d) | 返回 e 的 d 幂次方，返回的是一个 DOUBLE 型值 |
DOUBLE | ln(DOUBLE d) | 以自然数为底 d 的对数，返回 DOUBLE 型值 |
DOUBLE | log10(DOUBLE d) | 以10为底 d 的对数，返回 DOUBLE 型值 |
DOUBLE | log2(DOUBLE d) | 以2为底 d 的对数，返回 DOUBLE 型值 |
DOUBLE | log(DOUBLE base,DOUBLE d) | 以 base 为底 d 的对数，返回 DOUBLE 型值,其中 base 和 d 都是DOUBLE型的 |
DOUBLE | pow(DOUBLE d,DOUBLE p)power(DOUBLE d,DOUBLE p) | 计算 d 的 p 次幂，返回 DOUBLE 型值，其中 d 和 p 都是 DOUBLE 型的 |
DOUBLE | sqrt(DOUBLE d) | 计算 d 的平方根，其中 d 是 DOUBLE 型的 |
STRING | bin(BIGINT i) | 计算二进制值 i 的 STRING 类型值，其中 i 是 BIGINT 类型的 |
STRING | hex(BIGINT i) | 计算十六进制值 i 的 STRING 类型值，其中 i 是 BIGINT 类型的 |
STRING | hex(STRING str) | 计算十六进制表达的值 str 的 STRING 类型值 |
STRING | hex(BINARY b) | 计算二进制表达的值 b 的 STRING 类型值 |
STRING | unhex(STRING i) | hex(STRING str)的逆方法 |
STRING | conv(BIGINT num,INT from_base,INT to_base) | 将 BIGINT 类型的 num 从 from_base 进制转换成 to_base 进制，并返回 STRING 类型结果 |
DOUBLE | abs(DOUBLE d) | 计算 DOUBLE 型值 d 的绝对值，返回结果也是 DOUBLE 型的 |
INT | pmod(INT i1,INT i2) | INT 值 i1 对 INT 值 i2 取模，结果也是 INT 型的 |
DOUBLE | pmod(DOUBLE d1,DOUBLE d2) | DOUBLE 值 d1 对 DOUBLE 值 d2 取模，结果也是 DOUBLE 型的 |
DOUBLE | sin(DOUBLE d) | 在弧度度量中，返回 DOUBLE 型值 d 的正弦值，结果是 DOUBLE 型的 |
DOUBLE | asin(DOUBLE d) | 在弧度度量中，返回 DOUBLE 型值 d 的反正弦值，结果是 DOUBLE 型的 |
DOUBLE | cos(DOUBLE d) | 在弧度度量中，返回 DOUBLE 型值 d 的余弦值，结果是 DOUBLE 型的 |
DOUBLE | acos(DOUBLE d) | 在弧度度量中，返回 DOUBLE 型值 d 的反余弦值，结果是 DOUBLE 型的 |
DOUBLE | tan(DOUBLE d) | 在弧度度量中，返回 DOUBLE 型值 d 的正切值，结果是 DOUBLE 型的 |
DOUBLE | atan(DOUBLE d) | 在弧度度量中，返回 DOUBLE 型值 d 的反正切值，结果是 DOUBLE 型的 |
DOUBLE | degrees(DOUBLE d) | 将 DOUBLE 型弧度值 d 转换成角度值，结果是 DOUBLE 型的 |
DOUBLE | radians(DOUBLE d) | 将 DOUBLE 型角度值 d 转换成弧度值，结果是 DOUBLE 型的 |
INT | positive(INT i) | 返回 INT 型值 i |
DOUBLE | positive(DOUBLE d) | 返回 DOUBLE 型值 d |
INT | negative(INT i) | 返回 INT 型值 i 的负数 |
DOUBLE | negative(DOUBLE d) | 返回 DOUBLE 型值 d 的负数 |
FLOAT | sign(DOUBLE d) | 如果 DOUBLE 型值 d 是正数的话，则返回 FLOAT 值1.0；如果 d 是负数的话，则返回-1.0；否则返回0.0 |
DOUBLE | e( ) | 数字常数 e ,也就是超越数的 DOUBLE 型值 |
DOUBLE | pi( ) | 数字常数pi,也就是圆周率的 DOUBLE 型值 |
注：函数 floor,round 和 ceil 输入的是 DOUBLE 类型的值，而返回值是 BIGINT 类型的，将浮点型转换成整型。在进行数据类型转换时，这些函数是首选的处理方式，而不是用cast转换
2.聚合函数
聚合函数是一类比较特殊的函数，其可以对多行进行一些计算，然后得到一个结果值。
返回值类型 | 样式 | 描述 |
|---|---|---|
BIGINT | count(*) | 计算总行数，包括含有NULL值的行 |
BIGINT | count(expr) | 计算提供的 expr 表达式的值非NULL的行数 |
BIGINT | count(DISTINCT expr[, expr_.]) | 计算提供的 expr 表达式的值排重后非NULL的行数 |
DOUBLE | sum(col) | 计算指定行的值的和 |
DOUBLE | sum(DISTINCT col) | 计算排重后的值的和 |
DOUBLE | avg(col) | 计算指定行的值的平均值 |
DOUBLE | avg(DISTINCT col) | 计算排重后的值的平均值 |
DOUBLE | min(col) | 计算指定行的最小值 |
DOUBLE | max(col) | 计算指定行的最大值 |
DOUBLE | variance(col)var_pop(col) | 返回集合 col 中的一组数值的方差 |
DOUBLE | var_samp(col) | 返回集合 col 中的一组数值的样本方差 |
DOUBLE | stddev_pop(col) | 返回一组数值的标准偏差 |
DOUBLE | stddev_samp(col) | 返回一组数值的标准样本偏差 |
DOUBLE | covar_pop(col1,col2) | 返回一组数值的协方差 |
DOUBLE | covar_samp(col) | 返回一组数值的样本协方差 |
DOUBLE | corr(col1,col2) | 返回两组数值的相关系数 |
ARRAY | collect_set(col) | 返回集合col元素排重后的数组 |
前面文章我们有建过 employees 雇员表，下面我们直接操作此表，忘记或者前面没看的小伙伴可以翻翻前面的文章哈
eg: hive>SELECT count(*),avg(salary) FROM employees;
4 77500.0
3.表生成函数
表生成函数是与聚合函数“相反的”一类函数，其可以单列扩展成多列或者多行。
下面我们通过一个例子来进行讲解，如下的语句将 employees 表中每行记录中的 subordinates 字段内容转换成0个或者多个新纪录，如果某员工 subordinates 字段为空，如果不为空，那么这个数组的每个元素都将产生一行新记录
eg: hive>SELECT explode(subordinates) AS sub FROM employees;
Mary Smith
Todd Jones
Bill King
返回值类型 | 样式 | 描述 |
|---|---|---|
N行结果 | explode(ARRAY array) | 返回0到多行结果，每行对应输入的array数组中的一个元素 |
N行结果 | explode(MAP map) | 返回0到多行结果，每行对应每个map键-值对，其中一个字段是map的键，另一个字段对应map的值 |
数组的类型 | explode(ARRAY<TYPE> a) | 对于a中的每个元素，explode（）会生成一行记录包含这个元素 |
朝辞秋虎夜乘凉 处暑禾田始换装
一场秋雨 一场寒