5.4 Applying Functions to Create a New Column
You can perform calculations on the data streaming in the pipeline, and also add new fields into the stream using in-built functions of the Expression Builder.
To launch the Expression Builder, click fx in the Live Output table.
Note:
Currently, you can use expressions only within a query stage.Adding a Constant Value Column
A constant value is a simple string or number. No calculation is performed on a constant value. Enter a constant value directly in the expression builder to add it to the live output table.
Using Functions
You can select a CQL Function from the list of available functions and select the input parameters. Make sure to begin the expression with ”=”. Click Apply to apply the function to the streaming data.
=float((CanceledOrdersFloat/NewOrdersFloat) * 100.0)
Description of the illustration list_of_functions.png
You can see custom functions in the list of available functions when you add/import a custom jar in your pipeline.
For a list of supported functions, see unresolvable-reference.html#GUID-B515EA58-E4F0-4523-B4D5-EFFD8E46F079 .
5.4.1 Using Bessel Functions
The mathematical cylinder functions for integers are known as Bessel functions.
Function Name | Description |
---|---|
|
Returns the modified Bessel function of order 0 of the double argument as a double |
|
Returns the exponentially scaled modified Bessel function of order 0 of the double argument as a double |
|
Returns the modified Bessel function of order 1 of the double argument as a double |
|
Returns the exponentially scaled modified Bessel function of order 1 of the double argument as a double |
|
Returns the Bessel function of the first kind of order n of the argument as a double |
|
Returns the modified Bessel function of the third kind of order n of the argument as a double |
|
Returns the exponentially scaled modified Bessel function of the third kind of order 0 of the double argument as a double |
|
Returns the exponentially scaled modified Bessel function of the third kind of order 1 of the double argument as a double |
|
Returns the Bessel function of the second kind of order n of the double argument as a double |
5.4.1.1 BesselI0
Returns the modified Bessel function of order 0 of the input argument.
The input arguments can be one of the following data types: double, float.
Returned value type will be double.
Function | Result |
---|---|
|
8.403039845625433E26 |
|
1.07389541368045088E17 |
5.4.1.2 BesselIO_exp
Returns the exponentially scaled modified Bessel function of order 0 of the double argument as a double.
The input argument can be one of the following data type: double, integer, float. Returned value type will be double.
Function | Result |
---|---|
|
8.113723742037748E23 |
5.4.1.3 BesselI1(value1)
Function returns the modified Bessel function of order 1 of the double argument.
The input arguments can be one of the following data types: double, integer, float.
The returned value type will be double.
Function | Result |
---|---|
|
2.1043808863643512E186 |
|
2.055972795294565E12 |
5.4.1.4 BesselI1_exp(value1)
Function returns the exponentially scaled modified Bessel function of order 1, of the input argument.
The input arguments can be one of the following types: double, integer, float.
Returned value type will be double.
Function | Result |
---|---|
|
0.03994284829937756 |
5.4.1.5 BesselK0_exp(value1)
Function returns the exponentially scaled modified Bessel function of the third kind of order 0.
Input value can be one of the following types: double, integer, float.
Returned value type will be double.
Function | Result |
---|---|
|
0.6404559726736455 |
5.4.1.6 BesselIK1_exp(value1)
Function returns the exponentially scaled modified Bessel function of the third kind of order 1.
Input value can be one of the following types: double, integer, float.
Returned value type will be double.
Function | Result |
---|---|
|
0.14847048263652857 |
|
0.7244606719817783 |
5.4.1.7 BesselY(value1, value2)
Function returns the Bessel function of the second kind of order n of the input argument.
Value 1 can be of the following types: integer.
Value 2 can be of the following types: double, integer, float.
Returned value type will be double.
Function | Result |
---|---|
|
-1.6816755062290252E29 |
5.4.1.8 BesselJ(value1, value2)
Function returns the Bessel function of the first kind of order n of the argument.
The input arguments can be one of the following data types:
- Value1 can be one of the following types: integer.
- Value 2 can be one of the following types: double, integer, float.
Returned value type will be double.
Function | Result |
---|---|
|
0.1742753869717833 |
5.4.1.9 BesselK(value1,value2)
Function returns the modified Bessel function of the third kind of order n of the input argument.
Value1 can be one of the following types: integer.
Value 2 can be one of the following types: double, integer, float.
Returned value type will be double.
Function | Result |
---|---|
|
4.271125754887687E30 |
5.4.2 Using Conversion Functions
The conversion functions help in converting values from one data type to other.
The following conversion functions are supported in this release:
Function Name | Description |
---|---|
|
Converts the given value to bigdecimal |
|
Converts the given value to logical |
|
Converts the given value to datetime |
|
Converts the given value to double |
|
Converts the given value to float |
|
Converts the given value to integer |
|
Converts the given value to long |
|
Converts the given value to string |
5.4.2.1 bigdecimal(value1)
Converts the input argument value to big decimal. The input argument can be one of the following data types: big integer, number, double, integer, text, float. Returned value type will be number.
Function | Result |
---|---|
|
6E+1 |
|
32 |
5.4.2.2 boolean(value1)
Converts the input argument value to logical. The input argument can be one of the following data type: big integer or integer. Returned value type will be Boolean.
Examples
Function | Result |
---|---|
|
TRUE |
|
FALSE |
|
TRUE |
|
TRUE |
|
TRUE |
5.4.2.3 double(value1)
Converts the input argument value to double. The input argument can be one of the following data types: integer, big integer, double, text or float. Returned value type will be double.
Examples
Function | Result |
---|---|
|
3.1405999660491943 |
|
1234.56005859375 |
5.4.2.4 float(value1)
Converts the input argument value to float. The input argument can be one of the following data types: integer, big integer, double, text or float. Returned value will be a single-precision floating-point number.
Examples
Function | Result |
---|---|
|
1.6789899 |
|
1.7967093 |
|
12.605081 |
5.4.2.5 int(value1)
Converts the input argument value to integer. The input argument can be one of the following types: integer, text. Returned value type will be integer.
Function | Result |
---|---|
|
16 |
5.4.2.6 long()
Converts the input argument value to long. The input argument can be one of the following types: big integer, integer, text, float, timestamp. Returned value type will be big integer.
Function | Result |
---|---|
|
5039505078907524 |
|
22 |
5.4.2.7 string(value1, value2)
Conversion to string.
Value 1 can be one of the following types: intervalym, big integer, number, boolean, double, interval, integer, float, timestamp. Required.
Value 2 is output date format. It's required argument for value1 of type timestamp. Value can be one of the following types: text. Optional.
Returned value will be of type text.
Function | Result |
---|---|
|
12-23-04 |
|
12-19-16 |
5.4.3 Using Date Functions
The following date functions are supported in this release:
Function Name | Description |
---|---|
|
Returns day of the date |
|
Returns event timestamp from stream |
|
Returns hour of the date |
|
Returns minute of the date |
|
Returns month of the date |
|
Returns nanosecond of the date |
|
Returns second of the date |
|
Returns the system’s timestamp on which the application is running |
|
Returns the provided timestamp in required time format |
|
Returns year of the date |
5.4.3.1 Acceptable Formats for Timestamp Values
This sections lists the acceptable formats for timestamp values in Oracle Stream Analytics.
Format | Example Values |
---|---|
|
3/21/2018 11:14:23.1111 |
|
3/21/2018 11:14:23.111 |
|
3/21/2018 11:14:23.11 |
|
3/21/2018 11:14:23.1 |
|
3/21/2018 11:14:23 |
|
3/21/2018 11:14 |
|
3/21/2018 11 |
|
3/21/2018 |
|
11-21-2018 11:14:23.1111 |
|
11-21-2018 11:14:23.111 |
|
11-21-2018 11:14:23.11 |
|
11-21-2018 11:14:23.1 |
|
11-21-2018 11:14:23 |
|
11-21-2018 11:14 |
|
11-21-2018 11 |
|
11-21-2018 |
|
11-Jan-18 11.14.23.111111 AM |
|
11-Jan-18 11.14.23.1111 |
|
11-Jan-18 11.14.23.111 |
|
11-Jan-18 11.14.23.11 |
|
11-Jan-18 11.14.23.1 |
|
11-Jan-18 11.14.23 |
|
11-Jan-18 11.14 |
|
11-Jan-18 11 |
|
11-Jan-18 |
|
15/MAR/18 |
|
2018-03-5 15:16:0.756000 +5:30, 2018-03-5 15:16:0.756000 |
|
2018-03-5 15.16.0.756000 +5:30, 2018-03-5 15.16.0.756000 |
|
2018-03-5 15:16:0; 2018-03-5 15:16:0 +5:30 |
|
2018-03-5 15.16.0; 2018-03-5 15.16.0 +5:30 |
|
2018-03-5 15:16; 2018-03-5 15:16 +5:30 |
|
2018-03-5 15.16; 2018-03-5 15.16 +5:30 |
|
2018-03-5 15 |
|
2018-03-5 |
|
11:14:14 PST |
|
2018-03-04T12:08:56.235 |
|
2018-03-04T12:08:56.235-0700 |
|
2018-03-04T12:08:56.235 PDT |
|
2018-03-04T12:08:56 |
|
2018-03-04T12:08:56-0700 |
|
2018-03-04T12:08:56 PDT |
5.4.3.2 Day(date)
day(date)
function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the day in the timestamp represented by this date object. Returns a big integer indicating the day represented by this date.
Examples
Function | Result |
---|---|
|
19 |
5.4.3.3 eventtimestamp(value1)
Event timestamp from stream.
Returned value will be of type timestamp.
Function | Result |
---|---|
|
4/4/2019 16:40:57 |
5.4.3.4 hour(date)
hour(date)
function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the hour in the time represented by this date object. Returns a big integer indicating the hour of the time represented by this date.
Examples
Function | Result |
---|---|
|
09 |
|
12 |
5.4.3.5 minute(date)
minute(date)
function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the minutes in the time represented by this date object. Returns a big integer indicating the minutes of the time represented by this date.
Examples
Function | Result |
---|---|
|
15 |
|
45 |
5.4.3.6 month(date)
month(date)
function takes as an argument any one of the following data types: time interval or timestamp. The returned value represents the month of the year that contains or begins with the instant in time represented by this date object. Returns a big integer indicating the month of the year represented by this date.
Examples
Function | Result |
---|---|
|
12 |
|
9 |
5.4.3.7 nanosecond(value1)
Extracts and returns the current fractional part of second from date.
Value 1 can be one of the following types: timestamp.
Returned value will be of type big integer.
Function | Result |
---|---|
|
719978080 |
5.4.3.8 systemtimestamp(value1)
Returns the current system time.
Returned value will be of type timestamp.
Function | Result |
---|---|
|
4/4/2019 17:06:14 |
5.4.3.9 timeformat(value1, value2)
Event Formatted time.
Value 1 can be one of the following types: timestamp.
Value 2 can be one of the following types: text.
Returned value will be of type text.
Function | Result |
---|---|
|
12-19-16 |
timeformat(transaction_time, "DAY") , where transaction_time is 12/19/2016 12:22:44
|
Monday |
5.4.3.10 Year(date)
year(date)
function takes as an argument any one of the following data types: time interval or time stamp. The returned value represents the year of the instant in time represented by this date object. Returns a big integer indicating the year represented by this date.
Examples
Function | Result |
---|---|
|
17 |
|
2015 |
5.4.4 Using Geometry Functions
The Geometry functions allow you to convert the given values into a geometrical shape.
The following interval functions are supported in this release:
Function Name | Description |
---|---|
|
Returns a 2–dimensional point type geometry from the given latitude and longitude. The default SRID is 8307. The return value is of the datatype |
|
Returns distance between the first set of latitude, longitude and the second set of latitude, longitude values. The default SRID is 8307. Note: Only SRID 8307 is supported in the current release.The return value is of the datatype |
5.4.4.1 CreatePoint(value1, value2, value3)
createPoint(lat,long,SRID) - Function Returns a 2d point type geometry, default SRID is 8307.
Value 1: Latitude - Value can be one of the following types: number, double, float. Required.
Value 2: Longitude - Value can be one of the following types: number, double, float.
Value 3: SRID - Value can be one of the following types: integer.
Returned value type will be sdo geometry.
Function | Result |
---|---|
|
point |
5.4.4.2 distance(lat1, long1, lat2, long2,SRID)
Function Returns distance between lat1/long1 and lat2/long2, default SRID is 8307.
Value 1: Latitude1 - Value can be one of the following types: number, double, float.
Value 2: Longitude1 - Value can be one of the following types: number, double, float.
Value 3: Latitude2 - Value can be one of the following types: number, double, float.
Value 4: Longitude2 - Value can be one of the following types: number, double, float.
Value 5: SRID - Value can be one of the following types: integer.
Returned value will be of type double.
Function | Result |
---|---|
|
1.1394718018250743E7 |
5.4.5 Using Interval Functions
The Interval functions help you in calculating time interval from given values.
The following interval functions are supported in this release:
Function Name | Description |
---|---|
|
Converts the given value to a numeric value. User must provide unit for the output numeric value as second argument to this function. Allowed values for the unit is: DAY,HOUR,MINUTE,SECOND |
|
Converts the given value to an The return value is of the datatype |
|
Converts the given value to an |
|
Converts a string in format The return value is of the datatype |
|
Converts a string in format The return value is of the datatype |
|
Converts the given value to a numeric value. User must provide unit for the output numeric value as second argument to this function. Allowed values for the unit is: YEAR,MONTH |
5.4.5.1 dsintervaltonum(value1, value 2)
DSINTERVALTONUM(c1 INTERVAL DAY TO SECOND, c2 char) - Function will convert interval value(c1) into a numeric value. User must provide unit for the output numeric value as second argument to this function. Allowed values for the unit is: DAY,HOUR,MINUTE,SECOND
Input value 1 can be one of the following types: interval.
Input value 2 can be one of the following types: text.
Returned value will be of type double.
Function | Result |
---|---|
|
301.0 |
|
5.016666666666667 |
5.4.5.2 numtodsinterval(value1, value2)
Function converts n to an INTERVAL DAY TO SECOND literal.The value for interval_unit specifies the unit of n and must resolve to one of the following string values:DAY,HOUR,MINUTE,SECOND.
Value 1 can be one of the following types: big integer, double, integer, float.
Value 2 can be one of the following types: text.
Returned value will be of type interval.
Function | Result |
---|---|
|
2 yy 10 mm |
|
1 dd 2 hr 30 mm 0 sec |
|
00 dd 20 hr 30 mm 0 sec |
|
1 dd 0 hr 0 mm 0 sec |
5.4.5.3 numtoyminterval(value1, value 2)
NUMTOYMINTERVAL(n,interval_unit) - Function converts n to an INTERVAL YEAR TO MONTH literal. The value for interval_unit specifies the unit of n and must resolve to one of the following string values:YEAR, MONTH.
Value 1 can be one of the following types: big integer, double, integer, float.
Value 2 can be one of the following types: text.
Returned value will be of type intervalym.
Function | Result |
---|---|
|
10 yy 6 mm |
|
2 yy 10 mm |
5.4.5.4 to_dsinterval(value1)
Function converts a string in format 'DD HH:MM:SS' into a INTERVAL DAY TO SECOND data type. The DD part indicates the number of days between 0 to 99. The HH:MM:SS part indicates the number of hours, minutes and seconds in the interval from 0:0:0 to 23:59:59.999999. The second part can accept upto 6 decimal places.
Input value can be one of the following types: text.
Returned value will be of type interval.
Function | Result |
---|---|
|
2 dd 23hr 34mm 12 sec |
5.4.5.5 to_yminterval(value1)
Function converts a string in format 'YY-MM' into a INTERVAL YEAR TO MONTH data type.The YY part indicates the number of years between 0 to 99. The MM part indicates the number of months between 0-11.
Value can be one of the following types: text.
Returned value type will be intervalym.
Function | Result |
---|---|
|
94 yy 3 mm |
5.4.5.6 ymintervaltonum(value1, value2)
Function converts interval value(c1) into a numeric value. You must provide the unit for the output numeric value as the second argument to this function. Allowed values for the unit are: YEAR,MONTH.
Value 1 can be one of the following types: intervalym.
Value 2 can be one of the following types: text.
Returned value type will be double.
Function | Result |
---|---|
|
1133.0 |
5.4.6 Using Math Functions
The math functions allow you to perform various mathematical operations and calculations ranging from simple to complex.
The following math functions are supported in this release:
Function Name | Description |
---|---|
|
Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard |
|
Returns the absolute value of a number |
|
Returns arc cosine of a value |
|
Returns arc sine of a value |
|
Returns arc tangent of a value |
|
Returns polar angle of a point ( |
|
Returns binomial coefficient of the base raised to the specified power |
|
BitMask with BitsSet (From, To) |
|
Returns cubic root of the specified value |
|
Rounds to ceiling |
|
Returns the first floating-point argument with the sign of the second floating-point argument |
|
Returns cosine of a value |
|
Returns cosine hyperbolic of a value |
|
Returns exponent of a value |
|
More precise equivalent of |
|
Returns factorial of a natural number |
|
Rounds to floor |
|
Returns the unbiased exponent used in the representation of a double |
|
Returns a deterministic seed as an integer from a (seemingly gigantic) matrix of predefined seeds |
|
Returns an integer hashcode for the specified double value |
|
Returns square root of sum of squares of the two arguments |
|
Returns the least significant 64 bits of this UUID's 128 bit value |
|
Calculates the log value of the given argument to the given base, where |
|
Returns the natural logarithm of a number |
|
Calculates the log value of the given argument to base 10 |
|
Calculates the log value of the given argument to base 2 |
|
Returns the natural logarithm (base e) of the factorial of its integer argument as a double |
|
Returns the factorial of its integer argument (in the range k >= 0 && k < 21) as a long |
|
Returns the maximum of 2 arguments |
|
Returns the minimum of 2 arguments |
|
Returns modulo of a number |
|
Returns the most significant 64 bits of this UUID's 128 bit value |
|
Returns the floating-point number adjacent to the first argument in the direction of the second argument |
|
Returns the floating-point value adjacent to the input argument in the direction of negative infinity |
|
Returns the floating-point value adjacent to the input argument in the direction of positive infinity |
|
Returns m raised to the nth power |
|
Returns the double value that is closest in value to the argument and is equal to a mathematical integer |
|
Rounds to the nearest integral value |
|
Returns d × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set |
|
Returns signum of an argument as a double value |
|
Returns sine of a value |
|
Returns sine hyperbolic of a value |
|
Returns square root of a value |
|
Returns the correction term of the Stirling approximation of the natural logarithm (base e) of the factorial of the integer argument as a double |
|
Returns tangent of a value |
|
Returns tangent hyperbolic of a value |
|
Converts the argument value to degrees |
|
Returns the measurement of the angle in radians |
|
Returns the size of an ulp of the argument |
5.4.6.1 IEEEremainder(value1, value1)
Computes the remainder operation on two arguments as prescribed by the IEEE 754 standard : IEEEREMAINDER
Value 1 can be one of the following types: double.
Value 2 can be one of the following types: double
Returned value type will be double.
Function | Result |
---|---|
|
-1.0 |
5.4.6.2 abs(value1)
Returns the Absolute value of the input argument.
Input value can be one of the following types: number, big integer, double, integer, float.
Returned value type will be the same as the input argument type.
Function | Result |
---|---|
|
1234.56078 |
abs(0.67) |
0.6700000166893005 |
5.4.6.3 acos(value1)
Returns the Arc cosine of a value.
Value can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
1.0471975511965979 |
5.4.6.4 asin(value1)
Computes the arc sine of a value.
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value type will be double.
Function | Result |
---|---|
|
0.5235987755982989 |
5.4.6.5 atan(value1)
Returns the arc tangent of the input value.
Input value can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
1.5413930385908916 |
5.4.6.6 atan2
Returns the polar angle of a point (value2, value1).
Value 1 can be one of the following types: big integer, double, integer, float.
Value 2 can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
0.7853405725825559 |
5.4.6.7 binomial(base, power)
Returns the Binomial coefficient of the input base and power values.
Value 1 can be one of the following types: big integer, double, integer, float.
Value 2 can be one of the following types: big integer, integer.
Returned value will be of type double.
Function | Result |
---|---|
|
5.955734227594785E104 |
5.4.6.8 bitMaskWithBitsSetFromTo(value1, value2)
Value 1 can be one of the following types: integer.
Value 2 can be one of the following types: integer.
Returned value will be of type double.
Function | Result |
---|---|
|
8388608.0 |
5.4.6.9 cbrt()
Returns the cubic root of a value.
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value type will be double.
Function | Result |
---|---|
|
3 |
5.4.6.10 ceil()
Round to ceiling.
The input arguments can be one of the following data types: double, float.
Returned value type will be float.
Function | Result |
---|---|
|
65.0 |
5.4.6.11 copySign()
Function returns the first floating-point argument with the sign of the second floating-point argument.
Value1 can be one of the following types: double, float.
Returned value type will be double, float.
Function | Result |
---|---|
|
-3.0 |
5.4.6.12 cos(value1)
Returns the cosine of a value
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value type will be double.
Function | Result |
---|---|
|
0.9964325256163951 |
5.4.6.13 cosh(value1)
Returns the Cosine hyperbolic of a value.
Value 1 can be one of the following types: big integer, double, integer, float.
Function | Result |
---|---|
|
1.1276259652063807 |
5.4.6.14 exp(value1, value2)
Returns the exponent of a value.
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
22026.465794806718 |
5.4.6.15 expm1(value1)
Returns the more precise equivalent of Exp(x)-1 when x is around zero.
Value can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
1.0137526834646737 |
5.4.6.16 factorial(value1)
Returns the Factorial of a natural.
Value 1 can be one of the following types: integer.
Returned value type will be double.
Function | Result |
---|---|
|
720.0 |
5.4.6.17 floor(value1)
Value can be one of the following types: big integer, double, integer, float.
Returned value will be of type float.
Function | Result |
---|---|
|
0.0 |
5.4.6.18 GetExponent(value1)
Function returns the unbiased exponent used in the representation of a double.
Value 1 can be one of the following types: double, float.
Returned value will be of type integer.
Function | Result |
---|---|
|
3.0 |
5.4.6.19 getSeedAtRowColumn(value1, value2)
Returns a deterministic seed as an integer from a (seemingly gigantic) matrix of predefined seeds : GETSEEDATROWCOLUMN
Value 1 can be one of the following types: integer.
Value 2 can be one of the following types: integer.
Returned value will be of type integer.
Function | Result |
---|---|
|
443210610 |
5.4.6.20 hash(value1)
Function returns an integer hashcode for the specified value.
Value can be one of the following types: big integer, double, integer, float.
Returned value will be of type integer.
Function | Result |
---|---|
|
1.33589862E9 |
5.4.6.21 hypot(value1, value2)
Square root of sum of squares of the two arguments.
Value 1 can be one of the following types: big integer, double, integer, float.
Value 2 can be one of the following types: big integer, double, integer, float.
Returned value type will be double.
Function | Result |
---|---|
|
4.47213595499958 |
5.4.6.22 LeastSignificantBit(value1)
Method is used to return the least significant 64 bits of this UUID's 128 bit value.
Value 1 can be one of the following types: integer.
Returned value will be same as the input argument.
Function | Result |
---|---|
|
1.0 |
5.4.6.23 log(value1, value2)
Logarithm(base, arg)
Value 1 can be one of the following types: big integer, double, integer, float.
Value 2 can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
0.3667257913420846 |
5.4.6.24 log1(value1)
Function returns the natural logarithm of a number.
Value 1 can be one of the following types: double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
2.995732273553991 |
5.4.6.25 log10(value1)
Logarithm(10, arg)
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
1.301029995663981 |
5.4.6.26 log2(value1)
Logarithm(2, arg)
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
4.321928094887362 |
5.4.6.27 logFactorial(value1)
Function returns the natural logarithm (base e) of the factorial of its integer argument as a double.
Value 1 can be one of the following types: double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
42.335616460753485 |
5.4.6.28 long()
Converts the input argument value to long. The input argument can be one of the following types: big integer, integer, text, float, timestamp. Returned value type will be big integer.
Function | Result |
---|---|
|
5039505078907524 |
|
22 |
5.4.6.29 longFactorial(value1)
Function returns the natural logarithm (base e) of the factorial of its integer argument as a double.
Value 1 can be one of the following types: double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
15.104412573075516 |
5.4.6.30 minimum(value1, value2)
Returns the minimum of two arguments. The first argument is a value to compare with the second argument’s value and can be any one of the following data type: big integer, double, interval, integer, float. The second argument is a value to compare with the first argument’s value and can be any one of the following data type: big integer, double, interval, integer, float.
Examples
Function | Result |
---|---|
|
16321 |
|
3.10 |
Note:
If the user provides two different data types as arguments, then Stream Analytics does implicit conversion to convert one argument to the other argument’s type.5.4.6.31 mod(value1, value2)
Functions returns Modulo of a number
Value 1 can be one of the following types: big integer, double, integer, float.
Value 2 can be one of the following types: big integer, double, integer, float.
Returned value will be of the same type as the first argument.
Function | Result |
---|---|
|
1.0 |
5.4.6.32 mostSignificantBit(value1)
Function returns the most significant 64 bits of this UUID's 128 bit value .
Value 1 can be one of the following types: integer.
Returned value will be of the same type as the first argument.
Function | Result |
---|---|
|
3.0 |
5.4.6.33 nextAfter(value1, value2)
Function returns the floating-point number adjacent to the first argument in the direction of the second argument.
Value 1 can be one of the following types: double, float.
Value 2 can be one of the following types: double, float.
Returned value will be the same type as the first argument.
Function | Result |
---|---|
|
5.4.6.34 nextDown(value1, value2)
Function returns the floating-point number adjacent to the first argument in the direction of the second argument.
Value 1 can be one of the following types: double, float.
Value 2 can be one of the following types: double, float.
Returned value will be the same type as the first argument.
Function | Result |
---|---|
|
5.4.6.35 nextUp(value1)
Function returns the floating-point number adjacent to the first argument in the direction of the second argument.
Value 1 can be one of the following types: double, float.
Returned value will be the same type as the first argument.
Function | Result |
---|---|
|
5.4.6.36 pow(value1, value2)
Power function returns m raised to the nth power.
Value 1 can be one of the following types: double, integer, float.
Value 2 can be one of the following types: double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
144 |
5.4.6.37 rint(value1)
Returns the double value that is closest in value to the argument and is equal to a mathematical integer.
Value can be one of the following types: double.
Returned value will be of type double.
Function | Result |
---|---|
|
5.4.6.38 round(value1)
Rounds the argument value to the nearest integer value. The input argument can be of the following data types: big integer, double, integer, float.
Examples
Function | Result |
---|---|
|
7 |
|
39 |
|
4 |
5.4.6.39 scalb(
Function Return d × 2scaleFactor rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set.
Value 1 can be one of the following types: double, float.
Value 2 can be one of the following types: integer.
Returned value will be the same type as the first argument.
Function | Result |
---|---|
|
40.0 |
5.4.6.40 signum(value1)
Signum of an argument as a double value.
Value 1 can be one of the following types: number, big integer, double, integer, float.
Returned value will be of type integer.
Function | Result |
---|---|
|
1.0 |
5.4.6.41 sin(value1)
Returns the sine of the input value.
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
0.08419864005868474 |
5.4.6.42 sinh(value1)
Returns the Sine hyperbolic of a value.
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
0.5210953054937474 |
5.4.6.43 sqrt(value1)
Returns the Square root of the input value.
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value will be of the type double.
Function | Result |
---|---|
|
86.22488040003303 |
5.4.6.44 stirlingCorrection(value1)
Returns the correction term of the Stirling approximation of the natural logarithm (base e) of the factorial of the integer argument as a double: STIRLINGCORRECTION
Value 1 can be one of the following types: integer.
Returned value will be of the type double.
Function | Result |
---|---|
|
0.0011904680924708464 |
5.4.6.45 tan(value1)
Returns the Tangent of a value.
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
0.320040389379563 |
5.4.6.46 tanh(value1)
Returns the Tangent hyperbolic of a value.
Value 1 can be one of the following types: big integer, double, integer, float.
Returned value will be of type double.
Function | Result |
---|---|
|
0.7615941559557649 |
5.4.6.47 toDegrees(value1)
Converts the argument value to degrees. The input argument is an angle in radians and can be of type double. The returned value will be the measurement of the angle in degrees and is of type double.
Examples
Function | Result |
---|---|
|
180.0 |
|
45.0 |
5.4.7 Using Null-related Functions
The following null-related functions are supported in this release:
Function Name | Description |
---|---|
|
Replaces null with a value of the same type |
5.4.7.1 nvl(value1, value2)
nvl
lets you replace null (returned as a blank) with a value of the same type as the first argument. For example, in a list of employees and commission, you can substitute Not Applicable if the employee receives no commission using the nvl(value1,value2)
function as nvl(Not Applicable,Commission)
.
Example
Function | Result |
---|---|
|
Not Applicable |
5.4.8 Using Statistical Functions
Statistical functions help you in calculating the statistics of different values.
The following statistical functions are supported in this release:
Function Name | Description |
---|---|
|
Returns the area from zero to |
|
Returns the area under the right hand tail (from |
|
Returns the sum of the terms 0 through |
|
Returns the sum of the terms |
|
Returns the area under the left hand tail (from 0 to |
|
Returns the area under the right hand tail (from |
|
Returns the error function of the normal distribution |
|
Returns the complementary error function of the normal distribution |
|
Returns the gamma function of the arguments |
|
Returns the integral from |
|
Returns the incomplete beta function evaluated from zero to |
|
Returns the incomplete gamma function |
|
Returns the complemented incomplete gamma function |
|
Returns the natural logarithm of the gamma function |
|
Returns the sum of the terms 0 through |
|
Returns the sum of the terms |
|
Returns the area under the normal (Gaussian) probability density function, integrated from minus infinity to |
|
Returns the value for which the area under the normal (Gaussian) probability density function is equal to the argument |
|
Returns the sum of the first |
|
Returns the sum of the terms |
|
Returns the integral from minus infinity to |
|
Returns the value, for which the area under the Student-t probability density function is equal to |
5.4.8.1 beta1(value1, value2, value3)
Returns the area from zero to value3 under the beta density function.
The input arguments can be one of the following data types: double, float. Returned value is of type double.
Values 1 and 2 must be greater than 0.0. Value 3 must be greater than 0 and less than 1.
Function | Result |
---|---|
|
0.8620112116492348 |
|
1.40801423421089E-63 |
5.4.8.2 betacomplemented(value1, value2, value3)
Returns the area under the right hand tail (value 3 to infinity) of the beta density function.
The input arguments can be one of the following data types: double, float. Returned value is of type double.
Values 1 and 2 must be greater than 0.0. Value 3 must be greater than 0 and less than 1.
Function | Result |
---|---|
|
0.017407170120127144 |
5.4.8.3 binomial2(value1, value2, value3)
Returns the sum of the terms 0 through value1 of the Binomial probability density.
- Value 1 - The end term. Data Type: Integer.
- Value 2 - The number of trials. Data type: Integer.
- Value 3 - The probability of success. Value must be between 0.0 and 1.0. Data type: Double, float.
Function | Result |
---|---|
|
1.0 |
5.4.8.4 binomialcomplemented(value1, value2, value3)
Returns the sum of the terms value1 + 1 through value 2, of the Binomial probability density.
- Value 1 - The end term. Data Type: Integer.
- Value 2 - The number of trials. Data type: Integer.
- Value 3 - The probability of success. Value must be between 0.0 and 1.0. Data type: Double, float.
The returned value is of the type double.
Function | Result |
---|---|
|
0.125 |
5.4.8.5 chiSquare(value1, value2)
Returns the area under the left hand tail (from 0 to value2) of the Chi square probability density function with value1 degrees of freedom. The arguments must both be positive.
Value 1: The degrees of freedom. Value can be one of the following types: double, float.
Value 2: The integration end point. Value can be one of the following types: double, float.
Returned value type will be double.
Function | Result |
---|---|
chiSquare(3.0, 5.0) | 0.8282028557032665 |
5.4.8.6 chiSquareComplemented(value1, value2)
Returns the area under the right hand tail (from value2 to infinity) of the Chi square probability density function with value1 degrees of freedom. The arguments must both be positive.
Value 1 is the degrees of freedom. Value can be one of the following types: double, float.
Value 2 is the Chi-square variable. Value can be one of the following types: double, float.
Returned value type will be double.
Function | Result |
---|---|
|
0.1717971442967335 |
5.4.8.7 errorFunction(value1)
Returns the error function of the normal distribution.
Value 1 can be one of the following types: double, float.
Returned value type will be double.
Function | Result |
---|---|
|
0.9999999999984626 |
5.4.8.8 errorFunctionComplemented(value1)
Returns the complementary Error function of the normal distribution.
Value 1 can be one of the following types: double, float.
Returned value type will be double.
Function | Result |
---|---|
errorFunctionComplemented(5.0) | 1.5374597944280347E-12 |
5.4.8.9 gamma(value1, value2, value3)
Returns the gamma function of the input arguments.
Value1: The paramater a (alpha) of the gamma distribution. Value can be one of the following types: double, float. Required.
Value 2: The paramater b (beta, lambda) of the gamma distribution. Value can be one of the following types: double, float. Optional.
Value 3: The integration end point. Value can be one of the following types: double, float. Optional.
Returned value type will be double.
Function | Result |
---|---|
gamma(1.0,2.0,5.0) |
0.04042768199451279 |
5.4.8.10 gammacomplemented(value1, value2, value3)
Returns the integral from value3 to infinity of the gamma probability density function.
Value1: The paramater a (alpha) of the gamma distribution. Value can be one of the following types: double, float.
Value 2: The paramater b (beta, lambda) of the gamma distribution. Value can be one of the following types: double, float. R
value3The integration end point. Value can be one of the following types: double, float.
Returned value type will be double.
Function | Result |
---|---|
|
0.04042768199451279 |
5.4.8.11 incompleteBeta(value1, value2, value3)
Returns the Incomplete Beta Function evaluated from zero to value3. Where values must be in range (Value1 && Value2 > 0.0) and (Value3 > 0 && Value3 < 1).
Value1: The alpha parameter of the beta distribution. Value can be one of the following types: double, float. Required.
Value2: The beta parameter of the beta distribution. Value can be one of the following types: double, float. Required.
Value3: The integration end point. Value can be one of the following types: double, float. Required.
Returned value type will be double.
Function | Result |
---|---|
incompleteBeta(1.0,2.0,0.5) |
0.75 |
5.4.8.12 incompleteGamma(value1, value2)
Returns the Incomplete Gamma function.
Value1: The parameter of the gamma distribution. Value can be one of the following types: double, float. Required.
Value2: The integration end point. Value can be one of the following types: double, float. Required.
Returned value type will be double.
Function | Result |
---|---|
|
0.8646647167633873 |
5.4.8.13 incompleteGammaComplement(value1, value2)
Returns the Complemented Incomplete Gamma function.
Value1: The parameter of the gamma distribution. Value can be one of the following types: double, float. Required.
Value2: The integration start point. Value can be one of the following types: double, float. Required.
Returned value type will be double.
Function | Result |
---|---|
incompleteGammaComplement(1.0, 2.0) | 0.1353352832366127 |
5.4.8.14 logGamma(value1)
Returns the natural logarithm of the gamma function
Value can be one of the following types: double, float.
Returned value will be of type double.
Function | Result |
---|---|
logGamma(7795.6) | 62059.66356433673 |
5.4.8.15 negativeBinomial(value1, value2, value3)
Returns the sum of the terms 0 through value1 of the Negative Binomial Distribution. All arguments must be positive.
Value1: The end term. Value can be one of the following types: integer. Required.
Value2: The number of trials. Value can be one of the following types: integer. Required.
Value3: The probability of success [must be in (0.0,1.0)]. Value can be one of the following types: double, float. Required.
Returned value type will be double.
Function | Result |
---|---|
negativeBinomial(1,2,0.5) |
0.5 |
5.4.8.16 negativeBinomialComplemented(value1, value2, value3)
Returns the sum of the terms value1+1 to infinity of the Negative Binomial distribution. All arguments must be positive.
Value1: The end term. Value can be one of the following types: integer. Required.
Value2: The number of trials. Value can be one of the following types: integer. Required.
Value3: The probability of success [must be in (0.0,1.0)]. Value can be one of the following types: double, float. Required.
Returned value type will be double.
Function | Result |
---|---|
|
0.5 |
5.4.8.17 normal(value1, value2, value3)
Returns the area under the Normal (Gaussian) probability density function, integrated from minus infinity to value1 (assumes mean is zero, variance is one).
Value1: The mean of the normal distribution. Value can be one of the following types: double, float. Required.
Value2: The variance of the normal distribution. Value can be one of the following types: double, float. Optional.
Value3: The integration limit. Value can be one of the following types: double, float. Optional.
Returned value type will be double.
Function | Result |
---|---|
|
0.004687384229717484 |
5.4.8.18 normalInverse(value1)
Returns the value for which the area under the Normal (Gaussian) probability density function is equal to the argument value1 (assumes mean is zero, variance is one).
Input value should be between 0 and 1. The value can be one of the following types: double, float. Required.
Returned value type will be double.
Function | Result |
---|---|
|
0.0 |
5.4.8.19 poisson(value1, value2)
Returns the sum of the first value1 terms of the Poisson distribution. Both the arguments must be positive.
Value1: The number of terms. Value can be one of the following types: integer. Required.
Value2: The mean of the poisson distribution. Value can be one of the following types: double, float. Required.
Returned value type will be double.
Function | Result |
---|---|
|
3.0509714140892473E-10 |
5.4.8.20 poissonComplemented(value1, value2)
Returns the sum of the terms value1+1 to Infinity of the Poisson distribution.
Value1: The start term. Value can be one of the following types: integer. Required.
Value2: The mean of the poisson distribution. Value can be one of the following types: double, float. Required.
Returned value type will be double.
Function | Result |
---|---|
|
0.08391794203130347 |
5.4.8.21 studentT(value1, value2)
Returns the integral from minus infinity to 'value2' of the Student-t distribution with value1 > 0 degrees of freedom.
Value1: The degrees of freedom. Value can be one of the following types: double, float. Required.
Value2: The integration end point. Value can be one of the following types: double, float. Required.
Returned value type will be double.
Function | Result |
---|---|
|
0.9811252243246882 |
5.4.8.22 studentTInverse(value1, value2)
Returns the value, for which the area under the Student-t probability density function is equal to 1-value1/2. The function uses the studentT function to determine the return value iteratively.
Value1: The probability. Value can be one of the following types: double, float. Required.
Value2: The size of data set. Value can be one of the following types: integer. Required.
Returned value type will be double.
Function | Result |
---|---|
|
0.6998121397488263 |
5.4.9 Using String Functions
The following String functions are supported in this release:
Function Name | Description |
---|---|
|
Returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null |
|
Returns concatenation of values converted to strings |
|
Returns first index of |
|
Returns a specified text expression, with the first letter of each word in uppercase and all other letters in lowercase |
|
Returns the length of the specified string |
|
Returns a matching pattern |
|
Converts the given string to lower case |
|
Pads the left side of a string with a specific set of characters (when |
|
Removes all specified characters from the left hand side of a string |
|
Replaces all |
|
Pads the right side of a string with a specific set of characters (when |
|
Removes all specified characters from the right hand side of a string |
|
Returns substring of a 'string' when indices are between 'from' (inclusive) and up to the end of the string |
|
Returns substring of a \'string\' when indices are between \'from\' (inclusive) and \'to\' (exclusive) |
|
Replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. |
|
Converts given string to uppercase |
5.4.9.1 coalesce(value1,... )
coalesce
returns the first non-null expression in the list of expressions. You must specify at least two expressions. If all expressions evaluate to null then the coalesce
function will return null.
For example:
In coalesce(expr1,expr2)
:
-
If
expr1
is not null then the function returnsexpr1
. -
If
expr1
is null then the function returnsexpr2
. -
If
expr1
andexpr2
are null then the function returns null.
In coalesce(expr1,expr2,......,exprn)
-
If
expr1
is not null then the function returnsexpr1
. -
If
expr1
is null then the function returnsexpr2
. -
If
expr1
andexpr2
are null then the function returns the next non-null expression.
5.4.9.2 Concat(value1,...)
Concat(value1,...) - Concatenation of values converted to strings
Value1: A part of string to concatenate with others. Value can be one of the following types: big integer, number, double, text, integer, float, timestamp. Required.
Vararg1: A part of string to concatenate with others. Value can be one of the following types: big integer, number, double, text, integer, float, timestamp. Optional.
Returned value will be of type text.
Function | Result |
---|---|
|
Declan BENNETT0142354466948788 |
5.4.9.3 indexof(value1, value2)
Returns first index of 'match' in 'string' or -1 if not found
Value1: First argument. Value can be one of the following types: text. Required.
Value2: Second argument. Value can be one of the following types: text. Required.
Returned value type will be integer.
Function | Result |
---|---|
|
17 |
indexof(client_name,"c") , where client name is Braden Gray
|
-1 |
5.4.9.4 initcap(value1)
Function returns a specified text expression, with the first letter of each word in uppercase and all other letters in lowercase : INITCAP
Value1: A text expression. Value can be one of the following types: text.
Returned value will be of type text.
Function | Result |
---|---|
|
Owen Taylor |
5.4.9.5 length(value1)
Returns the length in characters of the string passed as an input argument. The input argument is of the data type text. The returned value is an integer representing the total length of the string.
If value1 is null, then length(value1)
returns null.
If value1 is an empty string, then length(value1)
returns null.
Examples
Function | Result |
---|---|
|
3 |
|
ERROR: Function has invalid parameters. |
|
4 |
|
NULL |
|
NULL |
|
30 |
5.4.9.6 like(string, pattern)
Function returns 'true' or 'false' based on the string matching the supplied pattern. .
Value 1 can be one of the following types: text.
Value 2 can be one of the following types: text.
Returned value type will be boolean.
Function | Result |
---|---|
|
True |
|
False |
5.4.9.7 lower(value1)
Converts a string to all lower-case characters. The input argument is of the data type text. The returned value is the lowercase of the specified string.
Examples
Function | Result |
---|---|
|
product |
|
abcdef |
|
abc |
5.4.9.8 lpad(value1, value2, value3)
LPad(text-exp , length [,pad-exp]) - Function pads the left-side of a string with a specific set of characters (when string1 is not null). : LPAD
Value1: text-exp - A text expression that you want to pad. Value can be one of the following types: text.
Value 2: length - The total length of the return value as it is displayed on your screen. Value can be one of the following types: integer.
Value 3: pad-exp - A text expression that specifies the padding characters. The default value of pad-exp is a single blank. Value can be one of the following types: text.
Returned value type will be text.
Function | Result |
---|---|
|
eeeeeDavid |
5.4.9.9 ltrim(value1, value2)
The ltrim() function removes all specified characters from the left-hand side of a string : LTRIM.
Value 1 can be one of the following types: text.
Value 2 can be one of the following types: text.
Returned value type will be text.
Function | Result |
---|---|
|
lphonse Gabriel CAPONE |
5.4.9.10 replace(string, match, replacement)
Replaces all match
characters in a string with replacement
characters. The first input argument is the string
and is of the data type text. The second argument is the match
and is of the data type text. The third argument is replacement
and is of data type text. The returned value is a text in which the third string argument (replacement
) replaces the second string argument (match
).
If match
is not found in the string, then the original string will be returned.
Examples
Function | Result |
---|---|
|
aabbffdd |
|
aabbffcdd |
|
aabbddee |
5.4.9.11 rpad(value1, value2, value3)
RPad(text-exp , length [,pad-exp]) - Function pads the right-side of a string with a specific set of characters (when string1 is not null). : RPAD
Value 1: text-exp - A text expression that you want to pad. Value can be one of the following types: text.
Value 2: length - The total length of the return value as it is displayed on your screen. Value can be one of the following types: integer.
Value 3: pad-exp - A text expression that specifies the padding characters. The default value of pad-exp is a single blank. Value can be one of the following types: text.
Returned value will be of type text.
Function | Result |
---|---|
|
Levi Cruzaaaaaaaaaaaaaaaa |
5.4.9.12 rtrim(value1, value2)
The rtrim() function removes all specified characters from the right-hand side of a string : RTRIM.
Value 1 can be one of the following types: text.
Value 2 can be one of the following types: text.
Returned value type will be text.
Function | Result |
---|---|
|
Cooper DAVI |
5.4.9.13 substr()
Substr(string, from) - Substring of a 'string' when indices are between 'from' (inclusive) and up to the end of the string.
Value 1 can be one of the following types: text.
Value 2 can be one of the following types: integer.
Returned value type will be text.
Function | Result |
---|---|
|
n THOMPSON |
5.4.9.14 substring(string, from, to)
Returns a substring of a string when indices are between from
(inclusive) and to
(exclusive). The first input argument is the string
and is of the data type text. The second argument is the start index and is an integer. The third argument is the finish index and is an integer. The returned value is a substring and is of type text.
Examples
Function | Result |
---|---|
|
cdef |
|
abcde |
5.4.9.15 translate(expression, from_string, to_string)
Function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time.
Vallue1: exp - A text expression in which you want to replace characters. Value can be one of the following types: text.
Value2: from_string - A text expression that is the characters you want to replace. Value can be one of the following types: text.
Value3: to_string - A text expression that is the characters that you want to use for replacement in the order of from_string. When you include fewer characters in this argument than are in from_string, the function removes the extra characters in from_string from the return value. Value can be one of the following types: text.
Returned value type will be text.
Function | Result |
---|---|
|
Cooper Mark |