出力結果に番号を振りたい。ということで調べてみました。
動作環境
SQL FIDDLEでPostgreSQL9.6で動作確認しています。
row_number() で番号を振る
row_number()というウィンドウ関数を使って番号を振ります。ウィンドウ関数ではover()関数を併用する必要があります。
row_number()を使用すると、SELECT文で抽出したパーティション内での行番号をつけます。(1から数える)
ウィンドウ関数の構文
OVER()の括弧内にはORDER BYかPARTITION BYが入ります。
ORDER BYはORDER byで指定した順に上から番号を振っていきます。
関数(…) OVER (ORDER BY …)
ORDER BYの書き方にはオプションがありますが、ここでは省略します。
PARTITION BYでは区切りごとに番号を振ります。
関数(…) OVER (PARTITION BY …)
row_number()の構文
上記を踏まえてrow_number()の書き方です。例えばORDER BY。
row_number() over( 列名 )
シンプルにSELECT文で書いてみると以下のようになります。
SELECT
row_number() over(order by 列名)
FROM
テーブル名
実際にrow_number()を使ってみる
構文だけではよく理解できないので、実際に使って理解を深めていきます。
準備
以下のテーブルを使用します。
| itemname | quantity | area |
|---|---|---|
| プリン | 25 | 大阪 |
| ゼリー | 20 | 大阪 |
| ゼリー | 30 | 東京 |
| プリン | 15 | 大阪 |
| プリン | 60 | 東京 |
テーブル作成用コードです。
— CREATE文 item テーブル
CREATE TABLE item (
ItemName character varying (20),
Quantity numeric(8, 0),
AREA character varying (20)
);
— itemテーブル用INSERT文
insert into item
values(‘プリン’, 25, ‘大阪’),
( ‘ゼリー’, 20, ‘大阪’),
( ‘ゼリー’, 30, ‘東京’),
( ‘プリン’, 15,’大阪’),
( ‘プリン’, 60, ‘東京’);
row_number() over(order by 列名) サンプルコード
SELECT
row_number() over(order by quantity) as 順番
,itemName as 名前
, quantity as 個数
,area as エリア
FROM
item
WHERE
area = ‘大阪’;
■ 結果
| 順番 | 名前 | 個数 | エリア |
|---|---|---|---|
| 1 | プリン | 15 | 大阪 |
| 2 | ゼリー | 20 | 大阪 |
| 3 | プリン | 25 | 大阪 |
over(order by quantity)で個数順に並べ、番号を振っています。
またWHERE句で大阪のみを対象にしました。
row_number() over( PARTITION BY 列名) サンプルコード
はじめorder byで慣れていたのでPARTITION BYが難しく感じました。理解してしまえば使えそうです。
SELECT
row_number() over(PARTITION by area) as 順番
,itemName as 名前
, quantity as 個数
,area as エリア
FROM
item
■ 結果
| 順番 | 名前 | 個数 | エリア |
|---|---|---|---|
| 1 | プリン | 25 | 大阪 |
| 2 | ゼリー | 20 | 大阪 |
| 3 | プリン | 15 | 大阪 |
| 1 | ゼリー | 30 | 東京 |
| 2 | プリン | 60 | 東京 |
大阪と東京(Area)で区切って番号を振っています。ItemNameを指定すればプリンとゼリー区切りで番号を振ってくれます。
おわり
他のウィンドウ関数も興味が湧いてきました。
Rankは同一順位含めて並び順を振ってくれるので便利そうです。
参考サイト：PostgreSQL 9.6.5文書 9.21. ウィンドウ関数
