时间区间查询慢：即使对时间戳字段建立了索引，在亿级数据表中进行范围扫描，性能衰减依然明显。SELECT * FROM imu_data WHERE drone_id = 1 AND timestamp BETWEEN '2023-10-01 10:00:00' AND '2023-10-01 10:00:10' 这类查询会变得异常缓慢。
-- 1. 查询最近 24 小时各仿真 ID 的飞行性能记录数量 TOP10（保持不变） SELECT simulation_id, COUNT(*) as flight_count, AVG(performance_score) as avg_performance, MIN(ts) as first_flight_time, MAX(ts) as last_flight_time FROM px4_simulation.performance_metrics WHERE ts >= NOW() - INTERVAL '24 hours' GROUP BY simulation_id ORDER BY flight_count DESC LIMIT 10; -- 2. 查询不同电池的健康状态统计（保持不变） SELECT battery_id, battery_type, COUNT(*) as record_count, ROUND(AVG(state_of_health)::numeric, 2) as avg_soh, ROUND(AVG(remaining_capacity)::numeric, 2) as avg_remaining, ROUND(MIN(remaining_capacity)::numeric, 2) as min_remaining FROM px4_simulation.battery_health_monitoring WHERE ts >= NOW() - INTERVAL '24 hours' GROUP BY battery_id, battery_type ORDER BY avg_soh DESC LIMIT 100; -- 3. 查询各仿真 ID 的告警/异常统计 WITH alert_data AS (-- 电池异常 SELECT simulation_id, 'battery_low' as alert_type, COUNT(*) as alert_count FROM px4_simulation.battery_health_monitoring WHERE remaining_capacity < 0.2 AND ts >= NOW() - INTERVAL '24 hours' GROUP BY simulation_id UNION ALL -- 振动异常 SELECT simulation_id, 'vibration_high' as alert_type, COUNT(*) as alert_count FROM px4_simulation.vibration_monitoring WHERE vibration_severity_index > 0.5 AND ts >= NOW() - INTERVAL '24 hours' GROUP BY simulation_id UNION ALL -- 姿态误差过大 SELECT simulation_id, 'attitude_error' as alert_type, COUNT(*) as alert_count FROM px4_simulation.performance_metrics WHERE roll_error > 10.0 OR pitch_error > 10.0 OR yaw_error > 10.0 AND ts >= NOW() - INTERVAL '24 hours' GROUP BY simulation_id ) SELECT simulation_id, alert_type, SUM(alert_count) as total_alerts FROM alert_data GROUP BY simulation_id, alert_type ORDER BY total_alerts DESC LIMIT 100; -- 4. 查询飞行性能分数分布（修正：移除 ORDER BY 中的 CASE，使用别名） SELECT score_range, COUNT(*) as count, ROUND(MIN(performance_score)::numeric, 2) as min_score, ROUND(MAX(performance_score)::numeric, 2) as max_score, ROUND(AVG(performance_score)::numeric, 2) as avg_score FROM ( SELECT performance_score, CASE WHEN performance_score >= 0 AND performance_score < 10 THEN '0-9' WHEN performance_score >= 10 AND performance_score < 20 THEN '10-19' WHEN performance_score >= 20 AND performance_score < 30 THEN '20-29' WHEN performance_score >= 30 AND performance_score < 40 THEN '30-39' WHEN performance_score >= 40 AND performance_score < 50 THEN '40-49' WHEN performance_score >= 50 AND performance_score < 60 THEN '50-59' WHEN performance_score >= 60 AND performance_score < 70 THEN '60-69' WHEN performance_score >= 70 AND performance_score < 80 THEN '70-79' WHEN performance_score >= 80 AND performance_score < 90 THEN '80-89' WHEN performance_score >= 90 AND performance_score <= 100 THEN '90-100' ELSE '其他' END as score_range FROM px4_simulation.performance_metrics WHERE ts >= NOW() - INTERVAL '24 hours' ) as subquery GROUP BY score_range ORDER BY CASE score_range WHEN '0-9' THEN 0 WHEN '10-19' THEN 1 WHEN '20-29' THEN 2 WHEN '30-39' THEN 3 WHEN '40-49' THEN 4 WHEN '50-59' THEN 5 WHEN '60-69' THEN 6 WHEN '70-79' THEN 7 WHEN '80-89' THEN 8 WHEN '90-100' THEN 9 ELSE 10 END LIMIT 100; -- 5. 查询关键性能指标统计（保持不变） SELECT simulation_id, COUNT(*) as total_flights, ROUND(AVG(position_error_xy)::numeric, 4) as avg_position_error_xy, ROUND(AVG(position_error_z)::numeric, 4) as avg_position_error_z, ROUND(AVG(attitude_error_roll)::numeric, 4) as avg_attitude_error_roll, ROUND(AVG(attitude_error_pitch)::numeric, 4) as avg_attitude_error_pitch, ROUND(AVG(attitude_error_yaw)::numeric, 4) as avg_attitude_error_yaw, ROUND(AVG(velocity_response_time)::numeric, 4) as avg_response_time, ROUND(AVG(max_acceleration)::numeric, 4) as avg_max_acceleration FROM px4_simulation.flight_performance_baseline WHERE ts >= NOW() - INTERVAL '24 hours' GROUP BY simulation_id ORDER BY avg_position_error_xy DESC LIMIT 10; -- 6.查询未处理的高优先级异常（修正版） WITH battery_critical AS (SELECT'battery_critical'as alert_type, '紧急'as alert_level, '电池电量严重不足'as alert_description, battery_id, remaining_capacity, ts FROM px4_simulation.battery_health_monitoring WHERE remaining_capacity <0.15AND ts >= NOW() -INTERVAL'1 hour' ), vibration_critical AS (
SELECT'vibration_critical'as alert_type, '紧急'as alert_level, '振动严重异常'as alert_description, component_id, vibration_severity_index, ts FROM px4_simulation.vibration_monitoring WHERE vibration_severity_index >0.8AND ts >= NOW() -INTERVAL'1 hour' ), performance_critical AS (
SELECT'performance_critical'as alert_type, '高'as alert_level, '飞行性能严重下降'as alert_description, CAST(simulation_id ASVARCHAR) as simulation_id_str, performance_score, ts FROM px4_simulation.performance_metrics WHERE performance_score <60AND ts >= NOW() -INTERVAL'1 hour' ), -- 注意：从 performance_metrics 表查询时，需要确保列名和数据类型与其他子查询一致 performance_critical_fixed AS (SELECT alert_type, alert_level, alert_description, -- 使用空字符串作为占位符，因为其他表有 battery_id/component_id，但 performance_metrics 没有 '' as component_id, performance_score as severity_value, -- 重命名以统一 ts FROM performance_critical ), -- 统一所有子查询的列 battery_critical_fixed AS (SELECT alert_type, alert_level, alert_description, battery_id as component_id, remaining_capacity as severity_value, ts FROM battery_critical ), vibration_critical_fixed AS (
SELECT alert_type, alert_level, alert_description, component_id, vibration_severity_index as severity_value, ts FROM vibration_critical )
-- 合并所有警报 SELECT * FROM battery_critical_fixed UNION ALL SELECT * FROM vibration_critical_fixed UNION ALL SELECT * FROM performance_critical_fixed ORDER BY CASE alert_type WHEN 'battery_critical' THEN 1 WHEN 'vibration_critical' THEN 2 WHEN 'performance_critical' THEN 3 ELSE 4 END, severity_value, -- 按严重程度排序（值越小/越大表示越严重） ts DESC; -- 7. 查询飞行失败原因分析（修正：使用正确的表和列） SELECT failure_reason, SUM(failure_count) as total_failures, ROUND(AVG(avg_performance)::numeric, 2) as avg_failure_score, COUNT(DISTINCT simulation_id) as affected_simulations FROM ( SELECT simulation_id, CASE -- 使用 performance_metrics 表中的列 WHEN pos_error > 1.0 THEN '位置控制误差过大' WHEN vel_error > 0.5 THEN '速度控制误差过大' WHEN roll_error > 15.0 THEN '滚转角不稳定' WHEN pitch_error > 15.0 THEN '俯仰角不稳定' WHEN yaw_error > 20.0 THEN '偏航角不稳定' WHEN control_effort > 2.0 THEN '控制努力度过大' ELSE '其他原因' END as failure_reason, COUNT(*) as failure_count, AVG(performance_score) as avg_performance FROM px4_simulation.performance_metrics -- 修改为 performance_metrics 表 WHERE performance_score < 70 AND ts >= NOW() - INTERVAL '24 hours' GROUP BY simulation_id, pos_error, vel_error, roll_error, pitch_error, yaw_error, control_effort ) as subquery GROUP BY failure_reason ORDER BY total_failures DESC LIMIT 100; -- 8. 查询无人机组件维护计划（保持不变） SELECT component_id, component_type, total_operating_hours, predicted_remaining_life_hours, ROUND((total_operating_hours / (total_operating_hours + predicted_remaining_life_hours))::numeric, 4) as wear_percentage, maintenance_recommendation, CASE WHEN predicted_remaining_life_hours < 24 THEN '紧急维护' WHEN predicted_remaining_life_hours < 72 THEN '近期维护' ELSE '计划维护' END as maintenance_priority, CASE WHEN predicted_remaining_life_hours < 24 THEN NOW() WHEN predicted_remaining_life_hours < 72 THEN NOW() + INTERVAL '7 days' ELSE NOW() + INTERVAL '30 days' END as suggested_maintenance_time FROM px4_simulation.component_life_prediction WHERE ts >= NOW() - INTERVAL '1 hour' ORDER BY maintenance_priority, predicted_remaining_life_hours LIMIT 100; -- 9. 查询异常处理效率分析（修正：使用正确的列名） WITH battery_exceptions AS (SELECT'battery'as exception_type, b1.battery_id as component, b1.ts as detection_time, MIN(b2.ts) as resolution_time FROM px4_simulation.battery_health_monitoring b1 LEFTJOIN px4_simulation.battery_health_monitoring b2 ON b1.battery_id = b2.battery_id AND b2.ts > b1.ts AND b2.remaining_capacity > b1.remaining_capacity WHERE b1.remaining_capacity <0.2AND b1.ts >= NOW() -INTERVAL'24 hours'GROUPBY b1.battery_id, b1.ts ), vibration_exceptions AS (
SELECT'vibration'as exception_type, v1.component_id as component, v1.ts as detection_time, MIN(v2.ts) as resolution_time FROM px4_simulation.vibration_monitoring v1 LEFTJOIN px4_simulation.vibration_monitoring v2 ON v1.component_id = v2.component_id AND v2.ts > v1.ts AND v2.vibration_severity_index < v1.vibration_severity_index -- 修正：使用正确的列名 WHERE v1.vibration_severity_index > 0.5 -- 修正：使用正确的列名 AND v1.ts >= NOW() - INTERVAL '24 hours' GROUP BY v1.component_id, v1.ts ), all_exceptions AS (SELECT exception_type, component, detection_time, resolution_time FROM battery_exceptions UNIONALLSELECT exception_type, component, detection_time, resolution_time FROM vibration_exceptions ) SELECT exception_type, COUNT(*) as total_exceptions, COUNT(resolution_time) as handled_exceptions, ROUND(AVG( CASEWHEN resolution_time ISNOT NULLTHENEXTRACT(EPOCH FROM (resolution_time - detection_time)) ELSENULLEND )::numeric, 2) as avg_handling_time_seconds, ROUND(MIN( CASEWHEN resolution_time ISNOT NULLTHENEXTRACT(EPOCH FROM (resolution_time - detection_time)) ELSENULLEND )::numeric, 2) as min_handling_time, ROUND(MAX( CASEWHEN resolution_time ISNOT NULLTHENEXTRACT(EPOCH FROM (resolution_time - detection_time)) ELSENULLEND )::numeric, 2) as max_handling_time, ROUND((COUNT(resolution_time)::float/NULLIF(COUNT(*), 0) *100)::numeric, 2) as handling_rate_percent FROM all_exceptions GROUPBY exception_type ORDERBY avg_handling_time_seconds LIMIT 100;
-- 10. 查询测试记录的时间分布和性能趋势（保持不变） -- 查询测试记录的时间分布和性能趋势（修正版） SELECT DATE_TRUNC('hour', ts) as hour_bucket, COUNT(*) as test_count, ROUND(AVG(hover_stability_index)::numeric, 2) as avg_stability, ROUND(AVG(position_error_xy)::numeric, 4) as avg_position_error_xy, ROUND(AVG(position_error_z)::numeric, 4) as avg_position_error_z, ROUND(AVG(attitude_error_roll)::numeric, 4) as avg_attitude_error_roll, ROUND(AVG(attitude_error_pitch)::numeric, 4) as avg_attitude_error_pitch, ROUND(AVG(attitude_error_yaw)::numeric, 4) as avg_attitude_error_yaw, ROUND(AVG(velocity_response_time)::numeric, 4) as avg_response_time FROM px4_simulation.flight_performance_baseline WHERE ts >= NOW() - INTERVAL '24 hours' GROUP BY DATE_TRUNC('hour', ts) ORDER BY hour_bucket DESC LIMIT 100; -- 11. 查询通信质量分析（保持不变） SELECT simulation_id, link_type, COUNT(*) as sample_count, ROUND(AVG(rssi_dbm)::numeric, 2) as avg_rssi_dbm, ROUND(AVG(packet_loss_rate)::numeric, 4) as avg_packet_loss, ROUND(AVG(latency_ms)::numeric, 2) as avg_latency_ms, ROUND(AVG(jitter_ms)::numeric, 2) as avg_jitter_ms, ROUND(MIN(link_margin_db)::numeric, 2) as min_link_margin, ROUND(MAX(link_margin_db)::numeric, 2) as max_link_margin, SUM(CASE WHEN packet_loss_rate > 0.1 THEN 1 ELSE 0 END) as high_loss_samples FROM px4_simulation.communication_quality WHERE ts >= NOW() - INTERVAL '24 hours' GROUP BY simulation_id, link_type ORDER BY avg_packet_loss DESC LIMIT 100; -- 12. 查询环境适应能力分析（保持不变） SELECT simulation_id, environment_type, COUNT(*) as sample_count, ROUND(AVG(wind_speed_estimated)::numeric, 2) as avg_wind_speed, ROUND(AVG(turbulence_intensity)::numeric, 4) as avg_turbulence, ROUND(AVG(position_hold_error)::numeric, 4) as avg_position_error, ROUND(AVG(power_consumption_factor)::numeric, 4) as avg_power_factor, ROUND(AVG(attitude_correction_roll)::numeric, 4) as avg_roll_correction, ROUND(AVG(attitude_correction_pitch)::numeric, 4) as avg_pitch_correction FROM px4_simulation.environmental_adaptation WHERE ts >= NOW() - INTERVAL '24 hours' GROUP BY simulation_id, environment_type ORDER BY avg_position_error DESC LIMIT 100; -- 13. 查询参数调优历史分析（修正：添加 GROUP BY 中的 performance_score 或使用聚合函数） SELECT simulation_id, parameter_name, COUNT(*) as tuning_count, ROUND(MIN(parameter_value)::numeric, 4) as min_value, ROUND(MAX(parameter_value)::numeric, 4) as max_value, ROUND(AVG(parameter_value)::numeric, 4) as avg_value, ROUND(AVG(performance_score)::numeric, 2) as avg_performance, tuner_name, MAX(ts) as last_tuning_time FROM px4_simulation.parameter_tuning WHERE ts >= NOW() - INTERVAL '24 hours' GROUP BY simulation_id, parameter_name, tuner_name ORDER BY tuning_count DESC LIMIT 100;
