spark常用函数

!

! expr -逻辑非

.

 

%

expr1 % expr2 -在expr1/expr2之后返回剩余的部分.

Examples:

1. > SELECT 2 % 1.8;

2. 0.2

3. > SELECT MOD(2, 1.8);

4. 0.2

 

&

expr1 & expr2 - 返回和expr1和expr2的结果.

Examples:

1. > SELECT 3 & 5;

2. 1

 

*

expr1 * expr2 - Returns expr1*expr2.

Examples:

1. > SELECT 2 * 3;

2. 6

 

+

expr1 + expr2 - Returns expr1+expr2.

Examples:

1. > SELECT 1 + 2;

2. 3

 

-

expr1 - expr2 - Returns expr1-expr2.

Examples:

1. > SELECT 2 - 1;

2. 1

 

/

expr1/expr2返回expr1/expr2。它总是执行浮点分。

Examples:

1. > SELECT 3 / 2;

2. 1.5

3. > SELECT 2L / 2L;

4. 1.0

 

<

expr1 < expr2 - 如果expr1小于expr2，则返回true。.

Arguments:

• expr1, expr2 - 这两个表达式必须是相同类型的，或者可以被一个普通类型的类型所使用，并且必须是一个可以被排序的类型。例如，映射类型不是可排序的，因此它不受支持。对于复杂类型的数组/结构体，字段的数据类型必须是可排序的.

Examples:

1. > SELECT 1 < 2;

2. true

3. > SELECT 1.1 < '1';

4. false

5. > SELECT to_date('2009-07-30 04:17:52') < to_date('2009-07-30 04:17:52');

6. false

7. > SELECT to_date('2009-07-30 04:17:52') < to_date('2009-08-01 04:17:52');

8. true

9. > SELECT 1 < NULL;

10. NULL

 

<=

expr1 <= expr2 - 如果expr1小于或等于expr2，则返回true.

Examples:

1. > SELECT 2 <= 2;

2. true

3. > SELECT 1.0 <= '1';

4. true

5. > SELECT to_date('2009-07-30 04:17:52') <= to_date('2009-07-30 04:17:52');

6. true

7. > SELECT to_date('2009-07-30 04:17:52') <= to_date('2009-08-01 04:17:52');

8. true

9. > SELECT 1 <= NULL;

10. NULL

 

<=>

expr1 <=> expr2 - 返回与非空操作数相等的（=）操作符相同的结果，但如果两者都是null，则返回true，如果其中一个为null，则返回false.

 

Examples:

1. > SELECT 2 <=> 2;

2. true

3. > SELECT 1 <=> '1';

4. true

5. > SELECT true <=> NULL;

6. false

7. > SELECT NULL <=> NULL;

8. true

 

=

expr1 = expr2 - 如果expr1等于expr2，则返回true，否则将返回false。

 

Examples:

1. > SELECT 2 = 2;

2. true

3. > SELECT 1 = '1';

4. true

5. > SELECT true = NULL;

6. NULL

7. > SELECT NULL = NULL;

8. NULL

 

==

expr1 == expr2 - 如果expr1等于expr2，则返回true，否则返回false.

 

Examples:

1. > SELECT 2 == 2;

2. true

3. > SELECT 1 == '1';

4. true

5. > SELECT true == NULL;

6. NULL

7. > SELECT NULL == NULL;

8. NULL

 

>

expr1 > expr2 - 如果expr1大于expr2，则返回true。

 

Examples:

1. > SELECT 2 > 1;

2. true

3. > SELECT 2 > '1.1';

4. true

5. > SELECT to_date('2009-07-30 04:17:52') > to_date('2009-07-30 04:17:52');

6. false

7. > SELECT to_date('2009-07-30 04:17:52') > to_date('2009-08-01 04:17:52');

8. false

9. > SELECT 1 > NULL;

10. NULL

 

>=

expr1 >= expr2 - 如果expr1大于或等于expr2，则返回true.

 

Examples:

1. > SELECT 2 >= 1;

