PostgreSQL 入門

SQL のLIMITおよびOFFSET句

データベースから特定のデータサブセットを抽出する能力は、パフォーマンスとユーザビリティにおいて非常に重要です。クエリが返すデータ量を制限できない場合、アプリケーションは不要な情報に圧倒され、レスポンスタイムの悪化やリソース消費の増大を招く可能性があります。SQLにおける LIMIT および OFFSET 句（特に PostgreSQL において）は、返される行数を制御し、ページネーション（Pagination）を実現するための強力なメカニズムを提供します。これは、大規模なデータセットを管理しやすい形で表示するために不可欠です。本章では、これら2つの句を効果的に使用する方法を深く掘り下げます。

1. LIMIT句の理解

LIMIT 句は、SELECT ステートメントが返す行数を制限するために使用されます。通常、クエリの末尾に記述され、返される最大行数を指定する整数の引数（パラメーター）を受け取ります。

1.1 基本構文と使用方法

基本構文は以下の通りです。

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1, ...
LIMIT number_of_rows;

以下は、仮想の products（製品）テーブルを使用したシンプルな例です。

-- 最近追加されたトップ5の製品を選択
SELECT product_name, price
FROM products
ORDER BY created_at DESC
LIMIT 5;

この例では、クエリは products テーブルから product_name（製品名）と price（価格）を選択し、created_at（作成日時）列で降順にソート（最新のものが最初に来るように）した後、出力を最初の5行に制限しています。

1.2 実践的なユースケース

ECサイトで「注目の製品」を表示したい場合を想定します。ホームページ上の目立つ場所には、少数の製品のみを表示する必要があります。

-- 評価の高いトップ3の製品を表示用として選択
SELECT product_name, rating
FROM products
ORDER BY rating DESC
LIMIT 3;

別のシナリオとして、最も安価な3つの製品を表示する場合：

-- 最も安価な3つの製品を取得
SELECT product_name, price
FROM products
ORDER BY price ASC
LIMIT 3;

1.3 エッジケースと注意点

  • 一致する行がない場合： WHERE 句（存在する場合）ですべての行がフィルタリングされた場合、LIMIT は単に空の結果セットを返します。エラーは発生しません。
  • LIMIT 0： LIMIT 0 を使用すると、テーブルのデータに関係なく、常に空の結果セットが返されます。これは、実際のデータを取得せずにクエリの構造（スキーマ）だけをテストしたい場合に非常に便利です。

2. OFFSET句の理解

OFFSET 句は、行を返し始める前に、結果セットの先頭から何行をスキップするかを指定します。通常、ページネーション機能を実装するために LIMIT と組み合わせて使用されます。

2.1 基本構文と使用方法

基本構文は以下の通りです。

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1, ...
LIMIT number_of_rows OFFSET number_of_rows_to_skip;

以下の例を見てみましょう。

-- 11番目の製品から5つを選択（最初の10行をスキップ）
SELECT product_name, price
FROM products
ORDER BY product_name
LIMIT 5 OFFSET 10;

このクエリは products テーブルから product_name と price を選択し、product_name のアルファベット順にソートして、最初の10行をスキップした後、続く5行を返します。

2.2 実践的なユースケース

ブログでページネーションを実装する場合、1ページに10記事ずつ表示したいとします。

第1ページ：

-- 最初の10件のブログ記事を取得（公開日順）
SELECT title, content
FROM blog_posts
ORDER BY publication_date DESC
LIMIT 10 OFFSET 0;

第2ページ：

-- 次の10件（11〜20件目）を取得
SELECT title, content
FROM blog_posts
ORDER BY publication_date DESC
LIMIT 10 OFFSET 10;

第3ページ：

-- さらに次の10件（21〜30件目）を取得
SELECT title, content
FROM blog_posts
ORDER BY publication_date DESC
LIMIT 10 OFFSET 20;

一般的に、第 n ページを表示するための OFFSET の計算式は (n - 1) * page_size となります。ここで page_size は1ページあたりの表示件数です。

2.3 構文のバリエーションについて

PostgreSQL では、LIMIT と OFFSET の記述順序を柔軟に入れ替えることができます。

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, ...
LIMIT number_of_rows OFFSET number_of_rows_to_skip;

-- 以下のように記述することも可能（PostgreSQLではLIMITとOFFSETの順序は互換性があります）
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, ...
OFFSET number_of_rows_to_skip LIMIT number_of_rows;

これら2つのクエリは PostgreSQL において完全に等価です。後者の形式は単なる構文上のバリエーションです。

2.4 エッジケースと注意点

  • LIMITなしのOFFSET： 技術的には許可されていますが、LIMIT なしで OFFSET 単独で使用することは推奨されません。LIMIT がない場合でも、データベースはオフセット（スキップする行数）に達するまでのすべての行を処理する必要があり、オフセットが大きくなると非常に非効率になります。
  • 非常に大きなOFFSET値： 極端に大きな OFFSET 値を使用すると、特に巨大なテーブルではパフォーマンスの問題が発生する可能性があります。データベースは、制限された結果セットを返す前にテーブルの大部分をスキャンしなければならない場合があるからです。超大規模データセットでパフォーマンスが重要な場合は、カーソルページネーション（Keyset Pagination / キーセットページネーション）などの代替手法を検討してください。
  • OFFSETが総行数を超える場合： OFFSET 値が結果セットの総行数以上である場合、クエリは空の結果セットを返します。エラーはスローされません。
  • ソーティングの重要性： LIMIT および OFFSET を使用する場合、ORDER BY 句は必須です。これがないと、返される行の順序は未定義となり、クエリを実行するたびに予測不能な結果が得られる可能性があります。ページネーションは、一貫したソーティング（並び替え）に強く依存しています。

3. LIMITとOFFSETの組み合わせ

ページネーションの例で示したように、LIMIT と OFFSET は組み合わせて使用することで最大の力を発揮します。LIMIT が1ページあたりの行数を制御し、OFFSET が表示するページを決定します。

3.1 例：検索結果の表示

ECサイトの検索機能を想像してみてください。1ページに20件の結果を表示したいとします。

-- "shoes" という単語を含む製品を検索し結果を表示
SELECT product_name, description, price
FROM products
WHERE description LIKE '%shoes%'  -- 検索キーワードを含む
ORDER BY relevance_score DESC      -- 関連性スコア（クエリとのマッチ度）順にソート
LIMIT 20 OFFSET 0;                  -- 第1ページ（最初の20件）

第2ページを取得する場合：

SELECT product_name, description, price
FROM products
WHERE description LIKE '%shoes%'
ORDER BY relevance_score DESC
LIMIT 20 OFFSET 20;  -- 第2ページ（21〜40件目）

3.2 一貫性のあるソーティングの維持

すべてのページネーションリクエストにおいて、一貫した ORDER BY 句を維持することが極めて重要です。ページ間でソート順が変わってしまうと、ユーザーに重複した項目が表示されたり、一部の項目が欠落したりする可能性があります。上記の例では、リクエストごとに relevance_score（関連性スコア）列が一貫して計算される必要があります。