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

Подключившись к базе данных, вы сможете извлекать данные с помощью 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 учитывайте следующие методы оптимизации:

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

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

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