Tablestore:Contoh kueri SQL

Sintaks

CREATE TABLE table_name (
  `_m_name` VARCHAR(1024), 
  `_data_source` VARCHAR(1024), 
  `_tags` VARCHAR(1024), 
  `_time` BIGINT(20),
  `column_name ` data_type, 
  ......
  `user_column_namen ` data_type,
  PRIMARY KEY(`_m_name`,`_data_source`,`_tags`,`_time`))ENGINE=AnalyticalStore

Parameter

Contoh

Buat tabel pemetaan di model multi-nilai bernama `device::cpu` untuk tabel deret waktu perangkat.

CREATE TABLE `device::cpu` 
(`_m_name` VARCHAR(1024),
 `_data_source` VARCHAR(1024),
 `_tags` VARCHAR(1024),
 `_time` BIGINT(20), 
 `usage_user` BIGINT(20), 
 `usage_system` BIGINT(20),  
 `usage_idle` BIGINT(20), 
 `usage_nice` BIGINT(20), 
 `usage_iowait` BIGINT(20), 
 `usage_irq` BIGINT(20), 
 `usage_softirq` BIGINT(20), 
 `usage_steal` BIGINT(20),
 `usage_guest` BIGINT(20), 
 `usage_guest_nice` BIGINT(20),
 `one_hour_stamp` BIGINT(20),
 `one_minute_stamp` BIGINT(20),
 PRIMARY KEY(`_m_name`,`_data_source`,`_tags`,`_time`))  ENGINE=AnalyticStore;

Sintaks

SELECT
 select_expr [, select_expr] ...
 [FROM table_references]
 [WHERE where_condition]
 [GROUP BY groupby_condition]
 [ORDER BY order_condition]
 [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Parameter

Contoh

• Contoh 1: Kueri nilai maksimum kolom usage_irq dan nilai maksimum kolom usage_softirq untuk semua perangkat per hari dalam rentang waktu dari 2023-01-05 05:14:00 hingga 2023-01-07 09:14:00.

  Penting

  Zona waktu untuk unix_timestamp_micros("2023-01-05 05:14:00.000000") adalah zona waktu sistem (untuk Tiongkok, yaitu waktu Beijing UTC+8).

  SELECT time_bin(_time,"1day"), max(usage_irq),max(usage_softirq) 
  FROM `device::cpu` 
  WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
  AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000") 
  GROUP BY 1 ORDER BY 1;

• Contoh 2: Kueri rata-rata semua nilai dalam kolom usage_nice untuk semua periode waktu pada perangkat dengan host_50625 dan arsitektur CPU x64.

  SELECT avg(usage_nice) 
  FROM `device::cpu` 
  WHERE _data_source = "host_50625" 
  AND tag_value_at(_tags,"arch") = "x64";

• Contoh 3: Kueri jumlah baris data, rata-rata semua nilai dalam kolom usage_user, dan rata-rata semua nilai dalam kolom usage_system untuk semua perangkat dalam rentang waktu dari 2023-01-05 05:14:00 hingga 2023-01-07 09:14:00.

  SELECT count(*),avg(usage_idle),avg(usage_system) 
  FROM `device::cpu` 
  WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
  AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000");

• Contoh 4: Kelompokkan data CPU berdasarkan minggu, lalu kelompokkan setiap kelompok berdasarkan jam untuk menghitung deviasi standar nilai dalam kolom usage_user di setiap kelompok.

  SELECT week(from_unixtime_micros(_time)) as week,time_bin(_time,"1h"), stddev(usage_user) 
  FROM `device::cpu` 
  GROUP BY 1,2 ORDER BY 1,2;

• Contoh 5: Kueri nilai kolom usage_user, usage_system, dan usage_nice pada titik terakhir (lastpoint) setiap dua jam untuk semua perangkat dengan host_50625 dalam rentang waktu dari 2023-01-05 05:14:00 hingga 2023-01-07 09:14:00.

  SELECT time_bin(_time,"2h"), max_by(usage_user,_time),
  max_by(usage_system,_time),max_by(usage_nice,_time) 
  FROM `device::cpu` 
  WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
  AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000")
  AND _data_source = 'host_50625'
  GROUP BY 1 ORDER BY 1;

• Contoh 6: Tampilkan waktu, bulan, hari dalam seminggu, jam, menit, detik, dan mikrodetik saat nilai kolom usage_user adalah 100 dalam rentang waktu dari 2023-01-05 05:14:00 hingga 2023-01-07 09:14:00.

  SELECT from_unixtime_micros(_time) as time, 
  monthname(from_unixtime_micros(_time)) as monthname,
  dayname(from_unixtime_micros(_time)) as dayname,
  hour(from_unixtime_micros(_time)) as hour,
  minute(from_unixtime_micros(_time)) as minute,
  second(from_unixtime_micros(_time)) as second,
  microsecond(from_unixtime_micros(_time)) as microsecond 
  FROM `device::cpu` 
  WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
  AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000") 
  AND usage_user = 100 LIMIT 100;