Kueri umum

Saat Anda terhubung ke database, Anda akan dapat mengambil data melalui kueri SQL. Bagian ini menyediakan contoh kueri SQL untuk membantu Anda memulai bekerja dengan Private Telematics Lakehouse. Contoh-contoh ini menunjukkan cara mengakses dan menganalisis data dari lapisan Raw data lapisan, yang berisi data bisnis dan telematik mentah dengan transformasi minimal.

Perlu diperhatikan: Karena database berisi jumlah informasi yang sangat besar, pastikan untuk membuat kueri pengujian berdasarkan jumlah nilai yang dibatasi.

Kueri dasar

Ambil informasi objek dasar

Kueri ini mengembalikan informasi tentang objek (kendaraan/aset) dalam sistem Anda:

SELECT
    o.object_id,
    o.object_label,
    o.model,
    d.device_imei,
    g.group_label
DARI
    raw_business_data.objects o
    LEFT JOIN raw_business_data.devices d ON o.device_id = d.device_id
    LEFT JOIN raw_business_data.groups g ON o.group_id = g.group_id
WHERE
    o.is_deleted = false
URUTKAN BERDASARKAN
    o.object_label;

Dapatkan lokasi perangkat terbaru

Ambil data lokasi terbaru untuk semua perangkat Anda:

SELECT
    t.device_id,
    o.object_label,
    -- Konversi koordinat bilangan bulat berskala kembali ke format desimal
    t.latitude::float / 10000000 AS latitude,
    t.longitude::float / 10000000 AS longitude,
    t.speed,
    t.device_time
DARI
    raw_telematics_data.tracking_data_core t
    JOIN raw_business_data.objects o ON t.device_id = o.device_id
WHERE
    t.device_time > (CURRENT_DATE - INTERVAL '1 day')
    DAN t.latitude != 0
    DAN t.longitude != 0
URUTKAN BERDASARKAN
    t.device_id, t.device_time DESC;

Nilai koordinat disimpan sebagai bilangan bulat yang diskalakan oleh 10^7 untuk meningkatkan efisiensi penyimpanan di TimescaleDB. Saat melakukan kueri, bagi dengan 10000000 untuk mengonversi kembali ke format desimal standar.

Menggabungkan data bisnis dan telematik

Laporan aktivitas kendaraan

Kueri ini menghasilkan ringkasan aktivitas harian dengan menggabungkan data bisnis dan telematik:

SELECT
    o.object_label AS vehicle,
    v.vehicle_type,
    DATE(t.device_time) AS date,
    COUNT(DISTINCT DATE_PART('hour', t.device_time)) AS active_hours,
    MAX(t.speed) AS max_speed,
    AVG(t.speed) AS avg_speed
DARI
    raw_telematics_data.tracking_data_core t
    JOIN raw_business_data.objects o ON t.device_id = o.device_id
    LEFT JOIN raw_business_data.vehicles v ON o.object_id = v.object_id
WHERE
    t.device_time BETWEEN '2025-03-01' AND '2025-03-28'
KELOMPOKKAN BERDASARKAN
    o.object_label, v.vehicle_type, DATE(t.device_time)
URUTKAN BERDASARKAN
    o.object_label, DATE(t.device_time);

Penugasan pengemudi dan riwayat lokasi

Lacak karyawan mana yang ditugaskan ke kendaraan mana dan riwayat lokasi mereka:

SELECT 
    o.object_label AS vehicle,
    new_row.changed_datetime AS assignment_time,
    e_new.first_name || ' ' || e_new.last_name AS new_driver_name,
    e_old.first_name || ' ' || e_old.last_name AS old_driver_name,
    new_row.address,
    new_row.latitude,
    new_row.longitude
DARI 
    raw_business_data.driver_history new_row
JOIN 
    raw_business_data.driver_history old_row
    ON new_row.changed_datetime = old_row.changed_datetime
    DAN new_row.object_id = old_row.object_id
LEFT JOIN 
    raw_business_data.employees e_new ON new_row.new_employee_id = e_new.employee_id
LEFT JOIN 
    raw_business_data.employees e_old ON old_row.old_employee_id = e_old.employee_id
