時系列 SQL の共通関数 - Lindorm - Alibaba Cloud ドキュメントセンター

LindormTSDB SQL は、集計、選択、変換、AI を活用した分析など、時系列データのための一連の組み込み関数をサポートしています。このページでは、各関数の構文、パラメーター、および実行可能な例を用いた使用法について説明します。

適用エンジンとバージョン

これらの関数は LindormTSDB にのみ適用されます。
LindormTSDB 3.4.7 以降が必要です。アップグレードの手順については、「LindormTSDB のリリースノート」および「Lindorm インスタンスのマイナーエンジンバージョンのアップグレード」をご参照ください。

関数リスト

カテゴリ	関数	説明
タイムスタンプ	CURRENT_TIMESTAMP	現在のシステム時刻を返します。
集計	SUM	フィールド列の値の合計を計算します。
集計	AVG	フィールド列の値の平均を計算します。
集計	COUNT	フィールド列の値の数をカウントします。
選択	MIN	フィールド列の最小値を返します。
選択	MAX	フィールド列の最大値を返します。
選択	FIRST	フィールド列の最も古い値を返します。
選択	LAST	フィールド列の最新の値を返します。
選択	PERCENTILE	フィールド列の指定されたパーセンタイルにおける値を返します。
変換	RATE	フィールド列の連続する値の間の変化率 (勾配) を計算します。
変換	DELTA	フィールド列の各値とその直前の値との差を計算します。
変換	LATEST	フィールド列の最新 N 個の値を返します。
AI	ANOMALY_DETECT	フィールド列の異常を検出します。
AI	BINS	フィールド列の値をビンにグループ化します (特徴量ビニング)。

データ準備

このページのすべての例では、次のスキーマを持つ 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 |
+-----------+----------+---------------------------+-------------+-----------+

このテーブルには 2 つの時系列があります。

時系列 1： device_id=F07A1260, region=north-cn、temperature および humidity フィールドを持つ。
時系列 2： device_id=F07A1261, region=south-cn、temperature および humidity フィールドを持つ。

時系列の概念に関する詳細については、「用語」をご参照ください。

クエリパターン

このページの例では、LindormTSDB 固有の SQL 構文である 2 つの SAMPLE BY パターンを使用します。

SAMPLE BY <interval> — 行を固定のタイムウィンドウにグループ化することでデータをダウンサンプリングします。例えば、SAMPLE BY 20s は行を 20 秒間隔にグループ化し、各間隔に集計関数を適用します。
SAMPLE BY 0 — ダウンサンプリングせずに、各時系列の全時間範囲にわたって集計します。

タイムスタンプ関数

CURRENT_TIMESTAMP

Lindorm インスタンスが実行されているサーバーの現在のシステム時刻を返します。

構文

CURRENT_TIMESTAMP

重要

CURRENT_TIMESTAMP はパラメーターを取りません。
返されるタイムスタンプはミリ秒単位で正確です。例： 2023-04-23T21:13:15.819+08:00。
本番環境の書き込みにタイムスタンプを生成するために CURRENT_TIMESTAMP を使用しないでください。テストデータの生成にのみ使用してください。

例

現在のシステム時刻をタイムスタンプとして使用して、sensor テーブルに行を挿入します。

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

集計関数

SUM

フィールド列の値の合計を計算します。DOUBLE または BIGINT を返します。

構文

SUM(field_name)

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。列の型は VARCHAR または BOOLEAN にはできません。

例

例 1： 20 秒間隔でダウンサンプリングし、デバイスごと、間隔ごとに 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：全時間範囲にわたって、デバイスごとの 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 0;

結果：

+-----------+----------+---------------------------+-------------+
| 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   |
+-----------+----------+---------------------------+-------------+

例 3：すべてのデバイスにわたる temperature の合計を計算します。

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

フィールド列の値の平均を計算します。DOUBLE を返します。

構文

AVG(field_name)

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。列の型は VARCHAR または BOOLEAN にはできません。

例

例 1： 20 秒間隔でダウンサンプリングし、デバイスごと、間隔ごとに 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：全時間範囲にわたって、デバイスごとの 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 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：すべてのデバイスにわたる temperature の平均を計算します。

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

フィールド列の値の数をカウントします。BIGINT を返します。

構文

COUNT(field_name)

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。

例

例 1： 20 秒間隔でダウンサンプリングし、デバイスごと、間隔ごとに 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：全時間範囲にわたって、デバイスごとの 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 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：すべてのデバイスにわたる temperature の値の合計数をカウントします。

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           |
+-------------+

選択関数

選択関数は、計算されたスカラーではなく、実際のデータ行を返します。SUM や AVG のような集計関数とは異なり、選択関数は元の行の元の値 (場合によっては関連するタイムスタンプも) を保持します。

MIN

フィールド列の最小値を返します。DOUBLE または BIGINT を返します。

構文

MIN(field_name)

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。列の型は VARCHAR または BOOLEAN にはできません。

例

例 1： 20 秒間隔でダウンサンプリングし、デバイスごと、間隔ごとに 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：全時間範囲にわたって、デバイスごとの 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 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：すべてのデバイスにわたる temperature の最小値を見つけます。

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

