Fungsi umum untuk Time Series SQL - Lindorm - Alibaba Cloud Documentation Center

LindormTSDB SQL menyediakan serangkaian fungsi bawaan untuk data deret waktu, mencakup agregasi, seleksi, konversi, dan analisis berbasis AI. Halaman ini menjelaskan sintaksis, parameter, penggunaan, serta contoh yang dapat dijalankan untuk setiap fungsi.

Mesin dan versi yang didukung

Fungsi-fungsi ini hanya berlaku untuk LindormTSDB.
Diperlukan LindormTSDB versi 3.4.7 atau lebih baru. Untuk instruksi peningkatan, lihat Catatan rilis LindormTSDB dan Peningkatan versi mesin minor instans Lindorm.

Daftar fungsi

Kategori	Fungsi	Deskripsi
Timestamp	CURRENT_TIMESTAMP	Mengembalikan waktu sistem saat ini.
Agregat	SUM	Menghitung jumlah nilai dalam kolom field.
Agregat	AVG	Menghitung rata-rata nilai dalam kolom field.
Agregat	COUNT	Menghitung jumlah nilai dalam kolom field.
Select	MIN	Mengembalikan nilai minimum dalam kolom field.
Select	MAX	Mengembalikan nilai maksimum dalam kolom bidang.
Select	FIRST	Mengembalikan nilai paling awal dalam kolom field.
Select	LAST	Mengembalikan nilai paling akhir dalam kolom field.
Select	PERCENTILE	Mengembalikan nilai pada persentil tertentu dalam kolom field.
Konversi	RATE	Menghitung laju perubahan (kemiringan) antara nilai-nilai berturut-turut dalam kolom field.
Konversi	DELTA	Menghitung selisih antara setiap nilai dengan nilai sebelumnya dalam kolom field.
Konversi	LATEST	Mengembalikan N nilai terbaru dalam kolom field.
AI	ANOMALY_DETECT	Mendeteksi anomali dalam kolom field.
AI	BINS	Mengelompokkan nilai dalam kolom field ke dalam bin (feature binning).

Persiapan data

Semua contoh dalam halaman ini menggunakan tabel bernama sensor dengan skema berikut:

+-------------+-----------+------------+
| columnName  | typeName  | columnKind |
+-------------+-----------+------------+
| device_id   | VARCHAR   | TAG        |
| region      | VARCHAR   | TAG        |
| time        | TIMESTAMP | TIMESTAMP  |
| temperature | DOUBLE    | FIELD      |
| humidity    | DOUBLE    | FIELD      |
+-------------+-----------+------------+

Tabel tersebut berisi baris-baris berikut:

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

Tabel tersebut memiliki dua deret waktu:

Deret waktu 1: device_id=F07A1260, region=north-cn, dengan field temperature dan humidity.
Deret waktu 2: device_id=F07A1261, region=south-cn, dengan field temperature dan humidity.

Untuk informasi lebih lanjut mengenai konsep deret waktu, lihat Istilah.

Pola kueri

Contoh-contoh dalam halaman ini menggunakan dua pola SAMPLE BY, keduanya merupakan sintaksis SQL khusus LindormTSDB:

SAMPLE BY <interval> — melakukan downsampling data dengan mengelompokkan baris ke dalam jendela waktu tetap. Misalnya, SAMPLE BY 20s mengelompokkan baris ke dalam interval 20 detik dan menerapkan fungsi agregat pada setiap interval.
SAMPLE BY 0 — melakukan agregasi pada rentang waktu penuh untuk setiap deret waktu tanpa downsampling.

Fungsi timestamp

CURRENT_TIMESTAMP

Mengembalikan waktu sistem saat ini dari server yang menjalankan instans Lindorm.

Sintaksis

CURRENT_TIMESTAMP

Penting

CURRENT_TIMESTAMP tidak memiliki parameter.
Timestamp yang dikembalikan akurat hingga milidetik. Contoh: 2023-04-23T21:13:15.819+08:00.
Jangan gunakan CURRENT_TIMESTAMP untuk menghasilkan timestamp untuk penulisan produksi. Gunakan hanya untuk menghasilkan data uji.

Contoh

Masukkan satu baris ke dalam tabel sensor menggunakan waktu sistem saat ini sebagai timestamp:

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

Fungsi agregat

SUM

Menghitung jumlah nilai dalam kolom field. Mengembalikan DOUBLE atau BIGINT.

Sintaksis

SUM(field_name)

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field. Kolom tidak boleh bertipe VARCHAR atau BOOLEAN.

Contoh

