Using Mathematical Operations in Queries

Learn about the Mathematical functions supported in Oracle NoSQL Database Cloud Service.

The mathematical functions are used to perform mathematical calculations on the input arguments. Here, the supplied arguments are expressions that resolve to numbers.

You can invoke the mathematical functions from the SELECT/WHERE clauses, and also from other Oracle NoSQL statements where function calls are allowed in the syntax. For example, you can supply mathematical functions as arguments to Using Aggregate Functions.

The following functions are supported in the Oracle NoSQL Database Cloud Service:

Table 1 - Mathematical functions

Function	Description
abs(n)	Returns the absolute value of n.
acos(n)	Returns the arc cosine of n expressed in radians.
asin(n)	Returns the arc sine of n expressed in radians.
atan(n)	Returns the arc tangent of n expressed in radians.
atan2(n1,n2)	Returns the arc tangent of arguments n1 and n2 expressed in radians.
ceil(n)	Returns the smallest integer that is greater than or equal to n.
cos(n)	Returns the cosine of an angle n specified in radians.
cot(n)	Returns the cotangent of an angle n specified in radians.
degrees(n)	Converts n from radians to degrees.
e()	Returns the value of the Euler number e.
exp(n)	Returns the exponential value of the expression n.
floor(n)	Returns the largest integer that is less than or equal to n.
ln(n)	Returns the natural logarithmic (base e) value of n.
log(n,b)	Returns the logarithmic base b value of n.
log10(n)	Returns the logarithmic base 10 value of n.
pi()	Returns the value of pi.
power(n2,n1)	Returns the value of n2 raised to the power n1.
radians(n)	Converts n from degrees to radians.
rand()	Returns a pseudo-random number between zero and one.
round(n [,d])	Rounds n to d places to the right of the decimal point.
sign(n)	Returns the sign of n.
sin(n)	Returns the sine of an angle n specified in radians.
sqrt(n)	Returns the square root of n.
tan(n)	Returns the tangent of an angle n specified in radians.
trunc(n [,d])	Returns the value of n truncated to d decimal places.

Note:

The mathematical function names are case-sensitive.
All the mathematical functions expect numeric types (integer, long, float, double, and number) as arguments. These functions return NULL in the following cases. Here, the NULL refers to SQL NULL unless specified otherwise.
- Any of the arguments resolves to NULL.
- Any of the arguments is a field in a JSON column whose value is NULL, that is, JSON NULL.
- Any of the arguments is a field in a JSON column, which does not exist.
- Any of the arguments is a field in JSON collection, which does not exist.
- Any of the arguments does not resolve to a numeric type.
While using trigonometric functions such as sin(n), cos(n), and so forth, you must specify the input value in radians. You can convert an angle from degrees to radians using the radians(n) function. Similarly, for inverse trigonometric functions like asin(n), acos(n), and so forth, you can convert the resultant radians value to degrees using the degrees(n) function.

To follow along with the examples in the sections, you can create the tables and load data as described in the Additional Examples section.

abs function

The abs function returns the absolute value of the input expression.

Example 1 - abs function

SELECT abs(n) FROM Userstocks

Value of n	Output
10	10
-10	10
1.67	1.67
-1.67	1.67

arc cosine function

The acos function is an inverse trigonometric function. The acos function returns the arc cosine (inverse of cosine) of the input expression.

Example 1 - arc cosine function

SELECT acos(n) FROM Archery

Value of n	Output
1	0.0
0	1.5707963267948966
sqrt(3)/2	0.5235987755982989
0.5	1.0471975511965979
-1	3.141592653589793

arc sine function

The asin function is an inverse trigonometric function. The asin function returns the arc sine (inverse of sine) of the input expression.

Example 1 - arc sine function

SELECT asin(n) FROM Archery

Value of n	Output
1	1.5707963267948966
sqrt(3)/2	1.0471975511965976
0.5	0.5235987755982989
-1	-1.5707963267948966

arc tan function

The atan function is an inverse trigonometric function. The atan function returns the arc tangent (inverse of tangent) of the input expression.

Example 1 - arc tan function

SELECT atan(n) FROM Archery

Value of n	Output
1	0.7853981633974483
sqrt(3)	1.0471975511965976

arc tan2 function

The atan2 function is an inverse trigonometric function. The atan2 function is a two-argument arc tangent (inverse of tan) function, which returns the arc tangent of a coordinate or point in two-dimensional space (n1, n2) calculated as atan(n2/n1).

Example 1 - arc tan2 function

SELECT atan2(n1,n2) FROM Archery

Value of n1	Value of n2	Output
1	1	0.7853981633974483
1	sqrt(3)	1.0471975511965976
2	0	1.5707963267948966

ceil function

The ceil function returns the smallest integer that is greater than or equal to the specified expression.

Example 1 - ceil function

