受講料が最大70%OFF 受講料が最大70%OFF

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句では次の3つの方法を用いて、集計対象の範囲を指定できます。

・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の使い方をマスターしてください!

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。