在查询中使用数学运算

了解 Oracle NoSQL Database Cloud Service 中支持的数学函数。

数学函数用于对输入参数执行数学计算。这里，提供的参数是解析为数字的表达式。

可以从 SELECT/WHERE 子句以及允许在语法中执行函数调用的其他 Oracle NoSQL 语句中调用数学函数。例如，您可以将数学函数作为参数提供给使用聚合函数。

Oracle NoSQL Database Cloud Service 支持以下功能：

表 1 - 数学函数

功能	说明
abs(n)	返回 n 的绝对值。
acos(n)	返回 n 的余弦（以弧度表示）。
asin(n)	返回 n 的反负弦（用弧度表示）。
atan(n)	返回 n 的反反正切（以弧度表示）。
atan2(n1,n2)	返回参数 n1 和 n2 的正切弧，以弧度表示。
天花板 (n)	返回大于或等于 n 的最小整数。
cos(n)	返回以弧度指定的角度 n 的余弦。
婴儿床 (n)	返回以弧度指定的角度 n 的正切。
学位 (n)	将 n 从弧度转换为度。
e()	返回欧拉编号 e 的值。
exp(n)	返回表达式 n 的指数值。
地板 (n)	返回小于或等于 n 的最大整数值。
ln(n)	返回 n 的自然对数（基数 e ）。
日志 (n，b)	返回 n 的对数基 b 值。
log10(n)	返回 n 的对数基数 10 值。
pi()	返回 pi 的值。
电源 (n2，n1)	返回向电源 n1 引发的 n2 值。
弧度 (n)	将 n 从度转换为弧度。
rand()	返回一个介于零和一之间的伪随机数。
舍入 (n [，d])	将 n 舍入到小数点右侧的 d 。
符号 (n)	返回 n 的符号。
sin(n)	返回以弧度指定的角度 n 的正弦。
平方英尺 (n)	返回 n 的平方根。
tan(n)	返回以弧度指定的角度 n 的正切。
trunc(n [,d])	返回截断为 d 位小数位的 n 值。

注:

数学函数名称区分大小写。
所有数学函数都期望数字类型（整数、长整型、浮点型、双精度型和数字型）作为参数。在以下情况下，这些函数返回 NULL。在此处，除非另有说明，否则 NULL 指的是 SQL NULL。
- 任何参数均解析为 NULL。
- 任何参数都是 JSON 列中的一个字段，其值为 NULL，即 JSON NULL。
- 任何参数都是 JSON 列中的字段，该字段不存在。
- 任何参数都是 JSON 集合中的字段，该字段不存在。
- 任何参数都不会解析为数字类型。
使用三角函数（如 sin(n)、cos(n) 等）时，必须以弧度指定输入值。可以使用 radians(n) 函数将角度从度转换为弧度。同样，对于反三角函数（如 asin(n)、acos(n) 等），可以使用 degrees(n) 函数将生成的弧度值转换为度。

要与各节中的示例一起使用，可以创建表并加载数据，如其他示例部分中所述。

abs 函数

abs 函数返回输入表达式的绝对值。

示例 1 - abs 函数

SELECT abs(n) FROM Userstocks

n 的值	输出
10	10
-10	10
1.67	1.67
-1.67	1.67

余弦函数

acos 函数是反三角函数。acos 函数返回输入表达式的弧余弦（余弦的逆）。

示例 1 - 余弦函数

SELECT acos(n) FROM Archery

n 的值	输出
1	0.0
0	1.5707963267948966
sqrt(3)/2	0.5235987755982989
0.5	1.0471975511965979
-1	3.141592653589793

arc 正弦函数

asin 函数是反三角函数。asin 函数返回输入表达式的正弦（正弦反向）。

示例 1 - 弧正弦函数

SELECT asin(n) FROM Archery

n 的值	输出
1	1.5707963267948966
sqrt(3)/2	1.0471975511965976
0.5	0.5235987755982989
-1	-1.5707963267948966

arc tan 函数

atan 函数是反三角函数。atan 函数返回输入表达式的弧正切（正切的反面）。

示例 1 - 弧 tan 函数

SELECT atan(n) FROM Archery

n 的值	输出
1	0.7853981633974483
sqrt(3)	1.0471975511965976

arc tan2 函数

atan2 函数是反三角函数。atan2 函数是一个双参数的弧正切（tan 的反向）函数，它返回坐标或点的弧正切（在计算方法为 atan(n2/n1) 的二维空间（n1、n2）中）。

示例 1 - 弧 tan2 函数

SELECT atan2(n1,n2) FROM Archery

