SQL Academy · レッスン

PostgreSQLのDISTINCT ON

Q: 「PostgreSQLのDISTINCT ON」で何を学びますか？

グループごとに1行を選びます。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

グループごとに1行を選びます。

「PostgreSQLのDISTINCT ON」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

DISTINCT ON とは

PostgreSQL には、標準の DISTINCT キーワードを拡張した強力な機能として、DISTINCT ON があります。通常の DISTINCT が完全に重複した行を削除するのに対し、DISTINCT ON では、指定した1つ以上の列を基準に、グループごとに1行だけを選択できます。

「この列の一意な値ごとに、1行を返してください」と考えると分かりやすいでしょう。顧客ごとの最新の注文、学生ごとの最高得点、カテゴリごとの最初のイベントなどを取得する場合に非常に便利です。

DISTINCT ON の基本構文

構文では、DISTINCT ON (column) を SELECT の直後に記述します。括弧内の列がグループ化の基準となり、PostgreSQL はその列の一意な値ごとに1行を返します。

次の例では、orders テーブルから customer_id ごとに1行を返します。どの行を返すかは、その後に続く ORDER BY 句に基づいて PostgreSQL が決定します。

SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total_amount
FROM orders
ORDER BY customer_id, order_date DESC;

例のテーブルを準備する

シンプルな orders テーブルを作成し、サンプル行をいくつか挿入して、DISTINCT ON を実際に試してみましょう。3人の顧客がおり、それぞれが異なる日に複数の注文を行ったデータを用意します。

CREATE TABLE orders (
  order_id      SERIAL PRIMARY KEY,
  customer_id   INT,
  order_date    DATE,
  total_amount  NUMERIC(10, 2)
);

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
  (1, '2024-01-05', 120.00),
  (1, '2024-03-12', 85.50),
  (1, '2024-06-20', 200.00),
  (2, '2024-02-14', 45.00),
  (2, '2024-05-30', 310.00),
  (3, '2024-04-01', 75.00);

顧客ごとの最新の注文

非常によくある使用例として、顧客ごとに最も新しい注文を見つける方法があります。各 customer_id グループ内で order_date DESC の順に並べると、DISTINCT ON は日付が最も新しい行を選択します。

ORDER BY 句は、DISTINCT ON に記述した列と同じ列から始める必要があることに注意してください。これは PostgreSQL の要件です。

SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total_amount
FROM orders
ORDER BY customer_id, order_date DESC;

顧客ごとの最初の注文

顧客ごとに最初（最も古い）の注文を取得するには、並べ替え方向を ASC に変更するだけです。違いは各グループ内の行の順序だけであり、DISTINCT ON は並べ替え後の最初の行を常に選択します。

SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total_amount
FROM orders
ORDER BY customer_id, order_date ASC;

ORDER BY のルール

重要なルール： DISTINCT ON (col) を使用する場合、ORDER BY 句は DISTINCT ON 内に記述した同じ列から必ず始める必要があります。そうでない場合、PostgreSQL はエラーを発生させます。

グループ化の列の後には、各グループ内でどの行を選択するかを制御するため、任意の追加の並べ替え条件を指定できます。

-- Correct: ORDER BY starts with the DISTINCT ON column
SELECT DISTINCT ON (customer_id)
  customer_id, order_date, total_amount
FROM orders
ORDER BY customer_id, total_amount DESC;

-- This would cause an error:
-- ORDER BY order_date DESC  (missing customer_id at the start)

生徒ごとの最高得点

ここでは、test_scores テーブルを使った別の実践例を見てみましょう。各生徒がこれまでに獲得した最高得点を取得します。各生徒のグループ内で score DESC の順に並べると、DISTINCT ON は生徒ごとに最高得点の行だけを返します。

CREATE TABLE test_scores (
  id         SERIAL PRIMARY KEY,
  student_id INT,
  subject    VARCHAR(50),
  score      INT,
  taken_on   DATE
);

INSERT INTO test_scores (student_id, subject, score, taken_on) VALUES
  (101, 'Math',    92, '2024-02-10'),
  (101, 'Math',    78, '2024-04-15'),
  (102, 'Math',    85, '2024-02-10'),
  (102, 'Math',    91, '2024-04-15'),
  (103, 'Math',    67, '2024-02-10');

SELECT DISTINCT ON (student_id)
  student_id, subject, score, taken_on