Contoh 1: Lakukan downsampling pada interval 20 detik, lalu hitung jumlah temperature per interval per perangkat.

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;

Hasil:

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

Contoh 2: Hitung total jumlah temperature per perangkat pada rentang waktu penuh.

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;

Hasil:

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

Contoh 3: Hitung total jumlah temperature di semua perangkat.

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';

Hasil:

+-------------+
| temperature |
+-------------+
| 92.400000   |
+-------------+

AVG

Menghitung rata-rata nilai dalam kolom field. Mengembalikan DOUBLE.

Sintaksis

AVG(field_name)

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field. Kolom tidak boleh bertipe VARCHAR atau BOOLEAN.

Contoh

Contoh 1: Lakukan downsampling pada interval 20 detik, lalu hitung rata-rata temperature per interval per perangkat.

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;

Hasil:

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

Contoh 2: Hitung rata-rata temperature per perangkat pada rentang waktu penuh.

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;

Hasil:

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

Contoh 3: Hitung rata-rata temperature di semua perangkat.

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';

Hasil:

+-------------+
| temperature |
+-------------+
| 13.200000   |
+-------------+

COUNT

Menghitung jumlah nilai dalam kolom field. Mengembalikan BIGINT.

Sintaksis

COUNT(field_name)

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field.

Contoh

Contoh 1: Lakukan downsampling pada interval 20 detik, lalu hitung jumlah nilai temperature per interval per perangkat.

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;

Hasil:

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

Contoh 2: Hitung total jumlah nilai temperature per perangkat pada rentang waktu penuh.

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;

Hasil:

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

Contoh 3: Hitung total jumlah nilai temperature di semua perangkat.

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';

Hasil:

+-------------+
| temperature |
+-------------+
| 7           |
+-------------+

Pilih fungsi

Fungsi select mengembalikan baris data aktual, bukan skalar hasil komputasi. Berbeda dengan fungsi agregat seperti SUM dan AVG, fungsi select mempertahankan nilai asli—dan dalam beberapa kasus juga timestamp terkait—dari baris dasar.

MIN

Mengembalikan nilai minimum dalam kolom field. Mengembalikan DOUBLE atau BIGINT.

Sintaksis

MIN(field_name)

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field. Kolom tidak boleh bertipe VARCHAR atau BOOLEAN.

Contoh

Contoh 1: Lakukan downsampling pada interval 20 detik, lalu cari nilai minimum temperature per interval per perangkat.

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;

Hasil:

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

Contoh 2: Cari nilai minimum temperature per perangkat pada rentang waktu penuh.

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;

Hasil:

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

Contoh 3: Cari nilai minimum temperature di semua perangkat.

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';

Hasil:

+-------------+
| temperature |
+-------------+
| 10.600000   |
+-------------+

MAX

Mengembalikan nilai maksimum dalam kolom field. Mengembalikan DOUBLE atau BIGINT.

Sintaksis

MAX(field_name)

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field. Kolom tidak boleh bertipe VARCHAR atau BOOLEAN.

Contoh

Contoh 1: Lakukan downsampling pada interval 20 detik, lalu cari nilai maksimum temperature per interval per perangkat.

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;

Hasil:

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

Contoh 2: Cari nilai maksimum temperature per perangkat pada rentang waktu penuh.

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;

Hasil:

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

Contoh 3: Cari nilai maksimum temperature di semua perangkat.

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';

Hasil:

+-------------+
| temperature |
+-------------+
| 20.600000   |
+-------------+

FIRST

Mengembalikan nilai paling awal dalam kolom field dalam jendela kueri. Tipe pengembalian sesuai dengan tipe kolom field.

Sintaksis

FIRST(field_name)

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field.

Contoh

Contoh 1: Lakukan downsampling pada interval 20 detik, lalu ambil nilai pertama temperature per interval per perangkat.

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;

Hasil:

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

Contoh 2: Ambil nilai pertama temperature per perangkat pada rentang waktu penuh.

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;

Hasil:

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

Mengembalikan nilai paling akhir dalam kolom field dalam jendela kueri. Tipe pengembalian sesuai dengan tipe kolom field.

Sintaksis

LAST(field_name)

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field.

Contoh

Contoh 1: Lakukan downsampling pada interval 20 detik, lalu ambil nilai terakhir temperature per interval per perangkat.

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;

Hasil:

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

Contoh 2: Ambil nilai terakhir temperature per perangkat pada rentang waktu penuh.

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;

Hasil:

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

Mengembalikan nilai pada persentil tertentu dalam kolom field. Mengembalikan DOUBLE.

Sintaksis

