Apache Presto - SQL Functions - Adglob Infosystem Pvt Ltd

As of now we were discussing running some simple basic queries on Presto. This chapter will discuss the important SQL functions.

Math Functions

Math functions operate on mathematical formulas. Following table describes the list of functions in detail.

S.No.	Function & Description
1.	abs(x)Returns the absolute value of x
2.	cbrt(x)Returns the cube root of x
3.	ceiling(x)Returns the x value rounded up to the nearest integer
4.	ceil(x)Alias for ceiling(x)
5.	degrees(x)Returns the degree value for x
6.	e(x)Returns the double value for Euler’s number
7.	exp(x)Returns the exponent value for Euler’s number
8.	floor(x)Returns x rounded down to the nearest integer
9.	from_base(string,radix)Returns the value of string interpreted as a base-radix number
10.	ln(x)Returns the natural logarithm of x
11.	log2(x)Returns the base 2 logarithm of x
12.	log10(x)Returns the base 10 logarithm of x
13.	log(x,y)Returns the base y logarithm of x
14.	mod(n,m)Returns the modulus (remainder) of n divided by m
15.	pi()Returns pi value. The result will be returned as a double value
16.	power(x,p)Returns power of value ‘p’ to the x value
17.	pow(x,p)Alias for power(x,p)
18.	radians(x)converts the angle x in degree radians
19.	rand()Alias for radians()
20.	random()Returns the pseudo-random value
21.	rand(n)Alias for random()
22.	round(x)Returns the rounded value for x
23.	round(x,d)x value rounded for the ‘d’ decimal places
24.	sign(x)Returns the signum function of x, i.e.,0 if the argument is 01 if the argument is greater than 0-1 if the argument is less than 0For double arguments, the function additionally returns −NaN if the argument is NaN1 if the argument is +Infinity-1 if the argument is -Infinity
25.	sqrt(x)Returns the square root of x
26.	to_base(x,radix)Return type is archer. The result is returned as the base radix for x
27.	truncate(x)Truncates the value for x
28.	width_bucket(x, bound1, bound2, n)Returns the bin number of x specified bound1 and bound2 bounds and n number of buckets
29.	width_bucket(x, bins)Returns the bin number of x according to the bins specified by the array bins

Trigonometric Functions

Trigonometric functions arguments are represented as radians(). Following table lists out the functions.

S.No	Functions & Description
1.	acos(x)Returns the inverse cosine value(x)
2.	asin(x)Returns the inverse sine value(x)
3.	atan(x)Returns the inverse tangent value(x)
4.	atan2(y,x)Returns the inverse tangent value(y/x)
5.	cos(x)Returns the cosine value(x)
6.	cosh(x)Returns the hyperbolic cosine value(x)
7.	sin(x)Returns the sine value(x)
8.	tan(x)Returns the tangent value(x)
9.	tanh(x)Returns the hyperbolic tangent value(x)

Bitwise Functions

The following table lists out the Bitwise functions.

S.No	Functions & Description
1.	bit_count(x, bits)Count the number of bits
2.	bitwise_and(x,y)Perform bitwise AND operation for two bits, x and y
3.	bitwise_or(x,y)Bitwise OR operation between two bits x, y
4.	bitwise_not(x)Bitwise Not operation for bit x
5.	bitwise_xor(x,y)XOR operation for bits x, y

String Functions

Following table lists out the String functions.

S.No	Functions & Description
1.	concat(string1, …, stringN)Concatenate the given strings
2.	length(string)Returns the length of the given string
3.	lower(string)Returns the lowercase format for the string
4.	upper(string)Returns the uppercase format for the given string
5.	lpad(string, size, padstring)Left padding for the given string
6.	ltrim(string)Removes the leading whitespace from the string
7.	replace(string, search, replace)Replaces the string value
8.	reverse(string)Reverses the operation performed for the string
9.	rpad(string, size, padstring)Right padding for the given string
10.	rtrim(string)Removes the trailing whitespace from the string
11.	split(string, delimiter)Splits the string on delimiter and returns an array of size at the most limit
12.	split_part(string, delimiter, index)Splits the string on delimiter and returns the field index
13.	strpos(string, substring)Returns the starting position of the substring in the string
14.	substr(string, start)Returns the substring for the given string
15.	substr(string, start, length)Returns the substring for the given string with the specific length
16.	trim(string)Removes the leading and trailing whitespace from the string

