PostgreSQLのDISTINCT ONによる正しい重複除去方法

DISTINCT ONとは

PostgreSQLのDISTINCT ONは「特定のカラムごとに先頭の1行だけを取得する」ためのPostgreSQL独自の構文です。

例えば以下のようなデータがあったとします。

DROP TABLE IF EXISTS staff;

CREATE TABLE staff (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  job TEXT
);

INSERT INTO staff (name, job) VALUES
('Sato', 'Developer'),
('Tanaka', 'Manager'),
('Suzuki', 'Developer'),
('Yamada', 'Designer'),
('Kobayashi', 'Developer'),
('Fujimoto', 'Manager'),
('Shimizu', 'Designer'),
('Yoshida', 'Analyst');

SELECT * FROM staff;

DROP TABLE IF EXISTS staff;

CREATE TABLE staff (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  job TEXT
);

INSERT INTO staff (name, job) VALUES
('Sato', 'Developer'),
('Tanaka', 'Manager'),
('Suzuki', 'Developer'),
('Yamada', 'Designer'),
('Kobayashi', 'Developer'),
('Fujimoto', 'Manager'),
('Shimizu', 'Designer'),
('Yoshida', 'Analyst');

SELECT * FROM staff;

id	name	job
1	Sato	Developer
2	Tanaka	Manager
3	Suzuki	Developer
4	Yamada	Designer
5	Kobayashi	Developer
6	Fujimoto	Manager
7	Shimizu	Designer
8	Yoshida	Analyst

サンプル1

重複除去は「SELECT DISTINCT ON (job) * FROM staff;」で実行できますが、この書き方だと、重複除去した際にどの行が残るかを制御できません。

SELECT DISTINCT ON (job) * FROM staff;

SELECT DISTINCT ON (job) * FROM staff;

id	name	job
8	Yoshida	Analyst
4	Yamada	Designer
5	Kobayashi	Developer
6	Fujimoto	Manager

上からidの昇順で2つ目以降の重複を除去する場合は、重複除去の部分をサブクエリにして以下のように書きます。

SELECT *
FROM (
  SELECT DISTINCT ON (job) * FROM staff
  ORDER BY job, id
) sub
ORDER BY id;

SELECT *
FROM (
  SELECT DISTINCT ON (job) * FROM staff
  ORDER BY job, id
) sub
ORDER BY id;

id	name	job
1	Sato	Developer
2	Tanaka	Manager
4	Yamada	Designer
8	Yoshida	Analyst

サンプル2

逆に重複しているものの最後以外を除去したい場合は「ORDER BY job, id desc」にします。

SELECT *
FROM (
  SELECT DISTINCT ON (job) * FROM staff
  ORDER BY job, id desc
) sub
ORDER BY id;

SELECT *
FROM (
  SELECT DISTINCT ON (job) * FROM staff
  ORDER BY job, id desc
) sub
ORDER BY id;

id	name	job
5	Kobayashi	Developer
6	Fujimoto	Manager
7	Shimizu	Designer
8	Yoshida	Analyst

サンプル3

PostgreSQLのDISTINCT ONを知っている人は多いですが、重複しているレコードの最初または最後以外を除去して並び替える方法まで理解している人は少ないです。

この重複除去の方法を知っておけば、必ず役立ちます。

DISTINCT ONとは

関連した記事