全部产品
云市场

TSQL 函数说明

更新时间:2019-04-15 10:38:29

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 同参数表达式的类型相同 表达式的最小值

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,表的数据如下:

  1. select * from math_func_demo;
  2. +---------------+-----------------------+
  3. | integer | float |
  4. +---------------+-----------------------+
  5. | 2010 | 17.4 |
  6. | -2002 | -1.2 |
  7. | 2001 | 1.2 |
  8. | 6005 | 1.2 |
  9. +---------------+-----------------------+
  • ABS示例:
  1. SELECT ABS(`integer`) FROM math_func_demo;
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | 2010 |
  6. | 2002 |
  7. | 2001 |
  8. | 6005 |
  9. +------------+
  10. 4 rows selected (0.357 seconds)
  • CEIL示例:
  1. SELECT CEIL(`float`) FROM math_func_demo;
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | 18.0 |
  6. | -1.0 |
  7. | 2.0 |
  8. | 2.0 |
  9. +------------+
  10. 4 rows selected (0.647 seconds)
  • FLOOR示例:
  1. SELECT FLOOR(`float`) FROM math_func_demo;
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | 17.0 |
  6. | -2.0 |
  7. | 1.0 |
  8. | 1.0 |
  9. +------------+
  10. 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)

  1. * LOG示例:
  2. 分别获取以2为底64的对数,100的常用对数,和7.5的自然对数。
  3. ```bash
  4. SELECT LOG(2, 64) FROM (VALUES(1));
  5. +------------+
  6. | EXPR$0 |
  7. +------------+
  8. | 6.0 |
  9. +------------+
  10. 1 row selected (0.069 seconds)
  11. SELECT LOG10(100) FROM (VALUES(1));
  12. +------------+
  13. | EXPR$0 |
  14. +------------+
  15. | 2.0 |
  16. +------------+
  17. 1 row selected (0.203 seconds)
  18. SELECT LOG(7.5) FROM (VALUES(1));
  19. +---------------------+
  20. | EXPR$0 |
  21. +---------------------+
  22. | 2.0149030205422647 |
  23. +---------------------+
  24. 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的双曲正切值。

示例:

  1. SELECT RADIANS(30) AS Degrees FROM (VALUES(1));
  2. +------------+
  3. | Degrees |
  4. +------------+
  5. | 0.7853981633974483 |
  6. +------------+
  7. 1 row selected (0.045 seconds)
  8. SELECT SIN(0.7853981633974483) AS `Sine of 30 degrees` FROM (VALUES(1));
  9. +-----------------------+
  10. | Sine of 45 degrees |
  11. +-----------------------+
  12. | 0.7071067811865475 |
  13. +-----------------------+
  14. 1 row selected (0.059 seconds)
  15. SELECT TAN(0.7853981633974483) AS `Tangent of 30 degrees` from (VALUES(1));
  16. +-----------------------+
  17. | Tangent of 45 degrees |
  18. +-----------------------+
  19. | 0.9999999999999999 |
  20. +-----------------------+

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 [, ...]])
连接字符串。

  1. SELECT CONCAT('Drill', ' ', 1.0, ' ', 'release') FROM (VALUES(1));
  2. +--------------------+
  3. | EXPR$0 |
  4. +--------------------+
  5. | Drill 1.0 release |
  6. +--------------------+
  7. 1 row selected (0.134 seconds)
  • INITCAP

语法INITCAP(string)
返回使用首字母大写的字符串。

  1. SELECT INITCAP('china beijing') FROM (VALUES(1));
  2. +---------------------------+
  3. | EXPR$0 |
  4. +---------------------------+
  5. | China Beijing |
  6. +---------------------------+
  7. 1 row selected (0.106 seconds)
  • LENGTH

语法LENGTH(string [, encoding])
返回字符串中的字符数。

  1. SELECT LENGTH('Hangzhou') FROM (VALUES(1));
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | 8 |
  6. +------------+
  7. 1 row selected (0.127 seconds)
  • LOWER

语法LOWER(string)
将字符串中的字符转换为小写。

  1. SELECT LOWER('China Beijing') FROM (VALUES(1));
  2. +---------------+
  3. | EXPR$0 |
  4. +---------------+
  5. | china beijing |
  6. +---------------+
  7. 1 row selected (0.103 seconds)
  • LPAD

语法LPAD(string,length [, fill text])
将字符串填充到预先填充(fill text)或空格指定的长度, 填充从从左边进行。 如果长度超过指定长度,则截断字符串。

  1. SELECT LPAD('hi', 5, 'xy') FROM (VALUES(1));
  2. +------------------------------+
  3. | EXPR$0 |
  4. +------------------------------+
  5. | xyxhi |
  6. +------------------------------+
  7. 1 row selected (0.132 seconds)
  • LTRIM

语法LTRIM(string1, string2)
从string1的开头删除与string2中的字符匹配的任何字符。

  1. SELECT LTRIM('zzzytest', 'xyz') FROM (VALUES(1));
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | test |
  6. +------------+
  7. 1 row selected (0.131 seconds)
  • REGEXP_REPLACE

语法REGEXP_REPLACE(source_char, pattern, replacement)
替换匹配Java正则表达式模式的子字符串的新文本。

  1. SELECT REGEXP_REPLACE('abc, acd, ade, aef', 'a', 'b') FROM (VALUES(1));
  2. +---------------------+
  3. | EXPR$0 |
  4. +---------------------+
  5. | bbc, bcd, bde, bef |
  6. +---------------------+
  7. 1 row selected (0.105 seconds)
  8. SELECT REGEXP_REPLACE('abc, acd, ade, aef', 'a.','b') FROM (VALUES(1));
  9. +-----------------+
  10. | EXPR$0 |
  11. +-----------------+
  12. | bc, bd, be, bf |
  13. +-----------------+
  14. 1 row selected (0.113 seconds)
  • RPAD语法RPAD (string, length [, fill text])
    将字符串填充到指定的长度。 如果您未提供文本或文本不足以实现长度,则使用填充空格填充填充关键字后指定的文本。 如果长度超过指定长度,则截断字符串。
  1. SELECT RPAD('hi', 5, 'xy') FROM (VALUES(1));
  2. +-------------------------+
  3. | EXPR$0 |
  4. +-------------------------+
  5. | hixyx |
  6. +-------------------------+
  7. 1 row selected (0.107 seconds)
  • RTRIM

语法RTRIM(string1, string2)
从string1的末尾删除与string2中的字符匹配的任何字符。

  1. SELECT RTRIM('testxxzx', 'xyz') from (VALUES(1));
  2. +--------------------+
  3. | EXPR$0 |
  4. +--------------------+
  5. | tes |
  6. +--------------------+
  7. 1 row selected (0.102 seconds)
  • STRPOS

语法STRPOS(string, substring)
返回字符串中子字符串的位置。

  1. SELECT STRPOS('high', 'ig') FROM (VALUES(1));
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | 2 |
  6. +------------+
  7. 1 row selected (0.22 seconds)
  • SUBSTR

语法SUBSTR(string, x, y)
返回string第x至x+y的子字符串,y可缺省。

  1. SELECT SUBSTR('China Beijing', 7) FROM (VALUES(1));
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | Beijing |
  6. +------------+
  7. 1 row selected (0.134 seconds)
  8. SELECT SUBSTR('China Beijing', 3, 2) FROM (VALUES(1));
  9. +------------+
  10. | EXPR$0 |
  11. +------------+
  12. | in |
  13. +------------+
  14. 1 row selected (0.129 seconds)
  • TRIM

语法TRIM ([leading | trailing | both] [string1] from string2)
删除string2的开头,结尾或两侧与string1中的字符匹配的任何字符。

  1. SELECT TRIM(trailing 'A' from 'AABBAA') FROM (VALUES(1));
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | AABB |
  6. +------------+
  7. 1 row selected (0.172 seconds)
  8. SELECT TRIM(both 'A' from 'AABBAA') FROM (VALUES(1));
  9. +---------------+
  10. | EXPR$0 |
  11. +---------------+
  12. | BB |
  13. +---------------+
  14. 1 row selected (0.104 seconds)
  15. SELECT TRIM(leading 'A' from 'AABBAA') FROM (VALUES(1));
  16. +-----------------+
  17. | EXPR$0 |
  18. +-----------------+
  19. | BBAA |
  20. +-----------------+
  21. 1 row selected (0.101 seconds)
  • UPPER

语法UPPER(string)
将字符串中的字符转换为大写。

  1. SELECT UPPER('china beijing') FROM (VALUES(1));
  2. +---------------+
  3. | EXPR$0 |
  4. +---------------+
  5. | CHINA BEIJING |
  6. +---------------+
  7. 1 row selected (0.081 seconds)

5. 类型转换函数

5.1 CAST

CAST函数将实体(例如计算结果为单个值的表达式)从一种类型转换为另一种类型。

  • 语法

`

  1. CAST (<expression> AS <data type>)
  • expression

