249 lines
9.0 KiB
SQL
249 lines
9.0 KiB
SQL
-- 为学生健康监测系统生成测试数据
|
||
-- 这个脚本将创建测试的教师、班级、学生和健康数据
|
||
|
||
-- 1. 创建测试班级
|
||
INSERT INTO ak_classes (id, name, description, created_at) VALUES
|
||
('class_001', '计算机科学1班', '计算机科学专业一年级1班', NOW()),
|
||
('class_002', '软件工程1班', '软件工程专业一年级1班', NOW()),
|
||
('class_003', '信息管理1班', '信息管理专业一年级1班', NOW())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- 2. 创建测试教师
|
||
INSERT INTO ak_users (id, name, email, role, avatar, class_id, student_id, created_at) VALUES
|
||
('teacher_001', '张老师', 'zhang@school.com', 'teacher', 'https://via.placeholder.com/100x100?text=张', NULL, NULL, NOW())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- 3. 设置教师负责的班级
|
||
INSERT INTO ak_teacher_roles (user_id, class_id, created_at) VALUES
|
||
('teacher_001', 'class_001', NOW()),
|
||
('teacher_001', 'class_002', NOW())
|
||
ON CONFLICT (user_id, class_id) DO NOTHING;
|
||
|
||
-- 4. 创建测试学生
|
||
INSERT INTO ak_users (id, name, email, role, avatar, class_id, student_id, created_at) VALUES
|
||
-- 计算机科学1班学生
|
||
('student_001', '王小明', 'wang@student.com', 'student', 'https://via.placeholder.com/100x100?text=王', 'class_001', '2024001', NOW()),
|
||
('student_002', '李小红', 'li@student.com', 'student', NULL, 'class_001', '2024002', NOW()),
|
||
('student_003', '张小华', 'zhang@student.com', 'student', 'https://via.placeholder.com/100x100?text=张', 'class_001', '2024003', NOW()),
|
||
('student_004', '刘小刚', 'liu@student.com', 'student', NULL, 'class_001', '2024004', NOW()),
|
||
|
||
-- 软件工程1班学生
|
||
('student_005', '陈小美', 'chen@student.com', 'student', 'https://via.placeholder.com/100x100?text=陈', 'class_002', '2024005', NOW()),
|
||
('student_006', '赵小强', 'zhao@student.com', 'student', NULL, 'class_002', '2024006', NOW()),
|
||
('student_007', '孙小丽', 'sun@student.com', 'student', 'https://via.placeholder.com/100x100?text=孙', 'class_002', '2024007', NOW()),
|
||
|
||
-- 信息管理1班学生（不属于张老师管理）
|
||
('student_008', '吴小亮', 'wu@student.com', 'student', NULL, 'class_003', '2024008', NOW()),
|
||
('student_009', '马小静', 'ma@student.com', 'student', 'https://via.placeholder.com/100x100?text=马', 'class_003', '2024009', NOW())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- 5. 生成传感器主记录
|
||
INSERT INTO ss_sensor_measurements (id, user_id, device_id, measurement_time, created_at) VALUES
|
||
-- 最近的数据（1小时内）
|
||
('measurement_001', 'student_001', 'device_001', NOW() - INTERVAL '30 minutes', NOW()),
|
||
('measurement_002', 'student_002', 'device_002', NOW() - INTERVAL '45 minutes', NOW()),
|
||
('measurement_003', 'student_003', 'device_003', NOW() - INTERVAL '20 minutes', NOW()),
|
||
('measurement_004', 'student_004', 'device_004', NOW() - INTERVAL '15 minutes', NOW()),
|
||
('measurement_005', 'student_005', 'device_005', NOW() - INTERVAL '25 minutes', NOW()),
|
||
('measurement_006', 'student_006', 'device_006', NOW() - INTERVAL '35 minutes', NOW()),
|
||
('measurement_007', 'student_007', 'device_007', NOW() - INTERVAL '40 minutes', NOW()),
|
||
|
||
-- 较早的数据（1天前）
|
||
('measurement_101', 'student_001', 'device_001', NOW() - INTERVAL '1 day', NOW()),
|
||
('measurement_102', 'student_002', 'device_002', NOW() - INTERVAL '1 day', NOW()),
|
||
('measurement_103', 'student_003', 'device_003', NOW() - INTERVAL '1 day', NOW())
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- 6. 生成体温数据
|
||
INSERT INTO ss_temperature (measurement_id, temperature_c, created_at) VALUES
|
||
-- 正常体温
|
||
('measurement_001', 36.5, NOW()),
|
||
('measurement_003', 36.8, NOW()),
|
||
('measurement_005', 36.3, NOW()),
|
||
('measurement_007', 36.6, NOW()),
|
||
|
||
-- 异常体温（发烧）
|
||
('measurement_002', 38.2, NOW()), -- 李小红发烧
|
||
('measurement_004', 37.8, NOW()), -- 刘小刚体温偏高
|
||
('measurement_006', 35.5, NOW()), -- 赵小强体温偏低
|
||
|
||
-- 历史数据
|
||
('measurement_101', 36.4, NOW()),
|
||
('measurement_102', 36.7, NOW()),
|
||
('measurement_103', 36.5, NOW())
|
||
ON CONFLICT (measurement_id) DO NOTHING;
|
||
|
||
-- 7. 生成心率数据
|
||
INSERT INTO ss_heart_rate (measurement_id, heart_rate_bpm, created_at) VALUES
|
||
-- 正常心率
|
||
('measurement_001', 75, NOW()),
|
||
('measurement_003', 82, NOW()),
|
||
('measurement_005', 68, NOW()),
|
||
|
||
-- 异常心率
|
||
('measurement_002', 110, NOW()), -- 李小红心率过快（配合发烧）
|
||
('measurement_004', 95, NOW()), -- 刘小刚心率偏快
|
||
('measurement_006', 45, NOW()), -- 赵小强心率过慢
|
||
('measurement_007', 105, NOW()), -- 孙小丽心率偏快
|
||
|
||
-- 历史数据
|
||
('measurement_101', 78, NOW()),
|
||
('measurement_102', 72, NOW()),
|
||
('measurement_103', 80, NOW())
|
||
ON CONFLICT (measurement_id) DO NOTHING;
|
||
|
||
-- 8. 生成血氧数据
|
||
INSERT INTO ss_spo2 (measurement_id, spo2_percent, created_at) VALUES
|
||
-- 正常血氧
|
||
('measurement_001', 98, NOW()),
|
||
('measurement_003', 97, NOW()),
|
||
('measurement_005', 99, NOW()),
|
||
('measurement_007', 96, NOW()),
|
||
|
||
-- 异常血氧
|
||
('measurement_002', 93, NOW()), -- 李小红血氧偏低（配合发烧）
|
||
('measurement_004', 94, NOW()), -- 刘小刚血氧偏低
|
||
('measurement_006', 92, NOW()), -- 赵小强血氧偏低
|
||
|
||
-- 历史数据
|
||
('measurement_101', 98, NOW()),
|
||
('measurement_102', 97, NOW()),
|
||
('measurement_103', 96, NOW())
|
||
ON CONFLICT (measurement_id) DO NOTHING;
|
||
|
||
-- 9. 生成步数数据
|
||
INSERT INTO ss_steps (measurement_id, step_count, created_at) VALUES
|
||
-- 正常步数
|
||
('measurement_001', 8500, NOW()),
|
||
('measurement_002', 6200, NOW()),
|
||
('measurement_003', 9200, NOW()),
|
||
('measurement_004', 7800, NOW()),
|
||
('measurement_005', 10200, NOW()),
|
||
('measurement_006', 5500, NOW()),
|
||
('measurement_007', 8800, NOW()),
|
||
|
||
-- 历史数据
|
||
('measurement_101', 7200, NOW()),
|
||
('measurement_102', 8900, NOW()),
|
||
('measurement_103', 6800, NOW())
|
||
ON CONFLICT (measurement_id) DO NOTHING;
|
||
|
||
-- 10. 创建或更新健康数据视图
|
||
CREATE OR REPLACE VIEW v_latest_student_health AS
|
||
WITH latest_measurements AS (
|
||
SELECT
|
||
user_id,
|
||
MAX(measurement_time) as latest_time
|
||
FROM ss_sensor_measurements
|
||
WHERE user_id IN (
|
||
SELECT id FROM ak_users WHERE role = 'student'
|
||
)
|
||
GROUP BY user_id
|
||
),
|
||
student_health_data AS (
|
||
SELECT
|
||
u.id as user_id,
|
||
u.name,
|
||
u.student_id,
|
||
u.avatar,
|
||
u.class_id,
|
||
sm.measurement_time,
|
||
sm.id as measurement_id
|
||
FROM ak_users u
|
||
LEFT JOIN latest_measurements lm ON u.id = lm.user_id
|
||
LEFT JOIN ss_sensor_measurements sm ON u.id = sm.user_id AND sm.measurement_time = lm.latest_time
|
||
WHERE u.role = 'student'
|
||
)
|
||
SELECT
|
||
shd.user_id,
|
||
shd.name,
|
||
shd.student_id,
|
||
shd.avatar,
|
||
shd.class_id,
|
||
shd.measurement_time as latest_measurement_time,
|
||
-- 体温数据
|
||
st.temperature_c,
|
||
st.created_at as temp_time,
|
||
-- 心率数据
|
||
shr.heart_rate_bpm,
|
||
shr.created_at as heart_time,
|
||
-- 血氧数据
|
||
ssp.spo2_percent,
|
||
ssp.created_at as spo2_time,
|
||
-- 步数数据
|
||
sst.step_count,
|
||
sst.created_at as steps_time
|
||
FROM student_health_data shd
|
||
LEFT JOIN ss_temperature st ON shd.measurement_id = st.measurement_id
|
||
LEFT JOIN ss_heart_rate shr ON shd.measurement_id = shr.measurement_id
|
||
LEFT JOIN ss_spo2 ssp ON shd.measurement_id = ssp.measurement_id
|
||
LEFT JOIN ss_steps sst ON shd.measurement_id = sst.measurement_id;
|
||
|
||
-- 测试查询：验证数据是否正确生成
|
||
-- 这将显示张老师负责的学生健康数据
|
||
SELECT
|
||
vlsh.*,
|
||
-- 异常判断
|
||
CASE
|
||
WHEN temperature_c < 36.0 OR temperature_c > 37.5 THEN 'TEMP_ABNORMAL'
|
||
ELSE 'TEMP_NORMAL'
|
||
END as temp_status,
|
||
CASE
|
||
WHEN heart_rate_bpm < 60 OR heart_rate_bpm > 100 THEN 'HR_ABNORMAL'
|
||
ELSE 'HR_NORMAL'
|
||
END as hr_status,
|
||
CASE
|
||
WHEN spo2_percent < 95 THEN 'SPO2_ABNORMAL'
|
||
ELSE 'SPO2_NORMAL'
|
||
END as spo2_status
|
||
FROM v_latest_student_health vlsh
|
||
WHERE user_id IN (
|
||
SELECT u.id
|
||
FROM ak_users u
|
||
WHERE u.role = 'student'
|
||
AND u.class_id IN (
|
||
SELECT class_id
|
||
FROM ak_teacher_roles
|
||
WHERE user_id = 'teacher_001'
|
||
)
|
||
)
|
||
ORDER BY
|
||
-- 异常优先排序
|
||
CASE
|
||
WHEN (temperature_c < 36.0 OR temperature_c > 37.5 OR
|
||
heart_rate_bpm < 60 OR heart_rate_bpm > 100 OR
|
||
spo2_percent < 95)
|
||
THEN 0
|
||
ELSE 1
|
||
END,
|
||
name;
|
||
|
||
-- 显示创建的数据统计
|
||
SELECT
|
||
'教师数量' as type, COUNT(*) as count
|
||
FROM ak_users WHERE role = 'teacher'
|
||
UNION ALL
|
||
SELECT
|
||
'学生数量' as type, COUNT(*) as count
|
||
FROM ak_users WHERE role = 'student'
|
||
UNION ALL
|
||
SELECT
|
||
'张老师负责的学生' as type, COUNT(*) as count
|
||
FROM ak_users u
|
||
WHERE u.role = 'student'
|
||
AND u.class_id IN (
|
||
SELECT class_id FROM ak_teacher_roles WHERE user_id = 'teacher_001'
|
||
)
|
||
UNION ALL
|
||
SELECT
|
||
'有健康数据的学生' as type, COUNT(DISTINCT user_id) as count
|
||
FROM v_latest_student_health
|
||
WHERE temperature_c IS NOT NULL OR heart_rate_bpm IS NOT NULL OR spo2_percent IS NOT NULL
|
||
UNION ALL
|
||
SELECT
|
||
'异常健康指标的学生' as type, COUNT(*) as count
|
||
FROM v_latest_student_health
|
||
WHERE temperature_c < 36.0 OR temperature_c > 37.5 OR
|
||
heart_rate_bpm < 60 OR heart_rate_bpm > 100 OR
|
||
spo2_percent < 95;
|