Логистика

Кейс по логистике и сборник SQL-рецептов

Включить IoT Query перед использованием данных для построения всесторонней аналитики. Если у вас его еще нет, свяжитесь с нами для получения деталей активации - [email protected]

Логистика — это сложная экосистема, включающая координацию транспорта, складских операций, управления запасами и выполнения доставок. Интеграция телематики в логистические процессы позволяет компаниям собирать данные в реальном времени о транспортных средствах, водителях, маршрутах и состоянии груза, что значительно улучшает принятие решений и операционную эффективность.

Navixy IoT Query, благодаря своим мощным возможностям приема данных и анализа временных рядов, поддерживает цифровую трансформацию логистических операций, обеспечивая глубокую видимость на каждом этапе жизненного цикла. Ее надежные возможности приема телематических данных предоставляют всестороннюю видимость этих операций. Данные GPS в реальном времени, диагностика данных датчиков, геозонирование и аналитика сенсоров позволяют операторам логистики цифровизировать рабочие процессы, автоматизировать контроль и принимать обоснованные решения.

Фаза жизненного цикла	Цели	Покрываемые сценарии / Рецепты
Управление маршрутами	Оптимизировать маршрутизацию транспортных средств, обеспечить эффективную диспетчеризацию и сократить задержки	Количество поездок в день Счетчик пробега на транспортное средство в день (за последние 7 дней)
Мониторинг груза	Обеспечить надлежащие условия перевозки для чувствительных грузов	События нарушения температуры (и влажности) за последние 7 дней
Эксплуатация транспортных средств	Отслеживать использование парка, обеспечивать обслуживание и снижать время простоя	Итог по моточасам по транспортному средству / водителю / дню (за последние 7 дней) Анализ простоев транспортных средств Отслеживание активов без перемещения
Безопасность и охрана маршрута	Обнаруживать злоупотребления, несанкционированную деятельность и нарушения безопасности	Обнаружение отклонений от маршрута — несанкционированные остановки (за последние 24 часа) Обнаружение использования в нерабочее время
Управление соответствием	Контролировать поведение водителей, обеспечивать соблюдение политик и операционное соответствие	Итог по моточасам по транспортному средству / водителю / дню (за последние 7 дней) Обнаружение использования в нерабочее время
Анализ после доставки	Оценивать операционную эффективность и историческую производительность	Отчет журнала событий транспортного средства Счетчик пробега на транспортное средство в день (за последние 7 дней) Количество поездок в день Отслеживание активов без перемещения

Отслеживание активов без перемещения

Этот сценарий выявляет активы (например, транспортные средства или прицепы), которые не изменили свои GPS сравнить минимальные и максимальные координаты в течение периода. Если оба значения находятся в очень узком диапазоне (порог допуска, например ±0.01 градуса), мы помечаем актив как неподвижный. Запрос также объединяется с таблицами objects и vehicles в raw_business_data, чтобы получить информативные метки активов для вывода результата.

WITH gps_bounds AS (
    SELECT
        td.device_id,
        MIN(td.latitude) AS min_lat,
        MAX(td.latitude) AS max_lat,
        MIN(td.longitude) AS min_lon,
        MAX(td.longitude) AS max_lon,
        COUNT(*) AS location_records
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '48 hours'
    GROUP BY td.device_id
),
stationary_devices AS (
    SELECT
        device_id
    FROM gps_bounds
    WHERE location_records > 10 -- исключить устройства с очень разреженными данными
	AND((max_lat - min_lat) <= 2000 -- ~10 метров
		OR
      	(max_lon - min_lon) <= 1000)  -- ~10 метров
    		)
SELECT
    v.vehicle_id,
    v.vehicle_label,
    o.object_id,
    o.object_label,
    sd.device_id,
    gb.min_lat / 1e7 AS latitude,
    gb.min_lon / 1e7 AS longitude,
    gb.location_records
FROM stationary_devices sd
JOIN gps_bounds gb ON sd.device_id = gb.device_id
JOIN raw_business_data.objects o ON o.device_id = sd.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
ORDER BY gb.location_records DESC;

Анализ простоев транспортных средств

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

