energy-manage-platform/docs/database/20261105_fault_frequency.sql

-- 振动故障频率参数与测点映射
-- 目标库：energy_prod
-- 版本：2026-11-05
-- 说明：只新增表/字段，不改动既有业务表结构；与现有 unit_dict_brand_model、
--       unit_bearings、unit_dynamo、unit_gearbox* 兼容。

CREATE TABLE IF NOT EXISTS component_fault_spec (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  component_type VARCHAR(32) NOT NULL DEFAULT 'bearing'
    COMMENT 'bearing/main_bearing/generator/gearbox_bearing/gearbox_gear',
  brand VARCHAR(128) NOT NULL,
  model VARCHAR(128) NOT NULL,
  position VARCHAR(32) NOT NULL DEFAULT ''
    COMMENT 'front/rear/drive_end/non_drive_end/planet/low_speed/high_speed/input/output',
  stage INT NOT NULL DEFAULT 0 COMMENT '齿轮箱级数，0 表示不区分',
  n_rolls DECIMAL(10,3) NULL COMMENT '滚动体数量',
  roller_diameter DECIMAL(10,3) NULL COMMENT '滚动体直径 mm',
  pitch_diameter DECIMAL(10,3) NULL COMMENT '节圆直径 mm',
  contact_angle DECIMAL(10,3) NULL COMMENT '接触角 度',
  gear_teeth INT NULL COMMENT '齿轮齿数',
  stage_count INT NULL COMMENT '齿轮箱级数',
  pole_pairs INT NULL COMMENT '发电机极对数',
  source VARCHAR(128) NULL COMMENT '参数来源',
  confidence DECIMAL(5,2) NOT NULL DEFAULT 1.00 COMMENT '0-1',
  valid_state VARCHAR(16) NOT NULL DEFAULT 'valid'
    COMMENT 'valid/missing/review',
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_fault_spec (component_type, brand, model, position, stage)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部件故障频率参数库';

CREATE TABLE IF NOT EXISTS measurement_point_component (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  point_name VARCHAR(255) NOT NULL COMMENT '波形 mesure_point_name',
  component_type VARCHAR(32) NOT NULL COMMENT '部件类型',
  position VARCHAR(32) NOT NULL DEFAULT '' COMMENT '安装位置',
  stage INT NOT NULL DEFAULT 0 COMMENT '级数',
  note VARCHAR(255) NULL,
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_point_name (point_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测点到部件/位置映射';

-- 复用现有轴承字典中的几何参数，避免重复维护
INSERT INTO component_fault_spec
(component_type, brand, model, position, stage,
 n_rolls, roller_diameter, pitch_diameter, contact_angle,
 source, confidence, valid_state)
SELECT 'bearing', manufacture, model_number, '', 0,
       rolls_number, rolls_diameter, circle_diameter, theta_deg,
       'unit_dict_brand_model', 0.95, 'valid'
FROM unit_dict_brand_model
WHERE unit_type = 1
  AND rolls_number IS NOT NULL
  AND rolls_diameter IS NOT NULL
  AND circle_diameter IS NOT NULL
  AND theta_deg IS NOT NULL
ON DUPLICATE KEY UPDATE
  n_rolls = VALUES(n_rolls),
  roller_diameter = VALUES(roller_diameter),
  pitch_diameter = VALUES(pitch_diameter),
  contact_angle = VALUES(contact_angle),
  source = VALUES(source),
  confidence = VALUES(confidence),
  valid_state = VALUES(valid_state);

-- 测点映射（与前端测点英文键保持一致）
INSERT INTO measurement_point_component
(point_name, component_type, position, stage, note)
VALUES
('main_bearing_radial_vertical_vibration','main_bearing','main',0,'主轴承径向垂直'),
('main_bearing_radial_vibration','main_bearing','main',0,'主轴承径向'),
('main_bearing_radial_horizontal_vibration','main_bearing','main',0,'主轴承径向水平'),
('main_bearing_axial_vibration','main_bearing','main',0,'主轴承轴向'),
('front_main_bearing_radial_vertical_vibration','main_bearing','front',0,'前主轴承径向垂直'),
('front_main_bearing_radial_vibration','main_bearing','front',0,'前主轴承径向'),
('front_main_bearing_radial_horizontal_vibration','main_bearing','front',0,'前主轴承径向水平'),
('front_main_bearing_axial_vibration','main_bearing','front',0,'前主轴承轴向'),
('rear_main_bearing_radial_vertical_vibration','main_bearing','rear',0,'后主轴承径向垂直'),
('rear_main_bearing_radial_vibration','main_bearing','rear',0,'后主轴承径向'),
('rear_main_bearing_radial_horizontal_vibration','main_bearing','rear',0,'后主轴承径向水平'),
('rear_main_bearing_axial_vibration','main_bearing','rear',0,'后主轴承轴向'),
('generator_radial_1_vibration','generator','drive_end',0,'发电机径向1'),
('generator_radial_2_vibration','generator','non_drive_end',0,'发电机径向2'),
('generator_drive_end_radial_vibration','generator','drive_end',0,'发电机驱动端'),
('generator_non_drive_end_radial_vibration','generator','non_drive_end',0,'发电机非驱动端'),
('stator_radial_horizontal_vibration','generator','stator',0,'定子径向水平'),
('gearbox_first_stage_planet_large_ring_radial_vibration','gearbox_gear','planet',1,'一级行星级大齿圈'),
('gearbox_first_stage_planet_radial_vibration','gearbox_gear','planet',1,'一级行星级'),
('gearbox_second_stage_planet_large_ring_radial_vibration','gearbox_gear','planet',2,'二级行星级大齿圈'),
('gearbox_second_stage_planet_radial_vibration','gearbox_gear','planet',2,'二级行星级'),
('gearbox_second_stage_planet_axial_vibration','gearbox_gear','planet',2,'二级行星级轴向'),
('gearbox_third_stage_planet_radial_vibration','gearbox_gear','planet',3,'三级行星级'),
('gearbox_low_speed_shaft_output_end_radial_vibration','gearbox_bearing','low_speed',0,'低速轴输出端'),
('gearbox_input_end_radial_vibration','gearbox_bearing','input',0,'齿轮箱输入端'),
('gearbox_output_shaft_axial_vibration','gearbox_bearing','output',0,'齿轮箱输出轴'),
('gearbox_high_speed_shaft_output_end_radial_vibration','gearbox_bearing','high_speed',0,'高速轴输出端'),
('gearbox_high_speed_shaft_output_end_axial_vibration','gearbox_bearing','high_speed',0,'高速轴输出端轴向')
ON DUPLICATE KEY UPDATE
  component_type = VALUES(component_type),
  position = VALUES(position),
  stage = VALUES(stage),
  note = VALUES(note);

-- 报警表补充故障频率证据字段
ALTER TABLE vibration_alarm_event
  ADD COLUMN fault_type VARCHAR(32) NULL COMMENT 'BPFO/BPFI/BSF/FTF/GMF',
  ADD COLUMN characteristic_freq DOUBLE NULL COMMENT '特征频率 Hz',
  ADD COLUMN fault_order DOUBLE NULL COMMENT '阶次',
  ADD COLUMN spec_source VARCHAR(128) NULL COMMENT '参数来源',
  ADD COLUMN diagnosis_detail VARCHAR(512) NULL COMMENT '诊断证据';