如何用SQL按学生向后偏移数据集，用于体能测试结果预测？

原始数据集 #

我拥有以下包含不同学生体能测试信息的数据集：测试日期、测试后记录的体重、测试是否通过：

name date_test_taken result_of_test weight_after_time_of_test
1 john      2013-01-01           pass                    165
2 john      2016-01-01           fail                    183
3 john      2017-01-01           fail                    175
4 john      2020-01-01           pass                    182
5 alex      2019-01-01           fail                    220
6 alex      2020-01-01           fail                    225
7  tim      2018-01-01           pass                    176

需求说明 #

学生参加体能测试后得知结果，随后记录体重，且并非每年都参加测试。我希望构建统计/机器学习模型，基于学生当前体重和上一次测试结果，预测其下一次体能测试是否通过。

例如：John第二次测试后体重为183磅，但当时已知的最新体重是165磅，需按学生将数据集向后偏移，用165磅预测第二次测试结果。请求提供SQL代码实现该数据集偏移操作，以便教师预测学生下一次测试是否失败并给予帮助。

SQL实现代码 #

以下SQL代码使用窗口函数LAG()按学生分组并按测试日期排序，获取上一次测试的体重和结果，从而构建用于预测的特征：

SELECT
    name,
    date_test_taken AS current_test_date,
    result_of_test AS current_test_result,
    weight_after_time_of_test AS current_weight,
    -- 获取上一次测试的体重（用于预测当前测试结果）
    LAG(weight_after_time_of_test) OVER (PARTITION BY name ORDER BY date_test_taken) AS previous_weight,
    -- 获取上一次测试的结果（用于预测当前测试结果）
    LAG(result_of_test) OVER (PARTITION BY name ORDER BY date_test_taken) AS previous_test_result
FROM
    student_fitness_tests
ORDER BY
    name, date_test_taken;

代码说明 #

• PARTITION BY name：按学生分组，确保只在同一学生的测试记录间偏移
• ORDER BY date_test_taken：按测试日期排序，保证偏移的是时间上的前一次测试记录
• LAG()函数：返回当前行之前的指定行的值，这里默认取前一行（即上一次测试的数据）

执行后得到的结果中，previous_weight和previous_test_result就是用于预测当前测试结果的特征，完全符合"用上一次的体重和结果预测下一次测试"的逻辑。

内容的提问来源于stack exchange，提问作者stats_noob