全部产品

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)