Yanor.net/
Wiki
Blog
GitHub
Sandbox
開始行:
* ROWSで範囲指定する [#r74e8465]
=> SELECT *, MAX(score) OVER (PARTITION BY student_id OR...
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 OR...
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）までで、パー...
=> SELECT *, MAX(score) OVER (PARTITION BY student_id OR...
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）で、パーティ...
終了行:
* ROWSで範囲指定する [#r74e8465]
=> SELECT *, MAX(score) OVER (PARTITION BY student_id OR...
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 OR...
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）までで、パー...
=> SELECT *, MAX(score) OVER (PARTITION BY student_id OR...
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）で、パーティ...
ページ名:
