[SQL_Server]ROW_NUMBER() を使ってみる。

SQL Serverでは、'2005'から「ROW_NUMBER()」という機能(関数)を実装したらしい。
私は使ったことないが、Oracleのソレと同じ(？)ようなものみたい。

MSDN⇒ ROW_NUMBER (Transact-SQL)

簡単に言うと、「ROW_NUMBER()」は SELECT した結果(レコードの集合)に対して、
指定された条件で順位付けして番号を振ってくれる便利関数。
ちなみに、「OVER()句」とセットで使用。条件は、この「OVER( 条件 )」に書く。


先月より触り始めた SQL Server の Stored Procude で、これを使う機会がありそうなので、
ちょいと練習で使ってみた。
以下は、操作履歴テーブル【tbOperationLog】を条件なしに(全件)抽出したもの。
※ここで使用するテーブル、カラムはあくまでサンプル。


テーブル：　tbOperationLog -- 操作履歴を格納するテーブル
　カラム：　dtOperation -- 操作日時
　　　　　　cUserID -- 操作ユーザーID
　　　　　　cProgramID -- 操作画面ID
　　　　　　vcContents -- 操作種別コード


サンプル その1：

SELECT ROW_NUMBER() OVER( ORDER BY dtOperation ASC ) AS 'RowNum'
     ,dtOperation AS 'Date'
     ,cUserID AS 'uID'
     ,cProgramID AS 'pID'
     ,vcContents AS 'Code'
  FROM tbOperationLog
-- ORDER BY vcContents
;

結果：
RowNum    Date          uID         pID         Code
--------  ------------  ----------  ----------  ----
1         2008-01-06    BLOSSOMS    A2008085    00
2         2008-02-14    BLOSSOMS    C2008086    01
3         2008-03-04    BLOSSOMS    A2008085    02
4         2008-06-26    SAKURA39    A2008085    02
5         2008-07-19    BLOSSOMS    C2008086    03
6         2008-07-19    SAKURA39    C2008086    03
7         2008-07-27    SAKURA39    C2008086    00
8         2008-08-02    SAKURA39    C2008086    01

解説：
「ROW_NUMBER()」は、直後の「OVER( ORDER BY dtOperation ASC )」を条件に順位付けを行う。
サンプル1 では『操作日時( dtOperation )』の昇順( ASC )で順位付けを行った。

ちなみに、コメントアウトで無効にしている最終行の【ORDER BY vcContents】を有効にすると、
順位は「OVER()句」で指定された条件で行われるが、表示される順序がこっちで行われる。


結果：(外側のORDER BY を有効にした場合)

RowNum    Date          uID         pID         Code
--------  ------------  ----------  ----------  ----
1         2008-01-06    BLOSSOMS    A2008085    00
7         2008-07-27    SAKURA39    C2008086    00
8         2008-08-02    SAKURA39    C2008086    01
2         2008-02-14    BLOSSOMS    C2008086    01
3         2008-03-04    BLOSSOMS    A2008085    02
4         2008-06-26    SAKURA39    A2008085    02
5         2008-07-19    BLOSSOMS    C2008086    03
6         2008-07-19    SAKURA39    C2008086    03

で、条件指定にはもう少し便利なものがある。
「PARTITION BY」というもの。
「ORDER BY 句」の直前に指定する。
私もコレは初めて知ったものなので、とりあえず使ってみる。

サンプル その2：

SELECT ROW_NUMBER() OVER( PARTITION BY cUserID ORDER BY dtOperation ASC ) AS 'RowNum'
     ,dtOperation AS 'Date'
     ,cUserID AS 'uID'
     ,cProgramID AS 'pID'
     ,vcContents AS 'Code'
  FROM tbOperationLog
;

結果：
RowNum    Date          uID         pID         Code
--------  ------------  ----------  ----------  ----
1         2008-01-06    BLOSSOMS    A2008085    00
2         2008-02-14    BLOSSOMS    C2008086    01
3         2008-03-04    BLOSSOMS    A2008085    02
4         2008-07-19    BLOSSOMS    C2008086    03
1         2008-06-26    SAKURA39    A2008085    02
2         2008-07-19    SAKURA39    C2008086    03
3         2008-07-27    SAKURA39    C2008086    00
4         2008-08-02    SAKURA39    C2008086    01

解説：
「PARTITION BY」を指定すると、順位付けを行うグループを設定できる。
サンプル2 は「操作ユーザーID( cUserID )」内で、『操作日時( dtOperation )』の昇順( ASC )に順位付けを行った。


もちろん、「ORDER BY 句」は降順( DESC )の指定もできますよ。