【PostgreSQL】集計結果の2番目以降を取得する方法を解説！【OFFSET・ウィンドウ関数】

スポンサーリンク
2025.01.29

データ分析やレポート作成の際、集計結果から「トップ１件を除いたデータを取得したい」「2位以降のデータを表示したい」といった要件が出てくることがあります。例えば、売上ランキングで1位を除いたデータを表示したり、特定の条件を満たすデータの2番目以降を取得したい場合などです。

PostgreSQLでは、このような集計結果のフィルタリングを行う方法として、OFFSET を使う方法や ウィンドウ関数 （RANK()や ROW_NUMBER()）を使う方法があります。本記事では、それぞれの方法を具体的なSQLとともに解説し、どのような場面で使い分けるべきかを説明します。

サンプルデータを用意してわかりやすく解説しますので、ぜひ最後までご覧ください！

使用するサンプルデータ

まずは、今回の例で使用するデータを準備します。ここでは、売上データを記録する sales テーブルを作成し、カテゴリごとの売上額を集計するケースを想定します。

以下のSQLを実行して、サンプルデータを作成しましょう。

テーブルの作成

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    category TEXT,
    amount INT
);

データの挿入

INSERT INTO sales (category, amount) VALUES
    ('A', 100),
    ('A', 200),
    ('A', 300),
    ('B', 150),
    ('B', 250),
    ('B', 350);

この sales テーブルには、各カテゴリ（category）の売上（amount）が記録されています。このデータを基に、カテゴリごとの売上合計を算出し、2番目以降のデータを取得する方法を見ていきます。

OFFSETを使った方法

最もシンプルな方法は、OFFSET を使用する方法です。これは、集計結果のうち指定した件数をスキップして取得する方法で、LIMIT と組み合わせてよく使われます。

基本的な考え方

  • LIMIT を使うと、取得するデータの件数を指定できます。
  • OFFSET を使うと、指定した件数分のデータをスキップできます。
  • ORDER BY を適切に指定することで、並び順をコントロールできます。

例として、売上合計をカテゴリごとに集計し、1位を除いたデータを取得する際のSQLを見てみましょう。

SELECT category, SUM(amount) AS total_sales
FROM sales
GROUP BY category
ORDER BY total_sales DESC
OFFSET 1;

実行結果

このクエリでは、カテゴリごとの売上合計を計算し（SUM(amount)）、売上が高い順（ORDER BY total_sales DESC）に並べた後、最も売上の高い1件を OFFSET 1 でスキップしています。そのため、2番目以降のデータのみが取得されます。

ウィンドウ関数を使った方法

OFFSET を使う方法はシンプルですが、同じ順位のデータが複数ある場合や、特定のランキングを考慮する場合にはウィンドウ関数 を使う方法が便利です。

ウィンドウ関数の基本

PostgreSQLのRANK() や ROW_NUMBER() を使うと、各データに順位を振り分けることができます。

  • RANK()
    順位を付与するが、同順位のデータがある場合はスキップされる（例: 1位が2件あると次は3位になる）。
  • ROW_NUMBER()
    一意の連番を振る（同順位でも連番になる）。

RANK() 関数を使って、２番目以降のデータを取得するSQLを見ていきましょう。

WITH ranked_sales AS (
    SELECT category, SUM(amount) AS total_sales,
           RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk
    FROM sales
    GROUP BY category
)
SELECT category, total_sales
FROM ranked_sales
WHERE rnk > 1;

実行結果（売上が1位のデータを除外）

このクエリでは、次の処理を行っています。

  1. SUM(amount) を計算してカテゴリごとに売上合計を集計
  2. RANK() OVER (ORDER BY SUM(amount) DESC) で順位を振る
  3. WHERE rnk > 1 で1位を除外し、2位以降のデータを取得

RANK()とROW_NUMBER()の違い

もし、同じ売上合計のデータがあった場合、RANK() は順位をスキップしますが、ROW_NUMBER() を使うと完全な連番になります。

ROW_NUMBER() を使う例

WITH ranked_sales AS (
    SELECT category, SUM(amount) AS total_sales,
           ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row_num
    FROM sales
    GROUP BY category
)
SELECT category, total_sales
FROM ranked_sales
WHERE row_num > 1;

ROW_NUMBER() を使うと、1件目以降を単純にスキップ する形になります。

どの方法を使うべき？

これまでに紹介した OFFSET を使う方法 と ウィンドウ関数 (RANK() / ROW_NUMBER()) を使う方法 には、それぞれの特徴があります。どちらを使うべきか、以下のポイントを参考に選びましょう。

OFFSET を使うべきケース

  • シンプルに上位1件を除外したい場合
  • ランキングを意識する必要がなく、単に2件目以降のデータが欲しい場合
  • 小規模なデータセットでパフォーマンスの問題がない場合

注意点 OFFSET はデータの並び順に影響を受けるため、 ランキングにギャップがある場合（1位が2つある場合など）には適さない ことがあります。

ウィンドウ関数（RANK() / ROW_NUMBER()）を使うべきケース

  • ランキングの順位を考慮し、正確に「2位以降」を取得したい場合
  • 1位のデータが複数存在する場合でも対応したい場合
  • より柔軟なデータ処理が必要な場合

ポイント

  • RANK() を使うと、 同じ順位のデータがある場合も適切に処理 できる。
  • ROW_NUMBER() を使うと、 単純に2行目以降を取得する用途に適している（ただし、同順位のデータがあっても連番になるため、厳密なランキングを考慮したい場合は RANK() のほうが適切）。

基本的には、 「シンプルな処理なら OFFSET」「ランキングを考慮するなら RANK() / ROW_NUMBER()」 という使い分けがベストです。

まとめ

本記事では、PostgreSQLで集計結果の2番目以降のデータを取得する方法について解説しました。

実際のデータに応じて最適な方法を選び、効率的なSQLを書いていきましょう！

スポンサーリンク

コメント