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的自然对数。```bashSELECT 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示例：将日期转换为时间戳。```sqlSELECT 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_TZFROM (VALUES(1));+------------------------+---------+| Original | New_TZ |+------------------------+---------+| 2015-03-30 20:49:00.0 | UTC |+------------------------+---------+1 row selected (0.148 seconds)
版权声明
本文仅代表作者观点，不代表本站立场。
本文系作者授权发表，未经许可，不得转载。
评论