Additional Examples

Learn to use mathematical functions in applications.

Example 12-55 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 12-56 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 12-57 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"}