#author("2023-03-16T01:50:11+09:00","default:ryuichi","ryuichi")
#author("2023-03-16T01:50:53+09:00","default:ryuichi","ryuichi")
* partition byとgroup byの違い [#ob7ba7fc]
** 前提 [#oa1101f9]
=> 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)
- もともとテーブルには6行ある
** GROUP BY [#hef0f06c]
** GROUP BY student_id [#hef0f06c]
=> SELECT student_id, MAX(score) FROM tests GROUP BY student_id;
student_id | max
------------+-----
100 | 5
101 | 1
102 | 5
(3 rows)
- GROUP BYすると6行から3行に減る
** PARTITION BY [#k9fade18]
** PARTITION BY student_id [#k9fade18]
=> 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)
- PARTITION BYすると行を減らさずに済む
** GROUP BYでPARTITION BYと同じことを実現するには [#y32a2f58]
=> 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が複雑になる
