Menulis kueri SQL

Dashboard Studio menggunakan SQL untuk mengambil data dari skema IoT Query. Anda menulis SQL dalam dua konteks: editor panel, di mana pernyataan menggerakkan visualisasi, dan Editor SQL mandiri untuk eksplorasi data. Halaman ini menjelaskan cara menulis SQL yang efektif untuk kedua konteks, dengan penekanan pada persyaratan visualisasi karena mereka memiliki batasan struktural khusus.

Tempat penggunaan SQL

Dashboard Studio menyediakan dua lingkungan SQL untuk tujuan yang berbeda. Memahami kapan menggunakan masing‑masing membantu Anda bekerja lebih efisien.

Kueri visualisasi menggerakkan panel individual dalam laporan. Anda menulis pernyataan ini di tab SQL Query editor panel. Setiap panel menjalankan satu pernyataan yang harus mengembalikan data dalam struktur tertentu yang sesuai dengan tipe visualisasi. Pernyataan ini dieksekusi saat laporan dimuat atau diperbarui, sehingga kinerja penting bagi pengalaman pengguna. SQL visualisasi tidak dapat memodifikasi data; semua pernyataan dijalankan sebagai operasi SELECT read‑only terhadap skema IoT Query.

SQL Editor mendukung eksplorasi dan ekspor data. Akses SQL Editor dari bilah samping kiri di bawah Tools. Tulis pernyataan SELECT apa pun untuk memeriksa struktur data, memvalidasi asumsi, atau mengekspor hasil sebagai CSV. SQL Editor menampilkan tabel hasil penuh dengan pengurutan kolom dan menyediakan metrik eksekusi. Gunakan ini untuk menguji logika sebelum menambahkan SQL ke panel visualisasi, atau untuk ekstraksi data ad‑hoc yang tidak memerlukan visualisasi.

Perbedaan utama: SQL visualisasi harus sesuai dengan struktur kolom yang tepat, sedangkan pernyataan di SQL Editor dapat mengembalikan format hasil apa pun. Uji logika kompleks di SQL Editor terlebih dahulu, lalu adaptasikan untuk visualisasi.

Cara menulis SQL untuk visualisasi

SQL visualisasi harus mengembalikan jumlah kolom dan tipe data tertentu. Dashboard Studio tidak dapat merender diagram batang dari tiga kolom atau ubin statistik dari data teks. Periksa bagian Dataset Requirements di tab SQL Query untuk melihat persis apa yang diharapkan visualisasi pilihan Anda sebelum menulis pernyataan. Tabel di bawah berisi tipe visualisasi yang didukung:

Visualisasi	Persyaratan kueri	Contoh
Ubin statistik	Nilai numerik tunggal	SELECT COUNT(*) FROM schema.table
Diagram batang	Dua kolom: kategori, nilai	SELECT column1, COUNT(*) FROM schema.table GROUP BY column1
Diagram pai	Dua kolom: label, nilai	SELECT category, SUM(value) FROM schema.table GROUP BY category
Tabel	Kolom apa pun	SELECT column1, column2, column3 FROM schema.table
Teks	Tidak memerlukan kueri	Hanya konten Markdown

Ubin statistik

Ubin statistik menampilkan nilai numerik tunggal. Pernyataan harus mengembalikan tepat satu baris dengan satu kolom numerik:

Total perjalanan pada bulan berjalan

SELECT COUNT(*) as value
FROM silver.trips
WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE);

Total jarak tempuh (km)

SELECT SUM(distance_km) as value
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '7 days';

Nama kolom tidak masalah, yang penting hasilnya adalah satu nilai numerik tunggal. Dashboard Studio menampilkan nilai ini dengan pemformatan yang Anda konfigurasikan di Visualization Settings.

Diagram batang

Diagram batang membutuhkan tepat dua kolom: kategori (teks atau tanggal) dan nilai (numerik). Kolom pertama menjadi sumbu X, kolom kedua menjadi tinggi batang:

Perjalanan per tipe kendaraan

SELECT 
  vehicle_type as category,
  COUNT(*) as value
FROM silver.trips
WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY vehicle_type
ORDER BY value DESC;

Jumlah perjalanan harian

SELECT 
  DATE_TRUNC('day', start_time)::date as category,
  COUNT(*) as value
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', start_time)
ORDER BY category;

