-- 去除無效數據
DELETE FROM study_record WHERE course_id IS NULL OR user_id IS NULL;

-- 填補缺失值
UPDATE study_record SET learn_time = 0 WHERE learn_time IS NULL;

-- 處理異常值
UPDATE study_record SET learn_time = 0 WHERE learn_time < 0;

-- 查詢學生的學習情況
SELECT 
    user_id,
    course_id,
    SUM(learn_time) AS total_learn_time,
    COUNT(CASE WHEN status = 'completed' THEN 1 ELSE NULL END) AS completed_count,
    COUNT(CASE WHEN status = 'in_progress' THEN 1 ELSE NULL END) AS in_progress_count,
    COUNT(CASE WHEN status = 'not_started' THEN 1 ELSE NULL END) AS not_started_count
FROM 
    study_record
GROUP BY 
    user_id,
    course_id;

-- 利用邏輯回歸模型對學生的學習行為進行預測
-- 假設我們已經建立好了一個學習行為預測模型，模型中包含以下特征：學習時長、學習狀態、課程難度等
SELECT 
    user_id,
    course_id,
    CASE WHEN predicted_label = 1 THEN 'recommended' ELSE 'not recommended' END AS recommendation
FROM 
    study_record
JOIN 
    (SELECT 
        user_id,
        course_id,
        predicted_label
     FROM 
        study_behavior_prediction) AS prediction_table
ON 
    study_record.user_id = prediction_table.user_id AND study_record.course_id = prediction_table.course_id;