#author("2023-03-16T02:47:14+09:00","default:ryuichi","ryuichi")
#author("2023-03-16T02:49:20+09:00","default:ryuichi","ryuichi")
* ROWSで範囲指定する [#r74e8465]
=> 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 [#g1822c57]
=> 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行
- 過去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行
