SQL99のWITH句について

MySQLしか触った事のない人にとっては全く知らない文法だというとこを最近知ったので。

WITH句とは

WITH句は、ネストされたテーブルに別名をつけて読みやすくするために使用する構文。

PostgreSQL, Oracle, MS SQL Serverなどで使用可能。 MySQLは(少なくとも少し前のバージョンでは)対応していない。

-- ネストされたテーブル
SELECT
    *
FROM
    (
        SELECT
            user_id,
            SUM(payment) AS total_payment
        FROM
            payments
        GROUP BY
            user_id
    ) a
WHERE
    a.total_payment > 3000

この例だとHAVINGすればいいとかはひとまず無視して、WITHを使うとこんな感じ。

WITH a AS (
    SELECT
        user_id,
        SUM(payment) AS total_payment
    FROM
        payments
    GROUP BY
        user_id
)

SELECT
    *
FROM
    a
WHERE
    a.total_payment > 3000

通常の言語の変数宣言と同じ！！便利！！！ 変数のように、適切な名前をつけるべき。aとかbとかfとか。

複数の変数を使用する

WITH句で複数のテーブルを取得する場合、,で繋げる。

WITH a AS (SELECT ...),
b AS (SELECT ...),
c AS (SELECT ...)

SELECT * FROM c

自分より上で宣言された別名は利用可能！！

WITH a AS (
    SELECT
        user_id,
        MAX(log_date) AS log_date
    FROM
        some_log
    GROUP BY
        user_id
),
b AS (
    SELECT
        *
    FROM
        some_log s
    WHERE
        EXISTS(
            SELECT
                0
            FROM
                a
            WHERE
                a.user_id = s.user_id
                AND
                a.log_date = s.log_date
        )
),

...

再帰

一部条件を満たせば、自分自身の中でも再帰的に参照が可能。

WITH a AS (
    SELECT 0 AS index
    UNION
    SELECT index + 1 AS index FROM a WHERE index < 2
)
SELECT * FROM a

• PostgreSQLではWITH RECURSIVE a ASとしないと不可。他のDBは知らない。
  つまり1番目の変数のみ再帰可能。

関数型の再帰と同様に、ループの終了条件書かないと無限ループして返ってこないので注意。

結果はこんな感じ。

index
=====
0
1
2
3

これを利用すれば「n月m日からo日までの毎日、0件なら0を取得する」なども簡単ですよね？

パフォーマンスについて

WITH句で別名をつけた場合、1つの名前につき最大1回しか読み込まれないことが保証される。 また、ほとんどのDBMSではSQLを最後まで読み込んだうえで最適化するので、インデックススキャンで済むものは一時テーブルを作成しない。 なのでネストした表現と最低限同等、読み込み回数によってはWITHの方が圧倒的に効率化が可能。

でも、最後に信じるべきはオプティマイザーですね。過信ダメ、ゼッタイ。

あ、MySQLではCREATE TEMPORARY TABLE 〜 SELECT 〜を使おう。
こっちは自分でインデックス貼れるのでもっと早いよ！！！