Gunakan ORDER BY untuk mengontrol urutan batang. Urutkan berdasarkan value untuk perbandingan peringkat atau berdasarkan category untuk perkembangan deret waktu.

Diagram pai

Diagram pai membutuhkan tepat dua kolom: label (teks) dan nilai (numerik). Kolom pertama menjadi label irisan, kolom kedua menentukan ukuran irisan:

Distribusi perjalanan menurut zona

SELECT 
  zone_name as label,
  COUNT(*) as value
FROM silver.zone_visits
WHERE enter_time >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY zone_name
ORDER BY value DESC
LIMIT 10;

Tambahkan klausa LIMIT untuk kategori dengan banyak nilai. Diagram pai dengan 20+ irisan menjadi tidak terbaca; batasi ke 10–15 kategori teratas.

Tabel

Tabel menerima jumlah kolom berapa pun dengan tipe data apa pun. Pilih kolom yang ingin Anda tampilkan:

Detail perjalanan terbaru

SELECT 
  device_id,
  start_time,
  end_time,
  distance_km,
  duration_minutes,
  max_speed_kmh
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY start_time DESC
LIMIT 100;

Nama kolom menjadi header tabel. Gunakan alias dengan spasi untuk header yang mudah dibaca: distance_km as "Distance (km)".

Panel teks

Panel teks menampilkan nilai teks tunggal atau string terformat. Pernyataan harus mengembalikan satu kolom teks:

Timestamp pembaruan data terakhir

SELECT 
  'Last updated: ' || MAX(record_added_at)::text as value
FROM bronze.tracking_data_core;

Cara menggunakan variabel global

Variabel global menyediakan nilai yang dapat digunakan ulang di beberapa pernyataan SQL. Definisikan variabel di Settings > Configuration > Global Variables, lalu referensikan menggunakan ${variable_name} syntax.

Definisikan variabel untuk nilai yang berubah secara berkala tetapi tetap konsisten di banyak panel: rentang tanggal analisis, filter tipe kendaraan, atau nilai ambang. Ketika nilai‑nilai ini berubah, perbarui definisi variabel sekali saja daripada mengedit pernyataan SQL individu.

Menggunakan variabel rentang tanggal

SELECT 
  DATE_TRUNC('day', start_time)::date as category,
  COUNT(*) as value
FROM silver.trips
WHERE start_time >= '${analysis_start_date}'::date
  AND start_time < '${analysis_end_date}'::date
GROUP BY DATE_TRUNC('day', start_time)
ORDER BY category;

Variabel menyimpan nilai teks. Cast ke tipe yang sesuai di SQL: '${variable_name}'::date untuk tanggal, '${variable_name}'::integer untuk angka.

Untuk parameter spesifik pernyataan yang sering berubah, Anda dapat menggunakan blok parameter CTE di awal:

WITH params AS (
  SELECT 
    5 as min_idle_minutes,
    10 as max_idle_speed_kmh,
    '${analysis_start_date}'::date as date_from,
    '${analysis_end_date}'::date as date_to
)

SELECT 
  device_id,
  COUNT(*) as idle_count,
  SUM(duration_minutes) as total_idle_minutes
FROM silver.idle_events e
CROSS JOIN params p
WHERE e.event_time >= p.date_from
  AND e.event_time < p.date_to
  AND e.speed_kmh <= p.max_idle_speed_kmh
  AND e.duration_minutes >= p.min_idle_minutes
GROUP BY device_id
ORDER BY total_idle_minutes DESC;

Pola ini menggabungkan variabel global (rentang tanggal) dengan parameter spesifik pernyataan (ambang), menjaga semua nilai yang dapat disesuaikan di bagian atas untuk pemeliharaan yang mudah.

Cara mengakses skema IoT Query

IoT Query mengorganisir data dalam lapisan Bronze, Silver, dan Gold. Memahami lapisan mana yang digunakan menghemat waktu dan meningkatkan kejelasan SQL. Untuk detail skema lengkap, lihat IoT Query Schema Overview.

Lapisan Bronze berisi titik pelacakan mentah dari perangkat: bronze.tracking_data_core menyimpan setiap posisi GPS dengan timestamp, koordinat, dan pembacaan sensor. Gunakan Bronze untuk analisis pada tingkat titik atau ketika Anda memerlukan nilai sensor mentah yang tidak diproses ke lapisan yang lebih tinggi.

