ダウンサンプリングクエリ - Lindorm - Alibaba Cloud ドキュメントセンター

ダウンサンプリングクエリは、時系列データのサンプルレートを削減するためによく使用される、時間ディメンション上の集計クエリです。

エンジンとバージョン

ダウンサンプリングクエリは LindormTSDB でのみサポートされています。データベースエンジンのバージョンは 3.4.15 以降である必要があります。

説明

Lindorm インスタンスの LindormTSDB バージョンの確認方法とアップグレード方法の詳細については、「LindormTSDB のリリースノート」および「Lindorm インスタンスのマイナーエンジンバージョンのアップグレード」をご参照ください。

構文

select_sample_by_statement ::=  SELECT ( select_clause | '*' )
                                FROM table_identifier
                                WHERE where_clause
                                SAMPLE BY time_interval [ OFFSET offset_interval ] [ FILL fill_option ]
select_clause              ::=  selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector                   ::=  tag_identifier | time | function_identifier '(' field_identifier [ ',' function_args ] ')'
where_clause               ::=  relation ( AND relation )* (OR relation)*
relation                   ::=  ( field_identifier | tag_identifier ) operator term
operator                   ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | CONTAINS | CONTAINS KEY
time_interval              ::= interval units | 0
offset_interval            ::= interval units

サポートされる集計関数のリスト

SAMPLE BY 句は、個々の時系列に対してダウンサンプリング操作を実行します。時系列の詳細については、「データモデル」をご参照ください。

次の表に、SAMPLE BY でサポートされている関数を示します。

関数	説明
SUM	指定された各タイムウィンドウ内の値の合計を計算します。
AVG	指定された各タイムウィンドウ内の値の平均を計算します。
COUNT	指定された各タイムウィンドウ内の値の数をカウントします。
MIN	指定された各タイムウィンドウ内の最小値を見つけます。
MAX	指定された各タイムウィンドウ内の最大値を見つけます。
FIRST	指定された各タイムウィンドウ内の最初の値を見つけます。
LAST	指定された各タイムウィンドウ内の最後の値を見つけます。
PERCENTILE	指定された各タイムウィンドウ内のパーセンタイルを計算します。
LATEST	全時間範囲内の最新の値を見つけます。
RATE	前の行の値からの変化率を計算します。
DELTA	前の行の値との差を計算します。

ダウンサンプリングクエリは、タグ列によるフィルターのみをサポートし、フィールド列によるフィルターはサポートしていません。フィールド列にフィルター条件を指定した場合、その条件は無視されます。これにより、返されるデータが期待と異なる場合があります。フィールド列でフィルターするには、ダウンサンプリングクエリの外部でデータをフィルターする必要があります。

LATEST 関数 latest(field, n) を例にとると、temperature はフィールド列です。

SELECT device_id,region,time,temperature FROM (SELECT device_id,region,time,latest(temperature, 2) AS temperature FROM sensor WHERE device_id in ('F07A1260', 'F07A1261') SAMPLE BY 0) WHERE temperature > 45.0;

例

SELECT クエリで指定されたタグ列にはダウンサンプリング関数は必要ありません。ただし、他のすべてのフィールド列にはダウンサンプリング関数を指定する必要があります。

以下の例では、sensor という名前のサンプルテーブルを使用します。このテーブルは次の構造とデータを持っています。

-- サンプルテーブル sensor を作成
CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
);

-- データを挿入
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 09:00:00',0,9);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 12:01:00',1,45);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 14:03:00',2,46);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 20:00:00',10,47);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-02-10 12:00:30',3,40);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-03-01 12:01:00',4,41);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-03-08 12:08:00',5,42);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-05-01 13:00:00',6,43);

ダウンサンプリングとサブクエリの例

説明

ダウンサンプリングクエリはネストされたサブクエリをサポートしていませんが、他のクエリ内のサブクエリとしてネストすることはできます。

例 1：デフォルトの UTC スナップを使用して、データを 8 時間のタイムウィンドウに集約し、各時系列のカウントを計算します。

