SQL:将数据集“向后推”？

Question

我有以下数据集，其中包括参加健康测试的不同学生的信息、他们参加健身测试的日期、他们在健康测试时的体重以及他们是否通过了健身测试：

  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

在这个例子中，学生参加体能测试，然后被告知他们是否通过或失败，然后学生记录他们的体重。学生们不一定每年都参加考试。

我有兴趣建立一个统计/机器学习模型，根据学生目前的体重和上一次健康测试的结果来预测学生是否通过下一次体能测试。

这意味着，如果你在这个数据集的第二行-约翰在第二次测试后体重183磅，但他最后知道的重量实际上是165磅。因此，我感兴趣的是为每个学生“移动”数据集。我有兴趣预测约翰是否会通过他的第二次健康测试，当他最后知道的体重是165磅，而不是183磅。

因此，使用SQL代码，我想“移动”每个学生的数据，以修改数据集。这样，老师就可以根据当前的体能测试结果来预测下一次的体能测试谁会不及格--然后在全年帮助那些学生。

有人能告诉我怎么做吗？

谢谢!

Answer 1

显然，有了提供的信息，我们将无法为您提供预测结果的模型。然而，我们可以帮助您提供您的建模所需的数据。

这里的关键任务是将个人先前的结果与当前的结果放在同一行(以供分析)。

SQL (至少是Server和多个其他产品)具有铅和迟滞函数，这些函数允许您根据给定的排序和分区机制，通过数据集“向前看”(铅)或向后(滞后)，告诉它如何识别上一行/下一行。

在这种情况下，我们希望按照个人(名称)进行分区，接受他们之前的结果(滞后函数，1行返回)，按他们接受测试的日期排序。

下面的SQL获取与当前数据相同行的个人的先前结果(注-我假设数据表名为#FT，第一列称为'Auto_ID')：

SELECT      [name], 
            [date_test_taken],
            LAG([date_test_taken], 1) OVER (PARTITION BY [name] ORDER BY [date_test_taken], [Auto_Id]) AS [date_test_taken_Previous],
            LAG([result_of_test], 1) OVER (PARTITION BY [name] ORDER BY [date_test_taken], [Auto_Id]) AS [result_of_test_Previous],
            LAG([weight_after_time_of_test], 1) OVER (PARTITION BY [name] ORDER BY [date_test_taken], [Auto_Id]) AS [weight_after_time_of_test_Previous],
            [result_of_test],
            [weight_after_time_of_test]
    FROM    #FT

注意，如果它们没有以前的记录，则前面的结果为NULL。

以下是结果

|name|date_test_taken|date_test_taken_Previous|result_of_test_Previous|weight_after_time_of_test_Previous|result_of_test|weight_after_time_of_test|
|----|---------------|------------------------|-----------------------|----------------------------------|----------------------------------------|
|alex|2019-01-01     |NULL                    |NULL                   |NULL                              |fail          |220.00                   |
|alex|2020-01-01     |2019-01-01              |fail                   |220.00                            |fail          |225.00                   |
|john|2013-01-01     |NULL                    |NULL                   |NULL                              |pass          |165.00                   |
|john|2016-01-01     |2013-01-01              |pass                   |165.00                            |fail          |183.00                   |
|john|2017-01-01     |2016-01-01              |fail                   |183.00                            |fail          |175.00                   |
|john|2020-01-01     |2017-01-01              |fail                   |175.00                            |pass          |182.00                   |
|tim |2018-01-01     |NULL                    |NULL                   |NULL                              |pass          |176.00                   |

要查看它的运行情况，这里是一个包含数据、查询和结果的[医]小提琴。