一个或多个值,运算符和SQL函数的组合,用于求值。

  • data type

要转换表达式的目标数据类型,例如INTEGER或DATE。

以下示例显示如何将字符串转换为数字,将数字转换为字符串,将一种类型的数字转换为另一个。

  1. SELECT CAST(456 as VARCHAR(3)) FROM (VALUES(1));
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | 456 |
  6. +------------+
  7. 1 row selected (0.08 seconds)
  8. SELECT CAST(456 as CHAR(3)) FROM (VALUES(1));
  9. +------------+
  10. | EXPR$0 |
  11. +------------+
  12. | 456 |
  13. +------------+
  14. 1 row selected (0.093 seconds)

5.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将数字,日期,时间或时间戳表达式转换为字符串。

  1. SELECT TO_CHAR(1256.789383, '#,###.###') FROM (VALUES(1));
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | 1,256.789 |
  6. +------------+
  7. 1 row selected (1.767 seconds)
  8. SELECT TO_CHAR(125677.4567, '#,###.###') FROM (VALUES(1));
  9. +--------------+
  10. | EXPR$0 |
  11. +--------------+
  12. | 125,677.457 |
  13. +--------------+
  14. 1 row selected (0.083 seconds)
  15. SELECT TO_CHAR((CAST('2008-2-23' AS DATE)), 'yyyy-MMM-dd') FROM (VALUES(1));
  16. +--------------+
  17. | EXPR$0 |
  18. +--------------+
  19. | 2008-Feb-23 |
  20. +--------------+
  21. 1 row selected (0.166 seconds)
  22. SELECT TO_CHAR(CAST('12:20:30' AS TIME), 'HH mm ss') FROM (VALUES(1));
  23. +------------+
  24. | EXPR$0 |
  25. +------------+
  26. | 12 20 30 |
  27. +------------+
  28. 1 row selected (0.07 seconds)
  29. SELECT TO_CHAR(CAST('2015-2-23 12:00:00' AS TIMESTAMP), 'yyyy MMM dd HH:mm:ss') FROM (VALUES(1));
  30. +-----------------------+
  31. | EXPR$0 |
  32. +-----------------------+
  33. | 2015 Feb 23 12:00:00 |
  34. +-----------------------+
  35. 1 row selected (0.142 seconds)
  • TO_DATE示例:第一个示例将字符串转换为日期。 第二个示例提取年份以验证TSQL将日期识别为日期类型。
  1. SELECT TO_DATE('2015-FEB-23', 'yyyy-MMM-dd') FROM (VALUES(1));
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | 2015-02-23 |
  6. +------------+
  7. 1 row selected (0.077 seconds)
  8. SELECT EXTRACT(year from mydate) `extracted year` FROM (SELECT TO_DATE('2015-FEB-23', 'yyyy-MMM-dd') AS mydate FROM (VALUES(1)));
  9. +------------+
  10. | myyear |
  11. +------------+
  12. | 2015 |
  13. +------------+
  14. 1 row selected (0.128 seconds)