LEFT JOIN 
    raw_business_data.objects o ON new_row.object_id = o.object_id
URUTKAN BERDASARKAN 
    assignment_time;

Menganalisis data sensor

Pelacakan tingkat bahan bakar

Kueri ini menunjukkan cara menganalisis data sensor bahan bakar:

SELECT
    o.object_label AS vehicle,
    t.device_time,
    i.value::numeric AS fuel_level
DARI
    raw_telematics_data.inputs i
    JOIN raw_business_data.objects o ON i.device_id = o.device_id
    JOIN raw_telematics_data.tracking_data_core t ON
        i.device_id = t.device_id DAN
        i.device_time = t.device_time
WHERE
    i.sensor_name = 'fuel'
    DAN t.device_time > (CURRENT_DATE - INTERVAL '7 days')
URUTKAN BERDASARKAN
    o.object_label, t.device_time;

Analisis geospasial

Kendaraan dalam geozone

Identifikasi kendaraan mana yang memasuki geozone tertentu:

SELECT
    o.object_label AS vehicle,
    z.zone_label AS geozone,
    t.device_time AS entry_time
DARI
    raw_telematics_data.tracking_data_core t
    JOIN raw_business_data.objects o ON t.device_id = o.device_id
    JOIN raw_business_data.zones z ON
        -- Hitung apakah titik berada dalam zona berbentuk lingkaran
        -- Konversi koordinat dari bilangan bulat berskala ke desimal
        (
            CASE
                KETIKA z.zone_type = 'circle' MAKA
                    ST_DWithin(
                        ST_MakePoint(t.longitude::float/10000000, t.latitude::float/10000000)::geography,
                        ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude)::geography,
                        z.radius
                    )
                LAINNYA false
            END
        )
WHERE
    t.device_time > (CURRENT_DATE - INTERVAL '1 day')
URUTKAN BERDASARKAN
    z.zone_label, o.object_label, t.device_time;

Kueri ini menggunakan fungsi spasial PostGIS. Jika Anda mengalami kesalahan, verifikasi bahwa ekstensi PostGIS diaktifkan di database Anda.

Tips optimisasi kinerja

Saat bekerja dengan Cloud Data Warehouse, pertimbangkan teknik optimisasi ini:

1. Gunakan penyaringan berbasis waktu: Selalu sertakan filter waktu pada device_time atau record_added_at kolom untuk membatasi data yang dipindai. Praktik yang baik:

SELECT * FROM raw_telematics_data.tracking_data_core 
WHERE device_time > (CURRENT_DATE - INTERVAL '7 days');

Hindari ini (memindai seluruh tabel)

SELECT * FROM raw_telematics_data.tracking_data_core;

1. Manfaatkan indeks: Database memiliki indeks pada (device_id, device_time) pasangan. Susun kueri Anda untuk menggunakan indeks ini bila memungkinkan.

2. Gunakan join secara selektif: Gabungkan tabel hanya bila diperlukan dan usahakan untuk memfilter data sebelum menggabungkan tabel besar.

3. Konversi bilangan bulat berskala: Ingat bahwa data koordinat disimpan sebagai bilangan bulat yang diskalakan. Konversikan hanya di SELECT akhir, bukan di klausa WHERE.

4. Batasi set hasil: Selalu gunakan LIMIT untuk kueri eksplorasi agar tidak mengembalikan jutaan baris.

SELECT * FROM raw_telematics_data.tracking_data_core 
WHERE device_time > (CURRENT_DATE - INTERVAL '1 day')
LIMIT 1000;

1. Manfaatkan hubungan hierarkis: Susun kueri kompleks mengikuti hierarki entitas (dealer → client → user/device → object) untuk join dan penyaringan yang lebih efisien.

2. Kelola koneksi dengan benar: Tutup koneksi database ketika tidak digunakan, terutama di alat BI atau skrip terjadwal, untuk menghindari penguncian sumber daya atau masalah waktu habis.

Langkah selanjutnya

Contoh-contoh ini memberikan titik awal untuk bekerja dengan data Anda. Saat Anda menjadi lebih akrab dengan skema, Anda dapat mengembangkan kueri yang lebih kompleks untuk memenuhi kebutuhan bisnis spesifik Anda.