时序数据库 TSDB TSQL函数说明

本文档主要介绍了TSQL函数。

聚合函数

聚合函数	参数类型	返回类型	返回值
avg(expression)	SMALLINT，INTEGER，BIGINT，FLOAT，DOUBLE	同参数表达式的类型相同	表达式的平均值
count(*)	无	BigInt	行的总数
count(expression)	表达式可以是任意的类型	BigInt	表达式除去null的个数
count(distinct expression)	表达式可以是任意的类型		表达式去重复去null后的个数
max(expression)	SMALLINT，INTEGER，BIGINT，FLOAT，DOUBLE， VARCHAR	同参数表达式的类型相同	表达式的最大值
min(expression)	SMALLINT，INTEGER，BIGINT，FLOAT，DOUBLE，VARCHAR	同参数表达式的类型相同	表达式的最小值
ts_last(expression, timestamp)	expression : DOUBLE，VARCHAR， BOOLEAN; timestamp: TIMESTAM	同参数expression类型相同	表达式按照时间戳排序后，最大时间戳对应的表达式的值
ts_first(expression, timestamp)	DOUBLE，VARCHAR， BOOLEAN; timestamp: TIMESTAMP	同参数expression类型相同	表达式按照时间戳排序后，最小时间戳对应的表达式的值

数学函数

TSQL支持下表中显示的数学函数以及本章节末尾列出的三角函数。大多数数学函数和所有的三角函数都采用以下输入类型：

INTEGER
BIGINT
FLOAT
DOUBLE
SMALLINT

函数	返回类型	描述
ABS(x)	Same as input	返回x的绝对值。
CBRT(x)	FLOAT8	返回x的立方根。
CEIL(x)	Same as input	返回不小于x的最小整数。
CEILING(x)	Same as input	返回不小于x的最小整数。
DEGREES(x)	FLOAT8	将x弧度转换为度数。
E()	FLOAT8	返回2.718281828459045.
EXP(x)	FLOAT8	返回e的x幂。
FLOOR(x)	Same as input	返回不大于x的最大整数。
LOG(x)	FLOAT8	返回以e为底x的对数。
LOG(x, y)	FLOAT8	返回以x为底y的对数。
LOG10(x)	FLOAT8	返回以10为底x的对数。
LSHIFT(x, y)	Same as input	返回x二进制左移y位的结果。
MOD(x, y)	FLOAT8	返回x除以y的余数。
NEGATIVE(x)	Same as input	返回x的相反数。
PI	FLOAT8	返回圆周率。
POW(x, y)	FLOAT8	返回x的y次方。
RADIANS(x)	FLOAT8	将x度转换为弧度。
RAND	FLOAT8	返回0~1之间随机数。
ROUND(x)	Same as input	舍入到最近的整数。
RSHIFT(x, y)	Same as input	返回x二进制右移y位的结果。
SIGN(x)	INT	返回sign(x)。
SQRT(x)	Same as input	返回x的平方根。
TRUNC(x, y)	DOUBLE	返回x截断到y小数位。y可缺省。默认值为0。

示例

以下的数学函数实例假设有一个表 math_func_demo，表的数据如下：

select * from math_func_demo;

+---------------+-----------------------+
|    integer    |         float         |
+---------------+-----------------------+
|2010|17.4|
|-2002|-1.2|
|2001|1.2|
|6005|1.2|
+---------------+-----------------------+

ABS示例：

SELECT ABS(`integer`) FROM math_func_demo;
+------------+
|   EXPR$0   |
+------------+
| 2010       |
| 2002       |
| 2001       |
| 6005       |
+------------+
4 rows selected (0.357 seconds)

CEIL示例：

SELECT CEIL(`float`) FROM math_func_demo;
+------------+
|   EXPR$0   |
+------------+
| 18.0       |
| -1.0       |
| 2.0        |
| 2.0        |
+------------+
4 rows selected (0.647 seconds)

FLOOR示例：