Lapisan Silver menyediakan entitas terproses: silver.trips mengagregasi titik pelacakan menjadi catatan perjalanan dengan waktu mulai/berakhir, jarak, dan durasi. silver.zone_visits mencatat ketika perangkat masuk dan keluar geofence. silver.idle_events mengidentifikasi periode ketika kendaraan tetap diam dengan mesin menyala. Gunakan Silver untuk sebagian besar kebutuhan visualisasi karena menyediakan struktur siap‑analisis.

Lapisan Gold menawarkan metrik pra‑agregat dan model dimensional untuk analitik kompleks. Gunakan Gold untuk statistik seluruh armada atau analisis multidimensi yang akan memerlukan join kompleks terhadap tabel Silver.

Referensikan tabel menggunakan schema.table format: silver.trips, bukan hanya trips. Sertakan filter rentang tanggal dalam klausa WHERE untuk membatasi data yang dipindai:

Selalu filter berdasarkan rentang waktu

SELECT device_id, COUNT(*) as trip_count
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY device_id;

Sebagian besar pernyataan SQL memfilter berdasarkan perangkat, rentang waktu, atau keduanya. Tambahkan filter ini di awal klausa WHERE untuk mengurangi volume data yang diproses.

Cara menggunakan SQL Editor

Akses SQL Editor dari bilah samping kiri di bawah Tools. Gunakan untuk tiga tujuan utama: menguji logika sebelum menambahkan ke panel, mengeksplorasi skema data untuk memahami kolom yang tersedia, dan mengekspor data yang tidak memerlukan visualisasi.

SQL Editor mendukung beberapa tab untuk pernyataan yang berbeda. Tulis SQL di tab, jalankan dengan tombol "Execute Query", dan lihat hasil di tabel di bawah. Hasil menampilkan metrik eksekusi (waktu eksekusi, baris yang dikembalikan) dan mendukung pengurutan kolom untuk pemeriksaan data cepat.

Ekspor hasil sebagai CSV menggunakan tombol "Export CSV". Ini berguna untuk laporan ad‑hoc atau ekstrak data untuk analisis eksternal. SQL Editor tidak memiliki batas baris hasil, tidak seperti SQL visualisasi yang harus mengembalikan dataset terfokus.

Uji SQL visualisasi di SQL Editor sebelum menambahkannya ke panel. Tulis pernyataan, verifikasi mengembalikan kolom dan tipe data yang diharapkan, lalu salin ke tab SQL Query editor panel. Alur kerja ini menangkap masalah struktural sebelum Anda mengkonfigurasi pengaturan visualisasi.

Pola eksplorasi untuk data baru:

-- 1. Periksa struktur tabel
SELECT * FROM silver.trips LIMIT 10;

-- 2. Periksa cakupan rentang tanggal
SELECT 
  MIN(start_time) as earliest,
  MAX(start_time) as latest,
  COUNT(*) as total_trips
FROM silver.trips;

-- 3. Uji logika penyaringan
SELECT 
  device_id,
  start_time,
  distance_km
FROM silver.trips
WHERE start_time >= '2024-01-01'
  AND device_id = 12345
ORDER BY start_time;

-- 4. Adaptasi untuk visualisasi (2 kolom untuk diagram batang)
SELECT 
  DATE_TRUNC('day', start_time)::date as day,
  COUNT(*) as trips
FROM silver.trips
WHERE start_time >= '2024-01-01'
  AND device_id = 12345
GROUP BY DATE_TRUNC('day', start_time)
ORDER BY day;

Pola SQL umum

Sebagian besar SQL visualisasi mengikuti pola serupa. Salin struktur ini dan sesuaikan filter, kolom, dan agregasi untuk kebutuhan spesifik Anda.

Jumlah deret waktu untuk melacak tren

SELECT 
  DATE_TRUNC('hour', start_time) as time_bucket,
  COUNT(*) as event_count
FROM silver.trips
WHERE start_time >= CURRENT_DATE - INTERVAL '24 hours'
GROUP BY DATE_TRUNC('hour', start_time)
ORDER BY time_bucket;

Peringkat kategori untuk membandingkan grup

SELECT 
  category_column,
  COUNT(*) as count
FROM schema.table
WHERE filter_conditions
GROUP BY category_column
ORDER BY count DESC
LIMIT 15;

