Search

# TSQL函数说明

## 聚合函数

`avg(expression)`

SMALLINT，INTEGER，BIGINT，FLOAT，DOUBLE

`count(*)`

BigInt

`count(expression)`

BigInt

`count(distinct expression)`

`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

`ts_first(expression, timestamp)`

expression : DOUBLE，VARCHAR， BOOLEAN; timestamp: TIMESTAMP

## 数学函数

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

• INTEGER

• BIGINT

• FLOAT

• DOUBLE

• SMALLINT

`ABS(x)`

Same as input

`CBRT(x)`

FLOAT8

`CEIL(x)`

Same as input

`CEILING(x)`

Same as input

`DEGREES(x)`

FLOAT8

`E()`

FLOAT8

`EXP(x)`

FLOAT8

`FLOOR(x)`

Same as input

`LOG(x)`

FLOAT8

`LOG(x, y)`

FLOAT8

`LOG10(x)`

FLOAT8

`LSHIFT(x, y)`

Same as input

`MOD(x, y)`

FLOAT8

`NEGATIVE(x)`

Same as input

PI

FLOAT8

`POW(x, y)`

FLOAT8

`RADIANS(x)`

FLOAT8

RAND

FLOAT8

`ROUND(x)`

Same as input

`RSHIFT(x, y)`

Same as input

`SIGN(x)`

INT

`SQRT(x)`

Same as input

`TRUNC(x, y)`

DOUBLE

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

VARCHAR

LTRIM

VARCHAR

REGEXP_REPLACE

VARCHAR

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

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

`EXTRACT（day from `timestamp`)`

`tumble(timestamp, interval)`

timestamp

`tumble(`timestamp`, interval‘5’minute)`

`date_diff(timestamp, interval)`

timestamp

`date_diff(timestamp, interval‘5’minute)`

`date_add(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。

TO_CHAR(expression, format)

VARCHAR

TO_DATE(expression, format)

DATE

TO_TIMESTAMP(VARCHAR, format)

TIMESTAMP

TO_TIMESTAMP(DOUBLE)

TIMESTAMP

Symbol

Meaning

Presentation

Examples

G

era

text

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