n1 的值	n2 的值	输出
1	1	0.7853981633974483
1	sqrt(3)	1.0471975511965976
2	0	1.5707963267948966

ceil 函数

ceil 函数返回大于或等于指定表达式的最小整数。

示例 1 - ceil 函数

SELECT ceil(n) FROM Userstocks

n 的值	输出
1.34	2.0
-1.34	-1.0
pi()	4.0

余弦函数

cos 函数是三角函数。cos 函数返回以弧度指定的角度的余弦。

示例 1 - 余弦函数

SELECT cos(n) FROM Archery

n 的值	输出
0	1.0
弧度 (90)	6.123233995736766E-17
pi()	-1.0

cotangent 函数

cot 函数是三角函数。cot 函数返回以弧度指定的角度的正切值。

示例 1 - cotangent 函数

SELECT cot(n) FROM Archery

n 的值	输出
0	无穷大
弧度 (90)	6.123233995736766E-17
pi()/4	1.0000000000000002

度函数

degrees 函数将指定的表达式从弧度转换为度。

示例 1 - 度函数

SELECT degrees(n) FROM Archery

n 的值	输出
pi()/2	90
0	0.0

欧拉函数

e 函数返回欧拉编号 e 的值，即 2.718281828459045。

示例 1 - Euler 函数

SELECT e() AS EULER FROM Archery

输出：

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

exp 函数

exp 函数返回给定表达式的指数值，即向指定表达式的幂引发的 e。这里，e 是自然对数的基数，其值为 2.718281828459045。

示例 1 - exp 函数

SELECT exp(n) FROM PHtable

n 的值	输出
0	1.0
2	7.38905609893065
-2	0.1353352832366127

地板功能

floor 函数返回小于或等于指定表达式的最大整数。

示例 1 - 基底函数

SELECT floor(n) FROM Userstocks

n 的值	输出
1.34	1.0
-1.34	-2.0
pi()	3.0

ln 函数

ln 函数返回指定表达式的自然对数值（以 e 为基础）。

示例 1 - ln 函数

SELECT ln(n) FROM PHtable

n 的值	输出
1	0.0
10	2.302585092994046
1.0E-7	-16.11809565095832

log 函数

log 函数返回给定表达式的具有指定基数的对数值。

示例 1 - 日志函数

SELECT log(n,b) FROM PHtable

n 的值	b 的值	输出
2	2	1.0
81	3	4.0
255	5	3.442980622208573

log10 函数

log10 函数为指定的表达式返回基数为 10 的对数值。

示例 1 - log10 函数

SELECT log10(n) FROM PHtable

n 的值	输出
1	0.0
10	1.0
1.0E-7	-7.0

pi 函数

pi 函数返回 pi 的值，即 3.141592653589793。

示例 1 - pi 函数

SELECT pi() AS PI FROM Archery

输出：

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

功率函数

pow 函数返回将第一个表达式的值提升到第二个表达式的幂。

示例 1- 电源功能

SELECT pow(n2,n1) FROM PHtable

n 的值	b 的值	输出
2	4	16.0
2	0	1.0
-0.5	4	0.0625
0.5	-4	16.0

弧度函数

radians 函数将指定的表达式从度转换为弧度。

示例 1 - 弧度函数

SELECT radians(n) FROM Archery

n 的值	输出
180	3.141592653589793
0	0.0

随机函数

rand 函数返回一个正伪随机数。结果值可以大于或等于零，并且小于一。

示例 1 - 随机函数

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

输出：

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

舍入函数

round 函数将输入表达式的值舍入到小数点右侧的指定小数位。

示例 1 - 舍入函数

SELECT round(n,d) FROM Userstocks

n 的值	d 的值	输出
100.331	2	100.33
100.367	2	100.37
111.567	0.5	112.0
111.567	4	111.567
10.361	0 或未指定	10.0
111.331	-2	100.0
111.331	-4	0.0
-100.331	2	-100.33
-111.331	-2	-100.0

标志函数

sign 函数返回指定表达式的符号，即，如果参数为零则为零；如果参数大于零，则为 1；如果参数小于零，则为 -1。

示例 1 - 符号函数

SELECT sign(n) FROM PHtable

n 的值	输出
1.89	1.0
-1.89	-1.0
0	0.0
sin (3* pi()/2)	-1.0

正弦函数

sin 函数是三角函数。sin 函数返回以弧度指定的角度的正弦。

示例 1 - 正弦函数

SELECT sin(n) FROM Archery

n 的值	输出
弧度 (90)	1.0
pi()/4	0.7071067811865475
3*pi()/2	-1.0

