Yanor.net/
Wiki
Blog
GitHub
Sandbox
開始行:
* 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 student_id [#hef0f06c]
=> SELECT student_id, MAX(score) FROM tests GROUP BY stu...
student_id | max
------------+-----
100 | 5
101 | 1
102 | 5
(3 rows)
- GROUP BYすると6行から3行に減る
** PARTITION BY student_id [#k9fade18]
=> SELECT *, MAX(score) OVER (PARTITION BY student_id) F...
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と同じことを実現するには [#y32a2...
=> SELECT t1.*, t2.max_score FROM tests AS t1
JOIN (
SELECT student_id, Max(score) AS max_score FROM t...
) 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させる必要があ...
終了行:
* 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 student_id [#hef0f06c]
=> SELECT student_id, MAX(score) FROM tests GROUP BY stu...
student_id | max
------------+-----
100 | 5
101 | 1
102 | 5
(3 rows)
- GROUP BYすると6行から3行に減る
** PARTITION BY student_id [#k9fade18]
=> SELECT *, MAX(score) OVER (PARTITION BY student_id) F...
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と同じことを実現するには [#y32a2...
=> SELECT t1.*, t2.max_score FROM tests AS t1
JOIN (
SELECT student_id, Max(score) AS max_score FROM t...
) 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させる必要があ...
ページ名: