hive函数大全：数值与数学相关函数

迈不过友情╰ 2022-05-30 05:25 507阅读 0赞

### hive函数官网文档：[hive函数官网文档][hive] ###

### 1.Hive所有内嵌数学函数 ###

<table> 
 <thead> 
 <tr> 
 <td style="vertical-align:middle;"> Return Type </td> 
 <th> Name (Signature) </th> 
 <th> Description </th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td> DOUBLE </td> 
 <td> round(DOUBLE a) </td> 
 <td> Returns the rounded&nbsp;<code>BIGINT</code>&nbsp;value of&nbsp;<code>a</code>. 返回对a四舍五入的BIGINT值 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> round(DOUBLE a, INT d) </td> 
 <td> Returns&nbsp;<code>a</code>&nbsp;rounded to&nbsp;<code>d</code>&nbsp;decimal places. 返回DOUBLE型d的保留n位小数的DOUBLW型的近似值 </td> 
 </tr> 
 <tr> 
 <td colspan="1">DOUBLE</td> 
 <td colspan="1">bround(DOUBLE a)</td> 
 <td colspan="1">Returns the rounded BIGINT value of&nbsp;<code>a</code>&nbsp;using HALF_EVEN rounding mode (as of&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-11103" rel="nofollow">Hive 1.3.0, 2.0.0</a>). Also known as Gaussian rounding or bankers' rounding. Example: bround(2.5) = 2, bround(3.5) = 4. 银行家舍入法（1~4：舍，6~9：进，5-&gt;前位数是偶：舍，5-&gt;前位数是奇：进）</td> 
 </tr> 
 <tr> 
 <td colspan="1">DOUBLE</td> 
 <td colspan="1">bround(DOUBLE a, INT d)</td> 
 <td colspan="1">Returns&nbsp;<code>a</code>&nbsp;rounded to&nbsp;<code>d</code>&nbsp;decimal places using HALF_EVEN rounding mode (as of&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-11103" rel="nofollow">Hive 1.3.0, 2.0.0</a>). Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4. 银行家舍入法,保留d位小数</td> 
 </tr> 
 <tr> 
 <td> BIGINT </td> 
 <td> floor(DOUBLE a) </td> 
 <td> Returns the maximum&nbsp;<code>BIGINT</code>&nbsp;value that is equal to or less than&nbsp;<code>a</code> 向下取整，最数轴上最接近要求的值的左边的值 &nbsp;如：6.10-&gt;6 &nbsp; -3.4-&gt;-4 </td> 
 </tr> 
 <tr> 
 <td> BIGINT </td> 
 <td> ceil(DOUBLE a), ceiling(DOUBLE a) </td> 
 <td> Returns the minimum BIGINT value that is equal to or greater than&nbsp;<code>a</code>. 求其不小于小给定实数的最小整数如：ceil(6) = ceil(6.1)= ceil(6.9) = 6 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> rand(), rand(INT seed) </td> 
 <td> Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the seed will make sure the generated random number sequence is deterministic. 每行返回一个DOUBLE型随机数seed是随机因子 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> exp(DOUBLE a), exp(DECIMAL a) </td> 
 <td> Returns&nbsp;<code>ea</code>&nbsp;where&nbsp;<code>e</code>&nbsp;is the base of the natural logarithm. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 返回e的a幂次方， a可为小数 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> ln(DOUBLE a), ln(DECIMAL a) </td> 
 <td> Returns the natural logarithm of the argument&nbsp;<code>a</code>. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 以自然数为底d的对数，a可为小数 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> log10(DOUBLE a), log10(DECIMAL a) </td> 
 <td> Returns the base-10 logarithm of the argument&nbsp;<code>a</code>. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 以10为底d的对数，a可为小数 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> log2(DOUBLE a), log2(DECIMAL a) </td> 
 <td> Returns the base-2 logarithm of the argument&nbsp;<code>a</code>. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 以2为底数d的对数，a可为小数 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> log(DOUBLE base, DOUBLE a) log(DECIMAL base, DECIMAL a) </td> 
 <td> Returns the base-<code>base</code>&nbsp;logarithm of the argument&nbsp;<code>a</code>.&nbsp;Decimal versions added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 以base为底的对数，base 与 a都是DOUBLE类型 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) </td> 
 <td> Returns&nbsp;<code>ap</code>. 计算a的p次幂 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> sqrt(DOUBLE a), sqrt(DECIMAL a) </td> 
 <td> Returns the square root of&nbsp;<code>a</code>. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 计算a的平方根 </td> 
 </tr> 
 <tr> 
 <td> STRING </td> 
 <td> bin(BIGINT a) </td> 
 <td> Returns the number in binary format (see&nbsp;<a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_bin" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_bin</a>). 计算二进制a的STRING类型，a为BIGINT类型 </td> 
 </tr> 
 <tr> 
 <td> STRING </td> 
 <td> hex(BIGINT a) hex(STRING a) hex(BINARY a) </td> 
 <td> If the argument is an&nbsp;<code>INT</code>&nbsp;or&nbsp;<code>binary</code>,&nbsp;<code>hex</code>&nbsp;returns the number as a&nbsp;<code>STRING</code>&nbsp;in hexadecimal format. Otherwise if the number is a&nbsp;<code>STRING</code>, it converts each character into its hexadecimal representation and returns the resulting&nbsp;<code>STRING</code>. (See<a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex</a>,&nbsp;<code>BINARY</code>&nbsp;version as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2482" rel="nofollow">0.12.0</a>.) 计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制 </td> 
 </tr> 
 <tr> 
 <td> BINARY </td> 
 <td> unhex(STRING a) </td> 
 <td> Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number. (<code>BINARY</code>&nbsp;version as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-2482" rel="nofollow">0.12.0</a>, used to return a string.) hex的逆方法 </td> 
 </tr> 
 <tr> 
 <td> STRING </td> 
 <td> conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base) </td> 
 <td> Converts a number from a given base to another (see&nbsp;<a href="http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv" rel="nofollow">http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv</a>). 将GIGINT/STRING类型的num从from_base进制转换成to_base进制 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> abs(DOUBLE a) </td> 
 <td> Returns the absolute value. 计算a的绝对值 </td> 
 </tr> 
 <tr> 
 <td> INT or DOUBLE </td> 
 <td> pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b) </td> 
 <td> Returns the positive value of&nbsp;<code>a mod b</code>. a对b取模 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> sin(DOUBLE a), sin(DECIMAL a) </td> 
 <td> Returns the sine of&nbsp;<code>a</code>&nbsp;(<code>a</code>&nbsp;is in radians). Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 求a的正弦值 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> asin(DOUBLE a), asin(DECIMAL a) </td> 
 <td> Returns the arc sin of&nbsp;<code>a</code>&nbsp;if -1&lt;=a&lt;=1 or NULL otherwise. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 求d的反正弦值 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> cos(DOUBLE a), cos(DECIMAL a) </td> 
 <td> Returns the cosine of&nbsp;<code>a</code>&nbsp;(<code>a</code>&nbsp;is in radians). Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 求余弦值 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> acos(DOUBLE a), acos(DECIMAL a) </td> 
 <td> Returns the arccosine of&nbsp;<code>a</code>&nbsp;if -1&lt;=a&lt;=1 or NULL otherwise. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 求反余弦值 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> tan(DOUBLE a), tan(DECIMAL a) </td> 
 <td> Returns the tangent of&nbsp;<code>a</code>&nbsp;(<code>a</code>&nbsp;is in radians). Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 求正切值 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> atan(DOUBLE a), atan(DECIMAL a) </td> 
 <td> Returns the arctangent of&nbsp;<code>a</code>. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 求反正切值 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> degrees(DOUBLE a), degrees(DECIMAL a) </td> 
 <td> Converts value of&nbsp;<code>a</code>&nbsp;from radians to degrees. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6385" rel="nofollow">Hive 0.13.0</a>. 奖弧度值转换角度值 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> radians(DOUBLE a), radians(DOUBLE a) </td> 
 <td> Converts value of&nbsp;<code>a</code>&nbsp;from degrees to radians. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>. 将角度值转换成弧度值 </td> 
 </tr> 
 <tr> 
 <td> INT or DOUBLE </td> 
 <td> positive(INT a), positive(DOUBLE a) </td> 
 <td> Returns&nbsp;<code>a</code>. 返回a </td> 
 </tr> 
 <tr> 
 <td> INT or DOUBLE </td> 
 <td> negative(INT a), negative(DOUBLE a) </td> 
 <td> Returns&nbsp;<code>-a</code>. 返回a的相反数 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE or INT </td> 
 <td> sign(DOUBLE a), sign(DECIMAL a) </td> 
 <td> Returns the sign of&nbsp;<code>a</code>&nbsp;as '1.0' (if&nbsp;<code>a</code>&nbsp;is positive) or '-1.0' (if&nbsp;<code>a</code>&nbsp;is negative), '0.0' otherwise. The decimal version returns INT instead of DOUBLE. Decimal version added in&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-6246" rel="nofollow">Hive 0.13.0</a>. 如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0 </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> e() </td> 
 <td> Returns the value of&nbsp;<code>e</code>. 数学常数e </td> 
 </tr> 
 <tr> 
 <td> DOUBLE </td> 
 <td> pi() </td> 
 <td> Returns the value of&nbsp;<code>pi</code>. 数学常数pi </td> 
 </tr> 
 <tr> 
 <td colspan="1">BIGINT</td> 
 <td colspan="1">factorial(INT a)</td> 
 <td colspan="1">Returns the factorial of&nbsp;<code>a</code>&nbsp;(as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9857" rel="nofollow">1.2.0</a>). Valid&nbsp;<code>a</code>&nbsp;is [0..20]. 求a的阶乘</td> 
 </tr> 
 <tr> 
 <td colspan="1">DOUBLE</td> 
 <td colspan="1">cbrt(DOUBLE a)</td> 
 <td colspan="1">Returns the cube root of&nbsp;<code>a</code>&nbsp;double value&nbsp;(as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9858" rel="nofollow">1.2.0</a>). 求a的立方根</td> 
 </tr> 
 <tr> 
 <td colspan="1"> &nbsp; INT BIGINT </td> 
 <td colspan="1"> shiftleft(TINYINT|SMALLINT|INT a, INT b) shiftleft(BIGINT a, INT&nbsp;b) </td> 
 <td colspan="1"> Bitwise left shift (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9859" rel="nofollow">1.2.0</a>). Shifts&nbsp;<code>a</code>&nbsp;<code>b</code>&nbsp;positions to the left. Returns int for tinyint, smallint and int&nbsp;<code>a</code>. Returns bigint for bigint&nbsp;<code>a</code>. 按位左移 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> INT BIGINT </td> 
 <td colspan="1"> shiftright(TINYINT|SMALLINT|INT a, INTb) shiftright(BIGINT a, INT&nbsp;b) </td> 
 <td colspan="1"> Bitwise right shift (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9859" rel="nofollow">1.2.0</a>). Shifts&nbsp;<code>a</code>&nbsp;<code>b</code>&nbsp;positions to the right. Returns int for tinyint, smallint and int&nbsp;<code>a</code>. Returns bigint for bigint&nbsp;<code>a</code>. 按拉右移 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> INT BIGINT </td> 
 <td colspan="1"> shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b), shiftrightunsigned(BIGINT a, INT b) </td> 
 <td colspan="1"> Bitwise unsigned right shift (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9859" rel="nofollow">1.2.0</a>).&nbsp;Shifts&nbsp;<code>a</code>&nbsp;<code>b</code>&nbsp;positions to the right. Returns int for tinyint, smallint and int&nbsp;<code>a</code>. Returns bigint for bigint&nbsp;<code>a</code>. 无符号按位右移（&lt;&lt;&lt;） </td> 
 </tr> 
 <tr> 
 <td colspan="1">T</td> 
 <td colspan="1">greatest(T v1, T v2, ...)</td> 
 <td colspan="1">Returns the greatest value of the list of values (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9402" rel="nofollow">1.1.0</a>). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with "&gt;" operator (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-12082" rel="nofollow">2.0.0</a>). 求最大值</td> 
 </tr> 
 <tr> 
 <td>T</td> 
 <td>least(T v1, T v2, ...)</td> 
 <td>Returns the least value of the list of values (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-9402" rel="nofollow">1.1.0</a>).&nbsp;Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with "&lt;" operator (as of Hive&nbsp;<a href="https://issues.apache.org/jira/browse/HIVE-12082" rel="nofollow">2.0.0</a>). 求最小值</td> 
 </tr> 
 </tbody> 
</table>

### **2.常用数值计算函数演示** ###

**1. 取整函数: round**  
语法: round(double a)  
返回值: BIGINT  
说明: **返回double类型的整数值部分 （遵循四舍五入）**  
举例：  
hive> select round(3.1415926) from t\_fin\_demo;  
         3  
hive> select round(3.5) from t\_fin\_demo;  
         4  
hive> create table t\_fin\_demo as select round(9542.158) from t\_fin\_demo;  
hive> describe t\_fin\_demo;  
         \_c0     bigint  
  
**2. 指定精度取整函数: round**  
语法: round(double a, int d)  
返回值: DOUBLE  
**说明: 返回指定精度d的double类型**  
举例：  
hive> select round(3.1415926,4) from t\_fin\_demo;  
         3.1416  
**3. 向下取整函数: floor**  
语法: floor(double a)  
返回值: BIGINT  
说明: 返回等于或者小于该double变量的最大的整数

hive> select floor(3.1415926) from t_fin_demo;
               3
    hive> select floor(25) from t_fin_demo;
             25
    
    hive> select  floor(-25.1233) from t_fin_demo;  
    
             -26

**4. 向上取整函数: ceil,与ceiling一样**  
语法: ceil(double a)  
返回值: BIGINT  
说明: **返回等于或者大于该double变量的最小的整数**  
举例：

hive> select ceil(3.1415926) from t_fin_demo;
              4
    hive> select ceil(46) from t_fin_demo;
              46
    
    hive> select  ceil(-25.1233) from t_fin_demo; 
    
           -25

**5. 向上取整函数: ceiling**  
语法: ceiling(double a)  
返回值: BIGINT  
说明: 与ceil功能相同  
举例：  
hive> select ceiling(3.1415926) from t\_fin\_demo;  
        4  
hive> select ceiling(46) from t\_fin\_demo;  
        46  
**6. 取随机数函数: rand**  
语法: rand(),rand(int seed)  
返回值: double  
说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列  
举例：  
hive> select rand() from t\_fin\_demo;  
        0.5577432776034763  
hive> select rand() from t\_fin\_demo;  
        0.6638336467363424  
hive> select rand(100) from t\_fin\_demo;  
        0.7220096548596434  
hive> select rand(100) from t\_fin\_demo;  
        0.7220096548596434  
**7. 自然指数函数: exp**  
语法: exp(double a)  
返回值: double  
说明: 返回自然对数e的a次方  
举例：  
hive> select exp(2) from t\_fin\_demo;  
        7.38905609893065  
自然对数函数: ln  
语法: ln(double a)  
返回值: double  
说明: 返回a的自然对数  
举例：  
hive> select ln(7.38905609893065) from t\_fin\_demo;  
        2.0  
**9. 以2为底对数函数: log2**  
语法: log2(double a)  
返回值: double  
说明: 返回以2为底的a的对数  
举例：  
hive> select log2(8) from t\_fin\_demo;  
        3.0  
**10. 对数函数: log**  
语法: log(double base, double a)  
返回值: double  
说明: 返回以base为底的a的对数  
举例：  
hive> select log(4,256) from t\_fin\_demo;  
        4.0  
**11. 幂运算函数: pow**  
语法: pow(double a, double p)  
返回值: double  
说明: 返回a的p次幂  
举例：  
hive> select pow(2,4) from t\_fin\_demo;  
        16.0  
**12. 幂运算函数: power**  
语法: power(double a, double p)  
返回值: double  
说明: 返回a的p次幂,与pow功能相同  
举例：  
hive> select power(2,4) from t\_fin\_demo;  
        16.0  
**13. 开平方函数: sqrt**  
语法: sqrt(double a)  
返回值: double  
说明: 返回a的平方根  
举例：  
hive> select sqrt(16) from lxw\_dual;  
        4.0  
**14. 二进制函数: bin**  
语法: bin(BIGINT a)  
返回值: string  
说明: 返回a的二进制代码表示  
举例：  
hive> select bin(7) from t\_fin\_demo;  
        111  
**15. 十六进制函数: hex**  
语法: hex(BIGINT a)  
返回值: string  
说明: 如果变量是int类型，那么返回a的十六进制表示；如果变量是string类型，则返回该字符串的十六进制表示  
举例：  
hive> select hex(17) from t\_fin\_demo;  
        11  
hive> select hex(‘abc’) from t\_fin\_demo;  
        616263  
**16. 反转十六进制函数: unhex**  
语法: unhex(string a)  
返回值: string  
说明: 返回该十六进制字符串所代码的字符串  
举例：  
hive> select unhex(‘616263’) from t\_fin\_demo;  
abc  
hive> select unhex(‘11’) from t\_fin\_demo;  
\-  
hive> select unhex(616263) from t\_fin\_demo;  
abc  
  
**17. 进制转换函数: conv**  
语法: conv(BIGINT num, int from\_base, int to\_base)  
返回值: string  
说明: 将数值num从from\_base进制转化到to\_base进制  
举例：  
hive> select conv(17,10,16) from t\_fin\_demo;  
        11  
hive> select conv(17,10,2) from t\_fin\_demo;  
        10001  
  
**18. 绝对值函数: abs**  
语法: abs(double a)   abs(int a)  
返回值: double        int  
说明: 返回数值a的绝对值  
举例：  
hive> select abs(-3.9) from lxw\_dual;  
3.9  
hive> select abs(10.9) from t\_fin\_demo;  
10.9  
  
**19. negative函数: negative**  
语法: negative(int a), negative(double a)  
返回值: int double  
说明: 返回-a,取负数  
举例：  
hive> select negative(-5) from t\_fin\_demo;  
        5

[hive]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF