> 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).

# Логистика

Шаблоны SQL-запросов для аналитики логистики и транспорта, охватывающие оптимизацию маршрутов, мониторинг грузов, использование автопарка и эффективность доставок

{% 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 compare the **минимальные и максимальные координаты** в течение периода. Если оба значения попадают в очень узкий диапазон (порог допуска, например, ±0.01 градуса), мы помечаем Актив как не Движется. Запрос также выполняет соединение с таблицами objects и Транспорт в 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'
    Группа BY td.device_id
),
stationary_devices AS (
    SELECT
        device_id
    FROM gps_bounds
    WHERE location_records > 10 -- исключить устройства с очень редкими данными
	AND((max_lat - min_lat) <= 2000 -- ~10 метров
		ИЛИ
      	(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;
```

{% endcode %}

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

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

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

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

{% 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
    ИЗ 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'
),
вычислено AS (
    SELECT
        device_id,
        device_time,
        object_id,
        Объект_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,
    Объект_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, например 'Зажигание', и значением 1 (включено) или 0 (выключено). Чтобы вычислить Моточасы, мы находим все переходы с отметкой времени для каждого устройства и вычисляем длительности, в течение которых двигатель был включен (1).

Чтобы связать активность двигателя с обоими **Транспорт и Водители**, мы используем таблицы Объект, Транспорт и 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.*
       ИЗ 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,
   Объект_label,
   e.first_name || ' ' || e.last_name AS driver_name,
   SUM(engine_hours) AS total_engine_hours
ИЗ assigned_drivers ad
LEFT JOIN raw_business_data.employees e ON ad.employee_id = e.employee_id
ГРУППИРОВАТЬ ПО 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.

Запрос анализирует **точки местоположения с низкой или нулевой скоростью** используя это Запрос использует таблицу tracking\_data\_core из raw\_telematics\_data для извлечения данных о местоположении в виде временных рядов и скорости. Остановка определяется, когда **скорость падает ниже 3 км/ч** в течение **более 2 минут**. Используя функции LAG и LEAD, запрос сегментирует эти периоды с низкой скоростью, чтобы определить временные метки начала и окончания стоянки.

Чтобы выявить **несанкционированные Стоянки**, он исключает точки, которые попадают в известные **геозоны** (zones table) с использованием 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
    ИЗ 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_стоянки 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,
    Объект_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 AM, после 6 PM или в любое время по выходным).

Для наглядности мы обогащаем данные 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)  -- Суббота или воскресенье
        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
    Объект_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, запрос сегментирует данные на основе этих переходов. Поездка определяется как a **период непрерывного движения** где скорость остается выше порога (например, >5 км/ч).

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

* A **временная метка и местоположение начала** (первая точка, когда Транспорт Движется)
* Один **временная метка и местоположение окончания** (последняя точка, когда Транспорт Движется, перед остановкой)
* Этот **Расстояние по формуле гаверсинуса** между начальным и конечным местоположениями

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

{% 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'
),
Поездка_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
),
поездка_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.поездка_день,
    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
Группа 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. **Суммирование расстояний по дням по устройствам** чтобы получить общий Пробег.

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

{% 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,
        Поездка_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 -- exclude outliers
)
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) через Объект и Транспорт.

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

{% 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,
        Объект_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,
    Объект_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.