SELECT FLOOR(`float`) FROM math_func_demo;
+------------+
|   EXPR$0   |
+------------+
| 17.0       |
| -2.0       |
| 1.0        |
| 1.0        |
+------------+
4 rows selected (0.11 seconds)

ROUND示例：获得舍入到最近的整数和保留4位小数的近似数。

``bash
SELECT ROUND(float`) FROM math_func_demo;
+——————+| EXPR$0 |+——————+| 3.0 || -1.0 || 1.0 || 1.0 |+——————+4 rows selected (0.061 seconds)
SELECT ROUND(float, 4) FROM math_func_demo;
+——————+| EXPR$0 |+——————+| 3.1416 || -1.2 || 1.2 || 1.2 |+——————+4 rows selected (0.059 seconds)

LOG示例：分别获取以2为底64的对数，100的常用对数，和7.5的自然对数。

```bash
SELECT LOG(2, 64) FROM (VALUES(1));
+------------+
|   EXPR$0   |
+------------+
| 6.0        |
+------------+
1 row selected (0.069 seconds)
SELECT LOG10(100) FROM (VALUES(1));
+------------+
|   EXPR$0   |
+------------+
| 2.0        |
+------------+
1 row selected (0.203 seconds)
SELECT LOG(7.5) FROM (VALUES(1));
+---------------------+
|       EXPR$0        |
+---------------------+
| 2.0149030205422647  |
+---------------------+
1 row selected (0.139 seconds)

三角函数

TSQL支持以下三角函数，返回FLOAT8类型的结果。

SIN(x)返回x的正弦值。
COS(x)返回x的余弦值。
TAN(x)返回x的正切值。
ASIN(x)返回x的反正弦值。
ACOS(x)返回x的反余弦值。
ATAN(x)返回x的反正切值。
SINH(x)返回x的双曲正弦值。
COSH(x)返回x的双曲余弦值。
TANH(x)返回x的双曲正切值。

示例：

SELECT RADIANS(30) AS Degrees FROM (VALUES(1));

+------------+
|Degrees|
+------------+
|0.7853981633974483|
+------------+
1 row selected (0.045 seconds)

SELECT SIN(0.7853981633974483) AS `Sine of 30 degrees` FROM (VALUES(1));

+-----------------------+
|Sine of 45 degrees   |
+-----------------------+
|0.7071067811865475|
+-----------------------+
1 row selected (0.059 seconds)

SELECT TAN(0.7853981633974483) AS `Tangent of 30 degrees` from (VALUES(1));

+-----------------------+
|Tangent of 45 degrees |
+-----------------------+
|0.9999999999999999|
+-----------------------+

String函数

TSQL支持以下字符串函数：

函数	返回类型
CONCAT	VARCHAR
INITCAP	VARCHAR
LENGTH	INTEGER
LOWER	VARCHAR
LPAD	VARCHAR
LTRIM	VARCHAR
REGEXP_REPLACE	VARCHAR
RPAD	VARCHAR
RTRIM	VARCHAR
STRPOS	INTEGER
SUBSTR	VARCHAR
TRIM	VARCHAR
UPPER	VARCHAR

示例

CONCAT

语法：CONCAT(string [,string [, ...]]) 连接字符串。

SELECT CONCAT('Drill',' ',1.0,' ','release') FROM (VALUES(1));

+--------------------+
|       EXPR$0       |
+--------------------+
|Drill1.0 release  |
+--------------------+
1 row selected (0.134 seconds)

INITCAP

语法：INITCAP(string) 返回使用首字母大写的字符串。

SELECT INITCAP('china beijing') FROM (VALUES(1));

+---------------------------+
|          EXPR$0           |
+---------------------------+
|ChinaBeijing|
+---------------------------+
1 row selected (0.106 seconds)

LENGTH

语法： LENGTH(string [, encoding]) 返回字符串中的字符数。

SELECT LENGTH('Hangzhou') FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
|8|
+------------+
1 row selected (0.127 seconds)

LOWER

语法：LOWER(string) 将字符串中的字符转换为小写。

SELECT LOWER('China Beijing') FROM (VALUES(1));

+---------------+
|    EXPR$0     |
+---------------+
| china beijing  |
+---------------+
1 row selected (0.103 seconds)

LPAD

语法：LPAD(string,length [, fill text]) 将字符串填充到预先填充（fill text）或空格指定的长度，填充从左边进行。如果长度超过指定长度，则截断字符串。

SELECT LPAD('hi',5,'xy') FROM (VALUES(1));

+------------------------------+
|            EXPR$0            |
+------------------------------+
| xyxhi                        |
+------------------------------+
1 row selected (0.132 seconds)

LTRIM

语法：LTRIM(string1, string2) 从string1的开头删除与string2中的字符匹配的任何字符。

SELECT LTRIM('zzzytest','xyz') FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
| test       |
+------------+
1 row selected (0.131 seconds)

REGEXP_REPLACE

语法：REGEXP_REPLACE(source_char, pattern, replacement) 替换匹配Java正则表达式模式的子字符串的新文本。

SELECT REGEXP_REPLACE('abc, acd, ade, aef','a','b') FROM (VALUES(1));
+---------------------+
|       EXPR$0        |
+---------------------+
| bbc, bcd, bde, bef  |
+---------------------+
1 row selected (0.105 seconds)

SELECT REGEXP_REPLACE('abc, acd, ade, aef','a.','b') FROM (VALUES(1));
+-----------------+
|     EXPR$0      |
+-----------------+
| bc, bd, be, bf  |
+-----------------+
1 row selected (0.113 seconds)

RPAD

语法：RPAD (string, length [, fill text]) 将字符串填充到指定的长度。如果您未提供文本或文本不足以实现长度，则使用填充空格填充关键字后指定的文本。如果长度超过指定长度，则截断字符串。
```
SELECT RPAD('hi',5,'xy') FROM (VALUES(1));
+-------------------------+
|         EXPR$0          |
+-------------------------+
| hixyx                   |
+-------------------------+
1 row selected (0.107 seconds)
```

RTRIM

语法：RTRIM(string1, string2) 从string1的末尾删除与string2中的字符匹配的任何字符。

SELECT RTRIM('testxxzx','xyz')from(VALUES(1));
+--------------------+
|       EXPR$0       |
+--------------------+
| tes                |
+--------------------+
1 row selected (0.102 seconds)

STRPOS

语法：STRPOS(string, substring) 返回字符串中子字符串的位置。

SELECT STRPOS('high','ig') FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
|2|
+------------+
1 row selected (0.22 seconds)

SUBSTR

语法：SUBSTR(string, x, y) 返回string第x至x+y的子字符串，y可缺省。

SELECT SUBSTR('China Beijing',7) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
|Beijing|
+------------+
1 row selected (0.134 seconds)

SELECT SUBSTR('China Beijing',3,2) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
|in|
+------------+
1 row selected (0.129 seconds)

TRIM

语法：TRIM ([leading | trailing | both] [string1] from string2) 删除string2的开头，结尾或两侧与string1中的字符匹配的任何字符。

SELECT TRIM(trailing 'A'from'AABBAA') FROM (VALUES(1));
+------------+
|   EXPR$0   |
+------------+
| AABB       |
+------------+
1 row selected (0.172 seconds)

SELECT TRIM(both 'A'from'AABBAA') FROM (VALUES(1));
+---------------+
|    EXPR$0     |
+---------------+
| BB            |
+---------------+
1 row selected (0.104 seconds)

SELECT TRIM(leading 'A'from'AABBAA') FROM (VALUES(1));
+-----------------+
|     EXPR$0      |
+-----------------+
| BBAA            |
+-----------------+
1 row selected (0.101 seconds)

UPPER

语法：UPPER(string) 将字符串中的字符转换为大写。

SELECT UPPER('china beijing') FROM (VALUES(1));

+---------------+
|    EXPR$0     |
+---------------+
| CHINA BEIJING |
+---------------+
1 row selected (0.081 seconds)

时间戳函数

TSQL支持以下的时间戳函数:

函数	返回类型	描述	例子
now()	timestamp	返回当前时间戳	now()
CURRENT_TIMESTAMP	timestamp	返回当前时间戳	CURRENT_TIMESTAMP
CURRENT_DATE	date	返回当前日期	CURRENT_DATE
CURRENT_TIME	time	返回当前时间 (不包含日期)
EXTRACT(component FROM timestamp/date/time)	integer	从时间戳，日期或时间中获得指定时间单位 (year, month, day, hour, minute, second)的值	EXTRACT（day from `timestamp`
tumble(timestamp, interval)	timestamp	按照指定的时间窗口，获得包含输入时间戳的时间窗口的下届	tumble(`timestamp`, interval ‘5’ minute)
date_diff(timestamp, interval)	timestamp	从timestamp中减去interval后获得的timestamp	date_diff(timestamp, interval ‘5’ minute)
date_add(timestamp, interval)	timestamp	从timestamp中加上interval后获得的timestamp	date_add(timestamp, interval ‘5’ minute)

下面列出这些时间戳相关的函数的简单用法：

SELECT CURRENT_DATE FROM (VALUES(1));
+---------------+
| CURRENT_DATE  |
+---------------+
|2019-11-27|
+---------------+

SELECT EXTRACT(hour FROM TIME '17:12:28.5') FROM (VALUES(1));
+---------+
| EXPR$0  |
+---------+
|17|
+---------+

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40') FROM (VALUES(1));
+---------+
| EXPR$0  |
+---------+
|40.0|
+---------+

SELECT DATE_DIFF(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) FROM (VALUES(1));
+------------------------+
|         EXPR$0         |
+------------------------+
|2001-02-1620:33:40.0|
+------------------------+

SELECT DATE_ADD(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) FROM (VALUES(1));
+------------------------+
|         EXPR$0         |
+------------------------+
|2001-02-1620:43:40.0|
+------------------------+

SELECT tumble(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) FROM (VALUES(1));
+------------------------+
|         EXPR$0         |
+------------------------+
|2001-02-1620:35:00.0|
+------------------------+

类型转换函数

CAST

CAST函数将实体（例如计算结果为单个值的表达式）从一种类型转换为另一种类型。

语法
```
CAST (<expression> AS <data type>)
```
expression

一个或多个值，运算符和SQL函数的组合，用于求值。

data type

要转换表达式的目标数据类型，例如INTEGER或DATE。

以下示例显示如何将字符串转换为数字，将数字转换为字符串，将一种类型的数字转换为另一个。

SELECT CAST(456as VARCHAR(3)) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
|456|
+------------+
1 row selected (0.08 seconds)

SELECT CAST(456as CHAR(3)) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
|456|
+------------+
1 row selected (0.093 seconds)

时间戳、日期、时间类型相关的转化函数

TSQL支持以下示例中显示的日期和时间文字格式：

2008-12-15
22:55:55.123…

如果您有其他格式的日期和时间，请使用数据类型转换功能执行以下转换。

TIMESTAMP，DATE，TIME，INTEGER，FLOAT或DOUBLE到字符串，类型为VARCHAR。
字符串到DATE。
字符串到NUMBER。

下表列出了您可以在TSQL查询中使用的数据类型格式化函数，如本节所述。

Function	Return Type
TO_CHAR(expression, format)	VARCHAR
TO_DATE(expression, format)	DATE
TO_TIMESTAMP(VARCHAR, format)	TIMESTAMP
TO_TIMESTAMP(DOUBLE)	TIMESTAMP

日期/时间转换的格式说明符

使用以下Joda格式说明符进行日期/时间转换。

Symbol	Meaning	Presentation	Examples
G	era	text	AD
C	century of era (>=0)	number	20
Y	year of era (>=0)	year	1996
x	weekyear	year	1996
w	week of weekyear	number	27
e	day of week	number	2
E	day of week	text	Tuesday; Tue
y	year	year	1996
D	day of year	number	189
M	month of year	month	July; Jul; 07
d	day of month	number	10
a	halfday of day	text	PM
K	hour of halfday (0~11)	number	0
h	clockhour of halfday (1~12) number	12	无
H	hour of day (0~23)	number	0
k	clockhour of day (1~24)	number	24
m	minute of hour	number	30
s	second of minute	number	55
S	fraction of second	number	978
z	time zone	text	Pacific Standard Time; PST
Z	time zone offset/id	zone	-0800; -08:00; America/Los_Angeles
‘	single quotation mark, escape for text delimiter	literal	无

TO_CHAR示例：

将数字，日期，时间或时间戳表达式转换为字符串。

SELECT TO_CHAR(1256.789383,'#,###.###') FROM (VALUES(1));
+------------+
|   EXPR$0   |
+------------+
|1,256.789|
+------------+
1 row selected (1.767 seconds)

SELECT TO_CHAR(125677.4567,'#,###.###') FROM (VALUES(1));
+--------------+
|    EXPR$0    |
+--------------+
|125,677.457|
+--------------+
1 row selected (0.083 seconds)

SELECT TO_CHAR((CAST('2008-2-23' AS DATE)),'yyyy-MMM-dd') FROM (VALUES(1));
+--------------+
|    EXPR$0    |
+--------------+
|2008-Feb-23|
+--------------+
1 row selected (0.166 seconds)

SELECT TO_CHAR(CAST('12:20:30' AS TIME),'HH mm ss') FROM (VALUES(1));
+------------+
|   EXPR$0   |
+------------+
|122030|
+------------+
1 row selected (0.07 seconds)

SELECT TO_CHAR(CAST('2015-2-23 12:00:00' AS TIMESTAMP),'yyyy MMM dd HH:mm:ss') FROM (VALUES(1));
+-----------------------+
|        EXPR$0         |
+-----------------------+
|2015Feb2312:00:00|
+-----------------------+
1 row selected (0.142 seconds)

TO_DATE示例：

以下示例将字符串转换为日期。

SELECT TO_DATE('2015-FEB-23','yyyy-MMM-dd') FROM (VALUES(1));
+------------+
|   EXPR$0   |
+------------+
|2015-02-23|
+------------+
1 row selected (0.077 seconds)

SELECT EXTRACT(year from mydate)`extracted year` FROM (SELECT TO_DATE('2015-FEB-23','yyyy-MMM-dd') AS mydate FROM (VALUES(1)));

+------------+
|   myyear   |
+------------+
|2015|
+------------+
1 row selected (0.128 seconds)

以下示例将UNIX纪元时间戳转换为日期。

SELECT TO_DATE(1427849046000) FROM (VALUES(1));

+------------+
|   EXPR$0   |
+------------+
|2015-04-01|
+------------+
1 row selected (0.082 seconds)

TO_TIME示例：

将字符串转换为时间。

SELECT to_time(82855000) FROM (VALUES(1));+——————+| EXPR$0 |+——————+| 23:00:55 |+——————+1 row selected (0.086 seconds)

TO_TIMESTAMP示例：

将日期转换为时间戳。

```sql
SELECT TO_TIMESTAMP('2008-2-23 12:00:00', 'yyyy-MM-dd HH:mm:ss') FROM (VALUES(1));
+------------------------+
|         EXPR$0         |
+------------------------+
| 2008-02-23 12:00:00.0  |
+------------------------+
1 row selected (0.126 seconds)

将Unix Epoch时间转换为时间戳。

SELECT TO_TIMESTAMP(1427936330) FROM (VALUES(1));
+------------------------+
|         EXPR$0         |
+------------------------+
| 2015-04-01 17:58:50.0  |
+------------------------+
1 row selected (0.114 seconds)

将UTC日期转换为UTC时区代码的时间戳偏移量。

SELECT TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z') AS Original, 
       TO_CHAR(TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z'), 'z') AS New_TZ 
FROM (VALUES(1));
+------------------------+---------+
|        Original        | New_TZ  |
+------------------------+---------+
| 2015-03-30 20:49:00.0  | UTC     |
+------------------------+---------+
1 row selected (0.148 seconds)