2. true

3. > SELECT 2.0 >= '2.1';

4. false

5. > SELECT to_date('2009-07-30 04:17:52') >= to_date('2009-07-30 04:17:52');

6. true

7. > SELECT to_date('2009-07-30 04:17:52') >= to_date('2009-08-01 04:17:52');

8. false

9. > SELECT 1 >= NULL;

10. NULL

 

^

expr1 ^ expr2 -返回位或expr1或expr2的结果.

Examples:

1. > SELECT 3 ^ 5;

2. 2

 

abs

abs(expr) - 返回数值的绝对值.

Examples:

1. > SELECT abs(-1);

2. 1

 

acos

acos(expr) -如果-1小于=expr=1或NaN，则返回expr的反余弦（又称arccos）.

Examples:

1. > SELECT acos(1);

2. 0.0

3. > SELECT acos(2);

4. NaN

 

add_months

add_months(start_date, num_months) -返回开始日期后的num数月的日期.

Examples:

1. > SELECT add_months('2016-08-31', 1);

2. 2016-09-30

Since: 1.5.0

 

and

expr1 and expr2 - 逻辑与.

 

approx_count_distinct

approx_count_distinct(expr[, relativeSD]) - 通过HyperLogLog++返回估计的基数。相对维定义了允许的最大估计误差

 

approx_percentile

approx_percentile(col, percentage [, accuracy]) - 根据给定百分比返回数值列的近似百分比值。百分比的值必须在0.0和1.0之间。精度参数（默认值:10000）是一个正数值，它控制着近似精度，在内存中。较高的精度能产生更好的精度，1.0/精度是近似的相对误差。当百分比是一个数组时，百分比数组的每个值必须在0.0和1.0之间。在本例中，返回给定百分比数组中列的大约百分比数组。

Examples:

1. > SELECT approx_percentile(10.0, array(0.5, 0.4, 0.1), 100);

2. [10.0,10.0,10.0]

3. > SELECT approx_percentile(10.0, 0.5, 100);

4. 10.0

 

array

array(expr, ...) - 返回带有给定元素的数组.

Examples:

1. > SELECT array(1, 2, 3);

2. [1,2,3]

 

array_contains

array_contains(array, value) - Returns true if the array contains the value.

Examples:

1. > SELECT array_contains(array(1, 2, 3), 2);

2. true

 

ascii

ascii(str) - Returns the numeric value of the first character of str.

Examples:

1. > SELECT ascii('222');

2. 50

3. > SELECT ascii(2);

4. 50

 

asin

asin(expr) - Returns the inverse sine (a.k.a. arcsine) the arc sin of expr if -1<=expr<=1 or NaN otherwise.

Examples:

1. > SELECT asin(0);

2. 0.0

3. > SELECT asin(2);

4. NaN

 

assert_true

assert_true(expr) - Throws an exception if expr is not true.

Examples:

1. > SELECT assert_true(0 < 1);

2. NULL

 

atan

atan(expr) - Returns the inverse tangent (a.k.a. arctangent).

Examples:

1. > SELECT atan(0);

2. 0.0

 

atan2

atan2(expr1, expr2) - Returns the angle in radians between the positive x-axis of a plane and the point given by the coordinates (expr1, expr2).

Examples:

1. > SELECT atan2(0, 0);

2. 0.0

 

avg

avg(expr) - Returns the mean calculated from values of a group.

 

base64

base64(bin) - Converts the argument from a binary bin to a base 64 string.

Examples:

1. > SELECT base64('Spark SQL');

2. U3BhcmsgU1FM

 

bigint

bigint(expr) - Casts the value expr to the target data type bigint.

 

bin

bin(expr) - Returns the string representation of the long value expr represented in binary.

Examples:

1. > SELECT bin(13);

2. 1101

3. > SELECT bin(-13);

4. 1111111111111111111111111111111111111111111111111111111111110011

5. > SELECT bin(13.3);

6. 1101

 

binary

binary(expr) - Casts the value expr to the target data type binary.

 

