Условие

Карьерные самосвалы (БелАЗ) перевозят породу. На каждом стоят датчики: скорость, высота, расход топлива, координаты, обороты двигателя, ускорение, etc. Один рейс = от пункта погрузки до пункта разгрузки и обратно.

Дано:

telemetry.parquet (objectid, tripid, driverid, time, lat, lon, x, y, speed, height, engine_speed, fuel_cons, fuel_tank_level, weight_dynamic, weight, DQ_vertical_bump, accelerator_position, w_fl).
weather_hourly.parquet.

SQL (часть 1) — три задачи на агрегации по telemetry/objects/sensors.

Python (часть 2):

Карта карьера, чистка координатных выбросов.
Средние параметры по самосвалам, гистограммы расстояний рейсов, средняя скорость по часу.
Обучить модель прогноза скорости (speed).

Решение

Часть 1 (SQL): % телеметрии в допустимых пределах

SELECT o.id,
       COUNT(CASE WHEN t.value BETWEEN s.min_value AND s.max_value
                  THEN 1 END) * 100.0 / COUNT(*) AS percentage
FROM telemetry t
JOIN objects  o ON t.objectid = o.id
JOIN sensors  s ON t.sensorid = s.id
WHERE o.modelname = 'БелАЗ-75320'
  AND s.tag       = 'height'
  AND o.enterprise_id = 6
  AND t.time      >= NOW() - INTERVAL '1 day'
GROUP BY o.id;

Часть 2 (Python)

Очистка координат

import pandas as pd
import numpy as np
df = pd.read_parquet('telemetry.parquet')
 
# 1) Очевидные выбросы по координатам — фильтр по медианному квадрату
lat_q = df['lat'].quantile([0.001, 0.999])
lon_q = df['lon'].quantile([0.001, 0.999])
df = df[(df['lat'].between(*lat_q)) & (df['lon'].between(*lon_q))]
 
# 2) Speed-аномалии (если speed > 100 km/h на карьере — нереально)
df = df[df['speed'].between(0, 80)]
 
# 3) hdop (Horizontal Dilution of Precision) если есть — фильтр < 5
if 'hdop' in df.columns:
    df = df[df['hdop'] < 5]
 
# fill NaN forward для редких датчиков
df['fuel_tank_level']   = df.groupby('objectid')['fuel_tank_level'].ffill()
df['weight_dynamic']    = df.groupby('objectid')['weight_dynamic'].ffill()

Feature engineering

# Time features
df['hour'] = df['time'].dt.hour
df['dow']  = df['time'].dt.dayofweek
 
# Lag/rolling
df = df.sort_values(['objectid', 'time'])
df['speed_lag1']     = df.groupby('objectid')['speed'].shift(1)
df['speed_lag5']     = df.groupby('objectid')['speed'].shift(5)
df['speed_roll10']   = df.groupby('objectid')['speed'].rolling(10).mean().reset_index(0, drop=True)
 
# Acceleration as derivative of speed
df['accel'] = df.groupby('objectid')['speed'].diff() / df.groupby('objectid')['time'].diff().dt.total_seconds()
 
# Slope (height change)
df['slope'] = df.groupby('objectid')['height'].diff() / df.groupby('objectid')['time'].diff().dt.total_seconds()
 
# Loaded flag — weight_dynamic > threshold of weight
df['is_loaded'] = (df['weight_dynamic'] > df['weight'] * 0.5).astype(int)
 
# Weather merge
weather = pd.read_parquet('weather_hourly.parquet')
df['hour_floor'] = df['time'].dt.floor('H')
df = df.merge(weather, left_on='hour_floor', right_on='time', how='left')

Модель

from sklearn.model_selection import GroupKFold
from sklearn.ensemble import GradientBoostingRegressor
from sklearn.metrics import mean_absolute_error
import lightgbm as lgb
 
features = ['accelerator_position', 'engine_speed', 'fuel_cons',
            'is_loaded', 'slope', 'hour', 'dow',
            'speed_lag1', 'speed_lag5', 'speed_roll10',
            'temp', 'precipitation']  # из weather
 
X = df[features].dropna()
y = df.loc[X.index, 'speed']
 
gkf = GroupKFold(n_splits=5)
groups = df.loc[X.index, 'tripid']
mae_scores = []
for tr, te in gkf.split(X, y, groups=groups):
    m = lgb.LGBMRegressor(n_estimators=500, learning_rate=0.05)
    m.fit(X.iloc[tr], y.iloc[tr])
    mae_scores.append(mean_absolute_error(y.iloc[te], m.predict(X.iloc[te])))
 
print('MAE:', np.mean(mae_scores))

Метрика

MAE — интерпретируемая (км/ч).
RMSE — для penalty больших ошибок.
MAPE — relative error, но плохо при низких скоростях (деление на ~0).

Выбираем MAE как primary, RMSE для контроля выбросов.

Сравнение моделей

Linear — baseline.
Random Forest — для нелинейностей.
LightGBM / XGBoost — обычно лучше всего на табличке.
LSTM — если хотим использовать sequence-структуру (но дороже).

Интерпретация

Топ-фичи: engine_speed, accelerator_position, slope (нагрузка двигателя).
is_loaded × slope — взаимодействие важно: гружёный самосвал в гору едет очень медленно.
weather — снег/гололёд снижают скорость; влияние небольшое, но есть.

Подводные камни

GroupKFold по tripid обязателен: если одна и та же поездка попадет в train и test, лекаж через временные ряды.
speed_lag1 — сильнейший предиктор. С точки зрения «прогноза» это OK для коротких горизонтов; но для «free-form» прогноза без таргета — лекаж.
fuel_tank_level и weight_dynamic — низкая частота. Forward-fill OK для коротких пропусков.
weight = const for trip — если weight включён, модель фактически узнаёт tripid через близкие значения. Использовать осторожно или вообще как фичу не брать (или нормализовать).
w_fl — неизвестное поле; нужно понять по корреляции с другими (фронт-левое колесо? скорость колеса). Анализ через corr и shap.
Координаты x,y и lat,lon дают двоeточный сигнал: x,y в UTM проще для расчёта расстояния (Евклид), lat/lon — Haversine.
Outlier в speed = 0: simulator-стояние — оставлять или нет, зависит от формулировки задачи.

Эталонный ответ

Чистка по hdop / quantile-cutoff на координаты. Фичи: lag/rolling/derivative speed, slope из height, is_loaded, weather, time. Модель — LightGBM с GroupKFold по tripid. Метрика — MAE. Топ-фичи — engine_speed, accelerator, slope, is_loaded.

Пиклема — модель прогноза скорости карьерных самосвалов по телеметрии