FROM test_scores
ORDER BY student_id, score DESC;

複数列での DISTINCT ON

DISTINCT ON 内に複数の列を記述すると、2列以上でグループ化できます。これにより、それらの列の一意な組み合わせごとに1行が返されます。

次の例では、科目ごとの生徒の最高得点を取得し、各 (student, subject) の組をそれぞれ独自のグループとして扱います。

INSERT INTO test_scores (student_id, subject, score, taken_on) VALUES
  (101, 'Science', 88, '2024-03-01'),
  (101, 'Science', 95, '2024-05-20'),
  (102, 'Science', 72, '2024-03-01');

SELECT DISTINCT ON (student_id, subject)
  student_id, subject, score, taken_on
FROM test_scores
ORDER BY student_id, subject, score DESC;

WHERE による絞り込み

DISTINCT ON は WHERE 句と自然に組み合わせて使用できます。まずフィルターが適用され、その後、DISTINCT ON が絞り込み後の結果からグループごとに1行を選択します。

ここでは、100を超える注文だけを対象に、顧客ごとの最新の注文を取得します。

SELECT DISTINCT ON (customer_id)
  customer_id,
  order_id,
  order_date,
  total_amount
FROM orders
WHERE total_amount > 100
ORDER BY customer_id, order_date DESC;

DISTINCT ON と GROUP BY の違い

DISTINCT ON と GROUP BY はどちらもグループごとに1行を返せますが、目的が異なります。

GROUP BY は行をまとめ、グループ化されていない列には集約関数（SUM、MAX など）が必要です。
DISTINCT ON は実際に存在する行を保持するため、集約せずにその行のすべての列を利用できます。

集約した値が必要な場合は GROUP BY を使用します。各グループ内の特定の行から完全なデータが必要な場合は DISTINCT ON を使用します。

-- GROUP BY: only aggregated columns allowed
SELECT customer_id, MAX(order_date) AS latest_date
FROM orders
GROUP BY customer_id;

-- DISTINCT ON: returns the whole row for that latest date
SELECT DISTINCT ON (customer_id)
  customer_id, order_id, order_date, total_amount
FROM orders
ORDER BY customer_id, order_date DESC;

サブクエリで DISTINCT ON を使う

DISTINCT ON の結果に対して、さらに絞り込みや並べ替えが必要になる場合があります。外側の ORDER BY はグループ化の列に関連付けられているため、クエリをサブクエリ（または CTE）で囲むと、最終出力に別の並べ替えを適用できます。

この例では、まず顧客ごとの最新の注文を選択し、その後、最終結果を total_amount の降順で並べ替えています。

SELECT *
FROM (
  SELECT DISTINCT ON (customer_id)
    customer_id,
    order_id,
    order_date,
    total_amount
  FROM orders
  ORDER BY customer_id, order_date DESC
) AS latest_orders
ORDER BY total_amount DESC;

理解度チェック

DISTINCT ON の理解度を確認しましょう。次のクエリを注意深く読み、返される結果を最も正しく説明している答えを選んでください。

SELECT DISTINCT ON (department_id) department_id, employee_name, salary FROM employees ORDER BY department_id, salary DESC;

レッスンのまとめ

よくできました。PostgreSQL の DISTINCT ON について、次のことを学びました。

DISTINCT ON (col) は、指定した列の一意な値ごとにちょうど1行を返します。
ORDER BY 句は DISTINCT ON に記述した同じ列から必ず始める必要があり、各グループからどの行を選択するかを制御します。
複数の列を使用できます。DISTINCT ON (col1, col2) は、両方の列の組み合わせでグループ化します。
GROUP BY とは異なり、DISTINCT ON は元の列をすべて持つ実際の行を返すため、集約は必要ありません。
最終出力を別の列で並べ替える必要がある場合は、クエリをサブクエリで囲みます。

DISTINCT ON は PostgreSQL 固有の機能であり、「グループごとに1行を選ぶ」問題を解決する最も洗練された方法の1つです。

よくある質問

「PostgreSQLのDISTINCT ON」レッスンは無料ですか？

はい。「PostgreSQLのDISTINCT ON」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「PostgreSQLのDISTINCT ON」で何を学びますか？

グループごとに1行を選びます。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン3/4です。

「PostgreSQLのDISTINCT ON」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Academyに戻る