partition byとgroup byの違い
前提
=> SELECT * FROM tests ORDER;
test_id | student_id | score
---------+------------+-------
1 | 100 | 3
2 | 101 | 1
3 | 102 | 4
4 | 100 | 2
5 | 102 | 5
6 | 100 | 5
(6 rows)
GROUP BY student_id
=> SELECT student_id, MAX(score) FROM tests GROUP BY student_id;
student_id | max
------------+-----
100 | 5
101 | 1
102 | 5
(3 rows)
PARTITION BY student_id
=> SELECT *, MAX(score) OVER (PARTITION BY student_id) FROM tests;
test_id | student_id | score | max
---------+------------+-------+-----
1 | 100 | 3 | 5
2 | 101 | 1 | 1
3 | 102 | 4 | 5
4 | 100 | 2 | 5
5 | 102 | 5 | 5
6 | 100 | 5 | 5
(6 rows)
GROUP BYでPARTITION BYと同じことを実現するには
=> SELECT t1.*, t2.max_score FROM tests AS t1
JOIN (
SELECT student_id, Max(score) AS max_score FROM tests GROUP BY student_id
) AS t2
ON t1.student_id = t2.student_id;
test_id | student_id | score | max_score
---------+------------+-------+-----------
1 | 100 | 3 | 5
2 | 101 | 1 | 1
3 | 102 | 4 | 5
4 | 100 | 2 | 5
5 | 102 | 5 | 5
6 | 100 | 5 | 5
(6 rows)
- GROUP BYでできたインラインテーブルをJOINさせる必要があるため、SQLが複雑になる