Типичные запросы

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

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

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

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

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

SELECT
    o.object_id,
    o.object_label,
    o.model,
    d.device_imei,
    g.group_label
ИЗ
    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
СОРТИРОВАТЬ ПО
    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
ИЗ
    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
СОРТИРОВАТЬ ПО
    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
ИЗ
    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'
ГРУППИРОВАТЬ ПО
    o.object_label, v.vehicle_type, DATE(t.device_time)
СОРТИРОВАТЬ ПО
    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
ИЗ 
    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
СОРТИРОВАТЬ ПО 
    assignment_time;

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

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

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

SELECT
    o.object_label AS vehicle,
    t.device_time,
    i.value::numeric AS fuel_level
ИЗ
    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')
СОРТИРОВАТЬ ПО
    o.object_label, t.device_time;

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

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

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

SELECT
    o.object_label AS vehicle,
    z.zone_label AS geozone,
    t.device_time AS entry_time
ИЗ
    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')
СОРТИРОВАТЬ ПО
    z.zone_label, o.object_label, t.device_time;

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

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

При работе с облачным хранилищем данных рассмотрите следующие методы оптимизации:

1. Используйте фильтрацию по времени: Всегда включайте временной фильтр по 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;

1. Используйте индексы: В базе данных имеются индексы по (device_id, device_time) парам. Стройте запросы таким образом, чтобы по возможности использовать эти индексы.

2. Используйте JOIN выборочно: Объединяйте таблицы только когда это необходимо и старайтесь фильтровать данные до объединения больших таблиц.

3. Преобразование масштабированных целых чисел: Помните, что данные координат хранятся как масштабированные целые числа. Преобразуйте их только в финальном SELECT, а не в WHERE-условиях.

4. Ограничивайте наборы результатов: Всегда используйте LIMIT для исследовательских запросов, чтобы избежать возвращения миллионов строк.

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

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

2. Правильно управляйте подключениями: Закрывайте подключения к базе данных, когда они не используются, особенно в BI-инструментах или плановых скриптах, чтобы избежать блокировок ресурсов или проблем с таймаутом.

Дальнейшие шаги

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