【SQL】PARTITION BYの基礎から列ごとの計算方法まとめ
  • facebookページ
  • twitterページ
  • 2020.05.22

    【SQL】PARTITION BYの基礎から列ごとの計算方法まとめ

    PARTITION BYとは?

    PARTITION BYとは、指定した列の値ごとに計算処理をする機能のことです。

    「部門ごとの件数を付けたい」や「商品ごとにランクを付けたい」などの処理を行いたい場合に、PARTITION BYが使用されます。

    分析関数や分析関数とあわせて使われるOVER句と一緒に、PARTITION BYも使うことが多いです。

    分析関数とは?

    「そもそも分析関数って何?」と思う人もいるでしょう。

    分析関数とは、グループの中で指定された範囲を集計し、集計結果を各行に戻す関数のことです。

    SQLの中でも分析関数は比較的新しく追加された機能であり、ウィンドウ(Window)関数とも言われます。

    分析関数では、サブクエリーをいくつか組み合わせる必要があったものが、より簡単なSQL文で実現可能になりました。

    分析関数の中で、PARTITION BYは列を指定するために用います。

    分析関数でPARTITION BYを使う例

    ここでは、分析関数でPARTITION BYを使う例を紹介します。

    次のクエリでは、PARTITION BYとORDER BYを用いてNAMEごとにPRICEが大きい順に順番をつけます。

    上記のように、NAMEごとにPRICEが大きい順番で表示できます。

    指定方法を少し工夫して、各NAMEごとに最大のPRICEをもつ行を取得するクエリも作成できます。

    そのやり方は「PARTITION BYとWHEREを使って、最大値をもつ行を抜き出す」で解説しますね。

    OVER句の基本的な使い方

    先ほどの分析関数の例で「OVER句」を使用しました。

    ここでは、OVER句について解説します。

    OVER句では次の3つの方法を用いて、集計対象の範囲を指定できます。

    ・PARTITION BY
    ・ORDER BY
    ・WINDOW(Frame)

    分析関数でOVER句を使う場合は、次のように指定しましょう。

    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の累計を求めてみましょう。

    次のようにクエリを記述します。

    PARTITION BYによってNAMEでグルーピングされ、ORDER BYによって順序が決まって累計されています。

    PARTITION BYとWHEREを使って、最大値をもつ行を抜き出す

    分析関数でPARTITION BYを使う例」で少し説明したように、分析関数を使うことでグループ内の最大値や最小値など特定の行を抜き出せます。

    ここでは、PARTITION BYとWHEREを使って、最大値をもつ行を抜き出してみましょう。

    最大値をもつ行を抜き出すには、次のようにクエリを記述します。

    NAMEごとにRN=1(最大値)の行が抜き出せました。

    PARTITION BYとRANKを使って、ランク付けする

    ここでは、PARTITION BYとRANKを使って、ランク付けするクエリを見ていきましょう。

    RANKは、結果セットに順位を付ける関数です。構文はROW_NUMBER と同じになります。

    ROW_NUMBERは各行に異なる番号を振るのに対し、RANKは列の値が同じだった場合には同じ番号が振られます。

    実際にクエリを書いてみましょう。

    GENDERごとにわけて年齢が高い順に順位付けされています。

    また、RANKNOは同じ年齢(32)の場合は両方とも同じ番号が振られており、3はスキップされています。

    PARTITION BYとGROUP BYの違い

    SQLの中で「グループ分けの機能」をもっている句が、「GROUP BY」と「PARTITION BY」です。

    両者はテーブルを指定されたキーで分割する働きがあります。

    そんな2つで異なる点が、GROUP BYの場合は「分割後に集約して、1行にまとめる」操作が入ることです。

    それ以外の点は同じですので、覚えておきましょう。

    まとめ

    SQLのPARTITION BYについて解説しました。

    PARTITION BYは、指定した列の値ごとに計算処理ができ、分析関数と併用することで効率よくデータを取得できます。

    ぜひこの記事を参考に、PARTITION BYの使い方をマスターしてください!



    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE