PostgreSQLで出力結果に番号を振りたい! SQL超初心者の勉強

出力結果に番号を振りたい。ということで調べてみました。

動作環境

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()を使ってみる

構文だけではよく理解できないので、実際に使って理解を深めていきます。

準備

以下のテーブルを使用します。

itemnamequantityarea
プリン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. ウィンドウ関数

PostgreSQL, SQL

Posted by Nakamoto