Основой анализа простоев служит использование таблицы vehicle_service_tasks из raw_business_data, которая регистрирует как плановые, так и неплановые события обслуживания. Каждая задача содержит start_date и end_date, представляющие период простоя. Фильтруя по завершенным задачам обслуживания, мы можем вычислить точную продолжительность, в течение которой каждое транспортное средство было выведено из эксплуатации.

Запрос рассчитывает общий простой по каждому транспортному средству, суммируя продолжительности всех его задач обслуживания (в часах). Он также позволяет разделять плановое и неплановое обслуживание с помощью флага is_unplanned. Чтобы сделать результаты более практичными, он объединяется с таблицей vehicles для включения меток транспортных средств, номеров регистрации и информации о модели.

WITH downtime_durations AS (
    SELECT
        vst.vehicle_id,
        vst.is_unplanned,
        vst.start_date,
        vst.end_date,
        EXTRACT(EPOCH FROM (vst.end_date - vst.start_date))/3600 AS downtime_hours
    FROM raw_business_data.vehicle_service_tasks vst
    WHERE vst.status = 'done'
      AND vst.start_date IS NOT NULL
      AND vst.end_date IS NOT NULL
)
SELECT
    v.vehicle_id,
    v.vehicle_label,
    v.registration_number,
    v.model,
    COUNT(dd.*) AS total_service_events,
    SUM(dd.downtime_hours) AS total_downtime_hours,
    SUM(dd.downtime_hours) FILTER (WHERE dd.is_unplanned = TRUE) AS unplanned_downtime_hours,
    SUM(dd.downtime_hours) FILTER (WHERE dd.is_unplanned = FALSE) AS planned_downtime_hours
FROM downtime_durations dd
JOIN raw_business_data.vehicles v ON v.vehicle_id = dd.vehicle_id
GROUP BY v.vehicle_id, v.vehicle_label, v.registration_number, v.model
ORDER BY total_downtime_hours DESC;

Обнаружение отклонений от маршрута

Этот сценарий выявляет случаи, когда транспортные средства отклоняются от назначенных или ожидаемых маршрутов — особенно от геозонированных зон или коридоров доставки. Отслеживание таких отклонений помогает обеспечить соблюдение маршрутов, снизить задержки, обнаружить рискованное поведение водителя и поддерживать уровень обслуживания доставок (SLA).

Эта логика сравнивает фактические GPS‑позиции транспортного средства из tracking_data_core (в схеме raw_telematics_data) с предопределенными географическими зонами из таблицы zones в raw_business_data. Эти зоны представляют назначенные маршруты или сегменты маршрутов. Используя геометрические сравнения через ST_DWithin, мы определяем, находится ли точка внутри или вне буферной зоны маршрута.

Запрос объединяет каждую GPS‑позицию с каждой известной зоной маршрута с помощью пространственного CROSS JOIN, затем применяет ST_DWithin() для проверки, находилось ли транспортное средство внутри разрешенного коридора. Мы изолируем строки, где транспортное средство было вне всех геозонированных маршрутов и помечаем их как отклонения. В финальном выводе перечислены эти отклонения, включая устройство, отметку времени, метку транспортного средства и расстояние точки до центра ближайшей зоны.

WITH positions AS (
    SELECT
        td.device_id,
        td.device_time,
        o.object_id,
        o.object_label,
        z.zone_id,
        z.zone_label,
        ST_SetSRID(ST_MakePoint(td.longitude / 1e7, td.latitude / 1e7), 4326)::geography AS gps_point,
        ST_Buffer(
            ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography,
            z.radius
        ) AS route_buffer
    FROM raw_telematics_data.tracking_data_core td
    JOIN raw_business_data.objects o ON td.device_id = o.device_id
    CROSS JOIN raw_business_data.zones z
    WHERE td.device_time >= now() - interval '2 days'
),
evaluated AS (
    SELECT
        device_id,
        device_time,
        object_id,
        object_label,
        zone_id,
        zone_label,
        NOT ST_DWithin(gps_point, route_buffer, 0) AS is_deviation,
        ST_Distance(gps_point, route_buffer) AS deviation_distance_meters
    FROM positions
),
deviations_only AS (
    SELECT *
    FROM evaluated
    WHERE is_deviation = true
)
SELECT
    device_id,
    object_label,
    zone_label,
    device_time,
    deviation_distance_meters
