Kueri umum

Saat Anda terhubung ke basis data, 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 Bronze, yang berisi data bisnis dan telematik mentah dengan transformasi minimal.

Harap diperhatikan: Karena basis data berisi jumlah informasi yang sangat besar, pastikan untuk melakukan kueri percobaan berdasarkan jumlah nilai yang terbatas yang diambil.

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
day_of_week IN (0, 6)  -- Sabtu atau Minggu
    o.is_deleted = false
URUTKAN BERDASARKAN
    o.object_label;

Dapatkan lokasi perangkat terbaru

Ambil data lokasi terkini untuk semua perangkat Anda:

SELECT
    t.device_id,
    o.object_label,
    -- Konversi koordinat bilangan bulat yang diskalakan 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
day_of_week IN (0, 6)  -- Sabtu atau Minggu
    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 dengan 10^7 untuk efisiensi penyimpanan yang lebih baik 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
day_of_week IN (0, 6)  -- Sabtu atau Minggu
    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 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 level 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
day_of_week IN (0, 6)  -- Sabtu atau Minggu
    i.sensor_name = 'fuel'
    DAN t.device_time > (CURRENT_DATE - INTERVAL '7 days')
URUTKAN BERDASARKAN
    o.object_label, t.device_time;

Analisis geospasial

Kendaraan di 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 melingkar
        -- Konversi koordinat dari bilangan bulat yang diskalakan ke desimal
        (
            WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN
                KETIKA z.zone_type = 'circle' MAKA
                    ST_SetSRID(ST_MakePoint(ss.lon, ss.lat), 4326)::geography,
                        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
            ELSE jd.value
        )
day_of_week IN (0, 6)  -- Sabtu atau Minggu
    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 menemui kesalahan, verifikasilah bahwa ekstensi PostGIS diaktifkan di basis data Anda.

Tips optimasi performa

Saat bekerja dengan Cloud Data Warehouse, pertimbangkan teknik optimasi berikut:

1. Gunakan pemfilteran 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: Basis data 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 saat perlu dan cobalah untuk memfilter data sebelum menggabungkan tabel besar.

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

4. Batasi 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 hirarkis: Susun kueri kompleks mengikuti hierarki entitas (dealer → client → user/device → object) untuk join dan pemfilteran yang lebih efisien.

2. Kelola koneksi dengan benar: Tutup koneksi basis data saat 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 familiar dengan skema, Anda dapat mengembangkan kueri yang lebih kompleks untuk memenuhi kebutuhan bisnis spesifik Anda.