数学函数

openGemini大约 10 分钟约 2952 字

本章主要介绍如下函数：

数学函数	说明
ABS()	绝对值
ACOS()	反余弦值
ASIN()	反正弦值
COS()	余弦值
SIN()	正弦值
TAN()	正切值
ATAN()	反正切值
ATAN2()	y/x的反正切值
CEIL()	向上取整
EXP()	指数
FLOOR()	向下取整
LN()	自然对数
LOG()	指定底数的对数
LOG2()	底数2的对数
LOG10()	底数10的对数
POW()	幂值
ROUND()	四舍五入
SQRT()	平方根

提示

openGemini提供的函数兼容InfluxDB的用法，可参考InfluxDB对应的函数用法open in new window。

ABS()

返回指定Field列的绝对值，不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT ABS( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算degrees连续差值的绝对值

> SELECT * FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+---------+--------------+
| time                | degrees | location     |
+---------------------+---------+--------------+
| 1566000360000000000 | 60      | santa_monica |
| 1566000720000000000 | 62      | santa_monica |
| 1566001080000000000 | 62      | santa_monica |
| 1566001440000000000 | 60      | santa_monica |
| 1566001800000000000 | 63      | santa_monica |
| 1566002160000000000 | 64      | santa_monica |
| 1566002520000000000 | 63      | santa_monica |
| 1566002880000000000 | 63      | santa_monica |
| 1566003240000000000 | 61      | santa_monica |
+---------------------+---------+--------------+
3 columns, 9 rows in set

> SELECT DIFFERENCE(degrees) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z'
name: h2o_temperature
+---------------------+------------+
| time                | difference |
+---------------------+------------+
| 1566000720000000000 | 2          |
| 1566001080000000000 | 0          |
| 1566001440000000000 | -2         |
| 1566001800000000000 | 3          |
| 1566002160000000000 | 1          |
| 1566002520000000000 | -1         |
| 1566002880000000000 | 0          |
| 1566003240000000000 | -2         |
+---------------------+------------+
2 columns, 8 rows in set

> SELECT ABS(DIFFERENCE(degrees)) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z'
name: h2o_temperature
+---------------------+-----+
| time                | abs |
+---------------------+-----+
| 1566000720000000000 | 2   |
| 1566001080000000000 | 0   |
| 1566001440000000000 | 2   |
| 1566001800000000000 | 3   |
| 1566002160000000000 | 1   |
| 1566002520000000000 | 1   |
| 1566002880000000000 | 0   |
| 1566003240000000000 | 2   |
+---------------------+-----+
2 columns, 8 rows in set

原始数据通过DIFFERENCE(degrees)计算后的第3、第6和第8个值分别是-2、-1、-2，均为负数，经过ABS()函数计算后变为2、1、2。相关阅读 DIFFERENCE

ACOS()

返回指定Filed列值的反余弦值，返回值采用弧度形式且介于 0 和 PI 之间，Field列值的范围必须是[-1,1]，超出范围的值返回<nil>。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT ACOS( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算反余弦值

> SELECT ACOS("a") FROM math
name: math
+---------------------+--------------------+
| time                | acos               |
+---------------------+--------------------+
| 1704616695559558000 | 0.2003348423231197 |
| 1704616698618449000 | 0.3481660212729609 |
| 1704616705557497000 | 0.5155940062460904 |
| 1704616711437450000 | 0.6919551751263169 |
| 1704616714600279000 | 0                  |
| 1704616725041028000 | 0.8762980611683406 |
| 1704616732497883000 | 1.0471975511965976 |
| 1704616739281242000 | 1.2238794292677349 |
| 1704616741456246000 | 1.399966657665792  |
| 1704616744909623000 | 1.5707963267948966 |
+---------------------+--------------------+
2 columns, 10 rows in set

ACOS还可以与其他函数互相嵌套使用，比如FIRST, LAST, MEAN, MIN, MAX , DIFFERENCE 等。

> SELECT ACOS(FIRST("a")) FROM math GROUP BY time(10s) LIMIT 5
name: math
+---------------------+--------------------+
| time                | acos               |
+---------------------+--------------------+
| 1704616690000000000 | 0.2003348423231197 |
| 1704616700000000000 | 0.5155940062460904 |
| 1704616710000000000 | 0.6919551751263169 |
| 1704616720000000000 | 0.8762980611683406 |
| 1704616730000000000 | 1.0471975511965976 |
+---------------------+--------------------+
2 columns, 5 rows in set

ASIN()

返回指定Filed列值的反正弦值，返回值采用弧度形式且介于 -Pi/2 到 Pi/2 之间，Field列值的范围必须是[-1,1]，超出范围的值返回<nil>。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT ASIN( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算反正弦值

> SELECT ASIN("a") FROM math
name: math
+---------------------+---------------------+
| time                | asin                |
+---------------------+---------------------+
| 1704616695559558000 | 1.3704614844717768  |
| 1704616698618449000 | 1.2226303055219356  |
| 1704616705557497000 | 1.0552023205488061  |
| 1704616711437450000 | 0.8788411516685797  |
| 1704616714600279000 | 1.5707963267948966  |
| 1704616725041028000 | 0.6944982656265559  |
| 1704616732497883000 | 0.5235987755982989  |
| 1704616739281242000 | 0.34691689752716176 |
| 1704616741456246000 | 0.1708296691291045  |
| 1704616744909623000 | 0                   |
+---------------------+---------------------+
2 columns, 10 rows in set

ASIN还可以与其他函数互相嵌套使用，比如FIRST, LAST, MEAN, DIFFERENCE 等

> SELECT ASIN(FIRST("a")) FROM math GROUP BY time(10s) LIMIT 5
name: math
+---------------------+--------------------+
| time                | asin               |
+---------------------+--------------------+
| 1704616690000000000 | 1.3704614844717768 |
| 1704616700000000000 | 1.0552023205488061 |
| 1704616710000000000 | 0.8788411516685797 |
| 1704616720000000000 | 0.6944982656265559 |
| 1704616730000000000 | 0.5235987755982989 |
+---------------------+--------------------+
2 columns, 5 rows in set

COS()

返回指定Field列值的余弦值，Field列值采用弧度值表示，返回值范围是[-1,1]。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT COS( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算余弦值

> select degrees from math WHERE time > now()-2h
name: math
+---------------------+-------------+
| time                | degrees     |
+---------------------+-------------+
| 1704616115424705000 | 0           |
| 1704616122906818000 | 0.174532925 |
| 1704616185692931000 | 0.34906585  |
| 1704616206396238000 | 0.523598776 |
| 1704616222584839000 | 0.34906585  |
| 1704616238382964000 | 0.698131701 |
| 1704616260741136000 | 1.570796327 |
+---------------------+-------------+
2 columns, 7 rows in set

> SELECT COS(degrees) FROM math WHERE time > now()-2h
name: math
+---------------------+-------------------------+
| time                | cos                     |
+---------------------+-------------------------+
| 1704616115424705000 | 1                       |
| 1704616122906818000 | 0.9848077530468392      |
| 1704616185692931000 | 0.9396926209223285      |
| 1704616206396238000 | 0.866025403583588       |
| 1704616222584839000 | 0.9396926209223285      |
| 1704616238382964000 | 0.7660444429889625      |
| 1704616260741136000 | -2.0510342851533115e-10 |
+---------------------+-------------------------+
2 columns, 7 rows in set

COS可以其他函数互相嵌套使用，比如FIRST, LAST, MEAN, MIN, MAX , DIFFERENCE 等。

SIN()

返回指定Field列值的正弦值，Field列值采用弧度值表示，返回值范围是[-1,1]。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT SIN( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算正弦值

> SELECT SIN(degrees) FROM math WHERE time > now()-2h
name: math
+---------------------+---------------------+
| time                | sin                 |
+---------------------+---------------------+
| 1704616115424705000 | 0                   |
| 1704616122906818000 | 0.17364817747052724 |
| 1704616185692931000 | 0.34202014295085736 |
| 1704616206396238000 | 0.5000000003478834  |
| 1704616222584839000 | 0.34202014295085736 |
| 1704616238382964000 | 0.6427876098414858  |
| 1704616260741136000 | 1                   |
+---------------------+---------------------+
2 columns, 7 rows in set

SIN可以其他函数互相嵌套使用，比如FIRST, LAST, MEAN, MIN, MAX , DIFFERENCE 等。

ATAN()

返回指定Filed列值的反正切值。返回值采用弧度形式且介于在 -Pi/2 到 Pi/2 之间，Field列值的范围必须是[-1,1]。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT ATAN( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算反正切值

> SELECT ATAN("a") FROM math
name: math
+---------------------+---------------------+
| time                | atan                |
+---------------------+---------------------+
| 1704616695559558000 | 0.7752974968121263  |
| 1704616698618449000 | 0.7544801838344056  |
| 1704616705557497000 | 0.7159911144163001  |
| 1704616711437450000 | 0.6561787179913948  |
| 1704616714600279000 | 0.7853981633974483  |
| 1704616725041028000 | 0.5693131911006619  |
| 1704616732497883000 | 0.4636476090008061  |
| 1704616739281242000 | 0.3277385067805555  |
| 1704616741456246000 | 0.16839015714752992 |
| 1704616744909623000 | 0                   |
+---------------------+---------------------+
2 columns, 10 rows in set

ATAN还可以与其他函数互相嵌套使用，比如FIRST, LAST, MEAN, MIN, MAX 等

ATAN2()

根据给定的 X 轴及 Y 轴坐标值，返回 y/x 的反正切值。返回值在 -Pi到 Pi 之间(不包括 -Pi)。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT ATAN2( [ * | <field_key> | num ], [ <field_key> | num ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算两个值的正切值

> SELECT x,y FROM math
name: math
+---------------------+-----+------+
| time                | x   | y    |
+---------------------+-----+------+
| 1704618741588098000 | 12  | 13   |
| 1704618746237393000 | 12  | 14   |
| 1704618756398017000 | 9.8 | 10.6 |
| 1704618766297371000 | 23  | 10.6 |
| 1704618773693502000 | 2   | 5    |
+---------------------+-----+------+
3 columns, 5 rows in set

> SELECT ATAN2(x,y) FROM math
name: math
+---------------------+--------------------+
| time                | atan2              |
+---------------------+--------------------+
| 1704618741588098000 | 0.7454194762741583 |
| 1704618746237393000 | 0.7086262721276703 |
| 1704618756398017000 | 0.7462025614703937 |
| 1704618766297371000 | 1.138940123148567  |
| 1704618773693502000 | 0.3805063771123649 |
+---------------------+--------------------+
2 columns, 5 rows in set

计算两个值的正切值，其中一个为固定值

> SELECT ATAN2(x,10) FROM math
name: math
+---------------------+---------------------+
| time                | atan2               |
+---------------------+---------------------+
| 1704618741588098000 | 0.8760580505981934  |
| 1704618746237393000 | 0.8760580505981934  |
| 1704618756398017000 | 0.7752974968121265  |
| 1704618766297371000 | 1.1606689862534056  |
| 1704618773693502000 | 0.19739555984988078 |
+---------------------+---------------------+
2 columns, 5 rows in set

ATAN2还可以与其他函数互相嵌套使用，比如FIRST, LAST, MEAN, MIN, MAX 等

> SELECT ATAN2(FIRST("x"),LAST("y")) FROM math GROUP BY time(10s) LIMIT 4
name: math
+---------------------+--------------------+
| time                | atan2              |
+---------------------+--------------------+
| 1704618740000000000 | 0.7086262721276703 |
| 1704618750000000000 | 0.7462025614703937 |
| 1704618760000000000 | 1.138940123148567  |
| 1704618770000000000 | 0.3805063771123649 |
+---------------------+--------------------+
2 columns, 4 rows in set

CEIL()

返回指定Field列值向上取整后的值。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT CEIL( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

向上取整

> SELECT "a" FROM math
name: math
+---------------------+------+
| time                | a    |
+---------------------+------+
| 1704616695559558000 | 0.98 |
| 1704616698618449000 | 0.94 |
| 1704616705557497000 | 0.87 |
| 1704616711437450000 | 0.77 |
| 1704616714600279000 | 1    |
| 1704616725041028000 | 0.64 |
| 1704616732497883000 | 0.5  |
| 1704616739281242000 | 0.34 |
| 1704616741456246000 | 0.17 |
| 1704616744909623000 | 0    |
+---------------------+------+
2 columns, 10 rows in set

> SELECT CEIL(a) FROM math
name: math
+---------------------+------+
| time                | ceil |
+---------------------+------+
| 1704616695559558000 | 1    |
| 1704616698618449000 | 1    |
| 1704616705557497000 | 1    |
| 1704616711437450000 | 1    |
| 1704616714600279000 | 1    |
| 1704616725041028000 | 1    |
| 1704616732497883000 | 1    |
| 1704616739281242000 | 1    |
| 1704616741456246000 | 1    |
| 1704616744909623000 | 0    |
+---------------------+------+
2 columns, 10 rows in set

CEIL仅对小数生效，第一个原始数据0.98向上取整为1。1和0等整数向上取整不变。

EXP()

返回 e 的 n 次方, n为指定Field列值。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT EXP( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

> SELECT degrees/10 FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+---------+--------------+
| time                | degrees | location     |
+---------------------+---------+--------------+
| 1566000360000000000 | 6      | santa_monica |
| 1566000720000000000 | 6.2      | santa_monica |
| 1566001080000000000 | 6.2      | santa_monica |
| 1566001440000000000 | 6      | santa_monica |
| 1566001800000000000 | 6.3      | santa_monica |
| 1566002160000000000 | 6.4      | santa_monica |
| 1566002520000000000 | 6.3      | santa_monica |
| 1566002880000000000 | 6.3      | santa_monica |
| 1566003240000000000 | 6.1      | santa_monica |
+---------------------+---------+--------------+
3 columns, 9 rows in set

> SELECT EXP(degrees/10) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+--------------------+
| time                | exp                |
+---------------------+--------------------+
| 1566000360000000000 | 403.4287934927351  |
| 1566000720000000000 | 492.7490410932563  |
| 1566001080000000000 | 492.7490410932563  |
| 1566001440000000000 | 403.4287934927351  |
| 1566001800000000000 | 544.571910125929   |
| 1566002160000000000 | 601.8450378720822  |
| 1566002520000000000 | 544.571910125929   |
| 1566002880000000000 | 544.571910125929   |
| 1566003240000000000 | 445.85777008251677 |
+---------------------+--------------------+
2 columns, 9 rows in set

EXP还可以与其他函数互相嵌套使用，比如FIRST, LAST, MEAN, MIN, MAX, ABS, CEIL 等

FLOOR()

返回指定Field列向下取整后的值。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT FLOOR( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算向下取整

> SELECT degrees/10 FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+---------+
| time                | degrees |
+---------------------+---------+
| 1566000360000000000 | 6       |
| 1566000720000000000 | 6.2     |
| 1566001080000000000 | 6.2     |
| 1566001440000000000 | 6       |
| 1566001800000000000 | 6.3     |
| 1566002160000000000 | 6.4     |
| 1566002520000000000 | 6.3     |
| 1566002880000000000 | 6.3     |
| 1566003240000000000 | 6.1     |
+---------------------+---------+
2 columns, 9 rows in set

> SELECT FLOOR(degrees/10) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+-------+
| time                | floor |
+---------------------+-------+
| 1566000360000000000 | 6     |
| 1566000720000000000 | 6     |
| 1566001080000000000 | 6     |
| 1566001440000000000 | 6     |
| 1566001800000000000 | 6     |
| 1566002160000000000 | 6     |
| 1566002520000000000 | 6     |
| 1566002880000000000 | 6     |
| 1566003240000000000 | 6     |
+---------------------+-------+
2 columns, 9 rows in set

向下取整仅对小数生效，第一个原始数据6向下取整仍为6。

LN()

返回指定Field列值的自然对数。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT LN( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例计算degrees列的自然对数

> SELECT degrees FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+---------+
| time                | degrees |
+---------------------+---------+
| 1566000360000000000 | 60      |
| 1566000720000000000 | 62      |
| 1566001080000000000 | 62      |
| 1566001440000000000 | 60      |
| 1566001800000000000 | 63      |
| 1566002160000000000 | 64      |
| 1566002520000000000 | 63      |
| 1566002880000000000 | 63      |
| 1566003240000000000 | 61      |
+---------------------+---------+
2 columns, 9 rows in set

> SELECT LN(degrees) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+--------------------+
| time                | ln                 |
+---------------------+--------------------+
| 1566000360000000000 | 4.0943445622221    |
| 1566000720000000000 | 4.127134385045092  |
| 1566001080000000000 | 4.127134385045092  |
| 1566001440000000000 | 4.0943445622221    |
| 1566001800000000000 | 4.143134726391533  |
| 1566002160000000000 | 4.1588830833596715 |
| 1566002520000000000 | 4.143134726391533  |
| 1566002880000000000 | 4.143134726391533  |
| 1566003240000000000 | 4.110873864173311  |
+---------------------+--------------------+
2 columns, 9 rows in set

LN可以与其他函数互相嵌套使用，比如FIRST, LAST, MEAN, MIN, MAX, ABS, CEIL , FLOOR等

LOG()

根据给定底数返回指定Field列值的对数。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT LOG( [ * | <field_key> ], <b> ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算LOG₄(x)

> SELECT degrees FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+---------+
| time                | degrees |
+---------------------+---------+
| 1566000360000000000 | 60      |
| 1566000720000000000 | 62      |
| 1566001080000000000 | 62      |
| 1566001440000000000 | 60      |
| 1566001800000000000 | 63      |
| 1566002160000000000 | 64      |
| 1566002520000000000 | 63      |
| 1566002880000000000 | 63      |
| 1566003240000000000 | 61      |
+---------------------+---------+
2 columns, 9 rows in set

> SELECT LOG(degrees,4) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+--------------------+
| time                | log                |
+---------------------+--------------------+
| 1566000360000000000 | 2.9534452978042594 |
| 1566000720000000000 | 2.977098155193438  |
| 1566001080000000000 | 2.977098155193438  |
| 1566001440000000000 | 2.9534452978042594 |
| 1566001800000000000 | 2.9886399617499584 |
| 1566002160000000000 | 3                  |
| 1566002520000000000 | 2.9886399617499584 |
| 1566002880000000000 | 2.9886399617499584 |
| 1566003240000000000 | 2.9653686687814433 |
+---------------------+--------------------+
2 columns, 9 rows in set

LOG(degrees,4)中4表示底数，也可以是2或者10。可以与其他函数互相嵌套使用。

LOG2()

给定底数为2返回指定Field列值的对数。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT LOG2( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算LOG₂(x)

> SELECT LOG2(degrees) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+-------------------+
| time                | log2              |
+---------------------+-------------------+
| 1566000360000000000 | 5.906890595608519 |
| 1566000720000000000 | 5.954196310386875 |
| 1566001080000000000 | 5.954196310386875 |
| 1566001440000000000 | 5.906890595608519 |
| 1566001800000000000 | 5.977279923499917 |
| 1566002160000000000 | 6                 |
| 1566002520000000000 | 5.977279923499917 |
| 1566002880000000000 | 5.977279923499917 |
| 1566003240000000000 | 5.930737337562887 |
+---------------------+-------------------+
2 columns, 9 rows in set

可以与其他函数互相嵌套使用。

LOG10()

给定底数为10返回指定Field列值的对数。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT LOG10( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算LOG₁₀(x)

> SELECT LOG10(degrees) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+--------------------+
| time                | log10              |
+---------------------+--------------------+
| 1566000360000000000 | 1.7781512503836434 |
| 1566000720000000000 | 1.792391689498254  |
| 1566001080000000000 | 1.792391689498254  |
| 1566001440000000000 | 1.7781512503836434 |
| 1566001800000000000 | 1.7993405494535817 |
| 1566002160000000000 | 1.806179973983887  |
| 1566002520000000000 | 1.7993405494535817 |
| 1566002880000000000 | 1.7993405494535817 |
| 1566003240000000000 | 1.7853298350107671 |
+---------------------+--------------------+
2 columns, 9 rows in set

可以与其他函数互相嵌套使用。

POW()

返回指定Field列值的N次幂。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT POW( [ * | <field_key> ], <x> ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算degrees的2次幂值

> SELECT POW(degrees, 2) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+------+
| time                | pow  |
+---------------------+------+
| 1566000360000000000 | 3600 |
| 1566000720000000000 | 3844 |
| 1566001080000000000 | 3844 |
| 1566001440000000000 | 3600 |
| 1566001800000000000 | 3969 |
| 1566002160000000000 | 4096 |
| 1566002520000000000 | 3969 |
| 1566002880000000000 | 3969 |
| 1566003240000000000 | 3721 |
+---------------------+------+
2 columns, 9 rows in set

可以与其他函数互相嵌套使用。

> SELECT POW(CEIL(degrees/10), 2) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+-----+
| time                | pow |
+---------------------+-----+
| 1566000360000000000 | 36  |
| 1566000720000000000 | 49  |
| 1566001080000000000 | 49  |
| 1566001440000000000 | 36  |
| 1566001800000000000 | 49  |
| 1566002160000000000 | 49  |
| 1566002520000000000 | 49  |
| 1566002880000000000 | 49  |
| 1566003240000000000 | 49  |
+---------------------+-----+
2 columns, 9 rows in set

ROUND()

根据指定Field列值进行四舍五入(仅对小数部分)。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT ROUND( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算degrees/10，再四舍五入

> SELECT degrees/10 FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+---------+
| time                | degrees |
+---------------------+---------+
| 1566000360000000000 | 6       |
| 1566000720000000000 | 6.2     |
| 1566001080000000000 | 6.2     |
| 1566001440000000000 | 6       |
| 1566001800000000000 | 6.3     |
| 1566002160000000000 | 6.4     |
| 1566002520000000000 | 6.3     |
| 1566002880000000000 | 6.3     |
| 1566003240000000000 | 6.1     |
+---------------------+---------+
2 columns, 9 rows in set

> SELECT ROUND(degrees/10) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+-------+
| time                | round |
+---------------------+-------+
| 1566000360000000000 | 6     |
| 1566000720000000000 | 6     |
| 1566001080000000000 | 6     |
| 1566001440000000000 | 6     |
| 1566001800000000000 | 6     |
| 1566002160000000000 | 6     |
| 1566002520000000000 | 6     |
| 1566002880000000000 | 6     |
| 1566003240000000000 | 6     |
+---------------------+-------+
2 columns, 9 rows in set

可以与其他函数互相嵌套使用。

SQRT()

返回指定Field列值的平方根。不支持对Tag列计算, 不支持SLIMIT和SOFFSET。

语法

SELECT SQRT( [ * | <field_key> ] ) [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause]

示例

计算degrees的平方根

> SELECT SQRT(degrees) FROM h2o_temperature WHERE time > '2019-08-17T00:00:00Z' AND time < '2019-08-17T01:00:00Z' AND location='santa_monica'
name: h2o_temperature
+---------------------+-------------------+
| time                | sqrt              |
+---------------------+-------------------+
| 1566000360000000000 | 7.745966692414834 |
| 1566000720000000000 | 7.874007874011811 |
| 1566001080000000000 | 7.874007874011811 |
| 1566001440000000000 | 7.745966692414834 |
| 1566001800000000000 | 7.937253933193772 |
| 1566002160000000000 | 8                 |
| 1566002520000000000 | 7.937253933193772 |
| 1566002880000000000 | 7.937253933193772 |
| 1566003240000000000 | 7.810249675906654 |
+---------------------+-------------------+
2 columns, 9 rows in set

可以与其他函数互相嵌套使用。

数学函数

# ABS()

# ACOS()

# ASIN()

# COS()

# SIN()

# ATAN()

# ATAN2()

# CEIL()

# EXP()

# FLOOR()

# LN()

# LOG()

# LOG2()

# LOG10()

# POW()

# ROUND()

# SQRT()

ABS()

ACOS()

ASIN()

COS()

SIN()

ATAN()

ATAN2()

CEIL()

EXP()

FLOOR()

LN()

LOG()

LOG2()

LOG10()

POW()

ROUND()

SQRT()