目次
PARTITION BYとは？
PARTITION BYとは、指定した列の値ごとに計算処理をする機能のことです。
「部門ごとの件数を付けたい」や「商品ごとにランクを付けたい」などの処理を行いたい場合に、PARTITION BYが使用されます。
分析関数や分析関数とあわせて使われるOVER句と一緒に、PARTITION BYも使うことが多いです。
分析関数とは？
「そもそも分析関数って何？」と思う人もいるでしょう。
分析関数とは、グループの中で指定された範囲を集計し、集計結果を各行に戻す関数のことです。
SQLの中でも分析関数は比較的新しく追加された機能であり、ウィンドウ(Window)関数とも言われます。
分析関数では、サブクエリーをいくつか組み合わせる必要があったものが、より簡単なSQL文で実現可能になりました。
分析関数の中で、PARTITION BYは列を指定するために用います。
分析関数でPARTITION BYを使う例
ここでは、分析関数でPARTITION BYを使う例を紹介します。
次のクエリでは、PARTITION BYとORDER BYを用いてNAMEごとにPRICEが大きい順に順番をつけます。
- SELECT id, name, price,
- ROW_NUMBER() OVER (PARTITION BY name ORDER BY price DESC)
- FROM my_table;
- ID NAME PRICE ROW_NUMBER()OVER(PARTITIONBYNAMEORDERBYPRICEDESC)
- ---------- ---------- ---------- -----------------------------------------------
- 1 Satou 570 1
- 3 Satou 440 2
- 2 Satou 200 3
- 5 Satou 80 4
- 6 Abe 350 1
- 4 Abe 130 2
上記のように、NAMEごとにPRICEが大きい順番で表示できます。
指定方法を少し工夫して、各NAMEごとに最大のPRICEをもつ行を取得するクエリも作成できます。
そのやり方は「PARTITION BYとWHEREを使って、最大値をもつ行を抜き出す」で解説しますね。
OVER句の基本的な使い方
先ほどの分析関数の例で「OVER句」を使用しました。
ここでは、OVER句について解説します。
OVER句では次の３つの方法を用いて、集計対象の範囲を指定できます。
・PARTITION BY
・ORDER BY
・WINDOW(Frame)
分析関数でOVER句を使う場合は、次のように指定しましょう。
- OVER (PARTITION BY 集計単位 ORDER BY 表示順)
PARTITION BYが集計したい列を指定するのに対して、ORDER BYは表示順を指定します。
先ほどの例でも使用した「ROW_NUMBER（行番号）」など、集計結果を並び替えたい場合に使うことが多いです。
WINDOWは、ORDER BYのオプションのような扱いです。
行をソートした際に、各行でどこからどこまでを集計の対象にするかを指定できます。
そのため、WINDOWを指定する場合は必ずORDER BYが必要です。
ORDER BYでは先頭行から現在行までが対象範囲になりますが、これはWINDOWを省略した場合のデフォルト動作になっています。
それをWINDOWを使うことで、対象範囲を自由に設定可能です。
ただし、分析関数によってはWINDOWが使えない関数もあるため、マニュアルで確認が必要になります。
PARTITION BYとSUMを使って、累計を求める
ここでは、PARTITION BYとSUMを使ってNAMEごとにPRICEの累計を求めてみましょう。
次のようにクエリを記述します。
- SELECT id, name, price,
- SUM(price) OVER (PARTITION BY name ORDER BY price DESC)
- FROM my_table;
- ID NAME PRICE SUM(PRICE)OVER(PARTITIONBYNAMEORDERBYPRICEDESC)
- ---------- ---------- ---------- -----------------------------------------------
- 1 Satou 570 570
- 3 Satou 440 1010
- 2 Satou 200 1210
- 5 Satou 80 1290
- 6 Abe 350 350
- 4 Abe 130 480
PARTITION BYによってNAMEでグルーピングされ、ORDER BYによって順序が決まって累計されています。
PARTITION BYとWHEREを使って、最大値をもつ行を抜き出す
「分析関数でPARTITION BYを使う例」で少し説明したように、分析関数を使うことでグループ内の最大値や最小値など特定の行を抜き出せます。
ここでは、PARTITION BYとWHEREを使って、最大値をもつ行を抜き出してみましょう。
最大値をもつ行を抜き出すには、次のようにクエリを記述します。
- SELECT * FROM (
- SELECT id, name, price,
- ROW_NUMBER() OVER (PARTITION BY name ORDER BY price DESC) rn
- FROM my_table)
- WHERE rn = 1;
- ID NAME PRICE RN
- ---------- ---------- ---------- ----------
- 1 Satou 570 1
- 6 Abe 350 1
NAMEごとにRN=1（最大値）の行が抜き出せました。
PARTITION BYとRANKを使って、ランク付けする
ここでは、PARTITION BYとRANKを使って、ランク付けするクエリを見ていきましょう。
RANKは、結果セットに順位を付ける関数です。構文はROW_NUMBER と同じになります。
ROW_NUMBERは各行に異なる番号を振るのに対し、RANKは列の値が同じだった場合には同じ番号が振られます。
実際にクエリを書いてみましょう。
- SELECT name, age, gender
- ROW_NUMBER() OVER (PARTITION BY gender ORDER BY age DESC) AS RowNo,
- RANK() OVER (PARTITION BY gender ORDER BY age DESC) AS RankNo,
- FROM my_table;
- NAME AGE GENDER ROWNO RANKNO
- ---------- ---------- ---------- ---------- ----------
- Akio 45 M 1 1
- Tarou 32 M 2 2
- Daichi 32 M 3 2
- Masaki 31 M 4 4
- Hanako 29 F 1 1
- Yuki 24 F 2 2
GENDERごとにわけて年齢が高い順に順位付けされています。
また、RANKNOは同じ年齢（32）の場合は両方とも同じ番号が振られており、3はスキップされています。
PARTITION BYとGROUP BYの違い
SQLの中で「グループ分けの機能」をもっている句が、「GROUP BY」と「PARTITION BY」です。
両者はテーブルを指定されたキーで分割する働きがあります。
そんな2つで異なる点が、GROUP BYの場合は「分割後に集約して、1行にまとめる」操作が入ることです。
それ以外の点は同じですので、覚えておきましょう。
まとめ
SQLのPARTITION BYについて解説しました。
PARTITION BYは、指定した列の値ごとに計算処理ができ、分析関数と併用することで効率よくデータを取得できます。
ぜひこの記事を参考に、PARTITION BYの使い方をマスターしてください！