SELECT device_id,region,time,count(humidity) AS count_humidity FROM sensor WHERE device_id='F07A1260' sample by 8h;

次の結果が返されます。

+-----------+----------+---------------------------+----------------+
| device_id |  region  |           time            | count_humidity |
+-----------+----------+---------------------------+----------------+
| F07A1260  | north-cn | 2021-01-01T08:00:00+08:00 | 3              |
| F07A1260  | north-cn | 2021-01-01T16:00:00+08:00 | 1              |
+-----------+----------+---------------------------+----------------+

例 2：データを 8 時間のタイムウィンドウに集約して、各時系列のカウントを計算します。このクエリは、デフォルトの UTC スナップとウィンドウの開始に対して 3 時間のオフセットを使用します。

SELECT device_id,region,time,count(humidity) AS count_humidity FROM sensor WHERE device_id='F07A1260' sample by 8h offset 3h;

次の結果が返されます。

+-----------+----------+---------------------------+----------------+
| device_id |  region  |           time            | count_humidity |
+-----------+----------+---------------------------+----------------+
| F07A1260  | north-cn | 2021-01-01T03:00:00+08:00 | 1              |
| F07A1260  | north-cn | 2021-01-01T11:00:00+08:00 | 2              |
| F07A1260  | north-cn | 2021-01-01T19:00:00+08:00 | 1              |
+-----------+----------+---------------------------+----------------+

例 3：データを 24 時間のタイムウィンドウに集約して、各時系列のカウントを計算します。このクエリは、デフォルトの UTC スナップで 16 時間のオフセットを使用して、ローカルタイム (例：UTC+08:00 タイムゾーン) の 00:00 にスナップします。

SELECT device_id,region,time,count(humidity) AS count_humidity FROM sensor WHERE device_id='F07A1260' sample by 24h offset 16h

次の結果が返されます。

+-----------+----------+---------------------------+----------------+
| device_id | region   | time                      | count_humidity |
+-----------+----------+---------------------------+----------------+
| F07A1260  | north-cn | 2021-01-01T00:00:00+08:00 | 4              |
+-----------+----------+---------------------------+----------------+

例 4：`SAMPLE BY` は `GROUP BY`、`LIMIT OFFSET`、または `ORDER BY` と一緒に使用することはできません。これらの句を使用するには、`SAMPLE BY` クエリをサブクエリとしてネストする必要があります。

SELECT device_id, max(avg_humidity) AS max_humidity  FROM (SELECT device_id,region,time,avg(humidity) AS avg_humidity FROM sensor sample by 8h) group by device_id;

次の結果が返されます。

+-----------+--------------+
| device_id | max_humidity |
+-----------+--------------+
| F07A1261  | 43.000000    |
| F07A1260  | 47.000000    |
+-----------+--------------+

例 5：`LIMIT OFFSET` を使用して結果の数を制限します。

SELECT device_id,region, avg_humidity FROM (select device_id,region,time,avg(humidity) AS avg_humidity FROM sensor sample by 8h) limit 1 offset 1;

次の結果が返されます。

+-----------+----------+--------------+
| device_id |  region  | avg_humidity |
+-----------+----------+--------------+
| F07A1261  | north-cn | 40.000000    |
+-----------+----------+--------------+

ダウンサンプリングウィンドウの補間の例

例 1：静的な値で補間します。

SELECT * from (select device_id,region,time, avg(humidity) AS humidity FROM sensor WHERE device_id='F07A1260' sample by 2h fill 1) order by device_id;

次の結果が返されます。

+-----------+----------+---------------------------+-----------+
| device_id |  region  |           time            | humidity  |
+-----------+----------+---------------------------+-----------+
| F07A1260  | north-cn | 2021-01-01T08:00:00+08:00 | 9.000000  |
| F07A1260  | north-cn | 2021-01-01T10:00:00+08:00 | 1.000000  |
| F07A1260  | north-cn | 2021-01-01T12:00:00+08:00 | 45.000000 |
| F07A1260  | north-cn | 2021-01-01T14:00:00+08:00 | 46.000000 |
| F07A1260  | north-cn | 2021-01-01T16:00:00+08:00 | 1.000000  |
| F07A1260  | north-cn | 2021-01-01T18:00:00+08:00 | 1.000000  |
| F07A1260  | north-cn | 2021-01-01T20:00:00+08:00 | 47.000000 |
+-----------+----------+---------------------------+-----------+