以下示例将UNIX纪元时间戳转换为日期。

  1. SELECT TO_DATE(1427849046000) FROM (VALUES(1));
  2. +------------+
  3. | EXPR$0 |
  4. +------------+
  5. | 2015-04-01 |
  6. +------------+
  7. 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)

  1. * TO_TIMESTAMP示例:
  2. 将日期转换为时间戳。
  3. ```sql
  4. SELECT TO_TIMESTAMP('2008-2-23 12:00:00', 'yyyy-MM-dd HH:mm:ss') FROM (VALUES(1));
  5. +------------------------+
  6. | EXPR$0 |
  7. +------------------------+
  8. | 2008-02-23 12:00:00.0 |
  9. +------------------------+
  10. 1 row selected (0.126 seconds)

将Unix Epoch时间转换为时间戳。

  1. SELECT TO_TIMESTAMP(1427936330) FROM (VALUES(1));
  2. +------------------------+
  3. | EXPR$0 |
  4. +------------------------+
  5. | 2015-04-01 17:58:50.0 |
  6. +------------------------+
  7. 1 row selected (0.114 seconds)

将UTC日期转换为UTC时区代码的时间戳偏移量。

  1. SELECT TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z') AS Original,
  2. TO_CHAR(TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s z'), 'z') AS New_TZ
  3. FROM (VALUES(1));
  4. +------------------------+---------+
  5. | Original | New_TZ |
  6. +------------------------+---------+
  7. | 2015-03-30 20:49:00.0 | UTC |
  8. +------------------------+---------+
  9. 1 row selected (0.148 seconds)