ROWSで範囲指定する

 => SELECT *, MAX(score) OVER (PARTITION BY student_id ORDER BY test_id ROWS 2 PRECEDING) FROM tests;
 
  test_id | student_id | score | max
 ---------+------------+-------+-----
        1 |        100 |     5 |   5
        2 |        101 |     3 |   3
        3 |        100 |     4 |   5
        4 |        100 |     2 |   5
        5 |        100 |     3 |   4
        6 |        101 |     4 |   4
 (6 rows)

• 直近3回でMAXのscoreを出す

ROWS BETWEEN N AND M

 => SELECT *, MAX(score) OVER (PARTITION BY student_id ORDER BY test_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM tests;
 
  test_id | student_id | score | max
 ---------+------------+-------+-----
        1 |        100 |     5 |   5
        2 |        101 |     3 |   3
        3 |        100 |     4 |   5
        4 |        100 |     2 |   5
        5 |        100 |     3 |   4
        6 |        101 |     4 |   4
 (6 rows)

• 過去2行（PRECEDING）と現在行（CURRENT ROW）までで、パーティションは3行

 => SELECT *, MAX(score) OVER (PARTITION BY student_id ORDER BY test_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM tests;
  test_id | student_id | score | max
 ---------+------------+-------+-----
        1 |        100 |     5 |   5
        2 |        101 |     3 |   4
        3 |        100 |     4 |   5
        4 |        100 |     2 |   4
        5 |        100 |     3 |   3
        6 |        101 |     4 |   4
 (6 rows)

• 過去1行（PRECEDING）と次の1行（FOLLOWING）で、パーティションは3行