SQLite CTE入門 - WITH句で読みやすいクエリを書く

CTEは「名前を付けたサブクエリ」

共通テーブル式（CTE: Common Table Expression）とは、サブクエリを切り出して名前を付けたものです。SELECT の中に別の SELECT をネストする代わりに、先頭で WITH を使って定義し、名前を付けて、メインのクエリの中でそれをテーブルのように参照します。

形は常にこのパターンです。

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

上から順に読めるのがポイントです。まず customer_totals という名前付きの結果セットを作り、その後でそれをクエリする、という流れになっています。CTE は、このステートメントが実行されている間だけ存在する一時的なビューのようなものだと考えてください。

CTE を使わずに同じクエリを書くと

比較のために、同じロジックをサブクエリで書いた例を見てみましょう。CTE が何を置き換えているのかが見えてきます。

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

答えはまったく同じです。ただし、読む順序に注目してください。サブクエリ版だと、いったん括弧の中に潜って何を計算しているのかを把握し、それからまた外に戻ってくる、という視線の動きが必要になります。一方CTE版は、処理が実際に進む順番どおりに読めます。まず中間結果を定義して、それを使う、というシンプルな流れです。小さなクエリならどちらでも大差ありません。でも処理が3つ4つと積み重なってくると、サッと読めるコードと、頑張って解読しないといけないコードの差として効いてきます。

1つのクエリで複数のCTEを使う

複数のCTEはカンマで区切って数珠つなぎに書けます。それぞれのCTEは、その前に定義したCTEを参照できるので、名前付きのステップを連ねたパイプラインのように組み立てられます。

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

WITH は1回だけ書いて、あとはカンマで CTE 定義を並べていきます。2番目の CTE（big_spenders）は、最初の CTE（customer_totals）をまるでテーブルのように参照しています。メインの SELECT は、最後の CTE 定義の後ろに続けます。

よくあるミスとして、2つ目の CTE の前にもう一度 WITH を書いてしまうパターンがあります。これは構文エラーになるのでNGです。WITH は最初の1回だけで、すべての CTE をカバーします。

同じ CTE を複数回参照する

CTE がサブクエリより一歩リードするのが、まさにこの場面です。同じ中間結果を2か所で使いたいとき、CTE なら一度計算した結果を2回参照できます。

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

このCTEは2回参照されています。1回目は平均値の計算用、2回目はメインのデータソースとしてです。もしCTEを使わなければ、GROUP BYのクエリを2か所に書くことになり、変更があるたびに両方を直す必要が出てきます。

INSERT・UPDATE・DELETEでのCTE活用

CTEはSELECT専用というわけではありません。WITH句はINSERT、UPDATE、DELETEの前にも置けるので、書き込み系の処理でも名前付きサブクエリとして使えます。

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

CTEで「どの行に印を付けるか」を定義し、INSERT ... SELECT のソースとして使う、という流れです。同じ書き方は DELETE FROM ... WHERE id IN (SELECT id FROM cte) にも応用できるので、削除対象の絞り込みロジックが複雑なときの段階的な削除にも便利です。

CTEを使うべきタイミング

判断の目安をいくつか挙げておきます。

• クエリが複数の論理的なステップに分かれているとき。 集計して、その集計結果でフィルタして、さらに別のテーブルと結合して……というパイプライン処理は、ステップごとにCTEを切り出すと一気に読みやすくなります。
• 同じサブクエリを何度も書くことになりそうなとき。 一度定義して、二回参照すればOKです。
• そのサブクエリに名前を付けたいとき。 「このサブクエリは何をしているのか」をコメントで補足したくなるなら、CTEの名前自体がそのコメント代わりになります。しかも構文として強制されるので、ズレることがありません。
• 再帰クエリを書こうとしているとき。 これは WITH RECURSIVE でしか実現できません。次ページで詳しく扱います。

逆に、CTEにしなくてよい場面はこちら。

• 一箇所でしか使わないシンプルなサブクエリ。WHERE id IN (SELECT id FROM ...) のままで十分です。
• パフォーマンスがシビアなクエリで、ロジックを直接埋め込んだ方が速いと検証済みの場合。SQLiteは他のDBほどCTEを最適化の壁(optimization fence)として強く扱わないのですが、ホットパスの処理なら EXPLAIN QUERY PLAN で実行計画を確認しておくと安心です。

実例で見るCTEの使い方

ここまでの内容を組み合わせて、ちょっとしたレポートを書いてみましょう。各顧客の最大注文額と、その顧客の平均注文額との比較を出すクエリです。

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

CTEを2つ用意して、それぞれに1つの役割を持たせています。最後の SELECT は結果の整形担当です。クエリを上から順に読むだけで、各ステップが何をしているか自然に追えるはず——これこそがCTEを使う最大のメリットですね。

次のステップ：再帰CTE

ここまで紹介してきたのは、いわゆる通常のCTE、つまり「名前付きサブクエリを1回だけ評価する」パターンでした。SQLiteにはこれに加えて WITH RECURSIVE という構文もあって、CTEが自分自身を参照することで、階層構造をたどったり、連番を生成したり、グラフを探索したりできます。詳しくは次のページで見ていきましょう。