SELECT ceil(n) FROM Userstocks

Value of n	Output
1.34	2.0
-1.34	-1.0
pi()	4.0

cosine function

The cos function is a trigonometric function. The cos function returns the cosine of an angle specified in radians.

Example 1 - cosine function

SELECT cos(n) FROM Archery

Value of n	Output
0	1.0
radians(90)	6.123233995736766E-17
pi()	-1.0

cotangent function

The cot function is a trigonometric function. The cot function returns the cotangent of an angle specified in radians.

Example 1 - cotangent function

SELECT cot(n) FROM Archery

Value of n	Output
0	Infinity
radians(90)	6.123233995736766E-17
pi()/4	1.0000000000000002

degrees function

The degrees function converts the specified expression from radians to degrees.

Example 1 - degrees function

SELECT degrees(n) FROM Archery

Value of n	Output
pi()/2	90
0	0.0

Euler function

The e function returns the value of Euler number e, that is, 2.718281828459045.

Example 1 - Euler function

SELECT e() AS EULER FROM Archery

Output:

+-----------+-------------------+
| EULER     | 2.718281828459045 |
+-----------+-------------------+

exp function

The exp function returns the exponential value of the given expression, that is, e raised to the power of the specified expression. Here, e is the base of the natural logarithm and has the value 2.718281828459045.

Example 1 - exp function

SELECT exp(n) FROM PHtable

Value of n	Output
0	1.0
2	7.38905609893065
-2	0.1353352832366127

floor function

The floor function returns the largest integer that is less than or equal to the specified expression.

Example 1 - floor function

SELECT floor(n) FROM Userstocks

Value of n	Output
1.34	1.0
-1.34	-2.0
pi()	3.0

ln function

The ln function returns the natural logarithmic value (base e) of the specified expression.

Example 1 - ln function

SELECT ln(n) FROM PHtable

Value of n	Output
1	0.0
10	2.302585092994046
1.0E-7	-16.11809565095832

log function

The log function returns the logarithmic value with the specified base for the given expression.

Example 1 - log function

SELECT log(n,b) FROM PHtable

Value of n	Value of b	Output
2	2	1.0
81	3	4.0
255	5	3.442980622208573

log10 function

The log10 function returns the logarithmic value with base 10 for the specified expression.

Example 1 - log10 function

SELECT log10(n) FROM PHtable

Value of n	Output
1	0.0
10	1.0
1.0E-7	-7.0

pi function

The pi function returns the value of pi, that is, 3.141592653589793.

Example 1 - pi function

SELECT pi() AS PI FROM Archery

Output:

+-----------+-------------------+
| PI        | 3.141592653589793 |
+-----------+-------------------+

power function

The pow function returns the value of the first expression raised to the power of the second expression.

Example 1 - power function

SELECT pow(n2,n1) FROM PHtable

Value of n	Value of b	Output
2	4	16.0
2	0	1.0
-0.5	4	0.0625
0.5	-4	16.0

radians function

The radians function converts the specified expression from degrees to radians.

Example 1 - radians function

SELECT radians(n) FROM Archery

Value of n	Output
180	3.141592653589793
0	0.0

random function

The rand function returns a positive pseudo-random number. The resultant value can be greater than or equal to zero and less than one.

Example 1 - random function

SELECT rand() AS RANDOM from Archery WHERE sim=1

Output:

+-----------+-------------------+
| RANDOM    | 0.891655403699787 |
+-----------+-------------------+

round function

The round function rounds the value of an input expression to the specified decimal places to the right of the decimal point.

Example 1 - round function

SELECT round(n,d) FROM Userstocks

Value of n	Value of d	Output
100.331	2	100.33
100.367	2	100.37
111.567	0.5	112.0
111.567	4	111.567
10.361	0 or not specified	10.0
111.331	-2	100.0
111.331	-4	0.0
-100.331	2	-100.33
-111.331	-2	-100.0

sign function

The sign function returns the sign of the specified expression, that is, zero if the argument is zero, 1 if the argument is greater than zero, and -1 if the argument is less than zero.

Example 1 - sign function

SELECT sign(n) FROM PHtable

Value of n	Output
1.89	1.0
-1.89	-1.0
0	0.0
sin(3*pi()/2)	-1.0

sine function

The sin function is a trigonometric function. The sin function returns the sine of an angle specified in radians.

Example 1 - sine function

SELECT sin(n) FROM Archery

Value of n	Output
radians(90)	1.0
pi()/4	0.7071067811865475
3*pi()/2	-1.0

square root function

The sqrt function returns the square root of the specified expression.

Example 1 - square root function

SELECT sqrt(n) FROM Archery

Value of n	Output
16	4.0
12.57/pi()	2.00028879648171

tangent function

The tan function is a trigonometric function. The tan function returns the tangent of an angle specified in radians.

