各位大佬帮忙看一下收集的sql信息，是什么原因引起的insert慢

AntTech_X7SNNO · 2025 年11 月 19 日 14:32

sql_plan_monitor_report.zip (2.8 MB)
sql如下

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;