平方根函数

sqrt 函数返回指定表达式的平方根。

示例 1 - 方根函数

SELECT sqrt(n) FROM Archery

n 的值	输出
16	4.0
12.57/pi()	2.00028879648171

切线函数

tan 函数是三角函数。tan 函数返回以弧度指定的角度的正切。

示例 1 - 正切函数

SELECT tan(n) FROM Archery

n 的值	输出
0	0.0
弧度 (90)	1.633123935319537E16
pi()/4	0.9999999999999999

truncate 函数

trunc 函数将输入表达式的值截断到小数点右侧的指定小数位。

示例 1 - 截断函数

SELECT trunc(n,d) FROM Userstocks

n 的值	d 的值	输出
111.567	0 或未指定	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

使用 QueryRequest API 的示例

您可以使用 QueryRequest API 并应用 SQL 函数从 NoSQL 表中提取数据，这与时间戳函数和字符串函数类似。

以下代码段显示了如何使用数学函数查询 Userstocks 表。

要执行查询，请使用 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);
    }
}

要执行查询，请使用 borneo.NoSQLHandle.query() 方法。

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

要执行查询，请使用 Client.Query 函数。

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
    }
}

要执行查询，请使用 query 方法。

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);

要执行查询，可以调用 QueryAsync 方法或调用 GetQueryAsyncEnumerable 方法并迭代生成的异步枚举。

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());
    }
}

其他示例

学习在应用程序中使用数学函数。

示例 1 - 应用数学函数从交易应用程序中检索当日交易

考虑一个 Userstocks 表，其中包含交易应用程序中股票交易者的当日交易。

表 DDL 如下所示：

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

id 字段包含公司的 ID，stock 字段包含用户有股票期权的公司名称，units 字段包含用户拥有的股票数量，buyRate 是用户购买股票的价格，sellRate 是用户出售股票的价格。

将示例行插入表中：

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

可以使用以下查询应用数学函数，并从表中检索所需的事务处理详细信息：

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

解释：在上面的查询中，您将提取当天的营业额、利润/损失和经纪费用。可以使用给定的算术表达式计算损益，并应用 ceil 函数以将结果获取为最接近的整数值。您可以计算损益并应用 abs 函数来计算每个事务处理的营业额。您可以通过对每次营业额应用 0.5% 来计算经纪人佣金费用，并使用舍入函数将结果四舍五入到两位小数。

输出：

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

示例 2 - 应用数学函数计算游戏应用程序中的拍摄距离

考虑一个 Archery 表，其中包含用于游戏应用程序中目标拍摄的数据。

表 DDL 如下所示：

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

sim 字段标识模拟计数，angle 字段包括玩家可以瞄准目标的不同角度（以度为单位），elevation 字段包含放置目标的高度（以米为单位）。

将示例行插入表中：

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

您可以在以下查询中应用数学函数来计算目标的各种角度和海拔的拍摄距离。

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

解释：要击中放置在特定海拔处的目标，玩家的目标是角度并射击一定的距离。这些元素形成一个直角三角形的一边，一个尖锐的角度，和一个低功耗。在此查询中，您将根据玩家从目标位置计算目标高程和角度的各种组合的拍摄距离。可以使用 radians 函数将角度转换为弧度，并使用 sine 函数计算正弦值。可以使用截断函数将高程除以正弦值，以计算距离并将结果截断到两位小数。

输出：

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

示例 3 - 使用数学函数计算给定解答的 PH 值

考虑含有不同溶液的氢离子浓度的 PHtable。

表 DDL 如下所示：

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

id 字段包含解决方案示例的标识符，sampleName 字段包含给定示例的名称，hIons 字段是液体的每升摩尔中的氢离子浓度，phValue 字段是解决方案的 PH 值。可以使用 hIons 值计算解决方案的 phValue。在向表中插入数据时，可以将 phValue 列初始化为 NULL。

将示例行插入表中：

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)

可以使用数学函数计算给定解答的 PH 值并使用以下查询更新表：

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

解释：PH 值指示溶液中酸或碱的强度。在此查询中，可以使用 log10 函数使用公式 PH=log10(1/hIons) 计算解决方案的 PH 值。由于函数的返回值为 double，因此可以使用 truncate function 将结果截断到一个小数位。可以使用 UPDATE 语句在指定解答的 phValue 字段中设置生成的 PH 值。

输出：

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

也可以在 WHERE 子句中使用数学函数。在此示例中，您使用以下查询找到酸性样本 (PH < 7)：

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

输出：

{"ACIDIC":"sample1"}