時序SQL的常用的函數 - Lindorm

本文介紹時序SQL中支援的常用函數和使用樣本。

引擎與版本

常用時序函數僅適用於時序引擎。
時序引擎3.4.7及以上版本支援常用時序函數。如何查看或升級目前的版本，請參見時序引擎版本說明和升級小版本。

常用時序函數列表

函數	說明
CURRENT_TIMESTAMP函數	擷取系統當前已耗用時間。
SUM函數	計算時序資料表中Field列的和。
AVG函數	計算時序資料表中Field列的平均值。
COUNT函數	統計時序資料表中Field列值的個數。
MIN函數	查詢時序資料表中Field列的最小值。
MAX函數	查詢時序資料表中Field列的最大值。
FIRST函數	擷取時序資料表中Field列的第一個值。
LAST函數	擷取時序資料表中Field列的最後一個值。
PERCENTILE函數	計算時序資料表中與Field列關聯的第P個百分位欄位值。
RATE函數	計算時序資料表中Field列值的斜率。
DELTA函數	計算時序資料表中Field列的值與前一行對應值的差。
LATEST函數	擷取時序資料表中Field列的最新的N條資料。
ANOMALY_DETECT函數（時序異常檢測）	對時序資料表中Field列進行時序異常檢測。
BINS函數（特徵分箱）	對時序資料表中Field列進行特徵分箱。

資料準備

假設樣本表sensor的結構如下：

+-------------+-----------+------------+
| columnName  | typeName  | columnKind |
+-------------+-----------+------------+
| device_id   | VARCHAR   | TAG        |
| region      | VARCHAR   | TAG        |
| time        | TIMESTAMP | TIMESTAMP  |
| temperature | DOUBLE    | FIELD      |
| humidity    | DOUBLE    | FIELD      |
+-------------+-----------+------------+

表中資料如下：

+-----------+----------+---------------------------+-------------+-----------+
| device_id |  region  |           time            | temperature | humidity  |
+-----------+----------+---------------------------+-------------+-----------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.100000   | 45.000000 |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 13.200000   | 47.000000 |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   | 46.000000 |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 12.100000   | 51.000000 |
| F07A1261  | south-cn | 2021-04-22T15:51:21+08:00 | 13.200000   | 52.000000 |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | 10.600000   | 53.000000 |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 20.600000   | 55.000000 |
+-----------+----------+---------------------------+-------------+-----------+

共包含以下兩條時間軸：

時間軸1：Tag為device_id=F07A1260, region=north-cn。Field為temperature、humidity。
時間軸2：Tag為device_id=F07A1261, region=south-cn。Field為temperature、humidity。

說明

關於時間軸的概念，請參見基本概念。

時間處理函數

CURRENT_TIMESTAMP函數

說明

擷取系統當前已耗用時間。

文法

CURRENT_TIMESTAMP

重要

CURRENT_TIMESTAMP無參數，使用時直接通過關鍵字CURRENT_TIMESTAMP調用即可。
CURRENT_TIMESTAMP函數返回的時間戳記精確至毫秒，例如2023-04-23T21:13:15.819+08:00。
CURRENT_TIMESTAMP函數返回的是UTC+8格式的Lindorm執行個體服務端的系統時間，使用該函數前請確認系統時間是否滿足業務需求。
請盡量避免在實際業務寫入資料時直接使用該函數的返回結果。建議在開發測試中需要大規模產生測試資料的情境下使用CURRENT_TIMESTAMP函數。

樣本

在sensor表中插入一條資料，TAG為device_id=F07A1262, region=north-cn，時間戳記為資料寫入的目前時間。

INSERT INTO sensor(device_id,region,time,temperature,humidity) VALUES ('F07A1262','north-cn',CURRENT_TIMESTAMP,19.9,42);

彙總函式

SUM函數

說明

計算時序資料表中Field列的和。

文法

SUM(field_name)

說明

SUM的傳回型別為DOUBLE或長整型BIGINT。

參數

參數	描述
field_name	Field列名。說明 Field列的類型不能是VARCHAR和BOOLEAN類型。

