我有一个 table 命名测试，其结构如下

id mark join_id
1  5    1
2  4    1
3  9    1
4  5    2 
5  7    2
6  12   2

我想写一个查询，可以让我得到从开始记录到这条记录的平均分数，所需的结果如下

id mark join_id   avg_of_previous_marks
1  5    1         5      
2  4    1         4.5
3  9    1         6
4  5    2         5.75
5  7    2         6
6  12   2         7

我写了这个查询，但它似乎不能正常工作

SELECT test.id, test.mark, test.join_id,  test_avg.avg_of_previous_marks FROM test
LEFT JOIN (SELECT id, join_id, AVG(mark) as avg_of_previous_marks FROM test GROUP BY 
join_id) test_avg 
ON test_avg.join_id = test.join_id AND test_avg.id <= test.id

它给出了这个结果

id mark join_id   avg_of_previous_marks
1  5    1         6      
2  4    1         6
3  9    1         6
4  5    2         8
5  7    2         8
6  12   2         8

select id,mark,join_id, avg(mark) over (order by id) avg_of_previous_marks from test_avg ;