全部產品
Search

搜尋本產品

    Time Series Database：TSQL函數說明

    文件中心

    Time Series Database：TSQL函數說明

    更新時間：Jul 06, 2024

    本文檔主要介紹了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)

    expression : 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) 替換匹配JavaRegex模式的子字串的新文本。

      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查詢中使用的資料類型格式化函數，如本節所述。

    函數

    傳回型別

    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)