”;
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 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 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_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 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 |
”;