电梯交通量仿真分析7
7. 数据库设计
7.1 设计原则
7.1.1 数据库选型理由
选择PostgreSQL作为本系统的数据库，基于以下考虑：
- 强大的JSONB支持：适合存储灵活的仿真配置和结果数据
- 地理空间数据支持：虽本系统不直接使用，但为未来扩展提供可能
- ACID合规性：确保仿真结果数据的完整性和一致性
- 高性能：能够处理大规模仿真产生的大量时序数据
- 开源与成熟：企业级应用的可靠选择
7.1.2 设计原则
- 规范化与反规范化平衡：核心配置数据规范化，时序数据适当反规范化
- 时序数据优化：针对仿真时间切片数据进行特殊优化
- 查询性能优先：为关键查询模式设计合适的索引
- 数据完整性：通过约束确保OD矩阵等关键数据的完整性
- 可扩展性：设计支持未来功能扩展的灵活结构
7.2 核心表结构设计
7.2.1 建筑配置表
building - 存储建筑基本信息
CREATE TABLE building (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
total_floors INTEGER NOT NULL CHECK (total_floors > 0),
floor_height NUMERIC(5,2) NOT NULL CHECK (floor_height > 0),
building_type VARCHAR(50) NOT NULL, -- OFFICE, MALL, RESIDENTIAL, MIXED
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE building IS '建筑基本信息表，存储建筑的静态属性';
COMMENT ON COLUMN building.building_type IS '建筑类型：OFFICE(办公楼), MALL(商场), RESIDENTIAL(住宅), MIXED(混合用途)';floor - 存储楼层详细信息
CREATE TABLE floor (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
building_id UUID NOT NULL REFERENCES building(id) ON DELETE CASCADE,
floor_number INTEGER NOT NULL CHECK (floor_number >= 1),
usage VARCHAR(50) NOT NULL, -- LOBBY, PARKING, OFFICE, RETAIL, RESIDENTIAL
population INTEGER NOT NULL DEFAULT 0 CHECK (population >= 0),
UNIQUE (building_id, floor_number)
);
COMMENT ON TABLE floor IS '楼层详细信息表，存储每层的用途和人口信息';
COMMENT ON COLUMN floor.usage IS '楼层用途：LOBBY(大厅), PARKING(车库), OFFICE(办公), RETAIL(零售), RESIDENTIAL(住宅)';floor_time_period - 存储楼层在不同时段的交通特性
CREATE TABLE floor_time_period (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
floor_id UUID NOT NULL REFERENCES floor(id) ON DELETE CASCADE,
time_period VARCHAR(50) NOT NULL, -- MORNING_PEAK, LUNCH_PEAK, EVENING_PEAK, NORMAL
incoming_traffic_ratio NUMERIC(4,3) NOT NULL CHECK (incoming_traffic_ratio BETWEEN 0 AND 1),
outgoing_traffic_ratio NUMERIC(4,3) NOT NULL CHECK (outgoing_traffic_ratio BETWEEN 0 AND 1),
interfloor_traffic_ratio NUMERIC(4,3) NOT NULL CHECK (interfloor_traffic_ratio BETWEEN 0 AND 1),
CHECK (incoming_traffic_ratio + outgoing_traffic_ratio + interfloor_traffic_ratio = 1),
batch_probability NUMERIC(4,3) NOT NULL CHECK (batch_probability BETWEEN 0 AND 1),
inter_arrival_mean NUMERIC(5,2) NOT NULL CHECK (inter_arrival_mean > 0),
inter_arrival_stddev NUMERIC(5,2) NOT NULL CHECK (inter_arrival_stddev >= 0)
);
COMMENT ON TABLE floor_time_period IS '存储楼层在不同时段的交通特性，实现PDF中描述的三种交通成分比例';
COMMENT ON COLUMN floor_time_period.time_period IS '时间段：MORNING_PEAK(早高峰), LUNCH_PEAK(午餐高峰), EVENING_PEAK(晚高峰), NORMAL(正常)';
COMMENT ON COLUMN floor_time_period.incoming_traffic_ratio IS '进楼交通比例，对应PDF中"incoming traffic"';
COMMENT ON COLUMN floor_time_period.outgoing_traffic_ratio IS '出楼交通比例，对应PDF中"outgoing traffic"';
COMMENT ON COLUMN floor_time_period.interfloor_traffic_ratio IS '楼层间交通比例，对应PDF中"inter-floor traffic"';batch_size_distribution - 存储批次大小分布
CREATE TABLE batch_size_distribution (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
floor_time_period_id UUID NOT NULL REFERENCES floor_time_period(id) ON DELETE CASCADE,
batch_size INTEGER NOT NULL CHECK (batch_size >= 1),
probability NUMERIC(4,3) NOT NULL CHECK (probability BETWEEN 0 AND 1),
UNIQUE (floor_time_period_id, batch_size)
);
COMMENT ON TABLE batch_size_distribution IS '存储PDF中描述的批次大小分布，实现"批次到达可以建模为时间非齐次泊松过程"';7.2.2 电梯配置表
elevator_group - 电梯组配置
CREATE TABLE elevator_group (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
building_id UUID NOT NULL REFERENCES building(id) ON DELETE CASCADE,
name VARCHAR(100),
control_type VARCHAR(50) NOT NULL, -- SINGLE_CONTROL, PARALLEL_CONTROL, GROUP_CONTROL, DESTINATION_CONTROL
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE elevator_group IS '电梯组配置表，支持PDF中描述的四种控制方式';
COMMENT ON COLUMN elevator_group.control_type IS '控制类型：SINGLE_CONTROL(单控), PARALLEL_CONTROL(并联), GROUP_CONTROL(群控), DESTINATION_CONTROL(目的选层)';elevator - 单台电梯配置
CREATE TABLE elevator (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
group_id UUID NOT NULL REFERENCES elevator_group(id) ON DELETE CASCADE,
name VARCHAR(100),
capacity INTEGER NOT NULL CHECK (capacity > 0),
rated_speed NUMERIC(4,2) NOT NULL CHECK (rated_speed > 0),
acceleration NUMERIC(4,2) NOT NULL CHECK (acceleration > 0),
deceleration NUMERIC(4,2) NOT NULL CHECK (deceleration > 0),
door_width NUMERIC(3,2) NOT NULL CHECK (door_width > 0),
standby_floor INTEGER NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE elevator IS '单台电梯配置表，存储电梯的物理和运行参数';
COMMENT ON COLUMN elevator.door_width IS '门宽(米)，影响PDF中描述的"乘客流动时间"';elevator_service_floor - 电梯服务楼层
CREATE TABLE elevator_service_floor (
elevator_id UUID NOT NULL REFERENCES elevator(id) ON DELETE CASCADE,
floor_number INTEGER NOT NULL,
PRIMARY KEY (elevator_id, floor_number),
CHECK (floor_number >= 1)
);
COMMENT ON TABLE elevator_service_floor IS '电梯服务楼层表，实现PDF中描述的电梯服务区域划分';7.2.3 仿真配置与结果表
simulation - 仿真任务基本信息
CREATE TABLE simulation (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255),
building_id UUID NOT NULL REFERENCES building(id),
created_by UUID, -- 用户ID，外键到用户表
status VARCHAR(20) NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'RUNNING', 'COMPLETED', 'FAILED')),
start_time TIMESTAMPTZ NOT NULL,
end_time TIMESTAMPTZ,
duration INTEGER NOT NULL CHECK (duration > 0), -- 仿真时长(秒)
time_slice INTEGER NOT NULL DEFAULT 5 CHECK (time_slice > 0), -- 时间切片(秒)
od_matrix_method VARCHAR(10) NOT NULL DEFAULT 'BILS' CHECK (od_matrix_method IN ('LP', 'BILS', 'CP')),
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE simulation IS '仿真任务基本信息表，存储仿真任务的状态和配置';
COMMENT ON COLUMN simulation.od_matrix_method IS 'OD矩阵估计方法：LP(线性规划), BILS(带界约束的整数最小二乘), CP(约束规划)';simulation_result - 仿真结果指标
CREATE TABLE simulation_result (
simulation_id UUID PRIMARY KEY REFERENCES simulation(id) ON DELETE CASCADE,
average_waiting_time NUMERIC(6,2),
max_waiting_time NUMERIC(6,2),
min_waiting_time NUMERIC(6,2),
waiting_time_stddev NUMERIC(6,2),
waiting_time_percentiles JSONB NOT NULL DEFAULT '[0,0,0,0,0,0,0,0,0,0]'::jsonb,
average_travel_time NUMERIC(6,2),
max_travel_time NUMERIC(6,2),
min_travel_time NUMERIC(6,2),
travel_time_stddev NUMERIC(6,2),
travel_time_percentiles JSONB NOT NULL DEFAULT '[0,0,0,0,0,0,0,0,0,0]'::jsonb,
handling_capacity NUMERIC(5,2), -- 5分钟输送能力(%)
energy_consumption NUMERIC(8,2),
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE simulation_result IS '仿真结果指标表，存储PDF中要求的各项性能指标';
COMMENT ON COLUMN simulation_result.waiting_time_percentiles IS '等待时间百分位数[10%,20%,...,100%]';
COMMENT ON COLUMN simulation_result.travel_time_percentiles IS '运输时间百分位数[10%,20%,...,100%]';
COMMENT ON COLUMN simulation_result.handling_capacity IS '5分钟输送能力(%)，对应PDF中"handling capacity"';simulation_result_by_floor - 按楼层划分的仿真结果
CREATE TABLE simulation_result_by_floor (
simulation_id UUID NOT NULL REFERENCES simulation(id) ON DELETE CASCADE,
floor_number INTEGER NOT NULL CHECK (floor_number >= 1),
average_waiting_time NUMERIC(6,2),
max_waiting_time NUMERIC(6,2),
min_waiting_time NUMERIC(6,2),
waiting_time_stddev NUMERIC(6,2),
waiting_time_percentiles JSONB NOT NULL DEFAULT '[0,0,0,0,0,0,0,0,0,0]'::jsonb,
average_travel_time NUMERIC(6,2),
PRIMARY KEY (simulation_id, floor_number)
);
COMMENT ON TABLE simulation_result_by_floor IS '按楼层划分的仿真结果表，用于分析不同楼层的服务水平';7.2.4 乘客流量与OD矩阵表
od_matrix - OD矩阵主表
CREATE TABLE od_matrix (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
simulation_id UUID REFERENCES simulation(id) ON DELETE CASCADE,
building_id UUID REFERENCES building(id) ON DELETE CASCADE,
method VARCHAR(10) NOT NULL CHECK (method IN ('LP', 'BILS', 'CP')),
is_rounded BOOLEAN NOT NULL DEFAULT false,
computation_time_ms INTEGER NOT NULL CHECK (computation_time_ms >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE od_matrix IS 'OD矩阵主表，存储PDF中描述的电梯行程OD矩阵估计结果';
COMMENT ON COLUMN od_matrix.is_rounded IS '是否为舍入解，对应PDF中"by rounding a continuous solution to the nearest integer"';od_matrix_entry - OD矩阵条目
CREATE TABLE od_matrix_entry (
od_matrix_id UUID NOT NULL REFERENCES od_matrix(id) ON DELETE CASCADE,
origin_floor INTEGER NOT NULL CHECK (origin_floor >= 1),
destination_floor INTEGER NOT NULL CHECK (destination_floor >= 1),
passenger_count INTEGER NOT NULL CHECK (passenger_count >= 0),
probability NUMERIC(5,4) NOT NULL CHECK (probability BETWEEN 0 AND 1),
PRIMARY KEY (od_matrix_id, origin_floor, destination_floor),
CHECK (origin_floor <> destination_floor)
);
CREATE INDEX idx_od_matrix_entry_origin ON od_matrix_entry(origin_floor);
CREATE INDEX idx_od_matrix_entry_destination ON od_matrix_entry(destination_floor);
COMMENT ON TABLE od_matrix_entry IS 'OD矩阵条目表，存储起点到终点的乘客流量分布';
COMMENT ON COLUMN od_matrix_entry.probability IS '概率值，用于PDF中描述的"statistics"和"forecast"';elevator_trip - 电梯行程记录
CREATE TABLE elevator_trip (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
simulation_id UUID NOT NULL REFERENCES simulation(id) ON DELETE CASCADE,
elevator_id UUID NOT NULL,
start_time TIMESTAMPTZ NOT NULL,
end_time TIMESTAMPTZ NOT NULL,
CHECK (end_time > start_time)
);
COMMENT ON TABLE elevator_trip IS '电梯行程记录表，用于OD矩阵估计的输入数据';elevator_trip_stop - 电梯行程停靠点
CREATE TABLE elevator_trip_stop (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
trip_id UUID NOT NULL REFERENCES elevator_trip(id) ON DELETE CASCADE,
floor_number INTEGER NOT NULL CHECK (floor_number >= 1),
arrival_time TIMESTAMPTZ NOT NULL,
departure_time TIMESTAMPTZ NOT NULL,
boarding_count INTEGER NOT NULL DEFAULT 0 CHECK (boarding_count >= 0),
alighting_count INTEGER NOT NULL DEFAULT 0 CHECK (alighting_count >= 0),
CHECK (departure_time > arrival_time),
UNIQUE (trip_id, floor_number)
);
CREATE INDEX idx_elevator_trip_stop_trip ON elevator_trip_stop(trip_id);
CREATE INDEX idx_elevator_trip_stop_floor ON elevator_trip_stop(floor_number);
COMMENT ON TABLE elevator_trip_stop IS '电梯行程停靠点表，存储PDF中描述的"elevator trip"数据';7.2.5 时序数据表
simulation_timeline - 仿真时间切片数据
CREATE TABLE simulation_timeline (
id BIGSERIAL PRIMARY KEY,
simulation_id UUID NOT NULL REFERENCES simulation(id) ON DELETE CASCADE,
time_point INTEGER NOT NULL CHECK (time_point >= 0), -- 相对于仿真开始的时间(秒)
elevators JSONB NOT NULL, -- 电梯状态数组
passenger_events JSONB NOT NULL, -- 乘客事件数组
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_simulation_timeline_simulation ON simulation_timeline(simulation_id);
CREATE INDEX idx_simulation_timeline_time ON simulation_timeline(time_point);
COMMENT ON TABLE simulation_timeline IS '仿真时间切片数据表，用于前端动画展示，按指定时间切片提供电梯和乘客数据';
COMMENT ON COLUMN simulation_timeline.elevators IS 'JSONB格式存储电梯状态，包含位置、方向、门状态等';
COMMENT ON COLUMN simulation_timeline.passenger_events IS 'JSONB格式存储乘客事件，包含上车、下车等';simulation_timeline_partitioned - 分区版时间切片数据(针对大规模仿真)
CREATE TABLE simulation_timeline_partitioned (
id BIGSERIAL PRIMARY KEY,
simulation_id UUID NOT NULL,
time_point INTEGER NOT NULL,
elevators JSONB NOT NULL,
passenger_events JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (time_point);
-- 每1000秒创建一个分区
CREATE TABLE simulation_timeline_p0 PARTITION OF simulation_timeline_partitioned
FOR VALUES FROM (0) TO (1000);
CREATE TABLE simulation_timeline_p1 PARTITION OF simulation_timeline_partitioned
FOR VALUES FROM (1000) TO (2000);
-- 更多分区...
CREATE INDEX idx_simulation_timeline_partitioned_simulation ON simulation_timeline_partitioned(simulation_id);
CREATE INDEX idx_simulation_timeline_partitioned_time ON simulation_timeline_partitioned(time_point);
COMMENT ON TABLE simulation_timeline_partitioned IS '分区版仿真时间切片数据表，针对大规模仿真优化查询性能';7.3 索引设计与性能优化
7.3.1 关键索引设计
-- 建筑与楼层相关索引
CREATE INDEX idx_floor_building ON floor(building_id);
CREATE INDEX idx_floor_time_period_floor ON floor_time_period(floor_id);
-- 仿真相关索引
CREATE INDEX idx_simulation_building ON simulation(building_id);
CREATE INDEX idx_simulation_status ON simulation(status);
CREATE INDEX idx_simulation_created ON simulation(created_at DESC);
CREATE INDEX idx_simulation_result_simulation ON simulation_result(simulation_id);
-- OD矩阵相关索引
CREATE INDEX idx_od_matrix_simulation ON od_matrix(simulation_id) WHERE simulation_id IS NOT NULL;
CREATE INDEX idx_od_matrix_building ON od_matrix(building_id) WHERE building_id IS NOT NULL;
CREATE INDEX idx_od_matrix_entry_matrix ON od_matrix_entry(od_matrix_id);
CREATE INDEX od_matrix_entry_origin_dest_idx ON od_matrix_entry(origin_floor, destination_floor);
CREATE INDEX od_matrix_entry_prob_idx ON od_matrix_entry(probability DESC);
-- 电梯行程相关索引
CREATE INDEX idx_elevator_trip_simulation ON elevator_trip(simulation_id);
CREATE INDEX idx_elevator_trip_stop_trip_time ON elevator_trip_stop(trip_id, arrival_time);
-- 时序数据索引
CREATE INDEX idx_simulation_timeline_time_range ON simulation_timeline(simulation_id, time_point)
WHERE time_point BETWEEN 0 AND 3600; -- 常用时间范围7.3.2 性能优化策略
1. 时序数据优化
-- 为时间切片数据表添加BRIN索引(适合时间序列)
CREATE INDEX idx_simulation_timeline_brin ON simulation_timeline USING BRIN (time_point)
WITH (pages_per_range = 16);
-- 分区表的分区剪枝优化
-- 针对查询特定时间范围的场景
EXPLAIN ANALYZE
SELECT * FROM simulation_timeline
WHERE simulation_id = 'some-uuid' AND time_point BETWEEN 1800 AND 2400;2. OD矩阵查询优化
-- 创建函数索引，用于快速查询特定origin-destination对
CREATE INDEX idx_od_matrix_entry_origin_dest_func
ON od_matrix_entry ((origin_floor * 1000 + destination_floor));
-- 创建物化视图，用于快速获取建筑级OD矩阵
CREATE MATERIALIZED VIEW building_od_matrix AS
SELECT
origin_floor,
destination_floor,
SUM(passenger_count) AS total_passengers,
AVG(probability) AS avg_probability
FROM od_matrix_entry ome
JOIN od_matrix om ON ome.od_matrix_id = om.id
GROUP BY origin_floor, destination_floor;
CREATE INDEX idx_building_od_matrix ON building_od_matrix(origin_floor, destination_floor);3. 仿真结果查询优化
-- 创建部分索引，针对已完成的仿真
CREATE INDEX idx_simulation_completed ON simulation(id)
WHERE status = 'COMPLETED';
-- 创建函数索引，用于快速获取特定性能指标
CREATE INDEX idx_simulation_result_waiting_time
ON simulation_result(average_waiting_time)
WHERE average_waiting_time IS NOT NULL;4. 批量数据插入优化
-- 针对仿真结果的大批量插入，使用COPY命令替代INSERT
-- 示例Java代码:
/*
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(
"COPY simulation_timeline(simulation_id, time_point, elevators, passenger_events) " +
"FROM STDIN WITH (FORMAT csv)")) {
CopyManager copyManager = new CopyManager((BaseConnection) conn);
String csvData = simulationId + "," + timePoint + "," +
escapeJson(elevators) + "," + escapeJson(passengerEvents);
copyManager.copyIn(ps, new StringReader(csvData));
}
*/7.4 数据库函数与存储过程
7.4.1 OD矩阵验证函数
CREATE OR REPLACE FUNCTION validate_od_matrix(od_matrix_id UUID)
RETURNS TABLE (is_valid BOOLEAN, issues JSONB) AS $$
DECLARE
total_floors INTEGER;
issues_array JSONB := '[]'::jsonb;
floor INTEGER;
in_flow NUMERIC;
out_flow NUMERIC;
BEGIN
-- 获取建筑总楼层数
SELECT MAX(floor_number) INTO total_floors
FROM od_matrix_entry
WHERE od_matrix_id = validate_od_matrix.od_matrix_id;
-- 检查流量守恒
FOR floor IN 1..total_floors LOOP
-- 计算进入该楼层的流量
SELECT COALESCE(SUM(passenger_count), 0) INTO in_flow
FROM od_matrix_entry
WHERE od_matrix_id = validate_od_matrix.od_matrix_id
AND destination_floor = floor;
-- 计算离开该楼层的流量
SELECT COALESCE(SUM(passenger_count), 0) INTO out_flow
FROM od_matrix_entry
WHERE od_matrix_id = validate_od_matrix.od_matrix_id
AND origin_floor = floor;
-- 检查是否守恒
IF ABS(in_flow - out_flow) > 0.001 * GREATEST(in_flow, out_flow) THEN
issues_array := jsonb_set(issues_array, '{#}', jsonb_build_object(
'type', 'FLOW_CONSERVATION_VIOLATION',
'message', format('流量守恒违反: 楼层 %s, 出流量=%.1f, 入流量=%.1f', floor, out_flow, in_flow),
'floor', floor,
'in_flow', in_flow,
'out_flow', out_flow
));
END IF;
END LOOP;
-- 检查非负性
IF EXISTS (
SELECT 1 FROM od_matrix_entry
WHERE od_matrix_id = validate_od_matrix.od_matrix_id
AND passenger_count < 0
) THEN
issues_array := jsonb_set(issues_array, '{#}', jsonb_build_object(
'type', 'NEGATIVE_PASSENGER_COUNT',
'message', '发现负乘客数'
));
END IF;
RETURN QUERY
SELECT
jsonb_array_length(issues_array) = 0 AS is_valid,
issues_array AS issues;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION validate_od_matrix(UUID) IS '验证OD矩阵是否满足流量守恒原则，对应PDF中描述的"OD matrix must satisfy the flow conservation principle"';7.4.2 仿真结果聚合函数
CREATE OR REPLACE FUNCTION get_simulation_comparison(
simulation_ids UUID[]
)
RETURNS TABLE (
metric_name TEXT,
simulation_id UUID,
value NUMERIC
) AS $$
BEGIN
RETURN QUERY
-- 等待时间指标
SELECT 'average_waiting_time', simulation_id, average_waiting_time
FROM simulation_result
WHERE simulation_id = ANY(simulation_ids)
UNION ALL
SELECT 'max_waiting_time', simulation_id, max_waiting_time
FROM simulation_result
WHERE simulation_id = ANY(simulation_ids)
UNION ALL
-- 运输时间指标
SELECT 'average_travel_time', simulation_id, average_travel_time
FROM simulation_result
WHERE simulation_id = ANY(simulation_ids)
UNION ALL
SELECT 'max_travel_time', simulation_id, max_travel_time
FROM simulation_result
WHERE simulation_id = ANY(simulation_ids)
UNION ALL
-- 系统性能指标
SELECT 'handling_capacity', simulation_id, handling_capacity
FROM simulation_result
WHERE simulation_id = ANY(simulation_ids);
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION get_simulation_comparison(UUID[]) IS '获取多个仿真的关键指标比较，用于PDF中描述的"不同控制策略的对比分析"';7.4.3 电梯运行时间计算函数
CREATE OR REPLACE FUNCTION calculate_travel_time(
from_floor INTEGER,
to_floor INTEGER,
floor_height NUMERIC,
rated_speed NUMERIC,
acceleration NUMERIC,
deceleration NUMERIC
)
RETURNS NUMERIC AS $$
DECLARE
distance NUMERIC;
accel_distance NUMERIC;
decel_distance NUMERIC;
t_accel NUMERIC;
t_decel NUMERIC;
t_cruise NUMERIC;
BEGIN
-- 计算距离
distance := ABS(to_floor - from_floor) * floor_height;
-- 计算加速段距离
accel_distance := (rated_speed * rated_speed) / (2 * acceleration);
-- 计算减速段距离
decel_distance := (rated_speed * rated_speed) / (2 * deceleration);
-- 如果距离不足以达到额定速度
IF distance <= accel_distance + decel_distance THEN
-- 三段式运行: 加速-减速
RETURN 2 * SQRT(distance / acceleration);
ELSE
-- 三段式运行: 加速-匀速-减速
t_accel := rated_speed / acceleration;
t_decel := rated_speed / deceleration;
t_cruise := (distance - accel_distance - decel_distance) / rated_speed;
RETURN t_accel + t_cruise + t_decel;
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
COMMENT ON FUNCTION calculate_travel_time(INTEGER, INTEGER, NUMERIC, NUMERIC, NUMERIC, NUMERIC) IS '计算电梯理论运行时间，实现PDF中描述的"运行时间 = √(2×距离/加速度) + (距离-加速段-减速段)/额定速度 + √(2×距离/减速度)"';7.5 数据库维护策略
7.5.1 数据归档策略
-- 创建归档表
CREATE TABLE simulation_archive (LIKE simulation INCLUDING ALL);
CREATE TABLE simulation_result_archive (LIKE simulation_result INCLUDING ALL);
CREATE TABLE simulation_timeline_archive (LIKE simulation_timeline INCLUDING ALL);
-- 归档函数
CREATE OR REPLACE FUNCTION archive_old_simulations(days_threshold INTEGER DEFAULT 90)
RETURNS INTEGER AS $$
DECLARE
archived_count INTEGER := 0;
BEGIN
-- 归档仿真主记录
WITH archived_simulations AS (
INSERT INTO simulation_archive
SELECT * FROM simulation
WHERE status = 'COMPLETED'
AND end_time < NOW() - (days_threshold || ' days')::interval
RETURNING id
)
-- 归档仿真结果
INSERT INTO simulation_result_archive
SELECT sr.* FROM simulation_result sr
JOIN archived_simulations a ON sr.simulation_id = a.id;
-- 归档时间切片数据(使用批量删除)
WITH archived_simulations AS (
SELECT id FROM simulation
WHERE status = 'COMPLETED'
AND end_time < NOW() - (days_threshold || ' days')::interval
)
DELETE FROM simulation_timeline
WHERE simulation_id IN (SELECT id FROM archived_simulations)
RETURNING 1 INTO archived_count;
-- 最后删除主表记录
DELETE FROM simulation
WHERE status = 'COMPLETED'
AND end_time < NOW() - (days_threshold || ' days')::interval;
RETURN archived_count;
END;
$$ LANGUAGE plpgsql;
-- 创建定期归档任务
SELECT cron.schedule(
'archive-simulations',
'0 2 * * *', -- 每天凌晨2点
$$SELECT archive_old_simulations()$$
);7.5.2 性能监控与优化
-- 创建性能监控表
CREATE TABLE db_performance_metrics (
id BIGSERIAL PRIMARY KEY,
metric_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
metric_name VARCHAR(100) NOT NULL,
metric_value NUMERIC NOT NULL,
additional_info JSONB
);
-- 创建收集性能指标的函数
CREATE OR REPLACE FUNCTION collect_db_performance_metrics()
RETURNS VOID AS $$
BEGIN
-- 插入缓存命中率
INSERT INTO db_performance_metrics (metric_name, metric_value, additional_info)
SELECT
'cache_hit_ratio',
SUM(blks_hit) * 100.0 / (SUM(blks_hit) + SUM(blks_read)) AS cache_hit_ratio,
jsonb_build_object('total_blocks', SUM(blks_hit) + SUM(blks_read))
FROM pg_statio_user_tables;
-- 插入慢查询统计
INSERT INTO db_performance_metrics (metric_name, metric_value, additional_info)
SELECT
'slow_queries',
COUNT(*),
jsonb_build_object(
'avg_duration', AVG(total_time),
'max_duration', MAX(total_time)
)
FROM pg_stat_statements
WHERE total_time > 100; -- 超过100ms的查询
-- 插入索引使用率
INSERT INTO db_performance_metrics (metric_name, metric_value, additional_info)
SELECT
'index_usage_ratio',
SUM(idx_scan) * 100.0 / GREATEST(SUM(idx_scan) + SUM(seq_scan), 1) AS index_usage_ratio,
jsonb_build_object('total_scans', SUM(idx_scan) + SUM(seq_scan))
FROM pg_stat_user_tables;
END;
$$ LANGUAGE plpgsql;
-- 创建定期收集任务
SELECT cron.schedule(
'collect-db-metrics',
'*/15 * * * *', -- 每15分钟
$$SELECT collect_db_performance_metrics()$$
);7.5.3 数据验证与修复
-- 验证OD矩阵并修复
CREATE OR REPLACE FUNCTION repair_inconsistent_od_matrix()
RETURNS TABLE (fixed_od_matrix_id UUID, issues_fixed JSONB) AS $$
DECLARE
od_matrix_record RECORD;
issues JSONB;
is_valid BOOLEAN;
BEGIN
FOR od_matrix_record IN
SELECT id FROM od_matrix
LOOP
-- 验证OD矩阵
SELECT is_valid, issues INTO is_valid, issues
FROM validate_od_matrix(od_matrix_record.id);
IF NOT is_valid THEN
-- 尝试修复流量守恒问题
IF issues->'issues'->0->>'type' = 'FLOW_CONSERVATION_VIOLATION' THEN
-- 简单修复：按比例调整
PERFORM adjust_od_matrix_for_flow_conservation(od_matrix_record.id);
issues_fixed := jsonb_build_array(jsonb_build_object(
'type', 'FLOW_CONSERVATION_REPAIRED',
'message', '已修复流量守恒问题'
));
RETURN QUERY
SELECT od_matrix_record.id, issues_fixed;
END IF;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- 辅助函数：调整OD矩阵以满足流量守恒
CREATE OR REPLACE FUNCTION adjust_od_matrix_for_flow_conservation(od_matrix_id UUID)
RETURNS VOID AS $$
DECLARE
total_floors INTEGER;
floor INTEGER;
in_flow NUMERIC;
out_flow NUMERIC;
adjustment_factor NUMERIC;
BEGIN
-- 获取建筑总楼层数
SELECT MAX(floor_number) INTO total_floors
FROM od_matrix_entry
WHERE od_matrix_id = adjust_od_matrix_for_flow_conservation.od_matrix_id;
-- 调整每个楼层的流出量以匹配流入量
FOR floor IN 1..total_floors LOOP
-- 计算进入该楼层的流量
SELECT COALESCE(SUM(passenger_count), 0) INTO in_flow
FROM od_matrix_entry
WHERE od_matrix_id = adjust_od_matrix_for_flow_conservation.od_matrix_id
AND destination_floor = floor;
-- 计算离开该楼层的流量
SELECT COALESCE(SUM(passenger_count), 0) INTO out_flow
FROM od_matrix_entry
WHERE od_matrix_id = adjust_od_matrix_for_flow_conservation.od_matrix_id
AND origin_floor = floor;
-- 如果流出不等于流入，进行调整
IF out_flow > 0 AND in_flow <> out_flow THEN
adjustment_factor := in_flow / out_flow;
-- 调整该楼层作为起点的所有条目
UPDATE od_matrix_entry
SET passenger_count = passenger_count * adjustment_factor,
probability = probability * adjustment_factor
WHERE od_matrix_id = adjust_od_matrix_for_flow_conservation.od_matrix_id
AND origin_floor = floor;
END IF;
END LOOP;
-- 重新计算概率
WITH total_passengers AS (
SELECT SUM(passenger_count) AS total
FROM od_matrix_entry
WHERE od_matrix_id = adjust_od_matrix_for_flow_conservation.od_matrix_id
)
UPDATE od_matrix_entry ome
SET probability = ome.passenger_count::NUMERIC / tp.total
FROM total_passengers tp
WHERE ome.od_matrix_id = adjust_od_matrix_for_flow_conservation.od_matrix_id;
END;
$$ LANGUAGE plpgsql;开发文档下一章节标题：8. 附录：关键公式、参数设置建议