Downsampling queries - Lindorm - Alibaba Cloud ドキュメントセンター

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

エンジンとバージョン

ダウンサンプリングクエリは、LindormTSDB 3.4.15 以降のバージョンでのみサポートされています。

説明

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

構文

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 )* 
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(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 という名前のサンプルテーブルを使用します：

-- Create the sample table 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)
);

-- Insert data into the table.
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：オフセットを指定して UTC にアライメントされたダウンサンプリングを実行します。このクエリは、各時系列を 8 時間のタイムウィンドウに集計し、各ウィンドウの開始を 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：ローカルタイムゾーンの深夜に合わせるために、UTC にアライメントされたダウンサンプリングを実行します。このクエリは、各時系列を 24 時間のタイムウィンドウに集計し、開始を 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: AVG() 関数を使用して、データを 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 時間のウィンドウにダウンサンプリングします。次に、ダウンサンプリングされた結果の `delta` を計算します。

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` にギャップが存在します。補間のためのフィルポリシーを指定すると、クエリは `t+10` で値を生成できます。

次の表に、補間に使用できるフィルポリシーを示します。

フィルポリシー	補間値
none	空のタイムウィンドウを埋めません (デフォルト)。
zero	空のタイムウィンドウを 0 で埋めます。
linear	線形補間を実行して空のタイムウィンドウを埋めます。
previous	空のタイムウィンドウを前のタイムウィンドウの値で埋めます。
near	空のタイムウィンドウを最も近いタイムウィンドウの値で埋めます。
after	空のタイムウィンドウを次のタイムウィンドウの値で埋めます。
fixed	空のタイムウィンドウを指定された固定値で埋めます。