Kueri Downsampling - Lindorm

Kueri downsampling adalah kueri agregat yang dilakukan berdasarkan interval waktu tertentu. Kueri ini digunakan untuk mengurangi laju sampel dalam skenario deret waktu.

Mesin dan versi yang berlaku

Kueri downsampling hanya didukung oleh LindormTSDB versi 3.4.15 dan versi selanjutnya.

Catatan

Untuk informasi lebih lanjut tentang cara melihat dan meningkatkan versi LindormTSDB dari instance Lindorm, lihat Catatan Rilis LindormTSDB dan Tingkatkan Versi Mesin Minor dari Instance 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

Fungsi agregat yang didukung oleh kueri downsampling

Klausa SAMPLE BY digunakan untuk melakukan operasi downsampling berdasarkan deret waktu independen. Untuk informasi lebih lanjut, lihat Model Data.

Tabel berikut mencantumkan fungsi yang didukung oleh SAMPLE BY.

Fungsi	Deskripsi
SUM	Mengembalikan jumlah nilai yang dikumpulkan dalam setiap jendela waktu.
AVG	Mengembalikan nilai rata-rata yang dihitung berdasarkan nilai-nilai yang dikumpulkan dalam setiap jendela waktu.
COUNT	Mengembalikan jumlah nilai yang dikumpulkan dalam setiap jendela waktu.
MIN	Mengembalikan nilai minimum yang dikumpulkan dalam setiap jendela waktu.
MAX	Mengembalikan nilai maksimum yang dikumpulkan dalam setiap jendela waktu.
FIRST	Mengembalikan nilai pertama yang dikumpulkan dalam setiap jendela waktu.
LAST	Mengembalikan nilai terakhir yang dikumpulkan dalam setiap jendela waktu.
PERCENTILE	Mengembalikan persentil tertentu dari nilai-nilai yang dikumpulkan dalam setiap jendela waktu.
LATEST	Mengembalikan nilai terbaru yang dikumpulkan dalam jendela waktu.
RATE	Mengembalikan tingkat perubahan nilai pada titik data saat ini dibandingkan dengan nilai titik data sebelumnya.
DELTA	Mengembalikan selisih antara nilai titik data saat ini dan nilai titik data sebelumnya.

Beberapa fungsi agregat (seperti MIN, FIRST, dan LATEST) memiliki batasan pada jumlah baris yang dikembalikan. Operasi penyaringan berdasarkan kolom non-tag diterapkan pada set hasil downsampled daripada data mentah, yang dapat menghasilkan jumlah catatan yang tidak terduga. Disarankan untuk melakukan operasi penyaringan berdasarkan kolom tag.

Dalam contoh ini, fungsi latest(field, n) digunakan, di mana temperature adalah kolom non-tag:

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

Pernyataan setara:

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

Anda tidak perlu menentukan fungsi untuk downsampling dalam pernyataan SELECT saat menanyakan data dari kolom tag tabel. Namun, jika ingin menanyakan data dari kolom non-tag, Anda harus menentukan fungsi untuk downsampling.

Jalankan pernyataan berikut untuk membuat tabel bernama sensor dan menyisipkan data ke dalam tabel:

-- Buat tabel contoh bernama 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 ke dalam tabel.
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 kueri downsampling dan kueri downsampling yang mencakup subkueri

Catatan

Subkueri tidak didukung dalam kueri downsampling. Namun, kueri downsampling dapat digunakan sebagai subkueri dalam jenis kueri lainnya.

Contoh 1: Downsampling dilakukan pada interval yang ditentukan berdasarkan waktu UTC. Pernyataan berikut menggabungkan deret waktu berdasarkan interval 8 jam dan mengembalikan jumlah nilai yang dikumpulkan dalam kolom kelembapan untuk setiap jendela waktu:

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: Downsampling dilakukan pada interval yang ditentukan berdasarkan waktu UTC dan offset waktu tertentu. Pernyataan berikut menentukan bahwa deret waktu digabungkan berdasarkan interval 8 jam, dengan offset waktu mulai setiap jendela waktu sebesar 3 jam, serta menghitung jumlah nilai yang dikumpulkan dalam kolom kelembapan untuk setiap jendela waktu:

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: Downsampling dilakukan pada interval yang ditentukan berdasarkan waktu UTC. Pernyataan berikut menentukan bahwa deret waktu digabungkan berdasarkan interval 24 jam, dengan offset waktu mulai setiap jendela waktu sebesar 16 jam (sejajar dengan 00:00 di GMT+08:00), serta menghitung jumlah nilai yang dikumpulkan dalam kolom kelembapan untuk setiap jendela waktu:

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: Klausa SAMPLE BY tidak dapat digunakan bersama dengan klausa GROUP BY, LIMIT OFFSET, atau ORDER BY. Anda dapat menyertakan klausa GROUP BY, LIMIT OFFSET, dan ORDER BY dalam pernyataan SQL untuk menentukan 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 klausa LIMIT OFFSET untuk menentukan jumlah hasil yang ingin Anda peroleh.

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 kueri interpolasi

Contoh 1: Interpolasi nilai tertentu berdasarkan interval waktu tertentu.

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 nilai yang dikumpulkan dalam jendela waktu sebelumnya.

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 agregasi setelah downsampling

Contoh 1: Gunakan fungsi AVG untuk melakukan downsampling pada interval 2 jam, kemudian gunakan fungsi RATE untuk menghitung tingkat perubahan nilai antara dua titik data.

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: Lakukan downsampling pada interval 2 jam, kemudian gunakan fungsi DELTA untuk menghitung selisih antara nilai dua titik data.

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

Selama proses downsampling, semua deret waktu dibagi berdasarkan interval waktu tertentu, dan nilai titik data dalam setiap jendela waktu digabungkan. Jika tidak ada nilai yang dikumpulkan dalam jendela waktu, kebijakan pengisian tertentu dapat digunakan untuk menginterpolasi nilai untuk titik data tersebut. Sebagai contoh, timestamp nilai yang dikumpulkan dalam deret waktu setelah downsampling selesai adalah: t + 0, t + 20, dan t + 30. Jika Anda tidak menentukan kondisi untuk interpolasi dalam pernyataan kueri, hanya tiga nilai yang dikembalikan. Jika Anda menentukan 1 untuk interpolasi, empat nilai dikembalikan, dengan nilai 1 diinterpolasi sebagai nilai titik data pada t + 10.

Fungsi untuk Interpolasi

Kebijakan Pengisian	Nilai Interpolasi
none	Tidak ada nilai yang diinterpolasi. Ini adalah nilai default.
zero	Menginterpolasi nilai 0.
linear	Melakukan interpolasi linier.
previous	Menginterpolasi nilai sebelumnya.
near	Menginterpolasi nilai yang berdekatan.
after	Menginterpolasi nilai berikutnya.
fixed	Menginterpolasi nilai tertentu.