Date and Time Functions

Following table lists out the Date and Time functions.

S.No	Functions & Description
1.	current_dateReturns the current date
2.	current_timeReturns the current time
3.	current_timestampReturns the current timestamp
4.	current_timezone()Returns the current timezone
5.	now()Returns the current date,timestamp with the timezone
6.	localtimeReturns the local time
7.	localtimestampReturns the local timestamp

Regular Expression Functions

The following table lists out the Regular Expression functions.

S.No	Functions & Description
1.	regexp_extract_all(string, pattern)Returns the string matched by the regular expression for the pattern
2.	regexp_extract_all(string, pattern, group)Returns the string matched by the regular expression for the pattern and the group
3.	regexp_extract(string, pattern)Returns the first substring matched by the regular expression for the pattern
4.	regexp_extract(string, pattern, group)Returns the first substring matched by the regular expression for the pattern and the group
5.	regexp_like(string, pattern)Returns the string matches for the pattern. If the string is returned, the value will be true otherwise false
6.	regexp_replace(string, pattern)Replaces the instance of the string matched for the expression with the pattern
7.	regexp_replace(string, pattern, replacement)Replace the instance of the string matched for the expression with the pattern and replacement
8.	regexp_split(string, pattern)Splits the regular expression for the given pattern

JSON Functions

The following table lists out JSON functions.

S.No	Functions & Description
1.	json_array_contains(json, value)Check the value exists in a json array. If the value exists it will return true, otherwise false
2.	json_array_get(json_array, index)Get the element for index in json array
3.	json_array_length(json)Returns the length in json array
4.	json_format(json)Returns the json structure format
5.	json_parse(string)Parses the string as a json
6.	json_size(json, json_path)Returns the size of the value

URL Functions

The following table lists out the URL functions.

S.No	Functions & Description
1.	url_extract_host(url)Returns the URL’s host
2.	url_extract_path(url)Returns the URL’s path
3.	url_extract_port(url)Returns the URL’s port
4.	url_extract_protocol(url)Returns the URL’s protocol
5.	url_extract_query(url)Returns the URL’s query string

Aggregate Functions

The following table lists out the Aggregate functions.

S.No	Functions & Description
1.	avg(x)Returns average for the given value
2.	min(x,n)Returns the minimum value from two values
3.	max(x,n)Returns the maximum value from two values
4.	sum(x)Returns the sum of value
5.	*count()**Returns the number of input rows
6.	count(x)Returns the count of input values
7.	checksum(x)Returns the checksum for x
8.	arbitrary(x)Returns the arbitrary value for x

Color Functions

Following table lists out the Color functions.

S.No	Functions & Description
1.	bar(x, width)Renders a single bar using rgb low_color and high_color
2.	bar(x, width, low_color, high_color)Renders a single bar for the specified width
3.	color(string)Returns the color value for the entered string
4.	render(x, color)Renders value x using the specific color using ANSI color codes
5.	render(b)Accepts boolean value b and renders a green true or a red false using ANSI color codes
6.	rgb(red, green, blue)Returns a color value capturing the RGB value of three component color values supplied as int parameters ranging from 0 to 255

Array Functions

The following table lists out the Array functions.

S.No	Functions & Description
1.	array_max(x)Finds the max element in an array
2.	array_min(x)Finds the min element in an array
3.	array_sort(x)Sorts the elements in an array
4.	array_remove(x,element)Removes the specific element from an array
5.	concat(x,y)Concatenates two arrays
6.	contains(x,element)Finds the given elements in an array. True will be returned if it is present, otherwise false
7.	array_position(x,element)Find the position of the given element in an array
8.	array_intersect(x,y)Performs an intersection between two arrays
9.	element_at(array,index)Returns the array element position
10.	slice(x,start,length)Slices the array elements with the specific length

Teradata Functions

The following table lists out Teradata functions.

S.No	Functions & Description
1.	index(string,substring)Returns the index of the string with the given substring
2.	substring(string,start)Returns the substring of the given string. You can specify the start index here
3.	substring(string,start,length)Returns the substring of the given string for the specific start index and length of the string