OVER句 , PARTITION BY句

注意

本記事は、SELECT文 (SQL) の応用的な内容について記載しています。
そのため、下記を前提とした内容になります。

• SELECT文の基本的な文法を知っている

初めに

今回はOVER句について、(それに関連する)PARTITION BY句も併せて説明します。
OVER句は理解・活用することが比較的難しいかもしれませんが、これを活用できるようになると、対応できる抽出が一段と増えます。

「OVER句」とは

簡単に言うと、「あるグループ・順番で番号付け」ができる句です。
下記コードのような枠組みで書くことができます。
「ROW_NUMBER()」はOVER句とセットで利用できる関数で、名前の通り、入力テーブルのレコード順に1,2,3,…と連番を振る関数です。
「OVER()」の括弧内は順番(+グループ)を指定する形であり、下記SQLの場合は”col_1の昇順で連番(ROW_NUMBER)を振る”形になります。
ちょっとこれだけだと、イメージが湧きづらいかもしれませんので、早速、例を見てみましょう。

select 
    ROW_NUMBER() OVER( ORDER BY col_1 )
from table_1

「OVER句」の例

下記データに対して、”点数でランキング付け”をするように求められたとします。

[データ]

[求める抽出結果]

入力データに対して、ランキング列を追加する形です。
これはOVER句を用いて、下記の問合せで抽出することができます。

select 
    *
    , ROW_NUMBER() OVER (order by 点数 desc) as ランキング
from テスト結果;

SELECT句のランキング列箇所を分解すると、

• OVER (order by 点数 desc)　→　点数の降順で、
• ROW_NUMBER()　→　連番を振る。(1,2,3,…)

という内容になります。
次に説明するPARTITION BY句は、ROW_NUMBERと同じく、OVER句で使用されるものになります。

「PARTITION BY句」とは

簡単に言うと、「OVER句でグループ毎に番号付け」ができる句です。
上記の例だと、全データに対して昇順又は降順で番号付けする形でしたが、PARTITION BY句を用いると、“指定のグループ毎に”昇順又は降順で番号付けができます。
それでは例を見てみましょう。

「PARTITION BY句」の例

下記データに対して、「“男女別に”点数でランキング付け」をするように求められたとします。
※データは、上記 [3. 「OVER句」の例] と同じです。

[データ]

[求める抽出結果]

これはPARTITION BY句を用いて、下記の問合せで抽出することができます。

select 
    *
    , ROW_NUMBER() OVER (PARTITION BY 性別 order by 点数 desc) as ランキング
from テスト結果;

SELECT句のランキング列箇所を分解すると、

• OVER (PARTITION BY 性別　→　性別毎に、
• order by 点数 desc)　→　点数の降順で、
• ROW_NUMBER()　→　連番を振る。(1,2,3,…)

という内容になります。

最後に

OVER句 , PARTITION BY句について説明しました。
業務で使用する機会は比較的あまり無い印象がありますが、抽出できるパターンが一段と増える意味で、これらを身に着ける価値は大きいと思います。
直近で使用する機会が無くても、「こういう抽出方法があったな」程度でも思い出せるようにしておきましょう。