> For the complete documentation index, see [llms.txt](https://navixy.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://navixy.com/docs/analytics/ru/example-queries/logistics.md).

# Логистика

{% hint style="warning" %}
Включите **IoT Query** перед использованием данных для построения комплексной аналитики. Если у вас его еще нет, свяжитесь с нами для получения информации об активации - <iotquery@navixy.com>
{% endhint %}

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

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

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

## **Отслеживание активов без движения** <a href="#asset-tracking-without-movement" id="asset-tracking-without-movement"></a>

Этот сценарий определяет активы (например, транспортные средства или прицепы), которые не изменяли свои GPS сравните **минимальные и максимальные координаты** за период. Если оба значения находятся в очень узком диапазоне (порог допущения, например ±0,01 градуса), мы помечаем актив как неподвижный. Запрос также выполняет соединение с таблицами objects и vehicles в raw\_business\_data, чтобы получить осмысленные метки активов для результата.

{% code expandable="true" %}

```sql
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 -- exclude devices with very sparse data
	AND((max_lat - min_lat) <= 2000 -- ~10 meters
		OR
      	(max_lon - min_lon) <= 1000)  -- ~10 meters
    		)
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;
```

{% endcode %}

## **Анализ простоя транспортного средства** <a href="#vehicle-downtime-analysis" id="vehicle-downtime-analysis"></a>

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

Основой анализа простоя является использование таблицы vehicle\_service\_tasks из raw\_business\_data, которая фиксирует как **плановые, так и внеплановые события технического обслуживания**. Каждая задача содержит start\_date и end\_date, которые обозначают период простоя. При фильтрации по **завершенным задачам обслуживания**, мы можем вычислить точную продолжительность, в течение которой каждое транспортное средство не эксплуатировалось.

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

{% code expandable="true" %}

```sql
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;
```

{% endcode %}

## **Обнаружение отклонений от маршрута** <a href="#route-deviation-detection" id="route-deviation-detection"></a>

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

В этой логике фактические GPS-координаты транспортного средства из tracking\_data\_core (в схеме raw\_telematics\_data) сравниваются с заранее определенными географическими зонами из таблицы zones в raw\_business\_data. Эти зоны представляют назначенные маршруты или их сегменты. Используя геометрические сравнения через ST\_DWithin, мы определяем, находится ли точка внутри или вне буферной зоны маршрута.

Запрос соединяет каждую GPS-точку с каждой известной зоной маршрута с помощью пространственного **CROSS JOIN**, а затем применяет ST\_DWithin() для проверки, находилось ли транспортное средство в пределах разрешенного коридора. Мы выделяем строки, где транспортное средство было **вне всех геозон маршрутов** и помечаем их как отклонения. Итоговый вывод содержит список этих отклонений, включая устройство, метку времени, метку транспортного средства и расстояние, на котором точка находилась от ближайшего центра зоны.

{% code expandable="true" %}

```sql
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;
```

{% endcode %}

## **Сводка моточасов на одно транспортное средство / водителя / день (последние 7 дней)** <a href="#engine-hours-summary-per-vehicle-driver-day-last-7-days" id="engine-hours-summary-per-vehicle-driver-day-last-7-days"></a>

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

Таблица states в raw\_telematics\_data хранит **индикаторы состояния двигателя во временном ряду**, обычно с state\_name, равным 'ignition', и значением 1 (включено) или 0 (выключено). Чтобы вычислить моточасы, мы находим все переходы с отметками времени для каждого устройства и рассчитываем длительности, когда двигатель был включен (1).

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

{% code expandable="true" %}

```sql
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  -- transitions from ON to OFF
),
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;

```

{% endcode %}

## **События нарушений температуры (и влажности) за последние 7 дней** <a href="#temperature-and-humidity-violation-events-in-the-last-7-days" id="temperature-and-humidity-violation-events-in-the-last-7-days"></a>

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

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

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

{% code expandable="true" %}

```sql
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 'Высокая температура'
           WHEN cd.sensor_type = 'temperature' AND cd.calibrated_value < 0 THEN 'Низкая температура'
           WHEN cd.sensor_type = 'humidity' AND cd.calibrated_value > 80 THEN 'Высокая влажность'
           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;

```

{% endcode %}

## **Несанкционированные остановки (последние 24 часа)** <a href="#unauthorized-stops-last-24-hours" id="unauthorized-stops-last-24-hours"></a>

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

Запрос анализирует **точки местоположения с низкой или нулевой скоростью** с помощью The query uses the tracking\_data\_core table from raw\_telematics\_data to extract time-series location data and speed. A stop is detected when **скорость падает ниже 3 км/ч** в течение **более 2 минут**. Используя функции LAG и LEAD, запрос сегментирует эти периоды низкой скорости, чтобы определить время начала и окончания остановки.

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

{% code expandable="true" %}

```sql
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  -- not inside any known zone
      AND EXTRACT(EPOCH FROM (ss.stop_end_time - ss.stop_start_time)) > 120  -- minimum 2 minutes
),
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;
```

{% endcode %}

## **Обнаружение использования в нерабочее время** <a href="#off-hour-usage-detection" id="off-hour-usage-detection"></a>

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

Логика построена на таблице tracking\_data\_core из raw\_telematics\_data, которая фиксирует события GPS с временными метками по каждому устройству. Мы определяем локальные **день недели** и **час использования** по каждой записи device\_time и отфильтровываем записи **вне заданного рабочего окна** (то есть до 9:00, после 18:00 или в любое время по выходным).

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

{% code expandable="true" %}

```sql
WITH gps_events AS (
    SELECT
        td.device_id,
        td.device_time,
        EXTRACT(DOW FROM td.device_time) AS day_of_week, -- 0=Sunday, 6=Saturday
        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)  -- Saturday or Sunday
        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;
```

{% endcode %}

## **Количество рейсов в день** <a href="#trip-counts-per-day" id="trip-counts-per-day"></a>

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

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

Каждый рейс включает:

* Один **временная метка и местоположение начала** (первая движущаяся точка)
* An **временная метка и местоположение окончания** (последняя движущаяся точка перед остановкой)
* The **Расстояние по формуле гаверсинуса** между начальной и конечной точками

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

{% code expandable="true" %}

```sql
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;
```

{% endcode %}

## **Количество пробега по каждому транспортному средству за день (последние 7 дней)** <a href="#mileage-count-per-vehicle-per-day-last-7-days" id="mileage-count-per-vehicle-per-day-last-7-days"></a>

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

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

1. **Сортируем точки GPS хронологически** по каждому устройству.
2. **Рассчитываем расстояние между последовательными точками** с использованием формулы гаверсинуса.
3. **Суммируем расстояния за день по каждому устройству** чтобы получить общий пробег.

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

{% code expandable="true" %}

```sql
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;
```

{% endcode %}

## **Отчет журнала событий транспортных средств** <a href="#vehicle-event-log-report" id="vehicle-event-log-report"></a>

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

Основным источником является таблица states в схеме raw\_telematics\_data. Каждая строка включает: device\_id (источник события), device\_time (временная метка), state\_name (метка события) и value (состояние или измерение).

Чтобы создать пригодный для использования отчет:

1. Мы извлекаем все записи за последние 7 дней.
2. Группируем их по **тип события**, **транспортному средству**, и **дате** чтобы предоставить **количество раз, когда произошло каждое событие** и **время его возникновения**.
3. Дополняем результаты метаданными транспортного средства (vehicle\_label, registration\_number, object\_label) через objects и vehicles.

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

{% code expandable="true" %}

```sql
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;
```

{% endcode %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://navixy.com/docs/analytics/ru/example-queries/logistics.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.