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

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 menggerakkan 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 tipe visualisasi. Pernyataan ini dieksekusi saat laporan dimuat atau disegarkan, sehingga kinerja berpengaruh pada pengalaman pengguna. SQL visualisasi tidak dapat memodifikasi data; semua pernyataan dijalankan sebagai operasi SELECT hanya-baca terhadap skema IoT Query.

Laporan menggunakan pendekatan SQL visualisasi yang sama seperti panel dashboard. 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 dalam satu SELECT.

SQL Editor mendukung eksplorasi data dan ekspor. Akses SQL Editor dari sidebar 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, sementara pernyataan di SQL Editor dapat mengembalikan format hasil apa pun. Uji logika 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 grafik batang dari tiga kolom atau ubin stat dari data teks. Periksa bagian Dataset Requirements di tab SQL Query untuk melihat dengan tepat apa yang diharapkan oleh visualisasi yang Anda pilih sebelum menulis pernyataan. Tabel di bawah berisi tipe visualisasi yang didukung:

Visualisasi	Persyaratan query	Contoh
Ubin stat	Nilai numerik tunggal	SELECT COUNT(*) FROM schema.table
Grafik batang	Dua kolom: kategori, nilai	SELECT column1, COUNT(*) FROM schema.table GROUP BY column1
Diagram lingkaran	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 perlu query	Hanya konten Markdown

Ubin stat

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

Total perjalanan bulan ini

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 penting, yang penting hasilnya berupa satu nilai numerik tunggal. Dashboard Studio menampilkan nilai ini dengan pemformatan yang Anda konfigurasikan di Visualization Settings.

Grafik batang

Grafik batang memerlukan 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 URUTKAN BERDASARKAN untuk mengontrol urutan batang. Urutkan berdasarkan nilai untuk perbandingan peringkat atau berdasarkan kategori untuk perkembangan deret waktu.

Diagram lingkaran

Diagram lingkaran memerlukan 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 lingkaran dengan 20+ irisan menjadi tidak dapat dibaca; batasi pada 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 mudah dibaca: distance_km as "Distance (km)".

Panel teks

Panel teks menampilkan nilai teks tunggal atau string yang diformat. 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 dashboard. Karena satu pernyataan menggerakkan tabel data, grafik, dan peta lokasi bersama-sama, Anda mungkin perlu menggabungkan kolom yang biasanya ditulis sebagai query panel terpisah di dashboard. Misalnya, sebuah query panel grafik batang yang mengembalikan dua kolom tidak cukup untuk 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 di 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 dashboard, di mana setiap panel memiliki query fokus 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 dalam pengaturan grafik
Peta lokasi	Lintang dan bujur dalam 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 menjadi pemicu sebagian besar keputusan struktural.

Menggabungkan komponen dalam satu query

Query yang hanya mengembalikan kolom yang dibutuhkan untuk grafik (dua kolom: kategori dan nilai) tidak dapat juga menggerakkan 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 telematika mentah menyimpan koordinat dan kecepatan sebagai bilangan bulat yang diskalakan. Koordinat dibagi oleh 10.000.000 (10⁷) untuk mengonversi ke derajat desimal, dan kecepatan dibagi oleh 100 (10²) untuk mengonversi ke km/jam. Terapkan konversi ini dalam query apa pun yang membaca dari raw_telematics_data tabel.

Mengadaptasi query panel dashboard untuk laporan

Query panel mana pun dari dashboard adalah titik awal yang valid untuk laporan. Penyesuaian yang diperlukan bergantung pada komponen mana yang ingin Anda aktifkan.

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

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

Buku Resep SQL berisi contoh query siap-pakai untuk analisis armada umum. Resep dari buku dapat diadaptasi untuk laporan dengan menambahkan kolom koordinat ketika peta lokasi diperlukan. Logika WHERE dan JOIN inti 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 berkala tetapi tetap konsisten di beberapa panel: rentang tanggal analisis, filter tipe kendaraan, atau nilai ambang. Ketika nilai ini berubah, perbarui definisi variabel sekali 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. Lakukan cast ke tipe yang sesuai di SQL: '${variable_name}'::date untuk tanggal, '${variable_name}'::integer untuk angka.

Untuk parameter spesifik pernyataan yang berubah sering, 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;

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

Cara mengakses skema IoT Query

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

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

Lapisan Transformasi 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 memasuki dan keluar dari geofence. silver.idle_events mengidentifikasi periode ketika kendaraan tetap diam dengan mesin menyala. Gunakan Transformation untuk sebagian besar kebutuhan visualisasi karena menyediakan struktur yang siap analisis.

Lapisan Insight menawarkan metrik pra-agregasi dan model dimensional untuk analitik kompleks. Gunakan Insight untuk statistik tingkat armada atau analisis multi-dimensi yang akan memerlukan join kompleks melawan tabel Silver.

Referensikan 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 di awal klausa WHERE untuk mengurangi volume data yang diproses.

Cara menggunakan SQL Editor

Akses SQL Editor dari sidebar kiri di bawah Tools. Gunakan untuk tiga tujuan utama: menguji logika sebelum ditambahkan ke panel, mengeksplorasi skema data untuk memahami kolom yang tersedia, dan mengekspor data yang tidak perlu divisualisasikan.

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, berbeda dengan SQL visualisasi yang harus mengembalikan dataset terfokus.

Uji SQL visualisasi di SQL Editor sebelum menambahkannya ke panel. Tulis pernyataan, verifikasi bahwa ia 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 grafik 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;

Polanya 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 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 banyak 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 dalam 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 grafik batang tetapi SQL Anda mengembalikan tiga kolom, Dashboard Studio tidak dapat merendernya. Periksa Dataset Requirements di tab SQL Query. Grafik 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 prefix skema yang hilang (trips alih-alih silver.trips), kesalahan 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, hasil 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 dieksekusi lambat atau time-out, 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 penyaringan dan pemilihan skema.

Di mana menemukan contoh SQL

Skema Buku Resep SQL 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 menyertakan pernyataan SQL lengkap, penjelasan logika, dan contoh hasil.

Adaptasi contoh Buku Resep untuk visualisasi dengan menyesuaikan klausa SELECT agar sesuai dengan persyaratan visualisasi. Resep yang mengembalikan catatan perjalanan terperinci dapat menjadi grafik 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 Buku Resep 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. Referensi ini menjelaskan tabel yang tersedia, definisi kolom, dan hubungan antara lapisan Raw data, Transformation, dan Insight.