Типовые запросы

Подключившись к базе данных, вы сможете извлекать данные с помощью SQL-запросов. Этот раздел содержит примерные SQL-запросы, которые помогут вам начать работу с Private Telematics Lakehouse. Эти примеры показывают, как получить доступ к данным и анализировать данные из слоя Bronze, который содержит необработанные бизнес- и телематические данные с минимальной трансформацией.

Обратите внимание: поскольку база данных содержит огромный объём информации, убедитесь, что тестовые запросы выполняются с ограничением по количеству извлекаемых значений.

Базовые запросы

Получение базовой информации об объектах

Этот запрос возвращает информацию об объектах (транспортных средствах/активах) в вашей системе:

SELECT
    o.object_id,
    o.object_label,
    o.model,
    d.device_imei,
    g.group_label
FROM
    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
ORDER BY
    o.object_label;

Получить последние местоположения устройств

Извлечь самые последние данные о местоположении для всех ваших устройств:

SELECT
    t.device_id,
    o.object_label,
    -- Преобразовать масштабированные целочисленные координаты обратно в десятичный формат
    t.latitude::float / 10000000 AS latitude,
    t.longitude::float / 10000000 AS longitude,
    t.speed,
    t.device_time
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 > (CURRENT_DATE - INTERVAL '1 day')
    AND t.latitude != 0
    AND t.longitude != 0
ORDER BY
    t.device_id, t.device_time DESC;

Значения координат хранятся как целые числа, масштабированные на 10^7 для повышения эффективности хранения в TimescaleDB. При выполнении запросов делите на 10000000, чтобы преобразовать обратно в стандартный десятичный формат.

Объединение бизнес- и телематических данных

Отчёт об активности транспортных средств

Этот запрос формирует ежедневную сводку активности, объединяя бизнес- и телематические данные:

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
FROM
    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'
GROUP BY
    o.object_label, v.vehicle_type, DATE(t.device_time)
ORDER BY
    o.object_label, DATE(t.device_time);

Назначения водителей и история местоположений

Отслеживайте, какие сотрудники были назначены на какие транспортные средства и их историю местоположений:

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
FROM 
    raw_business_data.driver_history new_row
JOIN 
    raw_business_data.driver_history old_row
    ON new_row.changed_datetime = old_row.changed_datetime
    AND 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
ORDER BY 
    assignment_time;

Анализ данных датчиков

Отслеживание уровня топлива

Этот запрос показывает, как анализировать данные датчика топлива:

SELECT
    o.object_label AS vehicle,
    t.device_time,
    i.value::numeric AS fuel_level
FROM
    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 AND
        i.device_time = t.device_time
WHERE
    i.sensor_name = 'fuel'
    AND t.device_time > (CURRENT_DATE - INTERVAL '7 days')
ORDER BY
    o.object_label, t.device_time;

Геопространственный анализ

Транспортные средства в геозонах

Определите, какие транспортные средства въехали в определённые геозоны:

SELECT
    o.object_label AS vehicle,
    z.zone_label AS geozone,
    t.device_time AS entry_time
FROM
    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
        -- Вычислить, находится ли точка внутри круговой зоны
        -- Преобразовать координаты из масштабированных целых чисел в десятичные
        (
            CASE
                WHEN z.zone_type = 'circle' THEN
                    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
                    )
                ELSE false
            END
        )
WHERE
    t.device_time > (CURRENT_DATE - INTERVAL '1 day')
ORDER BY
    z.zone_label, o.object_label, t.device_time;

Этот запрос использует пространственные функции PostGIS. Если вы сталкиваетесь с ошибками, проверьте, включено ли расширение PostGIS в вашей базе данных.

Советы по оптимизации производительности

При работе с Cloud Data Warehouse рассмотрите следующие методы оптимизации:

Используйте фильтрацию по времени: Всегда включайте временной фильтр по device_time или record_added_at столбцам, чтобы ограничить объём просматриваемых данных. Хорошая практика:

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

Избегайте этого (сканирует всю таблицу)

SELECT * FROM raw_telematics_data.tracking_data_core;

Используйте индексы: В базе данных есть индексы по (device_id, device_time) парам. Структурируйте ваши запросы так, чтобы по возможности использовать эти индексы.
Используйте соединения выборочно: Соединяйте таблицы только при необходимости и старайтесь фильтровать данные до объединения больших таблиц.
Преобразование масштабированных целых чисел: Помните, что данные координат хранятся в виде масштабированных целых чисел. Преобразовывайте их только в финальном SELECT, а не в WHERE-условиях.
Ограничивайте наборы результатов: Всегда используйте LIMIT для исследовательских запросов, чтобы избежать возврата миллионов строк.

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

Используйте иерархические связи: Структурируйте сложные запросы, следуя иерархии сущностей (dealer → client → user/device → object) для более эффективных соединений и фильтрации.
Правильное управление соединениями: Закрывайте подключения к базе данных, когда они не используются, особенно в BI-инструментах или планируемых скриптах, чтобы избежать блокировок ресурсов или проблем с таймаутами.

Следующие шаги

Эти примеры предоставляют отправную точку для работы с вашими данными. По мере того как вы будете лучше знакомиться со схемой, вы сможете разрабатывать более сложные запросы для удовлетворения специфических бизнес-требований.

ПредыдущаяSQL Recipe Book СледующаяЛогистика

Последнее обновление 23 дня назад

Это было полезно?