hive函数大全:数值与数学相关函数 迈不过友情╰ 2022-05-30 05:25 472阅读 0赞 ### hive函数官网文档:[hive函数官网文档][hive] ### ### 1.Hive所有内嵌数学函数 ### <table> <thead> <tr> <td style="vertical-align:middle;"> <p><strong>Return Type</strong></p> </td> <th> <p><strong>Name (Signature)</strong></p> </th> <th> <p><strong>Description</strong></p> </th> </tr> </thead> <tbody> <tr> <td> <p>DOUBLE</p> </td> <td> <p>round(DOUBLE a)</p> </td> <td> <p>Returns the rounded <code>BIGINT</code> value of <code>a</code>.</p> <p><strong>返回对a四舍五入的BIGINT值</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>round(DOUBLE a, INT d)</p> </td> <td> <p>Returns <code>a</code> rounded to <code>d</code> decimal places.</p> <p><strong>返回DOUBLE型d的保留n位小数的DOUBLW型的近似值</strong></p> </td> </tr> <tr> <td colspan="1">DOUBLE</td> <td colspan="1">bround(DOUBLE a)</td> <td colspan="1">Returns the rounded BIGINT value of <code>a</code> using HALF_EVEN rounding mode (as of <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.<br><strong>银行家舍入法(1~4:舍,6~9:进,5->前位数是偶:舍,5->前位数是奇:进)</strong></td> </tr> <tr> <td colspan="1">DOUBLE</td> <td colspan="1">bround(DOUBLE a, INT d)</td> <td colspan="1">Returns <code>a</code> rounded to <code>d</code> decimal places using HALF_EVEN rounding mode (as of <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.<br><strong>银行家舍入法,保留d位小数</strong></td> </tr> <tr> <td> <p>BIGINT</p> </td> <td> <p>floor(DOUBLE a)</p> </td> <td> <p>Returns the maximum <code>BIGINT</code> value that is equal to or less than <code>a</code></p> <p><strong>向下取整,最数轴上最接近要求的值的左边的值 如:6.10->6 -3.4->-4</strong></p> </td> </tr> <tr> <td> <p>BIGINT</p> </td> <td> <p>ceil(DOUBLE a), ceiling(DOUBLE a)</p> </td> <td> <p>Returns the minimum BIGINT value that is equal to or greater than <code>a</code>.</p> <p><strong>求其不小于小给定实数的最小整数如:ceil(6) = ceil(6.1)= ceil(6.9) = 6</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>rand(), rand(INT seed)</p> </td> <td> <p>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.</p> <p><strong>每行返回一个DOUBLE型随机数seed是随机因子</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>exp(DOUBLE a), exp(DECIMAL a)</p> </td> <td> <p>Returns <code>ea</code> where <code>e</code> is the base of the natural logarithm. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>返回e的a幂次方, a可为小数</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>ln(DOUBLE a), ln(DECIMAL a)</p> </td> <td> <p>Returns the natural logarithm of the argument <code>a</code>. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>以自然数为底d的对数,a可为小数</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>log10(DOUBLE a), log10(DECIMAL a)</p> </td> <td> <p>Returns the base-10 logarithm of the argument <code>a</code>. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>以10为底d的对数,a可为小数</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>log2(DOUBLE a), log2(DECIMAL a)</p> </td> <td> <p>Returns the base-2 logarithm of the argument <code>a</code>. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>以2为底数d的对数,a可为小数</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>log(DOUBLE base, DOUBLE a)</p> <p>log(DECIMAL base, DECIMAL a)</p> </td> <td> <p>Returns the base-<code>base</code> logarithm of the argument <code>a</code>. Decimal versions added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>以base为底的对数,base 与 a都是DOUBLE类型</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)</p> </td> <td> <p>Returns <code>ap</code>.</p> <p><strong>计算a的p次幂</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>sqrt(DOUBLE a), sqrt(DECIMAL a)</p> </td> <td> <p>Returns the square root of <code>a</code>. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>计算a的平方根</strong></p> </td> </tr> <tr> <td> <p>STRING</p> </td> <td> <p>bin(BIGINT a)</p> </td> <td> <p>Returns the number in binary format (see <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>).</p> <p><strong>计算二进制a的STRING类型,a为BIGINT类型</strong></p> </td> </tr> <tr> <td> <p>STRING</p> </td> <td> <p>hex(BIGINT a) hex(STRING a) hex(BINARY a)</p> </td> <td> <p>If the argument is an <code>INT</code> or <code>binary</code>, <code>hex</code> returns the number as a <code>STRING</code> in hexadecimal format. Otherwise if the number is a <code>STRING</code>, it converts each character into its hexadecimal representation and returns the resulting <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>, <code>BINARY</code> version as of Hive <a href="https://issues.apache.org/jira/browse/HIVE-2482" rel="nofollow">0.12.0</a>.)</p> <p><strong>计算十六进制a的STRING类型,如果a为STRING类型就转换成字符相对应的十六进制</strong></p> </td> </tr> <tr> <td> <p>BINARY</p> </td> <td> <p>unhex(STRING a)</p> </td> <td> <p>Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number. (<code>BINARY</code> version as of Hive <a href="https://issues.apache.org/jira/browse/HIVE-2482" rel="nofollow">0.12.0</a>, used to return a string.)</p> <p><strong>hex的逆方法</strong></p> </td> </tr> <tr> <td> <p>STRING</p> </td> <td> <p>conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)</p> </td> <td> <p>Converts a number from a given base to another (see <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>).</p> <p><strong>将GIGINT/STRING类型的num从from_base进制转换成to_base进制</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>abs(DOUBLE a)</p> </td> <td> <p>Returns the absolute value.</p> <p><strong>计算a的绝对值</strong></p> </td> </tr> <tr> <td> <p>INT or DOUBLE</p> </td> <td> <p>pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)</p> </td> <td> <p>Returns the positive value of <code>a mod b</code>.</p> <p><strong>a对b取模</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>sin(DOUBLE a), sin(DECIMAL a)</p> </td> <td> <p>Returns the sine of <code>a</code> (<code>a</code> is in radians). Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>求a的正弦值</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>asin(DOUBLE a), asin(DECIMAL a)</p> </td> <td> <p>Returns the arc sin of <code>a</code> if -1<=a<=1 or NULL otherwise. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>求d的反正弦值</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>cos(DOUBLE a), cos(DECIMAL a)</p> </td> <td> <p>Returns the cosine of <code>a</code> (<code>a</code> is in radians). Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>求余弦值</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>acos(DOUBLE a), acos(DECIMAL a)</p> </td> <td> <p>Returns the arccosine of <code>a</code> if -1<=a<=1 or NULL otherwise. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>求反余弦值</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>tan(DOUBLE a), tan(DECIMAL a)</p> </td> <td> <p>Returns the tangent of <code>a</code> (<code>a</code> is in radians). Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>求正切值</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>atan(DOUBLE a), atan(DECIMAL a)</p> </td> <td> <p>Returns the arctangent of <code>a</code>. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>求反正切值</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>degrees(DOUBLE a), degrees(DECIMAL a)</p> </td> <td> <p>Converts value of <code>a</code> from radians to degrees. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6385" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>奖弧度值转换角度值</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>radians(DOUBLE a), radians(DOUBLE a)</p> </td> <td> <p>Converts value of <code>a</code> from degrees to radians. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6327" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>将角度值转换成弧度值</strong></p> </td> </tr> <tr> <td> <p>INT or DOUBLE</p> </td> <td> <p>positive(INT a), positive(DOUBLE a)</p> </td> <td> <p>Returns <code>a</code>.</p> <p><strong>返回a</strong></p> </td> </tr> <tr> <td> <p>INT or DOUBLE</p> </td> <td> <p>negative(INT a), negative(DOUBLE a)</p> </td> <td> <p>Returns <code>-a</code>.</p> <p><strong>返回a的相反数</strong></p> </td> </tr> <tr> <td> <p>DOUBLE or INT</p> </td> <td> <p>sign(DOUBLE a), sign(DECIMAL a)</p> </td> <td> <p>Returns the sign of <code>a</code> as '1.0' (if <code>a</code> is positive) or '-1.0' (if <code>a</code> is negative), '0.0' otherwise. The decimal version returns INT instead of DOUBLE. Decimal version added in <a href="https://issues.apache.org/jira/browse/HIVE-6246" rel="nofollow">Hive 0.13.0</a>.</p> <p><strong>如果a是正数则返回1.0,是负数则返回-1.0,否则返回0.0</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>e()</p> </td> <td> <p>Returns the value of <code>e</code>.</p> <p><strong>数学常数e</strong></p> </td> </tr> <tr> <td> <p>DOUBLE</p> </td> <td> <p>pi()</p> </td> <td> <p>Returns the value of <code>pi</code>.</p> <p><strong>数学常数pi</strong></p> </td> </tr> <tr> <td colspan="1">BIGINT</td> <td colspan="1">factorial(INT a)</td> <td colspan="1">Returns the factorial of <code>a</code> (as of Hive <a href="https://issues.apache.org/jira/browse/HIVE-9857" rel="nofollow">1.2.0</a>). Valid <code>a</code> is [0..20].<br><strong>求a的阶乘</strong></td> </tr> <tr> <td colspan="1">DOUBLE</td> <td colspan="1">cbrt(DOUBLE a)</td> <td colspan="1">Returns the cube root of <code>a</code> double value (as of Hive <a href="https://issues.apache.org/jira/browse/HIVE-9858" rel="nofollow">1.2.0</a>).<br><strong>求a的立方根</strong></td> </tr> <tr> <td colspan="1"> <p> </p> <p>INT BIGINT</p> </td> <td colspan="1"> <p>shiftleft(TINYINT|SMALLINT|INT a, INT b)</p> <p>shiftleft(BIGINT a, INT b)</p> </td> <td colspan="1"> <p>Bitwise left shift (as of Hive <a href="https://issues.apache.org/jira/browse/HIVE-9859" rel="nofollow">1.2.0</a>). Shifts <code>a</code> <code>b</code> positions to the left.</p> <p>Returns int for tinyint, smallint and int <code>a</code>. Returns bigint for bigint <code>a</code>.</p> <p><strong>按位左移</strong></p> </td> </tr> <tr> <td colspan="1"> <p>INT</p> <p>BIGINT</p> </td> <td colspan="1"> <p>shiftright(TINYINT|SMALLINT|INT a, INTb)</p> <p>shiftright(BIGINT a, INT b)</p> </td> <td colspan="1"> <p>Bitwise right shift (as of Hive <a href="https://issues.apache.org/jira/browse/HIVE-9859" rel="nofollow">1.2.0</a>). Shifts <code>a</code> <code>b</code> positions to the right.</p> <p>Returns int for tinyint, smallint and int <code>a</code>. Returns bigint for bigint <code>a</code>.</p> <p><strong>按拉右移</strong></p> </td> </tr> <tr> <td colspan="1"> <p>INT</p> <p>BIGINT</p> </td> <td colspan="1"> <p>shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b),</p> <p>shiftrightunsigned(BIGINT a, INT b)</p> </td> <td colspan="1"> <p>Bitwise unsigned right shift (as of Hive <a href="https://issues.apache.org/jira/browse/HIVE-9859" rel="nofollow">1.2.0</a>). Shifts <code>a</code> <code>b</code> positions to the right.</p> <p>Returns int for tinyint, smallint and int <code>a</code>. Returns bigint for bigint <code>a</code>.</p> <p><strong>无符号按位右移(<<<)</strong></p> </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 <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 ">" operator (as of Hive <a href="https://issues.apache.org/jira/browse/HIVE-12082" rel="nofollow">2.0.0</a>).<br><strong>求最大值</strong></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 <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 "<" operator (as of Hive <a href="https://issues.apache.org/jira/browse/HIVE-12082" rel="nofollow">2.0.0</a>).<br><strong>求最小值</strong></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
还没有评论,来说两句吧...