よく使われる順位付け関数 3 - NTILE

よく使われる順位付け関数 3 - NTILE

前回は 「 よく使われる順位付け関数 2 - RANK, DENSE_RANK 」 で、 順位付け関数のうち RANK と DENSE_RANK を使って順位を取得してみました。

次は、順位付け関数の NTILE について見ていきましょう。

こちら のスクリプトを実行して、テーブルを再生成しておいてください。 全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。


NTILE ファンクション

NTILE ファンクションは、結果セットを指定した数のグループに分けてくれるファンクションです。

構文は次の通りです。 

NTILE ([分けるグループの数]) OVER ( [ 
                  PARTITION BY [パティションカラム 1 ], [パティションカラム 2], ... ] 
                  ORDER BY [ソートカラム 1], [ソートカラム 2], ...  )

[分けるグループの数] は正の整数で指定され、グループ番号は 1, 2, 3, .., N のように　1 から順番に割り振られます。

PPARTITION BY が指定されていない場合は全体が指定した数の、PARTITION BY が指定されている場合はパティション内で指定した数のグループに分割されます。

結果セットのレコード数が指定した数で割り切れる時は、各グループに含まれるレコード数は [結果セットのレコード数] / [分けるグループの数] で均等になります。

結果セットのレコード数が指定した数で割り切れない時は、グループに含まれるレコード数が均等ではなくなります。

[結果セットのレコード数] / [分けるグループの数] で小数点以下を切り捨てしたレコード数が各グループに割り当てられ、その後グループ番号 1 から順番に、合計が [結果セットのレコード数] になるまで、グループに 1 レコードずつレコード数が追加される感じです。

例: パティションなしで [分けるグループの数]　= 3 のケース

ROW_NUMBERNTILE
11
21
31
42
52
62
73
83

それでは、NTILE ファンクションを使ってみましょう。 例えば、次のような Student テーブルがあります。

NTILE ファンクション 1


前回 ROW_NUMBER 関数の時と同様に、誕生日の年が古い -> 新しい、男性　-> 女性という順で、NTILE 関数を使ってグループ分けしてみましょう。 

SELECT  ROW_NUMBER() OVER (ORDER BY YEAR(Birthday), Gender DESC) AS RowNo,
        NTILE(2) OVER (ORDER BY YEAR(Birthday), Gender DESC) AS NtileNo2,
        NTILE(3) OVER (ORDER BY YEAR(Birthday), Gender DESC) AS NtileNo3,
        NTILE(4) OVER (ORDER BY YEAR(Birthday), Gender DESC) AS NtileNo4,
        *
FROM    Student;

[ 実行結果 ]
NTILE ファンクション 2

誕生日の年が古い -> 新しい、男性　-> 女性という順で、2 個、3 個、4 個と指定したグループ数に分かれていますね。

指定したグループの数が 4 の時は、結果セットのレコード数が割り切れないので、グループ 1, 2 のレコード数が 1 レコードずつ多くなっています。

© 2010-2024 SQL Server 入門