FROM deviations_only
ORDER BY device_time DESC;

Итог по моточасам по транспортному средству / водителю / дню (за последние 7 дней)

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

Таблица states в raw_telematics_data фиксирует индикаторы состояния двигателя во временных рядах, как правило, с state_name типа 'ignition' и значением 1 (включено) или 0 (выключено). Чтобы вычислить моточасы, мы находим все отметки времени переходов для каждого устройства и рассчитываем продолжительности, когда двигатель был включен (1).

Чтобы связать активность двигателя как с транспортными средствами, так и водителями, мы используем таблицы objects, vehicles и driver_history из raw_business_data. Мы ассоциируем каждую запись состояния с текущим водителем на этом объекте (через историю назначений водителей) и с соответствующим транспортным средством. Затем мы группируем данные по дню, транспортному средству и водителю, суммируя общее активное время работы двигателя (в часах).

WITH inputs_core AS (
   SELECT
       i.device_id,
       i.device_time,
       i.event_id,
       i.record_added_at,
       i.sensor_name,
       i.value
   FROM raw_telematics_data.inputs i
   WHERE i.device_time >= now() - interval '7 days'
),
clear_inputs AS (
   SELECT
       i.device_id,
       i.device_time,
       i.event_id,
       i.record_added_at,
       i.sensor_name,
       i.value,
       LAG(i.device_time) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS prev_time,
       LAG(i.value::int) OVER (PARTITION BY i.device_id ORDER BY i.device_time) AS prev_status
   FROM inputs_core i
   LEFT JOIN raw_business_data.sensor_description sd
       ON sd.input_label = i.sensor_name
      AND sd.device_id = i.device_id
   WHERE sd.sensor_type = 'engine'
     AND i.value = '1'
),
engine_on_periods AS (
   SELECT
       device_id,
       prev_time AS engine_on_time,
       device_time AS engine_off_time,
       device_time::date AS activity_day,
       EXTRACT(EPOCH FROM (device_time - prev_time)) / 3600 AS engine_hours
   FROM clear_inputs
   WHERE value::int = 0 AND prev_status = 1  -- переходы с ВКЛ на ВЫКЛ
),
enriched_with_objects AS (
   SELECT
       eop.*,
       o.object_id,
       o.object_label,
       v.vehicle_id,
       v.vehicle_label,
       v.registration_number
   FROM engine_on_periods eop
   JOIN raw_business_data.objects o ON o.device_id = eop.device_id
   LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
),
assigned_drivers AS (
   SELECT
       ewo.*,
       dh.new_employee_id as employee_id
   FROM enriched_with_objects ewo
   LEFT JOIN LATERAL (
       SELECT d.*
       FROM raw_business_data.driver_history d
       WHERE d.object_id = ewo.object_id
         AND d.changed_datetime <= ewo.engine_on_time
       ORDER BY d.changed_datetime DESC
       LIMIT 1
   ) dh ON true
)
SELECT
   activity_day,
   vehicle_label,
   registration_number,
   object_label,
   e.first_name || ' ' || e.last_name AS driver_name,
   SUM(engine_hours) AS total_engine_hours
FROM assigned_drivers ad
LEFT JOIN raw_business_data.employees e ON ad.employee_id = e.employee_id
GROUP BY activity_day, vehicle_label, registration_number, object_label, driver_name
ORDER BY activity_day DESC, vehicle_label;

События нарушения температуры (и влажности) за последние 7 дней

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

Этот запрос извлекает данные с входов датчиков из таблицы inputs в схеме raw_telematics_data. Каждая строка представляет собой показание датчика (например, температура, влажность), зафиксированное в конкретный момент времени устройством. Мы фильтруем эти записи, включая только те, которые поступили за последние 7 дней.

Основная логика фильтрации основана на шаблонах имен датчиков и сравнении их числовых значений с порогами (например, >25°C для температуры, >80% для влажности). Поскольку value хранится как текст, мы приводим его к числовому типу перед применением условий порога. Чтобы обогатить результаты, мы объединяемся с таблицей objects для получения меток транспортных средств или активов, что повышает понятность для менеджеров парка.

WITH recent_sensor_data AS (
   SELECT
       i.device_id,
       i.device_time,
       i.sensor_name,
       i.value::float AS value
   FROM raw_telematics_data.inputs i
   WHERE i.device_time >= now() - interval '1 hour'
),
sensor_meta AS (
   SELECT
       sd.device_id,
       sd.input_label,
       sd.sensor_id,
       sd.sensor_type,
       sd.calibration_data
   FROM raw_business_data.sensor_description sd
),
joined_data AS (
   SELECT
       rsd.*,
       sm.sensor_id,
       sm.sensor_type,
       sm.calibration_data
   FROM recent_sensor_data rsd
   LEFT JOIN sensor_meta sm
       ON rsd.device_id = sm.device_id
      AND rsd.sensor_name = sm.input_label
),
calibrated_data AS (
   SELECT
       jd.device_id,
       jd.device_time,
       jd.sensor_name,
       jd.value,
       jd.sensor_id,
       jd.sensor_type,
       CASE
           WHEN cd_low.cal_value IS NOT NULL AND cd_high.cal_value IS NOT NULL THEN
               CASE
                   WHEN cd_high.cal_value = cd_low.cal_value THEN cd_low.cal_volume
                   ELSE cd_low.cal_volume +
                       ((jd.value - cd_low.cal_value) / NULLIF(cd_high.cal_value - cd_low.cal_value, 0))
                       * (cd_high.cal_volume - cd_low.cal_volume)
               END
           ELSE jd.value
       END AS calibrated_value
   FROM joined_data jd
   LEFT JOIN LATERAL (
       SELECT
           (p->>'in')::float  AS cal_value,
           (p->>'out')::float AS cal_volume
       FROM jsonb_array_elements(jd.calibration_data) AS p
       WHERE (p->>'in')::float <= jd.value
       ORDER BY (p->>'in')::float DESC
       LIMIT 1
   ) cd_low ON TRUE
   LEFT JOIN LATERAL (
       SELECT
           (p->>'in')::float  AS cal_value,
           (p->>'out')::float AS cal_volume
       FROM jsonb_array_elements(jd.calibration_data) AS p
       WHERE (p->>'in')::float >= jd.value
       ORDER BY (p->>'in')::float ASC
       LIMIT 1
   ) cd_high ON TRUE
),
violations AS (
   SELECT
       cd.device_id,
       cd.device_time,
       cd.sensor_name,
       cd.calibrated_value,
       CASE
           WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value > 25 THEN 'High Temperature'
           WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Low Temperature'
           WHEN cd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'High Humidity'
           ELSE NULL
       END AS violation_type
   FROM calibrated_data cd
   WHERE cd.sensor_type IN ('temperature', 'humidity')
)
SELECT
   v.vehicle_label,
   o.object_label,
   v.registration_number,
   v.model,
   vio.device_time,
   vio.sensor_name,
   vio.calibrated_value,
   vio.violation_type
FROM violations vio
JOIN raw_business_data.objects o
  ON o.device_id = vio.device_id
LEFT JOIN raw_business_data.vehicles v
  ON v.object_id = o.object_id
ORDER BY vio.device_time DESC;

Неавторизованные остановки (за последние 24 часа)

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

Запрос анализирует точки местоположения с низкой или нулевой скоростью используя Запрос использует таблицу tracking_data_core из raw_telematics_data для извлечения данных временных рядов о местоположении и скорости. Остановка фиксируется, когда скорость падает ниже 3 км/ч в течение более 2 минут. С помощью функций LAG и LEAD запрос сегментирует эти периоды низкой скорости, чтобы определить время начала и окончания остановки.

Чтобы обнаружить неавторизованные остановки, он исключает местоположения, которые попадают в известные геозонированные зоны (таблица zones) с использованием ST_DWithin PostGIS. Только остановки вне буфера какой‑либо зоны отображаются в отчете. Результат включает ID транспортного средства, метку объекта, регистрацию, отметки времени, продолжительность и координаты каждой остановки.

WITH speed_data AS (
    SELECT
        td.device_id,
        td.device_time,
        td.speed / 100.0 AS speed_kph,
        td.latitude / 1e7 AS lat,
        td.longitude / 1e7 AS lon
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '1 day'
),
low_speed_points AS (
    SELECT
        *,
        LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_time,
        LAG(speed_kph) OVER (PARTITION BY device_id ORDER BY device_time) AS prev_speed
    FROM speed_data
),
stops_marked AS (
    SELECT *,
        CASE 
            WHEN speed_kph < 3 AND (prev_speed >= 3 OR prev_speed IS NULL) THEN 1 
            ELSE 0 
        END AS stop_start,
        CASE 
            WHEN speed_kph >= 3 AND prev_speed < 3 THEN 1 
            ELSE 0 
        END AS stop_end
    FROM low_speed_points
),
stop_segments AS (
    SELECT
        device_id,
        device_time AS stop_start_time,
        LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS stop_end_time,
        lat,
        lon
    FROM stops_marked
    WHERE stop_start = 1
),
unauthorized_stops AS (
    SELECT
        ss.*,
        EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time))/60 AS stop_duration_min
    FROM stop_segments ss
    LEFT JOIN raw_business_data.zones z ON
        ST_DWithin(
            ST_SetSRID(ST_MakePoint(ss.lon, ss.lat), 4326)::geography,
            ST_SetSRID(ST_MakePoint(z.circle_center_longitude, z.circle_center_latitude), 4326)::geography,
            z.radius
        )
    WHERE z.zone_id IS NULL  -- не внутри какой‑либо известной зоны
      AND EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time)) > 120  -- минимум 2 минуты
),
with_metadata AS (
    SELECT
        us.*,
        o.object_label,
        v.vehicle_label,
        v.registration_number
    FROM unauthorized_stops us
    JOIN raw_business_data.objects o ON o.device_id = us.device_id
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
)
SELECT
    vehicle_label,
    registration_number,
    object_label,
    stop_start_time,
    stop_end_time,
    ROUND(stop_duration_min, 1) AS stop_duration_minutes,
    ROUND(lat, 6) AS latitude,
    ROUND(lon, 6) AS longitude
FROM with_metadata
ORDER BY stop_start_time DESC;

Обнаружение использования в нерабочее время

Этот сценарий выявляет случаи, когда транспортные средства эксплуатируются за пределами нормальных рабочих часов — определенных здесь как с понедельника по пятницу, 09:00–18:00. Такие обнаружения важны для выявления несанкционированного использования, выявления потенциального злоупотребления транспортным средством, и повышения безопасности активов.

Логика построена на таблице tracking_data_core из raw_telematics_data, которая регистрирует временные отметки GPS‑событий для каждого устройства. Мы извлекаем локальные день недели и час использования из каждой записи device_time и фильтруем записи вне определенного рабочего окна (т.е. до 9:00, после 18:00 или в любое время в выходные).

Для ясности мы обогащаем GPS‑данные метаданными объектов и транспортных средств из raw_business_data (например, метка транспортного средства, регистрация, ID объекта). Для более содержательных сводок мы опционально агрегируем использование, подсчитывая сколько событий в нерабочее время произошло для каждого транспортного средства и когда они произошли. Это помогает выявлять закономерности или повторных нарушителей.

WITH gps_events AS (
    SELECT
        td.device_id,
        td.device_time,
        EXTRACT(DOW FROM td.device_time) AS day_of_week, -- 0=Воскресенье, 6=Суббота
        EXTRACT(HOUR FROM td.device_time) AS hour_of_day,
        td.latitude / 1e7 AS latitude,
        td.longitude / 1e7 AS longitude
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '7 days'
),
off_hour_events AS (
    SELECT
        ge.*
    FROM gps_events ge
    WHERE 
        day_of_week IN (0, 6)  -- Суббота или Воскресенье
        OR hour_of_day < 9 
        OR hour_of_day >= 18
),
with_metadata AS (
    SELECT
        o.object_label,
        v.vehicle_label,
        v.registration_number,
        e.first_name || ' ' || e.last_name AS driver_name,
        o.device_id,
        o.object_id,
        o.create_datetime,
        ge.device_time,
        ge.latitude,
        ge.longitude,
        ge.day_of_week,
        ge.hour_of_day
    FROM off_hour_events ge
    JOIN raw_business_data.objects o ON ge.device_id = o.device_id
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
    LEFT JOIN raw_business_data.driver_history dh ON dh.object_id = o.object_id 
        AND dh.changed_datetime <= ge.device_time
    LEFT JOIN raw_business_data.employees e ON e.employee_id = dh.new_employee_id
)
SELECT
    object_label,
    vehicle_label,
    registration_number,
    driver_name,
    device_time,
    TO_CHAR(device_time, 'Day') AS weekday,
    TO_CHAR(device_time, 'HH24:MI') AS time_of_event,
    ROUND(latitude::numeric, 6) AS lat,
    ROUND(longitude::numeric, 6) AS lon