PERCENTILE(field_name, P)

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field. Kolom tidak boleh bertipe VARCHAR atau BOOLEAN.
`P`	Bilangan bulat atau bilangan titik mengambang yang menentukan persentil. Rentang valid: 0 hingga 100. Default: 50.

Contoh

Contoh 1: Lakukan downsampling pada interval 20 detik, lalu hitung persentil ke-90 dari temperature per interval per perangkat.

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;

Hasil:

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

Contoh 2: Hitung persentil ke-90 dari temperature per perangkat pada rentang waktu penuh.

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;

Hasil:

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

Fungsi konversi

RATE

Menghitung laju perubahan (kemiringan) antara nilai-nilai berturut-turut dalam kolom field. Mengembalikan DOUBLE.

Diberikan titik data (t1,v1), (t2,v2) ... (tN,vN), RATE menghasilkan N−1 titik output. Timestamp dan nilai output masing-masing dihitung sebagai:

di mana interval adalah interval waktu yang ditentukan dalam detik.

Penting

RATE harus menjadi satu-satunya aggregator dalam pernyataan SQL. Fungsi ini tidak dapat digabungkan dengan aggregator lain seperti AVG, DELTA, atau LATEST dalam kueri yang sama.

Sintaksis

RATE(field_name, 'interval units')

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field. Kolom tidak boleh bertipe VARCHAR atau BOOLEAN.
`interval units`	Interval waktu untuk perhitungan kemiringan. Default: `1s`. Satuan yang valid: `s` (detik), `m` (menit), `h` (jam), `d` (hari).

Contoh

Contoh 1: Hitung laju perubahan per detik dari temperature untuk setiap perangkat.

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;

Hasil:

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

Contoh 2: Hitung laju perubahan per menit dari temperature untuk setiap perangkat.

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;

Hasil:

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

Contoh 3: Lakukan downsampling pada interval 20 detik, hitung rata-rata temperature per interval, lalu hitung laju perubahan per menit dari rata-rata tersebut.

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;

Hasil:

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

Contoh 4: Kueri berikut gagal karena RATE tidak dapat digabungkan dengan aggregator lain.

-- Error: multiple aggregators in one statement
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

-- Error: mixing RATE with 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

Menghitung selisih antara setiap nilai dengan nilai sebelumnya dalam kolom field. Mengembalikan DOUBLE.

Diberikan titik data (t1,v1), (t2,v2) ... (tN,vN), DELTA menghasilkan N−1 titik output: (t2, v2−v1), (t3, v3−v2) ... (tN, vN−vN-1).

Penting

DELTA harus menjadi satu-satunya aggregator dalam pernyataan SQL. Fungsi ini tidak dapat digabungkan dengan aggregator lain seperti AVG, RATE, atau LATEST dalam kueri yang sama.

Sintaksis

DELTA(field_name)

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field. Kolom tidak boleh bertipe VARCHAR atau BOOLEAN.

Contoh

Contoh 1: Hitung selisih nilai temperature antar baris untuk setiap perangkat.

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;

Hasil:

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

Contoh 2: Lakukan downsampling pada interval 20 detik, hitung rata-rata temperature per interval, lalu hitung selisih antar baris dari rata-rata tersebut.

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;

Hasil:

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

Contoh 3: Kueri berikut gagal karena DELTA tidak dapat digabungkan dengan aggregator lain.

-- Error: mixing DELTA with 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

Mengembalikan N nilai terbaru dalam kolom field, diurutkan berdasarkan timestamp. Tipe pengembalian sesuai dengan tipe kolom field.

LATEST harus menjadi satu-satunya aggregator dalam pernyataan SQL.

Sintaksis

LATEST(field_name, N)

Parameter

Parameter	Deskripsi
`field_name`	Nama kolom field.
`N`	Bilangan bulat yang menentukan jumlah nilai terbaru yang akan dikembalikan. Default: 1.

Contoh

Contoh 1: Dapatkan nilai temperature terbaru untuk setiap perangkat.

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;

Hasil:

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

Contoh 2: Dapatkan dua nilai temperature terbaru untuk setiap perangkat.

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;

Hasil:

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

Contoh 3: Kueri berikut gagal karena LATEST tidak dapat digabungkan dengan aggregator lain.

-- Error: mixing LATEST with 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

Fungsi AI

FORECAST

Meramalkan nilai field masa depan dalam tabel deret waktu menggunakan model yang telah dilatih. Mengembalikan DOUBLE.

Prasyarat

FORECAST bergantung pada Lindorm AI. Aktifkan Lindorm AI untuk instans Anda sebelum menggunakan fungsi ini.