フィールド列の最大値を返します。DOUBLE または BIGINT を返します。

構文

MAX(field_name)

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。列の型は VARCHAR または BOOLEAN にはできません。

例

例 1： 20 秒間隔でダウンサンプリングし、デバイスごと、間隔ごとに 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：全時間範囲にわたって、デバイスごとの 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 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   |
+-----------+----------+---------------------------+-------------+

例 3：すべてのデバイスにわたる temperature の最大値を見つけます。

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

クエリウィンドウ内のフィールド列の最も古い値を返します。戻り値の型はフィールド列の型と一致します。

構文

FIRST(field_name)

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。

例

例 1： 20 秒間隔でダウンサンプリングし、デバイスごと、間隔ごとに最初の temperature の値を取得します。

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：全時間範囲にわたって、デバイスごとの最初の temperature の値を取得します。

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

クエリウィンドウ内のフィールド列の最新の値を返します。戻り値の型はフィールド列の型と一致します。

構文

LAST(field_name)

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。

例

例 1： 20 秒間隔でダウンサンプリングし、デバイスごと、間隔ごとに最後の temperature の値を取得します。

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：全時間範囲にわたって、デバイスごとの最後の temperature の値を取得します。

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

フィールド列の指定されたパーセンタイルにおける値を返します。DOUBLE を返します。

構文

PERCENTILE(field_name, P)

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。列の型は VARCHAR または BOOLEAN にはできません。
`P`	パーセンタイルを指定する整数または浮動小数点数。有効範囲： 0 から 100。デフォルト： 50。

例

例 1： 20 秒間隔でダウンサンプリングし、デバイスごと、間隔ごとに 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：全時間範囲にわたって、デバイスごとの 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 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

フィールド列の連続する値の間の変化率 (勾配) を計算します。DOUBLE を返します。

データポイント (t1,v1), (t2,v2) ... (tN,vN) が与えられた場合、RATE は N-1 個の出力ポイントを生成します。各出力のタイムスタンプと値は次のように計算されます。

ここで、interval は指定された時間間隔 (秒) です。

重要

RATE は SQL ステートメント内で唯一の集計関数でなければなりません。同じクエリ内で AVG、DELTA、LATEST などの他の集計関数と組み合わせることはできません。

構文

RATE(field_name, 'interval units')

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。列の型は VARCHAR または BOOLEAN にはできません。
`interval units`	勾配計算の時間間隔。デフォルト： `1s`。有効な単位： `s` (秒)、`m` (分)、`h` (時間)、`d` (日)。

例

例 1：各デバイスの temperature の秒間変化率を計算します。

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：各デバイスの temperature の分間変化率を計算します。

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： 20 秒間隔でダウンサンプリングし、間隔ごとの平均 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： 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

-- エラー： RATE と LATEST の混在
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

フィールド列の各値とその直前の値との差を計算します。DOUBLE を返します。

データポイント (t1,v1), (t2,v2) ... (tN,vN) が与えられた場合、DELTA は N-1 個の出力ポイント (t2, v2−v1), (t3, v3−v2) ... (tN, vN−vN-1) を生成します。

重要

DELTA は SQL ステートメント内で唯一の集計関数でなければなりません。同じクエリ内で AVG、RATE、LATEST などの他の集計関数と組み合わせることはできません。

構文

DELTA(field_name)

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。列の型は VARCHAR または BOOLEAN にはできません。

例

例 1：各デバイスの temperature の行ごとの差を計算します。

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： 20 秒間隔でダウンサンプリングし、間隔ごとの平均 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： DELTA は他の集計関数と組み合わせることができないため、以下のクエリは失敗します。

-- エラー： DELTA と RATE の混在
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

フィールド列の最新 N 個の値をタイムスタンプ順に返します。戻り値の型はフィールド列の型と一致します。

LATEST は、SQL ステートメント内で唯一のアグリゲーターである必要があります。

構文

LATEST(field_name, N)

パラメーター

パラメーター	説明
`field_name`	フィールド列の名前。
`N`	返す最新の値の数を指定する整数。デフォルト： 1。

例

例 1：各デバイスの最新の temperature の値を取得します。

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：各デバイスの最新 2 つの temperature の値を取得します。

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： LATEST は他の集計関数と組み合わせることができないため、以下のクエリは失敗します。

-- エラー： LATEST と RATE の混在
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

AI 関数

FORECAST

トレーニング済みモデルを使用して、時系列テーブルの将来のフィールド値を予測します。DOUBLE を返します。

前提条件

FORECAST は Lindorm AI に依存します。この関数を使用する前に、ご利用のインスタンスで Lindorm AI を有効化してください。

構文

FORECAST(field_name, model_name, options)

パラメーター

パラメーター	必須	説明
`field_name`	はい	フィールド列の名前。列の型は VARCHAR または BOOLEAN にはできません。
`model_name`	はい	トレーニング済みモデルの名前。VARCHAR 型である必要があります。
`options`	いいえ	`key1=value1[, key2=value2]` 形式の予測オプション。以下のオプションをご参照ください。

