Impala function(学习笔记）

The impala function:

One、Mathematical function：

abs(double ): returns the absolute value of the argument

acos(double): return the arccosine of the argument

asin(double) return the arcsine of the argument

atan(double) return the arctangent of the argument.

atan2(double,double) return the arctangent of the two arguments, with the signs of the arguments used to determine

 the quadrant of the result.

bin(biting): returns the binary representation of an integer value.like 2 to 10,3 to 110

cos(double) return the cosine of the argument.

cosh(double) Returns the hyperbolic cosine of the argument.

cot(double) returns the cotangent of the argument.

degrees(double) Converts argument value from radians to degrees.

hex(biting) hex(string): return the hexadecimal representation of an integer value,or of the characters in a string.

max_int()  max_tinyint() max_smallint() max_bigint()

min_int() min_tinyint() min_smallint() min_bigint()

mod((numeric_type a,same_type b) return the modulus of a number.Equivalent to the % arithmetic operator. like mod(10,3) = 1

negative(numeric_type) returns the argument with the sign reversed.

pmod(biting,bigint),pmod(double, double) : returns the positive modulus of a number.like pmod(-5,2) = 1

radians(a) : Converts argument value from degrees to radians.

rand(),rand(int) random(),random(seed): Returns a random value between 0 and 1 after rand() is called with a seed argument, it produces a consistent random sequence based on the seed value.

round(double),round(double,int),round(double,int),dround(double,int)dround(decimal(pas), int_type) Rounds a floating-point value. By default (with a single argument), rounds to the nearest integer. Values ending in .5 are rounded up for positive numbers, down for negative numbers (that is, away from zero). The optional second argument specifies how many digits to leave after the decimal point; values greater than zero produce a floating-point return value rounded to the requested number of digits to the right of the decimal point.

Two、Bit functions

bitand(integer，same_type) Returns an integer value representing the bits that are set to 1 in both of the arguments.This function is equivalent to the & binary operator;

bitnot(integer) : Inverts all the bits of the input argument ,It equivalent to the ~ unary operator.

bitor(integer,same) Returns an integer value representing the bits that are set to 1 in either of the arguments. It equivalent to the | binary operator

bitter(integer, same) Returns an integer value representing the bits that are set to 1 in one but not both of the arguments.It equivalent to the ^ binary operator.

contest(integer,[int zero or one]) By default ,returns the number of 1 bits in the specified integer value.If the optional second argument is set to zero, it returns the number of 0 bits instead.

getbit(integer,int) Returns a 0 or 1 representing the bit at a specified position.The second are numbered right to left ,starting at zero.The second argument cannot be negative.

setbit(integer,int,[,int zero or one]) By default , changes a bit at a specified position to a 1 if it not already,If the optional third argument is set to zero ,The specified bit is set to 0 instead.

shiftleft(integer , int ) Shifts an integer value left by a specified number of bits . As the most significant bit is taken out of the original value, it is discarded and the least significant bit becomes 0.In computer science terms .

shift right(integer, int) Shifts an integer value right by a specified number of bits. As the least significant bit is taken out of the original value, it is discarded and the most significant bit becomes 0.In computer science terms

Three、Type Conversion Functions:

cast(express as type ) : Converts the value of an expression to any other type. example:cast(column as biting) x

typeof(type value): Returns the name of the data type corresponding to an expression. example:typeof(2) It will output TINYINT

Date and Time functions:

add_months(timestamp, months) add _moths(timestamp,bigint):Return the specified date and time plus some number of months.

adddate(timestamp, int) , adddate(timestamp,bigint):Adds a specified number of days to a TIMESTAMP value.

current_timestamp() : Alias for the now() function.

date_add(timestamp, int) date_add(timestamp, interval_expression) Adds a specified number of days to a  TIMESTAMP value

date_part(string, timestamp) Similar to extract() with the argument order reversed.

date_sub(timestamp, int) date_sub(timestamp, interval_exp):Subtracts a specified number of days from a  TIMESTAMP value.Then interval_exp likes 

interval 1 months,interval 6 hours.

date_trunc(string,timestamp):Truncates a Timestamp value to the specified precision.

 the first argument can be one of:

• • microseconds
  • milliseconds
  • second
  • minute
  • hour
  • day
  • week
  • month
  • year
  • decade
  • century
  • millennium

for example:

date_trunc(‘hour’,now()) =>  2017-12-05 13:00:00

datediff(timestamp endnote,startdate):Returns the number of days between two TIMESTAMP values. for example:

datediff(now()+interval 5 years, now()) => 1826

day(timestamp),dayofmonth(timestamp):Returns the day from the date portion of a TIMESTAMP.

dynamo(timestamp) Returns the day name in week, such as : dayname(now()) =>Tuesday

dayofweek(timestamp) Returns the day field from the date portion of a Timestamp,corresponding to the day of the week.such as:dayofweek(now()) => 3

dayofyear(timestamp) like above , nothing special Notice: return values is 1 to 366.

days_add and days_sub like above add_days and sub_days.

extract(timestamp, string) extract(unit from timestamp):Return one of the numeric date or time fields from a TIMESTAMP value, such as extract(year from now())

from_timestamp(date time,pattern) Converts a TImeStamp value into a string representing the same value. from_timestamp(now(),’yyyy/MM/dd’) =>  2017/10/01

from_unixtime(biting [,string]) : Converts the number of seconds from the Unix epoch to the specified time into a string in the local time zone. such as:

from_unixtime(1392394861, 'yyyy-mm-dd hh:mm:ss.ssss’) = >  2014-02-14 16:21:01.0000

from_utc_timestamp(timestamp, string) Converts a specified UTC timestamp value into the appropriate value for a specified time zone.

  hour(timestamp) : Returns the hour field from a TIMESTAMP field.

hours_add , hours_add : Returns the specified date and time plus some number of hours.

hours_sub, hours_sub: Returns the specified date and time minus some number of hours.

int_months_between(timestamp, timestamp) :Returns the number of months between the date portions of two TIMESTAMP values, as an INT representing only the full   months that passed.

last_day(timestamp):

FOUR、Conditional Functions:

case a when b then c [when d then e] … [else f] end : this code just like mysql.

if(boolean, type ifTrue , type ifFalseOrNull) : Tests an expression and returns a corresponding result depending on whether the result is true, false,or NULL.

ifnull（type ， type ifNull):alias for the isnull() function,with the same behavior.To simplify porting SQL with vendor extensions to Impala.

isfalse(boolean): Test if a Boolean expression is false or not。

isnotfalse(boolean) : if argument is Null returns true

isnottrue(boolean) :argument is not true results true, So argument is false or null returns true.

is null(type, type if null): Equivalent to the nvl() from Oracle.

FIVE、String Functions：

base64encode(string) encode string to base64 format.

base64decode(stirng)  decode string from base64 format string.

btrim(a),btrim(string,string) : Removes all instances of one or more characters from the start and end of a STRING value.By default ,removes only spaces.

If a non-null optional second argument is specified.The function removes all occurrence of characters in that second argument from the beginning and end of the   string.such as:  concat(‘[‘,btrim(‘xy hello zyzzxx],’xyz’),’]’) = > [ hello ] It removes any instances of x or y or z at beginning or end.Leave spaces alone.

char_length(string) , character_length(string): Aliases for the length() function;

concat(string a,string b…) : Returns a single string representing all the argument values joined together.

concat_ws(sep,a,b): Returns a single string representing the second and following argument values joined together,的limited by a specified separator.

find_in_set(string,string) returns the position(string from 1) of the first occurrence of a specified string within a commaseparated string.

group_concat(string[,str]) Returns a single string representing the argument value concatenated together for each row of the result set.If the optional separator string

is specified the separator is added between each pair of concatenated values.

initcap(str) , Results the input string with the first latter capitalized.

instr(str,substr[,bigint position [,bigint occurrence]]) : Returns the position(string from 1) of the first occurrence of a substring within a longer string.

length(str) : Returns the length in characters of the argument string.

locate(str, str[,pos]) : Returns the position(string from 1 ) of the first occurrence of a substring within a longer string,optionally after a particular position.

lower(str) , lcase(str) Returns the argument string converted to all-lowercase.

ltrim(string[,string]) : returns the argument string with all occurrences of characters specified by the second argument removed from the left side.Removes spaces if the   second argument is not specified.

regexp_extract(subject,pattern,index):Returns the specified () group from a string based on a regular expression pattern.Group 0 refers to the entire extracted string,while group 1,2,and so on refers to the first , second , and so on(..) portion.

rtrim(string[,string]) :  Like as ltrim;

split_part(source,delimiter,n) : Returns the nth field within a delimited string .The fields are numbered string from 1.The delimiter can consist of multiple characters.

substr(string a,int start[,int len]) , substring(string a,int start[,int len]): Returns the portion of the string starting at a specified point , optionally with a specified maximum   length.The characters in the string are indexed string at 1.

trim(str) : Returns the input string with both leading and trailing spaces removed . 

upper(string) , ucase(string): Returns the argument string converted to all-uppercase.

Six、Miscellaneous Functions

uuid() :Returns a universal unique identifier , a 128-bit value encoded as a string with groups of hexadecimal digits separated by dashes.

SEVEN、Aggregate Functions：

min(x) max(x) avg(x) count(x)

ndv : An aggregate function that returns an approximate value similar to the result of count(distinict col)), the “number of distinct values” . It is much faster then combination of count and distinct.and uses a constant amount of memory ad thus is less memory-intensive for columns with high cardinality.