Versions Compared

Old Version 70

changes.mady.by.user Attila Turoczy

Saved on

compared with

New Version Current

changes.mady.by.user Attila Turoczy

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Return Type

Name (Signature)

Description

Source code

double

round(double a)

Returns the rounded bigint value of a.

GenericUDFRound

double

round(double a,int d)

Returns a rounded to d decimal places.

double
bround(double a)
Returns the rounded bigint value of a using HALF_EVEN rounding mode. Also known as Gaussian rounding or bankers' rounding. Example: bround(2.5) = 2, bround(3.5) = 4.GenericUDFBRound
double
bround(double a,int d)
Returns a rounded to d decimal places using HALF_EVEN rounding mode.  Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4.

bigint

floor(double a)

Returns the maximum bigint value that is equal to or less than a.

GenericUDFFloor

bigint

ceil(double a), ceiling(double a)

Returns the minimum bigint value that is equal to or greater than a.

GenericUDFCeil

double

rand(), rand(INT seed)

Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the seed will make sure the generated random number sequence is deterministic.

UDFRand

double

exp(double a), exp(decimal a)

Returns ea where e is the base of the natural logarithm.

UDFExp

double

ln(double a), ln(decimal a)

Returns the natural logarithm of the argument a.

UDFLn

double

log10(double a), log10(decimal a)

Returns the base-10 logarithm of the argument a.

UDFLog10

double

log2(double a), log2(decimal a)

Returns the base-2 logarithm of the argument a. 

UDFLog2

double

log(double base, double a)
log(decimal base, decimal a)

Returns the base-base logarithm of the argument a.

UDFLog

double

pow(double a, double p), power(double a, double p)

Returns ap.

GenericUDFPower

double

sqrt(double a), sqrt(decimal a)

Returns the square root of a.

UDFSqrt

string

bin(bigint a)

Returns the number in binary format.

UDFBin

string

hex(bigint a) hex(string a) hex(binary a)

If the argument is an int or binary, hex returns the number as a string in hexadecimal format. Otherwise, if the number is a string, it converts each character into its hexadecimal representation and returns the resulting string. 

UDFHex

binary

unhex(STRING a)

Inverse of hex.interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number.

UDFUnhex

string

conv(bigint num,int from_base,int to_base), conv(STRING num,int from_base,int to_base)

Converts a number from a given base to another.

UDFConv

double

abs(double a)

Returns the absolute value.

GenericUDFAbs

int or double

pmod(INT a,int b), pmod(double a, double b)

Returns the positive value of a mod b.

GenericUDFOPMod

double

sin(double a), sin(decimal a)

Returns the sine of a (a is in radians).

UDFSin

double

asin(double a), asin(decimal a)

Returns the arc sin of a if -1<=a<=1 or NULL otherwise. 

UDFAsin

double

cos(double a), cos(decimal a)

Returns the cosine of a (a is in radians). 

UDFCos

double

acos(double a), acos(decimal a)

Returns the arccosine of a if -1<=a<=1 or NULL otherwise.

UDFAcos

double

tan(double a), tan(decimal a)

Returns the tangent of a (a is in radians).

UDFTan

double

atan(double a), atan(decimal a)

Returns the arctangent of a.

UDFAtan

double

degrees(double a), degrees(decimal a)

Converts value of a from radians to degrees. 

UDFDegrees

double

radians(double a), radians(double a)

Converts value of a from degrees to radians. 

UDFRadians

int or double

positive(INT a), positive(double a)

Returns a.

GenericUDFOPPositive

int or double

negative(INT a), negative(double a)

Returns -a.

GenericUDFOPNegative

double or int

sign(double a), sign(decimal a)

Returns the sign of a as '1.0' (if a is positive) or '-1.0' (if a is negative), '0.0' otherwise. The decimal version returns int instead of double. 

UDFSign

double

e()

Returns the value of e.

UDFE

double

pi()

Returns the value of pi.

UDFPI

bigint
factorial(INT a)
Returns the factorial of a Valid a is [0..20].GenericUDFFactorial
double
cbrt(double a)
Returns the cube root of a double value.GenericUDFCbrt

int

bigint

shiftleft(TINYINT|SMALLINT|INT a,int b)
shiftleft(bigint a,int b)

Bitwise left shift. Shifts a b positions to the left.

Returns int for tinyint, smallint andint a. Returns bigint for bigint a.

UDFOPBitShiftLeft

int

bigint

shiftright(TINYINT|SMALLINT|INT a,int b)
shiftright(bigint a,int b)

Bitwise right shift. Shifts a b positions to the right.

Returns int for tinyint, smallint andint a. Returns bigint for bigint a.

UDFOPBitShiftRight

int

bigint

shiftrightunsigned(TINYINT|SMALLINT|INT a,int b),
shiftrightunsigned(bigint a,int b)

Bitwise unsigned right shift. Shifts a b positions to the right.

Returns int for tinyint, smallint andint a. Returns bigint for bigint a.

UDFOPBitShiftRightUnsigned

T
greatest(T v1, T v2, ...)
Returns the greatest value of the list of values. Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with ">" operator.GenericUDFGreatest
T
least(T v1, T v2, ...)
Returns the least value of the list of values. Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with "<" operator.GenericUDFLeast
int
width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value,int num_buckets)

Returns an integer between 0 and num_buckets+1 by mapping expr into the ith equally sized bucket. Buckets are made by dividing [min_value, max_value]into equally sized regions. If expr < min_value, return 1, if expr > max_value return num_buckets+1. See https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions214.htm

GenericUDFWidthBucket

double
cosh(double x)

Status
colourGreen
titleNew
Returns the hyperbolic cosine of x, where x is in radians. Example: cosh(0) Result: 1

UDFCosh

double
tanh(double x)

Status
colourGreen
titleNew
Returns the hyperbolic tangent of x, where x is in radians. Example: tanh(0) Result: 1

UDFTanh


Collection Functions

...

Return Type

Name(Signature)

Description

OSS

int

size(Map<K.V>)

Returns the number of elements in the map type.


int

size(Array<T>)

Returns the number of elements in the array type.


array<K>

map_keys(Map<K.V>)

Returns an unordered array containing the keys of the input map.


array<V>

map_values(Map<K.V>)

Returns an unordered array containing the values of the input map.


boolean

array_contains(Array<T>, value)

Returns TRUE if the array contains the provided parameter value.


array<t>

sort_array(Array<T>)

Sorts the input array in ascending order according to the natural ordering of the array elements and returns it.


array<t>

array(obj1, obj2, ....  objN)

Status
colourGreen
titleNew
The function returns an array of the same type as the input array with distinct values. Example: array('b', 'd', 'd', 'a') reurtns ['b', 'd', 'a'] 

GenericUDFArrayDistinct.java

array<t>

array_slice(array, start, length)

Status
colourGreen
titleNew
Returns the subset or range of elements. Example: array-slice(array(1, 2, 3, 4), 2 , 2) Result: 3,4

GenericUDFArraySlice * 

t

array_min((array(obj1, obj2, obj3...))

Status
colourGreen
titleNew
The function returns the minimum value in the array with elements for which order is supported. Example: array_min(array(1, 3, 0, NULL)) Result: 0

GenericUDFArrayMin

t

array_max((array(obj1, obj2, obj3...))

Status
colourGreen
titleNew
The function returns the maximum value in the array with elements for which order is supported. Example: array_max(array(1, 3, 0, NULL)) Result: 3

GenericUDFArrayMax

t

array_distinct(array(obj1, obj2, obj3...))

Status
colourGreen
titleNew
The function returns an array of the same type as the input array with distinct values. Example: array_distinct(array('b', 'd', 'd', 'a')) Result:  ['b', 'd', 'a']

GenericUDFArrayDistinct

string

array_join(array, delimiter, replaceNull)

Status
colourGreen
titleNew
Concatenate the elements of an array with a specified delimiter. Example: 

array_join(array(1, 2, NULL, 4), ',',':') Result:  11,2,:,4

GenericUDFArrayJoin

array<t>

array_expect(array1, array2)

Status
colourGreen
titleNew
Returns an array of the elements in array1 but not in array2. Example: array_expect(array(1, 2, 3,4), array(2,3)) Result: [1,4]

GenericUDFArrayExcept

array<t>

array_intersect(array1, array2)

Status
colourGreen
titleNew
Returns an array of the elements in the intersection of array1 and array2, without duplicates. Example: array_intersect(array(1, 2, 3,4), array(1,2,3)) Result: [1,2,3]

GenericUDFArrayIntersect

array<t>

array_union(array1, array2)

Status
colourGreen
titleNew
Returns an array of the elements in the union of array1 and array2 without duplicates. Example: array_union(array(1, 2, 2, 4), array(2, 3)) Result: [1, 2, 3, 4]

GenericUDFArrayUnion

array<t>

array_remove(array, element)

Status
colourGreen
titleNew
Removes all occurrences of elements from the array. Example: array_remove(array(1, 2, 3, 4, 2), 2) Result: [1, 3, 4]

GenericUDFArrayRemove

...