カラムの重複削除 DISTINCT ONを使ってみた! PostgreSQLの勉強

重複削除するならDISTINCTと思っていたのだですが、DISTINCTは重複した行のみ間引いてくれます。
カラム(列)に対して重複削除したいときはDISTINCTじゃだめなのか、副問い合わせを使用して絞っていくのかなと悩んでいたらDISTINCT ONを使えば、カラムに対して重複削除ができると知りました。やはりDISTINCTは優秀なのかと早速使ってみました。

動作環境

この記事はSQL FIDDLE のPostgreSQL9.6 で動作確認しました。

DISTINCT ON とは

DISTINCT ON は指定したカラムで値が等しい場合、等しいと見なされた行のうち最初の行を保持します。

例えばユニークなIDなどを含むカラムをセレクトした場合でも簡単に特定のカラムから重複削除を行えます。

構文

SELECT DISTINCT ON ( カラム1 [, カラム2,…] )

カラムは複数指定することも可能です。DISTINCT ON(カラム1, カラム2) と指定した場合、一番左側に指定しているカラム1から順にORDER BYされます。

DISTINCT ON サンプルコード

せっかくなのでサンプルコードでDISTINCT ON の動きを確認してみます。

準備

使用するテーブルです。

-- CREATE文 item テーブル
CREATE TABLE item (
ItemID numeric(8, 0) unique not null,
ItemName character varying (20),
Quantity numeric(8, 0),
AREA character varying (20),
primary key(ItemID)
);
-- itemテーブル用INSERT文
insert into item
values(0001, 'プリン', 25, '大阪'),
(0002, 'ゼリー', 20, '大阪'),
(0003, 'ゼリー', 30, '東京'),
(0004, 'プリン', 15,'大阪'),
(0005, 'プリン', 60, '東京'),
(0006, 'プリン', 25, '大阪'),
(0007, 'ゼリー', 20, '東京');

ItemIDItemNameQuantityAREA
1プリン25大阪
2ゼリー20大阪
3ゼリー30東京
4プリン15大阪
5プリン60東京
6プリン25大阪
7ゼリー20東京
item テーブル

DISTINCT ON で 1つのカラムの重複を削除

SELECT DISTINCT ON(area) itemid, itemname, quantity, area FROM item

結果

itemiditemnamequantityarea
4プリン15大阪
5プリン60東京

大阪、東京の2行になりました。

DISTINCT ON で 複数のカラムから重複を削除

SELECT DISTINCT ON(quantity, itemname, area) itemid, itemname, quantity, area FROM item

結果

itemiditemnamequantityarea
4プリン15大阪
2ゼリー20大阪
7ゼリー20東京
1プリン25大阪
3ゼリー30東京
5プリン60東京

quantity, itemname, area 間の行での重複を削除してくれます。またDISTINCT ONで最初に指定した quantity でORDER BY されていることがわかります。

ORDER BY の癖

DISTINCT ONを 使った場合、基本的にはORDER BYを書く必要がありません。というのもDISTINCT ONで使用されたカラム以外指定することができないからです。

SELECT DISTINCT ON(quantity, itemname, area) itemid, itemname, quantity, area FROM
item ORDER BY itemid

結果

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions Position: 20
(エラー:SELECT DISTINCT ON式は、最初のORDER BY式と一致する必要があります位置:20)

itemidでソートしたい場合は仕方ないですが、副問い合わせするしかなさそうです。

SELECT * from (SELECT DISTINCT ON(quantity, itemname, area) itemid, itemname, quantity, area FROM item)table1 ORDER BY itemid

結果

itemiditemnamequantityarea
1プリン25大阪
2ゼリー20大阪
3ゼリー30東京
4プリン15大阪
5プリン60東京
7ゼリー20東京

おわり

以前、DISTINCT ONの存在を知らず、下記の記事を書きました。かなり苦労した記憶がありますがDISTINCT ONを使えば一瞬で作業を終えることができたんだなと思うと残念です。でもこれからはDISTINCT ONを使ってサクサク効率的に作業できそうです。

参考

https://www.postgresql.org/docs/12/sql-select.html

PostgreSQL, SQL

Posted by Nakamoto