例 2：次の値で補間します。

SELECT * from (select device_id,region,time,avg(humidity) AS humidity FROM sensor WHERE device_id='F07A1260' sample by 2h fill after) order by device_id;

次の結果が返されます。

+-----------+----------+---------------------------+-----------+
| device_id |  region  |           time            | humidity  |
+-----------+----------+---------------------------+-----------+
| F07A1260  | north-cn | 2021-01-01T08:00:00+08:00 | 9.000000  |
| F07A1260  | north-cn | 2021-01-01T10:00:00+08:00 | 45.000000 |
| F07A1260  | north-cn | 2021-01-01T12:00:00+08:00 | 45.000000 |
| F07A1260  | north-cn | 2021-01-01T14:00:00+08:00 | 46.000000 |
| F07A1260  | north-cn | 2021-01-01T16:00:00+08:00 | 47.000000 |
| F07A1260  | north-cn | 2021-01-01T18:00:00+08:00 | 47.000000 |
| F07A1260  | north-cn | 2021-01-01T20:00:00+08:00 | 47.000000 |
+-----------+----------+---------------------------+-----------+

ダウンサンプリング後の変換の例

例 1：まず、2 時間のウィンドウで平均値を計算してデータをダウンサンプリングします。次に、ダウンサンプリングされたデータの変化率 (rate) を計算します。

SELECT device_id,region,time,rate(avg(humidity)) AS rate_humidity FROM sensor WHERE device_id='F07A1260' sample by 2h;

次の結果が返されます。

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | rate_humidity |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2021-01-01T12:00:00+08:00 | 0.002500      |
| F07A1260  | north-cn | 2021-01-01T14:00:00+08:00 | 0.000139      |
| F07A1260  | north-cn | 2021-01-01T20:00:00+08:00 | 0.000046      |
+-----------+----------+---------------------------+---------------+

例 2：まず、データを 2 時間のウィンドウにダウンサンプリングします。次に、ダウンサンプリングされたデータのデルタ (差) を計算します。

SELECT device_id,region,time,delta(avg(humidity)) AS humidity FROM sensor WHERE device_id='F07A1260' sample by 2h;

次の結果が返されます。

+-----------+----------+---------------------------+-----------+
| device_id |  region  |           time            | humidity  |
+-----------+----------+---------------------------+-----------+
| F07A1260  | north-cn | 2021-01-01T12:00:00+08:00 | 36.000000 |
| F07A1260  | north-cn | 2021-01-01T14:00:00+08:00 | 1.000000  |
| F07A1260  | north-cn | 2021-01-01T20:00:00+08:00 | 1.000000  |
+-----------+----------+---------------------------+-----------+

補間

ダウンサンプリングは、各時系列を指定されたタイムウィンドウに分割し、各ウィンドウ内のデータに対して操作を実行します。ダウンサンプリング後、タイムウィンドウにデータポイントが含まれていない場合、補間を使用してその時点を特定のフィル値で埋めることができます。たとえば、ある時系列にタイムスタンプ t+0、t+20、t+30 のデータポイントがあるとします。10 単位のタイムウィンドウでダウンサンプリングすると、t+10 で始まるウィンドウは空になります。補間を使用しない場合、3 つのポイントのみが返されます。補間を使用して空のウィンドウを値 1 で埋めると、タイムスタンプ t+10 に値 1 の新しいデータポイントが追加されます。

次の表に、補間関数を示します。

フィルポリシー	フィル値
none	デフォルトの動作です。値を埋めません。
zero	0 で埋めます。
linear	線形補間された値で埋めます。
previous	前の値。
near	隣接値。
after	次の値。
fixed	指定された静的な値で埋めます。