TSQL 函数说明
1. 聚合函数
聚合函数 | 参数类型 | 返回类型 | 返回值 |
---|---|---|---|
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: TIMESTAMP | 同参数expression类型相同 | 表达式按照时间戳排序后,最大时间戳对应的表达式的值 |
ts_first(expression, timestamp) | expression : DOUBLE, VARCHAR, BOOLEAN; timestamp: TIMESTAMP | 同参数expression类型相同 | 表达式按照时间戳排序后,最小时间戳对应的表达式的值 |
2. 数学函数
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)
3. 三角函数
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 |
+-----------------------+
4. 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 |
+--------------------+
| Drill 1.0 release |
+--------------------+
1 row selected (0.134 seconds)
- INITCAP
语法 : INITCAP(string)
返回使用首字母大写的字符串。
SELECT INITCAP('china beijing') FROM (VALUES(1));
+---------------------------+
| EXPR$0 |
+---------------------------+
| China Beijing |
+---------------------------+
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)
5. 时间戳函数
TSQL支持以下的时间戳函数:
函数 | 返回类型 | 描述 | 例子 | |
---|---|---|---|---|
now() | timestamp | 返回当前时间戳 | now() | |
CURRENT_TIMESTAMP | timestamp | 返回当前时间戳 | CURRENT_TIMESTAMP | |
CURRENT_DATE | date | 返回当前日期 | CURRENT_DATE | |
CURRENT_TIME | time | 返回当前时间 (不包含日期) | CURRENT_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-16 20:33:40.0 |
+------------------------+
SELECT DATE_ADD(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) FROM (VALUES(1));
+------------------------+
| EXPR$0 |
+------------------------+
| 2001-02-16 20:43:40.0 |
+------------------------+
SELECT tumble(TIMESTAMP '2001-02-16 20:38:40', interval '5' minute) FROM (VALUES(1));
+------------------------+
| EXPR$0 |
+------------------------+
| 2001-02-16 20:35:00.0 |
+------------------------+
6. 类型转换函数
6.1 CAST
CAST函数将实体(例如计算结果为单个值的表达式)从一种类型转换为另一种类型。
- 语法 :
`
CAST (<expression> AS <data type>)
- expression
一个或多个值,运算符和SQL函数的组合,用于求值。
- data type
要转换表达式的目标数据类型,例如INTEGER或DATE。
以下示例显示如何将字符串转换为数字,将数字转换为字符串,将一种类型的数字转换为另一个。
SELECT CAST(456 as VARCHAR(3)) FROM (VALUES(1));
+------------+
| EXPR$0 |
+------------+
| 456 |
+------------+
1 row selected (0.08 seconds)
SELECT CAST(456 as CHAR(3)) FROM (VALUES(1));
+------------+
| EXPR$0 |
+------------+
| 456 |
+------------+
1 row selected (0.093 seconds)
6.2 时间戳,日期,时间类型相关的转化函数
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示例:
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 |
+------------+
| 12 20 30 |
+------------+
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 |
+-----------------------+
| 2015 Feb 23 12:00:00 |
+-----------------------+
1 row selected (0.142 seconds)
- TO_DATE示例:第一个示例将字符串转换为日期。 第二个示例提取年份以验证TSQL将日期识别为日期类型。
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示例:将字符串转换为时间。```sqlSELECT TO_TIME(‘12:20:30’, ‘HH:mm:ss’) FROM (VALUES(1));+——————+| EXPR$0 |+——————+| 12:20:30 |+——————+1 row selected (0.067 seconds)
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)
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论