insert into dwd.dwd_obd_analyze
SELECT /*+ PARALLEL(48) */
vin,
send_time,
speed,
rpm,
torque,
fuelflow,
lon,
lat,
odometer,
– 1. 计算速度变化率（加速度）
speed - LAG(speed) OVER (PARTITION BY vin ORDER BY send_time) AS speed_change,
-- 2. 计算连续数据点的时间间隔（秒）
TIMESTAMPDIFF(SECOND, LAG(send_time) OVER (PARTITION BY vin ORDER BY send_time), send_time) AS time_interval_seconds,
-- 3. 标记急加速事件
CASE
WHEN (speed - LAG(speed) OVER (PARTITION BY vin ORDER BY send_time)) /
GREATEST(TIMESTAMPDIFF(SECOND, LAG(send_time) OVER (PARTITION BY vin ORDER BY send_time), send_time), 1) > 10
THEN 1
ELSE 0
END AS rapid_acceleration,
-- 4. 计算移动平均速度（最近5个点）
AVG(speed) OVER (
PARTITION BY vin
ORDER BY send_time
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
) AS moving_avg_speed,
-- 5. 计算累计油耗
SUM(fuelflow) OVER (
PARTITION BY vin
ORDER BY send_time
) AS cumulative_fuel_consumption,
-- 6. 按车辆和日期分区排名
RANK() OVER (
PARTITION BY vin, DATE(send_time)
ORDER BY speed DESC
) AS daily_speed_rank,
-- 7. 计算行驶时长（从第一个数据点开始）
TIMESTAMPDIFF(MINUTE, FIRST_VALUE(send_time) OVER (PARTITION BY vin ORDER BY send_time), send_time) AS driving_duration_minutes,
-- 8. 窗口内统计信息
MAX(speed) OVER (
PARTITION BY vin
ORDER BY send_time
ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
) AS max_speed_last_10_points,
AVG(rpm) OVER (
PARTITION BY vin
ORDER BY send_time
ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
) AS avg_rpm_last_10_points,
-- 9. 检测数据延迟异常
CASE
WHEN TIMESTAMPDIFF(MINUTE, send_time, receive_time) > 60
THEN 1
ELSE 0
END AS data_delay_alert,
-- 10. 计算速度百分位（按车辆分组）
PERCENT_RANK() OVER (
PARTITION BY vin
ORDER BY speed
) AS speed_percent_rank,
-- 11. 计算与前值的里程差
odometer - LAG(odometer) OVER (PARTITION BY vin ORDER BY send_time) AS odometer_diff,
-- 12. 标记高速行驶段（速度持续高于80）
CASE
WHEN speed > 80 AND
LAG(speed) OVER (PARTITION BY vin ORDER BY send_time) > 80 AND
LEAD(speed) OVER (PARTITION BY vin ORDER BY send_time) > 80
THEN 1
ELSE 0
END AS high_speed_section
FROM ods.obd_data_ods
WHERE
speed IS NOT NULL
AND rpm IS NOT NULL
ORDER BY
vin,
send_time;