Example 1 - tangent function

SELECT tan(n) FROM Archery

Value of n	Output
0	0.0
radians(90)	1.633123935319537E16
pi()/4	0.9999999999999999

truncate function

The trunc function truncates the value of an input expression to the specified decimal places on the right of the decimal point.

Example 1 - truncate function

SELECT trunc(n,d) FROM Userstocks

Value of n	Value of d	Output
111.567	0 or not specified	100.00
111.567	2	100.56
111.567	3	111.567
111.567	-2	100.0
111.567	-3	0.0
-111.567	2	-100.567
-111.567	-1	-110.0

Examples using QueryRequest API

You can use QueryRequest API and apply SQL functions to fetch data from a NoSQL table similar to Timestamp functions and String functions.

The following code snippets show how to use math functions to query Userstocks table.

To execute your query, you use the NoSQLHandle.query() API.

String queryStatement =
    "SELECT stock, " +
    "ceil(units * sellRate - units * buyRate) AS PROFIT, " +
    "abs(units * sellRate - units * buyRate) AS TURNOVER, " +
    "round(abs(units * sellRate - units * buyRate) * 0.5 / 100, 2) " +
    "AS BROKERAGE " +
    "FROM Userstocks";

try (QueryRequest request =
         new QueryRequest().setStatement(queryStatement);
     QueryIterableResult results = handle.queryIterable(request)) {

    for (MapValue row : results) {
        System.out.println(row);
    }
}

To execute your query use the borneo.NoSQLHandle.query() method.

query_statement = '''
    SELECT stock,
           ceil(units * sellRate - units * buyRate) AS PROFIT,
           abs(units * sellRate - units * buyRate) AS TURNOVER,
           round(
               abs(units * sellRate - units * buyRate) * 0.5 / 100,
               2
           ) AS BROKERAGE
    FROM Userstocks
'''

request = QueryRequest().set_statement(query_statement)

while True:
    result = handle.query(request)

    for row in result.get_results():
        print(row)

    if request.is_done():
        break

To execute a query use the Client.Query function.

queryStatement := `
    SELECT stock,
           ceil(units * sellRate - units * buyRate) AS PROFIT,
           abs(units * sellRate - units * buyRate) AS TURNOVER,
           round(
               abs(units * sellRate - units * buyRate) * 0.5 / 100,
               2
           ) AS BROKERAGE
    FROM Userstocks`

prepareRequest := &nosqldb.PrepareRequest{
    Statement: queryStatement,
}

prepareResult, err := client.Prepare(prepareRequest)
if err != nil {
    fmt.Println("Prepare failed:", err)
    return
}

queryRequest := &nosqldb.QueryRequest{
    PreparedStatement: &prepareResult.PreparedStatement,
}

for {
    queryResult, err := client.Query(queryRequest)
    if err != nil {
        fmt.Println("Query failed:", err)
        return
    }

    rows, err := queryResult.GetResults()
    if err != nil {
        fmt.Println("Unable to get results:", err)
        return
    }

    for _, row := range rows {
        fmt.Println(jsonutil.AsJSON(row.Map()))
    }

    if queryRequest.IsDone() {
        break
    }
}

To execute a query use query method.

const queryStatement = `
    SELECT stock,
           ceil(units * sellRate - units * buyRate) AS PROFIT,
           abs(units * sellRate - units * buyRate) AS TURNOVER,
           round(
               abs(units * sellRate - units * buyRate) * 0.5 / 100,
               2
           ) AS BROKERAGE
    FROM Userstocks`;

const options = {};

do {
    const result = await handle.query(queryStatement, options);

    for (const row of result.rows) {
        console.log(row);
    }

    options.continuationKey = result.continuationKey;
} while (options.continuationKey);

To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

private const string QueryStatement = @"
    SELECT stock,
           ceil(units * sellRate - units * buyRate) AS PROFIT,
           abs(units * sellRate - units * buyRate) AS TURNOVER,
           round(
               abs(units * sellRate - units * buyRate) * 0.5 / 100,
               2
           ) AS BROKERAGE
    FROM Userstocks";

var queryEnumerable =
    client.GetQueryAsyncEnumerable(QueryStatement);

await foreach (var result in queryEnumerable)
{
    foreach (var row in result.Rows)
    {
        Console.WriteLine(row.ToJsonString());
    }
}

Additional Examples

Learn to use mathematical functions in applications.

Example 1 - Apply mathematical functions to retrieve intra-day transactions from a trading application

Consider a Userstocks table containing intra-day transactions of a Stock trader in a trading application.

The table DDL is as follows:

CREATE TABLE Userstocks (id INTEGER,
                     stock STRING,
                     units INTEGER,
                     buyRate DOUBLE,
                     sellRate DOUBLE,
PRIMARY KEY (id))