オプション

オプション	型	デフォルト	説明
`batch_size`	INTEGER	トレーニング時の時系列の数	推論バッチごとの時系列の数。キャッシュされた時系列がこの数に達すると、バッチ予測がトリガーされます。
`step`	INTEGER	トレーニング中に設定された `PREDICTION_LENGTH` の値	予測結果が出力されるステップ長。
`quantile_output`	DOUBLE	0.5	出力予測結果のパーセンタイル。データベース内機械学習は、データポイントごとに予測値の配列を提供します。`quantile_output` はその配列から 1 つの値を選択します。これは使用するアルゴリズムに基づいて設定します。DeepAR の場合は 0 から 1 の間の値を使用し、TFT の場合は 0.1、0.5、または 0.9 を使用します。

例

例 1：指定された時間範囲内の temperature の値を予測します。

SELECT device_id, region, `time`, forecast(temperature, forecast_model) AS forecast_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            |  forecast_result |
+-----------+----------+---------------------------+------------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 |  12.40307807     |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 |  11.36715841     |
| F07A1260  | north-cn | 2022-01-01T00:00:40+08:00 |  10.12969923     |
| F07A1261  | south-cn | 2022-01-01T00:00:00+08:00 |  26.51369649     |
| F07A1261  | south-cn | 2022-01-01T00:00:20+08:00 |  25.54403301     |
| F07A1261  | south-cn | 2022-01-01T00:00:40+08:00 |  24.46405267     |
+-----------+----------+---------------------------+------------------+

例 2： step=2 と quantile_output=0.9 で予測し、1 ステップおきに 90 パーセンタイルの予測を取得します。

SELECT device_id, region, `time`,
       forecast(temperature, forecast_model, 'step=2,quantile_output=0.9') AS forecast_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            |  forecast_result |
+-----------+----------+---------------------------+------------------+
| F07A1260  | north-cn | 2022-01-01T00:00:00+08:00 |  13.12353792     |
| F07A1260  | north-cn | 2022-01-01T00:00:20+08:00 |  12.14833554     |
| F07A1261  | south-cn | 2022-01-01T00:00:00+08:00 |  26.73869304     |
| F07A1261  | south-cn | 2022-01-01T00:00:20+08:00 |  24.92990853     |
+-----------+----------+---------------------------+------------------+

ANOMALY_DETECT

組み込みアルゴリズムまたはトレーニング済みモデルを使用して、フィールド列の異常を検出します。BOOLEAN を返します。

構文

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

パラメーター

パラメーター	必須	説明
`field_name`	はい	フィールド列の名前。列の型は VARCHAR または BOOLEAN にはできません。
`algo_name`	条件付き	DAMO アカデミーの組み込みアルゴリズムの名前。Lindorm AI が有効になっていない場合にこのパラメーターを使用します。サポートされている値： `esd` (モニタリング曲線のスパイク検出)、`nsigma` (単純な統計的異常分析)、`ttest` (平均値のシフトによる異常を検出)、`istl-esd` (周期的データの異常検出)。
`model_name`	条件付き	トレーニング済みの異常検出モデルの名前。VARCHAR 型である必要があります。Lindorm AI が有効になっている場合にこのパラメーターを使用します。詳細については、「Lindorm AI の有効化」をご参照ください。
`options`	いいえ	`key1=value1[, key2=value2]` 形式の検出オプション。利用可能なオプションについては、「時系列異常検知」をご参照ください。

例

例 1： ESD アルゴリズムを使用して、すべてのデバイスの temperature データにおける異常を検出します。

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： ESD を使用して、デバイス F07A1260 のみの temperature データにおける異常を検出します。

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 |  リージョン  |           時間            | 検出結果      |
+-----------+----------+---------------------------+---------------+
| 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          |
+-----------+----------+---------------------------+---------------+

例 3：カスタムオプションで ESD を使用します — lenHistoryWindow を 30 に、maxAnomalyRatio を 0.1 に設定します。

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： Lindorm AI のトレーニング済みモデルを使用して異常を検出します。

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

フィールド列の値をビンにグループ化します (特徴量ビニング)。特徴量ビニング (離散ビニングまたはバケット化とも呼ばれる) は、データの前処理技術です。VARCHAR を返します。

構文

BINS(field_name, options)

パラメーター

パラメーター	必須	説明
`field_name`	はい	フィールド列の名前。列の型は VARCHAR または BOOLEAN にはできません。
`options`	いいえ	`key1=value1, key2=value2` 形式のビニングポリシーと出力フォーマット。

例

例 1：指定された時間範囲内のすべての temperature の値をビンにグループ化します。

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：デバイス F07A1260 の temperature の値をビンにグループ化します。

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      |  1                 |
| F07A1260  | north-cn | 2021-04-22T15:33:10+08:00      |  1                 |
| F07A1260  | north-cn | 2021-04-22T15:33:20+08:00      |  0                 |
+-----------+----------+--------------------------------+--------------------+

例 3：デバイス F07A1260 の temperature の値を 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                 |
+-----------+----------+--------------------------------+--------------------+