樣本

樣本1：裝置ID為F07A1260和F07A1261，對每條時間軸進行降採樣操作，時間軸按照20s時間視窗計算temperature列的和。

SELECT device_id, region, time, sum(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 25.300000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 35.900000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

樣本2：裝置ID為F07A1260和F07A1261，計算每個裝置的溫度之和。

SELECT device_id, region, time, sum(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 1970-01-01T08:00:00+08:00 | 35.900000   |
| F07A1261  | south-cn | 1970-01-01T08:00:00+08:00 | 56.500000   |
+-----------+----------+---------------------------+-------------+

樣本3：計算所有裝置的溫度之和。

SELECT sum(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00';

返回結果如下：

+-------------+
| temperature |
+-------------+
| 92.400000   |
+-------------+

AVG函數

說明

計算時序資料表中Field列的平均值。

文法

AVG(field_name)

說明

AVG的傳回型別為DOUBLE類型。

參數

參數	描述
field_name	Field列名。說明 Field列的類型不能是VARCHAR和BOOLEAN類型。

樣本

樣本1：裝置ID為F07A1260和F07A1261，對每條時間軸進行降採樣操作，時間軸按照20s時間視窗計算temperature列的平均值。

SELECT device_id, region, time, avg(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.650000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 11.966667   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

樣本2：裝置ID為F07A1260和F07A1261，計算每個裝置的溫度的平均值。

SELECT device_id, region, time, avg(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 1970-01-01T08:00:00+08:00 | 11.966667   |
| F07A1261  | south-cn | 1970-01-01T08:00:00+08:00 | 14.125000   |
+-----------+----------+---------------------------+-------------+

樣本3：計算所有裝置的溫度的平均值。

SELECT avg(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00';

返回結果如下：

+-------------+
| temperature |
+-------------+
| 13.200000   |
+-------------+

COUNT函數

說明

統計時序資料表中Field列的值個數。

文法

COUNT(field_name)

說明

COUNT的傳回型別為BIGINT類型。

參數

參數	描述
field_name	Field列名。

樣本

樣本1：裝置ID為F07A1260和F07A1261，對每條時間軸進行降採樣操作，時間軸按照20s時間視窗統計各個視窗中temperature列的值個數。

SELECT device_id, region, time, count(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 2           |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 3           |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 1           |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 1           |
+-----------+----------+---------------------------+-------------+

樣本2：裝置ID為F07A1260和F07A1261，統計每個裝置的溫度的值個數。

SELECT device_id, region, time, count(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 1970-01-01T08:00:00+08:00 | 3           |
| F07A1261  | south-cn | 1970-01-01T08:00:00+08:00 | 4           |
+-----------+----------+---------------------------+-------------+

樣本3：統計所有裝置的溫度的值個數。

SELECT count(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00';

返回結果如下：

+-------------+
| temperature |
+-------------+
| 7           |
+-------------+

選擇函數

MIN函數

說明

查詢時序資料表中Field列的最小值。

文法

MIN(field_name)

說明

MIN的傳回型別為DOUBLE或長整型BIGINT。

參數

參數	描述
field_name	Field列名。說明 Field列的類型不能是VARCHAR和BOOLEAN類型。

樣本

樣本1：裝置ID為F07A1260和F07A1261，對每條時間軸進行降採樣操作，時間軸按照20s時間視窗查詢各個視窗中temperature列的最小值。

SELECT device_id, region, time, min(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.100000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 10.600000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

樣本2：裝置ID為F07A1260和F07A1261，查詢每個裝置的溫度的最小值。

SELECT device_id, region, time, min(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | 10.600000   |
+-----------+----------+---------------------------+-------------+

樣本3：查詢所有裝置的溫度的最小值。

SELECT min(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00';

返回結果如下：

+-------------+
| temperature |
+-------------+
| 10.600000   |
+-------------+

MAX函數

說明

查詢時序資料表中Field列的最大值。

文法

MAX(field_name)

說明

MAX的傳回型別為DOUBLE或長整型BIGINT。

參數

參數	描述
field_name	Field列名。說明 Field列的類型不能是VARCHAR和BOOLEAN類型。

樣本

樣本1：裝置ID為F07A1260和F07A1261，對每條時間軸進行降採樣操作，時間軸按照20s時間視窗查詢各個視窗中temperature列的最大值。

SELECT device_id, region, time, max(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 13.200000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 13.200000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

樣本2：裝置ID為F07A1260和F07A1261，查詢每個裝置的溫度的最大值。

SELECT device_id, region, time, max(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 13.200000   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

樣本三：查詢所有裝置的溫度的最大值。

SELECT max(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00';

返回結果如下：

+-------------+
| temperature |
+-------------+
| 20.600000   |
+-------------+

FIRST函數

說明

擷取時序資料表中Field列的第一個值。

文法

FIRST(field_name)

說明

FIRST的傳回型別與Field列的類型相同。

參數

參數	描述
field_name	Field列名。

樣本

樣本1：裝置ID為F07A1260和F07A1261，對每條時間軸進行降採樣操作，時間軸按照20s時間視窗擷取各個視窗中第一個溫度值。

SELECT device_id, region, time, first(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.100000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 12.100000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

樣本2：裝置ID為F07A1260和F07A1261，擷取每個裝置第一個溫度值。

SELECT device_id, region, time, first(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.100000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 12.100000   |
+-----------+----------+---------------------------+-------------+

LAST函數

說明

擷取時序資料表中Field列的最後一個值。

文法

LAST(field_name)

說明

LAST的傳回型別與Field列的類型相同。

參數

參數	描述
field_name	Field列名。

樣本

樣本1：裝置ID為F07A1260和F07A1261，對每條時間軸進行降採樣操作，時間軸按照20s時間視窗擷取各個視窗中最後一個溫度值。

SELECT device_id, region, time, last(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 13.200000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 10.600000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

樣本2：裝置ID為F07A1260和F07A1261，擷取每個裝置的最後一個溫度值。

SELECT device_id, region, time, last(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

PERCENTILE函數

說明

計算時序資料表中與Field列關聯的第P個百分位欄位值。

文法

PERCENTILE(field_name,P)

說明

PERCENTILE的傳回型別為DOUBLE類型。

參數

參數	描述
field_name	Field列名。說明 Field列的類型不能是VARCHAR和BOOLEAN類型。
P	取值為整數或浮點數，範圍為[0,100]，預設值為50。

樣本

樣本1：裝置ID為F07A1260和F07A1261，對每條時間軸進行降採樣操作，時間軸按照20s時間視窗擷取各個視窗中temperature列的90分位值。

SELECT device_id, region, time, percentile(temperature, 90) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 13.200000   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00 | 13.200000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

樣本2：裝置ID為F07A1260和F07A1261，擷取每個裝置的溫度的90分位值。

SELECT device_id, region, time, percentile(temperature, 90) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 1970-01-01T08:00:00+08:00 | 13.200000   |
| F07A1261  | south-cn | 1970-01-01T08:00:00+08:00 | 20.600000   |
+-----------+----------+---------------------------+-------------+

轉換函式

RATE函數

說明

計算時序資料表中Field列值的斜率。

計算公式：每個資料點的時間戳記和值分別是：(t₁,v₁),(t₂,v₂)...(t_N,v_N)。使用RATE函數後，返回N-1個資料點，每個資料點的時間戳記和值分別是：

2023-03-20_10-29-38

說明

公式中interval是指定的變動率，單位為秒。

文法

RATE(field_name, 'interval units')

說明

RATE的傳回型別為DOUBLE類型。
SQL語句中只支援RATE運算元單獨使用。

參數

參數	描述
field_name	Field列名。說明 Field列的類型不能是VARCHAR和BOOLEAN類型。
interval units	變動率。預設值為1s。單位包括s（秒）、m（分鐘）、h（小時）、d（天）。

樣本

樣本1：裝置ID為F07A1260和F07A1261，計算每個裝置溫度的秒級變動率。

SELECT device_id, region, time, rate(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 0.110000    |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | -0.260000   |
| F07A1261  | south-cn | 2021-04-22T15:51:21+08:00 | 1.100000    |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | -0.433333   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 0.500000    |
+-----------+----------+---------------------------+-------------+

樣本2：裝置ID為F07A1260和F07A1261，計算每個裝置溫度的分鐘變動率。

SELECT device_id, region, time, rate(temperature, '1m') AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 6.600000    |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | -15.600000  |
| F07A1261  | south-cn | 2021-04-22T15:51:21+08:00 | 66.000000   |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | -26.000000  |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 30.000000   |
+-----------+----------+---------------------------+-------------+

樣本3：裝置ID為F07A1260和F07A1261，先按照20s時間視窗計算temperature列的平均值，再對降採樣後的值計算分鐘變動率。

SELECT device_id, region, time, rate(avg(temperature), '1m') AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | -6.150000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 25.900000   |
+-----------+----------+---------------------------+-------------+

樣本4：SQL語句中只支援RATE運算元單獨使用，不支援以下混用方式。

錯誤樣本1：

SELECT region, time, rate(temperature), rate(humidity) FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

ERROR 9001 (0A000): UNSUPPORTED: latest/rate/delta must be the only aggregator in a single SQL statement

錯誤樣本2：

SELECT region, time, rate(temperature), latest(humidity) FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

ERROR 9001 (0A000): UNSUPPORTED: latest/rate/delta must be the only aggregator in a single SQL statement

DELTA函數

說明

計算時序資料表中Field列的值與前一行對應值的差。

計算公式：每個資料點的時間戳記和值分別是：(t₁,v₁),(t₂,v₂)...(t_N,v_N)。使用DELTA函數後，返回N-1個資料點，每個資料點的時間戳記和值分別是：(t₂,v₂-v₁),(t₃,v₃-v₂)...(t_N,v_N-,v_N-1)。

文法

DELTA(field_name)

說明

DELTA的傳回型別為DOUBLE類型。
SQL語句中只支援DELTA運算元單獨使用。

參數

參數	描述
field_name	Field列名。說明 Field列的類型不能是VARCHAR和BOOLEAN類型。

樣本

樣本1：裝置ID為F07A1260和F07A1261，計算每個裝置溫度與前一行差值。

SELECT device_id, region, time, delta(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 1.100000    |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | -2.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:21+08:00 | 1.100000    |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | -2.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 10.000000   |
+-----------+----------+---------------------------+-------------+

樣本2：裝置ID為F07A1260和F07A1261，先按照20s時間視窗計算temperature列的平均值，再對降採樣後的值計算差值。

SELECT device_id, region, time, delta(avg(temperature)) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 20s;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | -2.050000   |
| F07A1261  | south-cn | 2021-04-22T15:51:40+08:00 | 8.633333    |
+-----------+----------+---------------------------+-------------+

樣本3：SQL語句中只支援DELTA運算元單獨使用，不支援以下混用方式。

錯誤樣本：

錯誤樣本：SELECT region, time, delta(temperature), rate(humidity) FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

ERROR 9001 (0A000): UNSUPPORTED: latest/rate/delta must be the only aggregator in a single SQL statement

LATEST函數

說明

擷取時序資料表中Field列的最新的N條資料。

文法

LATEST(field_name, N)

說明

LATEST的傳回型別與Field列的類型相同。
SQL語句中只支援LATEST運算元單獨使用。

參數

參數	描述
field_name	Field列名。
N	整型，用於指定查詢最新的N條資料。預設值為1，表示擷取最新的一條資料。

樣本

樣本1：查詢裝置F07A1260和F07A1261最新的溫度值。

SELECT device_id, region, time, latest(temperature) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 20.600000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
+-----------+----------+---------------------------+-------------+

樣本2：查詢裝置F07A1260和F07A1261最新的兩條溫度值。

SELECT device_id, region, time, latest(temperature, 2) AS temperature FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+
| device_id |  region  |           time            | temperature |
+-----------+----------+---------------------------+-------------+
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00 | 10.600000   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00 | 20.600000   |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 13.200000   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00 | 10.600000   |
+-----------+----------+---------------------------+-------------+

樣本3：SQL語句中只支援LATEST運算元單獨使用，不支援以下混用方式。

錯誤樣本：

SELECT region, time, latest(temperature), rate(humidity) FROM sensor WHERE time <= '2021-04-22T15:51:47+08:00' and time >= '2021-04-22T15:33:00+08:00' sample by 0;

返回結果如下：

ERROR 9001 (0A000): UNSUPPORTED: latest/rate/delta must be the only aggregator in a single SQL statement

ANOMALY_DETECT函數（時序異常檢測）

說明

對時序資料表中Field列進行時序異常檢測。

文法

ANOMALY_DETECT(field_name, [algo_name | model_name], options)

說明

ANOMALY_DETECT的傳回型別為BOOLEAN類型。

參數

參數	描述
field_name	Field列名。說明 Field列的類型不能是VARCHAR和BOOLEAN類型。
algo_name	異常檢測演算法名稱。支援阿里達摩院自研線上異常檢測演算法。 esd：適用於尖刺型異常（例如監控曲線出現尖刺的現象），對於資料點中有少量顯著離群點的情況比較適用。 nsigma：原理簡單，便於分析異常原因。 ttest：用於識別一個時間視窗內時序指標是否存在因均值變化而發生的異常。 istl-esd：適用於帶周期性訊號的資料。說明 algo_identifer參數適用於未開通資料庫內機器學習功能，但有使用時序異常檢測需求的情境。
model_name	模型名稱。說明 model_name的類型為VARCHAR類型。 model_name參數僅適用於開通資料庫內機器學習功能後，使用時序異常檢測的情境。開通資料庫內機器學習的方法，請參見開通方式。
options	調整異常檢測演算法的檢測效果。選擇性參數。格式為：`key1=value1[, key2=value2]`。詳細說明，請參見時序異常檢測。

樣本

樣本1：對時序資料表sensor中指定時間範圍的溫度使用esd演算法進行時序異常檢測。

SELECT device_id, region, time, anomaly_detect(temperature, 'esd') AS detect_result FROM sensor WHERE time >= '2022-01-01T00:00:00+08:00' and time < '2022-01-01T00:01:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | detect_result |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 | true          |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:40+08:00 | true          |
| F07A1261  | south-cn | 2022-01-01T00:00:00+08:00 | false         |
| F07A1261  | south-cn | 2022-01-01T00:00:20+08:00 | false         |
| F07A1261  | south-cn | 2022-01-01T00:00:40+08:00 | false         |
+-----------+----------+---------------------------+---------------+

樣本2：對時序資料表sensor中F07A1260裝置指定時間範圍的溫度使用esd演算法進行時序異常檢測。

SELECT device_id, region, time, anomaly_detect(temperature, 'esd') AS detect_result FROM sensor WHERE device_id in ('F07A1260') and time >= '2022-01-01T00:00:00+08:00' and time < '2022-01-01T00:01:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | detect_result |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 | true          |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:40+08:00 | true          |
+-----------+----------+---------------------------+---------------+

樣本3：對時序資料表sensor中F07A1260裝置指定時間範圍的溫度使用esd演算法進行時序異常檢測，並自訂異常檢測演算法的參數（異常檢測演算法參考的時間視窗長度為30，異常檢測時值小於90%分位元的點被過濾）。

SELECT device_id, region, time, anomaly_detect(temperature, 'esd', 'lenHistoryWindow=30,maxAnomalyRatio=0.1') AS detect_result FROM sensor where device_id in ('F07A1260') and time >= '2022-01-01T00:00:00+08:00' and time < '2022-01-01T00:01:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | detect_result |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:40+08:00 | true          |
+-----------+----------+---------------------------+---------------+

樣本4：使用在資料庫內機器學習功能中建立的時序異常檢測模型，對sensor表中指定時間範圍的溫度進行時序異常檢測。

SELECT device_id, region, `time`, raw(temperature) as temperature, anomaly_detect(temperature, ad_model) AS detect_result FROM sensor WHERE time >= '2022-01-01T00:00:00+08:00' and time < '2022-01-01T00:01:00+08:00' sample by 0;

返回結果如下：

+-----------+----------+---------------------------+-------------+---------------+
| device_id |  region  |           time            | temperature | detect_result |
+-----------+----------+---------------------------+-------------+---------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 | 59.100000   | true          |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 | 13.200000   | false         |
| F07A1260  | north-cn | 2022-01-01T00:00:40+08:00 | 64.600000   | true          |
| F07A1261  | south-cn | 2022-01-01T00:00:00+08:00 | 12.100000   | false         |
| F07A1261  | south-cn | 2022-01-01T00:00:20+08:00 | 13.200000   | false         |
| F07A1261  | south-cn | 2022-01-01T00:00:40+08:00 | 10.600000   | false         |
+-----------+----------+---------------------------+-------------+---------------+

BINS函數（特徵分箱）

說明

對時序資料表中Field列進行特徵分箱，特徵分箱（也稱為離散分箱或者離散分段）是一種資料預先處理技術。

文法

BINS(field_name, options)

說明

BINS的傳回型別為VARCHAR類型。

參數

參數	是否必選	描述
field_name	是	Field列名。說明 Field列的類型不能是VARCHAR和BOOLEAN類型。
options	否	指定特徵分箱策略和輸出方式。格式為：`key1=value1[, key2=value2]`。

樣本

樣本1：對時序資料表sensor中指定時間範圍的溫度進行特徵分箱。

SELECT device_id, region, time, bins(temperature) AS temperature_bin FROM sensor WHERE time >= '2021-04-22 00:00:00' AND time < '2022-04-23 00:01:00' sample by 0;

返回結果如下：

+-----------+----------+--------------------------------+--------------------+
| device_id |  region  |           time                 |  temperature_bin   |
+-----------+----------+--------------------------------+--------------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00      |    [10.60,12.10)   |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00      |    [12.10,13.20]   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00      |    [10.60,10.60)   |
| F07A1261  | south-cn | 2021-04-22T15:51:20+08:00      |    [10.60,12.10)   |
| F07A1261  | south-cn | 2021-04-22T15:51:21+08:00      |    [12.10,13.20)   |
| F07A1261  | south-cn | 2021-04-22T15:51:27+08:00      |    [10.60,10.60)   |
| F07A1261  | south-cn | 2021-04-22T15:51:47+08:00      |    [13.20,20.60]   |
+-----------+-----------+-------------------------------+--------------------+

樣本2：對時序資料表sensor中F07A1260裝置指定時間範圍的溫度進行特徵分箱。

SELECT device_id, region, time, bins(temperature) AS temperature_bin FROM sensor WHERE device_id in ('F07A1260') AND time >= '2021-04-22 00:00:00' AND time < '2022-04-23 00:01:00' sample by 0;

返回結果如下：

+-----------+----------+--------------------------------+--------------------+
| device_id |  region  |           time                 |  temperature_bin   |
+-----------+----------+--------------------------------+--------------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00      |    [10.60,12.10)   |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00      |    [12.10,13.20]   |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00      |    [10.60,10.60)   |
+-----------+----------+--------------------------------+--------------------+

樣本3：對時序資料表sensor中F07A1260裝置指定時間範圍的溫度進行特徵分箱，並自訂分箱演算法的參數（產生分箱數為2的等寬分箱，結果以分箱區間的順序編碼形式輸出）。

SELECT device_id, region, time, bins(temperature, 'n_bins=2, output_type=ordinal, strategy=uniform') AS temperature_bin FROM sensor WHERE device_id in ('F07A1260') AND time >= '2021-04-22 00:00:00' AND time < '2022-04-23 00:01:00' sample by 0;

返回結果如下：

+-----------+----------+--------------------------------+--------------------+
| device_id |  region  |           time                 |  temperature_bin   |
+-----------+----------+--------------------------------+--------------------+
| F07A1260  | north-cn | 2021-04-22T15:33:00+08:00      |  1                 |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00      |  1                 |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00      |  0                 |
+-----------+----------+--------------------------------+--------------------+