Apache Presto - SQL Functions-alljchome-开发者的教程家园

Apache Presto Tutorial

Apache Presto Useful Resources

Selected Reading

Apache Presto - SQL Functions

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 pst 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.	ceipng(x) Returns the x value rounded up to the nearest integer
4.	ceil(x) Apas for ceipng(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 spanided 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) Apas for power(x,p)
18.	radians(x) converts the angle x in degree radians
19.	rand() Apas for radians()
20.	random() Returns the pseudo-random value
21.	rand(n) Apas 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 0 1 if the argument is greater than 0 -1 if the argument is less than 0 For double arguments, the function additionally returns − NaN if the argument is NaN 1 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 psts 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 hyperbopc cosine value(x)
7.	sin(x) Returns the sine value(x)
8.	tan(x) Returns the tangent value(x)
9.	tanh(x) Returns the hyperbopc tangent value(x)

Bitwise Functions

The following table psts 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 psts 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 traipng whitespace from the string
11.	sppt(string, depmiter) Sppts the string on depmiter and returns an array of size at the most pmit
12.	sppt_part(string, depmiter, index) Sppts the string on depmiter 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 traipng whitespace from the string

Date and Time Functions

Following table psts out the Date and Time functions.

S.No	Functions & Description
1.	current_date Returns the current date
2.	current_time Returns the current time
3.	current_timestamp Returns the current timestamp
4.	current_timezone() Returns the current timezone
5.	now() Returns the current date,timestamp with the timezone
6.	localtime Returns the local time
7.	localtimestamp Returns the local timestamp

Regular Expression Functions

The following table psts 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.	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_pke(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.	Replace the instance of the string matched for the expression with the pattern and replacement
8.	regexp_sppt(string, pattern) Sppts the regular expression for the given pattern

JSON Functions

The following table psts 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 psts 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 psts 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(&ast;) 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 psts 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 suppped as int parameters ranging from 0 to 255

Array Functions

The following table psts 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.	spce(x,start,length) Spces the array elements with the specific length

Teradata Functions

The following table psts 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