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)
  • もともとテーブルには6行ある

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)
  • GROUP BYすると6行から3行に減る

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)
  • PARTITION BYすると行を減らさずに済む

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が複雑になる

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

Last-modified: 2023-03-16 (木) 01:50:53