SQL Serverでウィンドウ関数を使ってみた。

はじめに

SQL Serverに触れる機会が有ったので調べているとウィンドウ関数なるものと遭遇しました。
MySQLでは使えないですが、PostgreSQLでは使えるそうで便利らしいので試してみました。
PostgreSQLではバージョン8.4から導入されたようです。

用意したテーブル

select * from hanbai_list;
 id |    namae     | shurui | baika | tanka | hanbai_su 
----+--------------+--------+-------+-------+-----------
  1 | えんぴつ     | 文房具 |   100 |    50 |        10
  2 | 消しゴム     | 文房具 |    50 |    25 |         5
  3 | じょうぎ     | 文房具 |    80 |    40 |         4
  4 | スマホケース | 雑貨   |  1000 |   500 |         3
  5 | イヤホン     | 雑貨   |  2500 |  1250 |         2

目的

テーブルのデータを shurui ごとの利益を出し、その順位を付けて表示させる。

ウィンドウ関数のやくそくごと

・SELECT句でしか使えない。
・SUM・AVGなどの集約関数をウィンドウ関数として扱える。
・OVER()句をセットで使用する。

実行と結果

構文

関数 OVER (PARTITION BY カラム名 ORDER BY カラム名) AS 表示するカラム名

実行するSQL文

SELECT id
       ,namae 
       ,shurui
       ,(baika-tanka) * hanbai_su AS rieki
       ,RANK() OVER(PARTITION BY shurui
                    ORDER     BY (baika-tanka)*hanbai_su DESC
                    ) AS ranking
FROM hanbai_list;

解説

５〜７行目。
目的は順位を付ける事なので、関数に RANK を指定。
次に OVER句 の PARTITION BY でグループを shurui に指定、
ORDER BY で計算するカラムを (baika-tanka)*hanbai_su と指定。
DESC を付けて、数値の大きい順にするんだよと指定しています。
最後に表示するカラム名を AS で指定。

実行結果

 id |    namae     | shurui | rieki | ranking 
----+--------------+--------+-------+---------
  5 | イヤホン     | 雑貨   |  2500 |       1
  4 | スマホケース | 雑貨   |  1500 |       2
  1 | えんぴつ     | 文房具 |   500 |       1
  3 | じょうぎ     | 文房具 |   160 |       2
  2 | 消しゴム     | 文房具 |   125 |       3

ちゃんと shurui ごとに並んでいます。
その中で売上の順位（ranking）が付けられています。

PARTITION BYについて

実は使わなくてもエラーは起きません。
試してみます。

実行するSQL文

SELECT id
       ,namae 
       ,shurui
       ,(baika-tanka)*hanbai_su AS rieki
       ,RANK() OVER(ORDER BY (baika-tanka)*hanbai_su DESC) AS ranking
FROM hanbai_list;

実行結果

 id |    namae     | shurui | rieki | ranking 
----+--------------+--------+-------+---------
  5 | イヤホン     | 雑貨   |  2500 |       1
  4 | スマホケース | 雑貨   |  1500 |       2
  1 | えんぴつ     | 文房具 |   500 |       3
  3 | じょうぎ     | 文房具 |   160 |       4
  2 | 消しゴム     | 文房具 |   125 |       5

PATITION BY でグループを設定していないので全体の順位（ranking）が表示されました。

並び順について

OVER句 の中で ORDER BY を使っていますが、これはあくまでも「ランキング付けのための計算」をするカラムの指定です。
もし、ランキングはそのままに id順 で表示をしたければ、下記の様に ORDER BY を外に持って来てください。

SELECT id
       ,namae 
       ,shurui
       ,(baika-tanka)*hanbai_su AS rieki
       ,RANK() OVER(ORDER BY (baika-tanka)*hanbai_su DESC) AS ranking
FROM hanbai_list
ORDER BY id;

実行結果

 id |    namae     | shurui | rieki | ranking 
----+--------------+--------+-------+---------
  1 | えんぴつ     | 文房具 |   500 |       3
  2 | 消しゴム     | 文房具 |   125 |       5
  3 | じょうぎ     | 文房具 |   160 |       4
  4 | スマホケース | 雑貨   |  1500 |       2
  5 | イヤホン     | 雑貨   |  2500 |       1

もしもMySQLだったなら...

MySQLで高速にランキングを求める

さいごに

今回使用したRANKはウィンドウ関数専用なのでRANK()と引数を指定していませんが、SUMなどの関数の場合はSUM(カラム名)の様にして使います。

このウィンドウ関数はOracleとDB2でも使えるようなのですが、SyntaxHighlighterではRANK・OVER・PARTITIONは自動で色分けされない様で、少し見辛くなってしまいました。