Sintaksis

FORECAST(field_name, model_name, options)

Parameter

Parameter	Wajib	Deskripsi
`field_name`	Ya	Nama kolom field. Kolom tidak boleh bertipe VARCHAR atau BOOLEAN.
`model_name`	Ya	Nama model yang telah dilatih. Harus bertipe VARCHAR.
`options`	Tidak	Opsi peramalan dalam format `key1=value1[, key2=value2]`. Lihat opsi di bawah ini.

Opsi

Opsi	Tipe	Default	Deskripsi
`batch_size`	INTEGER	Jumlah deret waktu dalam pelatihan	Jumlah deret waktu per batch inferensi. Ketika deret waktu yang di-cache mencapai jumlah ini, peramalan batch dipicu.
`step`	INTEGER	Nilai `PREDICTION_LENGTH` yang ditetapkan selama pelatihan	Panjang langkah tempat hasil peramalan dikeluarkan.
`quantile_output`	DOUBLE	0.5	Persentil dari hasil peramalan output. Pembelajaran mesin dalam database menyediakan array nilai yang diprediksi per titik data; `quantile_output` memilih satu nilai dari array tersebut. Tetapkan berdasarkan algoritma yang digunakan: untuk DeepAR, gunakan nilai antara 0 dan 1; untuk TFT, gunakan 0.1, 0.5, atau 0.9.

Contoh

Contoh 1: Ramalkan nilai temperature dalam rentang waktu tertentu.

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;

Hasil:

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

Contoh 2: Lakukan peramalan dengan step=2 dan quantile_output=0.9 untuk mendapatkan peramalan persentil ke-90 pada setiap langkah kedua.

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;

Hasil:

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

Mendeteksi anomali dalam kolom field menggunakan algoritma bawaan atau model yang telah dilatih. Mengembalikan BOOLEAN.

Sintaksis

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

Parameter

Parameter	Wajib	Deskripsi
`field_name`	Ya	Nama kolom field. Kolom tidak boleh bertipe VARCHAR atau BOOLEAN.
`algo_name`	Bersyarat	Nama algoritma bawaan dari Akademi DAMO. Gunakan parameter ini ketika Lindorm AI tidak diaktifkan. Nilai yang didukung: `esd` (deteksi lonjakan untuk kurva pemantauan), `nsigma` (analisis anomali statistik sederhana), `ttest` (mendeteksi anomali akibat pergeseran nilai rata-rata), `istl-esd` (deteksi anomali untuk data periodik).
`model_name`	Bersyarat	Nama model deteksi anomali yang telah dilatih. Harus bertipe VARCHAR. Gunakan parameter ini ketika Lindorm AI diaktifkan. Untuk informasi lebih lanjut, lihat Aktifkan Lindorm AI.
`options`	Tidak	Opsi deteksi dalam format `key1=value1[, key2=value2]`. Untuk opsi yang tersedia, lihat Deteksi anomali deret waktu.

Contoh

Contoh 1: Gunakan algoritma ESD untuk mendeteksi anomali dalam data temperature di semua perangkat.

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;

Hasil:

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

Contoh 2: Gunakan ESD untuk mendeteksi anomali dalam data temperature hanya untuk perangkat F07A1260.

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;

Hasil:

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

Contoh 3: Gunakan ESD dengan opsi kustom — atur lenHistoryWindow menjadi 30 dan maxAnomalyRatio menjadi 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;

Hasil:

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

Contoh 4: Gunakan model yang telah dilatih dari Lindorm AI untuk mendeteksi anomali.

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;

Hasil:

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

Mengelompokkan nilai dalam kolom field ke dalam bin (feature binning). Feature binning — juga dikenal sebagai bucketing diskret — adalah teknik praproses data. Mengembalikan VARCHAR.

Sintaksis

BINS(field_name, options)

Parameter

Parameter	Wajib	Deskripsi
`field_name`	Ya	Nama kolom field. Kolom tidak boleh bertipe VARCHAR atau BOOLEAN.
`options`	Tidak	Kebijakan binning dan format output dalam format `key1=value1, key2=value2`.

Contoh

Contoh 1: Kelompokkan semua nilai temperature dalam rentang waktu tertentu ke dalam bin.

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;

Hasil:

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

Contoh 2: Kelompokkan nilai temperature untuk perangkat F07A1260 ke dalam bin.

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;

Hasil:

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

Contoh 3: Kelompokkan nilai temperature untuk perangkat F07A1260 ke dalam 2 bin seragam dan keluarkan label ordinal.

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;

Hasil:

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