SQLのWITH句について

PostgreSQLを使用した案件にてサブクエリを最大５つ使用するSQLを作成していたが条件項目が多くレスポンスでよい結果がでないこともあり、そこでWITH句を使用することにした。

WITH句

PostgreSQLのドキュメントには以下のように書かれています。
https://www.postgresql.jp/document/9.5/html/queries-with.html

WITHは、より大規模な問い合わせで使用される補助文を記述する方法を提供します。 これらの文は共通テーブル式(Common Table Expressions)またはCTEとよく呼ばれるものであり、１つの問い合わせのために存在する一時テーブルを定義すると考えることができます。 WITH句内の補助文はそれぞれSELECT、INSERT、UPDATEまたはDELETEを取ることができます。 そしてWITH句自身は、これもSELECT、INSERT、UPDATEまたはDELETEを取ることができる主文に付与されます。

簡単にいうとサブクエリに名前を付けて使用する感じになりますね。

レスポンスの計測結果ですが
サブクエリ2個の場合で1.5倍高速に、
サブクエリ5個の場合で8.2倍高速になる結果となりました。

全てのクエリでレスポンスが向上するものを保証するものではありませんので

以下のSQL例が、今回改修したSQLのおおまかな例です。

WITH a1 AS (
    SELECT 
        z.id2 
        SUM(a.yy) yy_count
    FROM (
        SELECT 
            id,
            COUNT(xx) yy
        FROM table1
        WHERE
            aaa = '001'
            AND bbb = '002'
            AND ccc = '003'
        GROUP BY id
        ) a
    INNER JOIN
        table2 AS z
        ON z.id = a.id
    GROUP BY 
        z.id2
),
a2 (
    SELECT 
        z.id2 
        SUM(a.yy) yy_count
    FROM (
        SELECT 
            id,
            COUNT(xx) yy
        FROM table1
        WHERE
            aaa = '001'
            AND bbb = '012'
            AND ccc = '013'
            AND ddd = '014'
        GROUP BY id
        ) a
    INNER JOIN
        table2 AS z
        ON z.id = a.id
    GROUP BY 
        z.id2
)
SELECT
    b.*
FROM
    main_table AS b
LEFT OUTER JOIN a1
    ON a1.id2 = b.id2
LEFT OUTER JOIN a2
    ON a2.id2 = b.id2
WHERE
    b.eee = '0101'
ORDER BY
    b.id

WITH句が使用できるRDBMS

PostgreSQL
https://www.postgresql.jp/document/9.5/html/queries-with.html
Oracle
http://www.oracle.com/technetwork/jp/articles/otnj-sql-image7-1525406-ja.html#a
MS SQL Server
https://msdn.microsoft.com/ja-jp/library/ms175972.aspx

MySQL 5.7まではWITH句はサポートされていないようです。SQL-99で規格化されているものなんですけどねぇ
MySQLは使えないけど、MariaDBは使えるようです（SQL-99 Complete ってサイトに載ってます）。
MySQL8 ではWITH句がサポートされているらしいのですが未確認。

MariaDB
https://mariadb.com/kb/en/mariadb/with/
Amazon Redshift
http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_WITH_clause.html