Perhitungan metrik untuk statistik teragregasi

SELECT 
  SUM(distance_km) as total_distance,
  AVG(duration_minutes) as avg_duration,
  COUNT(*) as trip_count
FROM silver.trips
WHERE start_time >= DATE_TRUNC('week', CURRENT_DATE);

Ringkasan terfilter dengan beberapa kondisi

SELECT 
  device_id,
  COUNT(*) as trips,
  SUM(distance_km) as total_km
FROM silver.trips
WHERE start_time >= '${period_start}'::date
  AND start_time < '${period_end}'::date
  AND distance_km >= 5
  AND duration_minutes >= 10
GROUP BY device_id
HAVING COUNT(*) >= 5
ORDER BY total_km DESC;

Apa yang harus dilakukan saat SQL gagal

Kegagalan eksekusi dibagi menjadi tiga kategori: ketidakcocokan struktural dengan persyaratan visualisasi, kesalahan sintaks SQL, atau filter yang mengembalikan tidak ada data.

Ketidakcocokan struktur kolom

Terjadi ketika hasil tidak sesuai dengan ekspektasi visualisasi. Jika Anda memilih diagram batang tetapi SQL Anda mengembalikan tiga kolom, Dashboard Studio tidak dapat merendernya. Periksa Dataset Requirements di tab SQL Query. Diagram batang memerlukan tepat dua kolom (kategori, nilai), jadi sesuaikan klausa SELECT Anda:

-- Salah: tiga kolom
SELECT device_id, start_time, COUNT(*) FROM silver.trips GROUP BY device_id, start_time;

-- Benar: dua kolom
SELECT device_id, COUNT(*) as trips FROM silver.trips GROUP BY device_id;

Kesalahan sintaks SQL

Menampilkan pesan kesalahan spesifik. Masalah umum termasuk hilangnya prefix skema (trips alih‑alih silver.trips), salah ketik pada nama kolom, atau casting tanggal yang tidak benar. Uji pernyataan di SQL Editor untuk melihat pesan kesalahan rinci dengan nomor baris.

Hasil kosong

Meskipun eksekusi berhasil menunjukkan filter mengecualikan semua data. Uji SQL tanpa klausa WHERE di SQL Editor untuk memverifikasi tabel berisi data, lalu tambahkan filter secara bertahap untuk mengidentifikasi kondisi mana yang mengecualikan hasil yang Anda harapkan.

Masalah kinerja

Jika pernyataan berjalan lambat atau timeout, tambahkan filter rentang tanggal ke klausa WHERE. Operasi yang memindai seluruh tabel memproses jutaan baris secara tidak perlu:

-- Lambat: tanpa filter tanggal
SELECT device_id, COUNT(*) FROM silver.trips GROUP BY device_id;

-- Cepat: filter rentang tanggal
SELECT device_id, COUNT(*) 
FROM silver.trips 
WHERE start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY device_id;

Untuk panduan kinerja tambahan, lihat Cara mengakses skema IoT Query untuk praktik terbaik pada pemfilteran dan pemilihan skema.

Di mana menemukan contoh SQL

The SQL Recipe Book menyediakan contoh lengkap untuk analisis telematik umum. Resep‑resep ini menunjukkan pola untuk analisis perjalanan, perhitungan kunjungan zona, deteksi idle, dan metrik armada. Setiap resep mencakup pernyataan SQL lengkap, penjelasan logika, dan contoh hasil.

Adaptasi contoh dari Recipe Book untuk visualisasi dengan menyesuaikan klausa SELECT agar sesuai dengan persyaratan visualisasi. Resep yang mengembalikan catatan perjalanan rinci dapat menjadi diagram batang dengan menambahkan GROUP BY dan agregasi COUNT. Pernyataan yang menghitung metrik per kendaraan dapat menjadi ubin statistik dengan menambahkan SUM di seluruh kendaraan.

Yang perlu Anda lakukan:

1. Salin contoh dari Recipe Book ke Editor Dashboard Studio.

2. Uji dengan data aktual Anda.

3. Verifikasi hasil, lalu modifikasi klausa SELECT untuk visualisasi target Anda.

Logika WHERE dan JOIN inti tetap sama; Anda hanya menyesuaikan struktur keluaran.

Untuk detail skema, lihat IoT Query Schema Overview.