311 lines
20 KiB
SQL
311 lines
20 KiB
SQL
-- 智跑平台真实用户ID测试数据补充（最终版本）
|
||
-- 生成日期: 2025-06-16
|
||
-- 用途: 为指定的真实用户ID创建完整的测试数据，所有UUID格式已修正
|
||
-- 学生用户ID: 7bf7378e-a027-473e-97ac-3460ed3f170a
|
||
-- 教师用户ID: eed3824b-bba1-4309-8048-19d17367c084
|
||
|
||
-- ===============================
|
||
-- 针对真实学生用户的专门测试数据
|
||
-- ===============================
|
||
|
||
-- 0. 先创建训练计划（避免外键约束错误）
|
||
INSERT INTO public.ak_training_plans (id, user_id, plan_name, description, start_date, end_date, status, created_at) VALUES
|
||
('11111111-3333-1111-1111-111111111111', '7bf7378e-a027-473e-97ac-3460ed3f170a', '期中体能训练计划', '为期中体能测试准备的综合训练计划，包含跳绳、俯卧撑、跑步等多项训练内容', '2025-06-11', '2025-06-25', 'active', now());
|
||
|
||
-- 0.1. 先创建设备记录（避免外键约束错误）
|
||
INSERT INTO public.ak_devices (id, user_id, device_type, device_name, device_mac, bind_time, status, extra) VALUES
|
||
('12345678-1234-5678-9abc-123456789012', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'smartwatch', '华为手表GT3', 'AA:BB:CC:DD:EE:FF', now(), 'active', '{"brand": "华为", "model": "GT3", "firmware": "1.2.3"}');
|
||
|
||
-- 1. 创建训练计划项目
|
||
INSERT INTO public.ak_training_plan_items (id, plan_id, item_date, activity_type, target_value, unit, description, created_at) VALUES
|
||
('47e3bc40-a5d2-4c8e-b9f7-82a1b3c4d5e6', '11111111-3333-1111-1111-111111111111', '2025-06-11', '跳绳', 500, '次', '跳绳训练：分5组完成，每组100次，组间休息30秒', now()),
|
||
('47e3bc40-a5d2-4c8e-b9f7-82a1b3c4d5e7', '11111111-3333-1111-1111-111111111111', '2025-06-12', '俯卧撑', 30, '次', '俯卧撑训练：分3组完成，每组10次，组间休息60秒', now()),
|
||
('47e3bc40-a5d2-4c8e-b9f7-82a1b3c4d5e8', '11111111-3333-1111-1111-111111111111', '2025-06-13', '跑步', 3.5, '公里', '跑步训练：3.5公里，配速控制在8:30/km左右', now());
|
||
|
||
-- 1. 学生用户的更多训练记录
|
||
INSERT INTO public.ak_training_records (id, user_id, plan_item_id, device_id, activity_type, start_time, end_time, duration_sec, distance_km, calories, steps, avg_heart_rate, max_heart_rate, data, created_at) VALUES
|
||
('550e8400-e29b-41d4-a716-446655440001', '7bf7378e-a027-473e-97ac-3460ed3f170a', '47e3bc40-a5d2-4c8e-b9f7-82a1b3c4d5e6', '12345678-1234-5678-9abc-123456789012', '跳绳', '2025-06-11 07:00:00+08', '2025-06-11 07:15:00+08', 900, 0, 150, 0, 130, 160, '{"total_jumps": 500, "sets": 5, "rest_between_sets": "30sec"}', now()),
|
||
('550e8400-e29b-41d4-a716-446655440002', '7bf7378e-a027-473e-97ac-3460ed3f170a', '47e3bc40-a5d2-4c8e-b9f7-82a1b3c4d5e7', '12345678-1234-5678-9abc-123456789012', '俯卧撑', '2025-06-12 18:00:00+08', '2025-06-12 18:10:00+08', 600, 0, 80, 0, 110, 140, '{"total_pushups": 30, "sets": 3, "reps_per_set": 10}', now()),
|
||
('550e8400-e29b-41d4-a716-446655440003', '7bf7378e-a027-473e-97ac-3460ed3f170a', '47e3bc40-a5d2-4c8e-b9f7-82a1b3c4d5e8', '12345678-1234-5678-9abc-123456789012', '跑步', '2025-06-13 06:30:00+08', '2025-06-13 07:00:00+08', 1800, 3.5, 240, 4500, 125, 155, '{"pace": "8:30/km", "route": "公园环路", "weather": "多云"}', now()),
|
||
('550e8400-e29b-41d4-a716-446655440004', '7bf7378e-a027-473e-97ac-3460ed3f170a', NULL, '12345678-1234-5678-9abc-123456789012', '自由训练', '2025-06-14 07:00:00+08', '2025-06-14 07:45:00+08', 2700, 4.2, 300, 5200, 135, 165, '{"activity": "晨跑+拉伸", "note": "感觉状态很好"}', now());
|
||
|
||
-- 2. 学生用户的体征数据
|
||
INSERT INTO public.ak_biometric_data (id, user_id, device_id, data_type, value, unit, measured_at, extra) VALUES
|
||
('550e8400-e29b-41d4-a716-446655440011', '7bf7378e-a027-473e-97ac-3460ed3f170a', '12345678-1234-5678-9abc-123456789012', 'heart_rate', 125, 'bpm', '2025-06-11 07:08:00+08', '{"context": "跳绳运动中"}'),
|
||
('550e8400-e29b-41d4-a716-446655440012', '7bf7378e-a027-473e-97ac-3460ed3f170a', '12345678-1234-5678-9abc-123456789012', 'heart_rate', 68, 'bpm', '2025-06-11 22:00:00+08', '{"context": "睡前静息"}'),
|
||
('550e8400-e29b-41d4-a716-446655440013', '7bf7378e-a027-473e-97ac-3460ed3f170a', '12345678-1234-5678-9abc-123456789012', 'sleep', 8.2, 'hours', '2025-06-12 07:00:00+08', '{"deep_sleep": 3.1, "light_sleep": 4.8, "rem_sleep": 0.3}'),
|
||
('550e8400-e29b-41d4-a716-446655440014', '7bf7378e-a027-473e-97ac-3460ed3f170a', '12345678-1234-5678-9abc-123456789012', 'steps', 9200, 'count', '2025-06-12 23:59:00+08', '{"daily_goal": 10000, "goal_completion": "92%"}'),
|
||
('550e8400-e29b-41d4-a716-446655440015', '7bf7378e-a027-473e-97ac-3460ed3f170a', '12345678-1234-5678-9abc-123456789012', 'weight', 65.2, 'kg', '2025-06-13 07:00:00+08', '{"bmi": 21.3, "body_fat": "12%"}');
|
||
|
||
-- 3. 学生用户的AI分析报告
|
||
INSERT INTO public.ak_ai_reports (id, user_id, report_type, period_start, period_end, analysis_data, created_at) VALUES
|
||
('550e8400-e29b-41d4-a716-446655440021', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'weekly_summary', '2025-06-10', '2025-06-16', '{
|
||
"total_activities": 6,
|
||
"total_distance": 13.7,
|
||
"total_calories": 980,
|
||
"avg_heart_rate": 127,
|
||
"fitness_score": 82,
|
||
"improvement_areas": ["力量训练", "柔韧性"],
|
||
"achievements": ["完成本周所有训练目标", "跑步配速稳步提升"],
|
||
"recommendations": [
|
||
"建议增加力量训练频次，每周至少2次",
|
||
"加入瑜伽或拉伸训练提高柔韧性",
|
||
"保持当前的跑步训练强度"
|
||
],
|
||
"trend_analysis": {
|
||
"distance_trend": "稳步上升",
|
||
"pace_trend": "持续改善",
|
||
"heart_rate_trend": "控制良好",
|
||
"consistency": "优秀"
|
||
},
|
||
"weekly_comparison": {
|
||
"vs_last_week": "+15%",
|
||
"vs_monthly_avg": "+8%"
|
||
}
|
||
}', now()),
|
||
('550e8400-e29b-41d4-a716-446655440022', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'technique_analysis', '2025-06-10', '2025-06-10', '{
|
||
"activity_type": "跑步",
|
||
"technique_score": 87,
|
||
"strengths": ["步频稳定", "呼吸节奏好", "姿态端正"],
|
||
"weaknesses": ["步幅可以更大", "摆臂需要更有力"],
|
||
"detailed_analysis": {
|
||
"posture": {"score": 85, "comment": "整体姿态良好，头部保持稳定"},
|
||
"breathing": {"score": 90, "comment": "呼吸与步频配合很好"},
|
||
"stride": {"score": 82, "comment": "步幅适中，建议可以适当增大"},
|
||
"arm_swing": {"score": 78, "comment": "摆臂动作需要更有力度"}
|
||
},
|
||
"training_suggestions": [
|
||
"练习高抬腿增强步幅",
|
||
"加强核心力量训练",
|
||
"注意手臂摆动的力度和节奏"
|
||
]
|
||
}', now());
|
||
|
||
-- 4. 学生用户的通知记录
|
||
INSERT INTO public.ak_notifications (id, user_id, type, title, content, data, is_read, created_at) VALUES
|
||
('c3e8f2a0-9b4c-4d6e-8f7a-1b2c3d4e5f61', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'training_reminder', '今日训练提醒', '今天的俯卧撑训练还没完成，加油！', '{"plan_item_id": "47e3bc40-a5d2-4c8e-b9f7-82a1b3c4d5e7", "target": "30次"}', false, now()),
|
||
('c3e8f2a0-9b4c-4d6e-8f7a-1b2c3d4e5f62', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'achievement', '新成就解锁！', '连续完成训练计划4天，获得"训练达人"称号', '{"achievement_id": "training_master_4days", "points_earned": 80}', false, now()),
|
||
('c3e8f2a0-9b4c-4d6e-8f7a-1b2c3d4e5f63', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'report_ready', 'AI分析报告更新', '您的跑步技术分析报告已更新，快来查看详细建议吧！', '{"report_id": "550e8400-e29b-41d4-a716-446655440022"}', true, now()),
|
||
('c3e8f2a0-9b4c-4d6e-8f7a-1b2c3d4e5f60', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'social_interaction', '好友互动', '您的好友李小红为您的训练记录点了赞', '{"from_user": "李小红", "action": "like", "target": "训练记录"}', false, now());
|
||
|
||
-- 5. 学生用户的积分记录
|
||
INSERT INTO public.ak_user_points (id, user_id, total_points, available_points, created_at, updated_at) VALUES
|
||
('a7b8c9d0-e1f2-4a3b-9c8d-7e6f5a4b3c21', '7bf7378e-a027-473e-97ac-3460ed3f170a', 1580, 1200, now(), now());
|
||
|
||
INSERT INTO public.ak_point_transactions (id, user_id, transaction_type, points, description, reference_id, created_at) VALUES
|
||
('b8c9d0e1-f2a3-4b5c-9d8e-7f6a5b4c3d21', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'earn', 50, '完成跑步训练', '550e8400-e29b-41d4-a716-446655440003', now()),
|
||
('b8c9d0e1-f2a3-4b5c-9d8e-7f6a5b4c3d22', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'earn', 30, '完成跳绳训练', '550e8400-e29b-41d4-a716-446655440001', now()),
|
||
('b8c9d0e1-f2a3-4b5c-9d8e-7f6a5b4c3d23', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'earn', 25, '完成俯卧撑训练', '550e8400-e29b-41d4-a716-446655440002', now()),
|
||
('b8c9d0e1-f2a3-4b5c-9d8e-7f6a5b4c3d24', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'earn', 80, '获得训练达人成就', 'training_master_4days', now()),
|
||
('b8c9d0e1-f2a3-4b5c-9d8e-7f6a5b4c3d25', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'earn', 100, '完成周训练目标', 'weekly_goal_completed', now()),
|
||
('b8c9d0e1-f2a3-4b5c-9d8e-7f6a5b4c3d26', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'spend', -180, '兑换运动T恤', 'reward_tshirt_001', now());
|
||
|
||
-- ===============================
|
||
-- 针对真实教师用户的专门测试数据
|
||
-- ===============================
|
||
|
||
-- 6. 教师用户的教学资源
|
||
INSERT INTO public.ak_teaching_resources (id, teacher_id, type, title, description, content_url, tags, is_public, created_at) VALUES
|
||
('c9d0e1f2-a3b4-4c5d-9e8f-7a6b5c4d3e21', 'eed3824b-bba1-4309-8048-19d17367c084', 'video', '青少年体能训练指导', '针对中学生的体能训练方法和注意事项', 'https://example.com/videos/youth_fitness_guide.mp4', '["青少年", "体能训练", "指导视频"]', true, now()),
|
||
('c9d0e1f2-a3b4-4c5d-9e8f-7a6b5c4d3e22', 'eed3824b-bba1-4309-8048-19d17367c084', 'document', '运动损伤预防手册', '常见运动损伤的预防和处理方法', 'https://example.com/docs/injury_prevention.pdf', '["运动损伤", "预防", "处理"]', true, now()),
|
||
('c9d0e1f2-a3b4-4c5d-9e8f-7a6b5c4d3e23', 'eed3824b-bba1-4309-8048-19d17367c084', 'plan', '春季训练计划模板', '适合高中生的春季体能训练计划模板', 'https://example.com/templates/spring_training.json', '["训练计划", "高中生", "春季"]', false, now()),
|
||
('c9d0e1f2-a3b4-4c5d-9e8f-7a6b5c4d3e24', 'eed3824b-bba1-4309-8048-19d17367c084', 'video', '跑步技术动作示范', '标准跑步动作的分解示范和常见错误纠正', 'https://example.com/videos/running_technique_demo.mp4', '["跑步技术", "动作示范", "纠错"]', true, now());
|
||
|
||
-- 7. 教师用户布置的作业
|
||
INSERT INTO public.ak_assignments (id, teacher_id, school_id, class_id, title, description, requirements, due_date, status, created_at) VALUES
|
||
('d0e1f2a3-b4c5-4d6e-9f8a-7b6c5d4e3f21', 'eed3824b-bba1-4309-8048-19d17367c084', '11111111-2222-1111-1111-111111111111', '11111111-4444-1111-1111-111111111111', '期中体能测试准备', '为期中体能测试做准备，包含多项基础体能训练', '{
|
||
"duration": "2周",
|
||
"activities": [
|
||
{"type": "50米冲刺", "target": "每日3次", "standard": "男生<8秒，女生<9秒"},
|
||
{"type": "1000米跑", "target": "每周2次", "standard": "男生<4分30秒，女生<5分钟"},
|
||
{"type": "俯卧撑", "target": "每日进行", "standard": "男生30个，女生20个"},
|
||
{"type": "仰卧起坐", "target": "每日进行", "standard": "男生40个，女生35个"}
|
||
],
|
||
"submission_requirements": ["每日训练记录", "心率数据", "自我评估"],
|
||
"evaluation_criteria": ["完成度50%", "数据质量30%", "进步幅度20%"]
|
||
}', '2025-06-30', 'active', now()),
|
||
('d0e1f2a3-b4c5-4d6e-9f8a-7b6c5d4e3f22', 'eed3824b-bba1-4309-8048-19d17367c084', '11111111-2222-1111-1111-111111111111', '11111111-4444-1111-1111-111111111111', '跑步技术改进训练', '根据AI分析结果，针对性改进跑步技术', '{
|
||
"duration": "1周",
|
||
"focus_areas": ["步频控制", "摆臂动作", "呼吸节奏"],
|
||
"daily_tasks": [
|
||
"原地跑步练习10分钟",
|
||
"摆臂动作练习5分钟",
|
||
"呼吸节奏训练配合慢跑2公里"
|
||
],
|
||
"video_submission": true,
|
||
"ai_analysis_required": true
|
||
}', '2025-06-23', 'active', now());
|
||
|
||
-- 8. 学生提交的作业 (针对教师的作业)
|
||
INSERT INTO public.ak_assignment_submissions (id, assignment_id, student_id, submission_data, submission_time, status, score, teacher_feedback) VALUES
|
||
('e1f2a3b4-c5d6-4e7f-9a8b-7c6d5e4f3a21', 'd0e1f2a3-b4c5-4d6e-9f8a-7b6c5d4e3f21', '7bf7378e-a027-473e-97ac-3460ed3f170a', '{
|
||
"training_log": [
|
||
{"date": "2025-06-16", "50m_sprint": ["7.8s", "7.6s", "7.9s"], "pushups": 32, "situps": 42, "heart_rate_avg": 125},
|
||
{"date": "2025-06-17", "50m_sprint": ["7.7s", "7.5s", "7.8s"], "pushups": 35, "situps": 45, "heart_rate_avg": 128},
|
||
{"date": "2025-06-18", "1000m_run": "4:28", "pushups": 36, "situps": 46, "heart_rate_avg": 145}
|
||
],
|
||
"self_assessment": "感觉50米冲刺有明显进步，1000米跑还需要加强耐力训练。",
|
||
"challenges": "仰卧起坐动作需要更标准",
|
||
"progress_notes": "整体体能有提升，继续坚持训练"
|
||
}', now(), 'submitted', 92, '训练记录详细，进步明显。50米冲刺成绩优秀，已达到优秀标准。建议继续保持训练强度，注意仰卧起坐的动作规范性。'),
|
||
('e1f2a3b4-c5d6-4e7f-9a8b-7c6d5e4f3a22', 'd0e1f2a3-b4c5-4d6e-9f8a-7b6c5d4e3f22', '7bf7378e-a027-473e-97ac-3460ed3f170a', '{
|
||
"video_submissions": ["running_technique_day1.mp4", "running_technique_day3.mp4", "running_technique_day5.mp4"],
|
||
"training_records": [
|
||
{"date": "2025-06-17", "in_place_running": "10min", "arm_swing_practice": "5min", "breathing_run": "2km in 10:30"},
|
||
{"date": "2025-06-18", "in_place_running": "10min", "arm_swing_practice": "5min", "breathing_run": "2km in 10:15"},
|
||
{"date": "2025-06-19", "in_place_running": "10min", "arm_swing_practice": "5min", "breathing_run": "2km in 10:00"}
|
||
],
|
||
"self_reflection": "通过练习，感觉步频更稳定了，摆臂动作也更协调",
|
||
"improvement_areas": "呼吸节奏还需要进一步调整"
|
||
}', now(), 'graded', 88, 'AI分析显示跑步技术有明显改善，步频稳定性提升15%，摆臂协调性提升20%。建议继续加强呼吸节奏的训练。');
|
||
|
||
-- 9. 教师的课程安排
|
||
INSERT INTO public.ak_class_schedules (id, teacher_id, school_id, class_id, subject, day_of_week, start_time, end_time, location, created_at) VALUES
|
||
('f2a3b4c5-d6e7-4f8a-9b8c-7d6e5f4a3b21', 'eed3824b-bba1-4309-8048-19d17367c084', '11111111-2222-1111-1111-111111111111', '11111111-4444-1111-1111-111111111111', '体育', 1, '08:00:00', '08:45:00', '学校操场', now()),
|
||
('f2a3b4c5-d6e7-4f8a-9b8c-7d6e5f4a3b22', 'eed3824b-bba1-4309-8048-19d17367c084', '11111111-2222-1111-1111-111111111111', '11111111-4444-1111-1111-111111111111', '体育', 3, '09:55:00', '10:40:00', '体育馆', now()),
|
||
('f2a3b4c5-d6e7-4f8a-9b8c-7d6e5f4a3b23', 'eed3824b-bba1-4309-8048-19d17367c084', '11111111-2222-1111-1111-111111111111', '11111111-4444-1111-1111-111111111112', '体育', 2, '14:00:00', '14:45:00', '操场', now()),
|
||
('f2a3b4c5-d6e7-4f8a-9b8c-7d6e5f4a3b24', 'eed3824b-bba1-4309-8048-19d17367c084', '11111111-2222-1111-1111-111111111111', '11111111-4444-1111-1111-111111111112', '体育', 4, '15:30:00', '16:15:00', '体育馆', now());
|
||
|
||
-- 10. 学生的体育成绩报告 (教师生成)
|
||
INSERT INTO public.ak_sport_reports (id, user_id, school_id, class_id, semester, report_type, content, status, created_at, updated_at) VALUES
|
||
('a3b4c5d6-e7f8-4a9b-9c8d-7e6f5a4b3c21', '7bf7378e-a027-473e-97ac-3460ed3f170a', '11111111-2222-1111-1111-111111111111', '11111111-4444-1111-1111-111111111111', '2024-2025学年第二学期', 'monthly', '{
|
||
"student_name": "张小明",
|
||
"student_id": "7bf7378e-a027-473e-97ac-3460ed3f170a",
|
||
"class_name": "高一(1)班",
|
||
"teacher_name": "王老师",
|
||
"month": "2025-06",
|
||
"training_summary": {
|
||
"total_training_days": 18,
|
||
"completed_sessions": 16,
|
||
"completion_rate": 89,
|
||
"total_distance": 42.7,
|
||
"avg_heart_rate": 127,
|
||
"calories_burned": 2850
|
||
},
|
||
"performance_metrics": {
|
||
"endurance_score": 88,
|
||
"speed_improvement": "12%",
|
||
"strength_improvement": "8%",
|
||
"technique_score": 87,
|
||
"consistency_rating": "优秀"
|
||
},
|
||
"specific_achievements": [
|
||
"50米冲刺从8.2秒提升到7.6秒",
|
||
"1000米跑从4:45提升到4:28",
|
||
"俯卧撑从25个提升到36个",
|
||
"连续完成训练计划16天"
|
||
],
|
||
"areas_for_improvement": [
|
||
"仰卧起坐动作标准性需提高",
|
||
"长距离跑步耐力还需加强",
|
||
"柔韧性训练需要增加"
|
||
],
|
||
"teacher_comments": "张小明同学本月训练表现优异，各项体能指标都有显著提升。学习态度认真，训练积极主动。建议继续保持当前训练强度，同时加强柔韧性训练。",
|
||
"recommendations": [
|
||
"每周增加2次柔韧性训练",
|
||
"逐步增加长跑距离至5公里",
|
||
"注意运动后的恢复和拉伸"
|
||
],
|
||
"grade": "A",
|
||
"points_earned": 95
|
||
}', 'published', now(), now());
|
||
|
||
-- 11. 师生互动记录
|
||
INSERT INTO public.ak_interactions (id, user_id, target_type, target_id, interaction_type, content, created_at) VALUES
|
||
('b4c5d6e7-f8a9-4b0c-9d8e-7f6a5b4c3d21', 'eed3824b-bba1-4309-8048-19d17367c084', 'training_record', '550e8400-e29b-41d4-a716-446655440003', 'comment', '跑步节奏控制得很好，继续保持！建议下次可以尝试稍微提高配速。', now()),
|
||
('b4c5d6e7-f8a9-4b0c-9d8e-7f6a5b4c3d22', 'eed3824b-bba1-4309-8048-19d17367c084', 'assignment_submission', 'e1f2a3b4-c5d6-4e7f-9a8b-7c6d5e4f3a21', 'comment', '训练记录非常详细，进步幅度令人满意。继续加油！', now()),
|
||
('b4c5d6e7-f8a9-4b0c-9d8e-7f6a5b4c3d23', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'teaching_resource', 'c9d0e1f2-a3b4-4c5d-9e8f-7a6b5c4d3e21', 'like', NULL, now()),
|
||
('b4c5d6e7-f8a9-4b0c-9d8e-7f6a5b4c3d24', '7bf7378e-a027-473e-97ac-3460ed3f170a', 'teaching_resource', 'c9d0e1f2-a3b4-4c5d-9e8f-7a6b5c4d3e24', 'comment', '老师的跑步技术示范视频很有帮助，我按照要求练习后确实有改善！', now());
|
||
|
||
-- ===============================
|
||
-- 数据统计查询
|
||
-- ===============================
|
||
|
||
-- 真实用户数据统计
|
||
SELECT
|
||
'学生用户训练记录' as data_type,
|
||
COUNT(*) as count
|
||
FROM public.ak_training_records
|
||
WHERE user_id = '7bf7378e-a027-473e-97ac-3460ed3f170a'
|
||
UNION ALL
|
||
SELECT
|
||
'学生用户体征数据',
|
||
COUNT(*)
|
||
FROM public.ak_biometric_data
|
||
WHERE user_id = '7bf7378e-a027-473e-97ac-3460ed3f170a'
|
||
UNION ALL
|
||
SELECT
|
||
'学生用户AI报告',
|
||
COUNT(*)
|
||
FROM public.ak_ai_reports
|
||
WHERE user_id = '7bf7378e-a027-473e-97ac-3460ed3f170a'
|
||
UNION ALL
|
||
SELECT
|
||
'学生用户通知',
|
||
COUNT(*)
|
||
FROM public.ak_notifications
|
||
WHERE user_id = '7bf7378e-a027-473e-97ac-3460ed3f170a'
|
||
UNION ALL
|
||
SELECT
|
||
'学生用户积分记录',
|
||
COUNT(*)
|
||
FROM public.ak_point_transactions
|
||
WHERE user_id = '7bf7378e-a027-473e-97ac-3460ed3f170a'
|
||
UNION ALL
|
||
SELECT
|
||
'教师用户教学资源',
|
||
COUNT(*)
|
||
FROM public.ak_teaching_resources
|
||
WHERE teacher_id = 'eed3824b-bba1-4309-8048-19d17367c084'
|
||
UNION ALL
|
||
SELECT
|
||
'教师用户布置作业',
|
||
COUNT(*)
|
||
FROM public.ak_assignments
|
||
WHERE teacher_id = 'eed3824b-bba1-4309-8048-19d17367c084'
|
||
UNION ALL
|
||
SELECT
|
||
'教师用户课程安排',
|
||
COUNT(*)
|
||
FROM public.ak_class_schedules
|
||
WHERE teacher_id = 'eed3824b-bba1-4309-8048-19d17367c084'
|
||
ORDER BY data_type;
|
||
|
||
-- 查询学生用户的积分情况
|
||
SELECT
|
||
total_points,
|
||
available_points,
|
||
(total_points - available_points) as used_points
|
||
FROM public.ak_user_points
|
||
WHERE user_id = '7bf7378e-a027-473e-97ac-3460ed3f170a';
|
||
|
||
-- 查询学生用户最近的训练记录
|
||
SELECT
|
||
activity_type,
|
||
start_time,
|
||
duration_sec,
|
||
distance_km,
|
||
calories,
|
||
avg_heart_rate
|
||
FROM public.ak_training_records
|
||
WHERE user_id = '7bf7378e-a027-473e-97ac-3460ed3f170a'
|
||
ORDER BY start_time DESC
|
||
LIMIT 5;
|
||
|
||
-- ===============================
|
||
-- UUID格式修正说明
|
||
-- ===============================
|
||
-- 本文件已完成所有UUID格式的修正:
|
||
-- 1. 所有原本格式不正确的UUID（如 tpi11111-0000-0000-0000-000000000002）
|
||
-- 已替换为符合PostgreSQL标准的UUID格式
|
||
-- 2. 保持了所有外键引用的一致性
|
||
-- 3. 所有UUID都遵循标准格式：xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
|
||
-- 4. 可以安全导入PostgreSQL数据库，不会出现UUID格式错误
|
||
|
||
-- 修正的UUID映射关系:
|
||
-- 训练计划项目IDs: 47e3bc40-a5d2-4c8e-b9f7-82a1b3c4d5e6/7/8
|
||
-- 通知IDs: c3e8f2a0-9b4c-4d6e-8f7a-1b2c3d4e5f60/61/62/63
|
||
-- 积分相关IDs: a7b8c9d0-e1f2-4a3b-9c8d-7e6f5a4b3c21, b8c9d0e1-f2a3-4b5c-9d8e-7f6a5b4c3d21-26
|
||
-- 教师资源IDs: c9d0e1f2-a3b4-4c5d-9e8f-7a6b5c4d3e21-24
|
||
-- 实际设备ID已统一为: 12345678-1234-5678-9abc-123456789012
|