The id field contains the ID of the company, stock field contains the company name in which the user has stock options, the units field contains the number of stocks owned by the user, buyRate is the price at which the user has purchased the stocks, and sellRate is the price at which the user sells his stocks.

Insert sample rows into the table:

INSERT into Userstocks VALUES (1, "company1", 100, 10.2, 11.5)
INSERT into Userstocks VALUES (2, "company2", 20, 15, 14.7)

You can use the following query to apply mathematical functions and retrieve the required transaction details from the table:

SELECT stock,
ceil(units*sellRate-units*buyRate) AS PROFIT,
abs(units*sellRate-units*buyRate) AS TURNOVER,
round(abs(units*sellRate-units*buyRate)*0.5/100, 2) AS BROKERAGE
FROM Userstocks

Explanation: In the above query, you fetch the day’s turnover, profit/loss, and brokerage charges. You calculate the profit/loss using the given arithmetic expressions and apply the ceil function to get the result as the nearest integer value. You calculate the profit/loss and apply the abs function to calculate the turnover for each transaction. You calculate the brokerage charges by applying 0.5% on each turnover and round the result to two decimal places using the round function.

Output:

{"stock":"company2","PROFIT":-6.0,"TURNOVER":6.0,"BROKERAGE":0.03}
{"stock":"company1","PROFIT":131.0,"TURNOVER":130.0000000000001,"BROKERAGE":0.65}

Example 2 - Apply mathematical functions to calculate the shooting distance in a gaming application

Consider an Archery table containing data for target shooting in a gaming application.

The table DDL is as follows:

CREATE TABLE Archery (sim INTEGER,
                     angle DOUBLE,
                     elevation DOUBLE,
PRIMARY KEY (sim) )

The sim field identifies the simulation count, the angle field includes different angles (in degrees) at which a gamer can aim at a target, elevation field contains the height (in meters) at which the target is placed.

Insert sample rows into the table:

INSERT INTO Archery VALUES (1, 30, 50)
INSERT INTO Archery VALUES (2, 45, 50)
INSERT INTO Archery VALUES (3, 70, 95)

You can apply mathematical functions in the following query to calculate the shooting distance for various angles and elevations of the target.

SELECT sim, trunc(elevation/sin(radians(angle)),2) AS SLOPE FROM Archery ORDER BY
    sim

Explanation: To hit a target placed at a certain elevation, the gamer aims at an angle and shoots a certain distance. These elements form a side of a right-angle triangle, an acute angle, and a hypotenuse. In this query, you calculate the shooting distance for various combinations of target elevation and angles based on the gamer’s position from the target. You use the radians function to convert the angles to radians and calculate the sine value using the sine function. You divide the elevation by the sine value to calculate the distance and truncate the result to two decimal places using the truncate function.

Output:

{"sim":1,"SLOPE ":100.0}
{"sim":2,"SLOPE ":70.71}
{"sim":3,"SLOPE ":101.09}

Example 3 - Use the mathematical functions to calculate the PH value for a given solution

Consider a PHtable containing Hydrogen ions concentration for different solutions.

The table DDL is as follows:

CREATE TABLE PHtable (id INTEGER,
                    sampleName STRING,
                    hIons DOUBLE,
                    phValue DOUBLE,
PRIMARY KEY (id) )

The id field contains the identifier of the solution sample, the sampleName field contains the name of the given sample, the hIons field is the Hydrogen ion concentration in moles per liter of the liquid, and the phValue field is the PH value of the solution. You can use the hIons value to calculate the phValue of a solution. You initialize the phValue column to NULL while inserting data into the table.

Insert sample rows into the table:

INSERT INTO PHtable VALUES (1, "sample1", 0.0063095734448019, NULL)
INSERT INTO PHtable VALUES (2, "sample2", 5.0118723362727E-9, NULL)
INSERT INTO PHtable VALUES (3, "sample3", 1.0E-7, NULL)

You can use the mathematical functions to calculate the PH value for a given solution and update the table using the following query:

UPDATE PHtable SET phValue=trunc((log10(1/hIons)),1) where id=1 RETURNING
    *

Explanation: The PH value indicates the strength of acids or bases in a solution. In this query, you use log10 function to calculate the PH value of a solution using the formula PH=log10(1/hIons). As the return value of the function is double, you use the truncate function to truncate the result to one decimal place. You use the UPDATE Statement to set the resultant PH value in the phValue field for the specified solution.

Output:

{"id":1,"sampleName":"sample1","hIons":0.0063095734448019,"phValue":2.2}

You can also use the mathematical functions in the WHERE clause. In this example, you find the acidic samples (PH < 7) using the following query:

SELECT sampleName AS ACIDIC FROM PHtable WHERE trunc((log10(1/hIons)),1) <
    7.0

Output:

{"ACIDIC":"sample1"}