Kueri downsampling - Lindorm

Kueri downsampling adalah kueri agregat berdasarkan dimensi waktu yang umum digunakan untuk mengurangi laju sampel data deret waktu.

Engine dan versi

Kueri downsampling hanya didukung oleh LindormTSDB. Versi engine database harus 3.4.15 atau lebih baru.

Catatan

Untuk informasi selengkapnya tentang cara melihat dan melakukan upgrade versi LindormTSDB instans Lindorm, lihat Catatan rilis LindormTSDB dan Upgrade versi mesin minor instans Lindorm.

Sintaksis

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

Daftar fungsi agregat yang didukung

Klausa SAMPLE BY melakukan operasi downsampling pada setiap deret waktu secara individual. Untuk informasi selengkapnya tentang deret waktu, lihat Model data.

Tabel berikut mencantumkan fungsi yang didukung oleh SAMPLE BY.

Function	Description
SUM	Menghitung jumlah nilai dalam setiap jendela waktu yang ditentukan.
AVG	Menghitung rata-rata nilai dalam setiap jendela waktu yang ditentukan.
COUNT	Menghitung jumlah nilai dalam setiap jendela waktu yang ditentukan.
MIN	Menemukan nilai minimum dalam setiap jendela waktu yang ditentukan.
MAX	Menemukan nilai maksimum dalam setiap jendela waktu yang ditentukan.
FIRST	Menemukan nilai pertama dalam setiap jendela waktu yang ditentukan.
LAST	Menemukan nilai terakhir dalam setiap jendela waktu yang ditentukan.
PERCENTILE	Menghitung persentil dalam setiap jendela waktu yang ditentukan.
LATEST	Menemukan nilai terbaru dalam keseluruhan rentang waktu.
RATE	Menghitung laju perubahan dari nilai pada baris sebelumnya.
DELTA	Menghitung selisih dari nilai pada baris sebelumnya.

Kueri downsampling hanya mendukung penyaringan berdasarkan kolom tag, bukan kolom field. Jika Anda menentukan kondisi filter untuk kolom field, kondisi tersebut akan diabaikan. Hal ini dapat menyebabkan data yang dikembalikan berbeda dari yang diharapkan. Untuk menyaring berdasarkan kolom field, Anda harus memfilter data di luar kueri downsampling.

Ambil fungsi LATEST latest(field, n) sebagai contoh, di mana temperature adalah kolom field:

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;

Contoh

Fungsi downsampling tidak diperlukan untuk kolom tag yang ditentukan dalam kueri SELECT. Namun, fungsi downsampling harus ditentukan untuk semua kolom field lainnya.

Contoh berikut menggunakan tabel sampel bernama sensor yang memiliki struktur dan data sebagai berikut:

-- Buat tabel sampel 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)
);

-- Masukkan data
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);

Contoh downsampling dan subkueri

Catatan

Kueri downsampling tidak mendukung subkueri bersarang, tetapi dapat disarangkan sebagai subkueri dalam kueri lain.

Contoh 1: Hitung jumlah untuk setiap deret waktu dengan mengagregasi data ke dalam jendela waktu 8 jam menggunakan snapping UTC default.

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

Hasil berikut dikembalikan:

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

Contoh 2: Hitung jumlah untuk setiap deret waktu dengan mengagregasi data ke dalam jendela waktu 8 jam. Kueri ini menggunakan snapping UTC default dan offset 3 jam untuk awal jendela.

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

Hasil berikut dikembalikan:

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

Contoh 3: Hitung jumlah untuk setiap deret waktu dengan mengagregasi data ke dalam jendela waktu 24 jam. Kueri ini melakukan snapping ke pukul 00:00 waktu lokal (misalnya, di zona waktu UTC+8) menggunakan offset 16 jam dengan snapping UTC default.

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

Hasil berikut dikembalikan:

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

Contoh 4: Anda tidak dapat menggunakan `SAMPLE BY` bersamaan dengan `GROUP BY`, `LIMIT OFFSET`, atau `ORDER BY`. Untuk menggunakan klausa-klausa tersebut, Anda harus menyarangkan kueri `SAMPLE BY` sebagai subkueri.

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;

Hasil berikut dikembalikan:

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

Contoh 5: Gunakan `LIMIT OFFSET` untuk membatasi jumlah hasil.

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;

Hasil berikut dikembalikan:

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

Contoh interpolasi jendela downsampling

Contoh 1: Interpolasi dengan nilai statis.

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;

Hasil berikut dikembalikan:

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

Contoh 2: Interpolasi dengan nilai berikutnya.

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;

Hasil berikut dikembalikan:

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

Contoh transformasi setelah downsampling

Contoh 1: Pertama, lakukan downsampling data dengan menghitung nilai rata-rata dalam jendela 2 jam. Kemudian, hitung rate perubahan untuk data yang telah didownsampling.

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

Hasil berikut dikembalikan:

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

Contoh 2: Pertama, lakukan downsampling data ke dalam jendela 2 jam. Kemudian, hitung delta (selisih) untuk data yang telah didownsampling.

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

Hasil berikut dikembalikan:

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

Interpolasi

Downsampling membagi setiap deret waktu menjadi jendela waktu tertentu dan melakukan operasi pada data dalam setiap jendela. Setelah downsampling, jika suatu jendela waktu tidak berisi titik data, Anda dapat menggunakan interpolasi untuk mengisi titik waktu tersebut dengan nilai yang ditentukan. Misalnya, suatu deret waktu memiliki titik data pada timestamp t+0, t+20, dan t+30. Jika Anda melakukan downsampling dengan jendela waktu 10 unit, jendela yang dimulai pada t+10 kosong. Tanpa interpolasi, hanya tiga titik yang dikembalikan. Jika Anda menggunakan interpolasi untuk mengisi jendela kosong dengan nilai 1, titik data baru dengan nilai 1 ditambahkan pada timestamp t+10.

Tabel berikut menjelaskan fungsi interpolasi.

Fill Policy	Fill value
none	Perilaku default. Tidak mengisi nilai.
zero	Mengisi dengan 0.
linear	Mengisi dengan nilai hasil interpolasi linear.
previous	Nilai sebelumnya.
near	Nilai yang berdekatan.
after	Nilai berikutnya.
fixed	Mengisi dengan nilai statis yang ditentukan.