bit_length

bit_length(expr) - Returns the bit length of string data or number of bits of binary data.

Examples:

1. > SELECT bit_length('Spark SQL');

2. 72

 

boolean

boolean(expr) - Casts the value expr to the target data type boolean.

 

bround

bround(expr, d) - Returns expr rounded to d decimal places using HALF_EVEN rounding mode.

Examples:

1. > SELECT bround(2.5, 0);

2. 2.0

 

cast

cast(expr AS type) - Casts the value expr to the target data type type.

Examples:

1. > SELECT cast('10' as int);

2. 10

 

cbrt

cbrt(expr) - Returns the cube root of expr.

Examples:

1. > SELECT cbrt(27.0);

2. 3.0

 

ceil

ceil(expr) - Returns the smallest integer not smaller than expr.

Examples:

1. > SELECT ceil(-0.1);

2. 0

3. > SELECT ceil(5);

4. 5

 

ceiling

ceiling(expr) - Returns the smallest integer not smaller than expr.

Examples:

1. > SELECT ceiling(-0.1);

2. 0

3. > SELECT ceiling(5);

4. 5

 

char

char(expr) - Returns the ASCII character having the binary equivalent to expr. If n is larger than 256 the result is equivalent to chr(n % 256)

Examples:

1. > SELECT char(65);

2. A

 

char_length

char_length(expr) - Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.

Examples:

1. > SELECT char_length('Spark SQL ');

2. 10

3. > SELECT CHAR_LENGTH('Spark SQL ');

4. 10

5. > SELECT CHARACTER_LENGTH('Spark SQL ');

6. 10

 

character_length

character_length(expr) - Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.

Examples:

1. > SELECT character_length('Spark SQL ');

2. 10

3. > SELECT CHAR_LENGTH('Spark SQL ');

4. 10

5. > SELECT CHARACTER_LENGTH('Spark SQL ');

6. 10

 

chr

chr(expr) - Returns the ASCII character having the binary equivalent to expr. If n is larger than 256 the result is equivalent to chr(n % 256)

Examples:

1. > SELECT chr(65);

2. A

 

coalesce

coalesce(expr1, expr2, ...) - Returns the first non-null argument if exists. Otherwise, null.

Examples:

1. > SELECT coalesce(NULL, 1, NULL);

2. 1

 

collect_list

collect_list(expr) - Collects and returns a list of non-unique elements.

 

collect_set

collect_set(expr) - Collects and returns a set of unique elements.

 

concat

concat(str1, str2, ..., strN) - Returns the concatenation of str1, str2, ..., strN.

Examples:

1. > SELECT concat('Spark', 'SQL');

2. SparkSQL

 

concat_ws

concat_ws(sep, [str | array(str)]+) - Returns the concatenation of the strings separated by sep.

Examples:

1. > SELECT concat_ws(' ', 'Spark', 'SQL');

2. Spark SQL

 

conv

conv(num, from_base, to_base) - Convert num from from_base to to_base.

Examples:

1. > SELECT conv('100', 2, 10);

2. 4

3. > SELECT conv(-10, 16, -10);

4. -16

 

corr

corr(expr1, expr2) - Returns Pearson coefficient of correlation between a set of number pairs.

 

cos

cos(expr) - Returns the cosine of expr.

Examples:

1. > SELECT cos(0);

2. 1.0

 

cosh

cosh(expr) - Returns the hyperbolic cosine of expr.

Examples:

1. > SELECT cosh(0);

2. 1.0

 

cot

cot(expr) - Returns the cotangent of expr.

Examples:

1. > SELECT cot(1);

2. 0.6420926159343306

 

count

count(*) - Returns the total number of retrieved rows, including rows containing null.

count(expr) - Returns the number of rows for which the supplied expression is non-null.

count(DISTINCT expr[, expr...]) - Returns the number of rows for which the supplied expression(s) are unique and non-null.

 

count_min_sketch

count_min_sketch(col, eps, confidence, seed) - Returns a count-min sketch of