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行

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS

Last-modified: 2023-03-16 (木) 02:49:20