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 menjalankan visualisasi, dan SQL Editor mandiri untuk eksplorasi data. Halaman ini menjelaskan cara menulis SQL yang efektif untuk kedua konteks tersebut, dengan penekanan pada kebutuhan visualisasi karena memiliki batasan struktural tertentu.

Di mana SQL digunakan

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

Query visualisasi menjalankan panel individual dalam laporan. Anda menulis pernyataan ini di SQL Query tab editor panel. Setiap panel menjalankan satu pernyataan yang harus mengembalikan data dalam struktur spesifik yang sesuai dengan jenis visualisasi. Pernyataan ini dieksekusi ketika laporan dimuat atau disegarkan, sehingga kinerja penting untuk pengalaman pengguna. SQL visualisasi tidak dapat memodifikasi data; semua pernyataan dijalankan sebagai operasi SELECT baca-saja terhadap skema IoT Query.

Laporan menggunakan pendekatan SQL visualisasi yang sama seperti panel dasbor. Sebuah laporan menjalankan satu query yang menggerakkan tiga tampilan secara bersamaan: tabel data, grafik, dan peta lokasi. Pernyataan harus mengembalikan semua kolom yang diperlukan di ketiga komponen tersebut, jadi sertakan kolom koordinat, waktu, dan metrik bersama-sama dalam satu SELECT.

SQL Editor mendukung eksplorasi data dan ekspor. Akses SQL Editor dari bilah sisi 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 cocok dengan struktur kolom yang tepat, sedangkan pernyataan di SQL Editor dapat mengembalikan format hasil apa pun. Uji logika yang kompleks di SQL Editor terlebih dahulu, lalu sesuaikan 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 stat dari data teks. Periksa bagian Dataset Requirements di tab SQL Query untuk melihat persis apa yang diharapkan visualisasi yang Anda pilih sebelum menulis pernyataan. Tabel di bawah berisi jenis visualisasi yang didukung:

Visualisasi	Persyaratan query	Contoh
Ubin stat	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 query	Hanya konten Markdown

Ubin stat

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

Total perjalanan di 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 konfigurasi 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 nilai untuk perbandingan peringkat atau berdasarkan kategori untuk perkembangan deret waktu.

Diagram pai

Diagram pai membutuhkan tepat dua kolom: label (teks) dan nilai (numerik). Kolom pertama menjadi label irisan, yang 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 lebih dari 20 irisan menjadi sulit dibaca; batasi ke 10-15 kategori teratas.

Tabel

Tabel menerima jumlah kolom apa 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 terbaca: distance_km as "Distance (km)".

Panel teks

Panel teks menampilkan nilai teks tunggal atau string berformat. 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;

Query laporan mengikuti aturan struktural yang sama seperti query visualisasi di panel dasbor. Karena satu pernyataan menjalankan tabel data, grafik, dan peta lokasi bersama-sama, Anda mungkin perlu menggabungkan kolom yang biasanya ditulis sebagai query panel terpisah di dasbor. Misalnya, query panel diagram batang yang mengembalikan dua kolom tidak cukup untuk sebuah laporan yang juga membutuhkan koordinat GPS untuk peta lokasi. Sertakan semua kolom yang diperlukan untuk setiap komponen dalam satu pernyataan. Logika penyaringan inti dan JOIN tetap sama seperti pada query panel; hanya klausa SELECT yang perlu diperluas.

Cara menulis SQL untuk laporan

Sebuah laporan menjalankan satu query SQL yang menggerakkan tiga komponen secara bersamaan: tabel data, grafik, dan peta lokasi. Berbeda dengan panel dasbor, di mana setiap panel memiliki query terfokus sendiri, query laporan harus mengembalikan semua kolom yang dibutuhkan di setiap komponen dalam satu pernyataan SELECT.

Persyaratan kolom per komponen

Setiap komponen laporan memiliki persyaratan kolom spesifik. Query Anda harus memenuhi semua komponen yang Anda aktifkan.

Komponen	Kolom yang dibutuhkan	Catatan
Tabel data	Kolom apa pun	Semua kolom yang dikembalikan muncul sebagai kolom tabel
Grafik	Setidaknya satu kolom waktu atau kategori, setidaknya satu kolom numerik	Kolom sumbu dipilih di pengaturan grafik
Peta lokasi	Lintang dan bujur sebagai derajat desimal	Dashboard Studio mendeteksi kolom koordinat secara otomatis

Karena tabel data menerima kolom apa pun, ia tidak memberlakukan batasan tambahan. Grafik dan peta lokasi yang mengarahkan sebagian besar keputusan struktural.

Menggabungkan komponen dalam satu query

Query yang hanya mengembalikan kolom yang diperlukan untuk grafik (dua kolom: kategori dan nilai) tidak dapat juga menjalankan peta lokasi. Anda harus menyertakan semua kolom yang diperlukan bersama-sama.

Contoh berikut mengembalikan kolom untuk ketiga komponen: kolom waktu dan kolom numerik untuk grafik, kolom koordinat untuk peta lokasi, dan atribut tambahan yang muncul di tabel data.

SELECT
    t.device_id,
    o.object_label,
    t.device_time,
    t.latitude::float / 10000000 AS latitude,
    t.longitude::float / 10000000 AS longitude,
    t.speed::float / 100 AS speed
FROM raw_telematics_data.tracking_data_core t
JOIN raw_business_data.objects o ON t.device_id = o.device_id
WHERE t.device_time >= NOW() - INTERVAL '24 hours'
ORDER BY t.device_time DESC
LIMIT 1000

Dalam query ini, device_time dan speed melayani grafik, latitude dan longitude melayani peta lokasi, dan semua kolom muncul di tabel data.

Tabel telematik mentah menyimpan koordinat dan kecepatan sebagai bilangan bulat yang diskalakan. Koordinat dibagi oleh 10.000.000 (10⁷) untuk dikonversi ke derajat desimal, dan kecepatan dibagi oleh 100 (10²) untuk dikonversi ke km/jam. Terapkan konversi ini di query mana pun yang membaca dari raw_telematics_data tables.

Menyesuaikan query panel dasbor untuk laporan

Query panel mana pun dari dasbor merupakan titik awal yang valid untuk sebuah laporan. Penyesuaian yang diperlukan tergantung pada komponen mana yang ingin Anda aktifkan.

Jika query panel sudah merupakan visualisasi tabel yang mengembalikan beberapa kolom, mungkin sudah menyertakan semua yang diperlukan. Tambahkan kolom koordinat jika peta lokasi dibutuhkan.

Jika query panel adalah query diagram batang atau ubin stat yang mengembalikan hasil agregasi, kemungkinan besar kekurangan detail tingkat baris yang dibutuhkan untuk tabel data dan peta lokasi. Dalam kasus tersebut, hapus agregasi dan bekerja dari data mentah atau lapisan Silver yang mendasari sebagai gantinya.

Buku Resep SQL berisi contoh query siap pakai untuk analisis armada umum. Resep dari buku dapat disesuaikan untuk laporan dengan menambahkan kolom koordinat ketika peta lokasi dibutuhkan. Logika WHERE dan JOIN inti dapat ditransfer langsung; sesuaikan hanya klausa SELECT untuk mencakup semua komponen yang diperlukan.

Cara menggunakan variabel global

Variabel global menyediakan nilai yang dapat digunakan kembali di banyak pernyataan SQL. Definisikan variabel di Settings > Configuration > Global Variables, lalu rujuk menggunakan ${variable_name} sintaks.

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 ini berubah, perbarui definisi variabel sekali saja alih-alih mengedit pernyataan SQL individual.

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 stempel waktu, koordinat, dan pembacaan sensor. Gunakan Bronze untuk analisis tingkat titik atau ketika Anda memerlukan nilai sensor mentah yang tidak diproses ke lapisan yang lebih tinggi.

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

Lapisan Gold menawarkan metrik pra-agregasi dan model dimensional untuk analitik yang kompleks. Gunakan Gold untuk statistik armada secara luas atau analisis multi-dimensi yang memerlukan join kompleks terhadap tabel Silver.

Rujuk tabel menggunakan schema.table format: silver.trips, bukan hanya trips. Sertakan filter rentang tanggal di 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 lebih awal di klausa WHERE untuk mengurangi volume data yang diproses.

Cara menggunakan SQL Editor

Akses SQL Editor dari bilah sisi kiri di bawah Tools. Gunakan untuk tiga tujuan utama: menguji logika sebelum menambahkannya ke panel, menjelajahi 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 menunjukkan 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, berbeda dengan SQL visualisasi yang sebaiknya mengembalikan dataset terfokus.

Uji SQL visualisasi di SQL Editor sebelum menambahkannya ke panel. Tulis pernyataan, verifikasi bahwa itu mengembalikan kolom dan tipe data yang diharapkan, lalu salin ke tab SQL Query editor panel. Alur kerja ini menangkap masalah struktural sebelum Anda mengonfigurasi 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. Sesuaikan 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.

Hitungan 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 kelompok

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 agregat

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 ketika SQL gagal

Kegagalan eksekusi masuk ke tiga kategori: ketidakcocokan struktural dengan kebutuhan 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 membutuhkan 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 awalan skema yang hilang (trips alih-alih silver.trips), salah ketik pada nama kolom, atau casting tanggal yang salah. 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 waktu habis, 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 mengenai penyaringan dan pemilihan skema.

Di mana menemukan contoh SQL

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

Sesuaikan 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 stat dengan menambahkan SUM di seluruh kendaraan.

Anda hanya perlu:

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

2. Uji dengan data Anda yang sebenarnya.

3. Verifikasi hasil, lalu ubah 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. Referensi ini menjelaskan tabel yang tersedia, definisi kolom, dan hubungan antara lapisan Bronze, Silver, dan Gold.