350 lines
11 KiB
SQL
350 lines
11 KiB
SQL
-- API使用示例：学校运动训练系统增强功能
|
||
|
||
-- ==================== 1. 教师创建运动作业 ====================
|
||
|
||
-- 1.1 查询可用的作业类型
|
||
SELECT id, name, description, requires_device, requires_video, ai_evaluation_enabled, scoring_criteria
|
||
FROM public.ak_assignment_types
|
||
WHERE is_active = true
|
||
ORDER BY name;
|
||
|
||
-- 1.2 创建新作业（教师）
|
||
INSERT INTO public.ak_assignments (
|
||
teacher_id,
|
||
title,
|
||
description,
|
||
class_id,
|
||
assignment_type_id,
|
||
target_metrics,
|
||
ai_grading_enabled,
|
||
due_date,
|
||
workflow_status
|
||
) VALUES (
|
||
'teacher_user_id_here',
|
||
'周末跑步训练',
|
||
'完成3公里慢跑，注意心率控制在140-160之间',
|
||
'class_id_here',
|
||
(SELECT id FROM public.ak_assignment_types WHERE name = '跑步训练'),
|
||
'{"distance": {"target": 3000, "unit": "meters"}, "duration": {"max": 30, "unit": "minutes"}, "heart_rate": {"min": 140, "max": 160}}',
|
||
true,
|
||
'2025-06-07 23:59:59',
|
||
'draft'
|
||
);
|
||
|
||
-- 1.3 提交作业审批
|
||
SELECT public.update_assignment_workflow_status(
|
||
'assignment_id_here',
|
||
'submitted',
|
||
'teacher_user_id_here',
|
||
'请审批周末跑步训练作业'
|
||
);
|
||
|
||
-- ==================== 2. 管理员审批作业 ====================
|
||
|
||
-- 2.1 查询待审批的作业
|
||
SELECT a.id, a.title, a.description, u.username as teacher_name, c.name as class_name,
|
||
a.created_at, a.due_date
|
||
FROM public.ak_assignments a
|
||
JOIN public.ak_users u ON a.teacher_id = u.id
|
||
JOIN public.ak_classes c ON a.class_id = c.id
|
||
WHERE a.workflow_status = 'submitted'
|
||
ORDER BY a.created_at;
|
||
|
||
-- 2.2 审批通过作业
|
||
SELECT public.update_assignment_workflow_status(
|
||
'assignment_id_here',
|
||
'approved',
|
||
'admin_user_id_here',
|
||
'作业目标合理，批准发布'
|
||
);
|
||
|
||
-- 2.3 发布作业给学生
|
||
SELECT public.update_assignment_workflow_status(
|
||
'assignment_id_here',
|
||
'published',
|
||
'admin_user_id_here',
|
||
'作业已发布给学生'
|
||
);
|
||
|
||
-- ==================== 3. 学生查看和提交作业 ====================
|
||
|
||
-- 3.1 学生查看自己的作业列表
|
||
SELECT a.id, a.title, a.description, a.due_date, a.target_metrics,
|
||
at.name as assignment_type, at.requires_device, at.requires_video,
|
||
s.id as submission_id, s.status as submission_status
|
||
FROM public.ak_assignments a
|
||
JOIN public.ak_assignment_types at ON a.assignment_type_id = at.id
|
||
LEFT JOIN public.ak_assignment_submissions s ON a.id = s.assignment_id AND s.student_id = 'student_user_id_here'
|
||
WHERE a.class_id = (SELECT class_id FROM public.ak_users WHERE id = 'student_user_id_here')
|
||
AND a.workflow_status = 'published'
|
||
ORDER BY a.due_date;
|
||
|
||
-- 3.2 学生提交作业（关联训练记录）
|
||
INSERT INTO public.ak_assignment_submissions (
|
||
assignment_id,
|
||
student_id,
|
||
content_md,
|
||
device_data_id,
|
||
attachment_url,
|
||
status
|
||
) VALUES (
|
||
'assignment_id_here',
|
||
'student_user_id_here',
|
||
'## 跑步训练完成报告\n\n今天完成了3公里跑步训练，感觉良好。心率控制在目标范围内。',
|
||
'training_record_id_here', -- 关联的训练记录
|
||
'https://storage.url/running_video.mp4', -- 视频文件（如果需要）
|
||
'submitted'
|
||
);
|
||
|
||
-- ==================== 4. AI自动评估 ====================
|
||
|
||
-- 4.1 查看AI评估结果（自动触发）
|
||
SELECT ae.evaluation_type, ae.confidence_score, ae.suggested_score,
|
||
ae.feedback_text, ae.improvement_suggestions, ae.risk_indicators
|
||
FROM public.ak_ai_evaluations ae
|
||
JOIN public.ak_assignment_submissions s ON ae.submission_id = s.id
|
||
WHERE s.assignment_id = 'assignment_id_here'
|
||
AND s.student_id = 'student_user_id_here';
|
||
|
||
-- 4.2 教师审核AI评估结果
|
||
UPDATE public.ak_assignment_submissions
|
||
SET ai_score = 85.5,
|
||
manual_score = 88.0,
|
||
final_score = 87.0,
|
||
feedback = 'AI评估：动作标准，心率控制良好。教师评价：坚持很好，建议下次增加配速变化。',
|
||
review_status = 'reviewed',
|
||
reviewed_by = 'teacher_user_id_here',
|
||
reviewed_at = now(),
|
||
ai_feedback_approved = true
|
||
WHERE id = 'submission_id_here';
|
||
|
||
-- ==================== 5. AI训练建议生成 ====================
|
||
|
||
-- 5.1 为学生生成个性化训练建议
|
||
INSERT INTO public.ak_ai_training_suggestions (
|
||
user_id,
|
||
suggestion_type,
|
||
priority,
|
||
title,
|
||
content,
|
||
data_source,
|
||
confidence_score,
|
||
action_required
|
||
) VALUES (
|
||
'student_user_id_here',
|
||
'improvement',
|
||
'medium',
|
||
'建议增加间歇训练',
|
||
'根据您最近的跑步数据分析，建议每周增加1-2次间歇训练，可以提高最大摄氧量和跑步效率。建议间歇方案：400米快跑 + 200米慢跑，重复6-8次。',
|
||
'{"source": "training_records", "period": "last_30_days", "records_analyzed": 8}',
|
||
0.82,
|
||
false
|
||
);
|
||
|
||
-- 5.2 查询学生的AI建议
|
||
SELECT title, content, priority, confidence_score, created_at,
|
||
action_required, acknowledged
|
||
FROM public.ak_ai_training_suggestions
|
||
WHERE user_id = 'student_user_id_here'
|
||
AND acknowledged = false
|
||
ORDER BY priority DESC, created_at DESC;
|
||
|
||
-- ==================== 6. 学习分析和统计 ====================
|
||
|
||
-- 6.1 生成学生学习分析报告
|
||
SELECT public.calculate_learning_analytics(
|
||
'student_user_id_here',
|
||
'2025-05-01',
|
||
'2025-05-31'
|
||
);
|
||
|
||
-- 6.2 查看班级作业完成统计
|
||
SELECT * FROM public.vw_assignment_analytics
|
||
WHERE class_id = 'class_id_here'
|
||
ORDER BY assignment_created DESC;
|
||
|
||
-- 6.3 查看学生综合表现
|
||
SELECT * FROM public.vw_student_performance
|
||
WHERE class_id = 'class_id_here'
|
||
ORDER BY avg_score DESC;
|
||
|
||
-- ==================== 7. 数据质量监控 ====================
|
||
|
||
-- 7.1 执行数据质量检查
|
||
SELECT public.check_data_quality();
|
||
|
||
-- 7.2 查看数据质量问题
|
||
SELECT table_name, issue_type, severity, description, created_at,
|
||
resolved, resolved_by
|
||
FROM public.ak_data_quality_logs
|
||
WHERE resolved = false
|
||
ORDER BY severity DESC, created_at DESC;
|
||
|
||
-- 7.3 标记质量问题为已解决
|
||
UPDATE public.ak_data_quality_logs
|
||
SET resolved = true,
|
||
resolved_by = 'admin_user_id_here',
|
||
resolved_at = now()
|
||
WHERE id = 'quality_log_id_here';
|
||
|
||
-- ==================== 8. 权限检查示例 ====================
|
||
|
||
-- 8.1 检查用户是否有权限
|
||
SELECT public.has_permission('user_id_here', 'teacher.assignment.manage');
|
||
|
||
-- 8.2 检查作用域权限
|
||
SELECT public.has_scoped_permission(
|
||
'teacher_user_id_here',
|
||
'teacher.class.read',
|
||
'class',
|
||
'class_id_here'
|
||
);
|
||
|
||
-- 8.3 获取用户的所有权限
|
||
SELECT p.code, p.name, p.description
|
||
FROM public.ak_user_roles ur
|
||
JOIN public.ak_role_permissions rp ON ur.role_id = rp.role_id
|
||
JOIN public.ak_permissions p ON rp.permission_id = p.id
|
||
WHERE ur.user_id = 'user_id_here';
|
||
|
||
-- ==================== 9. 训练计划模板使用 ====================
|
||
|
||
-- 9.1 查询适合的训练模板
|
||
SELECT id, name, description, difficulty_level, duration_weeks,
|
||
target_age_min, target_age_max, ai_adaptive
|
||
FROM public.ak_training_templates
|
||
WHERE target_age_min <= 16 AND target_age_max >= 16 -- 16岁学生
|
||
AND difficulty_level <= 3 -- 中等难度以下
|
||
AND is_active = true;
|
||
|
||
-- 9.2 基于模板创建训练计划
|
||
INSERT INTO public.ak_training_plans (
|
||
user_id,
|
||
plan_name,
|
||
description,
|
||
start_date,
|
||
end_date,
|
||
status
|
||
)
|
||
SELECT
|
||
'student_user_id_here',
|
||
'基于模板：' || name,
|
||
description,
|
||
CURRENT_DATE,
|
||
CURRENT_DATE + INTERVAL '1 week' * duration_weeks,
|
||
'active'
|
||
FROM public.ak_training_templates
|
||
WHERE id = 'template_id_here';
|
||
|
||
-- ==================== 10. 国际化支持示例 ====================
|
||
|
||
-- 10.1 获取用户语言下的UI文本
|
||
SELECT public.get_ui_text('assignment.create.title');
|
||
SELECT public.get_ui_text('assignment.submit.success');
|
||
|
||
-- 10.2 获取多语言的学校名称
|
||
SELECT public.get_school_name('school_id_here');
|
||
|
||
-- 10.3 设置用户偏好语言
|
||
UPDATE public.ak_users
|
||
SET preferred_language = (SELECT id FROM public.ak_languages WHERE code = 'en-US')
|
||
WHERE id = 'user_id_here';
|
||
|
||
-- ==================== 11. 实时通知示例 ====================
|
||
|
||
-- 11.1 创建作业截止提醒通知
|
||
INSERT INTO public.ak_notifications (
|
||
user_id,
|
||
title,
|
||
content,
|
||
status
|
||
)
|
||
SELECT
|
||
s.student_id,
|
||
'作业截止提醒',
|
||
'您的作业"' || a.title || '"将在24小时内截止，请及时完成。',
|
||
'unread'
|
||
FROM public.ak_assignments a
|
||
JOIN public.ak_assignment_submissions s ON a.id = s.assignment_id
|
||
WHERE a.due_date BETWEEN now() AND now() + INTERVAL '24 hours'
|
||
AND s.status != 'submitted';
|
||
|
||
-- 11.2 查询用户未读通知
|
||
SELECT title, content, created_at
|
||
FROM public.ak_notifications
|
||
WHERE user_id = 'user_id_here'
|
||
AND status = 'unread'
|
||
ORDER BY created_at DESC;
|
||
|
||
-- ==================== 12. 综合报表查询 ====================
|
||
|
||
-- 12.1 学校运动训练概览报表
|
||
WITH school_stats AS (
|
||
SELECT
|
||
s.id as school_id,
|
||
s.name as school_name,
|
||
COUNT(DISTINCT c.id) as total_classes,
|
||
COUNT(DISTINCT u.id) as total_students,
|
||
COUNT(DISTINCT ut.id) as total_teachers,
|
||
COUNT(DISTINCT a.id) as total_assignments,
|
||
COUNT(DISTINCT sub.id) as total_submissions
|
||
FROM public.ak_schools s
|
||
LEFT JOIN public.ak_grades g ON s.id = g.school_id
|
||
LEFT JOIN public.ak_classes c ON g.id = c.grade_id
|
||
LEFT JOIN public.ak_users u ON c.id = u.class_id AND u.role = 'student'
|
||
LEFT JOIN public.ak_users ut ON s.id = ut.school_id AND ut.role = 'teacher'
|
||
LEFT JOIN public.ak_assignments a ON c.id = a.class_id
|
||
LEFT JOIN public.ak_assignment_submissions sub ON a.id = sub.assignment_id
|
||
GROUP BY s.id, s.name
|
||
)
|
||
SELECT *,
|
||
CASE WHEN total_assignments > 0
|
||
THEN ROUND(total_submissions::numeric / total_assignments * 100, 2)
|
||
ELSE 0
|
||
END as submission_rate_percent
|
||
FROM school_stats
|
||
ORDER BY total_students DESC;
|
||
|
||
-- 12.2 教师工作量统计
|
||
SELECT
|
||
u.username as teacher_name,
|
||
c.name as class_name,
|
||
COUNT(a.id) as assignments_created,
|
||
COUNT(sub.id) as submissions_received,
|
||
COUNT(CASE WHEN sub.review_status = 'reviewed' THEN 1 END) as submissions_reviewed,
|
||
AVG(sub.final_score) as avg_score_given,
|
||
COUNT(CASE WHEN sub.ai_feedback_approved = true THEN 1 END) as ai_feedbacks_approved
|
||
FROM public.ak_users u
|
||
JOIN public.ak_classes c ON u.class_id = c.id
|
||
LEFT JOIN public.ak_assignments a ON u.id = a.teacher_id
|
||
LEFT JOIN public.ak_assignment_submissions sub ON a.id = sub.assignment_id
|
||
WHERE u.role = 'teacher'
|
||
GROUP BY u.id, u.username, c.name
|
||
ORDER BY assignments_created DESC;
|
||
|
||
-- ==================== 13. 性能优化查询示例 ====================
|
||
|
||
-- 13.1 使用索引优化的学生作业查询
|
||
EXPLAIN (ANALYZE, BUFFERS)
|
||
SELECT a.title, s.submit_time, s.final_score
|
||
FROM public.ak_assignment_submissions s
|
||
JOIN public.ak_assignments a ON s.assignment_id = a.id
|
||
WHERE s.student_id = 'student_user_id_here'
|
||
AND s.submit_time >= '2025-05-01'
|
||
ORDER BY s.submit_time DESC;
|
||
|
||
-- 13.2 批量更新AI评估状态
|
||
UPDATE public.ak_assignment_submissions
|
||
SET ai_feedback_approved = true
|
||
WHERE id IN (
|
||
SELECT s.id
|
||
FROM public.ak_assignment_submissions s
|
||
JOIN public.ak_ai_evaluations ae ON s.id = ae.submission_id
|
||
WHERE ae.confidence_score > 0.9
|
||
AND s.ai_feedback_approved = false
|
||
LIMIT 100
|
||
);
|
||
|
||
-- 这些示例展示了如何在实际应用中使用增强后的运动训练系统
|
||
-- 涵盖了完整的业务流程：从作业创建到AI评估，再到数据分析和质量监控
|