重複削除するなら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, ‘東京’);
| ItemID | ItemName | Quantity | AREA |
|---|---|---|---|
| 1 | プリン | 25 | 大阪 |
| 2 | ゼリー | 20 | 大阪 |
| 3 | ゼリー | 30 | 東京 |
| 4 | プリン | 15 | 大阪 |
| 5 | プリン | 60 | 東京 |
| 6 | プリン | 25 | 大阪 |
| 7 | ゼリー | 20 | 東京 |
DISTINCT ON で 1つのカラムの重複を削除
SELECT DISTINCT ON(area) itemid, itemname, quantity, area FROM item
結果
| itemid | itemname | quantity | area |
|---|---|---|---|
| 4 | プリン | 15 | 大阪 |
| 5 | プリン | 60 | 東京 |
大阪、東京の2行になりました。
DISTINCT ON で 複数のカラムから重複を削除
SELECT DISTINCT ON(quantity, itemname, area) itemid, itemname, quantity, area FROM item
結果
| itemid | itemname | quantity | area |
|---|---|---|---|
| 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
結果
| itemid | itemname | quantity | area |
|---|---|---|---|
| 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