FROM with_metadata
ORDER BY device_time DESC;

Подсчет поездок в день

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

Чтобы определить поездку, мы используем изменение состояния движения транспортного средства — т.е. переход от остановки к движению и обратно к остановке. Используя значения скорости из таблицы tracking_data_core, запрос сегментирует данные на основе этих переходов. Поездка идентифицируется как непрерывный период движения где скорость остается выше порога (например, >5 км/ч).

Каждая поездка включает:

• A метка времени начала и местоположение (первая точка в движении)

• An метка времени окончания и местоположение (последняя точка в движении перед остановкой)

• The Расстояние по формуле Хаверсина между начальным и конечным местоположениями

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

WITH base_points AS (
    SELECT
        td.device_id,
        td.device_time,
        td.latitude / 1e7 AS lat,
        td.longitude / 1e7 AS lon,
        td.speed / 100.0 AS speed_kph,
        LEAD(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS next_speed,
        LAG(td.speed / 100.0) OVER (PARTITION BY td.device_id ORDER BY td.device_time) AS prev_speed
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '7 days'
),
trip_segments AS (
    SELECT
        *,
        CASE 
            WHEN speed_kph >= 5 AND (prev_speed < 5 OR prev_speed IS NULL) THEN 'start'
            WHEN speed_kph < 5 AND prev_speed >= 5 THEN 'end'
        END AS trip_marker
    FROM base_points
),
trip_points AS (
    SELECT
        device_id,
        device_time AS trip_start_time,
        lat AS start_lat,
        lon AS start_lon,
        LEAD(device_time) OVER (PARTITION BY device_id ORDER BY device_time) AS trip_end_time,
        LEAD(lat) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lat,
        LEAD(lon) OVER (PARTITION BY device_id ORDER BY device_time) AS end_lon
    FROM trip_segments
    WHERE trip_marker = 'start'
),
trip_metrics AS (
    SELECT
        tp.device_id,
        tp.trip_start_time,
        tp.trip_end_time,
        tp.start_lat,
        tp.start_lon,
        tp.end_lat,
        tp.end_lon,
        tp.trip_start_time::date AS trip_day,
        -- Приблизительная дистанция, рассчитанная по формуле Хаверсина (в км)
        111 * SQRT(POWER(tp.end_lat - tp.start_lat, 2) + POWER((tp.end_lon - tp.start_lon) * COS(RADIANS(tp.start_lat)), 2))::numeric AS distance_km
    FROM trip_points tp
    WHERE tp.trip_end_time IS NOT NULL
)
SELECT
    v.vehicle_label,
    v.registration_number,
    tm.device_id,
    tm.trip_day,
    COUNT(*) AS trip_count,
    ROUND(SUM(tm.distance_km), 2) AS total_distance_km
FROM trip_metrics tm
JOIN raw_business_data.objects o ON o.device_id = tm.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
GROUP BY v.vehicle_label, v.registration_number, tm.device_id, tm.trip_day
ORDER BY tm.trip_day DESC, v.vehicle_label;

Подсчёт пробега по каждому транспортному средству в день (последние 7 дней)

В этом примере рассчитывается ежедневный пробег (в километрах) для каждого транспортного средства за последние 7 дней. Это является основой для отслеживания использования транспортных средств, мониторинга топливной эффективности, планирования обслуживания, а также выявления недоиспользования или переиспользования.

Мы извлекаем все GPS-записи из tracking_data_core за последние 7 дней. Каждая GPS-точка содержит метку времени, широту и долготу. Для каждого транспортного средства и каждого дня мы:

1. Сортируем GPS-точки хронологически по устройству.

2. Вычисляем расстояние между последовательными точками с использованием формулы Хаверсина.

3. Суммируем расстояния по дням для каждого устройства чтобы получить общий пробег.

Этот подход обеспечивает высокую точность без опоры на внешние датчики одометра. При необходимости запрос объединяет данные с объектами и vehicles для обогащения результатов метаданными активов.

WITH gps_points AS (
    SELECT
        td.device_id,
        td.device_time,
        td.device_time::date AS trip_day,
        td.latitude / 1e7 AS lat,
        td.longitude / 1e7 AS lon,
        LAG(td.latitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS prev_lat,
        LAG(td.longitude / 1e7) OVER (PARTITION BY td.device_id, td.device_time::date ORDER BY td.device_time) AS prev_lon
    FROM raw_telematics_data.tracking_data_core td
    WHERE td.device_time >= now() - interval '7 days'
),
distances AS (
    SELECT
        device_id,
        trip_day,
        -- Приближённая формула Хаверсина в км
        111 * SQRT(POWER(lat - prev_lat, 2) + POWER((lon - prev_lon) * COS(RADIANS(lat)), 2)) AS segment_distance_km
    FROM gps_points
    WHERE prev_lat IS NOT NULL AND prev_lon IS NOT NULL
      AND ABS(lat - prev_lat) < 1 AND ABS(lon - prev_lon) < 1 -- исключить выбросы
)
SELECT
    v.vehicle_label,
    v.registration_number,
    o.object_label,
    d.device_id,
    d.trip_day,
    ROUND(SUM(d.segment_distance_km)::numeric, 2) AS mileage_km
FROM distances d
JOIN raw_business_data.objects o ON o.device_id = d.device_id
LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
GROUP BY v.vehicle_label, v.registration_number, o.object_label, d.device_id, d.trip_day
ORDER BY d.trip_day DESC, v.vehicle_label;

Отчёт журнала событий транспортного средства

В этом примере предоставляется исчерпывающий отчёт обо всех событиях, связанных с транспортными средствами (например, зажигание, открытие двери, резкое торможение и т.д.) по всему парку. В отчёт включены тип события, метка времени, и контекст транспортного средства, что позволяет командам эксплуатации проводить аудит поведения, отслеживать аномальную активность и формировать оповещения и аналитику.

Основным источником является таблица states из схемы raw_telematics_data. Каждая строка содержит: device_id (источник события), device_time (метка времени), state_name (метка события) и value (состояние или измерение).

Для создания удобного отчёта:

1. Мы извлекаем все записи за последние 7 дней.

2. Группируем их по тип события, транспортному средству, и дате чтобы предоставить подсчёт того, как часто происходило каждое событие и когда оно происходило.

3. Обогащаем результаты метаданными транспортного средства (vehicle_label, registration_number, object_label) через objects и vehicles.

Это даёт ежедневную хронологию событий по всему парку — необходимую для диагностики, анализа поведения и проактивного обслуживания.

WITH raw_events AS (
    SELECT
        s.device_id,
        s.device_time,
        s.device_time::date AS event_day,
        s.state_name,
        s.value
    FROM raw_telematics_data.states s
    WHERE s.device_time >= now() - interval '7 days'
),
with_vehicle_info AS (
    SELECT
        re.device_id,
        re.event_day,
        re.device_time,
        re.state_name,
        re.value,
        v.vehicle_label,
        v.registration_number,
        o.object_label
    FROM raw_events re
    JOIN raw_business_data.objects o ON o.device_id = re.device_id
    LEFT JOIN raw_business_data.vehicles v ON v.object_id = o.object_id
),
event_summary AS (
    SELECT
        event_day,
        state_name,
        vehicle_label,
        registration_number,
        object_label,
        COUNT(*) AS event_count,
        MIN(device_time) AS first_occurred,
        MAX(device_time) AS last_occurred
    FROM with_vehicle_info
    GROUP BY event_day, state_name, vehicle_label, registration_number, object_label
)
SELECT
    event_day,
    vehicle_label,
    registration_number,
    object_label,
    state_name AS event_type,
    event_count,
    first_occurred,
    last_occurred
FROM event_summary
ORDER BY event_day DESC, vehicle_label, state_name;