本文档主要介绍了TSQL函数。
聚合函数
聚合函数 | 参数类型 | 返回类型 | 返回值 |
---|---|---|---|
| SMALLINT,INTEGER,BIGINT,FLOAT,DOUBLE | 同参数表达式的类型相同 | 表达式的平均值。 |
| 不涉及 | BigInt | 行的总数。 |
| 表达式可以是任意的类型 | BigInt | 表达式除去null的个数。 |
| 表达式可以是任意的类型 | 不涉及 | 表达式去重、去null后的个数。 |
| SMALLINT,INTEGER,BIGINT,FLOAT,DOUBLE, VARCHAR | 同参数表达式的类型相同 | 表达式的最大值。 |
| SMALLINT,INTEGER,BIGINT,FLOAT,DOUBLE,VARCHAR | 同参数表达式的类型相同 | 表达式的最小值。 |
| expression : DOUBLE,VARCHAR, BOOLEAN; timestamp: TIMESTAM | 同参数expression类型相同 | 表达式按照时间戳排序后,最大时间戳对应的表达式的值。 |
| expression : DOUBLE,VARCHAR, BOOLEAN; timestamp: TIMESTAMP | 同参数expression类型相同 | 表达式按照时间戳排序后,最小时间戳对应的表达式的值。 |
数学函数
TSQL支持下表中显示的数学函数以及本章节末尾列出的三角函数。大多数数学函数和所有的三角函数都采用以下输入类型:
INTEGER
BIGINT
FLOAT
DOUBLE
SMALLINT
函数 | 返回类型 | 描述 |
---|---|---|
| Same as input | 返回x的绝对值。 |
| FLOAT8 | 返回x的立方根。 |
| Same as input | 返回不小于x的最小整数。 |
| Same as input | 返回不小于x的最小整数。 |
| FLOAT8 | 将x弧度转换为度数。 |
| FLOAT8 | 返回2.718281828459045。 |
| FLOAT8 | 返回e的x幂。 |
| Same as input | 返回不大于x的最大整数。 |
| FLOAT8 | 返回以e为底x的对数。 |
| FLOAT8 | 返回以x为底y的对数。 |
| FLOAT8 | 返回以10为底x的对数。 |
| Same as input | 返回x二进制左移y位的结果。 |
| FLOAT8 | 返回x除以y的余数。 |
| Same as input | 返回x的相反数。 |
PI | FLOAT8 | 返回圆周率。 |
| FLOAT8 | 返回x的y次方。 |
| FLOAT8 | 将x度转换为弧度。 |
RAND | FLOAT8 | 返回0~1之间随机数。 |
| Same as input | 舍入到最近的整数。 |
| Same as input | 返回x二进制右移y位的结果。 |
| INT | 返回 |
| Same as input | 返回x的平方根。 |
| 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支持以下的时间戳函数:
函数 | 返回类型 | 描述 | 例子 |
---|---|---|---|
| timestamp | 返回当前时间戳。 |
|
| timestamp | 返回当前时间戳。 |
|
| date | 返回当前日期。 |
|
| time | 返回当前时间(不包含日期)。 | 无 |
| integer | 从时间戳,日期或时间中获得指定时间单位 (year, month, day, hour, minute, second)的值。 |
|
| timestamp | 按照指定的时间窗口,获得包含输入时间戳的时间窗口的下届。 |
|
| timestamp | 从timestamp中减去interval后获得的timestamp。 |
|
| timestamp | 从timestamp中加上interval后获得的timestamp。 |
|
下面列出这些时间戳相关的函数的简单用法:
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查询中使用的数据类型格式化函数,如本节所述。
函数 | 返回类型 |
---|---|
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 | 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)