WHERE句とLIMIT句を使いこなせ！ PostgreSQLで条件付きかつ行制限付きのSELECTクエリを実行する方法

PostgreSQL の SELECT で行を制限する方法

LIMIT 句

LIMIT 句は、SELECT クエリの後に記述し、返される行の最大数を指定します。構文は以下の通りです。

SELECT *
FROM table_name
LIMIT n;

ここで、n は返される行の最大数です。例えば、以下のクエリは、customers テーブルから最初の 10 件のレコードのみを返します。

SELECT *
FROM customers
LIMIT 10;

オフセットと組み合わせる

LIMIT 句を OFFSET 句と組み合わせて、結果セットの特定の部分を返すこともできます。OFFSET 句は、LIMIT 句の前に記述し、スキップする行数を指定します。構文は以下の通りです。

SELECT *
FROM table_name
OFFSET m
LIMIT n;

ここで、m はスキップする行数、n は返される行の最大数です。例えば、以下のクエリは、customers テーブルから 11 番目のレコード以降の最初の 10 件のレコードを返します。

SELECT *
FROM customers
OFFSET 10
LIMIT 10;

WHERE 句を使用した制限

特定の条件に一致する行のみを返すには、WHERE 句を使用できます。例えば、以下のクエリは、orders テーブルから status 列が 'shipped' であるすべてのレコードを返します。

SELECT *
FROM orders
WHERE status = 'shipped';

この方法は、結果セットの行数を制限するためにのみ使用する場合には非効率的です。なぜなら、PostgreSQL はすべての行をスキャンしてから条件を評価する必要があるからです。

サブクエリを使用した制限

より効率的な方法は、サブクエリを使用して結果セットを制限することです。サブクエリは、FROM 句で参照されるクエリです。構文は以下の通りです。

SELECT *
FROM table_name
WHERE rownum IN (
    SELECT rownum
    FROM (
        SELECT *
        FROM table_name
        ORDER BY column_name
        LIMIT n
    ) AS subquery
);

ここで、n は返される行の最大数、column_name はソートする列名です。

この方法は、ORDER BY 句と組み合わせて、結果セットを特定の順序で返す場合に特に役立ちます。

サンプル 1: LIMIT 句を使用した制限

この例では、customers テーブルから最初の 10 件のレコードのみを返すクエリを示します。

SELECT *
FROM customers
LIMIT 10;

サンプル 2: OFFSET 句と組み合わせて LIMIT 句を使用する

SELECT *
FROM customers
OFFSET 10
LIMIT 10;

この例では、orders テーブルから status 列が 'shipped' であるすべてのレコードを返すクエリを示します。

SELECT *
FROM orders
WHERE status = 'shipped';

サンプル 4: サブクエリを使用した制限

この例では、employees テーブルから給与が 1,000 ドルを超えるすべての従業員を名前の昇順で返すクエリを示します。

SELECT *
FROM employees
WHERE salary > 1000
ORDER BY name
LIMIT 10;

PostgreSQL 8.0 以降では、ウィンドウ関数を使用して結果セット内の行を制限することができます。ウィンドウ関数は、現在の行だけでなく、その行の周辺の行にもアクセスできる関数です。

この方法は、複雑な制限条件を処理する場合に特に役立ちます。例えば、以下のクエリは、各顧客の最新の注文 1 件のみを返します。

SELECT *
FROM orders
ORDER BY customer_id, order_date
WINDOWING (
    PARTITION BY customer_id
    ORDER BY order_date DESC
    ROWS BETWEEN PRECEDING 1 AND CURRENT ROW
);

CTE (Common Table Expression) を使用する

CTE は、再利用可能な一時的な結果セットを定義するために使用できるクエリです。CTE を使用して、複雑な制限条件をより小さな、より管理しやすい部分クエリに分割することができます。

この方法は、長いまたは複雑なクエリをより読みやすく、保守しやすくする場合に役立ちます。例えば、以下の CTE は、各顧客の最新の注文と顧客情報を返すことができます。

WITH latest_orders AS (
    SELECT *
    FROM orders
    ORDER BY customer_id, order_date
    WINDOWING (
        PARTITION BY customer_id
        ORDER BY order_date DESC
        ROWS BETWEEN PRECEDING 1 AND CURRENT ROW
    )
)
SELECT o.*, c.name
FROM latest_orders o
JOIN customers c ON o.customer_id = c.id;

MATERIALIZED VIEW を使用する

MATERIALIZED VIEW は、データベースに保存された永続的な結果セットです。MATERIALIZED VIEW は、複雑なクエリの結果を事前に計算して保存しておくことで、パフォーマンスを向上させることができます。

この方法は、頻繁に実行されるクエリのパフォーマンスを向上させる場合に役立ちます。例えば、以下の MATERIALIZED VIEW は、各顧客の最新の注文と顧客情報を保存します。

CREATE MATERIALIZED VIEW latest_orders_view AS
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
ORDER BY customer_id, order_date
WINDOWING (
    PARTITION BY customer_id
    ORDER BY order_date DESC
    ROWS BETWEEN PRECEDING 1 AND CURRENT ROW
);

この MATERIALIZED VIEW を使用して、以下のクエリを実行できます。

SELECT *
FROM latest_orders_view;

PostgreSQL で行を制限するには、さまざまな方法があります。最良の方法は、特定のニーズによって異なります。簡単な制限条件の場合は、LIMIT 句または WHERE 句を使用するのが最良の方法です。より複雑な制限条件の場合は、ウィンドウ関数、CTE、または MATERIALIZED VIEW を使用する方法を検討する必要があります。

リソース